C#数据访问类修订版

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

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

namespace DataAccess
{
 /// <summary>
 /// 本类主要用于对数据库的操作
 /// </summary>
 public class operateDB
 {
  /// <summary>
  /// 静态连接对象
  /// </summary>
  public static OleDbConnection cnn;
  private static OleDbDataAdapter da;
  //  private static OleDbCommandBuilder cb;
  private static OleDbCommand cmd;

  private static OleDbCommand cmdGetIdentity;

  public static bool isAccessDB;     //是否Access数据库

  private  OleDbConnection cnn1;
  private  OleDbDataAdapter da1;
  private  OleDbCommandBuilder cb1;
  private  OleDbCommand cmd1;

  private static OleDbCommand cmdGetIdentity1;
  
  //构造函数
  #region initialize
  /// <summary>
  /// 构造函数
  /// </summary>
  public operateDB()
  {
   //
   // TODO: 在此处添加构造函数逻辑
   //
   cnn1=new OleDbConnection();
   
   da1=new OleDbDataAdapter();
   //不用OleDbCommand对象更新到数据库时，必须有下面一行
   cb1=new OleDbCommandBuilder(da);

   cmd1=new OleDbCommand();
   
   cmdGetIdentity = null;
   cmdGetIdentity1=new OleDbCommand("SELECT @@IDENTITY",cnn);

  
  }

  public static void  initializeOperateDB(string strCon)
  {
   cnn=new OleDbConnection();
   
   da=new OleDbDataAdapter();

   cmd=new OleDbCommand();
   
   conStr=strCon;
  }

  #endregion initialize

  //获取或设置连接字符串
  #region get&setConnectionString  
  
  private static string conStr;
  private string _连接字符串;

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

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

  #endregion get&setConnectionString

  //获得表的名称
  #region acquireTableNames

  /// <summary>
  /// 获取数据库中的表名集合
  /// </summary>
  /// <returns></returns>
  public static 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;
  }

  public DataTable 获取表名集合()
  {
   DataTable tbl=new DataTable();
   try
   {
   
    cnn1.ConnectionString=this._连接字符串;
    cnn1.Open();

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

   return tbl;
  }

  /// <summary>
  /// 获取某表列名的集合
  /// </summary>
  /// <param name="tbl"></param>
  /// <returns></returns>
  public static DataTable columnsCollection(DataTable tbl)
  {
   DataTable temp=new DataTable();
   cnn.Open();

   temp=cnn.GetOleDbSchemaTable(OleDbSchemaGuid.Columns,new object[]{null,null,tbl.TableName,null});

   //   temp=cnn.GetOleDbSchemaTable(OleDbSchemaGuid.Columns,null);
   cnn.Close();
   return temp;
  }

  public DataTable 获取列名集合(DataTable tbl)
  {
   DataTable temp=new DataTable();
   cnn1.Open();

   temp=cnn1.GetOleDbSchemaTable(OleDbSchemaGuid.Columns,new object[]{null,null,tbl.TableName,null});

   //   temp=cnn.GetOleDbSchemaTable(OleDbSchemaGuid.Columns,null);
   cnn1.Close();
   return temp;
  }

  #endregion acquireTableNames

  //获得表的结构
  #region acquireTableSchema
  /// <summary>
  /// 取得表的结构（不包含数据）
  /// </summary>
  /// <param name="tblName">表名字</param>
  /// <returns></returns>
  public  static DataTable schemaTable(string tblName)
  {
   DataTable dtl = null;
   try
   {
    if (cnn.ConnectionString=="")
     cnn.ConnectionString=conStr;
    if (cnn.State!=ConnectionState.Open)
     cnn.Open();

    da.SelectCommand=new OleDbCommand("select * from "+tblName,cnn);

    dtl=new DataTable(tblName);
    da.FillSchema(dtl,SchemaType.Source);
   }
   catch
   {

   }
   finally
   {
    cnn.Close();
   }

   return dtl;
  }

  public DataTable 获取表结构(string tblName)
  {
   DataTable dtl = null;
   try
   {
    if (cnn1.ConnectionString=="")
     cnn1.ConnectionString=this._连接字符串;
    if (cnn1.State!=ConnectionState.Open && cnn1.State!=ConnectionState.Connecting)
     cnn1.Open();

    da.SelectCommand=new OleDbCommand("select * from "+tblName,this.cnn1);

    dtl=new DataTable(tblName);
    da1.FillSchema(dtl,SchemaType.Source);
   }
   catch
   {
    dtl = null;
   }
   finally
   {
    cnn1.Close();
   }
   
   return dtl;
  }

  #endregion acquireTableSchema
  
  //获的表的主键
  #region acquirePrimaryKeys

  /// <summary>
  /// 获得表的主键列
  /// </summary>
  /// <param name="DBName">数据库</param>
  /// <param name="TableName">表名</param>
  /// <returns></returns>
  public static DataTable getPrimaryKeys(string DBName,string TableName)
  {
   DataTable dtlTemp = null;
   try
   {
    cnn.Open();
    dtlTemp = cnn.GetOleDbSchemaTable(OleDbSchemaGuid.Primary_Keys,
     new Object[] {DBName,"dbo",TableName});

    operateDB.cnn.Close();
   }
   catch
   {

   }
   finally
   {
    cnn.Close();
   }

   return dtlTemp;
  }

  public DataTable 获取主键(string DBName,string TableName)
  {
   
   DataTable dtlTemp = null;
   try
   {
    cnn1.Open();
    dtlTemp = cnn1.GetOleDbSchemaTable(OleDbSchemaGuid.Primary_Keys,
     new Object[] {DBName,"dbo",TableName});

   }
   catch
   {

   }
   finally
   {
    cnn.Close();
   }

   return dtlTemp;
  }

  #endregion
  
  
  //多表填充OleDbDataAdapter
  #region MultiTableDataAdapter
  
  /// <summary>
  /// 多表查询的DataAdapter
  /// </summary>
  /// <param name="sql">SQL命令</param>
  /// <returns></returns>
  public OleDbDataAdapter setAdapter(string sql)
  {
   cnn.ConnectionString=conStr;

   da.SelectCommand=new OleDbCommand(sql);
   da.SelectCommand.Connection=cnn;
   return da;
  }

  #endregion
  
  //填充数据
  
  #region fillTable

  /// <summary>
  /// 填充dataTable的查询
  /// </summary>
  /// <param name="tblName">数据表(必须输入数据库中存在的名称,也可以是视图)</param>
  /// <param name="sqlStr">SQL语句</param>
  /// <returns>记录条数</returns>
  public static 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.State!=ConnectionState.Connecting)
     cnn.Open();

    //OleDbCommand cmd=new OleDbCommand("select * from "+tblName.TableName+" where "+sqlStr,cnn);
    cmd.Connection=cnn;
    cmd.CommandType=CommandType.Text;
    cmd.CommandText=sqlStr;
    da.SelectCommand=cmd;
       
    i=da.Fill(tblName);
       
          
   }
   catch(Exception ce)
   {
    Console.WriteLine("产生错误:/n{0}",ce.Message);
   }
   finally
   {
    da.Dispose();
    cnn.Close();
    
   }
   return i;
  }
  
  public int 填充表(DataTable tblName,string sqlStr)
  {
   int i=0;

   try
   {
       
    tblName.Clear();
    da1.Dispose();
    
    if (cnn1.ConnectionString=="")
     cnn1.ConnectionString=this._连接字符串;
    if (cnn1.State!=ConnectionState.Open)
     cnn1.Open();
    cmd1.Connection=cnn1;
    cmd1.CommandType=CommandType.Text;
    cmd1.CommandText=sqlStr;
    da1.SelectCommand=cmd;
       
    i=da1.Fill(tblName);
   }
   catch(Exception ce)
   {
    Console.WriteLine("产生错误:/n{0}",ce.Message);
   }
   finally
   {
    da.Dispose();
    cnn1.Close();
    
   }
   return i;
  }

  public int sqlStatement(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();

    cmd.Connection=cnn;
    cmd.CommandType=CommandType.Text;
    cmd.CommandText=sqlStr;
    da.SelectCommand=cmd;
       
    i=da.Fill(tblName);
   
       
          
   }
   catch(Exception ce)
   {
    Console.WriteLine("产生错误:/n{0}",ce.Message);
   }
   finally
   {
    da.Dispose();
    cnn.Close();
    
   }
   return i;
  }
  #endregion fillTable

  #region fillDataSet

  public static void fillDataSet(DataSet ds)
  {
   foreach(DataTable dtl in ds.Tables)
   {
    select(dtl,"SELECT * FROM "+dtl.TableName);
   }
  }

  public void 填充DataSet(DataSet ds)
  {
   foreach(DataTable dtl in ds.Tables)
   {
    this.填充表(dtl,"SELECT * FROM "+dtl.TableName);
   }
  }

  #endregion

  //execSql
  #region dataReader to DataTable
  
  /// <summary>
  /// 执行SQL命令，返回结果集,可用于多表的SQL命令
  /// </summary>
  /// <param name="strSql"></param>
  /// <returns></returns>
  public static DataTable execSql(string strSql)
  {
   DataTable dtl = null;
   OleDbDataReader rdr = null;
   try
   {
    if(cnn==null)
     cnn=new OleDbConnection();
    if (cnn.ConnectionString=="")
     cnn.ConnectionString=conStr;
    if (cnn.State!=ConnectionState.Open)
     cnn.Open();
    OleDbCommand cmd=new OleDbCommand(strSql,cnn);
    rdr=cmd.ExecuteReader();

    dtl=new DataTable();
   
    while(rdr.Read())
    {
     //只有read后才能获取FieldCount,所以先在DataTable添加列
     if(!(dtl.Columns.Count>0))
     {
      for(int i=0;i<rdr.FieldCount;i++)
      {
       DataColumn col=new DataColumn(rdr.GetName(i));
       col.DataType=rdr[i].GetType().ToString().IndexOf("DBNull")>=0?Type.GetType("System.String"):rdr[i].GetType();
       dtl.Columns.Add(col);
      }
     }

     DataRow dr=dtl.NewRow();
     for(int i=0;i<dtl.Columns.Count;i++)
      dr[i]=rdr[i];
     dtl.Rows.Add(dr);
    
    }
    rdr.Close();
   }
   catch
   {

   }
   finally
   {
    cnn.Close();
   }

   
   return dtl;
   
  }

  public DataTable 查询结果集(string strSql)
  {
   DataTable dtl = null;
   OleDbDataReader rdr = null;
   try
   {
    if(cnn1==null)
     cnn1=new OleDbConnection();
    if (cnn1.ConnectionString=="")
     cnn1.ConnectionString=this._连接字符串;
    if (cnn1.State!=ConnectionState.Open)
     cnn1.Open();
    OleDbCommand cmd1=new OleDbCommand(strSql,cnn1);
    rdr=cmd.ExecuteReader();

    dtl=new DataTable();
   
    while(rdr.Read())
    {
     //只有read后才能获取FieldCount,所以先在DataTable添加列
     if(!(dtl.Columns.Count>0))
     {
      for(int i=0;i<rdr.FieldCount;i++)
      {
       DataColumn col=new DataColumn(rdr.GetName(i));
       col.DataType=rdr[i].GetType().ToString().IndexOf("DBNull")>=0?Type.GetType("System.String"):rdr[i].GetType();
       dtl.Columns.Add(col);
      }
     }

     DataRow dr=dtl.NewRow();
     for(int i=0;i<dtl.Columns.Count;i++)
      dr[i]=rdr[i];
     dtl.Rows.Add(dr);
     rdr.Close();
    }
   }
   catch
   {
    dtl = null;
   }
   finally
   {
    cnn1.Close();
   }
   return dtl;
  }

  #endregion
  
  //插入记录
  #region insert(use CommandBuilder)
  /// <summary>
  /// 插入记录(用OleDbCommandBuilder)
  /// </summary>
  /// <param name="tblName">数据表</param>
  /// <param name="newRow">与表中字段对应的新行</param>
  /// <returns>影响的行数</returns>
  public static int  insert(DataTable tblName,DataRow newRow)
  {
   if (cnn.ConnectionString=="")
    cnn.ConnectionString=conStr;
   if (cnn.State!=ConnectionState.Open)
    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();
   }
   return i;
  }

  public int  插入记录(DataTable tblName,DataRow newRow)
  {
   if (cnn1.ConnectionString=="")
    cnn1.ConnectionString=this._连接字符串;
   if (cnn1.State!=ConnectionState.Open)
    cnn1.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,cnn1);
    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 del(use CommandBuilder)
  /// <summary>
  /// 删除记录
  /// </summary>
  /// <param name="tblName">数据表</param>
  /// <returns>影响的行数</returns>
  public int delete(DataTable tblName) 
  {
   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)

  #region 用Command直接执行SQL命令

  /// <summary>
  /// 用SQL语句修改
  /// </summary>
  /// <param name="strUp">SQL语句</param>
  /// <returns>影响的行数</returns>
  public static int Exec(string strUp)
  {
   int i=0;
   try
   {
    if(cnn==null)
     cnn=new OleDbConnection();
    if (cnn.ConnectionString=="")
     cnn.ConnectionString=conStr;
    if (cnn.State!=ConnectionState.Open)
     cnn.Open();

    OleDbCommand cmd=new OleDbCommand(strUp,cnn);
   
    i=cmd.ExecuteNonQuery();
   }
   catch
   {
    i = -1;
   }
   finally
   {
    cnn.Close();
   }
   return i;
  }

  public int 执行SQL(string strUp)
  {
   int i = 0;
   try
   {
    if(cnn1==null)
     cnn1=new OleDbConnection();
    if (cnn1.ConnectionString=="")
     cnn1.ConnectionString=this._连接字符串;
    if (cnn1.State!=ConnectionState.Open)
     cnn1.Open();

    cmd1=new OleDbCommand(strUp,cnn1);
   
    i=cmd.ExecuteNonQuery();
   }
   catch
   {
   }
   finally
   {
    cnn1.Close();
   }
   return i;
  }

  #endregion

  //插入、修改、删除
  #region 构造Adapter更新到数据库(DataTable)

  #region updateCmd

  /// <summary>
  /// 构造DataAdapter的UpdateCommand对象
  /// </summary>
  /// <param name="dtl">DataTable参数</param>
  /// <returns>OleDbCommand</returns>
  private static OleDbCommand  createUpdateCommand(DataTable dtl)
  {
   OleDbCommand upCmd=new OleDbCommand();
   upCmd.Connection=cnn;

    
   string updateSQL="UPDATE "+dtl.TableName+" SET ";
   string whereSQL=" WHERE ";

   for (int i=0;i<dtl.Columns.Count;i++)
   {
    OleDbParameter myPara = new OleDbParameter();

    if(!checkColumnName(dtl.Columns[i].ColumnName))
     return null;
     
    myPara.ParameterName = "@" + dtl.Columns[i].ColumnName;
    //     string str=dtl.Columns[i].DataType.ToString();
    //     if(dtl.Columns[i].DataType.ToString().IndexOf("DateTime")>0)
    //      myPara.OleDbType=OleDbType.DBTimeStamp;
    //     else if(dtl.Columns[i].DataType.ToString().IndexOf("Byte")>=0)
    //      myPara.OleDbType=OleDbType.Binary;
    //     else
    myPara.DbType = GetDbType(dtl.Columns[i].DataType);
     
    myPara.SourceColumn = dtl.Columns[i].ColumnName;
     
    upCmd.Parameters.Add(myPara);

    updateSQL+=string.Format("{0}=?,",dtl.Columns[i].ColumnName);

    whereSQL+=string.Format("(({0}=?) OR (? IS NULL AND {0} IS NULL)) AND ",dtl.Columns[i].ColumnName);
   }

   for (int i=0;i<dtl.Columns.Count;i++)
   {
    OleDbParameter myPara1 = new OleDbParameter();
    myPara1.ParameterName = "or1" + dtl.Columns[i].ColumnName;
    //     if(dtl.Columns[i].DataType.ToString().IndexOf("DateTime")>=0)
    //      myPara1.OleDbType=OleDbType.DBTimeStamp;
    //     else if(dtl.Columns[i].DataType.ToString().IndexOf("Byte")>=0)
    //      myPara1.OleDbType=OleDbType.Binary;
    //     else
    //      myPara1.OleDbType = GetOleDbType(dtl.Columns[i].DataType.ToString());
    myPara1.DbType = GetDbType(dtl.Columns[i].DataType);

    myPara1.Direction = ParameterDirection.Input;
    myPara1.SourceColumn = dtl.Columns[i].ColumnName;
    myPara1.SourceVersion = DataRowVersion.Original;

    upCmd.Parameters.Add(myPara1);

     
    OleDbParameter myPara2 = new OleDbParameter();
    myPara2.ParameterName = "or2" + dtl.Columns[i].ColumnName;
    //     string str=dtl.Columns[i].DataType.ToString();
    //     if(dtl.Columns[i].DataType.ToString().IndexOf("DateTime")>=0)
    //      myPara2.OleDbType=OleDbType.DBTimeStamp;
    //     else if(dtl.Columns[i].DataType.ToString().IndexOf("Byte")>=0)
    //      myPara1.OleDbType=OleDbType.Binary;
    //     else
    //      myPara2.DbType = GetDbType(dtl.Columns[i].DataType);
    myPara2.DbType = GetDbType(dtl.Columns[i].DataType);

    myPara2.Direction = ParameterDirection.Input;
    myPara2.SourceColumn = dtl.Columns[i].ColumnName;
    myPara2.SourceVersion = DataRowVersion.Original;
    upCmd.Parameters.Add(myPara2);
   }
    
   upCmd.CommandText=updateSQL.Substring(0,updateSQL.Length-1)+whereSQL.Substring(0,whereSQL.Length-4);
    
   int j=upCmd.Parameters.Count;
    
   return upCmd;
  }

  private OleDbCommand  生成UpdateCommand(DataTable dtl)
  {
   OleDbCommand upCmd=new OleDbCommand();
   upCmd.Connection=cnn1;

    
   string updateSQL="UPDATE "+dtl.TableName+" SET ";
   string whereSQL=" WHERE ";

   for (int i=0;i<dtl.Columns.Count;i++)
   {
    OleDbParameter myPara = new OleDbParameter();
    myPara.ParameterName = "@" + dtl.Columns[i].ColumnName;
    myPara.DbType = GetDbType(dtl.Columns[i].DataType);
    myPara.SourceColumn = dtl.Columns[i].ColumnName;
     
    upCmd.Parameters.Add(myPara);

    updateSQL+=string.Format("{0}=?,",dtl.Columns[i].ColumnName);

    whereSQL+=string.Format("(({0}=?) OR (? IS NULL AND {0} IS NULL)) AND ",dtl.Columns[i].ColumnName);
   }

   for (int i=0;i<dtl.Columns.Count;i++)
   {
    OleDbParameter myPara1 = new OleDbParameter();
    myPara1.ParameterName = "or1" + dtl.Columns[i].ColumnName;
    myPara1.DbType = GetDbType(dtl.Columns[i].DataType);
    myPara1.Direction = ParameterDirection.Input;
    myPara1.SourceColumn = dtl.Columns[i].ColumnName;
    myPara1.SourceVersion = DataRowVersion.Original;

    upCmd.Parameters.Add(myPara1);

     
    OleDbParameter myPara2 = new OleDbParameter();
    myPara2.ParameterName = "or2" + dtl.Columns[i].ColumnName;
    string str=dtl.Columns[i].DataType.ToString();
    myPara1.DbType = GetDbType(dtl.Columns[i].DataType);
    myPara2.Direction = ParameterDirection.Input;
    myPara2.SourceColumn = dtl.Columns[i].ColumnName;
    myPara2.SourceVersion = DataRowVersion.Original;
    upCmd.Parameters.Add(myPara2);
   }
    
   upCmd.CommandText=updateSQL.Substring(0,updateSQL.Length-1)+whereSQL.Substring(0,whereSQL.Length-4);
    
   int j=upCmd.Parameters.Count;
    
   return upCmd;
  }

  #endregion

  #region deleteCmd

  /// <summary>
  /// 构造构造DataAdapter的DeleteCommand对象
  /// </summary>
  /// <param name="dtl">DataTable参数</param>
  /// <returns>OleDbCommand</returns>
  private static OleDbCommand  createDelCommand(DataTable dtl)
  {
   OleDbCommand delCmd=new OleDbCommand();
   delCmd.Connection=cnn;

   string sqlStr="delete from " + dtl.TableName.ToString() + " where ";
    
   for (int i=0;i<dtl.Columns.Count;i++)
   {
    sqlStr += "([" + dtl.Columns[i].ColumnName + "] = ? OR ? IS NULL AND ["+dtl.Columns[i].ColumnName+"] IS NULL) AND";
    OleDbParameter myPara = new OleDbParameter();
    myPara.ParameterName = "or1_" + dtl.Columns[i].ColumnName;
    myPara.DbType = GetDbType(dtl.Columns[i].DataType);
    myPara.Direction = ParameterDirection.Input;
    myPara.SourceColumn = dtl.Columns[i].ColumnName;
    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].ColumnName;
     myPara1.DbType = GetDbType(dtl.Columns[i].DataType);
     myPara1.Direction = ParameterDirection.Input;
     myPara1.SourceColumn = dtl.Columns[i].ColumnName;
     myPara1.SourceVersion = DataRowVersion.Original;
     delCmd.Parameters.Add(myPara1);
     j=delCmd.Parameters.Count;
    }
   
     
   }
   sqlStr=sqlStr.Substring(0,sqlStr.Length-3);

   delCmd.CommandText = sqlStr;

   return delCmd;

  }

  private OleDbCommand  生成DelCommand(DataTable dtl)
  {
   OleDbCommand delCmd=new OleDbCommand();
   delCmd.Connection=cnn1;

   string sqlStr="delete from " + dtl.TableName.ToString() + " where ";
    
   for (int i=0;i<dtl.Columns.Count;i++)
   {
    sqlStr += "([" + dtl.Columns[i].ColumnName + "] = ? OR ? IS NULL AND ["+dtl.Columns[i].ColumnName+"] IS NULL) AND";
    OleDbParameter myPara = new OleDbParameter();
    myPara.ParameterName = "or1_" + dtl.Columns[i].ColumnName;
    myPara.DbType = GetDbType(dtl.Columns[i].DataType);
    myPara.Direction = ParameterDirection.Input;
    myPara.SourceColumn = dtl.Columns[i].ColumnName;
    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].ColumnName;
     myPara1.DbType = GetDbType(dtl.Columns[i].DataType);
     myPara1.Direction = ParameterDirection.Input;
     myPara1.SourceColumn = dtl.Columns[i].ColumnName;
     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

  #region insertCmd

  private static OleDbCommand  createInsertCommand(DataTable dtl)
  {
   OleDbCommand insertCmd=new OleDbCommand();
   insertCmd.Connection=cnn;
    
   string sqlStr="INSERT INTO " + dtl.TableName.ToString() + "(";
   
   //access数据库
   if(isAccessDB)
   {
     
    for(int i=0;i<dtl.Columns.Count;i++)
    {
     sqlStr=sqlStr+dtl.Columns[i].ColumnName+",";

     OleDbParameter myPara = new OleDbParameter();
     myPara.ParameterName = dtl.Columns[i].ColumnName;
     myPara.DbType = GetDbType(dtl.Columns[i].DataType);
     myPara.SourceColumn = dtl.Columns[i].ToString();

     insertCmd.Parameters.Add(myPara);

    }
    sqlStr=sqlStr.Substring(0,sqlStr.Length-1)+") VALUES(";

    for(int i=0;i<dtl.Columns.Count;i++)
    {
     sqlStr=sqlStr+"?,";
    }
    sqlStr=sqlStr.Substring(0,sqlStr.Length-1)+")";
   }
   else            
   {
    //先判断表中是否有Identity列
    DataTable dtlTemp=operateDB.execSql("Select name from syscolumns Where autoval Is Not Null And id "+
     "= object_id('"+dtl.TableName+"')");

    for(int i=0;i<dtl.Columns.Count;i++)
    {
     if(!(dtlTemp.Rows.Count>0) || dtlTemp.Rows[0][0].ToString()!=dtl.Columns[i].ColumnName)
     {
      sqlStr=sqlStr+dtl.Columns[i].ColumnName+",";

      OleDbParameter myPara = new OleDbParameter();
      myPara.ParameterName = dtl.Columns[i].ColumnName;
      myPara.DbType = GetDbType(dtl.Columns[i].DataType);
      myPara.SourceColumn = dtl.Columns[i].ToString();

      insertCmd.Parameters.Add(myPara);
     }
    }
    sqlStr=sqlStr.Substring(0,sqlStr.Length-1)+") VALUES(";

    for(int i=0;i<dtl.Columns.Count;i++)
    {
     if(!(dtlTemp.Rows.Count>0) || dtlTemp.Rows[0][0].ToString()!=dtl.Columns[i].ColumnName)
      sqlStr=sqlStr+"?,";
    }
    sqlStr=sqlStr.Substring(0,sqlStr.Length-1)+")";

    insertCmd.CommandText = sqlStr;
   }

   insertCmd.CommandText = sqlStr;

   return insertCmd;

  }
   
  private OleDbCommand  生成InsertCommand(DataTable dtl)
  {
   OleDbCommand insertCmd=new OleDbCommand();
   insertCmd.Connection=cnn1;
    
   string sqlStr="INSERT INTO " + dtl.TableName.ToString() + "(";
   
   //access数据库
   if(isAccessDB)
   {
     
    for(int i=0;i<dtl.Columns.Count;i++)
    {
     sqlStr=sqlStr+dtl.Columns[i].ColumnName+",";

     OleDbParameter myPara = new OleDbParameter();
     myPara.ParameterName = dtl.Columns[i].ColumnName;
     myPara.DbType = GetDbType(dtl.Columns[i].DataType);
     myPara.SourceColumn = dtl.Columns[i].ToString();

     insertCmd.Parameters.Add(myPara);

    }
    sqlStr=sqlStr.Substring(0,sqlStr.Length-1)+") VALUES(";

    for(int i=0;i<dtl.Columns.Count;i++)
    {
     sqlStr=sqlStr+"?,";
    }
    sqlStr=sqlStr.Substring(0,sqlStr.Length-1)+")";
   }
   else            
   {
    //先判断表中是否有Identity列
    DataTable dtlTemp=operateDB.execSql("Select name from syscolumns Where autoval Is Not Null And id "+
     "= object_id('"+dtl.TableName+"')");

    for(int i=0;i<dtl.Columns.Count;i++)
    {
     if(!(dtlTemp.Rows.Count>0) || dtlTemp.Rows[0][0].ToString()!=dtl.Columns[i].ColumnName)
     {
      sqlStr=sqlStr+dtl.Columns[i].ColumnName+",";

      OleDbParameter myPara = new OleDbParameter();
      myPara.ParameterName = dtl.Columns[i].ColumnName;
      myPara.DbType = GetDbType(dtl.Columns[i].DataType);
      myPara.SourceColumn = dtl.Columns[i].ToString();

      insertCmd.Parameters.Add(myPara);
     }
    }
    sqlStr=sqlStr.Substring(0,sqlStr.Length-1)+") VALUES(";

    for(int i=0;i<dtl.Columns.Count;i++)
    {
     if(!(dtlTemp.Rows.Count>0) || dtlTemp.Rows[0][0].ToString()!=dtl.Columns[i].ColumnName)
      sqlStr=sqlStr+"?,";
    }
    sqlStr=sqlStr.Substring(0,sqlStr.Length-1)+")";

    insertCmd.CommandText = sqlStr;
   }

   insertCmd.CommandText = sqlStr;

   return insertCmd;

  }

  #endregion

  
  /// <summary>
  /// 插入、修改、删除
  /// </summary>
  /// <param name="tblName">要更新的DataTable</param>
  /// <returns>影响的行数</returns>
  public static int update(DataTable tblName)
  {
   int i=0;
   OleDbDataAdapter daUp=new OleDbDataAdapter();

   try
   {

    daUp.UpdateCommand=createUpdateCommand(tblName);
    daUp.DeleteCommand=createDelCommand(tblName);
    daUp.InsertCommand=createInsertCommand(tblName);

     
    i=daUp.Update(tblName);
   }
   catch
   {
    i=-1;
   }

   return i;
  }

  public int 更新到数据库(DataTable tblName)
  {
   int i=0;
   OleDbDataAdapter daUp=new OleDbDataAdapter();

   try
   {

    daUp.UpdateCommand=this.生成UpdateCommand(tblName);
    daUp.DeleteCommand=this.生成DelCommand(tblName);
    daUp.InsertCommand=this.生成InsertCommand(tblName);
    i=daUp.Update(tblName);
   }
   catch
   {
    i=-1;
   }

   return i;
  }

  #endregion

  #region 构造Adapter更新到数据库(DataSet)

  public static int update(DataSet dsName)
  {
   int i=0;
   OleDbDataAdapter[] daUp=new OleDbDataAdapter[dsName.Tables.Count];

   for(int j=0;j<daUp.Length;j++)
   {
    daUp[j]=new OleDbDataAdapter();
    daUp[j].UpdateCommand=createUpdateCommand(dsName.Tables[j]);
    daUp[j].DeleteCommand=createDelCommand(dsName.Tables[j]);
    daUp[j].InsertCommand=createInsertCommand(dsName.Tables[j]);

    i+=daUp[j].Update(dsName.Tables[j]);
   }

   return i;
    
  }
  
  public int 更新到数据库(DataSet dsName)
  {
   int i=0;
   OleDbDataAdapter[] daUp=new OleDbDataAdapter[dsName.Tables.Count];

   for(int j=0;j<daUp.Length;j++)
   {
    daUp[j]=new OleDbDataAdapter();
    daUp[j].UpdateCommand=this.生成UpdateCommand(dsName.Tables[j]);
    daUp[j].DeleteCommand=this.生成DelCommand(dsName.Tables[j]);
    daUp[j].InsertCommand=this.生成InsertCommand(dsName.Tables[j]);

    i+=daUp[j].Update(dsName.Tables[j]);
   }

   return i;
    
  }

  #endregion

  #region 构造Adapter更新到数据库(事务处理)
  
  #region selectCmd
  /// <summary>
  /// 构造selectCommand对象
  /// </summary>
  /// <param name="strSql">查询的SQL命令</param>
  /// <returns>OleDbCommand对象</returns>
  public OleDbCommand createSelectCmd(string strSql)
  {
   OleDbCommand selectCmd=new OleDbCommand();
   selectCmd.Connection=cnn;
   selectCmd.CommandText=strSql;
   return selectCmd;

  }
  #endregion 

  //事物处理时identity列不能通过SQL语句动态判断，所以重新构造insertCmd
  #region insertCmd(事物处理)

  /// <summary>
  /// 生成用于事务处理中的InsertCommand
  /// </summary>
  /// <param name="dtl">DataTable</param>
  /// <param name="identityColumnName">identity列名</param>
  /// <returns>OleDbCommand</returns>
  private static OleDbCommand createInsertCommand(DataTable dtl,string identityColumnName)
  {
   OleDbCommand insertCmd=new OleDbCommand();
   insertCmd.Connection=cnn;
    
   string sqlStr="INSERT INTO " + dtl.TableName.ToString() + "(";

   for(int i=0;i<dtl.Columns.Count;i++)
   {
    if(dtl.Columns[i].ColumnName!=identityColumnName)
    {
     sqlStr=sqlStr+dtl.Columns[i].ColumnName+",";

     OleDbParameter myPara = new OleDbParameter();
     myPara.ParameterName = dtl.Columns[i].ColumnName;
     myPara.DbType = GetDbType(dtl.Columns[i].DataType);
     myPara.SourceColumn = dtl.Columns[i].ToString();

     insertCmd.Parameters.Add(myPara);
    }
   }
   sqlStr=sqlStr.Substring(0,sqlStr.Length-1)+") VALUES(";

   for(int i=0;i<dtl.Columns.Count;i++)
   {
    if(dtl.Columns[i].ColumnName!=identityColumnName)
     sqlStr=sqlStr+"?,";
   }
   sqlStr=sqlStr.Substring(0,sqlStr.Length-1)+")";

   insertCmd.CommandText = sqlStr;

   return insertCmd;

  }
  #endregion

  #region CREATEADAPTER
  /// <summary>
  /// 生成Adapter的查询、插入、修改、删除command对象
  /// </summary>
  /// <param name="tblName">DataTable</param>
  /// <param name="trs">事务处理的OleDbTransaction对象</param>
  /// <param name="selectSql">查询表的SQL命令</param>
  /// <param name="IdenColumnName">表中identity列的名字</param>
  /// <returns>OleDbDataAdapter对象</returns>
  public OleDbDataAdapter createAdapter(DataTable tblName,OleDbTransaction trs,string selectSql,string IdenColumnName)
  {
   OleDbDataAdapter daUp=new OleDbDataAdapter();

   try
   {
    daUp.SelectCommand=createSelectCmd(selectSql);
    daUp.SelectCommand.Transaction=trs;
    daUp.Fill(tblName);
    daUp.UpdateCommand=createUpdateCommand(tblName);
    daUp.UpdateCommand.Transaction=trs;
    daUp.DeleteCommand=createDelCommand(tblName);
    daUp.DeleteCommand.Transaction=trs;
    daUp.InsertCommand=createInsertCommand(tblName,IdenColumnName);
    daUp.InsertCommand.Transaction=trs;
   }
   catch
   {

   }

   return daUp;
  }
  #endregion

  #endregion

  //插入记录
  #region insert(use InsideTransaction,DataTable[])
  
  /// <summary>
  /// 同时更新多表
  /// </summary>
  /// <param name="tbls">数据表集</param>
  /// <param name="newRows">插入行集</param>
  /// <returns></returns>
  public static 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=createInsertCommand(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 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>
  /// 执行存储过程
  /// </summary>
  /// <param name="procName">存储过程名字</param>
  /// <param name="ParaValue">参数的值</param>
  /// <param name="ParaName">参数名字</param>
  /// <param name="ParaType">参数的类型</param>
  /// <returns></returns>
  public string ExecProc(string procName,string[] ParaValue,string[] ParaName,string[] ParaType)
  {
   OleDbCommand cmdp=new OleDbCommand();
   cmdp.Connection=cnn;
   cmdp.CommandType=CommandType.StoredProcedure;
   cmdp.CommandText=procName;
  
   string strOut="";

   for (int i=0;i<ParaValue.Length;i++)
   {
    OleDbParameter pt=new OleDbParameter();

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

    pt.DbType=GetDbType(ParaType[i].GetType());

    pt.Value=ParaValue[i];

    pt.Direction=ParameterDirection.Input;

    cmdp.Parameters.Add(pt);
   
   }
   if((ParaName.Length-ParaValue.Length)==1 && ParaType.Length==ParaName.Length)
   {
    OleDbParameter pt=new OleDbParameter();

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

    pt.DbType=GetDbType(ParaType[ParaName.Length-1].GetType());

    pt.Direction=ParameterDirection.Output;

    cmdp.Parameters.Add(pt);
   }

   cnn.Open();
   
   DataTable dtl=new DataTable();
   da.SelectCommand=cmdp;
   da.RowUpdated+=new OleDbRowUpdatedEventHandler(da_RowUpdated);
   da.Fill(dtl);

   da.Update(dtl);

   if((ParaName.Length-ParaValue.Length)==1 && ParaType.Length==ParaName.Length)
    strOut=cmdp.Parameters[ParaName.Length-1].Value.ToString();

   cnn.Close();
   return strOut;
   
  }

  private static System.Data.DbType GetDbType(Type type)
  {
   DbType result = DbType.String;
   if( type.Equals(typeof(int)) ||  type.IsEnum)
    result = DbType.Int32;
   else if( type.Equals(typeof(long)))
    result = DbType.Int32;
   else if( type.Equals(typeof(double)) || type.Equals( typeof(Double)))
    result = DbType.Decimal;
   else if( type.Equals(typeof(DateTime)))
    result = DbType.DateTime;
   else if( type.Equals(typeof(bool)))
    result = DbType.Boolean;
   else if( type.Equals(typeof(string) ) )
    result = DbType.String;
   else if( type.Equals(typeof(decimal)))
    result = DbType.Decimal;
   else if( type.Equals(typeof(byte[])))
    result = DbType.Binary;
   else if( type.Equals(typeof(Guid)))
    result = DbType.Guid;       
   return result;
  }

  private static bool checkColumnName(string colName)
  {
   if(colName.IndexOfAny(new char[] {'(',')','/'','/"'})>0)
   {
    //    MessageBox.Show("列名包含非法字符！");
    return false;
   }
   return true;
  }

  private void da_RowUpdated(object sender, OleDbRowUpdatedEventArgs e)
  {
   if((e.Status==UpdateStatus.Continue) && (e.StatementType==StatementType.Insert))
   {
    e.Row["编号"]=(int)cmdGetIdentity.ExecuteScalar();
    e.Row.AcceptChanges();
   }
  }

  #endregion execProc(return dataTable)

  //修改数据库结构
  #region amendDataBase
  /// <summary>
  /// 修改数据库表的结构
  /// </summary>
  /// <param name="strSql">SQL命令</param>
  public  void alterTable(string strSql) //修改表的结构,更新到数据库
  {
   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);
   cmd.CommandText=strSql;
   cmd.ExecuteNonQuery();
  }
  
  #endregion amendDataBase

  //操作数据库对象
  #region operateDataBase(DDL)

  /// <summary>
  /// 附加数据库
  /// </summary>
  /// <param name="strCon">连接字符串</param>
  /// <param name="DBName">要生成的数据库名</param>
  /// <param name="strMdf">mdf文件位置</param>
  /// <param name="strLdf">ldf文件位置</param>
  /// <returns></returns>
  public bool attachDB(string strCon,string DBName,string strMdf,string strLdf)
  {
   bool bl=true;
   try
   {
    cnn.ConnectionString=strCon;
    if (cnn.State!=ConnectionState.Open)
     cnn.Open();
   
    cmd.Connection=cnn;
    cmd.CommandText=@"sp_attach_db";
    cmd.Parameters.Add(new OleDbParameter("@dbname",OleDbType.VarChar));
    cmd.Parameters["@dbname"].Value=DBName;
    cmd.Parameters.Add(new OleDbParameter("@filename1",OleDbType.VarChar));
    cmd.Parameters["@filename1"].Value=strMdf;
    cmd.Parameters.Add(new OleDbParameter("@filename2",OleDbType.VarChar));
    cmd.Parameters["@filename2"].Value=strLdf;

    cmd.CommandType=CommandType.StoredProcedure;
    cmd.ExecuteNonQuery();
   }
   catch
   {
    //    MessageBox.Show(e.Message.ToString());
    bl=false;
   }
   finally
   {
    cnn.Close();
   }
   return bl;

  }
  
  /// <summary>
  /// 分离数据库
  /// </summary>
  /// <param name="strCon">连接字符串</param>
  /// <param name="DBName">数据库名</param>
  /// <returns></returns>
  public bool detachDB(string strCon,string DBName)
  {
   bool bl=true;
   try
   {
    cnn.ConnectionString=strCon;
    if (cnn.State!=ConnectionState.Open)
     cnn.Open();
   
    cmd.Connection=cnn;
    cmd.CommandText=@"sp_detach_db";
    cmd.Parameters.Add(new OleDbParameter("@dbname",OleDbType.VarChar));
    cmd.Parameters["@dbname"].Value=DBName;

    cmd.CommandType=CommandType.StoredProcedure;
    cmd.ExecuteNonQuery();
   }
   catch
   {
    bl=false;
   }
   finally
   {
    cnn.Close();
   }
   return bl;
  }

  /// <summary>
  /// 备份数据库
  /// </summary>
  /// <param name="strCon">连接字符串</param>
  /// <param name="DBName">数据库名</param>
  /// <param name="backupName">备份名</param>
  /// <param name="backupFile">备份文件位置</param>
  /// <returns></returns>
  public bool BackUpDB(string strCon,string DBName,string backupName,string backupFile)
  {
   bool bl=true;
   try
   {
    cnn.ConnectionString=strCon;
    if (cnn.State!=ConnectionState.Open)
     cnn.Open();

    cmd.Connection=cnn;
    cmd.CommandText=@"BACKUP DATABASE "+DBName+" to disk='"+backupFile+"'"+
     "WITH  NOINIT ,  NOUNLOAD ,  NAME = N'"+backupName+"',  NOSKIP ,  STATS = 10,  NOFORMAT ";
    cmd.CommandType=CommandType.Text;
    cmd.ExecuteNonQuery();
   }
   catch
   {
    bl=false;
   }
   finally
   {
    cnn.Close();
   }
   return bl;
  }

  /// <summary>
  /// 还原数据库
  /// </summary>
  /// <param name="strCon">连接字符串</param>
  /// <param name="DBName">数据库名</param>
  /// <param name="backupFile">备份文件位置</param>
  /// <returns></returns>
  public bool restoreDB(string strCon,string DBName,string backupFile)
  {
   bool bl=true;
   try
   {
    cnn.ConnectionString=strCon;
    if (cnn.State!=ConnectionState.Open)
     cnn.Open();

    cmd.Connection=cnn;
    cmd.CommandText=@"RESTORE FILELISTONLY from disk='"+backupFile+"'";
    cmd.CommandType=CommandType.Text;
    cmd.ExecuteNonQuery();
   }
   catch
   {
    bl=false;
   }
   finally
   {
    cnn.Close();
   }
   return bl;
  }

  public static void DbBackup(string strHost,string strUserName,string strUserPwd,string strDBName,string strDBBakName)
  {
   SQLDMO.Backup oBackup = new SQLDMO.BackupClass();
   SQLDMO.SQLServer oSQLServer = new SQLDMO.SQLServerClass();
   try
   {
    oSQLServer.LoginSecure = false;
    oSQLServer.Connect(strHost,strUserName,strUserPwd);
    oBackup.Action = SQLDMO.SQLDMO_BACKUP_TYPE.SQLDMOBackup_Database;
    oBackup.Database = strDBName;
    oBackup.Files = strDBBakName;
    oBackup.BackupSetName = strDBName;
    oBackup.BackupSetDescription = "数据库备份";
    oBackup.Initialize = true;
    oBackup.SQLBackup(oSQLServer);
   }
   catch
   {
    throw;
   }
   finally
   {
    oSQLServer.DisConnect();
   }
  }
  ///
  /// 数据库恢复
  ///
  public static void DbRestore(string strHost,string strUserName,string strUserPwd,string strDBName,string strDBBakName)
  {
   SQLDMO.Restore oRestore = new SQLDMO.RestoreClass();
   SQLDMO.SQLServer oSQLServer = new SQLDMO.SQLServerClass();
   try
   {
    oSQLServer.LoginSecure = false;
    oSQLServer.Connect(strHost, strUserName,strUserPwd);
    oRestore.Action = SQLDMO.SQLDMO_RESTORE_TYPE.SQLDMORestore_Database;
    oRestore.Database = strDBName;
    oRestore.Files = strDBBakName;
    oRestore.FileNumber = 1;
    oRestore.ReplaceDatabase = true;
    oRestore.SQLRestore(oSQLServer);
   }
   catch
   {
    throw;
   }
   finally
   {
    oSQLServer.DisConnect();
   }
  }

  #endregion
 
 }
}