datable合并sqldataadapter不能update的思考

从数据库中读取两个dataset,如下面所示，通过merge函数，将两个datatable放在一块，此时合成的datatable是在gridview显示的是两个表中的数据，但是当用SqlDataAdapter.Update进行数据库表的更新时，是不能更新的，这个时候

string constring = ConfigurationManager.AppSettings["ConString"];            using (SqlConnection conn = new SqlConnection(constring))            {                conn.Open();                SqlDataAdapter da = new SqlDataAdapter("select * from t_tmp", conn);                DataSet ds = new DataSet("table");                da.Fill(ds, "table");                                SqlDataAdapter da2 = new SqlDataAdapter("select * from t_citycode where citycode='11'", conn);                DataSet ds2 = new DataSet("table2");                da2.Fill(ds2,"table2");                DataRow row = ds2.Tables[0].NewRow();                ds2.Tables[0].Rows.Add(row);                //ds.Tables[0].Merge(ds2.Tables[0], true);                ds.Tables[0].Merge(ds2.Tables[0]);                SqlCommandBuilder cb = new SqlCommandBuilder(da);                da.InsertCommand = cb.GetInsertCommand();                Response.Write(cb.GetInsertCommand().CommandText);                da.Update(ds.Tables[0]);                ds.Tables[0].AcceptChanges();                gv.DataSource = ds.Tables[0];                gv.DataBind();            }  

我们用下面的语句看一下每一行的RowState，发现全部都是unchanged，这是因为，在两个表进行合成的时候，两个表中的数据进行的是简单的合并，而并不存在数据的修改或者删除或者增加等状态的改变，而SqlDataAdapter.Update识别的是每一行状态，全部 为unchanged，自然，update也是不会响应的。

                foreach (DataRow r in ds.Tables[0].Rows)                {                    Response.Write(r.RowState);                                    }

解决办法：手动插入数据，进行状态位的改变

using (SqlConnection cnn = new SqlConnection(constring))                    {                        cnn.Open();                        string select = "select * from t_feerecord";                        SqlDataAdapter sda = new SqlDataAdapter(select, cnn);                        DataSet ds = new DataSet();                        sda.Fill(ds, "table");                        DataTable dt = ds.Tables["table"];                        SqlCommandBuilder cb = new SqlCommandBuilder(sda);                        sda.InsertCommand = cb.GetInsertCommand();                        foreach (GridViewRow row in gv_batch.Rows)                        {                            string name = row.Cells[0].Text.ToString();                            string sex = row.Cells[1].Text.ToString();                            string duty = row.Cells[3].Text.ToString();                            string type = row.Cells[4].Text.ToString();                            string rank = row.Cells[5].Text.ToString();                            string policeid = row.Cells[6].Text.ToString();                            string idcardnum = row.Cells[7].Text.ToString();                            string worktime = row.Cells[8].Text.ToString();                            string workplace = row.Cells[9].Text.ToString();                            //获取隐藏列的值                            string usernum = gv_batch.DataKeys[row.RowIndex]["user_num"].ToString();                            DataRow dr = dt.NewRow();                            dr[1] = name;                            dr[2] = sex;                            dr[3] = duty;                            dr[4] = type;                            dr[5] = rank;                            dr[6] = policeid;                            dr[7] = idcardnum;                            dr[8] = workplace;                            dr[9] = worktime;                            dr[10] = usernum;                            dt.Rows.Add(dr);                        }                        foreach (DataRow r in dt.Rows)                        {                            Response.Write(r.RowState);                        }                        sda.Update(dt);                        dt.AcceptChanges();                    }

补充：对于DATASET的MERGE命令，加进去的DATATABLE按照递增顺序，从1往后加，比如dataset1本来包含一张表，即dataset1.table[0],而又dataset1.merge(datatable2)，则datatable2默认的就是dataset.table[1].dataset默认使用的都是dataset1.table[0]!!!