[ASP.net(C#)]自定义数据库操作类(一)

这两天写了个类。针对Sql server和OLE数据库所写的。源码如下：

\Classes\DbControl.cs
==========================================================

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.OleDb;

namespace GuestBook
{
 /// <summary>
 /// DbControl 的摘要描述。
 /// </summary>
 public class DbControl:Classes.DataBaseType.DbOpen
 {
  //類成員定義。
  private int record_total = 0;
  protected string DBTYPE,SQL_SELECT;
  protected SqlConnection sqlConn;
  protected OleDbConnection oleConn;
  protected SqlCommand sqlCmd;
  protected OleDbCommand oleCmd;
  protected DataSet ds = new DataSet();

  public int RecordTotal
  {
   get
   {
    switch (DBTYPE)
    {
     case "SQL":
      sqlCmd.Cancel();
      sqlCmd.CommandText = SQL_SELECT;
      SqlDataReader sqlDr;
      sqlDr = sqlCmd.ExecuteReader();
      while (sqlDr.Read())
      {
       record_total++;
      }
      sqlDr.Close();
      break;
     case "OLE":
      oleCmd.Cancel();
      oleCmd.CommandText = SQL_SELECT;
      OleDbDataReader oleDr;
      oleDr = oleCmd.ExecuteReader();
      while (oleDr.Read())
      {
       record_total++;
      }
      oleDr.Close();
      break;
    }
    return record_total;
   }
  }

  public DbControl(string dbType,string dbName)
  {
   //重載構造函數。
   DBTYPE = dbType.ToUpper();
   switch (dbType.ToUpper())
   {
    case "SQL":
     sqlConn = this.SqlConnect(dbName);
     oleConn.Close();
     oleConn.Dispose();
     break;
    case "OLE":
     oleConn = this.OleConnect(dbName);
     oleConn.Close();
     oleConn.Dispose();
     break;
   }
  }

  public DbControl():base()
  {
   //
   // TODO: 在這裡加入建構函式的程式碼
   //
  }

  public void Open(string dbType,string dbName)
  {
   //數據庫文件打開。
   DBTYPE = dbType.ToUpper();
   switch (dbType.ToUpper())
   {
    case "SQL":
     sqlConn = this.SqlConnect(dbName);
     break;
    case "OLE":
     oleConn = this.OleConnect(dbName);
     break;
   }
  }

  public SqlDataReader SqlGetReader(string strQuery)
  {
   //返回一個SqlDataReader。用於Sql server
   SQL_SELECT = strQuery;
   sqlCmd = new SqlCommand(strQuery,sqlConn);
   SqlDataReader dr;
   try
   {
    sqlCmd.Connection.Open();
   }
   catch (Exception e)
   {
    throw e;
   }
   dr = sqlCmd.ExecuteReader();
   return dr;
  }

  public OleDbDataReader OleGetReader(string strQuery)
  {
   //返回一個OleDbDataReader。用於OleDb
   SQL_SELECT = strQuery;
   oleCmd = new OleDbCommand(strQuery,oleConn);
   OleDbDataReader dr;
   try
   {
    oleCmd.Connection.Open();
   }
   catch (Exception e)
   {
    throw e;
   }
   dr = oleCmd.ExecuteReader();
   return dr;
  }

  public int SqlRunCommand(string strQuery)
  {
   //執行一條SQL語句。包括記錄插入、更新、刪除。用於Sql server
   SQL_SELECT = strQuery;
   sqlCmd = new SqlCommand(strQuery,sqlConn);
   try
   {
    sqlCmd.Connection.Open();
   }
   catch (Exception e)
   {
    throw e;
   }
   return sqlCmd.ExecuteNonQuery();
  }

  public int OleRunCommand(string strQuery)
  {
   //執行一條SQL語句。包括記錄插入、更新、刪除。用於OleDb
   SQL_SELECT = strQuery;
   oleCmd = new OleDbCommand(strQuery,oleConn);
   try
   {
    oleCmd.Connection.Open();
   }
   catch (Exception e)
   {
    throw e;
   }
   return oleCmd.ExecuteNonQuery();
  }

  public DataView SqlGetDataSet(string strQuery)
  {
   //返回一個DataSet。用於Sql server
   SQL_SELECT = strQuery;
   sqlCmd = new SqlCommand(strQuery,sqlConn);
   try
   {
    sqlCmd.Connection.Open();
   }
   catch (Exception e)
   {
    throw e;
   }
   SqlDataAdapter da = new SqlDataAdapter();
   da.SelectCommand = sqlCmd;
   da.Fill(ds,"DefaultTable");
   return ds.Tables["DefaultTable"].DefaultView;
  }

  public DataView OleGetDataSet(string strQuery)
  {
   //返回一個DataSet。用於OleDb
   SQL_SELECT = strQuery;
   oleCmd = new OleDbCommand(strQuery,oleConn);
   try
   {
    oleCmd.Connection.Open();
   }
   catch (Exception e)
   {
    throw e;
   }
   OleDbDataAdapter da = new OleDbDataAdapter();
   da.SelectCommand = oleCmd;
   da.Fill(ds,"DefaultTable");
   return ds.Tables["DefaultTable"].DefaultView;
  }

  public void Close()
  {
   //數據庫關閉。
   switch (DBTYPE)
   {
    case "SQL":
     sqlCmd.Cancel();
     sqlCmd.Dispose();
     sqlConn.Close();
     sqlConn.Dispose();
     break;
    case "OLE":
     oleCmd.Cancel();
     oleCmd.Dispose();
     oleConn.Close();
     oleConn.Dispose();
     break;
   }
   ds.Clear();
   ds.Dispose();
  }
 }
}

============================================================

\Classes\DataBaseType\DbOpen.cs
============================================================

using System;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.Configuration;

namespace GuestBook.Classes.DataBaseType
{
 /// <summary>
 /// DbOpen 的摘要描述。
 /// </summary>
 public class DbOpen
 {
  public DbOpen()
  {
   //
   // TODO: 在這裡加入建構函式的程式碼
   //
  }

  protected SqlConnection SqlConnect(string dbName)
  {
   SqlConnection conn = new SqlConnection(ConfigurationSettings.AppSettings[dbName]);
   return conn;
  }

  protected OleDbConnection OleConnect(string dbName)
  {
   OleDbConnection conn = new OleDbConnection(ConfigurationSettings.AppSettings[dbName]);
   return conn;
  }
 }
}

==============================================================

类的调用方法：
==============================================================

   DbControl objDbControl = new DbControl();
   string SQL = "SELECT * FROM content";

   objDbControl.Open("sql","sqlConnection");
   dgList.DataSource = objDbControl.SqlGetDataSet(SQL);
   intTotal = objDbControl.RecordTotal;
   dgList.DataBind();
   objDbControl.Close();

==============================================================
其中“intTotal = objDbControl.RecordTotal;”即返回记录总数。
可以把它插入到DataGrid中。如：
==============================================================

  private void dgList_ItemCreated(object sender, System.Web.UI.WebControls.DataGridItemEventArgs e)
  {
   if (e.Item.ItemType == ListItemType.Pager)
   {
    System.Text.StringBuilder pagerString = new System.Text.StringBuilder();
    pagerString.Append("總計" + intTotal.ToString() + "  共" + dgList.PageCount + "頁  每頁" + dgList.PageSize + "筆");
    e.Item.Cells[0].Controls.AddAt(0,new LiteralControl(pagerString.ToString())) ;
   }
  }

==============================================================

本文地址：http://com.8s8s.com/it/it43567.htm