C#数据访问类

看了PetShop后虽然觉得其"数据工厂模式"比较不错,但是封装类写起来太麻烦,自己就尝试写了一个数据访问类,问题不少,留待以后
再解决

//********************************************************************************************************************
//
//本模块是用于数据访问的类
//
//********************************************************************************************************************

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

namespace stuffWMS.accessDB
{
 /// <summary>
 /// 本类主要用于对数据库的操作
 /// </summary>
 public class operateDB
 {
  
  
  private static string conStr;
  public static OleDbConnection cnn;
  OleDbDataAdapter da;
  OleDbCommandBuilder cb;
  OleDbCommand cmd;
  
  //构造函数
  #region initialize
  public operateDB()
  {
   //
   // TODO: 在此处添加构造函数逻辑
   //
   cnn=new OleDbConnection();
   
   da=new OleDbDataAdapter();
   //不用OleDbCommand对象更新到数据库时，必须有下面一行
   cb=new OleDbCommandBuilder(da);

   cmd=new OleDbCommand();

  }
  #endregion initialize

  //连接字符串
  #region get&setConnectionString

  /// <summary>
  /// 获取连接字符串
  /// </summary>
  public string MyConStr          
  {
   get {return conStr;}
   set {conStr = value;}
  }

  #endregion get&setConnectionString

  //获得表的名称
  #region acquireTableNames

  /// <summary>
  /// 获取数据库中的表名集合
  /// </summary>
  /// <returns></returns>
  public DataTable tablesCollection()
  {
   DataTable tbl=new DataTable();
   try
   {
   
    cnn.ConnectionString=conStr;
    cnn.Open();

    tbl = cnn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,
     new object[] {null, null, null, "TABLE"});
      
   }
   catch(Exception ce)
   {
    Console.WriteLine("产生错误:\n{0}",ce.Message);
   }
   finally
   {
    cnn.Close();
   }
   return tbl;
  }

  #endregion acquireTableNames
  
  //填充数据
  #region fillTable

  /// <summary>
  /// 填充dataTable的查询
  /// </summary>
  /// <param name="tblName">数据表(必须输入数据库中存在的名称,也可以是视图)</param>
  /// <param name="sqlStr">SQL语句</param>
  /// <returns>记录条数</returns>
  public int select(DataTable tblName,string sqlStr)
  {
   int i=0;

   //   try
   //   {
   //    
   tblName.Clear();
   da.Dispose();
    
   if (cnn.ConnectionString=="")
    cnn.ConnectionString=conStr;
   if (cnn.State!=ConnectionState.Open)
    cnn.Open();
   //    OleDbCommand cmd=new OleDbCommand("select * from "+tblName.TableName+" where "+sqlStr,cnn);
   cmd.Connection=cnn;
   cmd.CommandType=CommandType.Text;
   cmd.CommandText="select * from "+tblName.TableName+" where "+sqlStr;
   da.SelectCommand=cmd;
       
   i=da.Fill(tblName);
   //    
   //       
   //   }
   //   catch(Exception ce)
   //   {
   //    Console.WriteLine("产生错误:\n{0}",ce.Message);
   //   }
   //   finally
   //   {
   //this.da.Dispose();
   cnn.Close();
    
   //   }
   return i;
  }
  
  #endregion fillTable
  
  //插入记录
  #region insert(use CommandBuilder)
  /// <summary>
  /// 插入记录(用OleDbCommandBuilder)
  /// </summary>
  /// <param name="tblName">数据表</param>
  /// <param name="newRow">与表中字段对应的新行</param>
  /// <returns>影响的行数</returns>
  public int  insert(DataTable tblName,DataRow newRow)
  {
   cnn.Open();
   int i=0;
   
   //   
   //   try
   //   {
   //如何判断OleDbDataAdapter是否已经Dispose

   //下面如果不生成新的OleDbDataAdapter、OleDbCommandBuilder、OleDbCommand，
   //而用原来的全局da,cb,cmd,则在一次操作中只能更新一张表
   OleDbDataAdapter daIn=new OleDbDataAdapter();
   OleDbCommandBuilder cbIn=new OleDbCommandBuilder(daIn);
   OleDbCommand cmdIn=new OleDbCommand("select * from "+tblName.TableName,cnn);
   daIn.SelectCommand=cmdIn;
   
   //   foreach (DataTable dt in da.TableMappings)
   //   {
   //    if (dt.TableName!=tblName.TableName)
   //     dt.Clear();
   //   }
   tblName.Rows.Add(newRow);
   
 
   i=daIn.Update(tblName);

   //        
   //   }
   //   catch(Exception ce)
   //   {
   //    Console.WriteLine("产生错误:\n{0}",ce.Message);
   //   }
   //   finally
   //   {
   //    cnn.Close();
   //   }
   //   cnn.Close();
   return i;
  }
  #endregion insert(use CommandBuilder)

  //插入记录
  #region insert(use InsideTransaction,DataTable[])
  
  /// <summary>
  /// 同时更新多表
  /// </summary>
  /// <param name="tbls">数据表集</param>
  /// <param name="newRows">插入行集</param>
  /// <param name="indeCol">identity列名集</param>
  /// <returns></returns>
  public string insert(DataTable[] tbls,DataRow[] newRows,string[] indeCol)
  {
   int[] num=new int[tbls.Length];
   int sum=0;
   bool judge=false;
   string str="";

   
   if (tbls.Length==newRows.Length&&tbls.Length==indeCol.Length)
   { 
    cnn.Open();
    OleDbTransaction tran=cnn.BeginTransaction();

    for (int i=0;i<tbls.Length;i++)
    {
//     this.select(tbls[i],"1=1",tran);
     da.InsertCommand=insertCmd(tbls[i],indeCol[i]);
     
     tbls[i].Rows.Add(newRows[i]);

     da.InsertCommand.Transaction=tran;
//     try
//     {
      num[i]=da.Update(tbls[i]);
      sum+=num[i];
//     }
//     catch
//     {
//      sum=-1;
//     }
     

     if (num[i]==0)
      judge=true;
    }

    if (judge)
    {
     tran.Rollback();
     str="更新失败";
     sum=0;
    }
    else
    {
     tran.Commit();
     str="更新成功";
    }
     
   }
   cnn.Close();

   return str+",影响了 "+sum.ToString()+" 条记录";
   
  }
      
  #endregion insert(use InsideTransaction,DataTable[])

  //插入记录
  #region insert(use OutsideTransaction)
  
   /// <summary>
   /// 填充DataTable(用于事务处理)
   /// </summary>
   /// <param name="tblName">表</param>
   /// <param name="sqlStr">SQL语句</param>
   /// <param name="trs">Transaction对象</param>
   /// <returns>行数</returns>
   public int select(DataTable tblName,string sqlStr,OleDbTransaction trs)
   {
    int i=0;

    //   try
    //   {
    //    
    tblName.Clear();
    da.Dispose();
    
    if (cnn.ConnectionString=="")
     cnn.ConnectionString=conStr;
    if (cnn.State!=ConnectionState.Open)
     cnn.Open();
    //    OleDbCommand cmd=new OleDbCommand("select * from "+tblName.TableName+" where "+sqlStr,cnn);
    cmd.Connection=cnn;
    cmd.CommandType=CommandType.Text;
    cmd.CommandText="select * from "+tblName.TableName+" where "+sqlStr;
    da.SelectCommand=cmd;

    cmd.Transaction=trs;    
    i=da.Fill(tblName);
    //    
    //       
    //   }
    //   catch(Exception ce)
    //   {
    //    Console.WriteLine("产生错误:\n{0}",ce.Message);
    //   }
    //   finally
    //   {
    //this.da.Dispose();
    //   cnn.Close();
    
    //   }
    return i;
   }

  /// <summary>
  /// 插入记录(用OleDbDataAdapter.Update方法及OleDbTransaction)
  /// </summary>
  /// <param name="tblName">数据表</param>
  /// <param name="newRow">新行</param>
  /// <param name="trs">事务对象</param>
  /// <returns></returns>
  public int insert(DataTable tblName,DataRow newRow,OleDbTransaction trs)
  {

   da.InsertCommand=insertCmd(tblName,"操作编号");
   
   int num=0;

   try
   {
    tblName.Rows.Add(newRow);

    da.InsertCommand.Transaction=trs;

    num=da.Update(tblName);
   }
   catch
   {

   }
  
   return num;
   
  }

  
  #endregion insert(use OutsideTransaction)

  //构造DbDataAdapter的插入Command
  #region insertCommand
  /// <summary>
  /// 构造insertCommand
  /// </summary>
  /// <param name="dtl">数据表</param>
  /// <param name="identityCol">identity列的名称</param>
  /// <returns></returns>
  private static OleDbCommand insertCmd(DataTable dtl,string identityCol)
  {
   OleDbCommand inCmd=new OleDbCommand();
   inCmd.Connection=cnn;

   string sqlStr="";
   string strValue="";

   sqlStr = "INSERT " + dtl.TableName.ToString() + "(";
   strValue = ") Values (";
   
   for (int i=0;i<dtl.Columns.Count;i++)
   {
    //对于IDENTITY列无需赋值
    if (dtl.Columns[i].ToString() != identityCol)
    {
     sqlStr += "[" + dtl.Columns[i].ToString() + "], ";
     strValue +="?,";
     OleDbParameter myPara = new OleDbParameter();
     myPara.ParameterName = "@" + dtl.Columns[i].ToString();
     myPara.OleDbType = GetOleDbType(dtl.Columns[i].DataType.ToString());
     //      myPara.Direction = ParameterDirection.Input;
     myPara.SourceColumn = dtl.Columns[i].ToString();
     //      myPara.SourceVersion = DataRowVersion.Current;
    
     inCmd.Parameters.Add(myPara);
    }
    
   }
   
   sqlStr=sqlStr.Substring(0,sqlStr.Length-2);
   strValue=strValue.Substring(0,strValue.Length-1);

   sqlStr += strValue + ")";

   
   inCmd.CommandText = sqlStr;
  
   return inCmd;

  }

  #endregion insertCommand

  //修改
  #region update
  /// <summary>
  /// 修改记录
  /// </summary>
  /// <param name="tblName">数据表</param>
  /// <param name="strUp">SQL语句</param>
  /// <returns>影响的行数</returns>
  public int update(DataTable tblName,string strUp)
  {
   //   
   //      try
   //      {
   cnn.ConnectionString=conStr; 
   cnn.Open();
   //   OleDbCommand cmdS=new OleDbCommand("select * from "+tblName.TableName+" where "+strUp,cnn);
   //   da.SelectCommand=cmdS;
   //   tblName.Rows.Clear();
   //   da.Fill(tblName);
   OleDbCommand cmdU=new OleDbCommand("update "+tblName.TableName+" set "+strUp,cnn);
   
   //   da.UpdateCommand=cmdU;

   int i=cmdU.ExecuteNonQuery();
   //      }
   //      catch(Exception ce)
   //      {
   //       Console.WriteLine("产生错误:\n{0}",ce.Message);
   //      }
   //      finally
   //      {
   //       cnn.Close();
   //      }
   
   cnn.Close();
   return i;
  }
  #endregion update

  //修改
  #region updateCmd
  
  public OleDbCommand upCmd(string tblName,string strUp)
  {
   if (cnn.ConnectionString=="")
    cnn.ConnectionString=conStr;
   if (cnn.State!=ConnectionState.Open)
    cnn.Open();

   OleDbCommand cmdU=new OleDbCommand("update "+tblName+" set "+strUp,cnn);
            return cmdU;
  }
  
  #endregion updateCmd(use OutsideTransaction)

  //删除
  #region del(use CommandBuilder)
  /// <summary>
  /// 删除记录
  /// </summary>
  /// <param name="tblName">数据表</param>
  /// <param name="strDel">SQL语句</param>
  /// <returns>影响的行数</returns>
  public int delete(DataTable tblName,string strDel)  //strDel是删除条件
  {
   int rows=0;

   //用OleDbDataAdapter.Update方法自动更新必须在where中存在主键或唯一值
   //   try
   //   {
   //    
   cnn.Open();
   rows=tblName.Rows.Count; 
    
   for (int i=0;i< tblName.Rows.Count;i++)   
   {
    tblName.Rows[i].Delete();
   }

   //注意，如在da.Update前面用了下面的AcceptChanges方法，因为记录被删除--更新到数据库失败
   //tblName.AcceptChanges();    
   da.Update(tblName);
   //    
   
   //   }
   //   catch(Exception ce)
   //   {
   //    Console.WriteLine("产生错误:\n{0}",ce.Message);
   //   }
   //   finally
   //   {
   cnn.Close();
   //   }
   /// 
   //用OleDbCommand直接更新 
   //   try
   //   {
   //    string str="delete  from "+tblName.TableName+" where "+strDel;
   //    cnn.Open();
   //    OleDbCommand cmdD=new OleDbCommand(str,cnn);
   //    cmdD.CommandType=CommandType.Text;
   //    rows=cmdD.ExecuteNonQuery();
   //   }
   //
   //   catch(Exception ce)
   //   {
   //    Console.WriteLine("产生错误:\n{0}",ce.Message);
   //   }
   //   finally
   //   {
   //    cnn.Close();
   //   }
   return rows;
  }
  #endregion del(use CommandBuilder)

  //构造OleDbDataAdapter的删除Command
  #region delCommand(create OleDbDataAdapter.deleteCommand)

  public int delete(DataTable tblName) 
  {
   int rows=0;
   
   da.DeleteCommand=delCmd(tblName);
   
   for (int i=0;i< tblName.Rows.Count;i++)   
   {
    tblName.Rows[i].Delete();
   }

   rows=da.Update(tblName);

   return rows;
  }

  
  private static OleDbCommand delCmd(DataTable dtl)
  {
   OleDbCommand delCmd=new OleDbCommand();
   delCmd.Connection=cnn;

   string sqlStr="";
  
   sqlStr = "delete from " + dtl.TableName.ToString() + " where ";
   
   for (int i=0;i<dtl.Columns.Count;i++)
   {
    sqlStr += "([" + dtl.Columns[i].ToString() + "] = ? OR ? IS NULL AND ["+dtl.Columns[i].ToString()+"] IS NULL) AND";
    OleDbParameter myPara = new OleDbParameter();
    myPara.ParameterName = "or1_" + dtl.Columns[i].ToString();
    myPara.OleDbType = GetOleDbType(dtl.Columns[i].DataType.ToString());
    myPara.Direction = ParameterDirection.Input;
    myPara.SourceColumn = dtl.Columns[i].ToString();
    myPara.SourceVersion = DataRowVersion.Original;

    delCmd.Parameters.Add(myPara);

    int j=delCmd.Parameters.Count;
    
    bool b=dtl.Columns[i].AllowDBNull;
    
    //为何deleteCommnand对象的OleDbParameter要重复添加???,否则报错
    if (b)
    {
     
     OleDbParameter myPara1 = new OleDbParameter();
     myPara1.ParameterName = "or2_" + dtl.Columns[i].ToString();
     myPara1.OleDbType = GetOleDbType(dtl.Columns[i].DataType.ToString());
     myPara1.Direction = ParameterDirection.Input;
     myPara1.SourceColumn = dtl.Columns[i].ToString();
     myPara1.SourceVersion = DataRowVersion.Original;
     delCmd.Parameters.Add(myPara1);
     j=delCmd.Parameters.Count;
    }
  
    
   }
   sqlStr=sqlStr.Substring(0,sqlStr.Length-3);

   delCmd.CommandText = sqlStr;

   return delCmd;

  }

  #endregion delCommand(create OleDbDataAdapter.deleteCommand)

  #region amendDataBase
  public  void addColumn(DataTable tblName,string strUp) //修改表的结构,更新到数据库
  {
   cnn.Open();
   
   //  OleDbCommand cmdS=new OleDbCommand("select * from "+tblName.TableName,cnn);
   //  da.SelectCommand=cmdS;
   //  OleDbCommandBuilder cb=new OleDbCommandBuilder(da);
   //  DataColumn colItem = new DataColumn(strUp,Type.GetType("System.String"));
   //  
   //  tblName.Columns.Add(colItem);

   //为什么上面的方法不行，只能直接用SQL语句吗?
   
   da.Fill(tblName);
   da.Update(tblName);
  }
  
  #endregion amendDataBase

  //调用存储过程
  #region execProc(return dataTable)
  /// <summary>
  /// 执行存储过程
  /// </summary>
  /// <param name="procName">存储过程名字</param>
  /// <param name="ParaValue">参数的值</param>
  /// <param name="ParaName">参数名字</param>
  /// <param name="ParaType">参数的类型</param>
  /// <returns></returns>
  public DataTable ExecProc(string procName,string[] ParaValue,string[] ParaName,string[] ParaType)
  {
   OleDbCommand cmdp=new OleDbCommand();
   cmdp.Connection=cnn;
   cmdp.CommandType=CommandType.StoredProcedure;
   cmdp.CommandText=procName;
  
   for (int i=0;i<ParaName.Length;i++)
   {
    OleDbParameter pt=new OleDbParameter();

    ParaName[i]="@"+ParaName[i];
    
    //参数名字
    //pt.ParameterName=ParaName[i];
    pt.SourceColumn=ParaName[i];

    pt.OleDbType=GetOleDbType(ParaType[i]);

    pt.Value=ParaValue[i];

    cmdp.Parameters.Add(pt);

   }
   DataTable dtl=new DataTable();
   cnn.Open();
   
   da.SelectCommand=cmdp;
   da.Fill(dtl);
   cnn.Close();
   return dtl;
   
  }

  /// <summary>
  /// 设置OleDbParameter对象的DbType（把字符串变为相应的OleDbType类型）
  /// </summary>
  /// <param name="type">传入参数的字符串</param>
  /// <returns></returns>
  private static OleDbType GetOleDbType(string type)
  {
   //   try
   //   {
   //    return (OleDbType)Enum.Parse(typeof(OleDbType), type, true);
   //   }
   //   catch
   //   {
   //    return OleDbType.Varchar;
   //   }

   switch (type)
   {
    case "date":
     return OleDbType.DBDate;
     break;
    case "num":
     return OleDbType.Integer;
     break;
    default:
     return OleDbType.VarChar;
  

   }
   
  }
  #endregion execProc(return dataTable)
  

 }
}