в 15:30, 26 мая 2010

2010-05-26T15:30:57Z

Admin: 1 версия

2010-05-26T11:56:51Z

1 версия

Новая страница

== 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">
<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></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">
<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></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">
<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></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">
<div>
<h1>User Connections</h1>
<asp:GridView
id="grdStats"
Runat="server" />
</div>
</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">
<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></source>

← Предыдущая	Версия 15:30, 26 мая 2010
(нет различий)

ASP.NET Tutorial/ADO.net Database/SqlConnection - История изменений

в 15:30, 26 мая 2010

Admin: 1 версия