Learning never exhausts the mind

By

In this article, we will look at creating SQL Server 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.

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

 

Sharing is caring!

If you enjoyed this post, please share it with others. Click one of the social media buttons below to share on Facebook, Twitter, LinkedIn, Pinterest or email to a friend.

One thought on “Creating SQL Server Stored Procedures in C#
  • 20th January 2012 at 12:00 am

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

    Reply

Leave a Reply

Fields marked with * are mandatory.

We respect your privacy, and will not make your email public. Hashed email address may be checked against Gravatar service to retrieve avatars. This site uses Akismet to reduce spam. Learn how your comment data is processed.