Creating a stored procedure through C #

Question

I am trying to create an initial database in my web application, and I managed to create a database, fill in tables, I was just stuck in getting stored procedures to work. Here is what I have so far, but I get CREATE / ALTER PROCEDURE 'should be the first statement in the query package. \ R \ nInvalid syntax near' GO '. I also tried removing GO, and also adding \ r \ n between the USE statement and the no-luck creation routine. Any help would be appreciated.

StringBuilder sbSP = new StringBuilder(); sbSP.AppendLine("USE [" + txtDBName.Text + "]"); sbSP.AppendLine("GO"); sbSP.AppendLine("CREATE PROCEDURE [spInsertADAuthorization] @AD_Account varchar(255),@AD_SID varchar(255),@AD_EmailAddress varchar(255),@DateImported datetime,@Active bit AS BEGIN SET NOCOUNT ON; INSERT INTO AD_Authorization (AD_Account, AD_SID, AD_EmailAddress, DateImported, Active) VALUES (@AD_Account,@AD_SID,@AD_EmailAddress,@DateImported,@Active) END"); sbSP.AppendLine("GO"); using (SqlConnection connection = new SqlConnection(ConnectionString)) { using (SqlCommand cmd = new SqlCommand(sbSP.ToString(), connection)) { connection.Open(); cmd.CommandType = CommandType.Text; cmd.ExecuteNonQuery(); connection.Close(); } } 

Answer 1

"GO" is a batch separator. This is not a T-SQL expression. Delete the lines "USE" and "GO" and try again.

Answer 2

As already mentioned, the GO command is just a batch separator that is interpreted by SSMS. You want to create a stored procedure as follows:

 string sql = string.Format("CREATE PROCEDURE [{0}]..[spInsertADAuthorization] @AD_Account varchar(255),@AD_SID varchar(255),@AD_EmailAddress varchar(255),@DateImported datetime,@Active bit AS BEGIN SET NOCOUNT ON; INSERT INTO AD_Authorization (AD_Account, AD_SID, AD_EmailAddress, DateImported, Active) VALUES (@AD_Account,@AD_SID,@AD_EmailAddress,@DateImported,@Active) END", txtDBName.Text); using (SqlConnection connection = new SqlConnection(ConnectionString)) { using (SqlCommand cmd = new SqlCommand(sql, connection)) { connection.Open(); cmd.CommandType = CommandType.Text; cmd.ExecuteNonQuery(); connection.Close(); } } 

Answer 3

Instead of using 'USE [DataBase] Go', you can very easily set or modify the current database for an open SqlConnection:

 connection.ChangeDatabase("YourDB"); 

Example:

 private static void ConctDatabase(string connectionString) { using (SqlConnection conn = new SqlConnection(connectionString)) { conn.Open(); MessageBox.Show("Database: {0}", conn.Database); conn.ChangeDatabase("Northwind"); MessageBox.Show("Database: {0}", conn.Database); } } 

Answer 4

If you are looking to create a database and maintain versions in your code, you might want to use something like the Migrator Framework to write all of this SQL instead.