Calling a stored procedure using ADO.NET

Using stored procedures brings speed/momentum to programs. This example executes a stored procedure on a SQL Server database.

Let other users know how useful this tip is by rating it below. Got a tip or code of your own you'd like to share? Submit it here!

A stored procedure is code that is to be executed on the server in one single stroke. Using stored procedures introduces speed and momentum to programs. The example below executes a stored procedure on the SQL Server database. After understanding this example, you can try calling a Oracle stored procedure as well.

I have used ADO namespace to connect to the SQL Server database. If you want to use SQL namespace, simply change ADO with SQL in:


Steps to run this example:


  1. Create a database named Test in SQL Server.


  2. Create a table named Table1, add fields and some data to the table.


  3. Create a stored procedure called Proc1. The Syntax is CREATE PROCEDURE Proc1 AS Select * from Table1 GO.
  4. Copy the StoredProc.aspx in the wwwroot folder.
  5. Run http://localhost/StoredProc.aspx in the browser.

Here, the stored procedure returns a set of records that will be displayed in the grid when the page load event handler gets called.


 <%@ import namespace=""%> <%@ import namespace=""%> <html> <head> <script language="vb" runat="server"> sub page_load(sender as object, e as eventargs) dim con as adoconnection dim cmd as adodatasetcommand dim ds as new dataset dim str as string dim sql as string str="Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Initial Catalog=Test;Data Source=LocalHost;" sql="Proc1" con= new adoconnection(str) cmd= new adodatasetcommand(sql,con) cmd.SelectCommand.CommandType= CommandType.StoredProcedure cmd.filldataset(ds,"Proc1") datagrid1.datasource=ds.tables("proc1").DefaultView datagrid1.databind() end sub </script> </head> <asp:datagrid id="datagrid1" runat="server"/> </html>


This was first published in November 2002



Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: