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

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    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

  • Question
    Sign in to vote
    0
    Sign in to vote

    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
    |