в 15:30, 26 мая 2010

2010-05-26T15:30:47Z

Admin: 1 версия

2010-05-26T11:51:08Z

1 версия

Новая страница

==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" />
<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>

</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" />
<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

</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" />
<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>

</source>

← Предыдущая	Версия 15:30, 26 мая 2010
(нет различий)

ASP.Net/ADO.net Database/Stored Procedure Parameters - История изменений

в 15:30, 26 мая 2010

Admin: 1 версия