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

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

Содержание

1 Define data layer in a separate class
2 Displaying all provider statistics.
3 Hard code connection string in DropDownList
4 Improving Performance with Connection Pooling
5 Retrieving Provider Statistics about the database commands executed with the connection

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>

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

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

Содержание

Define data layer in a separate class

Displaying all provider statistics.

Hard code connection string in DropDownList

Improving Performance with Connection Pooling

Retrieving Provider Statistics about the database commands executed with the connection

Навигация

Персональные инструменты

Пространства имён

Варианты

Просмотры

Ещё

Поиск

Разделы

Инструменты

Навигация

Текущая версия на 11:56, 26 мая 2010 (просмотреть исходный код) Admin (обсуждение \| вклад) м (1 версия)	Версия 15:30, 26 мая 2010 (просмотреть исходный код) (обсуждение) Следующая правка →
(нет различий)