Stored Procedures in C#

ADO.NET consists of a set of Classes that interact to provide the required functionality. A stored procedure is a group of Transact-SQL statements compiled into a single execution plan. A sample Stored Procedure is given below :

 
CREATE PROCEDURE SPPUBLISHER
  AS
  SELECT PUB_NAME FROM publishers
  GO

The above code create a procedure named as 'SPPUBLISHER' and it execute SQL statement that select all publisher name from publishers table from the PUB database. Coding business logic into a single stored procedure also offers a single point of control for ensuring that business rules are correctly enforced.

The command Object in ADO.NET provides a number of Execute methods that can be used to perform the SQL queries in a variety of fashions. To call a stored procedure from C# , set the CommandType of the Command object to StoredProcedure.

 
command.CommandType = CommandType.StoredProcedure;

From the following source code you can see how to call a stored procedure from a C# application.

Next : C# Stored Procedure with Parameter

Download Source Code

Print Source Code

using System;
using System.Data;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace WindowsFormsApplication1
{
	public partial class Form1 : Form
	{
		public Form1()
		{
			InitializeComponent();
		}

		private void button1_Click(object sender, EventArgs e)
		{
			string connetionString = null;
			SqlConnection connection ;
			SqlDataAdapter adapter ;
			SqlCommand command = new SqlCommand();
			DataSet ds = new DataSet();

			int i = 0;

			connetionString = "Data Source=servername;Initial Catalog=PUBS;User ID=sa;Password=yourpassword";
			connection = new SqlConnection(connetionString);

			connection.Open();
			command.Connection = connection;
			command.CommandType = CommandType.StoredProcedure;
			command.CommandText = "SPPUBLISHER";
			adapter = new SqlDataAdapter(command);
			adapter.Fill(ds);

			for (i = 0; i <= ds.Tables[0].Rows.Count - 1; i++)
			{
				MessageBox.Show (ds.Tables[0].Rows[i][0].ToString ());
			}

			connection.Close();
		}
	}
}

C# Stored Procedure with Parameter