How to use the stored procedure in ADO.NET?

Introduction

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)

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.

CODE BEHIND

string _connStr = ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString;
 
   protected void Page_Load(object sender, EventArgs e)
   {
       int autoId = 6;
       this.FetchRecords(autoId);
   }
 
  /// <summary>
  /// Fetches the records.
  /// </summary>
  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;
 
            cmd.Parameters.Add(prm);
           // 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
              ad.Fill(table);
           }
           // 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.

STORED PROCEDURE

CREATE PROCEDURE [dbo].[GetRecords]
      -- Add the parameters for the stored procedure here
      @AutoId int
AS
BEGIN
      -- 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
END

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.

OUTPUT

In case you have missed my earlier articles on Inserting, Updating and Deleting records from the database; here is the link

1. How to update record into the database?
2. How to insert records into the database?
3. How to retrieve data from the database in ASP.NET?