封装的ADO.NET对数据库操作经典类

using System; using System.Collections; using System.Collections.Specialized; using System.Runtime.Remoting.Messaging; using System.Data; using System.Data.SqlClient; using System.Configuration; namespace LTP.SQLServerDAL {  /// <summary>  /// ADO.NET数据库操作基础类。  /// </summary>  public abstract class DbManagerSQL  {   //数据库连接字符串   protected static string connectionString = ConfigurationSettings.AppSettings["ConnectionString"];    public DbManagerSQL()   {    //    // TODO: 在此处添加构造函数逻辑    //   }   /// <summary>   /// 执行SQL语句，返回影响的记录数   /// </summary>   /// <param name="SQLString"></param>   /// <returns></returns>   public static int ExecuteSql(string SQLString)   {    using (SqlConnection connection = new SqlConnection(connectionString))    {         using (SqlCommand cmd = new SqlCommand(SQLString,connection))     {      try      {         connection.Open();       int rows=cmd.ExecuteNonQuery();       return rows;      }      catch(System.Data.SqlClient.SqlException E)      {           throw new Exception(E.Message);      }     }        }   }   /// <summary>   /// 执行两条SQL语句，实现数据库事务。   /// </summary>   /// <param name="SQLString1"></param>   /// <param name="SQLString2"></param>   public static void ExecuteSqlTran(string SQLString1,string SQLString2)   {    using (SqlConnection connection = new SqlConnection(connectionString))    {     connection.Open();     SqlCommand cmd = new SqlCommand();     cmd.Connection=connection;         SqlTransaction tx=connection.BeginTransaction();        cmd.Transaction=tx;         try     {           cmd.CommandText=SQLString1;      cmd.ExecuteNonQuery();      cmd.CommandText=SQLString2;      cmd.ExecuteNonQuery();           tx.Commit();          }     catch(System.Data.SqlClient.SqlException E)     {        tx.Rollback();      throw new Exception(E.Message);     }     finally     {      cmd.Dispose();      connection.Close();     }     }   }    /// <summary>   /// 执行多条SQL语句，实现数据库事务，每条语句以“;”分割。   /// </summary>   /// <param name="SQLStringList"></param>   public static void ExecuteSqlTran(string SQLStringList)   {    using (OdbcConnection conn = new OdbcConnection(connectionString))    {     conn.Open();     OdbcCommand cmd = new OdbcCommand();     cmd.Connection=conn;         OdbcTransaction tx=conn.BeginTransaction();        cmd.Transaction=tx;         try     {        string [] split= SQLStringList.Split(new Char [] { ';'});      foreach (string strsql in split)      {       if (strsql.Trim()!="")       {        cmd.CommandText=strsql;        cmd.ExecuteNonQuery();       }      }             tx.Commit();          }     catch(System.Data.Odbc.OdbcException E)     {        tx.Rollback();      throw new Exception(E.Message);     }    }   }   /// <summary>   /// 执行带一个存储过程参数的的SQL语句。   /// </summary>   /// <param name="SQLString"></param>   /// <param name="content"></param>   /// <returns></returns>   public static int ExecuteSql(string SQLString,string content)   {        using (SqlConnection connection = new SqlConnection(connectionString))    {     SqlCommand cmd = new SqlCommand(SQLString,connection);       System.Data.SqlClient.SqlParameter  myParameter = new System.Data.SqlClient.SqlParameter ( "@content", SqlDbType.NText);     myParameter.Value = content ;     cmd.Parameters.Add(myParameter);     try     {      connection.Open();      int rows=cmd.ExecuteNonQuery();      return rows;     }     catch(System.Data.SqlClient.SqlException E)     {          throw new Exception(E.Message);     }     finally     {      cmd.Dispose();      connection.Close();     }     }   }     /// <summary>   /// 向数据库里插入图像格式的字段   /// </summary>   /// <param name="strSQL"></param>   /// <param name="fs"></param>   /// <returns></returns>   public static int ExecuteSqlInsertImg(string strSQL,byte[] fs)   {      using (SqlConnection connection = new SqlConnection(connectionString))    {     SqlCommand cmd = new SqlCommand(strSQL,connection);      System.Data.SqlClient.SqlParameter  myParameter = new System.Data.SqlClient.SqlParameter ( "@fs", SqlDbType.Image);     myParameter.Value = fs ;     cmd.Parameters.Add(myParameter);     try     {      connection.Open();      int rows=cmd.ExecuteNonQuery();      return rows;     }     catch(System.Data.SqlClient.SqlException E)     {          throw new Exception(E.Message);     }     finally     {      cmd.Dispose();      connection.Close();     }          }   }   /// <summary>   /// 执行一条计算查询结果语句，返回查询结果（整数）。   /// </summary>   /// <param name="strSQL"></param>   /// <returns></returns>   public static int GetCount(string strSQL)   {    using (SqlConnection connection = new SqlConnection(connectionString))    {     SqlCommand cmd = new SqlCommand(strSQL,connection);         try     {      connection.Open();      SqlDataReader result = cmd.ExecuteReader();      int i=0;      while(result.Read())      {       i=result.GetInt32(0);      }      result.Close();          return i;     }     catch(System.Data.SqlClient.SqlException e)     {              throw new Exception(e.Message);     }      finally     {      cmd.Dispose();      connection.Close();     }    }   }    /// <summary>   /// 执行一条计算查询结果语句，返回查询结果（object）。   /// </summary>   /// <param name="SQLString"></param>   /// <returns></returns>   public static object GetSingle(string SQLString)   {    using (SqlConnection connection = new SqlConnection(connectionString))    {     SqlCommand cmd = new SqlCommand(SQLString,connection);       try     {      connection.Open();      object obj = cmd.ExecuteScalar();      if((Object.Equals(obj,null))||(Object.Equals(obj,System.DBNull.Value)))      {            return null;      }      else      {       return obj;      }         }     catch(System.Data.SqlClient.SqlException e)     {          throw new Exception(e.Message);     }     finally     {      cmd.Dispose();      connection.Close();     }    }   }   /// <summary>   /// 执行查询语句，返回SqlDataReader   /// </summary>   /// <param name="strSQL"></param>   /// <returns></returns>   public static SqlDataReader ExecuteReader(string strSQL)   {    using (SqlConnection connection = new SqlConnection(connectionString))    {     SqlCommand cmd = new SqlCommand(strSQL,connection);      SqlDataReader myReader;        try     {      connection.Open();       myReader = cmd.ExecuteReader();      return myReader;     }     catch(System.Data.SqlClient.SqlException e)     {              throw new Exception(e.Message);     }       finally     {      cmd.Dispose();      connection.Close();     }     }   }     /// <summary>   /// 执行查询语句，返回DataSet   /// </summary>   /// <param name="SQLString"></param>   /// <returns></returns>   public static DataSet Query(string SQLString)   {    using (SqlConnection connection = new SqlConnection(connectionString))    {     DataSet ds = new DataSet();     try     {      connection.Open();      SqlDataAdapter command = new SqlDataAdapter(SQLString,connection);          command.Fill(ds,"ds");     }     catch(System.Data.SqlClient.SqlException ex)     {          throw new Exception(ex.Message);     }        return ds;    }       }

  #region 存储过程操作

  /// <summary>   /// 运行存储过程   /// </summary>   /// <param name="storedProcName"></param>   /// <param name="parameters"></param>   /// <returns></returns>   public static SqlDataReader RunProcedure(string storedProcName, IDataParameter[] parameters )   {    using (SqlConnection connection = new SqlConnection(connectionString))    {     SqlDataReader returnReader;     connection.Open();     SqlCommand command = BuildQueryCommand( connection,storedProcName, parameters );     command.CommandType = CommandType.StoredProcedure;

    returnReader = command.ExecuteReader();     //Connection.Close();     return returnReader;    }   }   private static SqlCommand BuildQueryCommand(SqlConnection connection,string storedProcName, IDataParameter[] parameters)   {         SqlCommand command = new SqlCommand( storedProcName, connection );     command.CommandType = CommandType.StoredProcedure;     foreach (SqlParameter parameter in parameters)     {      command.Parameters.Add( parameter );     }     return command;       }     public static DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName )   {    using (SqlConnection connection = new SqlConnection(connectionString))    {     DataSet dataSet = new DataSet();     connection.Open();     SqlDataAdapter sqlDA = new SqlDataAdapter();     sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters );     sqlDA.Fill( dataSet, tableName );     connection.Close();

    return dataSet;    }   }

  #endregion   } }