在本篇文章中,我将自己手动写代码完成利用DataAdapter进行数据访问操作,以便能让初学DataAdapter的朋友明白它背后运行的一些情况。在此,我以SqlServer2005Express数据库作为数据源服务器,因此需要用到的是SqlDataAdapter数据访问对象,对于其它的OleDbDataAdapter等对象与此是类似的,也希望这篇文章对于初学者能起到抛砖引玉的微薄作用吧。首先在SqlServer2005Express里建立数据库作为数据源,关于此过程的实现可以通过数据定义语句或直接利用可视化界面来完成。我们将通过dataGridView控件来显示数据,详细的代码如下所示:
public partial class Form3 : Form
{
private SqlConnection myCon;
private SqlDataAdapter myAda;
private SqlCommand myCmd;
private SqlCommandBuilder myCbd;
private DataSet mySet;
public Form3()
{
InitializeComponent();
myCon = new SqlConnection("Data Source=localhost\SQLEXPRESS;Initial Catalog=Shop;Persist Security Info=True;User ID=sa;Password= ");
myAda = new SqlDataAdapter();
myCmd = new SqlCommand("select * from Userinfo",myCon);
myAda.SelectCommand = myCmd;
myCbd = new SqlCommandBuilder(myAda);
mySet = new DataSet();
myAda.TableMappings.Add("UserInfo", "UserInfo");
myAda.TableMappings[0].ColumnMappings.Add("UserID", "用户");
myAda.TableMappings[0].ColumnMappings.Add("UserName", "用户姓名");
myAda.TableMappings[0].ColumnMappings.Add("UserAge", "年龄");
myAda.TableMappings[0].ColumnMappings.Add("UserSex", "性别");
myAda.TableMappings[0].ColumnMappings.Add("UserAddress", "地址");
}
private void Form3_Load(object sender, EventArgs e)
{
try
{
myAda.Fill(mySet, "UserInfo");
}
catch (SqlException ex)
{
MessageBox.Show(ex.ToString());
}
finally
{
myCon.Close();
}
dataGridView1.DataSource = mySet.Tables["UserInfo"];
}
private void bt_Update_Click(object sender, EventArgs e)
{
try
{
//将更改的数据更新到数据表里
myAda.Update(mySet.Tables[0].GetChanges());
MessageBox.Show("数据更新成功!");
//DataTable接受更改,以便为下一次更改作准备
mySet.Tables[0].AcceptChanges();
}
catch (SqlException ex)
{
ex.ToString();
}
}
private void bt_Delete_Click(object sender, EventArgs e)
{
//从DataTable中删除当前选中的行
mySet.Tables[0].Rows[dataGridView1.CurrentRow.Index].Delete();
if (MessageBox.Show("确定要删除当前行数据?", "", MessageBoxButtons.OKCancel) == DialogResult.OK)
{
try