第五天 -- 《2014-07-11 ADO》1 --DataAdapter检索、DataTable遍历、DataAdapter更新数据的限制、DataAdapter和参数化查询

一、上午《01、第一个断开式的数据库操作》--《03、手动创建数据集》

1、手动创建数据集DataSet对象

//1.内存中创建数据集--相当于数据库。所以说可以建立多个DataTable对象添加到DataSet对象中。
DataSet ds = new DataSet();

//2.创建数据表--名字为grade
DataTable dt = new DataTable("grade");

//3.1 创建好表的架构--结构
DataColumn c1=new DataColumn("classId",  typeof(int));//创建一个列，列名为classId
c1.AutoIncrement = true;//设置为标识列
c1.AutoIncrementSeed = 1;//标识种子
c1.AutoIncrementStep = 1;//标识增量

//3.2 将创建好的列添加到表的列集合中
dt.Columns.Add(c1);

//3.3 再创建出一列，列名为className
DataColumn c2 = new DataColumn("className");
c2.AllowDBNull = false;//是否允许为DBNull
dt.Columns.Add(c2);

//3.4为表设置主键
dt.PrimaryKey = new DataColumn[]{c1};//因为主键可以是组合主键，所以需要用一个列的数组来指定。

//4.1创建表的数据行：因为如果new创建行，那么你又怎么确定这一行有几列呢？所以行就是根据表的架构生成的行

//手动添加数据行的值
DataRow row = dt.NewRow();
//row[0] = 1;//标识列不需要手动赋值，赋值会将自动产生的值覆盖掉。
row[1] = "sdfasdfas";

//4.将行添加到表的行集合中 
dt.Rows.Add(row);
//再加一行
DataRow row1 = dt.NewRow();
row1[1] = "dfasdfasdsdfasdfas";
//4.将行添加到表的行集合中 
dt.Rows.Add(row1); 

 

2、使用DataAdapter查询数据库，并把结果集Fill填充到DataSet对象

string connStr = "Data Source=.;Initial Catalog=MySchool;Integrated Security=True";
string sql = "select * from student; select * from grade";  //实际上是两条sql语句，会产生两个结果集

//创建数据适配器：它里面封装了SqlConnection和SqlCommand对象。也就意味着我们不需要显示的创建这两个对象和显示的打开连接通道
SqlDataAdapter da = new SqlDataAdapter(sql, connStr); //这么做实际上SqlDataAdapter内部创建了一个连接对象和一个查询用的SqlCommand对象。

//那么数据适配器就会根据用户的请求去运送数据回来。 
//DataTable dt = new DataTable();
//数据适配器将数据运送回来，存储到你指定的DataTable对象中.
//da.Fill(dt);

//如果有多个结果集就可以创建数据集做接收
DataSet ds = new DataSet();
//数据适配器将数据运送回来，它会根据查询语句生成多个DataTable对象，加入到DataSet中给你。
da.Fill(ds);

//现在就可以指定数据源啦。。数据源可以是集合（如List）、DataTable、数组
//DataGridView控件
//this.dataGridView1.DataSource = ds.Tables[0];
//this.dataGridView2.DataSource = ds.Tables[1];

//ComboBox控件
//this.comboBox1.ValueMember = "stuId";//下拉列表项被选中后，选中的值是对象的哪个属性的值。
//this.comboBox1.DisplayMember = "stuName";//下拉列表显示的对象属性
//this.comboBox1.DataSource = ds.Tables[0];//指定数据源。注意要先指定上面两个属性，再绑定数据源

注意：SqlDataAdapter.Fill()方法实际上就是使用其SelectCommand来查询数据库的。

 

 

二、上午《04、使用读取器读取数据到数据集》--简单模拟DataAdapter做的事情

 1、DataAdapter实际上就是封装了连接器、命令、读取器等对象，它负责连接数据库、发送命令（sql语句）、创建DataSet、通过DataReader读取结果集每一行并填充到本地DataSet对象中等一揽子事情。

//1.创建数据集--相当于数据库
DataSet ds = new DataSet();
//2.创建数据表
DataTable dt = new DataTable("grade");
//3.1 创建好表的架构--结构
DataColumn c1=new DataColumn("classId",typeof(int));
c1.AutoIncrement = true;//设置标识列
c1.AutoIncrementSeed = 1;//标识种子
c1.AutoIncrementStep = 1;//标识增量
//3.2 将创建好的列添加到表的列集合中
dt.Columns.Add(c1);
//3.3 再创建出一列
DataColumn c2 = new DataColumn("className");
c2.AllowDBNull = false;
dt.Columns.Add(c2);
//3.4为表设置主键
dt.PrimaryKey = new DataColumn[]{c1};

//下面开始用DataReader从数据库读取行
string connStr = "Data Source=.;Initial Catalog=MySchool;Integrated Security=True";
using (SqlConnection conn = new SqlConnection(connStr))
{
    string sql = "select classid,classname from grade";
    conn.Open();
    SqlCommand command = new SqlCommand(sql, conn);
    SqlDataReader reader = command.ExecuteReader();
    while (reader.Read())
    {
        //根据表的架构生成行
        DataRow row = dt.NewRow();
        //将读取器读取出的数据赋值给行的某一列
        row["className"] = reader["classname"];
        //将生成的行添加到表中
        dt.Rows.Add(row);
    }
}

//最后将生成的表添加到数据集
ds.Tables.Add(dt);
this.dgvList.DataSource = ds.Tables["grade"];

 

三、上午《05、DataTable表的遍历》--《06、使用数据适配器分页》

1、本地DataTable对象的遍历

　　注意DataTable对象主要有两大集合：列集合和行集合。遍历时可以循环行，再循环列，获得一个值。另外值的数据类型基本都是.NET下的预定义类型（可以参见System.TypeCode枚举里面的成员）。遍历的例子如下：

StringBuilder sb = new StringBuilder();

for (int row = 0; row < dt.Rows.Count; row++) //获取表的每一行
{
    foreach (DataColumn col  in dt.Columns)
    {
        sb.Append(dt.Rows[row][col].ToString()+"   ");
    }
    sb.AppendLine();
}

MessageBox.Show(sb.ToString());

 

2、用SqlDataAdapter分页（伪分页）

　　使用SqlDataAdapter.Fill()的重载方法，可以指定从第几行开始，最多检索几行，从而将这么多行填充到DataTable里面去，这么做看上去很像分页查询。但是要注意每次Fill()的时候，实际上都是提交之前的sql语句并将所有结果集全部读取到本地内存，然后再取指定的几行填充到DataTable里去的。所以这种假分页并没提高查询效率。如下所示：

DataTable table = new DataTable();

using (var conn = new SqlConnection(connStr))
{
    string sql1 = "select id,swift_no,product_name from ProductInfo where id <100";

    SqlDataAdapter adap = new SqlDataAdapter(sql1, conn);
    adap.Fill(0, 10, table);//先按sql检索所有结果集读取到本地内存，再从中取第0行开始的10条记录，填充到DataTable对象中
}

 可以在Profiler中监控到，调用SqlDataAdapter.Fill()重载方法后，根本没产生任何可以分页的SQL语句，仍然提交的是原来的sql，当然结果集也是全部读到本地内存里。

　　假如sql检索的结果集有1万条记录，那么每次Fill()都是先去读1万条到本地内存，然后取10条填充到DataTable。点下一页，又是用原sql检索这1万条读到本地内存，然后取下一批10条填充到DataTable。这算个毛线分页啊。

 

3、真正需要分页在ADO.NET中手写sql的话，还是要自己写分页的sql语句。

详见《第六天 -- 《2014-07-12 SQL进阶》第三节

当然有不少ORM在ADO.NET框架之上做了更酷的封装，像分页查询这种事，程序员已经可以不去管sql怎么写了。（但是还是要求会手写为好） 

 

 

四、上午《07、数据适配器的补充说明》--《08、使用数据适配器做更新提交时的限制》

1、先看一下MSDN上SqlDataAdapter的帮助

　　SqlDataAdapter 是 DataSet 和 SQL Server之间的桥接器，用于检索和保存数据。SqlDataAdapter 通过对数据源使用适当的 T-SQL 语句映射 Fill（它可更改 DataSet 中的数据以匹配数据源中的数据）和 Update（它可更改数据源中的数据以匹配 DataSet 中的数据）来提供这一桥接。
　　当 SqlDataAdapter 填充 DataSet 时，它为返回的数据创建必需的表和列（如果这些表和列尚不存在）。但是，除非 MissingSchemaAction 属性设置为 AddWithKey，否则这个隐式创建的架构中不包括主键信息。也可以使用 FillSchema，让 SqlDataAdapter 创建 DataSet 的架构，并在用数据填充它之前就将主键信息包括进去。有关更多信息，请参见向 DataSet 添加现有约束。
　　SqlDataAdapter 与 SqlConnection 和 SqlCommand 一起使用（可以配合使用，需要检索和单表保存时多用SqlDataAdapter，需要增删改的时候多用SqlCommand，根据语境和需求使用适合的对象），以便在连接到 SQL Server 数据库时提高性能。
　　SqlDataAdapter 还包括 SelectCommand、InsertCommand、DeleteCommand、UpdateCommand 和 TableMappings 属性，以便于数据的加载和更新。
当创建 SqlDataAdapter 的实例时，读/写属性将被设置为初始值。有关这些值的列表，请参见 SqlDataAdapter 构造函数。

 

2、使用数据适配器做更新提交

...
//帮助我们自动构建增删改的命令
SqlCommandBuilder ssb = new SqlCommandBuilder(da);

//DataAdapter更新数据的限制：
//1、修改和删除需要有主键，就要求必须从数据源读取出主键
//2、对于多个数据表不支持动态 SQL 生成
string sql = ssb.GetUpdateCommand().CommandText;
MessageBox.Show(sql);
da.Update(dt);
...

也就是说用DataAdapter更新数据库起码有两条限制：

（1）待更新的DataTable里必须有主键列。如果DataAdapter填充到DataTable中的数据，如果没有主键的话，DataAdapter不能Update

（2）待更新的DataTable里数据必须来自单张表。如果数据来自多表（多表连接）的话，DataAdapter不能Update。（当然也不能添加、删除）

 

 

五、上午《09、使用数据适配器重做省市级联》--DataAdapter与参数化查询、ComboBox控件数据源绑定DataTable对象

1、DataAdapter和参数化查询

注意检索时，参数要添加到DataAdapter的SelectCommand属性的Parameters属性（集合）里。

//根据所属省份id检索数据，返回DataTable
private DataTable LoadData(int id)
{
    string connStr = "Data Source=.;Initial Catalog=MySchool;Integrated Security=True";
    string sql = "select aid,aname from areas where apid = @id";
    SqlParameter p = new SqlParameter("id", id);
    SqlDataAdapter da = new SqlDataAdapter(sql, connStr);
    
    //将参数传递给数据适配器的SelectCommand
    da.SelectCommand.Parameters.Add(p);
    DataTable dt = new DataTable();
    da.Fill(dt);
    return dt;
} 

 

2、ComboBox控件的数据源绑定DataTable对象

注意：必须在绑定前，指定DisplayMember和ValueMember属性（分别对应哪个数据库列）。如下：

private void Form1_Load(object sender, EventArgs e)
{
    DataTable dt = LoadData(0);

    //指定下拉列表需要显示的列
    this.cboProvicence.DisplayMember = "aname";
    //还可以对每一项隐藏的存储某一个字段或者是属性的值
    this.cboProvicence.ValueMember = "aid";
    //指定数据源会让下拉列表默认显示第一项  指定数据源默认就会触 发控件的SelectedIndexChanged事件
    this.cboProvicence.DataSource = dt;

} 

//省下拉列表选项发生切换时触发
private void cboProvicence_SelectedIndexChanged(object sender, EventArgs e)
{
    int id = (int)this.cboProvicence.SelectedValue;
    DataTable dt = LoadData(id);

    cboCity.DisplayMember = "aname";
    cboCity.ValueMember = "aid";
    this.cboCity.DataSource = dt;
} 

 

 

六、《2014-07-14 SQL进阶》下午《01、大致使用存储过程的方式》--《04、调用有输出参数的存储过程》

1、C#用DataAdapter查询存储过程

详见 《第七天 -- 《2014-07-14 SQL进阶》1 -- 存储过程》的第五节