ADO.NET总结
SQL Server数据库连接。http://www.cnblogs.com/samwu/archive/2011/08/30/2159355.html
DataReader方式的插入数据行,删除数据行,查询数据行,更新数据行。
DataReader手工分页。http://www.cnblogs.com/samwu/archive/2011/06/16/2082313.html
DataReader和DataSet的比较。http://www.cnblogs.com/samwu/archive/2011/09/26/2191723.html
DataSet的基本使用。http://www.cnblogs.com/samwu/archive/2010/10/13/1850559.html
类型化DataSet。
ADO.NET使用存储过程。http://www.cnblogs.com/samwu/archive/2011/09/19/2181378.html
ADO.NET使用事务。http://www.cnblogs.com/samwu/archive/2011/08/30/2159380.html
ADO.NET连接池。http://www.cnblogs.com/waw/archive/2011/08/29/2158762.html和
http://jingyan.baidu.com/article/a3a3f811a5e2638da2eb8a02.html
SqlDataSource控件使用。
编写自己的SQLHelper类。
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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.Data.SqlClient;
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void Button1_Click(object sender, EventArgs e)
{
string sqlconnstr = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
SqlConnection sqlconn = new SqlConnection(sqlconnstr);
//建立Command对象
SqlCommand sqlcommand = new SqlCommand();
sqlcommand.Connection = sqlconn;
//把SQL语句赋给Command对象
sqlcommand.CommandText = "insert into student(no,name,sex,birth,address,photo) values (@no,@name,@sex,@birth,@address,@photo)";
sqlcommand.Parameters.AddWithValue("@no",TextBox1.Text);
sqlcommand.Parameters.AddWithValue("@name",TextBox2.Text);
sqlcommand.Parameters.AddWithValue("@sex",DropDownList1.Text);
sqlcommand.Parameters.AddWithValue("@birth",TextBox3.Text);
sqlcommand.Parameters.AddWithValue("@address",TextBox4.Text);
sqlcommand.Parameters.AddWithValue("@photo",FileUpload1.FileName);
try
{
//打开连接
sqlconn.Open();
//执行SQL命令,ExecuteNonQuery();返回值是执行的影响行数
sqlcommand.ExecuteNonQuery();
//把学生的照片上传到网站的“image”文件夹中
if (FileUpload1.HasFile == true)
{
FileUpload1.SaveAs(Server.MapPath(("~/image/") + FileUpload1.FileName));
}
Label1.Text = "成功增加记录";
}
catch (Exception ex)
{
Label1.Text = "错误原因:"+ex.Message;
}
finally
{
sqlcommand = null;
sqlconn.Close();
sqlconn = null;
}
}
}
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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.Data.SqlClient;
public partial class command : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void Button1_Click(object sender, EventArgs e)
{
int intDeleteCount;
string sqlconnstr = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
SqlConnection sqlconn = new SqlConnection(sqlconnstr);
//建立Command对象
SqlCommand sqlcommand = new SqlCommand();
//Command对象的属性赋值
sqlcommand.Connection = sqlconn;
sqlcommand.CommandText = "delete from student where no=@no";
sqlcommand.Parameters.AddWithValue("@no",TextBox1.Text);
try
{
sqlconn.Open();
intDeleteCount=sqlcommand.ExecuteNonQuery();
if (intDeleteCount>0)
Label1.Text = "成功删除记录";
else
Label1.Text = "该记录不存在";
}
catch (Exception ex)
{
Label1.Text = "错误原因:"+ex.Message;
}
finally
{
sqlcommand = null;
sqlconn.Close();
sqlconn = null;
}
}
}
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void Button1_Click(object sender, EventArgs e)
{
int intUpdateCount;
string sqlconnstr = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
SqlConnection sqlconn = new SqlConnection(sqlconnstr);
//建立Command对象
SqlCommand sqlcommand = new SqlCommand();
sqlcommand.Connection = sqlconn;
//把SQL语句赋给Command对象
sqlcommand.CommandText = "update_student";
//说明命令类型为存储过程
sqlcommand.CommandType = CommandType.StoredProcedure;
sqlcommand.Parameters.AddWithValue("@no", TextBox1.Text);
sqlcommand.Parameters.AddWithValue("@name", TextBox2.Text);
sqlcommand.Parameters.AddWithValue("@sex", DropDownList1.Text);
sqlcommand.Parameters.AddWithValue("@birth", TextBox3.Text);
sqlcommand.Parameters.AddWithValue("@address", TextBox4.Text);
sqlcommand.Parameters.AddWithValue("@photo", FileUpload1.FileName);
try
{
//打开连接
sqlconn.Open();
//执行SQL命令
intUpdateCount=sqlcommand.ExecuteNonQuery();
//把学生的照片上传到网站的“image”文件夹中
if (FileUpload1.HasFile == true)
{
FileUpload1.SaveAs(Server.MapPath(("~/image/") + FileUpload1.FileName));
}
if (intUpdateCount > 0)
Label1.Text = "成功修改记录";
else
Label1.Text = "该记录不存在";
}
catch (Exception ex)
{
Label1.Text = "错误原因:" + ex.Message;
}
finally
{
sqlcommand = null;
sqlconn.Close();
sqlconn = null;
}
}
}
从以上三个例子,可以看出,插入,删除,更新,都一个关键字,ExecuteNonQuery方法,返回的是影响行数。、
可以的话,来个ExecuteScalar 方法。
output inserted.Id values('admin','888888')";
int i = Convert.ToInt32(cmd.ExecuteScalar());
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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)
{
string sqlconnstr = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
SqlConnection sqlconn = new SqlConnection(sqlconnstr);
SqlCommand sqlcommand = new SqlCommand();
sqlcommand.Connection = sqlconn;
//打开连接
sqlconn.Open();
//建立Command对象
sqlcommand.CommandText = "select * from student";
//建立DataReader对象,返回查询结果
SqlDataReader sqldatareader=sqlcommand.ExecuteReader();
//逐行遍历查询结果
while(sqldatareader.Read())
{
Label1.Text += sqldatareader.GetString(0) + " ";
Label1.Text += sqldatareader.GetString(1) + " ";
Label1.Text += sqldatareader.GetString(2) + " ";
Label1.Text += sqldatareader.GetDateTime(3) + " ";
Label1.Text += sqldatareader.GetString(4) + " ";
Label1.Text += sqldatareader.GetString(5) + "<br />";
};
sqlcommand = null;
sqlconn.Close();
sqlconn = null;
}
}
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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.Data.SqlClient;
public partial class adapter2 : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
string sqlconnstr = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
SqlConnection sqlconn = new SqlConnection(sqlconnstr);
//建立DataSet对象
DataSet ds = new DataSet();
//建立DataTable对象
DataTable dtable;
//建立DataRowCollection对象
DataRowCollection coldrow;
//建立DataRow对象
DataRow drow;
//打开连接
sqlconn.Open();
//建立DataAdapter对象
SqlDataAdapter sqld = new SqlDataAdapter("select * from student", sqlconn);
//用Fill方法返回的数据,填充DataSet,数据表取名为“tabstudent”
sqld.Fill(ds, "tabstudent");
//将数据表tabstudent的数据复制到DataTable对象
dtable = ds.Tables["tabstudent"];
//用DataRowCollection对象获取这个数据表的所有数据行
coldrow = dtable.Rows;
//逐行遍历,取出各行的数据
for (int inti = 0; inti < coldrow.Count; inti++)
{
drow = coldrow[inti];
Label1.Text += "学号:" + drow[0];
Label1.Text += " 姓名:" + drow[1];
Label1.Text += " 性别:" + drow[2];
Label1.Text += " 出生日期:" + drow[3];
Label1.Text += " 地址:" + drow[4] + "<br />";
}
sqlconn.Close();
sqlconn = null;
}
}
遍历数据行还可以,foreach(DataRow row in dataset.Tables[0].Rows) {row["Name"]}
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void Button1_Click(object sender, EventArgs e)
{
string sqlconnstr = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
SqlConnection sqlconn = new SqlConnection(sqlconnstr);
//建立DataSet对象
DataSet ds = new DataSet();
//建立DataTable对象
DataTable dtable;
//建立DataRowCollection对象
//DataRowCollection coldrow;
//建立DataRow对象
DataRow drow;
//打开连接
sqlconn.Open();
//建立DataAdapter对象
SqlDataAdapter sqld = new SqlDataAdapter("select * from student", sqlconn);
//定义Update命令,其中@NAME,@NO是两个参数
sqld.UpdateCommand = new SqlCommand("UPDATE student SET NAME = @NAME WHERE NO = @NO", sqlconn);
//定义@NAME参数,对应于student表的NAME列
sqld.UpdateCommand.Parameters.Add("@NAME", SqlDbType.VarChar, 50, "NAME");
//定义@NO参数,对应于student表的NO列,而且@NO是修改前的原值
SqlParameter parameter = sqld.UpdateCommand.Parameters.Add("@NO", SqlDbType.VarChar, 10);
parameter.SourceColumn = "NO";
parameter.SourceVersion = DataRowVersion.Original;
//用Fill方法返回的数据,填充DataSet,数据表取名为“tabstudent”
sqld.Fill(ds, "tabstudent");
//将数据表tabstudent的数据复制到DataTable对象
dtable = ds.Tables["tabstudent"];
//用DataRowCollection对象获取这个数据表的所有数据行
//coldrow = dtable.Rows;
//修改操作,逐行遍历,取出各行的数据
for (int inti = 0; inti < dtable.Rows.Count; inti++)
{
drow = dtable.Rows[inti];
//给每位学生姓名后加上字母A
drow[1]=drow[1]+"A";
}
//提交更新
sqld.Update(ds, "tabstudent");
sqlconn.Close();
sqlconn = null;
Label1.Text = "更新成功";
}
}
这个例子是自己写的UpdateCommand方法。
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void Button1_Click(object sender, EventArgs e)
{
string sqlconnstr = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
SqlConnection sqlconn = new SqlConnection(sqlconnstr);
//建立DataSet对象
DataSet ds = new DataSet();
//建立DataTable对象
DataTable dtable;
//建立DataRow对象
DataRow drow;
//打开连接
sqlconn.Open();
//建立DataAdapter对象
SqlDataAdapter sqld = new SqlDataAdapter("select * from student", sqlconn);
//建立 CommandBuilder 对象来自动生成 DataAdapter 的 Command 命令,否则就要自己编写
//Insertcommand ,deletecommand , updatecommand 命令。
SqlCommandBuilder cb = new SqlCommandBuilder(sqld);
//用Fill方法返回的数据,填充DataSet,数据表取名为“tabstudent”
sqld.Fill(ds, "tabstudent");
//将数据表tabstudent的数据复制到DataTable对象
dtable = ds.Tables["tabstudent"];
//增加新记录
drow = ds.Tables["tabstudent"].NewRow();
//给该记录赋值
drow[0] = "19";
drow[1] = "陈峰";
drow[2] = "男";
ds.Tables["tabstudent"].Rows.Add(drow);
//提交更新
sqld.Update(ds, "tabstudent");
sqlconn.Close();
sqlconn = null;
Label1.Text = "增加成功";
}
}
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void Button1_Click(object sender, EventArgs e)
{
string sqlconnstr = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
SqlConnection sqlconn = new SqlConnection(sqlconnstr);
//建立DataSet对象
DataSet ds = new DataSet();
//建立DataTable对象
DataTable dtable;
//建立DataRowCollection对象
DataRowCollection coldrow;
//建立DataRow对象
DataRow drow;
//打开连接
sqlconn.Open();
//建立DataAdapter对象
SqlDataAdapter sqld = new SqlDataAdapter("select * from student", sqlconn);
//建立 CommandBuilder 对象来自动生成 DataAdapter 的 Command 命令,否则就要自己编写
//Insertcommand ,deletecommand , updatecommand 命令。
SqlCommandBuilder cb = new SqlCommandBuilder(sqld);
//用Fill方法返回的数据,填充DataSet,数据表取名为“tabstudent”
sqld.Fill(ds, "tabstudent");
//将数据表tabstudent的数据复制到DataTable对象
dtable = ds.Tables["tabstudent"];
//用DataRowCollection对象获取这个数据表的所有数据行
coldrow = dtable.Rows;
//逐行遍历,删除符合条件的记录
for (int inti = 0; inti < coldrow.Count; inti++)
{
drow = coldrow[inti];
if (drow["address"].ToString()== "")
drow.Delete();
}
//提交更新
sqld.Update(ds, "tabstudent");
sqlconn.Close();
sqlconn = null;
Label1.Text = "删除成功";
}
}
合乎自然而生生不息。。。