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:
- Create a database named Test in SQL Server.
- Create a table named Table1, add fields and some data to the table.
- Create a stored procedure called Proc1. The Syntax is CREATE PROCEDURE Proc1 AS Select * from Table1 GO.
- Copy the StoredProc.aspx in the wwwroot folder.
- 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="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>
This was first published in November 2002