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

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

Using a Built-in Stored Procedure With Parameters

<%@ 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" />
      <br/><br/>
      <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>


<A href="http://www.nfex.ru/Code/ASPDownload/Northwind.zip">Northwind.zip( 736 k)</a>


Using Stored Procedures With Output Parameters

<%@ 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" />
      <br/><br/>
      <asp:label id="lblRecords" runat="server" />
      <br/><br/>
      <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



Using Stored Procedures With Parameters: filter

<%@ 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" />
      <br/><br/>
      <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>
Источник — «http://nfex.ru/index.php?title=ASP.Net/ADO.net_Database/Stored_Procedure_Parameters&oldid=1679»