DataAdapter & DataSet 使用小结

DataAdapter & DataSet 使用小结 收藏
 
一、 Data Adapter的主要作用
Adapter是database与Dataset或DataTable之间的接口，它从数据库中get数据并填充至Dataset或Data table，这样就可以实现离线处理数据的能力。
一旦adapter对象将数据填充或提交完毕，它和所填充的Dataset对象就没有了任何联系。
二、Data Adapter重要属性或方法
（1）    Child Commands：这里主要包括4个commands对象
SelectCommand, UpdateCommand, InsertCommand, and DeleteCommand
（2）    Table Mappings Collection
这里需要了解一下填充机制，adapter默认向Dataset填充时，table Name依次是Table\Table1\Table2…。我们可以使用Table Mappings属性来设置我们需要的Table Name。一种方法是在fill的时候 指定，这种比较常见，还有一种是使用如下的代码去实现：
DbDataAdapter da = factory.CreateDataAdapter();
             da.SelectCommand = cmd;
             DataTableMapping tableMapping = da.TableMappings.Add("Table", "OrdersMapping");
             DataColumnMapping colMapping;
             colMapping = tableMapping.ColumnMappings.Add("OrderID", "订单号");
colMapping = tableMapping.ColumnMappings.Add("CustomerID", "客户名");
再向dataset填充的时候，表名就是 OrdersMapping，OrderID也被映射成订单号。这种应用在绑定Datagridview的时候可以应用。
使用这个功能时还要注意设置MissingMappingAction属性，不同的枚举值代表不同的处理方式。具体如下：
By default, this property is set to Passthrough. When the MissingMappingAction property is set to this value, the DataAdapter maps missing columns in your results to columns with the same name in your DataSet. Setting this property to Ignore tells the DataAdapter to ignore columns that don’t appear in the mappings collection. You can also set the MissingMappingAction property to Error, which will cause the DataAdapter to throw an exception if it detects a column in the results of your query that does not exist in the mappings collection.
（3）    使用data adapter的page功能
DataAdapter.Fill(DataSet, 0, 20, "Products")
注意：这时候即使查询出的记录有很多，也只有20条记录被get到本地。
（4）    与数据库连接的管理
如果在调用fill方法之前connection是open状态的话，adapter就不去open。
反之，adapter自己会打开对应的connection的连接，在填充完毕后在自行close。
这也就说明，如果我们是多次填充的话，最好用代码显示的Open和close数据库连接，这样会减少和数据库创建连接的次数，提高效率。
如：
Conn.open();
Da.fill(ds);
Da.fill(ds1);
Conn.close();
     (5)fill方法
Adapter在填充的时，默认是不复制表的结构的，如主键和外键，所以这样的情况下，如果我们连续调用两次fill的话，每次数据都会填充到dataset的对应表中。
如：da.Fill(ds1); da.Fill(ds1);这样ds1.Tables[0]表中就会有重复记录。
（6）当adapter从数据库get多个表的时候，它会自动分别填充到不同的表中
cmd.CommandText = "SELECT CustomerID, CompanyName, ContactName, Phone FROM Customers WHERE CustomerID = 'ALFKI';SELECT OrderID, CustomerID, EmployeeID, OrderDate FROM Orders WHERE CustomerID = 'ALFKI'";
DbDataAdapter da = factory.CreateDataAdapter();
da.SelectCommand = cmd;
da.MissingSchemaAction = MissingSchemaAction.AddWithKey;
 DataSet ds = new DataSet();
da.Fill(ds);
（7）MissingSchemaAction属性设置填充时是否获取表结构的信息，如主键和外键等信息
sqlConnection myConn = new SqlConnection(ConfigurationManager.ConnectionStrings["Northwind"].ToString());
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = myConn;
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "SELECT top 1 * FROM Region";
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            //da.MissingSchemaAction = MissingSchemaAction.AddWithKey;如果按照默认的情况，3次填充会填充3条一样的记录，如果设置，则只有1条数据。
            DataSet ds = new DataSet();
            myConn.Open();
            ds.EnforceConstraints = false;
            da.Fill(ds);
            da.Fill(ds);
            da.Fill(ds);
        myConn.Close();
（8）FillSchema Method
         （9）提高填充效率 设置的是Dataset的属性
                            ds.EnforceConstraints = false;
            da.Fill(ds);
            ds.EnforceConstraints = true;//这里设置这个属性没有什么实际意义，因为只有一个表，如果是多表，并且表与表之间有1对1或1对多的关系的话，则可以提高效率
三、 Data Adapter的update方法
主要是要设置对应的command命令，设置这样的命名通常有4种方法：
（1）         手动设置使用text或存储过程      //提倡使用
（2）         使用DataCommandBuilder   //做Demo的时候经常使用
（3）         使用向导
（4）         System.ComponentModel.ComponentResourceManager resources = new System.ComponentModel.ComponentResourceManager(typeof(Form1));
然后再使用 this.sqlInsertCommand1.CommandText = resources.GetString("sqlInsertCommand1.CommandText");来获得CommandText。
当然，还要添加parameters.
需要注意的是，通常为了提高效率，我们把这些功能分配给几个adapter对象单独完成读和更新或插入的功能。
在更新时，我们要在命令或存储过程中解决冲突。
四、 Data Adapter的并发编程
处理并发的原则有两个Optimistic Concurrency Options和pessimistic concurrency。pessimistic concurrency通常不使用，因为会带来种种不好的因素，而且我们通常可以使用事务来达到相同的效果。Optimistic Concurrency Options也有很多策略。
处理并发主要依据的是每个单元格的几个状态值，如original，current等等，通过与数据库对应值比较，我们就可以知道是否有并发，然后在根据我们实际需要的策略去解决并发问题。
附1：手动设置command命令Demo
ManuallyConfiguringDataAdapterCommandsDemo();
private void ManuallyConfiguringDataAdapterCommandsDemo()
        {
            SqlConnection myConn = new SqlConnection(ConfigurationManager.ConnectionStrings["Northwind"].ToString());
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = myConn;
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "SELECT * FROM Region";
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            da.MissingSchemaAction = MissingSchemaAction.AddWithKey;
            DataSet ds = new DataSet();
            ds.EnforceConstraints = false;
            da.Fill(ds);
            ds.EnforceConstraints = true;
            dataGridView1.DataSource = ds.Tables[0];
            //do sth like updating deleting and inserting
            DataRow newRow = ds.Tables[0].NewRow();
            newRow.BeginEdit();
            newRow["RegionID"] = 6;
            newRow["RegionDescription"] = "Russian";
            newRow.EndEdit();
            ds.Tables[0].Rows.Add(newRow);
            DataRow delRow = ds.Tables[0].Rows.Find(2);
            delRow.Delete();
            DataRow updateRow = ds.Tables[0].Rows.Find(1);
            updateRow["RegionDescription"] = "Updated";
            //update the changes into the database manually
            SqlCommand cmdUpdate = CreateUpdateCommand(myConn);
            SqlCommand cmdInsert = CreateInsertCommand(myConn);
            SqlCommand cmdDelete = CreateDeleteCommand(myConn);
            DataViewRowState dvrs = DataViewRowState.ModifiedCurrent | DataViewRowState.Deleted | DataViewRowState.Added;
            int intRowsAffected = 0;
            myConn.Open();
            foreach (DataRow row in ds.Tables[0].Select("", "", dvrs))
            {
                switch (row.RowState)
                {
                    case DataRowState.Modified:
                        intRowsAffected = SubmitUpdate(row, cmdUpdate);
                        break;
                    case DataRowState.Added:
                        intRowsAffected = SubmitInsert(row, cmdInsert);
                        break;
                    case DataRowState.Deleted:
                        intRowsAffected = SubmitDelete(row, cmdDelete);
                        break;
                }
                if (intRowsAffected == 1)
                    row.AcceptChanges();
                else
                    row.RowError = "Update attempt failed";
            }
            myConn.Close();
        }
    
        private int SubmitUpdate(DataRow row, SqlCommand cmd)
        {
            SqlParameterCollection pc = cmd.Parameters;
            pc["@RegionID"].Value = row["RegionID"];
            pc["@RegionDescription"].Value = row["RegionDescription"];
            return cmd.ExecuteNonQuery();
        }
        private int SubmitInsert(DataRow row, SqlCommand cmd)
        {
            SqlParameterCollection pc = cmd.Parameters;
            pc["@RegionID"].Value = row["RegionID"];
            pc["@RegionDescription"].Value = row["RegionID"];
            return cmd.ExecuteNonQuery();
        }
        private int SubmitDelete(DataRow row, SqlCommand cmd)
        {
            SqlParameterCollection pc = cmd.Parameters;
            pc["@RegionID"].Value = row["RegionID", DataRowVersion.Original];
            try
            {
                return cmd.ExecuteNonQuery();
            }
            catch
            {
                return 0;
            }
        }
        public SqlCommand CreateUpdateCommand(SqlConnection conn)
        {
            string strSQL;
            strSQL = "update [Region] set RegionDescription=@RegionDescription" + " where RegionID=@RegionID";
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            cmd.CommandText = strSQL;
            cmd.CommandType = CommandType.Text;
            SqlParameterCollection pc = cmd.Parameters;
            pc.Add("@RegionDescription", SqlDbType.NChar, 50, "RegionDescription");
            pc.Add("@RegionID", SqlDbType.Int, 0, "RegionID");
            return cmd;
        }
        public SqlCommand CreateInsertCommand(SqlConnection conn)
        {
            string strSQL;
            strSQL = "insert into [Region] values(@RegionID,@RegionDescription)";
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            cmd.CommandText = strSQL;
            cmd.CommandType = CommandType.Text;
            SqlParameterCollection pc = cmd.Parameters;
            pc.Add("@RegionDescription", SqlDbType.NChar, 50, "RegionDescription");
            pc.Add("@RegionID", SqlDbType.Int, 0, "RegionID");
            return cmd;
        }
        public SqlCommand CreateDeleteCommand(SqlConnection conn)
        {
            string strSQL;
            strSQL = "delete from [Region] where RegionID=@RegionID";
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            cmd.CommandText = strSQL;
            cmd.CommandType = CommandType.Text;
            SqlParameterCollection pc = cmd.Parameters;
            pc.Add("@RegionID", SqlDbType.Int, 0, "RegionID");
            return cmd;
        }
附2：使用Commandbuilder
///The CommandBuilder can generate updating logic if all of the following are true:
            //Your query returns data from only one table.
            //That table has a primary key.
            //The primary key is included in the results of your query.
            SqlConnection myconn = new SqlConnection(ConfigurationManager.ConnectionStrings["Northwind"].ConnectionString);
            //string strSQL = @"SELECT OrderID, ProductID, Quantity, UnitPrice FROM [OrderDetails] WHERE rderID = '10248' ORDER BY ProductID";
            string strSQL = "SELECT * FROM Region";
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = strSQL;
            cmd.CommandType = CommandType.Text;
            cmd.Connection = myconn;
            SqlDataAdapter da = new SqlDataAdapter(strSQL, myconn);
            SqlCommandBuilder cmdBuilder = new SqlCommandBuilder(da);
            da.InsertCommand = cmdBuilder.GetInsertCommand();
            da.UpdateCommand = cmdBuilder.GetUpdateCommand();
            da.MissingSchemaAction = MissingSchemaAction.AddWithKey;
            //da.DeleteCommand = cmdBuilder.GetDeleteCommand();
            //do sth like updating deleting and inserting
            DataSet ds = new DataSet();
            ds.EnforceConstraints = false;
            da.Fill(ds);
            ds.EnforceConstraints = true;//这里设置这个属性没有什么实际意义，因为只有一个表，如果是多表填充的话，则可以提高效率
            //DataRow newRow = ds.Tables[0].NewRow();
            //newRow.BeginEdit();
            //newRow["RegionID"] = 6;
            //newRow["RegionDescription"] = "Russian";
            //newRow.EndEdit();
            //ds.Tables[0].Rows.Add(newRow);
            //DataRow delRow = ds.Tables[0].Rows.Find(2);
            //delRow.Delete();
            DataRow updateRow = ds.Tables[0].Rows.Find(1);
            updateRow["RegionDescription"] = "Eastern";
            da.Update(ds);
 

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/25897606/viewspace-704292/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/25897606/viewspace-704292/