ASP.NET Tutorial/ADO.net Database/SqlConnection
Содержание
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("
Total employees: ");
htmlStr.Append(numEmployees.ToString());
htmlStr.Append("
");
EmployeeDetails[] employees = db.GetEmployees();
foreach (EmployeeDetails emp in employees)
{
htmlStr.Append(" }
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>
