#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
