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[])

   public string insert(DataTable[] tbls,DataRow[] newRows)
   {
    int[] num=new int[tbls.Length];
    int sum=0;
    bool judge=false;
    string str="";

       if (tbls.Length==newRows.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],"操作编号");
     
      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);

     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,"noo");
   
    int num=0;

    try
    {
     tblName.Rows.Add(newRow);

     da.InsertCommand.Transaction=trs;

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

    }
  
    return num;
   
   }

  
   #endregion insert(use OutsideTransaction)

  //构造插入的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)
   {
     cnn.Close();
    return i;
   }
   #endregion update

//删除
   #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)

  //构造删除的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;
         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)
   }