Running sql script file from .Net code - C#.net and VB.net

Many a times we get into situations where we have to execute a .sql script file from .NET code on Sql Server for eg. either to create a new database, or creating a new table, or backing up or restoring database etc. But for doing so the SqlCommand class is not useful. We can't execute the batch commands in the .sql script file using the SqlCommand class because the .sql script  file contains 'GO' (batch finalizer command) in it. 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.

The following example demonstrates how to do it. Here I have my .sql script file as myscript.sql and it is in C:\ directory.

The code for it in C# is as follows:

01	using System.IO;

02	using Microsoft.SqlServer.Management.Common;

03	using Microsoft.SqlServer.Management.Smo;

04

05	namespace ConsoleApplication1

06

{

07	class Program

08

{

09	private static void Main(string[] args)

10

{

11	string sqlConnectionString = "Data Source=(local);Initial Catalog=AdventureWorks;Integrated Security=True";

12	FileInfo file = new FileInfo("C:\\myscript.sql");

13	string script = file.OpenText().ReadToEnd();

14	SqlConnection conn = new SqlConnection(sqlConnectionString);

15	Server server = new Server(new ServerConnection(conn));

16	server.ConnectionContext.ExecuteNonQuery(script);

17

}

18

}

19

}

The code for it in VB.Net is as follows:

01
Imports System.Data.SqlClient

02
Imports System.IO

03
Imports Microsoft.SqlServer.Management.Common

04
Imports Microsoft.SqlServer.Management.Smo

05


06
Namespace ConsoleApplication1

07
    Class Program

08
        Private Shared Sub Main(args As String())

09
            Dim sqlConnectionString As String = "Data Source=(local);Initial Catalog=AdventureWorks;Integrated Security=True"

10
            Dim file As New FileInfo("C:\myscript.sql")

11
            Dim script As String = file.OpenText().ReadToEnd()

12
            Dim conn As New SqlConnection(sqlConnectionString)

13
            Dim server As New Server(New ServerConnection(conn))

14
            server.ConnectionContext.ExecuteNonQuery(script)

15
        End Sub

16
    End Class

17
End Namespace

Thus this will execute the .sql script file from the .Net code.