In this article, we shall learn how to use the stored procedure in ADO.NET.
ADO.NET is a mechanism to communicate with the database. It allows us to work in connected (database connection is alive while the operation is performed) as well as disconnected architecture (database connection is closed and operation can be performed on the data).
Get hundreds of ASP.NET Tips and Tricks and ASP.NET Online training here.
In this article, we are going to learn how to use the stored procedure in ADO.NET.
Below is my aspx page (aspx.page)
<asp:Label ID="lblData" runat="server" EnableViewState="false" />
In the above code snippet, we have a Label control in which we shall write the data retrieved from the stored procedure.
string _connStr = ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString;
protected void Page_Load(object sender, EventArgs e)
int autoId = 6;
/// Fetches the records.
private void FetchRecords(int autoId)
DataTable table = new DataTable();
// get the connection
using (SqlConnection conn = new SqlConnection(_connStr))
// instantiate the command object to fire
using (SqlCommand cmd = new SqlCommand("GetRecords", conn))
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter prm = new SqlParameter("@AutoId", SqlDbType.Int);
prm.Value = autoId;
// get the adapter object and attach the command object to it
using (SqlDataAdapter ad = new SqlDataAdapter(cmd))
// fire Fill method to fetch the data and fill into DataTable
// DataAdapter doesn't need open connection, it takes care of opening and
closing the database connection
// in case of concatenating multiple string we should use StringBuilder
StringBuilder strb = new StringBuilder();
// loop through the rows of the table
foreach (DataRow row in table.Rows)
strb.Append("<p>" + row["AutoId"] + " > " + row["FirstName"] + " > " +
row["LastName"] + " > " + row["Age"] + " > " + row["Active"]);
lblData.Text = strb.ToString();
The FetchRecords method accepts the AutoId as parameter and use a stored procedure (the name of the stored procedure is passed in place of the sql statement and CommandType is mentioned asCommandType.StoredProcedure). As this stored procedure is accepting
AutoId as parameter so I have created a
SqlParameter and added this to the
SqlCommand parameter collection. Rest all codes are same as if we are using the SQL Statement to retrieve the records from the database (Read my earlier article to know about it).
Once we have the data fetched from the database to DataTable, we are looping through and writing in the Label control.
CREATE PROCEDURE [dbo].[GetRecords]
-- Add the parameters for the stored procedure here
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT AutoID, FirstName, LastName, Age, Active FROM PersonalDetail
WHERE AutoId = @AutoId
Below is the code for the stored procedure. To create this stored, simply copy-paste into the New Query Windowthe Microsoft SLQ Server Management Studio and press F5.
In case you have missed my earlier articles on Inserting, Updating and Deleting records from the database; here is the link
- How to update record into the database?
- How to insert records into the database?
- How to retrieve data from the database in ASP.NET?
Hope this article will be of use to those who doesn't know how to use stored procedure to retrieve data from database.
Thanks for reading, stay tuned for more articles in ASP.NET.