反射寫的資料操作

using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections;
using System.Text;
namespace ASP.NET_6.DataBase
{
 /// <summary>
 /// DataMessageHandler 的摘要說明。
 /// </summary>
   public enum SQLDmlType
   {
      insert=1,update,delete,nothing
   }

  public class DataMessageHandler
  {
    private System.Data.SqlClient.SqlConnection cn;
    private System.Data.SqlClient.SqlDataAdapter adapter;
    private DataTable dt;
    private Hashtable hTable;
    private string []strPK;
    private SQLDmlType mySqlEnum;

    public DataMessageHandler(string sDataBase,string []PkField)
    {   
      strPK=PkField;

      cn=new SqlConnection("Data Source=.;Initial Catalog="+sDataBase+";Trusted_Connection=yes");
      dt=new DataTable();
    }

    public DataTable this[string sTableName]
    {
      get
      {
        try
        {
          adapter=new SqlDataAdapter("select * from "+sTableName,cn);
          adapter.Fill(dt);
          dt.TableName=sTableName;

          //註冊事件
          dt.RowChanged+=new DataRowChangeEventHandler(this.CurrentTable_Change);

          //使用RowDeleted無法訪問以被刪除的行!!!
          dt.RowDeleting+=new DataRowChangeEventHandler(this.CurrentTble_RowDelete);

          return dt;
        }
        catch(Exception exc)
        {
          throw new Exception(exc.Message);
        }
        finally
        {
          if(cn.State==ConnectionState.Open)
          {
            cn.Close();
          }
        }
        return null;
      }

    }
   
    private void CurrentTble_RowDelete(object sender,DataRowChangeEventArgs e)
    {
      mySqlEnum=this.SQLCommandFactory(this.GetSqlDellSentenct(e.Row),e.Row);
    }
   
    private void  CurrentTable_Change(object sender,DataRowChangeEventArgs e)
    {

      this.GetSQLHTable(e.Row);
      this.SQLCommandFactory(this.GetSqlSentence(e.Row),e.Row);
    }
   
    //擷取行的key/value對
    private void GetSQLHTable(DataRow row)
    {
      DataTable dt=row.Table;
      hTable=new Hashtable();

      for(int i=0;i<dt.Columns.Count;i++)
      {
        if(dt.Columns[i].DataType==typeof(String) || dt.Columns[i].DataType==typeof(DateTime))
        {
          hTable.Add(dt.Columns[i].ColumnName,"'"+row[i].ToString()+"'");  
        }
        else
        {
          hTable.Add(dt.Columns[i].ColumnName,row[i]);
        }

      }

    }

    //SQL語句工廠
    private string GetSqlSentence(DataRow row)
    {
      StringBuilder sBuilder=new StringBuilder();
      StringBuilder sBuilder1=new StringBuilder();

        //如果行的狀態為添加
       if(row.RowState==DataRowState.Added)
       {
          sBuilder.Append("insert into "+row.Table.TableName+"(");

          //添加列
          for(int i=0;i<row.Table.Columns.Count;i++)
          { 
             //主鍵列除外
             for(int j=0;j<strPK.Length;j++)
             { 
                //主鍵不是自動編號
                if(row.Table.Columns[i].ColumnName==strPK[j] &&
                   (row.Table.Columns[i].DataType==typeof(String) ||
                    row.Table.Columns[i].DataType==typeof(DateTime)))
                {
                   sBuilder.Append(row.Table.Columns[i].ColumnName+",");
                   sBuilder1.Append(hTable[row.Table.Columns[i].ColumnName].ToString()+",");

                }
                //主鍵為自動編號
                else if(row.Table.Columns[i].ColumnName!=strPK[j] &&
                    hTable[row.Table.Columns[i].ColumnName].ToString()!="''" &&
                     hTable[row.Table.Columns[i].ColumnName] is DBNull==false)
                { 
                   //如何某列值為空白,則不記錄
                 
                   sBuilder.Append(row.Table.Columns[i].ColumnName+",");
                   sBuilder1.Append(hTable[row.Table.Columns[i].ColumnName].ToString()+",");
                 
                }
             }
          }

          sBuilder.Remove(sBuilder.Length-1,1);
          sBuilder.Append(") values(");

          sBuilder1.Remove(sBuilder1.Length-1,1);
          sBuilder1.Append(")");

          sBuilder.Append(sBuilder1.ToString());

//          foreach(object obj in hTable.Keys)
//          {
//           
//             for(int j=0;j<strPK.Length;j++)
//            { 
//               if(obj.ToString()==strPK[j] &&
//                    (row.Table.Columns[j].DataType==typeof(String) ||
//                    row.Table.Columns[j].DataType==typeof(DateTime)))
//               {
//                  sBuilder.Append(hTable[obj].ToString()+",");
//
//               }
//               else if(obj.ToString()!=strPK[j] && hTable[obj].ToString()!="''" &&
//                       hTable[obj] is DBNull==false)
//               {
//                  sBuilder.Append(hTable[obj].ToString()+",");
//               }
//            }
            
//          }

//          sBuilder.Remove(sBuilder.Length-1,1);
//          sBuilder.Append(")");
     
       }
      
       //如果行的狀態為更改
       else if(row.RowState==DataRowState.Modified)
       { 
          sBuilder.Append("update "+row.Table.TableName+" set ");
          StringBuilder sBuilder2=new StringBuilder(" where ");

          foreach(object obj in hTable.Keys)
          {
             for(int n=0;n<strPK.Length;n++)
             { 
                //如果value不為空白
                if(obj.ToString()!=strPK[n])
                {
                   if(hTable[obj].ToString()!="")
                   {
                      sBuilder.Append(obj.ToString()+"="+hTable[obj].ToString()+",");
                   }
                }
                else
                {
                   sBuilder2.Append(obj.ToString()+"="+hTable[obj].ToString()+" and ");
                }
             }
          }

          sBuilder.Remove(sBuilder.Length-1,1);
          sBuilder2.Remove(sBuilder2.Length-4,4);
          sBuilder.Append(sBuilder2);
       }
     
        if(sBuilder.Length<=0)
        {
          return "";
        }
        else
        {
          hTable.Clear();
          return sBuilder.ToString();
        }
      }
     
     //刪除語句工廠
    private string GetSqlDellSentenct(DataRow row)
    {
        //如果行的狀態為刪除
         string sBuilder="delete from "+row.Table.TableName+" where ";
          
         DataTable dt=row.Table;
            for(int m=0;m<dt.Columns.Count;m++)
            {
               for(int k=0;k<strPK.Length;k++)
               {
                  if(dt.Columns[m].ColumnName==strPK[k])
                  {
                    
                     if(dt.Columns[m].DataType==typeof(String) || dt.Columns[m].DataType==typeof(DateTime))
                     {
                        sBuilder+=(dt.Columns[m].ColumnName+"="+"'"+row[m]+"' and ");
                     }
                     else
                     {
                        sBuilder+=(dt.Columns[m].ColumnName+"="+row[m].ToString()+" and ");

                     }
                  }
               }
              
            }
            sBuilder=sBuilder.Substring(0,sBuilder.LastIndexOf("and"));

         if(sBuilder.Length>0)
         {
            return sBuilder.ToString();
         }
         else
         {
            return "";
         }

    }

     //根據SQL語句工廠執行Command命令
     private SQLDmlType SQLCommandFactory(string strDml,DataRow row)
     {
        if(strDml!="")
        {
           SqlCommand cmd=new SqlCommand(strDml,cn);
           try
           {
              cn.Open();
              cmd.ExecuteNonQuery();
          
              switch(row.RowState)
              {
                 case DataRowState.Added:
                    return SQLDmlType.insert;
                    break;

                 case DataRowState.Modified:
                    return SQLDmlType.update;
                    break;
             
                 default:return SQLDmlType.delete;
                    break;
              }
           }
           catch(Exception exc)
           {
              throw new Exception(exc.Message);
           }
           finally
           {
              if(cn.State==ConnectionState.Open)
              {
                 cn.Close();
              }
           }
        }
        else
        {
           return SQLDmlType.nothing;
        }
     }

     //前台擷取枚舉資訊
     public SQLDmlType GetDmlTypeChange
     {
        get
        {
           return this.mySqlEnum;
        }
     }

  }
}