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