使用DataAdapter对象和DataSet对象实现断开连接的数据访问步骤
一、查询数据库的步骤
1.创建数据适配器对象
SqlDataAdapter da = new SqlDataAdapter();
2.创建数据库连接对象和命令对象
SqlConnection conn = new SqlConnection();
conn.ConnectionString = "Data Source=PC-2010;Initial Catalog=BookShop;User ID=sa;Password=123456";
SqlCommand selectCmd=new SqlCommand();
selectCmd.CommandText = "select * from Books";
selectCmd.Connection = conn;
SqlConnection conn = new SqlConnection();
conn.ConnectionString = "Data Source=PC-2010;Initial Catalog=BookShop;User ID=sa;Password=123456";
SqlCommand selectCmd=new SqlCommand();
selectCmd.CommandText = "select * from Books";
selectCmd.Connection = conn;
3.设置数据适配器的命令属性
da.SelectCommand = selectCmd;
da.SelectCommand = selectCmd;
4.创建数据集对象
DataSet data = new DataSet();
DataSet data = new DataSet();
5.调用数据适配器的Fill填充方法
da.Fill(data,"Books");
da.Fill(data,"Books");
6.将数据显示在网页上
gvCheckAll.DataSource=data.Tables["Books"];
gvCheckAll.DataBind();
gvCheckAll.DataSource=data.Tables["Books"];
gvCheckAll.DataBind();
二、向数据库中添加记录的步骤
1.创建数据适配器对象
SqlDataAdapter da = new SqlDataAdapter();
1.创建数据适配器对象
SqlDataAdapter da = new SqlDataAdapter();
2.创建数据库连接对象
SqlConnection conn = new SqlConnection();
conn.ConnectionString = "Data Source=PC-2010;Initial Catalog=BookShop;User ID=sa;Password=123456";
SqlConnection conn = new SqlConnection();
conn.ConnectionString = "Data Source=PC-2010;Initial Catalog=BookShop;User ID=sa;Password=123456";
3.创建查询命令对象
SqlCommand selectCmd = new SqlCommand();
selectCmd.CommandText = "select * from Books";
selectCmd.Connection = conn;
SqlCommand selectCmd = new SqlCommand();
selectCmd.CommandText = "select * from Books";
selectCmd.Connection = conn;
4.创建添加数据命令的对象
SqlCommand insertCmd = new SqlCommand();
insertCmd.CommandText = "insert into Books values(@BookName,@Author,@Publisher,@UnitPrice,@Discount,@BookImageFileName)";
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.NVarChar, 10, "UnitPrice");
insertCmd.Parameters.Add("@Discount", SqlDbType.NVarChar, 8, "Discount");
insertCmd.Parameters.Add("@BookImageFileName", SqlDbType.NVarChar, 50, "BookImageFileName");
insertCmd.Connection = conn;
SqlCommand insertCmd = new SqlCommand();
insertCmd.CommandText = "insert into Books values(@BookName,@Author,@Publisher,@UnitPrice,@Discount,@BookImageFileName)";
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.NVarChar, 10, "UnitPrice");
insertCmd.Parameters.Add("@Discount", SqlDbType.NVarChar, 8, "Discount");
insertCmd.Parameters.Add("@BookImageFileName", SqlDbType.NVarChar, 50, "BookImageFileName");
insertCmd.Connection = conn;
5.将命令对象关联到数据适配器
da.SelectCommand = selectCmd;
da.InsertCommand = insertCmd;
da.SelectCommand = selectCmd;
da.InsertCommand = insertCmd;
6.创建数据集对象
DataSet data = new DataSet();
DataSet data = new DataSet();
7.用SqlDataAdapter的Fill方法填充数据集
da.Fill(data,"Books");
da.Fill(data,"Books");
8.
创建一个具有data.Tables["Books"]框架的行对象,并向其中添加数据
DataRow drNew = data.Tables["Books"].NewRow();
drNew["BookName"] = txtName.Text;
drNew["Author"] = txtAuthor.Text;
drNew[3] = txtPublisher.Text;
drNew[4] = txtUnitPrice.Text;
drNew[5] = txtDiscout.Text;
drNew[6] = txtImage.Text;
DataRow drNew = data.Tables["Books"].NewRow();
drNew["BookName"] = txtName.Text;
drNew["Author"] = txtAuthor.Text;
drNew[3] = txtPublisher.Text;
drNew[4] = txtUnitPrice.Text;
drNew[5] = txtDiscout.Text;
drNew[6] = txtImage.Text;
9.向表中添加行
data.Tables["Books"].Rows.Add(drNew);
data.Tables["Books"].Rows.Add(drNew);
10.将数据通过数据适配器更新到数据库中
da.Update(data,"Books");
da.Update(data,"Books");
三、修改数据库中的数据
1.创建数据适配器对象
SqlDataAdapter da = new SqlDataAdapter();
1.创建数据适配器对象
SqlDataAdapter da = new SqlDataAdapter();
2.创建数据库连接对象
SqlConnection conn = new SqlConnection();
conn.ConnectionString = "Data Source=PC-2010;Initial Catalog=BookShop;User ID=sa;Password=123456";
SqlConnection conn = new SqlConnection();
conn.ConnectionString = "Data Source=PC-2010;Initial Catalog=BookShop;User ID=sa;Password=123456";
3.创建查询命令对象
SqlCommand selectCmd = new SqlCommand();
selectCmd.CommandText = "select * from Books";
selectCmd.Connection = conn;
SqlCommand selectCmd = new SqlCommand();
selectCmd.CommandText = "select * from Books";
selectCmd.Connection = conn;
4.创建修改数据的命令对象
SqlCommand editCmd = new SqlCommand();
editCmd.CommandText = "update Books set Discount=@Discount where BookID=@BookID ";
editCmd.Parameters.Add("@BookID", SqlDbType.Int, 4, "BookID");
editCmd.Parameters.Add("@Discount", SqlDbType.NVarChar, 8, "Discount");
editCmd.Connection = conn;
SqlCommand editCmd = new SqlCommand();
editCmd.CommandText = "update Books set Discount=@Discount where BookID=@BookID ";
editCmd.Parameters.Add("@BookID", SqlDbType.Int, 4, "BookID");
editCmd.Parameters.Add("@Discount", SqlDbType.NVarChar, 8, "Discount");
editCmd.Connection = conn;
5.将命令对象关联到数据适配器
da.SelectCommand = selectCmd;
da.UpdateCommand = editCmd;
da.SelectCommand = selectCmd;
da.UpdateCommand = editCmd;
6.创建数据集对象
DataSet data = new DataSet();
DataSet data = new DataSet();
7.用SqlDataAdapter的Fill方法填充数据集
da.Fill(data, "Books");
da.Fill(data, "Books");
8.查找要修改的数据所在的数据行
方法1:
int nBookID = int.Parse(txtBookId.Text);
DataRow editDr = null;
foreach(DataRow dr in data.Tables["Books"].Rows)
{
if (int.Parse(dr[0].ToString()) == nBookID)
{
editDr = dr;
break;
}
}
方法2:
data.Tables["Books"].PrimaryKey = new DataColumn[] { data.Tables["Books"].Columns["BookID"] };
DataRow editDr = data.Tables["Books"].Rows.Find(int.Parse(txtBookId.Text));
if (editDr == null)
{
return;
}
方法1:
int nBookID = int.Parse(txtBookId.Text);
DataRow editDr = null;
foreach(DataRow dr in data.Tables["Books"].Rows)
{
if (int.Parse(dr[0].ToString()) == nBookID)
{
editDr = dr;
break;
}
}
方法2:
data.Tables["Books"].PrimaryKey = new DataColumn[] { data.Tables["Books"].Columns["BookID"] };
DataRow editDr = data.Tables["Books"].Rows.Find(int.Parse(txtBookId.Text));
if (editDr == null)
{
return;
}
方法3:
DataRow[] drSelect = data.Tables["Books"].Select("BookID="+txtBookId.Text.Trim());
DataRow editDr;
if(drSelect.Length>0)
{
editDr = drSelect[0];
}
else
{
return;
}
DataRow[] drSelect = data.Tables["Books"].Select("BookID="+txtBookId.Text.Trim());
DataRow editDr;
if(drSelect.Length>0)
{
editDr = drSelect[0];
}
else
{
return;
}
9.找到要修改的数据所在的行后修改其对应字段的值
editDr["Discount"] = txtNewDiscout.Text;
editDr["Discount"] = txtNewDiscout.Text;
10.将数据通过数据适配器更新到数据库中
da.Update(data,"Books");
da.Update(data,"Books");
四、删除数据库中的数据
1.创建数据适配器对象
SqlDataAdapter da = new SqlDataAdapter();
1.创建数据适配器对象
SqlDataAdapter da = new SqlDataAdapter();
2.创建数据库连接对象
SqlConnection conn = new SqlConnection();
conn.ConnectionString = "Data Source=PC-2010;Initial Catalog=BookShop;User ID=sa;Password=123456";
SqlConnection conn = new SqlConnection();
conn.ConnectionString = "Data Source=PC-2010;Initial Catalog=BookShop;User ID=sa;Password=123456";
3.创建查询命令对象
SqlCommand selectCmd = new SqlCommand();
selectCmd.CommandText = "select * from Books";
selectCmd.Connection = conn;
SqlCommand selectCmd = new SqlCommand();
selectCmd.CommandText = "select * from Books";
selectCmd.Connection = conn;
4.创建删除数据的命令对象
SqlCommand delCmd = new SqlCommand();
delCmd.CommandText = "delete Books where BookID=@BookID ";
delCmd.Parameters.Add("@BookID", SqlDbType.Int, 4, "BookID");
delCmd.Connection = conn;
SqlCommand delCmd = new SqlCommand();
delCmd.CommandText = "delete Books where BookID=@BookID ";
delCmd.Parameters.Add("@BookID", SqlDbType.Int, 4, "BookID");
delCmd.Connection = conn;
5.将命令对象关联到数据适配器
da.SelectCommand = selectCmd;
da.DeleteCommand = delCmd;
da.SelectCommand = selectCmd;
da.DeleteCommand = delCmd;
6.创建数据集对象
DataSet data = new DataSet();
DataSet data = new DataSet();
7.用SqlDataAdapter的Fill方法填充数据集
da.Fill(data, "Books");
da.Fill(data, "Books");
8.查找要删除的数据所在的数据行
int nBookID = int.Parse(txtDelete.Text);
DataRow delDr = null;
foreach(DataRow dr in data.Tables["Books"].Rows)
{
if(int.Parse(dr[0].ToString())==nBookID)
{
delDr = dr;
break;
}
}
int nBookID = int.Parse(txtDelete.Text);
DataRow delDr = null;
foreach(DataRow dr in data.Tables["Books"].Rows)
{
if(int.Parse(dr[0].ToString())==nBookID)
{
delDr = dr;
break;
}
}
9.找到要删除的数据所在的行后将其删除
delDr.Delete();
delDr.Delete();
10.将数据通过数据适配器更新到数据库中
da.Update(data,"Books");
da.Update(data,"Books");
注:
(一)
DataAdapter有4个命令属性
(1)SelectCommand
(2)InsertCommand
(3)UpdateCommand
(4)DeleteCommand
(二)
数据适配器Fill方法用于使用DataAdapter的SelectCommand的结果来填充DataSet,
但如果我们通过代码在DataSet里已经定义好了表的架构,就可以不用再用SelectCommand从数据库中读取数据来填充数据集
数据适配器Fill方法用于使用DataAdapter的SelectCommand的结果来填充DataSet,
但如果我们通过代码在DataSet里已经定义好了表的架构,就可以不用再用SelectCommand从数据库中读取数据来填充数据集
(三)
用DataSet操作数据库有两大步骤
(1)更改DataSet中的数据
(2)将DataSet中的数据更新到数据库
用DataSet操作数据库有两大步骤
(1)更改DataSet中的数据
(2)将DataSet中的数据更新到数据库
(四)
find是数据行Rows的方法
select是数据表Tables["Books"]的方法
数据表的select方法用于查询满足条件的行,返回行数组
(五)find是数据行Rows的方法
select是数据表Tables["Books"]的方法
数据表的select方法用于查询满足条件的行,返回行数组
ADO.NET两种数据访问模型
1.使用Connection对象、Command对象、DataReader对象实现保持连接的数据访问
2.使用DataAdapter对象和DataSet对象实现断开连接的数据访问
(六)
SqlCommand.Parameters.Add(参数)里面参数的含义
insertCmd.Parameters.Add("@BookName", SqlDbType.NVarChar, 50, "BookName");
第四个参数"BookName"表示的是数据集里新添加数据的"BookName"字段对应的值
insertCmd.Parameters.Add("@BookName", SqlDbType.NVarChar, 50, "BookName");
第四个参数"BookName"表示的是数据集里新添加数据的"BookName"字段对应的值
editCmd.Parameters.Add("@BookID", SqlDbType.Int, 4, "BookID");
第四个参数"BookID"表示在数据集里面对数据所做修改的那一行"BookID"字段所对应的值
第四个参数"BookID"表示在数据集里面对数据所做修改的那一行"BookID"字段所对应的值