ASP.NET Tutorial/ADO.net Database/SqlConnection — различия между версиями
Admin (обсуждение | вклад) м (1 версия) |
|
(нет различий)
|
Версия 18:30, 26 мая 2010
Содержание
Define data layer in a separate class
<source lang="csharp">
<%@ 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">
Employees
<asp:Literal runat="server" ID="HtmlContent" />
</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 += "
Inserted 1 employee.
"; WriteEmployeesList(); db.DeleteEmployee(empID); HtmlContent.Text += "
Deleted 1 employee.
"; WriteEmployeesList(); }
private void WriteEmployeesList() { StringBuilder htmlStr = new StringBuilder(""); int numEmployees = db.CountEmployees(); htmlStr.Append("htmlStr.Append("
Total employees: "); htmlStr.Append(numEmployees.ToString()); htmlStr.Append("
"); EmployeeDetails[] employees = db.GetEmployees(); foreach (EmployeeDetails emp in employees) {
} htmlStr.Append("
"); 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></source>
Displaying all provider statistics.
<source lang="csharp">
<%@ 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">
Connection Statistics
<asp:GridView id="grdStats" AutoGenerateColumns="false" Runat="server"> <Columns> <asp:BoundField DataField="Key" HeaderText="Key" /> <asp:BoundField DataField="Value" HeaderText="Value" /> </Columns> </asp:GridView>
</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>
Hard code connection string in DropDownList
<source lang="csharp">
<%@ 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"> Note: None of these actually work for this demo.
<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>
<asp:Label id="lblConnectInfo" runat="server"></asp:Label> </form> </body>
</html></source>
Improving Performance with Connection Pooling
<source lang="csharp">
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">
User Connections
<asp:GridView id="grdStats" Runat="server" />
</form>
</body> </html></source>
Retrieving Provider Statistics about the database commands executed with the connection
<source lang="csharp">
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">
<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>
Execution time was <asp:Label id="lblExecutionTime" Runat="server" /> milliseconds
</form>
</body> </html></source>