C#對底層資料操作的通用類

C#對底層資料操作的通用類 
關鍵字：不詳 
using System;
using System.Data;
using System.Data.SqlClient;
using System.Windows.Forms;

namespace DataLibrary
{
 /// <summary>
 /// 資料連線設定
 /// </summary>
 public class SqlConnect
 {
  static string sqlconnstr;

  /// <summary>
  /// 連接字串
  /// </summary>
  public string ConnString
  {
   set
   {
    sqlconnstr=value;
   }
   get
   {
    return sqlconnstr;
   }
  }

  /// <summary>
  /// 取得串連
  /// </summary>
  /// <returns></returns>
  public SqlConnection GetSqlConnection()
  {
   SqlConnection _connection=null;
   if(sqlconnstr==null || sqlconnstr=="")
   {
    string SystemDir=System.Environment.SystemDirectory;
    DisposalConductService DisposalConductService1= new DisposalConductService(SystemDir+"\\CBERP_Dis.DLL") ;
    sqlconnstr = "server="+DisposalConductService1.GetValue("ServerName","(local)")
     +";user id="+DisposalConductService1.GetValue("SqlAccount","sa")
     +";password="+DisposalConductService1.GetValue("PassWord","jiayu520")
     +";Database="+DisposalConductService1.GetValue("Database","CBERP");
   }
   try
   {
    _connection = new SqlConnection(sqlconnstr);
    _connection.Open();
   }
   catch(SqlException SE)
   {
    if(SE.Number==17 || SE.Number==18456)
    {
     if( MessageBox.Show("資料庫訪問參數設定不正確,是否對需要對其進行設定?","提示",MessageBoxButtons.YesNo,MessageBoxIcon.Question)==DialogResult.Yes)
     {
      sqlconnstr="";
      DisposalConduct config = new DisposalConduct();
      config.ShowDialog();
      _connection=GetSqlConnection();
     }
     else
     {
      MessageBox.Show("建立資料庫連接失敗,程式不能繼續運行!","錯誤",MessageBoxButtons.OK,MessageBoxIcon.Stop);
      Application.ExitThread();
      Application.Exit();
     }
    }
    else
    {
     MessageBox.Show(SE.Message,"提示資訊",MessageBoxButtons.OK,MessageBoxIcon.Information);
     _connection.Close();
    }
   }
   return _connection;
  }
 }

 /// <summary>
 /// 資料配置
 /// </summary>
 public class DataCatena : IDisposable
 {
  private SqlConnection _connection;
  /// <summary>
  /// 資料配置
  /// </summary>
  public DataCatena()
  {
   SqlConnect sqlconnect = new SqlConnect();
   _connection = sqlconnect.GetSqlConnection();
   _connection.Close();
  }

  /// <summary>
  /// 資料配置
  /// </summary>
  /// <param name="connStr">連接字串</param>
  public DataCatena(String connStr)
  {
   _connection = new SqlConnection(connStr);
  }

  /// <summary>
  /// 取得DataView
  /// </summary>
  /// <param name="Sqlstr">連接字串</param>
  /// <returns>資料檢視</returns>
  public DataView GetDataView(String Sqlstr)
  {
   DataView dv=null;
   SqlDataAdapter myCommand = new SqlDataAdapter(Sqlstr, _connection);
   DataSet ds = new DataSet();
   try
   {
    myCommand.Fill(ds,"table");
    dv=ds.Tables["table"].DefaultView;
   }
   catch(SqlException exc)
   {
    DebugBox db = new DebugBox(Sqlstr + "\r\n" + exc.Message);
    db.Show();
   }
   return dv;
  }

  /// <summary>
  /// 清理資源
  /// </summary>
  public void Dispose()
  {
   Dispose(true);
   GC.SuppressFinalize(true);
  }

  /// <summary>
  /// 清理所有正在使用的資源。
  /// </summary>
  /// <param name="disposing"></param>
  protected virtual void Dispose(bool disposing)
  {
   if (! disposing)
    return;

   if (this._connection != null)
   {
    this._connection.Dispose();
   }
  }

  /// <summary>
  /// 取得資料配接器
  /// </summary>
  /// <param name="Sqlstr">SQL語句</param>
  /// <returns>資料配接器</returns>
  public SqlDataAdapter GetSqlDA(string Sqlstr)
  {
   SqlDataAdapter myCommand = new SqlDataAdapter(Sqlstr, _connection);
   return myCommand;
  }

  /// <summary>
  /// 取得資料表
  /// </summary>
  /// <param name="Sqlstr">SQL語句</param>
  /// <returns>資料表</returns>
  public DataTable GetDT(String Sqlstr)
  {
   SqlDataAdapter myCommand = new SqlDataAdapter(Sqlstr, _connection);
   DataSet ds = new DataSet();
   try
   {
    myCommand.Fill(ds,"table");
   }
   catch(SqlException exc)
   {
    DebugBox db = new DebugBox(Sqlstr + "\r\n" + exc.ToString());
    db.Show();
   }
   return ds.Tables["table"];
  }

  /// <summary>
  /// 執行語句
  /// </summary>
  /// <param name="Sqlstr">SQL語句</param>
  /// <returns></returns>
  public object ExecWithValue(String Sqlstr)
  {
   object result=null;
   _connection.Open();
   SqlCommand myCommand = new SqlCommand(Sqlstr, _connection);
   try
   {
    result=myCommand.ExecuteScalar();
   }
   catch (SqlException exc)
   {
    DebugBox db = new DebugBox(Sqlstr + "\r\n" + exc.ToString());
    db.Show();
    //MessageBox.Show(exc.Message);
   }
   _connection.Close();
   return result;
  }

  /// <summary>
  /// 返回相應的行數
  /// </summary>
  /// <param name="Sqlstr">SQL語句</param>
  /// <returns>行數</returns>
  public int Exec(String Sqlstr)
  {
   int sucess=0;     
   SqlCommand myCommand = new SqlCommand(Sqlstr, _connection);
   _connection.Open();
   try
   {
    myCommand.ExecuteNonQuery();
   }
   catch (SqlException exc)
   {
    DebugBox db = new DebugBox(Sqlstr + "\r\n" + exc.ToString());
    db.Show();
    //    MessageBox.Show(exc.Message);
    sucess=exc.Number;
   }
   _connection.Close();
   return sucess;
  }
 }

 /// <summary>
 /// 單表操作
 /// </summary>
 public class SingleTable
 {
  private DataCatena DataCatena;
  private SqlDataAdapter myDataAdapter;
  private DataSet ds;
  /// <summary>
  /// 單表操作
  /// </summary>
  /// <param name="Sqlstr"></param>
  public SingleTable(string Sqlstr)
  {
   DataCatena = new DataCatena();
   myDataAdapter = DataCatena.GetSqlDA(Sqlstr);
   ds = new DataSet();
   myDataAdapter.Fill(ds, "Table1");
  }

  /// <summary>
  /// 更新一個表
  /// </summary>
  /// <param name="Sqlstr"></param>
  public void SetUpdateCommand(string Sqlstr)
  {
   myDataAdapter.UpdateCommand = new SqlCommand(Sqlstr,myDataAdapter.SelectCommand.Connection);
  }

  /// <summary>
  /// 返回資料表
  /// </summary>
  /// <returns></returns>
  public DataTable GetDataTable()
  {
   return ds.Tables["Table1"];
  }

  /// <summary>
  /// 返回資料配接器
  /// </summary>
  /// <returns></returns>
  public SqlDataAdapter GetDataAdapter()
  {
   return myDataAdapter;
  }

  /// <summary>
  /// 更新表
  /// </summary>
  public void Update()
  {
   myDataAdapter.Update(ds, "Table1");
  }
 }

 /// <summary>
 /// 自動更新
 /// </summary>
 public class AutoUpdateObj
 {
  private DataCatena DataCatena;
  private SqlDataAdapter myDataAdapter;
  private SqlCommandBuilder cb;
  private DataSet ds;
  /// <summary>
  /// 自動更新
  /// </summary>
  /// <param name="Sqlstr"></param>
  public AutoUpdateObj(string Sqlstr)
  {
   DataCatena = new DataCatena();
   myDataAdapter = DataCatena.GetSqlDA(Sqlstr);
   cb = new SqlCommandBuilder(myDataAdapter);
   ds = new DataSet();
   myDataAdapter.Fill(ds, "Table1");
  }

  /// <summary>
  /// 更新SQL語句
  /// </summary>
  /// <param name="Sqlstr"></param>
  public void SetUpdateCommand(string Sqlstr)
  {
   myDataAdapter.UpdateCommand = new SqlCommand(Sqlstr,myDataAdapter.SelectCommand.Connection);
  }

  /// <summary>
  /// 設定Select語句
  /// </summary>
  /// <param name="Sqlstr"></param>
  public void SetSelectCommand(string Sqlstr)
  {
   myDataAdapter.SelectCommand = new SqlCommand(Sqlstr,myDataAdapter.SelectCommand.Connection);
   cb.DataAdapter = myDataAdapter ;
   ds.Tables.Clear();
  }

  /// <summary>
  /// 取得資料表
  /// </summary>
  /// <returns></returns>
  public DataTable GetDataTable()
  {
   ds.Clear();
   myDataAdapter.Fill(ds, "Table1");
   return ds.Tables["Table1"];
  }

  /// <summary>
  /// 取得資料配接器
  /// </summary>
  /// <returns></returns>
  public SqlDataAdapter GetDataAdapter()
  {
   return myDataAdapter;
  }

  /// <summary>
  /// 更新
  /// </summary>
  public void Update()
  {
   try
   {
    myDataAdapter.Update(ds, "Table1");
   }
   catch(SqlException sqlex)
   {
    MessageBox.Show("出錯原因："+sqlex.Message,"出錯",MessageBoxButtons.OK,MessageBoxIcon.Error);
   }
  }
 }
}