dotnet编码心得——数据库接口工厂

编程用户对数据库的操作封装
数据库接口工厂
public class dbFactory
{
    public dbFactory()
    {      
    }
    public dbConnect CreatOracleConnect( string connectstring)
    {        
            return new dbOracle(connectstring);                 
    }
}
数据库接口抽象类
public abstract class dbConnect
{
    public dbConnect() { }
    public abstract void Connect();//创建数据库连接 
    public abstract void CheckConn();//数据连接检查
    public abstract DataTable CreateDataTable(string strsql);//返回指定sql语句的datatable

    public abstract void DeleteBySql(string strsql);//提交删除命令
    public abstract void UpdateBySql(string strsql);//提交更新命令  
    public abstract void InsertBySql(string strsql);//提交插入命令

    public abstract void FillArray(string strsql, string[] arr);//填充数组
    public abstract void FillListBox(System.Web.UI.WebControls.ListBox Listboxbuff, string strsql);//填充listbox 
    public abstract void FillGridView(System.Web.UI.WebControls.GridView GridViewbuff, string strsql);//填充gridview 
    public abstract void FillDropDownList(System.Web.UI.WebControls.DropDownList Listboxbuff, string strsql); 
    public abstract int GetNumber(string strsql);//返回数字型自段
    public abstract string GetString(string strsql);//返回字符串字段 
    public abstract DataRow  GetDataRow(string strsql);//得到一行数据
 
}
oracle数据库接口类：
public class dbOracle : dbConnect
{
   private  OracleConnection MyConn;//全局数据连接
   private  string MyConnectString;//全局连接字符串
   private CFException myException = new CFException();
    public dbOracle( string connectstring)
    {
        try
        {
            MyConn = new OracleConnection();
            MyConnectString = connectstring;
            //MyConn.ConnectionString = MyConnectString;
            //MyConn.Open();
        }
      catch (Exception ex)
        {
            myException.showMessage(ex); 
        }
    }
    public override void Connect()//创建数据库连接
    {
        try
        {
            if (MyConn.State == ConnectionState.Closed || MyConn.State == ConnectionState.Broken)
            {
                MyConn.ConnectionString = MyConnectString;
                MyConn.Open();
            }
            else
            {
                MyConn.Close();
                MyConn.ConnectionString = MyConnectString;
                MyConn.Open();
            }          
       }
       catch (Exception ex)
       {
           myException.showMessage(ex);   
       }
    }
    public override void CheckConn()//数据连接检查
    {
        try
        {
            if (this.MyConn.State == ConnectionState.Closed || this.MyConn.State == ConnectionState.Broken)
            {
                this.Connect();
            }         
        }
        catch (Exception ex)
        {
            myException.showMessage(ex);    
        }
    }
    public override DataTable CreateDataTable(string strsql)
    {
        try
        {
            this.CheckConn();
            OracleCommand Cmd = MyConn.CreateCommand();//sql查询命令对象
            Cmd.CommandText = strsql;//sql命令付给MyOraCmd对象
            OracleDataAdapter dabuff = new OracleDataAdapter();//建立适配器
            DataTable tablebuff = new DataTable();
            tablebuff.Rows.Clear();
            dabuff.SelectCommand = Cmd;
            tablebuff.BeginLoadData();
            dabuff.Fill(tablebuff);
            tablebuff.EndLoadData();
            Cmd.Dispose();
            dabuff.Dispose();
            return tablebuff;
        }
        catch (Exception ex)
        {

            myException.showMessage(ex); 
            return null;
        }
    }
    //提交删除命令
  public override  void  DeleteBySql(string strsql)
    {
        try
        {
            this.CheckConn();
            OracleCommand cmdDel = new OracleCommand();
            OracleDataAdapter daDel = new OracleDataAdapter();
            cmdDel.Connection = MyConn;
            cmdDel.CommandText = strsql;
            daDel.DeleteCommand = cmdDel;
            daDel.DeleteCommand.ExecuteNonQuery();
            cmdDel.Dispose();
            daDel.Dispose();           
        }
        catch (Exception ex)
        {
            myException.showMessage(ex);      
        }
    }
    //提交更新命令
    public override void UpdateBySql(string strsql)
    {
        try
        {
            this.CheckConn();
            OracleCommand cmdUpdate = new OracleCommand();
            OracleDataAdapter daUpdate = new OracleDataAdapter();
            cmdUpdate.Connection = MyConn;
            cmdUpdate.CommandText = strsql;
            daUpdate.UpdateCommand = cmdUpdate;
            daUpdate.UpdateCommand.ExecuteNonQuery();
            cmdUpdate.Dispose();
            daUpdate.Dispose();           
        }
        catch (Exception ex)
        {
            myException.showMessage(ex);            
        }
    }
    //提交插入命令
    public override void InsertBySql(string strsql)
    {
        try
        {
            this.CheckConn();
            OracleCommand cmdInsert = new OracleCommand();
            OracleDataAdapter daInsert = new OracleDataAdapter();
            cmdInsert.Connection = MyConn;
            cmdInsert.CommandText = strsql;
            daInsert.InsertCommand = cmdInsert;
            daInsert.InsertCommand.ExecuteNonQuery();
            cmdInsert.Dispose();
            daInsert.Dispose();           
        }
        catch (Exception ex)
        {
            myException.showMessage(ex);        
        }
    }
    public override void FillArray(string strsql, string[] arr)
    {
        try
        {
            int i;
            DataTable tablebuff;
            tablebuff = this.CreateDataTable(strsql);
            for (i = 0; i < tablebuff.Rows.Count; i++)
            {
                arr[i] = tablebuff.Rows[i].ToString();
            }

        }
        catch (Exception ex)
        {
            myException.showMessage(ex);             
        }
    }
    //填充ListBox
    public override void FillListBox(System.Web.UI.WebControls.ListBox Listboxbuff, string strsql)
    {
        try
        {
            DataTable tablebuff;
            tablebuff = this.CreateDataTable(strsql); 
            Listboxbuff.DataSource = tablebuff;
            Listboxbuff.DataValueField = tablebuff.Columns[0].ColumnName ;
            Listboxbuff.DataTextField = tablebuff.Columns[1].ColumnName;
            Listboxbuff.DataBind();      

        }
        catch(Exception ex)
        {
            myException.showMessage(ex);           
        }
    }
  
    //填充DropDownListBox
    public override void FillDropDownList(System.Web.UI.WebControls.DropDownList Listboxbuff, string strsql)
    {
        try
        {
            DataTable tablebuff;
            tablebuff = this.CreateDataTable(strsql); 
            Listboxbuff.DataSource = tablebuff;
            Listboxbuff.DataValueField = tablebuff.Columns[0].ColumnName;
            Listboxbuff.DataTextField = tablebuff.Columns[1].ColumnName;
            Listboxbuff.DataBind();
         
        }
        catch (Exception ex)
        {
            myException.showMessage(ex);            
        }
    }
   
  
   //填充GridView
    public override void FillGridView(System.Web.UI.WebControls.GridView GridViewbuff, string strsql)
    {
        try
        {
            DataTable tablebuff;
            tablebuff = this.CreateDataTable(strsql); 
            GridViewbuff.DataSource = tablebuff;
            GridViewbuff.DataBind();
        }
        catch (Exception ex)
        {
            myException.showMessage(ex); 
        }
    }
  
    //获得查询记录数
    public override int GetNumber(string strsql)//通过select(*) from 得到结果
    {
        try
        {
            this.CheckConn();
            int  num;
            OracleCommand Cmd = MyConn.CreateCommand();
            Cmd.CommandText = strsql;
            num =Convert.ToInt32(Cmd.ExecuteScalar());
            return num;
        }
        catch (Exception ex)
        {
            myException.showMessage(ex); 
            return -1;
        }
    }
    //获得查询字段值
    public override string GetString(string strsql)
    {
        try
        {
            this.CheckConn();
            string str;
            OracleCommand Cmd = MyConn.CreateCommand();
            Cmd.CommandText = strsql;
            str = Convert.ToString(Cmd.ExecuteScalar());
            return str;
        }
        catch (Exception ex)
        {
            myException.showMessage(ex); 
            return null;   
        }
    }
  
    //得到sql指定的第一行
    public override  DataRow  GetDataRow(string strsql)
    {
        try
        {
            DataRow rowbuff ;
            DataTable tablebuff;
            tablebuff = this.CreateDataTable(strsql); 
            rowbuff = tablebuff.Rows[0];         
            return rowbuff ;
        }
        catch (Exception ex)
        {
            myException.showMessage(ex);
            return null;   
        }
    }
  
 
   }

应用实例：一个可以显示oracle数据库中，指定方案（这里是CFGS）中的所有表及表中数据，一个ListBox1控件和一个GridView控件。
public partial class manager_tableshow : System.Web.UI.Page
{
    private dbConnect myoracle;//数据库接口类
    private CFException myException = new CFException();//异常处理类
    protected void Page_Load(object sender, EventArgs e)
    {
        try
        {
            myoracle = (dbConnect)Session["myconnect"];
            if (Page.IsPostBack == false)
            {
                this.showtablename();
            }
            else
            {
                this.showgrid();
            }

        }
        catch (Exception ex)
        {
            myException.showMessage(ex, this.Page);
        }
    }
  
    protected void Button1_Click(object sender, EventArgs e)
    {
        this.showgrid();
    }
     private void showtablename()//列出所有表名
    {
        this.myoracle.FillListBox(this.ListBox1, "SELECT table_name,table_name FROM ALL_TABLES WHERE OWNER='CFGS' ");
  
    }
   
    private void showgrid()//显示表所有记录
    {
        try
        {   string tablename;
            tablename = this.ListBox1.Items[this.ListBox1.SelectedIndex].Value;
            myoracle.FillGridView(this.GridView1, "select * from cfgs." + tablename);
        }
        catch (Exception ex)
        {
            myException.showMessage(ex, this.Page);
        }
    }

   protected void changepage(object sender, GridViewPageEventArgs e)//翻页事件
    {
        this.GridView1.PageIndex = e.NewPageIndex;
        string tablename;
        tablename = this.ListBox1.Items[this.ListBox1.SelectedIndex].Value;
        myoracle.FillGridView(this.GridView1, "select * from cfgs." + tablename);
    }  
}