CODEWITHSUNDEEP
c#sql-serverstored-procedures
Prashant Tiwari
Prashant Tiwari

Reputation: 344

How To create a stored procedure for SQL Server from C# code?

I am trying to create a stored procedure in SQL server from a C# winforms application. This is the function I have so far.

public void CreateStoredProcedure(string SPname)
{
        try
        {
            string query = "CREATE PROCEDURE " + SPname + " AS SELECT * FROM People WHERE Address='Mumbai'";
            connection.Open();
            var command = new SqlCommand();
            command.CommandType = CommandType.Text;
            command.CommandText = "EXEC " + query;
            command.ExecuteNonQuery();
        }

        finally
        {
            connection.Close();
        }
}

Am I doing this right? I get an error message every time I try to achieve this.

Hey thanks a lot guys!! Its working now.. This is the code that finally did it..

 public void CreateStoredProcedure(string SPname)
    {
        try
        {
            string query = "CREATE PROCEDURE " + SPname + " AS SELECT * FROM People WHERE Address='Mumbai'";
            connection.Open();
            var command = new SqlCommand();
            command.Connection = connection;
            command.CommandText = query;
            command.ExecuteNonQuery();
            var adapter = new SqlDataAdapter(command);
            adapter.Fill(dt);
            dgv.DataSource = dt;
        }

        finally
        {
            connection.Close();
        }
    }

much appreciated! :)

Upvotes: 0

Views: 2310

Answers (5)

Osman Shareef
Osman Shareef

Reputation: 31

You need to define a connection object and link it with the command object

    CommandObject.Connection= ConnectionObject;

Also the CommandType.Text is by default.

You could also check if you connection is open using

    if(ConnectionObject.State== ConncetionState.Closed)
       {
        ConnectionObject.Open();
        }

If it is closed, you will need an active Open connection to pass a query.

Upvotes: 0

Zadalaxmi
Zadalaxmi

Reputation: 421

You can try:

 command.CommandType = CommandType.StoredProcedure;

Upvotes: 0

Conrad Lotz
Conrad Lotz

Reputation: 8828

Alternatively, you can create a Stored Procedure using Common Language Run-time integration in stead of doing it on the fly.

How to: Create and Run a SQL Server Stored Procedure by using Common Language Run-time Integration

Deploy CLR Stored Procedure to database

In your attempt above the code will only be able to run as a once off as it contains a CREATE command. It must then change to ALTER there after or you need to drop it every time and re-created. this would not be the best practice but just keep in mind.

Upvotes: 0

ElenA
ElenA

Reputation: 78

You do not need EXEC when creating a stored procedure and you need an active connection

Upvotes: 1

st4hoo
st4hoo

Reputation: 2204

Initialize connection property of your command:

            command.Connection = connection;

Upvotes: 0

Related Questions