从数据库中读取两个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]!!!