Monday, June 7, 2010

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:

01using System.IO;
02using Microsoft.SqlServer.Management.Common;
03using Microsoft.SqlServer.Management.Smo;
04
05namespace 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:


01Imports System.Data.SqlClient
02Imports System.IO
03Imports Microsoft.SqlServer.Management.Common
04Imports Microsoft.SqlServer.Management.Smo
05
06Namespace 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
17End Namespace


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

17 comments:

  1. THanks for this article

    ReplyDelete
  2. Hi. Thanks much.

    Can you please answer this question for me

    http://social.msdn.microsoft.com/Forums/en-US/vbgeneral/thread/ff46a43f-47cd-4783-8f16-07fa1e32e7f0

    ReplyDelete
  3. SQL Server version (7.0) is not supported.

    ReplyDelete
  4. will this handle if the script contains GO commands?
    using VS2008/SQL2008

    ReplyDelete
  5. Go VS 2010-> Project->Add Refrences
    browse : C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies
    Add
    1.Microsoft.SqlServer.Smo.dll
    2.Microsoft.SqlServer.Management.Sdk.Sfc.dll
    3.Microsoft.SqlServer.ConnectionInfo.dll

    ReplyDelete
    Replies
    1. This comment has been removed by the author.

      Delete
    2. hy, did all assemblys but when run the code gives error : mixed mode assembly is built against version 'v2.0.50727' of the runtime and cant be loaded in the 4.0 runtine without adicional configuration information.
      So i added in app.config
      startup useLegacyV2RuntimeActivationPolicy="true"
      supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.0,Profile=Client"
      startup
      runtime
      NetFx40_LegacySecurityPolicy enabled="true"
      runtime

      But i get the folowing error :
      An exception occured while executing Transact- Sql statment or batch.

      What can i do ?

      Delete
  6. how to create .sql File in Asp.ne C# coding

    ReplyDelete
  7. when I run the vb.net code above in console application, I got the following error:
    'Sub Main' was not found in 'ConsoleApplication1.Module1'

    Please help.

    ReplyDelete
  8. Hi Admin,
    Excellent blog and its totally loaded with valid posts on Java and .Net technology. Consider including RSS feed in your blog, so aspirants like me can follow your blog easily. .Net Training in Chennai

    ReplyDelete
  9. It is amazing and wonderful to visit your site.Thanks for sharing this information,this is useful to me...
    Android Training in Chennai
    Ios Training in Chennai

    ReplyDelete
  10. Thanks for sharing informative article… Know about How to Change BSNL WiFi Password from techfizy.

    ReplyDelete

  11. Dot Net is the best programming language ever. your blog have explained the excellent concept which help me to gain more info on C# programming language.

    dot net training institute in velachery |
    DOT NET Course Chennai

    ReplyDelete

Comments to this post

LinkWithin

Related Posts with Thumbnails