ASP.NET Tutorial/ADO.net Database/SqlParameter — различия между версиями

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

Версия 15:30, 26 мая 2010

The method adds an output parameter to the SqlCommand object

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">
    <div>
    <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>
    <br />
    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>
    </div>
    </form>
</body>
</html>


The use of a parameterized SQL statement (C#)

<%@ 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">
    <div>
        <asp:GridView ID="gvCustomers" runat="server">
        </asp:GridView>    
    </div>
    </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>


The use of a parameterized SQL statement (VB)

<%@ 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">
    <div>
        <asp:GridView ID="gvCustomers" runat="server">
        </asp:GridView>    
    </div>
    </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>


Use a SqlParameter to represent stored procedure return values and output parameters

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">
    <div>
    There are
    <asp:Label
        id="lblProductCount"
        Runat="server" />
    products in the database.
    </div>
    </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>
Источник — «http://nfex.ru/index.php?title=ASP.NET_Tutorial/ADO.net_Database/SqlParameter&oldid=2717»