现有A,B两个数据库 有一张同样结构的表 需要把A库中的数据导入到B库中,代码如下,不能更新到B库中啊,请高手帮助,SqlConnection cn1,cn2;
cn1=new SqlConnection("Server=myserver;database=A;uid=sa");
cn2=new SqlConnection("Server=myserver;database=B;uid=sa");
cn1.Open();
cn2.Open();
DataSet ds=new DataSet();
SqlDataAdapter da1;
SqlDataAdapter da2;
da1=new SqlDataAdapter("Select * from table",cn1);
da2=new SqlDataAdapter("Select * from table",cn2);
da1.Fill(ds,"T1");
da2.Fill(ds,"T2");
foreach(DataRow dr in ds.Tables["T1"].Rows)
{
object[] temp=dr.ItemArray;
ds.Tables["T2"].Rows.Add(temp);
}
ds.AcceptChanges();
da2.Update(ds,"T2");
cn1=new SqlConnection("Server=myserver;database=A;uid=sa");
cn2=new SqlConnection("Server=myserver;database=B;uid=sa");
cn1.Open();
cn2.Open();
DataSet ds=new DataSet();
SqlDataAdapter da1;
SqlDataAdapter da2;
da1=new SqlDataAdapter("Select * from table",cn1);
da2=new SqlDataAdapter("Select * from table",cn2);
da1.Fill(ds,"T1");
da2.Fill(ds,"T2");
foreach(DataRow dr in ds.Tables["T1"].Rows)
{
object[] temp=dr.ItemArray;
ds.Tables["T2"].Rows.Add(temp);
}
ds.AcceptChanges();
da2.Update(ds,"T2");
建议LZ分开用2个dataset吧,这样写好乱的...
其实我想说的是第二种方法,用工具完成,因为在数据库中都有工具的,如果你是用的相同的数据库,可以使用工具进行数据相互备份,如果是两个不同的数据库,可以导成文本,然后再用工具导入,这样做安全性和稳定性都不错,特别是效率一定很高!
cn1=new SqlConnection("Server=myserver;database=A;uid=sa");
cn2=new SqlConnection("Server=myserver;database=B;uid=sa");
cn1.Open();
cn2.Open();
DataSet ds=new DataSet();
SqlDataAdapter da1;
SqlDataAdapter da2;
da1=new SqlDataAdapter("Select * from table",cn1);
da2=new SqlDataAdapter("Select * from table",cn2);
da1.Fill(ds,"T1");
da2.Fill(ds,"T2");
foreach(DataRow dr in ds.Tables["T1"].Rows)
{
object[] temp=dr.ItemArray;
ds.Tables["T2"].Rows.Add(temp); //主键重复
}
ds.AcceptChanges();
da2.Update(ds,"T2");
Insert Into b.dbo.test(id,text) select id,text from a.dbo.test
Insert Into b.dbo.test(id,text) select id,text from a.dbo.test where a.dbo.text not in b.dbo.text
真的不知道为什么有许多人在调用da.Update()之前总要加上面一句
AcceptChanges()会将DataRowStatue状态从Added/Deleted/Modified变成Unchanged
private void btnInsert_Click(object sender, EventArgs e)
{
DataSet ds = new DataSet();
SqlDataAdapter adapter = new SqlDataAdapter("select * from A", this.cn);
adapter.Fill(ds, "temp"); SqlDataAdapter myAdapter = new SqlDataAdapter();
SqlCommand cmd = new SqlCommand("select * from B", this.cn);
myAdapter.SelectCommand = cmd;
myAdapter.Fill(ds, "factory"); SqlCommandBuilder myCommandBuilder = new SqlCommandBuilder(myAdapter); for (int rowCount = 0; rowCount < ds.Tables["temp"].Rows.Count; rowCount++)
{
DataRow newRow = ds.Tables["factory"].NewRow();
for (int columnCount = 0; columnCount < ds.Tables["temp"].Columns.Count; columnCount++)
{
newRow[columnCount] = ds.Tables["temp"].Rows[rowCount][columnCount];
}
ds.Tables["factory"].Rows.Add(newRow);
}
try
{
lock (this)
{
myAdapter.Update(ds, "factory");
}
} catch (Exception err)
{
cn.Close();
//throw new Exception(err);
}
}