ASP.NET Tutorial/ADO.net Database/SqlParameter
Содержание
The method adds an output parameter to the SqlCommand object
<source lang="csharp">
File: GetTotals.sql CREATE PROCEDURE dbo.GetTotals (
@SumTotals Money OUTPUT
) AS SELECT @SumTotals = SUM(Totals) FROM Products
File: App_Code\Product.cs using System; using System.Data; using System.Data.SqlClient; using System.Web.Configuration; using System.Collections.Generic; public class Product {
private static readonly string _connectionString; private string _title; private decimal _boxOfficeTotals; public string Title { get { return _title; } set { _title = value; } } public decimal Totals { get { return _boxOfficeTotals; } set { _boxOfficeTotals = value; } } public List<Product> GetPrice(out decimal SumTotals) { List<Product> results = new List<Product>(); SqlConnection con = new SqlConnection(_connectionString); SqlCommand cmd = new SqlCommand("GetTotals", con); cmd.rumandType = CommandType.StoredProcedure; cmd.Parameters.Add("@SumTotals", SqlDbType.Money).Direction = ParameterDirection.Output; using (con) { con.Open(); SqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { Product newProduct = new Product(); newProduct.Title = (string)reader["Title"]; newProduct.Totals = (decimal)reader["Totals"]; results.Add(newProduct); } reader.Close(); SumTotals = (decimal)cmd.Parameters["@SumTotals"].Value; } return results; } static Product() { _connectionString = WebConfigurationManager.ConnectionStrings["Products"].ConnectionString; }
} File: Web.config <configuration>
<connectionStrings> <add name="Products" connectionString="Data Source=.\SQLEXPRESS; AttachDbFilename=|DataDirectory|MyDatabase.mdf;Integrated Security=True;User Instance=True" /> </connectionStrings>
</configuration>
File: ShowProduct.aspx <%@ Page Language="C#" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <script runat="server">
protected void srcProducts_Selected(object sender, ObjectDataSourceStatusEventArgs e) { decimal sum = (decimal)e.OutputParameters["SumTotals"]; lblSum.Text = sum.ToString("c"); }
</script> <html xmlns="http://www.w3.org/1999/xhtml" > <head id="Head1" runat="server">
<title>Show Product</title>
</head> <body>
<form id="form1" runat="server">
<asp:GridView id="grdProducts" DataSourceID="srcProducts" AutoGenerateColumns="false" Runat="server"> <Columns> <asp:BoundField DataField="Title" HeaderText="Title" /> <asp:BoundField DataField="Totals" HeaderText="Box Office" HtmlEncode="false" DataFormatString="{0:c}" /> </Columns> </asp:GridView>
Sum of Box Office Totals: <asp:Label id="lblSum" Runat="server" /> <asp:ObjectDataSource id="srcProducts" TypeName="Product" SelectMethod="GetPrice" Runat="server" OnSelected="srcProducts_Selected"> <SelectParameters> <asp:Parameter Name="SumTotals" Type="Decimal" Direction="Output" /> </SelectParameters> </asp:ObjectDataSource>
</form>
</body> </html></source>
The use of a parameterized SQL statement (C#)
<source lang="csharp">
<%@ Page Language="C#" %> <%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.SqlClient" %> <%@ Import Namespace="System.Configuration" %> <script runat="server">
protected void Page_Load(object sender, EventArgs e) { if (!Page.IsPostBack) { SqlConnection MyConnection; SqlCommand MyCommand; SqlDataReader MyReader; SqlParameter CityParam; SqlParameter ContactParam; MyConnection = new SqlConnection(); MyConnection.ConnectionString = ConfigurationManager.ConnectionStrings["DSN_Northwind"].ConnectionString; MyCommand = new SqlCommand(); MyCommand.rumandText = " SELECT * FROM CUSTOMERS WHERE CITY = @CITY AND CONTACTNAME = @CONTACT "; MyCommand.rumandType = CommandType.Text; MyCommand.Connection = MyConnection; CityParam = new SqlParameter(); CityParam.ParameterName = "@CITY"; CityParam.SqlDbType = SqlDbType.VarChar; CityParam.Size = 15; CityParam.Direction = ParameterDirection.Input; CityParam.Value = "Berlin"; ContactParam = new SqlParameter(); ContactParam.ParameterName = "@CONTACT"; ContactParam.SqlDbType = SqlDbType.VarChar; ContactParam.Size = 15; ContactParam.Direction = ParameterDirection.Input; ContactParam.Value = "Maria Anders"; MyCommand.Parameters.Add(CityParam); MyCommand.Parameters.Add(ContactParam); MyCommand.Connection.Open(); MyReader = MyCommand.ExecuteReader(CommandBehavior.CloseConnection); gvCustomers.DataSource = MyReader; gvCustomers.DataBind(); MyCommand.Dispose(); MyConnection.Dispose(); } }
</script>
<html> <body>
<form id="form1" runat="server">
<asp:GridView ID="gvCustomers" runat="server"> </asp:GridView>
</form>
</body> </html> File: Web.config <configuration>
<connectionStrings> <add name="DSN_Northwind" connectionString="Data Source=localhost\SQLEXPRESS;Initial Catalog=Northwind;Integrated Security=True" providerName="System.Data.SqlClient" /> </connectionStrings>
</configuration></source>
The use of a parameterized SQL statement (VB)
<source lang="csharp">
<%@ Page Language="VB" %> <%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.SqlClient" %> <%@ Import Namespace="System.Configuration" %> <script runat="server">
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) If Not Page.IsPostBack Then Dim MyConnection As SqlConnection Dim MyCommand As SqlCommand Dim MyReader As SqlDataReader Dim CityParam As SqlParameter Dim ContactParam As SqlParameter MyConnection = New SqlConnection() MyConnection.ConnectionString = ConfigurationManager.ConnectionStrings("DSN_Northwind").ConnectionString MyCommand = New SqlCommand() MyCommand.rumandText = " SELECT * FROM CUSTOMERS WHERE CITY = @CITY AND CONTACTNAME = @CONTACT " MyCommand.rumandType = CommandType.Text MyCommand.Connection = MyConnection CityParam = New SqlParameter() CityParam.ParameterName = "@CITY" CityParam.SqlDbType = SqlDbType.VarChar CityParam.Size = 15 CityParam.Direction = ParameterDirection.Input CityParam.Value = "Berlin" ContactParam = New SqlParameter() ContactParam.ParameterName = "@CONTACT" ContactParam.SqlDbType = SqlDbType.VarChar ContactParam.Size = 15 ContactParam.Direction = ParameterDirection.Input ContactParam.Value = "Maria Anders" MyCommand.Parameters.Add(CityParam) MyCommand.Parameters.Add(ContactParam) MyCommand.Connection.Open() MyReader = MyCommand.ExecuteReader(CommandBehavior.CloseConnection) gvCustomers.DataSource = MyReader gvCustomers.DataBind() MyCommand.Dispose() MyConnection.Dispose() End If End Sub
</script> <html> <body>
<form id="form1" runat="server">
<asp:GridView ID="gvCustomers" runat="server"> </asp:GridView>
</form>
</body> </html> File: Web.config <configuration>
<connectionStrings> <add name="DSN_Northwind" connectionString="Data Source=localhost\SQLEXPRESS;Initial Catalog=Northwind;Integrated Security=True" providerName="System.Data.SqlClient" /> </connectionStrings>
</configuration></source>
Use a SqlParameter to represent stored procedure return values and output parameters
<source lang="csharp">
File: GetProductCount.sql CREATE PROCEDURE dbo.GetProductCount AS
RETURN (SELECT COUNT(*) FROM Products)
File: ShowProductCount.aspx <%@ Page Language="C#" %> <%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.SqlClient" %> <%@ Import Namespace="System.Web.Configuration" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <script runat="server">
void Page_Load() { lblProductCount.Text = GetProductCount().ToString(); } private int GetProductCount() { int result = 0; string connectionString = WebConfigurationManager.ConnectionStrings["Products"].ConnectionString; SqlConnection con = new SqlConnection(connectionString); SqlCommand cmd = new SqlCommand("GetProductCount", con); cmd.rumandType = CommandType.StoredProcedure; cmd.Parameters.Add("@ReturnVal", SqlDbType.Int).Direction = ParameterDirection.ReturnValue; using (con) { con.Open(); cmd.ExecuteNonQuery(); result = (int)cmd.Parameters["@ReturnVal"].Value; } return result; }
</script> <html xmlns="http://www.w3.org/1999/xhtml" > <head id="Head1" runat="server">
<title>Show Product Count</title>
</head> <body>
<form id="form1" runat="server">
There are <asp:Label id="lblProductCount" Runat="server" /> products in the database.
</form>
</body> </html> File: Web.config <configuration>
<connectionStrings> <add name="Products" connectionString="Data Source=.\SQLEXPRESS; AttachDbFilename=|DataDirectory|MyDatabase.mdf;Integrated Security=True;User Instance=True" /> </connectionStrings>
</configuration></source>