Creating SQL Server Stored Procedures in C#

Written by on Sunday 4th May 2008 under Databases

In this article we will look at creating stored procedures using C#. We will query the database to ensure that the procedure does not exist before we add it to the data dictionary.

This tutorial follows on from a previous tutorial where we looked at how to call stored procedures from C#

We need to create a connection to the SQL Server and execute a command that will interrogate the stored procedures for the database and store the results in the SqlDataReader. If the DataReader does not contain any rows then the stored procedure does not exist and it is safe to create it. If it does exist you may wish to update or delete it.

try
{
  SqlConnection MyConnection = new SqlConnection("server=(local);database=pubs;UID=myUser;PWD=myPassword;");
 
  SqlCommand MyCommand = new SqlCommand("select * from sysobjects where id = object_id(N'GetAuthorsByLastName') and OBJECTPROPERTY(id, N'IsProcedure') = 1", MyConnection);
  MyCommand.CommandType = CommandType.Text;
  MyCommand.Connection.Open();
 
  SqlDataReader MyDataReader = MyCommand.ExecuteReader();
 
  // If any rows are returned, the stored procedure that you are trying 
  // to create already exists. Therefore, try to create the stored procedure
  // only if it does not exist.
  if (!MyDataReader.Read())
  {
    MyCommand.CommandText = "create procedure GetAuthorsByLastName(@au_lname varchar(40), select * from authors where au_lname like @au_lname; select @RowCount=@@ROWCOUNT";
    MyDataReader.Close();
    MyCommand.ExecuteNonQuery();
  }
  else
  {
    MyDataReader.Close();
  }
}
catch (Exception ex)
{
  Console.WriteLine(ex.Message);
}
finally
{
  MyCommand.Dispose();
  MyConnection.Close();
}

More Tutorials in Active Data Objects (ADO.Net)

  1. What are Active Data Objects?
  2. Connecting to a SQL Server Database with C#
  3. Consuming Data in Windows Forms C# Applications
  4. Using ADO.Net Controls in ASP.Net Pages
  5. Modifying Data and Updating Databases with C#
  6. Connecting to MySql Databases with C# / ASP.Net
  7. Reading Excel Spreadsheets with C# ADO.Net
  8. Creating SQL Server Stored Procedures in C# ⇦ You Are here
  9. Calling SQL Server Stored Procedures from C#
  10. Importing and Exporting XML from a DataSet

One Response to “Creating SQL Server Stored Procedures in C#”

  1. Francissays:

    I'd like to ask what is the difference of ADO.net with OLEDB connection?

Leave a Reply