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:
- 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.
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
