数据访问层-- 对DataTable的扩展

对数据库的操作其实可以很简单

DataTable dt = new CustomDataTable("MyTable1");

DataRow dr = DT.GetNewRow(true);
   dr[0] = "sadfas";

   DT.UpData();
   GridBind();

就这样轻而易举的对数据进行操作

/// <summary>
 /// 自定义DataTable，封装对数据库的操作,只处理select 语句
 /// </summary>
 public  class CustomDataTable:DataTable
 {
  private SQLServer sqlServer;
  SqlDataAdapter sqlDataAdapter;
  //private CustomDataTable(){}
  private void Init(string SQLString)
  {
   sqlServer = SQLServer.Instance(); 
   sqlDataAdapter = new SqlDataAdapter(SQLString,sqlServer.Connection);
   SqlCommandBuilder custCB = new SqlCommandBuilder(sqlDataAdapter);
   sqlServer.Connection.Open();
   try
   {
    sqlDataAdapter.FillSchema(this,SchemaType.Source);
    sqlDataAdapter.Fill(this);
   }
   finally
   {
    sqlServer.Connection.Close();
   }
  }

  /// <summary>
  /// 构造函数
  /// </summary>
  /// <param name="SQLString">Select 语句或表名</param>
  public CustomDataTable(string SQLString)
  {
   string SQL = SQLString.Trim().ToUpper();
   if(SQL.StartsWith("SELECT")) 
   {
    Init(SQL);  
   }
   else 
   {
    Init("SELECT * FROM "+SQL);
    this.TableName = TableName;
   }
  }

  /// <summary>
  /// 构造函数
  /// </summary>
  /// <param name="TableName">表名</param>
  /// <param name="Condition">条件</param>
  public CustomDataTable(string TableName,string Condition)
  {
   string SQL = "SELECT * FROM " + TableName ;
   if(Condition != null && Condition != "" && Condition.Trim() != "")
    SQL += " WHERE " + Condition;
   Init(SQL);
   this.TableName = TableName;
  }

  /// <summary>
  /// 提交
  /// </summary>
  public void UpData()
  {
   try
   {
    sqlServer.Connection.Open();
    sqlServer.BeginTransaction();
    sqlDataAdapter.SelectCommand.Transaction = sqlServer.SqlTran;
    sqlDataAdapter.Update(this);
    sqlServer.CommitTransaction();
   }
   catch
   {
    sqlServer.RollbackTransaction();
    throw;
   }
   finally
   {
    sqlServer.Connection.Close();
   }
  }

  /// <summary>
  /// 取新行
  /// </summary>
  /// <param name="FlagAdd">标志位，True表示新行加入到表中，False反之</param>
  /// <returns>新行的引用</returns>
  public DataRow GetNewRow(bool FlagAdd)
  {
   DataRow dr = this.NewRow();
   if(FlagAdd)
    this.Rows.Add(dr);
   return dr;
  } 
 
  /// <summary>
  /// 添加新行
  /// </summary>
  /// <param name="dr">新行</param>
  public void AddNewRow(DataRow dr)
  {
   this.Rows.Add(dr);
  }

  /// <summary>
  /// 获取一行
  /// </summary>
  /// <param name="Index">索引</param>
  /// <returns>行的引用</returns>
  public DataRow GetRow(int Index)
  {
   return this.Rows[Index];
  }

  /// <summary>
  /// 获取行集
  /// </summary>
  /// <param name="Condition">条件</param>
  /// <returns>行集</returns>
  public DataRow[] GetRows(string Condition)
  {
   return this.Select(Condition,null,DataViewRowState.CurrentRows);
  }

  /// <summary>
  /// 删除一行
  /// </summary>
  /// <param name="Index"></param>
  public void DeleteRow(int Index)
  {
   this.Rows[Index].Delete();
  }

  /// <summary>
  /// 删除行集
  /// </summary>
  /// <param name="Condition">条件</param>
  public void DeleteRows(string Condition)
  {
   foreach(DataRow dr in GetRows(Condition))
    dr.Delete();
  }
  /// <summary>
  /// 删除表中所有记录
  /// </summary>
  public void Delete()
  {
   foreach(DataRow dr in this.Select(null,null,DataViewRowState.CurrentRows))
    dr.Delete();
  }

  /// <summary>
  /// 设置主键
  /// </summary>
  /// <param name="Keys">键</param>
  public void SetPrimaryKey(params string[] Keys)
  {
   DataColumn [] dc = new DataColumn[Keys.Length];
   for(int i=0;i<Keys.Length;i++)
   {
    dc[i] = this.Columns[Keys[i]];
   }
   this.PrimaryKey = dc;
  }

 }