How to use the stored procedure in ADO.NET?

Sheonarayan
Posted by in ADO.NET category on for Intermediate level | Points: 250 | Views : 13846 red flag
Rating: 4 out of 5  
 2 vote(s)

In this article, we shall learn 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? 
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.

Page copy protected against web site content infringement by Copyscape

About the Author

Sheonarayan
Full Name: Sheo Narayan
Member Level: HonoraryPlatinum
Member Status: Administrator
Member Since: 7/8/2008 6:32:14 PM
Country: India
Regards, Sheo Narayan http://www.dotnetfunda.com
http://www.snarayan.com
Ex-Microsoft MVP, Author, Writer, Mentor & architecting applications since year 2001. Connect me on http://www.facebook.com/sheo.narayan | https://twitter.com/sheonarayan | http://www.linkedin.com/in/sheonarayan

Login to vote for this post.

Comments or Responses

Posted by: Tripati.24 on: 8/23/2011 | Points: 25
Nice sir...
Posted by: Vinay13mar on: 10/24/2012 | Points: 25

Login to post response

Comment using Facebook(Author doesn't get notification)