SqlConnection myConn = new SqlConnection(myConnection);
SqlDataAdapter myDataAdapter = new SqlDataAdapter();
myDataAdapter.SelectCommand = new SqlCommand(mySelectQuery, myConn);
SqlCommandBuilder custCB = new SqlCommandBuilder(myDataAdapter);
myConn.Open();
DataSet custDS = new DataSet();
myDataAdapter.Fill(custDS, "Customers");
//code to modify data in dataset here................ //Without the SqlCommandBuilder this line would fail
myDataAdapter.Update(custDS, "Customers");
myConn.Close();
SqlDataAdapter myDataAdapter = new SqlDataAdapter();
myDataAdapter.SelectCommand = new SqlCommand(mySelectQuery, myConn);
SqlCommandBuilder custCB = new SqlCommandBuilder(myDataAdapter);
myConn.Open();
DataSet custDS = new DataSet();
myDataAdapter.Fill(custDS, "Customers");
//code to modify data in dataset here................ //Without the SqlCommandBuilder this line would fail
myDataAdapter.Update(custDS, "Customers");
myConn.Close();
这样才是把内存中的数据提交到数据库
DS是DATASET对象
/// 表间数据的复制(按Condition条件,复制源表数据到目的表)
/// </summary>
/// <param name="SourcArray">
/// 本参数数组说明:
/// SourcArray[0]表示源表的数据表名
/// SourcArray[1]---SourcArray[n-1]表示源表中字段名称集合
/// </param>
/// <param name="AimArray">
/// 本参数数组说明:
/// AimArray[0]表示目的数据表名
/// AimArray[1]---AimArray[n-1]表示目的表中字段名称集合
/// </param>
/// <param name="Condition">
/// 条件字符串
/// </param>
/// <param name="mode">
/// 数据移动的方式,参考Modes枚举
/// </param>
public bool Copy(string[] SourcArray,string[] AimArray,string Condition,Modes mode)
{
string SourceTableName="";
string [] SourceFieldName =new string[SourcArray.Length-1];
string AimTableName="";
string [] AimFieldName =new string[AimArray.Length-1];
try
{
//解析参数数组格式
SourceTableName=SourcArray[0]; //得到源表表名
for(int i=1;i<SourcArray.Length;i++) //得到要转移的源表字段集合(以数组形式)
SourceFieldName[i-1]=SourcArray[i];
AimTableName=AimArray[0]; //得到目的表表名
for(int i=1;i<AimArray.Length;i++) //得到目的表字段集合(以数组形式)
AimFieldName[i-1]=AimArray[i];
string str_SQLSource="";
str_SQLSource="select ";
for(int i=0;i<SourceFieldName.Length;i++)
str_SQLSource+=SourceFieldName[i]+",";
if(SourceFieldName.Length==0)
str_SQLSource+="*";
if(str_SQLSource.Substring(str_SQLSource.Length -1,1)==",")
str_SQLSource =str_SQLSource.Substring(0,str_SQLSource.Length-1);
str_SQLSource +=" from "+SourceTableName;
if(Condition!="")
str_SQLSource +=" where "+Condition; string str_SQLAim="select ";
for(int i=0;i<AimFieldName.Length;i++)
str_SQLAim+=AimFieldName[i]+",";
if(AimFieldName.Length==0)
str_SQLAim+="*";
if(str_SQLAim.Substring(str_SQLAim.Length -1,1)==",")
str_SQLAim =str_SQLAim.Substring(0,str_SQLAim.Length-1);
str_SQLAim +=" from "+AimTableName; DataSet dsSource;
DataSet dsAim=new DataSet();
DataRow dr;
dsSource=ExecDS(str_SQLSource);
SqlDataAdapter da=new SqlDataAdapter(str_SQLAim,m_conn); da.Fill(dsAim);
switch(mode) //判断数据转移模式(两种:Ins Cov)
{
case Modes.Ins:
{
if(dsSource!=null&&dsAim!=null)
{
#region *******源字段数与目的字段数相同时*********
if(SourceFieldName.Length ==AimFieldName.Length)
{
if(dsSource.Tables[0].Rows.Count==0)
return false;
for(int i=0;i<dsSource.Tables[0].Rows.Count;i++)
{ dr=dsAim.Tables[0].NewRow();
for (int j=0;j<dsSource.Tables[0].Columns.Count;j++)
{
dr[dsSource.Tables[0].Columns[j].Caption]=dsSource.Tables[0].Rows[i][dsSource.Tables[0].Columns[j].Caption];
}
dsAim.Tables[0].Rows.Add(dr);
}
}
#endregion #region *******源字段数大于目的字段数时***********
else if(SourceFieldName.Length >=AimFieldName.Length)
{ }
#endregion #region *******源字段数小于目的字段数时***********
else if(SourceFieldName.Length <=AimFieldName.Length)
{ }
#endregion
}
break;
}
case Modes.Cov:
{
str_SQLAim +=" where "+Condition; break;
}
}
SqlCommandBuilder thisBuilder=new SqlCommandBuilder(da);
da.Update(dsAim);
return true;
}
catch(Exception e)
{
string s=e.Message.ToString();
return false;
}
}
SQLDataAdapter.UPDate(ds.table[tablename])
sqlDataAdatper.UpDate(ds);
ds.AcceptChanges();
//更新单个表
sqlDataAdatper.UpDate(ds.Tables[1]);
ds.Tables[1].AcceptChanges();