C#连接数据库并使用datatable进行统一操作

1. C#连接数据库需要用到Connection、command、Reader对象。典型的建立数据库访问流程如下图所示。（这里使用DataReader）进行测试。

sqlserver的链接字符串：

// 1. 连接数据库的URL，@的意思就是后面所有的字符都不进行转义。
public static string CON_URL = @"Provider=SQLOLEDB;Data Source=hkjhk65\MYSQLSERVER;Integrated Security=SSPI;Initial Catalog=master";

  // 1. 建立链接对象，存在方法重载，在建立链接的时候可以指定链接串。
    OleDbConnection conn = new OleDbConnection(ProjectConst.CON_URL);

    // 2. 打开数据库系统连接
    conn.Open();

    // 3. 创建sql语句。
    string sql = "select * from useraccount where username = ? and userpassword = ?";

    // 4. 获得命令对象，类似于java中的statement；
    OleDbCommand cmd = new OleDbCommand(sql, conn);

   // 5. 设置我们的预定义参数, 我们这里使用一个数组的方式进行处理。
    OleDbParameter para1 = new OleDbParameter();
    para1.Value = tbUser.Text;

    OleDbParameter para2 = new OleDbParameter();
    para2.Value = tbPwd.Text;


    // 6. 把我们的参数数组添加到cmd对象中。
    cmd.Parameters.Add(para1);
    cmd.Parameters.Add(para2);


    // 7. 执行命令获取Reader;
    OleDbDataReader reader = cmd.ExecuteReader();

    // 8 .读取返回的数据Result.hasNext();
    while (reader.Read())
    {
        // 通过索引器的方式来进行一个取值。
        reader["username"]
    }

2. 使用DataTable、DataSet的方式。经常用于dataGridView的数据绑定。

OleDbConnection conn = new OleDbConnection(ProjectConst.CON_URL);

// 在C#中，可以对同时执行多条sql语句。
string sql = "select * from menu;select * from tpStu;select * from useraccount";
// 创建命令对象。
OleDbCommand cmd = new OleDbCommand(sql, conn);

OleDbDataAdapter adapter = new OleDbDataAdapter();
adapter.SelectCommand = cmd;
// 创建DataSet，dataset.tables就是一个数组，数组的每一项就是一个sql 的查询数据。
DataSet ds = new DataSet();
// 进行数据的填充。
adapter.Fill(ds);
// 可以吧DataGridView的属性绑定为dataset。
dataGridView1.DataSource = ds.Tables[0];
dataGridView2.DataSource = ds.Tables[0];

数据绑定的流程图，中间需要使用一个BindingSource进行中间转换。并不要把datatable的数据直接绑定到我们DataGridView之中。因为我们的数据全部是在内存中，如果直接使用DataGridView的datasource对datatable进行绑定的时候，当我们的光标定位到一个DataGridView的时候，会影响另外DataGridView的值。

DataGridView进行数据绑定的代码：

DataTable dt = new DataTable();
// 数据库操作，单表放入我们的DataTable中。
adapter.Fill(dt);
BindingSource bs = new BindingSource();
// 建立DataView和DataTable的关系。
DataView dv = dt.DefaultView;
// 建立BindingSource和DataView的关系;
bs.DataSource = dv;
// 建立DataGridVieW和BindingSourced的关系。
dataGridView1.DataSource = bs;

3. 使用DataGridView的自身属性进行数据的增删查改。

基础设施（适合放在FormBase中）：
获得我们DataGridView中变化的数据

public DataTable GetChangesByGrid(DataGridView dgv, DataRowState drs)
{
    DataTable dt = null;
    // 类型判断
    if (dgv.DataSource is BindingSource)
    {
        // 类型判断
        BindingSource bs = dgv.DataSource as BindingSource;
        if (bs.DataSource is DataView)
        {
            DataView dv = bs.DataSource as DataView;
            // 取到我们真正的DataTable（基于内存），并获取到修改的数据。
            dt = dv.Table.GetChanges(drs);
        }
    }
    return dt;
}

根据DataGridView ，获取对应的DataTable：

public DataTable GetDataTableByGrid(DataGridView dgv)
{
    DataTable dt = null;
    if (dgv.DataSource is BindingSource)
    {
        BindingSource bs = dgv.DataSource as BindingSource;
        if (bs.DataSource is DataView)
        {
            DataView dv = bs.DataSource as DataView;
            dt = dv.Table;
        }
    }
    return dt;
}

根据DataGridView ，获取对应的BindingSource：

public BindingSource GetBsByGrid(DataGridView dgv)
{
    BindingSource bs = null;
    if (dgv.DataSource is BindingSource)
    {
        bs = dgv.DataSource as BindingSource;
    }
    return bs;
}

为什么需要这么多的中间对象？
DataTable：基于内存，可以导出不同的DataView。
针对于不同的DataView，我们不能直接操纵里面的值，所以我们添加了BindingSource这个对象。对数据表格的数据源进行一个绑定。
我们的DataView是实时变化的，我们在进行信息保存的时候，如果要显示数据的操作，那么我们可以进行如下的数据表格的绑定（基于datatable新建一个视图，在内存中，实时变化。）：

 dv = new DataView(GetDataTableByGrid(dataGridView1), null, null
    ,DataViewRowState.Added 
    | DataViewRowState.ModifiedCurrent 
    | DataViewRowState.Deleted);
bs = new BindingSource();
bs.DataSource = dv;
dataGridView2.DataSource = bs;

界面展示：上面展示我们原始的数据，下面是基于datatable建立的视图为数据源的数据，可以动态的展示我们修改，删除，添加的数据。

因为数据是基于内存的，所以直接可以在原表上进行修改，然后统一提交到数据库。（这一点是Java做不到的）。
复制行的实现：

BindingSource bs = GetBsByGrid(dataGridView1);
if (bs != null)
{
    // 获得行视图。
    DataRowView drv = (DataRowView)bs.Current;
    // 获得DataTable，然后添加一个行（行的数据就是当前行的数据）。
    drv.Row.Table.ImportRow(drv.Row);
    // 我们的指针定位到添加行的位置，提升用户体验度。
    bs.Position = bs.Count - 1;
    // 获取添加的行；
    drv = (DataRowView)bs.Current;
    // 设置行状态为增加，默认为修改。
    drv.Row.SetAdded();
}

数据的保存：
处理删除的逻辑（删除表的原始数据在DataRowVersion.Original中，依旧使用索引器的方式进行体现。）：

 string sql = "delete from student where sno = ?";
//处理删除
DataTable dtDeleted = GetChangesByGrid(dataGridView1, DataRowState.Deleted);
if (dtDeleted != null)
{
    foreach (DataRow dr in dtDeleted.Rows)
    {
        dbHelper.Execute(sql, dr["sno", DataRowVersion.Original]);
    }
}

处理修改的数据（一定要注意，索引器默认是当前新数据，添加了DataRowVersion.Original的才是原先的数据，所以一定要进行声明）：
//处理更新数据

sql = "update student set sno = ?, sname = ?, age = ?, dept = ?, sex = ? where sno = ?";
DataTable dtUpdated = GetChangesByGrid(dataGridView1, DataRowState.Modified);
if (dtUpdated != null)
{
    foreach (DataRow dr in dtUpdated.Rows)
    {
        dbHelper.Execute(sql, dr["sno"], dr["sname"], dr["age"], dr["dept"], dr["sex"], dr["sno", DataRowVersion.Original]);
    }
}

处理插入的数据：

 sql = "insert into student (sname, sno, age, dept, sex) values (?, ?, ?, ?, ?)";
DataTable dtInserted = GetChangesByGrid(dataGridView1, DataRowState.Added);
if (dtInserted != null)
{
    foreach (DataRow dr in dtInserted.Rows)
    {
        dbHelper.Execute(sql, dr["sname"], dr["sno"], dr["age"], dr["dept"], dr["sex"]);
    }
}

更新完成需要进行DataTable的数据更新：

DataTable dt = GetDataTableByGrid(dataGridView1);
dt.AcceptChanges();
MessageBox.Show("恭喜您，数据保存成功！");