ASP.NET Tutorial/ADO.net Database/SqlConnection

Материал из .Net Framework эксперт
Версия от 11:56, 26 мая 2010; Admin (обсуждение | вклад) (1 версия)
(разн.) ← Предыдущая | Текущая версия (разн.) | Следующая → (разн.)
Перейти к: навигация, поиск

Define data layer in a separate class

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="ComponentTest" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
    <title>Untitled Page</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
    <h2>Employees</h2>
      <asp:Literal runat="server" ID="HtmlContent" />
    </div>
    </form>
</body>
</html>
File: Default.aspx.cs
using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Text;
using System.Data.SqlClient;
using System.Web.Configuration;
public partial class ComponentTest : System.Web.UI.Page
{
  private EmployeeDB db = new EmployeeDB();
  protected void Page_Load(object sender, System.EventArgs e)
  {
    WriteEmployeesList();
    int empID = db.InsertEmployee(
      new EmployeeDetails(0, "Mr.", "Bellinaso", "Marco"));
    HtmlContent.Text += "<br>Inserted 1 employee.<br>";
    WriteEmployeesList();
    db.DeleteEmployee(empID);
    HtmlContent.Text += "<br>Deleted 1 employee.<br>";
    WriteEmployeesList();
  }

  private void WriteEmployeesList()
  {
    StringBuilder htmlStr = new StringBuilder("");
    int numEmployees = db.CountEmployees();
    htmlStr.Append("<br>Total employees: <b>");
    htmlStr.Append(numEmployees.ToString());
    htmlStr.Append("</b><br><br>");
    EmployeeDetails[] employees = db.GetEmployees();
    foreach (EmployeeDetails emp in employees)
    {
      htmlStr.Append("<li>");
      htmlStr.Append(emp.EmployeeID);
      htmlStr.Append(" ");
      htmlStr.Append(emp.TitleOfCourtesy);
      htmlStr.Append(" <b>");
      htmlStr.Append(emp.FirstName);
      htmlStr.Append("</b>, ");
      htmlStr.Append(emp.LastName);
      htmlStr.Append("</li>");
    }
    htmlStr.Append("<br>");
    HtmlContent.Text += htmlStr.ToString();
  }
}
public class EmployeeDetails
{
  private int employeeID;
  private string firstName;
  private string lastName;
  private string titleOfCourtesy;
  public int EmployeeID
  {
    get {return employeeID;}
    set {employeeID = value;}
  }
  public string FirstName
  {
    get {return firstName;}
    set {firstName = value;}
  }
  public string LastName
  {
    get {return lastName;}
    set {lastName = value;}
  }
  public string TitleOfCourtesy
  {
    get {return titleOfCourtesy;}
    set {titleOfCourtesy = value;}
  }
  public EmployeeDetails(int employeeID, string firstName, string lastName,
    string titleOfCourtesy)
  {
    this.employeeID = employeeID;
    this.firstName = firstName;
    this.lastName = lastName;
    this.titleOfCourtesy = titleOfCourtesy;
  }
  public EmployeeDetails(){}
}

public class EmployeeDB
{
  private string connectionString;
  public EmployeeDB()
  {
    connectionString = WebConfigurationManager.ConnectionStrings["Northwind"].ConnectionString;
  }
  public EmployeeDB(string connectionString)
  {
    this.connectionString = connectionString;
  }
  public int InsertEmployee(EmployeeDetails emp)
  {
    SqlConnection con = new SqlConnection(connectionString);
    SqlCommand cmd = new SqlCommand("InsertEmployee", con);
    cmd.rumandType = CommandType.StoredProcedure;
    cmd.Parameters.Add(new SqlParameter("@FirstName", SqlDbType.NVarChar, 10));
    cmd.Parameters["@FirstName"].Value = emp.FirstName;
    cmd.Parameters.Add(new SqlParameter("@LastName", SqlDbType.NVarChar, 20));
    cmd.Parameters["@LastName"].Value = emp.LastName;
    cmd.Parameters.Add(new SqlParameter("@TitleOfCourtesy", SqlDbType.NVarChar, 25));
    cmd.Parameters["@TitleOfCourtesy"].Value = emp.TitleOfCourtesy;
    cmd.Parameters.Add(new SqlParameter("@EmployeeID", SqlDbType.Int, 4));
    cmd.Parameters["@EmployeeID"].Direction = ParameterDirection.Output;
  
    try 
    {
      con.Open();
      cmd.ExecuteNonQuery();
      return (int)cmd.Parameters["@EmployeeID"].Value;
    }
    catch (SqlException err) 
    {
      throw new ApplicationException("Data error.");
    }
    finally 
    {
      con.Close();      
    }
  }

  public void UpdateEmployee(EmployeeDetails emp)
  {
    SqlConnection con = new SqlConnection(connectionString);
    SqlCommand cmd = new SqlCommand("UpdateEmployee", con);
    cmd.rumandType = CommandType.StoredProcedure;
    cmd.Parameters.Add(new SqlParameter("@FirstName", SqlDbType.NVarChar, 10));
    cmd.Parameters["@FirstName"].Value = emp.FirstName;
    cmd.Parameters.Add(new SqlParameter("@LastName", SqlDbType.NVarChar, 20));
    cmd.Parameters["@LastName"].Value = emp.LastName;
    cmd.Parameters.Add(new SqlParameter("@TitleOfCourtesy", SqlDbType.NVarChar, 25));
    cmd.Parameters["@TitleOfCourtesy"].Value = emp.TitleOfCourtesy;
    cmd.Parameters.Add(new SqlParameter("@EmployeeID", SqlDbType.Int, 4));
    cmd.Parameters["@EmployeeID"].Value = emp.EmployeeID;
    try
    {
      con.Open();
      cmd.ExecuteNonQuery();
    }
    catch (SqlException err)
    {
      throw new ApplicationException("Data error.");
    }
    finally
    {
      con.Close();
    }
  }
  public void UpdateEmployee(int EmployeeID, string firstName, string lastName, string titleOfCourtesy)
  {
    SqlConnection con = new SqlConnection(connectionString);
    SqlCommand cmd = new SqlCommand("UpdateEmployee", con);
    cmd.rumandType = CommandType.StoredProcedure;
    cmd.Parameters.Add(new SqlParameter("@FirstName", SqlDbType.NVarChar, 10));
    cmd.Parameters["@FirstName"].Value = firstName;
    cmd.Parameters.Add(new SqlParameter("@LastName", SqlDbType.NVarChar, 20));
    cmd.Parameters["@LastName"].Value = lastName;
    cmd.Parameters.Add(new SqlParameter("@TitleOfCourtesy", SqlDbType.NVarChar, 25));
    cmd.Parameters["@TitleOfCourtesy"].Value = titleOfCourtesy;
    cmd.Parameters.Add(new SqlParameter("@EmployeeID", SqlDbType.Int, 4));
    cmd.Parameters["@EmployeeID"].Value = EmployeeID;
    try
    {
      con.Open();
      cmd.ExecuteNonQuery();
    }
    catch (SqlException err)
    {
      throw new ApplicationException("Data error.");
    }
    finally
    {
      con.Close();
    }
  }
  public void DeleteEmployee(int employeeID)
  {
    SqlConnection con = new SqlConnection(connectionString);
    SqlCommand cmd = new SqlCommand("DeleteEmployee", con);
    cmd.rumandType = CommandType.StoredProcedure;
    cmd.Parameters.Add(new SqlParameter("@EmployeeID", SqlDbType.Int, 4));
    cmd.Parameters["@EmployeeID"].Value = employeeID;
      
    try 
    {
      con.Open();
      cmd.ExecuteNonQuery();
    }
    catch (SqlException err) 
    {
      throw new ApplicationException("Data error.");
    }
    finally 
    {
      con.Close();      
    }
  }
  public EmployeeDetails GetEmployee(int employeeID)
  {
    SqlConnection con = new SqlConnection(connectionString);
    SqlCommand cmd = new SqlCommand("GetEmployee", con);
    cmd.rumandType = CommandType.StoredProcedure;
    cmd.Parameters.Add(new SqlParameter("@EmployeeID", SqlDbType.Int, 4));
    cmd.Parameters["@EmployeeID"].Value = employeeID;
        
    try 
    {
      con.Open();
      SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow);
      
      reader.Read();
      EmployeeDetails emp = new EmployeeDetails(
        (int)reader["EmployeeID"], (string)reader["FirstName"],
        (string)reader["LastName"], (string)reader["TitleOfCourtesy"]);
      reader.Close();
      return emp;
    }
    catch (SqlException err) 
    {
      throw new ApplicationException("Data error.");
    }
    finally 
    {
      con.Close();      
    }
  }
  public EmployeeDetails[] GetEmployees()
  {
    SqlConnection con = new SqlConnection(connectionString);
    SqlCommand cmd = new SqlCommand("GetAllEmployees", con);
    cmd.rumandType = CommandType.StoredProcedure;
        
    ArrayList employees = new ArrayList();
    try 
    {
      con.Open();
      SqlDataReader reader = cmd.ExecuteReader();
      while (reader.Read())
      {
        EmployeeDetails emp = new EmployeeDetails(
          (int)reader["EmployeeID"], (string)reader["FirstName"],
          (string)reader["LastName"], (string)reader["TitleOfCourtesy"]);
        employees.Add(emp);
      }
      reader.Close();
      
      return (EmployeeDetails[])employees.ToArray(typeof(EmployeeDetails));
    }
    catch (SqlException err) 
    {
      throw new ApplicationException("Data error.");
    }
    finally 
    {
      con.Close();      
    }
  }
      
  public int CountEmployees()
  {
    SqlConnection con = new SqlConnection(connectionString);
    SqlCommand cmd = new SqlCommand("CountEmployees", con);
    cmd.rumandType = CommandType.StoredProcedure;
        
    try 
    {
      con.Open();
      return (int)cmd.ExecuteScalar();
    }
    catch (SqlException err) 
    {
      throw new ApplicationException("Data error.");
    }
    finally 
    {
      con.Close();      
    }
  }
}
File: Web.config
<?xml version="1.0"?>
<configuration xmlns="http://schemas.microsoft.ru/.NetConfiguration/v2.0">
  <connectionStrings>
    <add name="Northwind" connectionString= "Data Source=localhost;Initial Catalog=Northwind;Integrated Security=SSPI"/>
  </connectionStrings>
  <system.web>
    <compilation debug="true"/>
    <authentication mode="Windows"/>
  </system.web>
</configuration>


Displaying all provider statistics.

<%@ Page Language="C#" %>
<%@ 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()
    {
        string connectionString = WebConfigurationManager.ConnectionStrings["Products"].ConnectionString;
        SqlConnection con = new SqlConnection(connectionString);
        SqlCommand cmd = new SqlCommand("WAITFOR DELAY "0:0:03";SELECT Title,Director FROM Products", con);
        con.StatisticsEnabled = true;
        using (con)
        {
            con.Open();
            SqlDataReader reader = cmd.ExecuteReader();
        }
        grdStats.DataSource = con.RetrieveStatistics();
        grdStats.DataBind();
    }
</script>
<html xmlns="http://www.w3.org/1999/xhtml" >
<head id="Head1" runat="server">
    <title>Show All Statistics</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
    <h1>Connection Statistics</h1>
    <asp:GridView
        id="grdStats"
        AutoGenerateColumns="false"
        Runat="server">
        <Columns>
        <asp:BoundField DataField="Key" HeaderText="Key" />
        <asp:BoundField DataField="Value" HeaderText="Value" />
        </Columns>
    </asp:GridView>
    </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>


Hard code connection string in DropDownList

<%@ Page Language="C#" %>
<%@ import Namespace="System.Data.SqlClient" %>
<script runat="server">
void cmdConnect_OnClick(Object sender, EventArgs e) 
{
    String connectString = ConnectionsList.SelectedItem.Text;
      
    SqlConnection sqlConn = null;
    try
    {      
      sqlConn = new SqlConnection(connectString);
      sqlConn.Open();
        
      lblConnectInfo.Text = "Connection successful!";
    }
    catch
    {
      lblConnectInfo.Text = "Connection failed!";
    }
    finally
    {
      if (sqlConn != null)
        sqlConn.Close();
    }        
}
</script>
<html>
  <head>
    <title>Default</title>
  </head>
  <body>
    <form id="form1" method="post" runat="server">
      <b>Note: None of these actually work for this demo.</b><br/>
      <asp:DropDownList id="ConnectionsList" runat="server">
        <asp:listitem value="logonpassword">User ID=user;Password=pass;Initial Catalog=Northwind;Data Source=(local)</asp:listitem>
        <asp:listitem value="IntegratedSecurity">Integrated Security=yes;Initial Catalog=Northwind;Data Source=(local)</asp:listitem>
      </asp:DropDownList>
      <asp:Button id="cmdConnect" onclick="cmdConnect_OnClick" runat="server" Text="Connect"></asp:Button><br />
      <asp:Label id="lblConnectInfo" runat="server"></asp:Label>
    </form>
  </body>
</html>


Improving Performance with Connection Pooling

File: ShowUserConnections.aspx
<%@ Page Language="C#" %>
<%@ 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()
    {
        string connectionString = @"Min Pool Size=10;Data Source=.\SQLExpress;Integrated Security=True;AttachDbFileName=|DataDirectory|MyDatabase.mdf;User Instance=True";
        SqlConnection con = new SqlConnection(connectionString);
        SqlCommand cmd = new SqlCommand("SELECT * FROM master..sysprocesses WHERE hostname<>""", con);
        using (con)
        {
            con.Open();
            grdStats.DataSource = cmd.ExecuteReader();
            grdStats.DataBind();
        }
    }
</script>
<html xmlns="http://www.w3.org/1999/xhtml" >
<head id="Head1" runat="server">
    <title>Show User Connections</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
    <h1>User Connections</h1>
    <asp:GridView
        id="grdStats"
        Runat="server" />
    </div>
    </form>
</body>
</html>


Retrieving Provider Statistics about the database commands executed with the connection

File: App_Code\Product.cs
using System;
using System.Data;
using System.Data.SqlClient;
using System.Web.Configuration;
using System.Collections;
using System.Collections.Generic;
public class Product
{
    private static readonly string _connectionString;
    private string _title;
    private string _director;
    public string Title
    {
        get { return _title; }
        set { _title = value; }
    }
    public string Director
    {
        get { return _director; }
        set { _director = value; }
    }
    public List<Product> GetAll(out long executionTime)
    {
        List<Product> results = new List<Product>();
        SqlConnection con = new SqlConnection(_connectionString);
        SqlCommand cmd = new SqlCommand("WAITFOR DELAY "0:0:03";SELECT Title, Director FROM Products", con);
        con.StatisticsEnabled = true;
        using (con)
        {
            con.Open();
            SqlDataReader reader = cmd.ExecuteReader();
            while (reader.Read())
            {                
                Product newProduct = new Product();
                newProduct.Title = (string)reader["Title"];
                newProduct.Director = (string)reader["Director"];
                results.Add(newProduct);
            }
        }
        IDictionary stats = con.RetrieveStatistics();
        executionTime = (long)stats["ExecutionTime"];
        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: Default.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)
    {
        lblExecutionTime.Text = e.OutputParameters["executionTime"].ToString();
    }
</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"
        Runat="server" />
    <asp:ObjectDataSource
        id="srcProducts"
        TypeName="Product"
        SelectMethod="GetAll"
        Runat="server" OnSelected="srcProducts_Selected">
        <SelectParameters>
        <asp:Parameter Name="executionTime" Type="Int64" Direction="Output" />
        </SelectParameters>
    </asp:ObjectDataSource>
    <br />
    Execution time was
    <asp:Label
        id="lblExecutionTime"
        Runat="server" />
    milliseconds
    </div>
    </form>
</body>
</html>
Источник — «http://nfex.ru/index.php?title=ASP.NET_Tutorial/ADO.net_Database/SqlConnection&oldid=2706»