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:

thecodeandalsoremembertochangethestr="Server=LocalHost;uid=sa;pwd=;database=test"

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.

StoredProc.aspx

 <%@ import namespace="system.data"%> <%@ import namespace="system.data.ado"%> <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>

Source: DotNetExtreme.com

This was first published in November 2002

0 comments

Oldest 

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:

-ADS BY GOOGLE

SearchCloudComputing

SearchSoftwareQuality

SearchSOA

TheServerSide

SearchCloudApplications

Close