一次性保存DataSet的方法

前提是保存的表必须有主键

调用SaveDataSet方法即可

 

#region 采用DataSet提交数据
  
  //取得选取语句
  private string GetSelectSql(DataSet ds,string strTbName)
  {
   string  strSql="";
   DataTable dt;
   if (strTbName=="")
   {
    dt=ds.Tables[0];
    strTbName=dt.TableName;
   }
   else
    dt=ds.Tables[strTbName];
   
   foreach(DataColumn dc in dt.Columns )
   {
    strSql+=dc.ColumnName +",";
   }
   
   strSql="Select "+strSql.Substring(0,strSql.Length -1)+" From "+strTbName;
   return strSql;

  }

  //取得更新命令
  private SqlCommand GetUpdateCommand(SqlDataAdapter da)
  {
   string  strSql="",strSql1="",strTable="";
   SqlCommand cmd1=new SqlCommand();
   if(open())
   {
    SqlCommand cmd=new SqlCommand(da.SelectCommand.CommandText ,conn );
    SqlDataReader rdr;
    rdr=cmd.ExecuteReader(CommandBehavior.SchemaOnly | CommandBehavior.KeyInfo );
    DataTable tb1=rdr.GetSchemaTable();
    rdr.Close();
    closeConn();
    foreach(DataRow row in tb1.Rows )
    {
     
     strSql+=row["ColumnName"].ToString() +@"=@"+row["ColumnName"].ToString() +",";    
     
     if  (row["DataType"].ToString ()==  "System.Decimal")
     // cmd1.Parameters.Add(new SqlParameter("@"+row["ColumnName"].ToString(), (SqlDbType)row["ProviderType"] ,(int) row["ColumnSize"], ParameterDirection.Input, false, ((System.Byte)((int) row["NumericPrecision"])), ((System.Byte)((int) row["NumericScale"])), row["ColumnName"].ToString(), System.Data.DataRowVersion.Current, null));
      cmd1.Parameters.Add(new SqlParameter("@"+row["ColumnName"].ToString(), (SqlDbType)row["ProviderType"] ,(int) row["ColumnSize"], ParameterDirection.Input, false, ((System.Byte)(System.Convert.ToInt32(row["NumericPrecision"]))), ((System.Byte)(System.Convert.ToInt32( row["NumericScale"]))), row["ColumnName"].ToString(), System.Data.DataRowVersion.Current, null));
     else
      cmd1.Parameters.Add(new SqlParameter("@"+row["ColumnName"].ToString(), (SqlDbType)row["ProviderType"] ,(int) row["ColumnSize"] ,row["ColumnName"].ToString())); 
     
     strTable=row["BaseTableName"].ToString();
     if ((bool) row["isKey"])
     {
      strSql1+=row["ColumnName"].ToString() +"=@Original_"+row["ColumnName"].ToString() +" And ";
      cmd1.Parameters.Add(new SqlParameter("@Original_"+row["ColumnName"].ToString(), (SqlDbType)row["ProviderType"] ,(int) row["ColumnSize"] ,row["ColumnName"].ToString())); 
     }
    }
    
    strSql=" Update "+ strTable +"  SET " +strSql.Substring(0,strSql.Length -1) +" where " + strSql1.Substring(0,strSql1.Length-4) ;
    cmd1.CommandText =strSql;
    cmd1.Connection =da.SelectCommand.Connection ;
    return cmd1;
   }
   return null;

  }

  //取得插入命令
  private SqlCommand GetInsertCommand(SqlDataAdapter da)
  {
   string  strSql="",strSql1="",strTable="";
   SqlCommand cmd1=new SqlCommand(da.SelectCommand.CommandText  );
   if(open())
   {
    SqlCommand cmd=new SqlCommand(da.SelectCommand.CommandText  ,conn );
    SqlDataReader rdr;
    rdr=cmd.ExecuteReader(CommandBehavior.SchemaOnly | CommandBehavior.KeyInfo );
    DataTable tb1=rdr.GetSchemaTable();
    rdr.Close();
    closeConn();
    foreach(DataRow row in tb1.Rows )
    {
     
     //INSERT INTO temp111(w1, w2, w3, w4, w5, w6) VALUES (@w1, @w2, @w3, @w4, @w5, @w6)" +
     strSql+=row["ColumnName"].ToString()  +",";    
     strSql1+="@"+row["ColumnName"].ToString() +",";
     
     if  (row["DataType"].ToString ()==  "System.Decimal")
      //cmd1.Parameters.Add(new SqlParameter("@"+row["ColumnName"].ToString(), (SqlDbType)row["ProviderType"] ,(int) row["ColumnSize"], ParameterDirection.Input, false, ((System.Byte)((int) row["NumericPrecision"])), ((System.Byte)((int) row["NumericScale"])), row["ColumnName"].ToString(), System.Data.DataRowVersion.Current, null));
      cmd1.Parameters.Add(new SqlParameter("@"+row["ColumnName"].ToString(), (SqlDbType)row["ProviderType"] ,(int) row["ColumnSize"], ParameterDirection.Input, false, ((System.Byte)(System.Convert.ToInt32(row["NumericPrecision"]))), ((System.Byte)(System.Convert.ToInt32( row["NumericScale"]))), row["ColumnName"].ToString(), System.Data.DataRowVersion.Current, null));
     else
      cmd1.Parameters.Add(new SqlParameter("@"+row["ColumnName"].ToString(), (SqlDbType)row["ProviderType"] ,(int) row["ColumnSize"] ,row["ColumnName"].ToString())); 
     strTable=row["BaseTableName"].ToString();
    }
    
    strSql=" INSERT INTO "+strTable + "(" + strSql.Substring(0,strSql.Length -1) + ") Values ("+ strSql1.Substring(0,strSql1.Length -1) +")" ;
    cmd1.CommandText =strSql;
    cmd1.Connection =da.SelectCommand.Connection ;
    return cmd1;
   }
   return null;
  }
  
  
  //取得删除命令
  private SqlCommand GetDeleteCommand(SqlDataAdapter da)
  {
   string  strSql="",strTable="";
   SqlCommand cmd1=new SqlCommand(da.SelectCommand.CommandText  );
   if(open())
   {
    SqlCommand cmd=new SqlCommand(da.SelectCommand.CommandText  ,conn );
    SqlDataReader rdr;
    rdr=cmd.ExecuteReader(CommandBehavior.SchemaOnly | CommandBehavior.KeyInfo );
    DataTable tb1=rdr.GetSchemaTable();
    rdr.Close();
    closeConn();
    foreach(DataRow row in tb1.Rows )
    {
     
     if ((bool) row["isKey"])
     {
      strSql+=row["ColumnName"].ToString() +"=@Original_"+row["ColumnName"].ToString() +" And ";
      cmd1.Parameters.Add(new SqlParameter("@Original_"+row["ColumnName"].ToString(), (SqlDbType)row["ProviderType"] ,(int) row["ColumnSize"] ,row["ColumnName"].ToString())); 
     }
     
     strTable=row["BaseTableName"].ToString();
    }
    strSql="DELETE From "+strTable + " where " + strSql.Substring(0,strSql.Length -4) ;
    cmd1.CommandText =strSql;
    cmd1.Connection =da.SelectCommand.Connection ;
    return cmd1;
   }
   return null;
  }

  //保存记录集
  public bool SaveDataSet(DataSet ds,string strTbName,ref string strErr)
  {
   bool bi=true;
   string strSqlSelect=GetSelectSql(ds,strTbName);
   SqlConnection cn=new SqlConnection(connSet );
   try
   {
    SqlDataAdapter da=new SqlDataAdapter(strSqlSelect,cn);
    da.UpdateCommand =GetUpdateCommand (da)  ;
    da.DeleteCommand =GetDeleteCommand(da)  ;
    da.InsertCommand  =GetInsertCommand(da)  ;
    if (strTbName!="") da.Update(ds,strTbName);
    if (strTbName=="") da.Update(ds);
   }
   catch(Exception e)
   { 
    Log.log(e);
    strErr=e.Message ;
    bi=false;
   }
   finally
   {
    if(cn.State.ToString()=="Open") cn.Close();
    cn.Dispose();
   }
   return bi;
  }
  ///

  /// 不标识名字,取第一个dt,dt必须带名字
  ///

  public bool SaveDataSet(DataSet ds,ref string strErr)
  {
   return SaveDataSet(ds,"",ref strErr);
  }
  #endregion