使用数据集和数据适配器对数据库增删改查
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.Data.SqlClient;//导入命名空间
public partial class chap09_DataAccessDemo : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void btnQuery_Click(object sender, EventArgs e)
{
SqlDataAdapter da = new SqlDataAdapter();
//创建连接对象
SqlConnection conn = new SqlConnection();
conn.ConnectionString = "Data Source=LIYONGPING;Initial Catalog=BookShop;User ID=sa;Password=2008aozheng@))^";
//创建命令对象
SqlCommand selectCmd = new SqlCommand();
selectCmd.CommandText = "select * from Books";
selectCmd.Connection = conn;
//设置数据适配器的SelectCommand属性
da.SelectCommand = selectCmd;
//创建数据集对象
DataSet data = new DataSet();
//使用数据适配器填充数据集
da.Fill(data, "Books");
//将数据显示在页面的GridView控件上
GridView1.DataSource = data.Tables["Books"];
GridView1.DataBind();
}
protected void btnAdd_Click(object sender, EventArgs e)
{
//第一步验证数据……
//
SqlDataAdapter da = new SqlDataAdapter();
//创建连接对象
SqlConnection conn = new SqlConnection();
conn.ConnectionString = "Data Source=LIYONGPING;Initial Catalog=BookShop;User ID=sa;Password=2008aozheng@))^";
//创建查询命令对象
SqlCommand selectCmd = new SqlCommand();
selectCmd.CommandText = "select * from Books";
selectCmd.Connection = conn;
//创建添加数据的命令对象
SqlCommand insertCmd = new SqlCommand();
insertCmd.CommandText = "insert into Books values(@BookName,@Author,@Publisher,@UnitPrice,@Discount,@BookImageFileName)";
insertCmd.Connection = conn;
//向插入命令添加参数
/*
SqlParameter bookNameParam = new SqlParameter("@BookName", SqlDbType.NVarChar, 50);
bookNameParam.SourceColumn = "BookName";
insertCmd.Parameters.Add(bookNameParam);
*/
insertCmd.Parameters.Add("@BookName",SqlDbType.NVarChar, 50, "BookName");
insertCmd.Parameters.Add("@Author", SqlDbType.NVarChar, 50, "Author");
insertCmd.Parameters.Add("@Publisher", SqlDbType.NVarChar,50, "Publisher");
insertCmd.Parameters.Add("@UnitPrice", SqlDbType.Money, 8, "UnitPrice");
insertCmd.Parameters.Add("@Discount", SqlDbType.NVarChar, 8, "Discount");
insertCmd.Parameters.Add("@BookImageFileName", SqlDbType.NVarChar, 50, "BookImageFileName");
//设置数据适配器的SelectCommand属性/InsertCommand
da.SelectCommand = selectCmd;
da.InsertCommand = insertCmd;
//创建数据集对象
DataSet data = new DataSet();
//使用数据适配器填充数据适配器
da.Fill(data, "Books");
//向DataSet的“Books”表中添加一条记录
DataRow drNew=data.Tables["Books"].NewRow();
//设置新添加行的值
drNew["BookName"] = txtBookName.Text;
drNew["Author"] = txtAuthor.Text;
drNew[3] = txtPublishName.Text;
drNew[4] = txtPrice.Text;
drNew[5] = txtDiscount.Text;
drNew[6] = txtFileName.Text;
//向表中添加行
data.Tables["Books"].Rows.Add(drNew);
//将数据通过数据适配器更新到数据库中
da.Update(data, "Books");
}
protected void btnEdit_Click(object sender, EventArgs e)
{
//第一步验证数据……
//
SqlDataAdapter da = new SqlDataAdapter();
//创建连接对象
SqlConnection conn = new SqlConnection();
conn.ConnectionString = "Data Source=LIYONGPING;Initial Catalog=BookShop;User ID=sa;Password=2008aozheng@))^";
//创建查询命令对象
SqlCommand selectCmd = new SqlCommand();
selectCmd.CommandText = "select * from Books";
selectCmd.Connection = conn;
//创建修改数据的命令对象
SqlCommand editCmd = new SqlCommand();
editCmd.CommandText = "update Books set Discount=@Discount where BookID=@BookID";
editCmd.Connection = conn;
//向修改数据命令添加参数
editCmd.Parameters.Add("@BookID", SqlDbType.Int, 4, "BookID");
editCmd.Parameters.Add("@Discount", SqlDbType.Float, 8, "Discount");
//editCmd.Parameters["@BookID"].SourceVersion = DataRowVersion.Original;
//设置数据适配器的SelectCommand属性/UpdateCommand属性
da.SelectCommand = selectCmd;
da.UpdateCommand = editCmd;
//创建数据集对象
DataSet data = new DataSet();
//使用数据适配器填充数据适配器
da.Fill(data, "Books");
//查找需要更改的行
DataRow[] drSelect=data.Tables["Books"].Select("BookID="+txtBookIDForEdit.Text.Trim());
DataRow editDr;
if (drSelect.Length > 0)
{
editDr = drSelect[0];
}
else
{
return;
}
/*
data.Tables["Books"].PrimaryKey = new DataColumn[] { data.Tables["Books"].Columns["BookID"] };
DataRow editDr=data.Tables["Books"].Rows.Find(int.Parse(txtBookIDForEdit.Text));
if (editDr == null)
return;
*/
/*
int nBookID = int.Parse(txtBookIDForEdit.Text);
DataRow editDr = null;
foreach (DataRow dr in data.Tables["Books"].Rows)
{
if (int.Parse(dr["BookID"].ToString()) == nBookID)
{
editDr = dr;
break;
}
}
*/
editDr["Discount"] = txtNewDiscount.Text;
//将数据通过数据适配器更新到数据库中
da.Update(data, "Books");
}
protected void btnDel_Click(object sender, EventArgs e)
{
//第一步验证数据……
//
SqlDataAdapter da = new SqlDataAdapter();
//创建连接对象
SqlConnection conn = new SqlConnection();
conn.ConnectionString = "Data Source=LIYONGPING;Initial Catalog=BookShop;User ID=sa;Password=2008aozheng@))^";
//创建查询命令对象
SqlCommand selectCmd = new SqlCommand();
selectCmd.CommandText = "select * from Books";
selectCmd.Connection = conn;
//创建删除数据的命令对象
SqlCommand delCmd = new SqlCommand();
delCmd.CommandText = "delete Books where BookID=@BookID";
delCmd.Connection = conn;
//向删除数据命令添加参数
delCmd.Parameters.Add("@BookID", SqlDbType.Int, 4, "BookID");
//editCmd.Parameters["@BookID"].SourceVersion = DataRowVersion.Original;
//设置数据适配器的SelectCommand属性/DeleteCommand属性
da.SelectCommand = selectCmd;
da.DeleteCommand = delCmd;
//创建数据集对象
DataSet data = new DataSet();
//使用数据适配器填充数据适配器
da.Fill(data, "Books");
int nBookID = int.Parse(txtBookIDForDel.Text);
DataRow delDr=null;
foreach (DataRow dr in data.Tables["Books"].Rows)
{
if (int.Parse(dr["BookID"].ToString()) == nBookID)
{
delDr = dr;
break;
}
}
if (delDr != null)
{
delDr.Delete();//从数据集中删除改行
//将数据通过数据适配器更新到数据库中
da.Update(data, "Books");
}
}
}
