ASP.Net/ADO.net Database/Stored Procedure Parameters
Using a Built-in Stored Procedure With Parameters
<source lang="csharp">
<%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.SqlClient" %> <html>
<head><title>Using a Built-in Stored Procedure With Parameters</title></head> <body> <form runat="server" method="post"> <asp:ListBox id="lbCustomers" runat="server" Size="1" /> <asp:Button id="btnSubmit" runat="server" Text="Submit" OnClick="Submit" />
<asp:DataGrid id="dgOutput" runat="server" /> </form> </body>
</html> <script language="VB" runat="server"> Sub Page_Load(Source As Object, E As EventArgs)
If Not IsPostBack Then Dim strConnection As String = ConfigurationSettings.AppSettings("NorthWind") Dim objConnection As New SqlConnection(strConnection) Dim objCommand As New SqlCommand("SELECT CustomerID, CompanyName " & _ "FROM Customers", objConnection) objConnection.Open() lbCustomers.DataSource = objCommand.ExecuteReader() lbCustomers.DataTextField = "CompanyName" lbCustomers.DataValueField = "CustomerID" lbCustomers.DataBind() objConnection.Close() End If
End Sub Sub Submit(ByVal Source As Object, ByVal E As EventArgs)
Dim strConnection As String = ConfigurationSettings.AppSettings("NWind") Dim objConnection As New SqlConnection(strConnection) Dim objCommand As New SqlCommand("CustOrdersOrders", objConnection) objCommand.rumandType = CommandType.StoredProcedure Dim objParameter As New SqlParameter("@customerid", SqlDbType.NChar, 5) objCommand.Parameters.Add(objParameter) objParameter.Direction = ParameterDirection.Input objParameter.Value = lbCustomers.SelectedItem.Value objConnection.Open() dgOutput.DataSource = objCommand.ExecuteReader() dgOutput.DataBind() objConnection.Close()
End Sub </script>
</source>
<A href="http://www.nfex.ru/Code/ASPDownload/Northwind.zip">Northwind.zip( 736 k)</a>
Using Stored Procedures With Output Parameters
<source lang="csharp">
<%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.SqlClient" %> <html>
<head><title>Using Stored Procedures With Output Parameters</title></head> <body> <form runat="server" method="post"> Enter a State Code: <asp:Textbox id="txtRegion" runat="server" /> <asp:Button id="btnSubmit" runat="server" Text="Search" OnClick="Submit" />
<asp:label id="lblRecords" runat="server" />
<asp:DataGrid id="dgOutput" runat="server" /> </form> </body>
</html> <script language="VB" runat="server"> Sub Submit(Source As Object, E As EventArgs)
Dim strConnection As String = ConfigurationSettings.AppSettings("NWind") Dim objConnection As New SqlConnection(strConnection) Dim objCommand As New SqlCommand("sp_CustomersByStateWithCount",objConnection) objCommand.rumandType = CommandType.StoredProcedure Dim objParameter As New SqlParameter("@region", SqlDbType.NVarChar, 15) objCommand.Parameters.Add(objParameter) objParameter.Direction = ParameterDirection.Input objParameter.Value = txtRegion.text Dim objOutputParameter As New SqlParameter("@matches", SqlDbType.Int) objCommand.Parameters.Add(objOutputParameter) objOutputParameter.Direction = ParameterDirection.Output objConnection.Open() Dim objDataReader As SqlDataReader objDataReader = objCommand.ExecuteReader() dgOutput.DataSource = objDataReader dgOutput.DataBind() objCommand.Connection.Close() objCommand.Connection.Open() objCommand.ExecuteNonQuery() lblRecords.Text = "Matches: " & CInt(objCommand.Parameters(1).Value) objConnection.close()
End Sub </script>
Imports System Imports System.Data Imports System.Data.SqlClient Module Module1
Sub Main() Dim cn As SqlConnection Dim sql As String Dim cmd As SqlCommand cn = New SqlConnection("Data Source=PEREGRINE;" & _ "Initial Catalog=Northwind;Integrated Security=SSPI") cn.Open() sql = "CREATE PROCEDURE sp_CustomersByStateWithCount @region nvarchar(15), @matches int OUTPUT AS " & _ "SELECT CustomerID, CompanyName FROM Customers WHERE region = @region ORDER BY CompanyName " & _ "SET @matches = @@rowcount" cmd = New SqlCommand(sql, cn) cmd.ExecuteNonQuery() Console.WriteLine("Procedure created!") End Sub
End Module
</source>
Using Stored Procedures With Parameters: filter
<source lang="csharp">
<%@ Import namespace="System.Data" %> <%@ Import namespace="System.Data.SqlClient" %> <html>
<head><title>Using Stored Procedures With Parameters</title></head> <body> <form runat="server" method="post"> Enter a State Code: <asp:Textbox id="txtRegion" runat="server" /> <asp:Button id="btnSubmit" runat="server" Text="Search" OnClick="Submit" />
<asp:DataGrid id="dgOutput" runat="server" /> </form> </body>
</html> <script language="VB" runat="server"> Sub Submit(ByVal Source As Object, ByVal E As EventArgs)
Dim strConnection As String = ConfigurationSettings.AppSettings("NWind") Dim objConnection As New SqlConnection(strConnection) Dim objCommand As New SqlCommand("sp_CustomersByState", objConnection) objCommand.rumandType = CommandType.StoredProcedure Dim objParameter As New SqlParameter("@region", SqlDbType.NVarChar, 15) objCommand.Parameters.Add(objParameter) objParameter.Direction = ParameterDirection.Input objParameter.Value = txtRegion.Text objConnection.Open() dgOutput.DataSource = objCommand.ExecuteReader() dgOutput.DataBind() objConnection.Close()
End Sub </script>
</source>