Web Design that taps into the haromny and vision of your dreams.

Creating SQL Server Stored Procedures in C#

By on in Coding

204 words, estimated reading time 1 minutes.

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.

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

Last updated on: Friday 23rd June 2017

 

Comments

Have a question or suggestion? Please leave a comment to start the discussion.

Francis

Francis

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

Reply to Francis

 

Leave a Reply

Please keep in mind that all comments are moderated according to our privacy policy, and all links are nofollow. Do NOT use keywords in the name field. Let's have a personal and meaningful conversation.

Your email address will not be published.