ADO.NET 4 快速上手（5）——弱类型DataSet（代码创建）

五、弱类型DataSet（代码创建）

1.    本地DataSet

A.    在内在中存储数据，修改立即生效

DataTable customersTable = new DataTable("Customers");
customersTable.Columns.Add("ID", typeof(int));
customersTable.Columns.Add("Name", typeof(string));
            
DataRow oneRow = customersTable.NewRow();

oneRow["ID"] = 0;
oneRow[1] = "Jack";

DataColumn column = customersTable.Columns[0];
oneRow[column] = 1;

customersTable.Rows.Add(oneRow);
customersTable.Rows.Add(new object[] {2, "Fred"});

oneRow.BeginEdit();
oneRow[0] = 3;  // 没有改变，仍为1
oneRow.CancelEdit(); // 恢复到BeginEdit时的值

foreach (DataRow row in customersTable.Rows)
{
    Console.WriteLine(row[0] + "    " + row["Name"]);
}

B.    在内在中存储数据，修改批量生效

DataTable customersTable = new DataTable("Customers");
// do something to customersTable
customersTable.AcceptChanges(); // 提交所有行的更改
customersTable.RejectChanges(); // 拒绝最后一次提交后的更改

立即生效，马上验证数据有效性：

DataTable customersTable = new DataTable("Customers");
            
DataColumn column = customersTable.Columns.Add("Name", typeof(string));
column.MaxLength = 3;

DataRow oneRow = customersTable.NewRow();
customersTable.Rows.Add(oneRow);
oneRow["Name"] = "Jack123456"; // 这行抛异常，字符串超长

下面代码说明，行的BeginEdit()到EndEdit()之间，对行数据的修改，若导致数据不完整或违反其他规则，不抛出异常，也不直接修改表中数据。此间可以通过CancelEdit()回滚修改。行的EndEdit()被调用，才批量验证数据有效性。但修改仍未生效。

直到调用行或表级别上的AcceptChanges()时，数据才被批量提交。在表、行级别上调用AcceptChanges()，自动执行EndEdit()；调用RejectChanges()时，自动调用CancelEdit()。

DataTable customersTable = new DataTable("Customers");
            
DataColumn column = customersTable.Columns.Add("Name", typeof(string));
column.MaxLength = 3;

DataRow oneRow = customersTable.NewRow();
customersTable.Rows.Add(oneRow);
oneRow.BeginEdit();
oneRow["Name"] = "Jack123456"; // 这行不抛异常
oneRow.EndEdit(); // 这行抛异常，字符串超长
customersTable.AcceptChanges();

C.    DataSet

DataTable customersTable = new DataTable("Customers");
DataSet dataSet = new DataSet();
dataSet.Tables.Add(customersTable);

D.    聚合计算

DataTable customersTable = new DataTable("Customers");
customersTable.Columns.Add("Nmae", typeof(string));
customersTable.Columns.Add("Salary", typeof(float));

customersTable.Rows.Add(new object[] { "Jack", 8000 });
customersTable.Rows.Add(new object[] { "Tommy", 3500 });

float result = (float)customersTable.Compute("Sum(Salary)", "");
Console.WriteLine(result);

2.    数据业务规则验证

DataTable customersTable = new DataTable("Customers");
// 验证事件
customersTable.ColumnChanging += (sender, e) => {
    if (e.Column.ColumnName == "Age")
    {
        // ProposedValue：已被更改后的值，但未生效
        if ((int)e.ProposedValue < 0 || (int)e.ProposedValue > 100)
        {
            e.Row.SetColumnError(e.Column, "年龄超限");
        }
    }
};

DataColumn column = customersTable.Columns.Add("Age", typeof(int));
            
DataRow oneRow = customersTable.NewRow();
customersTable.Rows.Add(oneRow);

oneRow.ClearErrors();
oneRow.BeginEdit();
oneRow["Age"] = 105;
// 行级别监视
if (oneRow.HasErrors)
{
    // show error
    oneRow.CancelEdit();
}
else
{
    oneRow.EndEdit();
}

// 表级别监视
if (customersTable.HasErrors)
{
    DataRow[] errorRows = customersTable.GetErrors();
    // show error
    string errorRowText = errorRows[0].GetColumnError(0);
    Console.WriteLine(errorRowText);

    customersTable.RejectChanges();
}
else
{
    customersTable.AcceptChanges();
}

3.    数据提取

A.    主键搜索

DataTable customersTable = new DataTable("Customers");
            
DataColumn column = customersTable.Columns.Add("ID", typeof(int));
customersTable.Columns.Add("name", typeof(string));

// 只能在主键上搜索
customersTable.PrimaryKey = new DataColumn[] { column };

customersTable.Rows.Add(new object[] {1, "Jack"});
            
DataRow row = customersTable.Rows.Find(1);
Console.WriteLine(row[1]);

B.    搜索条件

DataTable customersTable = new DataTable("Customers");
            
customersTable.Columns.Add("ID", typeof(int));
DataColumn column = customersTable.Columns.Add("name", typeof(string));

customersTable.Rows.Add(new object[] {100, "Jack"});

DataRow[] rows = customersTable.Select("name = 'Jack'");
if (rows.Length > 0)
{
    Console.WriteLine(rows[0][0]);
}

4.    DataView

DataTable包含实际的DataRow实例，DataView不包含DataRow实例，而包含了引用实际DataRow索引，指向各行。

DataTable employeesTable = new DataTable("Customers");
employeesTable.Columns.Add("Nmae", typeof(string));
employeesTable.Columns.Add("Salary", typeof(float));

employeesTable.Rows.Add(new object[] { "Jack", 8000 });
employeesTable.Rows.Add(new object[] { "Tommy", 3500 });

DataView view = new DataView(employeesTable);
view.RowFilter = "Salary > 0";
view.Sort = "Salary";

Console.WriteLine(view[0][0]);
Console.WriteLine(view[1][0]);

5.    读取外部数据

从外存（数据库）载入数据到内存（DataSet、DataTable）。

DataTable userTable = new DataTable();
DataSet dataSet = new DataSet();
using (SqlConnection conn = new SqlConnection(
    "Data Source=localhost;Initial Catalog=SettingKnowledgeManagement;Integrated Security=True"))
{
    SqlDataAdapter adapter = new SqlDataAdapter("select * from tb_user", conn);
                
    // 或：
    //SqlDataAdapter adapter2 = new SqlDataAdapter();
    //adapter2.SelectCommand = new SqlCommand("select * from user", conn);

    // Fill方法会自动打开数据库连接，完成后自动关闭
    adapter.Fill(userTable);
    adapter.Fill(dataSet, "userTable");
}

6.    数据更新

A.    将数据存回数据库

DataTable userTable = new DataTable();
using (SqlConnection conn = new SqlConnection(
    "Data Source=localhost;Initial Catalog=SettingKnowledgeManagement;Integrated Security=True"))
{
    SqlDataAdapter adapter = new SqlDataAdapter();
    SqlCommand cmd = new SqlCommand("select * from tb_user", conn);
    adapter.SelectCommand = cmd;

    adapter.Fill(userTable);

    cmd = new SqlCommand("insert into tb_user values(@username, @password)", conn);
                
    // 返回自动编号需加此：
    // cmd = new SqlCommand("insert into tb_user values(@username, @password); set @ID = @@IDENTITY;", conn);

    cmd.Parameters.Add("@username", SqlDbType.VarChar, 255, "your name");
    cmd.Parameters.Add("@password", SqlDbType.VarChar, 255, "your password");

    // 返回自动编号需加此：
    //SqlParameter returnParam = cmd.Parameters.Add("@ID", SqlDbType.BigInt, 0, "id");
    //returnParam.Direction = ParameterDirection.Output;

    adapter.InsertCommand = cmd;

    cmd = new SqlCommand("update tb_user set password = @password where username = @username", conn);
    cmd.Parameters.Add("@username", SqlDbType.VarChar, 255, "username");
    cmd.Parameters.Add("@password", SqlDbType.VarChar, 255, "password");
    adapter.UpdateCommand = cmd;

    cmd = new SqlCommand("delete from tb_user where username = @username", conn);
    cmd.Parameters.Add("@username", SqlDbType.VarChar, 255, "username");
    adapter.DeleteCommand = cmd;

    DataRow[] rows = userTable.Select("username = 'your name'");
    if (rows.Length > 0)
    {
        rows[0][1] = "your new password";
    }

    // 更新表到数据库
adapter.Update(userTable);
userTable.AcceptChanges();
}

B.    自动生成更新命令

注意：只能生成单表查询，不能有关联表；表必须有主键或有唯一约束；列名不能包含空格。

DataTable userTable = new DataTable();
using (SqlConnection conn = new SqlConnection(
    "Data Source=localhost;Initial Catalog=SettingKnowledgeManagement;Integrated Security=True"))
{
    SqlDataAdapter adapter = new SqlDataAdapter();
    // 指定select
    adapter.SelectCommand = new SqlCommand("select * from tb_user", conn);

    // 自动生成insert, update, delete命令，与select对应
    SqlCommandBuilder builder = new SqlCommandBuilder(adapter);

    adapter.Fill(userTable);
    userTable.Rows.Add(new object[] {"Jack", "my password"});
    adapter.Update(userTable);
    userTable.AcceptChanges();
}