DataFactory

using System;
using System.Data .SqlClient ;
using System.Data .OleDb ;
using System.Data ;
using System.Collections;
using System.Configuration ;
 
namespace DataFactory
{
 /// <summary>
 /// DataFactory提供Access或SqlServer数据库的访问连接
 /// </summary>
 public abstract class DBOperator:System.Exception//数据访问接口
 {  
  public abstract IDbConnection Connection{get;}  //得到数据库连接
  public abstract void Open();                    //打开数据库连接
  public abstract void Close();                   //关闭数据库连接
  public abstract void BeginTrans();              //开始一个事务
  public abstract void CommitTrans();             //提交一个事务
  public abstract void RollbackTrans();           //回滚一个事务
  public abstract bool IsOpen();                   //检测数据是否处于连线状态
  public abstract object Trans{get;}             //得到一个事务对象
  public abstract void exeSql(string strSql,string[] strParams,object[] objValues);     
  //执行Sql语句，没有返回值
  public abstract  DataSet exeSqlForDataSet(string QueryString);//执行Sql，返回DataSet

  public abstract string ExecuteNonQuery(string SqlString);//执行一条查询

  public abstract int ExecuteQuery(string SqlString);//执行一条查询并返回受影响的记录数
  
  public abstract object ObjDataRead(string SqlString);//执行并返回一个DataRead（以对象方式返回，所以在调用时要把对象转换）

        public abstract DataTable DBDataTable(string SqlString);//执行并返回一个数据表

  //public abstract object DataConn();//返回一个连接对像
  /// <summary>
  /// 检查是否存在记录
  /// </summary>
  /// <param name="SqlString">要检查的SQL语句</param>
  /// <returns>如果存在记录就是返回true反之返回false</returns>
  public abstract string CheckIsNull(string SqlString);//检测记录是否为空

  public abstract int RecordCount(string SqlString);//返回记录数,没有打开连接时

  public abstract string ReturnValue(string SqlString,string TheValue);//返回一个字段的值

  public static DBOperator Instance(string strConnection) 
  {  
   if(strConnection.IndexOf("provider=")<0) //SqlServer 
   {   
    return new SqlDBOperator(strConnection); 
   } 
   else     //other database 
   {   
    return new OleDBOperator(strConnection); 
   }
  }
 }
 internal class SqlDBOperator: DBOperator//SqlServer访问类
 {   
  private SqlConnection conn;         //数据库连接
  private SqlTransaction trans;       //事务处理类
  private bool inTransaction=false;   //指示当前是否正处于事务中
  public override IDbConnection Connection
  {  
 　　   get{return this.conn;}
  }
  
  public override object Trans
  {
   get {return trans;}
  }
  public SqlDBOperator(string strConnection)
  { 
 　　  this.conn= new SqlConnection(strConnection);
  }
  public override void Open()  
  {  
   if(conn.State.ToString().ToUpper()!="OPEN")
   {
　　　　      this.conn.Open();
   }
  }  
  public override void Close()
  {
            if (conn.State.ToString().ToUpper()=="OPEN")
        {
       this.conn.Close();
        }
  } 
  public override void BeginTrans() 
  {
   trans=conn.BeginTransaction() ;  
 　　　　inTransaction=true; 
  } 
  public override void CommitTrans()
  {
   trans.Commit();
 　　　　inTransaction=false; 
  }
  public override void RollbackTrans() 
  {
   trans.Rollback();
 　　　　inTransaction=false; 
  } 
  public override void exeSql(string strSql,string[] strParams,object[] strValues)
  { 
   SqlCommand cmd=new SqlCommand();  
   cmd.Connection=this.conn ;  
   if(inTransaction)   
   {
 　　　　  cmd.Transaction=trans; 
   }
 　　    if((strParams!=null)&&(strParams.Length!=strValues.Length) ) 
    {
     throw new Exception("查询参数和值不对应!");
     cmd.CommandText=strSql;  
    }
 　　    if(strParams!=null)  
    {
 　　    for(int i=0;i<strParams.Length;i++)
    {
     cmd.Parameters.Add(strParams[i],strValues[i]); 
    }
    }
 　      cmd.ExecuteNonQuery();
  }
  public override  DataSet exeSqlForDataSet(string QueryString)  
  {
   SqlCommand cmd=new SqlCommand(); 
   cmd.Connection=this.conn ;  
 　　    if(inTransaction)
   cmd.Transaction=trans;   
   DataSet ds = new DataSet();  
 　　 SqlDataAdapter ad = new SqlDataAdapter();
 　　 cmd.CommandText=QueryString;  
 　　 ad.SelectCommand =cmd;  
 　　 ad.Fill(ds);  
 　　 return ds; 
 　　}
  public override string ExecuteNonQuery(string SqlString)
  {
   try
   {
    SqlCommand cmd=new SqlCommand();
    if(inTransaction)
    cmd.Transaction=trans;
    cmd.Connection=this.conn ;
    cmd.CommandText =SqlString;
    cmd.ExecuteNonQuery ();
    cmd.Dispose ();
                return "true";    
   }
   catch(Exception ee)
   {
    return  ee.Message ;
   }
  }

  public override int ExecuteQuery(string SqlString)
  {
   try
   {
    SqlCommand cmd=new SqlCommand();
    if(inTransaction)
     cmd.Transaction=trans;
    cmd.Connection=this.conn ;
    cmd.CommandText =SqlString;
    int _int=cmd.ExecuteNonQuery ();
    cmd.Dispose ();
    return _int;
   }
   catch
   {
    return  -1 ;
   }
  }
  
  public override object  ObjDataRead(string SqlString)
  {
   try
   {
    SqlCommand cmd=new SqlCommand();
    if(inTransaction)
    cmd.Transaction=trans;
    cmd.Connection=this.conn ;
    cmd.CommandText=SqlString;   
    System.Data .SqlClient .SqlDataReader DataReader =cmd.ExecuteReader();
    return DataReader;
    
   }
   catch
   {
                return null;
   }
  }
  public override bool IsOpen()//判断数据库是否打开
  {
   if(conn.State.ToString().ToUpper()=="OPEN")
   {
　　　　      return true;
   }
   else
   {return false;}
  }
//  public override System.Data.SqlClient .SqlConnection DataConn()//返回连接
//  {
//   if (IsOpen())
//   {return this.conn;}
//   else
//   {return null;}
//  }
  public override DataTable DBDataTable(string SqlString)
  {
            try
   {
//     SqlDataAdapter myAdapter=new SqlDataAdapter(SqlString,this.conn);
//     DataTable myDTable=new DataTable();
//     myAdapter.Fill(myDTable);
//     return myDTable;
     SqlCommand cmd=new SqlCommand();
     cmd.Connection=this.conn ;
 　　      if(inTransaction)
     cmd.Transaction=trans;
     DataTable myDTable=new DataTable();
 　　   SqlDataAdapter ad = new SqlDataAdapter();
 　　   cmd.CommandText=SqlString;
 　　   ad.SelectCommand =cmd;
 　　   ad.Fill(myDTable);
 　　   return myDTable;
   }
   catch(Exception ex)
   {
    string aa=ex.Message ;
    return null;
   }
   
  }
  /// <summary>
  /// 检查是否存在记录
  /// </summary>
  /// <param name="SqlString">要检查的SQL语句</param>
  /// <returns>如果存在记录就是返回true反之返回false</returns>
  public override string CheckIsNull(string SqlString)
  {
   try
   {
    System.Data .SqlClient .SqlDataReader dr =(SqlDataReader)ObjDataRead(SqlString);
    if (dr.Read()){dr.Close ();return "true";}
    else{dr.Close ();return "false";}
   }
   catch(Exception ex)
   {return ex.Message ;}

  }
  /// <summary>
  /// 不用打开数据库取记录数
  /// </summary>
  /// <param name="SqlString">要执行的SQL语句</param>
  /// <returns>Int类</returns>
  public override int RecordCount(string SqlString)
  {
   try
   {
    int count=0;
    Open();
    DataTable dt=DBDataTable(SqlString);
    count=dt.Rows .Count;
    Close();
    return  count;
    
   }
   catch
   {Close();return 0;}
  }
  /// <summary>
  /// 返回一个字段中的值(不用打开数据库取值)
  /// </summary>
  /// <param name="SqlString">要执行的SQL语句</param>
  /// <param name="TheValue">要返回字段的值的名称</param>
  /// <returns></returns>
  public override string ReturnValue(string SqlString,string TheValue)
  {
   try
   {
    Open();
    System.Data .SqlClient .SqlDataReader dr=(SqlDataReader)this.ObjDataRead (SqlString);
    if (dr.Read ())
    {
                    string strTemp=dr[TheValue].ToString ();
     dr.Close ();
     Close();
     return strTemp;
    }
    dr.Close ();
    Close();
    return "";
   }
   catch
   {
    Close();
    return "";
   }

  }

  

　　　　}
 internal class OleDBOperator : DBOperator//提供Access访问接口
 { 
  private OleDbConnection conn; 
  private OleDbTransaction trans; 
  private bool inTransaction=false;
  public OleDBOperator(string strConnection) 
  {  
   　 this.conn= new OleDbConnection(strConnection);  
  } 
  public override IDbConnection Connection  
  {   
   　 get{return this.conn;}  
  }
  public override object Trans
  {
   get {return trans;}
  }
  public override void Open() 
  {
   if(conn.State.ToString().ToUpper()!="OPEN")
   {
    this.conn.Open();  
   }
  }
  public override void Close()
  {
     　　  if (conn.State.ToString().ToUpper()=="OPEN")
   {
    this.conn.Close();  
   }
  }
  public override void BeginTrans()
  {
   trans=conn.BeginTransaction() ;  
 　　　  inTransaction=true;  
  }  
  public override void CommitTrans() 
  {
   trans.Commit(); 
 　　    inTransaction=false;
  } 
  public override void RollbackTrans() 
  {
   trans.Rollback();  
 　　    inTransaction=false; 
  } 
  public override void exeSql(string strSql,string[] strParams,object[] strValues) 
  {  
   OleDbCommand cmd=new OleDbCommand(); 
　          cmd.Connection=this.conn ;  
   if(inTransaction) 
   {
 　　      cmd.Transaction=trans; 
   }
   if((strParams!=null)&&(strParams.Length!=strValues.Length) )
   {

    throw new System.Exception("查询参数和值不对应!");
    cmd.CommandText=this.ChangeQueryString(strSql);
   }
   if(strParams!=null)  
   {
    for(int i=0;i<strParams.Length;i++)
    {
     cmd.Parameters.Add(strParams[i],strValues[i]);
    }
   }
 　　    cmd.ExecuteNonQuery(); 
  }
  public override  DataSet exeSqlForDataSet(string QueryString)  
  {
   OleDbCommand cmd=new OleDbCommand();
   cmd.Connection=this.conn ;  
 　　    if(inTransaction)
        　　cmd.Transaction=trans;
 　　    DataSet ds = new DataSet();
 　　 OleDbDataAdapter ad = new OleDbDataAdapter();
   cmd.CommandText=QueryString; 
 　　 ad.SelectCommand =cmd;  
 　　 ad.Fill(ds); 
 　　 return ds; 
 　　}
  public override string ExecuteNonQuery(string SqlString)
  {
   try
   {
    OleDbCommand cmd=new OleDbCommand();
    if(inTransaction)
    cmd.Transaction=trans;   
    cmd.Connection=this.conn ;
    cmd.CommandText =SqlString;
    cmd.ExecuteNonQuery ();
    cmd.Dispose ();
    return "true";    
   }
   catch(Exception ee)
   {
    return  ee.Message ;
   }
  }
  public override int ExecuteQuery(string SqlString)
  {
   try
   {
    OleDbCommand cmd=new OleDbCommand();
    if(inTransaction)
     cmd.Transaction=trans;   
    cmd.Connection=this.conn ;
    cmd.CommandText =SqlString;
    int _int=cmd.ExecuteNonQuery ();
    cmd.Dispose ();
    return _int;    
   }
   catch
   {
    return  -1 ;
   }
  }
  public override object ObjDataRead(string SqlString)
  {
   try
   {
    OleDbCommand cmd=new  OleDbCommand ();
    if(inTransaction)
     cmd.Transaction=trans;
    cmd.Connection=this.conn ;
    cmd.CommandText=SqlString;   
    System.Data .OleDb .OleDbDataReader DataReader =cmd.ExecuteReader();
    return DataReader;
   }
   catch
   {
    return null;
   }
  }
  public override bool IsOpen()
  {
   if(conn.State.ToString().ToUpper()=="OPEN")
   {
　　　　      return true;
   }
   else
   {return false;}
  }
//  public override System.Data.OleDb .OleDbConnection  DataConn()//返回连接
//  {
//   if (IsOpen())
//   {return this.conn;}
//   else
//   {return null;}
//  }
  public override DataTable DBDataTable(string SqlString)
  {
   try
   {
    
//    OleDbDataAdapter myAdapter=new OleDbDataAdapter(SqlString,this.conn);
//    DataTable myDTable=new DataTable();
//    myAdapter.Fill(myDTable);
//    return myDTable;
   OleDbCommand cmd=new OleDbCommand();
   cmd.Connection=this.conn ;  
 　　    if(inTransaction)
        　　cmd.Transaction=trans;
 　　    DataTable myDTable=new DataTable();
 　　 OleDbDataAdapter ad = new OleDbDataAdapter();
   cmd.CommandText=SqlString; 
 　　 ad.SelectCommand =cmd;  
 　　 ad.Fill(myDTable); 
 　　 return myDTable;
   }
   catch
   {return null;}
   
  }
  public override string CheckIsNull(string SqlString)
  {
   try
   {
    System.Data.OleDb .OleDbDataReader dr =(OleDbDataReader)ObjDataRead(SqlString);
    if (dr.Read()){dr.Close ();return "true";}
    else{dr.Close ();return "false";}
   }
   catch(Exception ex)
   {return ex.Message ;}

  }

  /// <summary>
  /// 返回一个字段中的值
  /// </summary>
  /// <param name="SqlString">要执行的SQL语句</param>
  /// <param name="TheValue">要返回字段的值的名称</param>
  /// <returns></returns>
  public override string ReturnValue(string SqlString,string TheValue)
  {
   try
   {
    Open();
    System.Data .OleDb .OleDbDataReader  dr=(OleDbDataReader)this.ObjDataRead (SqlString);
    if (dr.Read ())
    {
     string strTemp=dr[TheValue].ToString ();
     dr.Close ();
     Close();
     return strTemp;
    }
    dr.Close ();
    Close();
    return "";
   }
   catch
   {
    Close();
    return "";
   }

  }

  public override int RecordCount(string SqlString)
  {
   try
   {
    int count=0;
    Open();
    DataTable dt=DBDataTable(SqlString);
    count=dt.Rows .Count;
    Close();
    return  count;
    
   }
   catch
   {Close();return 0;}
  }
  
  private string ChangeQueryString(string sql)//改变SqlServer中与Access之间的不同
  {
   return sql;
  }
 }

// public class DBOperatorFactory
// { 
//  public static DBOperator GetDBOperator(string strConnection)
//  {
//   if(strConnection.IndexOf("provider=")<0) //SqlServer 
//   {  
//　　      return new SqlDBOperator(strConnection); 
//   }  
//　　     else     //other database 
//   {  
//　　       return new OleDBOperator(strConnection); 
//   } 
//  }
// }
 
}