ADO.NET介绍
1.Connection对象
代码示例：
<%@ Page Language="C#" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<%@ Import Namespace="System.Data" %>
<!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 Page_Load(object sender, EventArgs e)
{
SqlConnection connection = new SqlConnection("Data Source=PANNING-PC;Initial Catalog=AspNetStudy;Persist Security Info=True;User ID=sa;Password=as");
Response.Write("时间" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "当前数据库连接状态是：" + connection.State + "<br/>");
connection.Open();
Response.Write("时间" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "当前数据库连接状态是：" + connection.State + "<br/>");
connection.Close();
Response.Write("时间" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "当前数据库连接状态是：" + connection.State + "<br/>");
}
</script>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>无标题页</title>
</head>
<body>
<form id="form1" runat="server">
<div>
</div>
</form>
</body>
</html>
2.Command对象
代码示例：
<%@ Page Language="C#" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<%@ Import Namespace="System.Data" %>
<!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 Page_Load(object sender, EventArgs e)
{
//实例化Connection对象
SqlConnection connection = new SqlConnection("Data Source=PANNING-PC;Initial Catalog=AspNetStudy;Persist Security Info=True;User ID=sa;Password=as");
//实例化Command对象
SqlCommand command = new SqlCommand("select count(1) as 男性人数 from UserInfo where sex = 1", connection);
//打开Connection对象
connection.Open();
//执行SQL语句
int count = int.Parse(command.ExecuteScalar().ToString());
//关闭Connection对象
connection.Close();
Response.Write("在UserInfo表里共有" + count + "个男性。");
}
</script>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>无标题页</title>
</head>
<body>
<form id="form1" runat="server">
<div>
</div>
</form>
</body>
</html>
3.DataReader对象
代码示例：
<%@ Page Language="C#" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Text" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<script runat="server">
public void ShowData()
{
//实例化Connection对象
SqlConnection connection = new SqlConnection("Data Source=PANNING-PC;Initial Catalog=AspNetStudy;Persist Security Info=True;User ID=sa;Password=as");
//实例化Command对象
SqlCommand command = new SqlCommand("select * from UserInfo where sex = 1", connection);
//打开Connection对象
connection.Open();
//得到DataReader的实例
//注意：使用了CommandBehavior这个参数以便同时关闭Connection
SqlDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection);
while (reader.Read())
{
Response.Write("<tr><td>" + reader.GetInt32(0) + "</td>");
Response.Write("<td>" + reader.GetString(1) + "</td>");
Response.Write("<td>" + reader.GetString(2) + "</td>");
Response.Write("<td>" + reader.GetByte(3) + "</td>");
Response.Write("<td>" + (reader.GetBoolean(4) == true ? "男" : "女") + "</td>");
Response.Write("<td>" + reader[5].ToString() + "</td>");
Response.Write("<td>" + reader["Phone"] + "</td>");
Response.Write("<td>" + reader["Email"].ToString() + "</td></tr>");
}
reader.Close();
}
</script>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>无标题页</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<table border="1" cellpadding="0" cellspacing="0">
<tr>
<td>编号</td>
<td>账号</td>
<td>真实姓名</td>
<td>年龄</td>
<td>性别</td>
<td>手机</td>
<td>电话</td>
<td>电子邮件</td>
</tr>
<%
ShowData();
%>
</table>
</div>
</form>
</body>
</html>
4.DataAdapter和DataSet对象
前台代码示例：
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="DataAdapter.aspx.cs" Inherits="DataAdapter" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>无标题页</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<table border="1" cellpadding="0" cellspacing="0">
<tr>
<td>编号</td>
<td>账号</td>
<td>真实姓名</td>
<td>年龄</td>
<td>性别</td>
<td>手机</td>
<td>电话</td>
<td>电子邮件</td>
</tr>
<%
ShowData();
%>
</table>
</div>
</form>
</body>
</html>
using System.Data.SqlClient;
public partial class DataAdapter : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void ShowData()
{
//实例化Connection对象
SqlConnection connection = new SqlConnection("Data Source=PANNING-PC;Initial Catalog=AspNetStudy;Persist Security Info=True;User ID=sa;Password=as");
//实例化Command对象
SqlCommand command = new SqlCommand("select * from UserInfo where sex = 0", connection);
SqlDataAdapter adapter = new SqlDataAdapter(command);
DataTable data = new DataTable();
adapter.Fill(data);
for (int i = 0; i < data.Rows.Count; i++)
{
Response.Write("<tr><td>" + data.Rows[i]["UserId"].ToString() + "</td>");
Response.Write("<td>" + data.Rows[i]["UserName"].ToString() + "</td>");
Response.Write("<td>" + data.Rows[i]["RealName"].ToString() + "</td>");
Response.Write("<td>" + data.Rows[i]["Age"].ToString() + "</td>");
Response.Write("<td>" + (bool.Parse(data.Rows[i]["Sex"].ToString()) == true ? "男" : "女") + "</td>");
Response.Write("<td>" + data.Rows[i]["Mobile"].ToString() + "</td>");
Response.Write("<td>" + data.Rows[i]["Phone"].ToString() + "</td>");
Response.Write("<td>" + data.Rows[i]["Email"].ToString() + "</td></tr>");
}
}
}
5.DataTable对象
代码示例：
<%@ Page Language="C#" %>
<%@ Import Namespace="System.Data" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<script runat="server">
private void CreateDataTable()
{
//实例化DataTable
DataTable data = new DataTable();
//创建列
DataColumn dc1 = new DataColumn("ID", typeof(int));
dc1.AllowDBNull = false; //不允许空
dc1.AutoIncrement = true; //自动递增
dc1.AutoIncrementSeed = 1; //列起始值为1
dc1.AutoIncrementStep = 1; //步长为1
data.Columns.Add(dc1); //添加列到表中
DataColumn dc = new DataColumn("UserName", typeof(string));
dc.Unique = true; //设置唯一索引
dc.MaxLength = 20; //设置字段最大长度
data.Columns.Add(dc);
dc = new DataColumn("Birthday", typeof(DateTime));
dc.DefaultValue = DateTime.Now.AddYears(-2000);
data.Columns.Add(dc);
DataRow row = data.NewRow(); //得到刚创建的表有相同结构的行
row["UserName"] = "张飞";
data.Rows.Add(row);
row = data.NewRow();
row["UserName"] = "刘备";
row["Birthday"] = new DateTime(1, 3, 4);
data.Rows.Add(row);
row = data.NewRow();
row["UserName"] = "关羽";
row["Birthday"] = new DateTime(6, 11, 12);
data.Rows.Add(row);
Session["Data"] = data;
}
protected void ShowData()
{
if (Session["Data"] == null)
{
CreateDataTable();
}
DataTable data = (DataTable)Session["Data"];
for (int i=0;i<data.Rows.Count;i++)
{
Response.Write("<tr><td>" + data.Rows[i]["ID"].ToString() + "</td>");
Response.Write("<td>" + data.Rows[i]["UserName"].ToString() + "</td>");
Response.Write("<td>" + DateTime.Parse(data.Rows[i]["Birthday"].ToString()) + "</td></tr>");
}
}
</script>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>无标题页</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<table border="1" cellpadding="0" cellspacing="0">
<tr>
<td>编号</td>
<td>姓名</td>
<td>生日</td>
</tr>
<%
ShowData();
%>
</table>
</div>
</form>
</body>
</html>
6.参数化SQL语句
代码示例：
SqlCommand command = new SqlCommand("select * from UserInfo where age > @age", connection);
SqlParameter parameter = new SqlParameter("@age", SqlDbType.Int);
parameter.Value = 20;
command.Parameters.Add(parameter);
7.分页查询SQL语句
如果每页显示5条记录，那么第n页显示数据记录的语句应该是
select top 5 * from UserInfo where UserId not in (select top (n-1)*5 UserId from UserInfo
order by UserId asc) order by UserId asc
如果总共有m条记录，每页显示n条记录，那么全部记录所用到的页数为
page=(m%n)==0?(m/n):(m/n+1);
