none
Creating multiple stored procedure in a single batch query through Ado.net RRS feed

  • Question

  • hi,

    i want to create multiple stored procedures in a single batch query through C#(ADo.net).

    But in 'CREATE/ALTER Procedure' it should be only statement. 

    I cannot use 'GO' inside this Batch query.

    Please anyone help i am stuck here. 

    Wednesday, February 2, 2011 1:56 PM

Answers

  • If you're talking about SQL Server, try using Sql SMO, it will handle the "GO"s.

    // -- need to add following references, found in C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies
    // Microsoft.SqlServer.Smo.dll 
    // Microsoft.SqlServer.ConnectionInfo.dll
    // Microsoft.SqlServer.Management.Sdk.Sfc.dll
    using Microsoft.SqlServer.Management.Smo;
    using Microsoft.SqlServer.Management.Common;
    
    // I have scripts stored in files and run them like this:
    using (SqlConnection connection = new SqlConnection(MyConnectionString))
    {
     Server smo = null;
     try
     {
      smo = new Server(new ServerConnection(connection));
      smo.ConnectionContext.BeginTransaction();
      smo.ConnectionContext.ExecuteNonQuery(this.GetFileString(FileName));
      smo.ConnectionContext.CommitTransaction();
     }
     catch (Exception ex)
     {
      this.ErrorMessage = string.Format(
       "An error has occurred while executing your script \r\n {0} \r\n Changes have been rolled back. \r\n Error is: \r\n {1}",
       FileName, ex.ToString());
    	
      smo.ConnectionContext.RollBackTransaction();
     }
    }
    
    

    ~~Bonnie Berent [C# MVP]

    geek-goddess-bonnie.blogspot.com
    Wednesday, February 2, 2011 4:33 PM