一次性保存DataSet的方法

#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() () +",";    
     
      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() +"() +" 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() +"() +" 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