Command对象
1、ExecuteReader方法
ExecuteReader方法将返回一个DataReader对象,DataReader对象是一个仅向前的只读数据流。主要用来执行基本SQL查询语句,要求SQL语句返回记录集。
添加DataGird控件
2、ExecuteScalar方法
使用ExecuteScalar方法返回单个值,用来执行聚合函数。
3、ExecuteNonQuary方法
ExecuteNonQuary方法用于执行不需要返回结果的命令。
4、ADO.NET事务处理
事务使一些事件的集合,执行一条SQL语句可以理解成一个事件。事务中包含多个事件,当每一个事件都能执行成功的时候,事务才执行;如果有任何一个事件不能成功执行,事务的其他事件也不被执行。
ExecuteReader方法将返回一个DataReader对象,DataReader对象是一个仅向前的只读数据流。主要用来执行基本SQL查询语句,要求SQL语句返回记录集。
添加DataGird控件
using System;
using System.Data;
using System.Configuration;
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.Data.SqlClient;
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
SqlConnection Conn = new SqlConnection();
Conn.ConnectionString = "server=localhost;database=pubs;uid=sa;pwd=''";
Conn.Open();
SqlCommand Comm = new SqlCommand("select * from Authors", Conn);
SqlDataReader dr = Comm.ExecuteReader();
dg.DataSource = dr;
dg.DataBind();
Conn.Close();
}
}
也可以带参数查询using System.Data;
using System.Configuration;
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.Data.SqlClient;
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
SqlConnection Conn = new SqlConnection();
Conn.ConnectionString = "server=localhost;database=pubs;uid=sa;pwd=''";
Conn.Open();
SqlCommand Comm = new SqlCommand("select * from Authors", Conn);
SqlDataReader dr = Comm.ExecuteReader();
dg.DataSource = dr;
dg.DataBind();
Conn.Close();
}
}
protected void Page_Load(object sender, EventArgs e)
{
SqlConnection Conn = new SqlConnection();
Conn.ConnectionString = "server=localhost;database=pubs;uid=sa;pwd=''";
Conn.Open();
string sql = "select * from Authors where state='CA'";
SqlCommand Comm = new SqlCommand(sql, Conn);
SqlDataReader dr = Comm.ExecuteReader();
dg.DataSource = dr;
dg.DataBind();
Conn.Close();
}
{
SqlConnection Conn = new SqlConnection();
Conn.ConnectionString = "server=localhost;database=pubs;uid=sa;pwd=''";
Conn.Open();
string sql = "select * from Authors where state='CA'";
SqlCommand Comm = new SqlCommand(sql, Conn);
SqlDataReader dr = Comm.ExecuteReader();
dg.DataSource = dr;
dg.DataBind();
Conn.Close();
}
2、ExecuteScalar方法
使用ExecuteScalar方法返回单个值,用来执行聚合函数。
protected void Page_Load(object sender, EventArgs e)
{
OleDbConnection conn = new OleDbConnection();
conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=" + Server.MapPath("person.mdb");
conn.Open();
string strSQL = "select avg(数学) from grade";
OleDbCommand Comm = new OleDbCommand(strSQL, conn);
double d = (double)Comm.ExecuteScalar();
Message.Text = "所有人数学的平均成绩为:"+d.ToString()+"分";
conn.Close();
}
{
OleDbConnection conn = new OleDbConnection();
conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=" + Server.MapPath("person.mdb");
conn.Open();
string strSQL = "select avg(数学) from grade";
OleDbCommand Comm = new OleDbCommand(strSQL, conn);
double d = (double)Comm.ExecuteScalar();
Message.Text = "所有人数学的平均成绩为:"+d.ToString()+"分";
conn.Close();
}
3、ExecuteNonQuary方法
ExecuteNonQuary方法用于执行不需要返回结果的命令。
protected void btnInsert_Click(object sender, EventArgs e)
{
OleDbConnection conn = new OleDbConnection();
conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=" + Server.MapPath("person.mdb");
conn.Open();
string strSQL = "insert into grade values(12,'女','小张',78,86,98)";
OleDbCommand Comm = new OleDbCommand(strSQL, conn);
Comm.ExecuteNonQuery();
conn.Close();
}
{
OleDbConnection conn = new OleDbConnection();
conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=" + Server.MapPath("person.mdb");
conn.Open();
string strSQL = "insert into grade values(12,'女','小张',78,86,98)";
OleDbCommand Comm = new OleDbCommand(strSQL, conn);
Comm.ExecuteNonQuery();
conn.Close();
}
4、ADO.NET事务处理
事务使一些事件的集合,执行一条SQL语句可以理解成一个事件。事务中包含多个事件,当每一个事件都能执行成功的时候,事务才执行;如果有任何一个事件不能成功执行,事务的其他事件也不被执行。
protected void btnTrans_Click(object sender, EventArgs e)
{
OleDbConnection Conn = new OleDbConnection();
Conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=" + Server.MapPath("person.mdb");
Conn.Open();
OleDbCommand Comm = new OleDbCommand();
OleDbTransaction Trans;
Trans = Conn.BeginTransaction();
Comm.Connection = Conn;
Comm.Transaction = Trans;
try
{
Comm.CommandText = "update grade set 数学=100 where 姓名 like '%齐%'";
Comm.ExecuteNonQuery();
Comm.CommandText = "update grade set 数学=60 where 姓名 like '%张%'";
Comm.ExecuteNonQuery();
Trans.Commit();
Response.Write("事务执行成功!");
}
catch (Exception ex)
{
Trans.Rollback();
Response.Write("出现错误,事务已经回滚!");
}
finally
{
Conn.Close();
}
}
{
OleDbConnection Conn = new OleDbConnection();
Conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=" + Server.MapPath("person.mdb");
Conn.Open();
OleDbCommand Comm = new OleDbCommand();
OleDbTransaction Trans;
Trans = Conn.BeginTransaction();
Comm.Connection = Conn;
Comm.Transaction = Trans;
try
{
Comm.CommandText = "update grade set 数学=100 where 姓名 like '%齐%'";
Comm.ExecuteNonQuery();
Comm.CommandText = "update grade set 数学=60 where 姓名 like '%张%'";
Comm.ExecuteNonQuery();
Trans.Commit();
Response.Write("事务执行成功!");
}
catch (Exception ex)
{
Trans.Rollback();
Response.Write("出现错误,事务已经回滚!");
}
finally
{
Conn.Close();
}
}