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 :

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.

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

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

namespace WindowsFormsApplication1
	public partial class Form1 : Form
		public Form1()

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

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

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

} (C) 2019    Founded by raps mk
All Rights Reserved. All other trademarks are property of their respective owners.
SiteMap  | Terms  | About