Creating a stored procedure via C#

Question

I'm trying to create a seed DB in my web app, and i was able to create the DB, populate the tables, i'm just stuck at getting the stored procedures to work. Here is what i have so far, but im getting CREATE/ALTER PROCEDURE' must be the first statement in a query batch.\r\nIncorrect syntax near 'GO'. I tried with removing the GO's too, and also adding \r\n's between the USE statement and the create procedure with no luck. 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. It's not a T-SQL Statement. Remove the "USE" and both "GO" lines and try again.

Answer 2

As others have mentioned, the "GO" command is simply a batch separator which is interpreted by SSMS. What you want to do is create the 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();
    }
}