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

Creating SQL Server Stored Procedures in C#

How to create SQL Server Stored Procedures in C#

Written By on in C#

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.

Active Data Objects (ADO.Net) Series
  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#
  9. Calling SQL Server Stored Procedures from C#
  10. Importing and Exporting XML from a DataSet

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
Francis

Francis

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

Reply to Francis

 

Leave a Reply

Your email address will not be published.





If you find something abusive or that does not comply with our terms or guidelines please flag it as inappropriate.

Copyright © 2001-2018 Tim Trott, all rights reserved. Web Design by Azulia Designs

This web page is licensed for your personal, private, non-commercial use only.

Disclaimer, Privacy & LegalSitemapContact Me