ASP.Net/ADO.net Database/Stored Procedure Parameters

Материал из .Net Framework эксперт
Перейти к: навигация, поиск

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>