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();
                            }
                        }

Have you tried ignoring the USE altogether and just being explicit in the name? `"CREATE PROCEDURE [" + txtDBName + "]..[spInsertADAuthorization] ..."` ?? — Dracorat, Dec 28 '11 at 18:22
Have you considered using Entity Framework Code First and having it generate the database? Raw ADO.NET is a lot of extra work. — TrueWill, Dec 28 '11 at 18:30

score 7 · Accepted Answer · answered Dec 28 '11 at 18:21

7

"GO" is a batch separator. It's not a T-SQL Statement. Remove the "USE" and both "GO" lines and try again.

answered Dec 28 '11 at 18:21

John Saunders

160,644
26
247
397

...your connection string will specify what database you're operating on; that's why you don't need that `use` (if the database in your connection string matches the database you want to seed, of course). – Michael Petrotta Dec 28 '11 at 18:22
The problem is, the user chooses the DB name so the connection string doesnt know what the name is yet, hence the USE. Now i'm getting "{"Incorrect syntax near the keyword 'PROCEDURE'.\r\nMust declare the scalar variable \"@AD_Account\"."} when get rid of the GO's and the USE. – Dan C. Dec 28 '11 at 18:36
2

Can you use the [`ChangeDatabase`](http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.changedatabase.aspx) method to change the database for the connection before executing the code to create the SP. – John Saunders Dec 28 '11 at 18:46
John is right. In C# for example you first you have to `connection.Open();` `connection.ChangeDatabase(the_name_of_your_catalog);`. After those you can call `yourCommand.ExecuteNonQuery()` – Junior Mayhé Dec 14 '13 at 14:09

Phil Klein · Answer 2 · 2014-01-10T14:58:58.883

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();
    }
}

Getting this when running it : {"'CREATE/ALTER PROCEDURE' does not allow specifying the database name as a prefix to the object name.\r\nMust declare the scalar variable \"@AD_Account\"."} — Dan C., Dec 28 '11 at 18:44

score 1 · Answer 3 · answered May 10 '14 at 21:31

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

connection.ChangeDatabase("YourDB");

An 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);
    }
}

Nick · Answer 4 · 2011-12-28T19:26:26.080

0

If you're looking at creating a Database and maintaining versions in your code, you might want to consider using something like the Migrator Framework instead of hand writing all that SQL.

edited Dec 28 '11 at 19:26

answered Dec 28 '11 at 18:24

Nick

5,875
1
27
38

Creating a stored procedure via C#

4 Answers4

Linked

Related