If you have placed in situation to execute .sql script files from .NET code on SQL Server, you will see that SqlCommand class is not that useful. In other words, you can't execute batch commands that contains 'GO' (batch finalizer command). So you can think of several options, like split that script to several commands, and execute one by one. But you realize how complex is that task.If you wanted to do that prior to SQL Server 2005, the only option is using osql utility. Since SQL Server 2005 there is another option which is much better and preferred. That option is using SMO library which comes with SQL Server and can be used for managing everything on SQL Server 2005. You can backup, restore databases, configure permissions, replication, etc.I plan to post about how to backup and restore database using this SMO library in near future. Here will write only about how to read sql file and execute the content on SQL Server.
using System.Data.SqlClient;
using System.IO;
using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Smo;
namespace ConsoleApplication1
{
class Program
static void Main(string[] args)
string sqlConnectionString =
"Data Source=(local);Initial Catalog=AdventureWorks;Integrated Security=True";
FileInfo file = new FileInfo("C:\\myscript.sql");
string script = file.OpenText().ReadToEnd();
SqlConnection conn = new SqlConnection(sqlConnectionString);
Server server = new Server(new ServerConnection(conn));
server.ConnectionContext.ExecuteNonQuery(script);
}
This post is based from my answer on one MSDN forums' question located here.
Small improvement on your code: It can be much more faster if you change the string variable "script" to be from StringBuilder type, especially for large scripts.
Nice example!
nC example ....
i use MySQL but i undestand the idea xD
( i made one simillar ) but this is more simply
nC
Hi Boban,
I am trying to run your code but each time I receive an error, "An exception occurred while executing a Transact-SQL statement or batch".
I have tried a Create Database, Create Table, and Update Table scripts but the same error occurs.
Am I missing something here?
cheers,
Mark Chimes
More information on the problem. I removed the 'GO' statements and I can successfully run the Create Database and Create Table scripts. However, the Update table script always errors at the first '<'.
Strangely, when I script an Update of a table within the SQL Server Management Studio, it too errors at the first '<'.
If the script will not run in inside SQL, it obviously will not run from within a .NET application.
I am stumped.
This code was really helpful.It is working fine.
I need to pass Database name and Verion(another varaible ) as parameters in the script which is being called from c# code.
You have ideas to share?
Thanks a lot. This is exactly what I was looking for. I used it for SQL Express 2008 though. Had to spend a little time to figure out the assemblies corresponding to the namespaces. They are:
Microsoft.SqlServer.Smo (using Microsoft.SqlServer.Management.Smo;)
Microsoft.SqlServer.ConnectionInfo (using Microsoft.SqlServer.Management.Common;)
hi
I have used your code very good. but i need to do the same as above fro multiple sql files
can you put in some error logging
ta
Gerard
var conn = new ServerConnection(new SqlConnection (sqlConnectionString));
conn.ExecuteNonQuery(script);
I have similar problem. But i do not have the following assemblies.
Microsoft.SqlServer.Management.Common
Microsoft.SqlServer.Management.Smo
Where can i get them.?
Thanks in advance
--Vishnu
I have found the answer for the question i just posted. A Reference should be added to the following DLLs.
Microsoft.SqlServer.ConnectionInfo
Microsoft.SqlServer.Smo
Please post my comments, so that the next visitor will save some time.
Thanks
-Vishnu.