1.数据操作类 SqlDBhelper

using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;

namespace MessageTool
{
    public class SqlDbhelper
    {
       private SqlConnection conn;
       private SqlCommand cmd;
       private SqlDataReader reader;
       private SqlDataAdapter adapter;
       public  string connectionString =System.Configuration.ConfigurationSettings.AppSettings["connectionstring"];
 
       public string ConnectionString
       {
           get { return this.connectionString; }
           set { this.connectionString = value; }
       }
 
       ///
       /// 获取一个未打开连接的SqlConnection对象
       ///
       /// SqlConnection对象
       public SqlConnection GetConnection()
       {
           if (conn != null)
               return this.conn;
           return this.conn = new SqlConnection(connectionString);
       }
 
       ///
       /// 使用连接字符串获取未打开连接SqlConnection对象
       ///
       /// 连接字符串
       /// SqlConnection对象
       public SqlConnection GetConnection(string _connStr)
       {
           if (this.conn != null)
               this.conn.ConnectionString = _connStr;
           else
               this.conn = new SqlConnection(_connStr);
           return this.conn;
       }
 
       ///
       /// 使用指定的Sql语句创建SqlCommand对象
       ///
       /// Sql语句
       /// SqlCommand对象
       private SqlCommand GetCommand(string sqlStr)
       {
           if (this.conn == null)
               this.conn = GetConnection();
           if (this.cmd == null)
               this.cmd = this.GetCommand(sqlStr, CommandType.Text, null);
           else
           {
               this.cmd.CommandType = CommandType.Text;
               this.cmd.Parameters.Clear();
           }
           this.cmd.CommandText = sqlStr;
           return this.cmd;
       }
 
       ///
       /// 使用指定的Sql语句,CommandType,SqlParameter数组创建SqlCommand对象
       ///
       /// Sql语句
       /// 命令类型
       /// SqlParameter数组
       /// SqlCommand对象
       public SqlCommand GetCommand(string sqlStr, CommandType type,SqlParameter[] paras)
       {
           if (conn == null)
               this.conn = this.GetConnection();
           if (cmd == null)
               this.cmd = conn.CreateCommand();
           this.cmd.CommandType = type;
           this.cmd.CommandText = sqlStr;
           this.cmd.Parameters.Clear();
           if (paras != null)
               this.cmd.Parameters.AddRange(paras);
           return this.cmd;
       }
 
       ///
       /// 执行Sql语句返回受影响的行数
       ///
       /// Sql语句
       /// 受影响的行数,失败则返回-1
       public int ExecuteNoQuery(string sqlStr)
       {
           int line = -1;
           CheckArgs(sqlStr);
           try { OpenConn(); line =this.ExecuteNonQuery(sqlStr,CommandType.Text,null); }
           catch (SqlException e) { throw e; }
           return line;
       }
 
       ///
       /// 使用指定的Sql语句,CommandType,SqlParameter数组执行Sql语句,返回受影响的行数
       ///
       /// Sql语句
       /// 命令类型
       /// SqlParameter数组
       /// 受影响的行数
       public int ExecuteNonQuery(string sqlStr, CommandType type,SqlParameter[] paras)
       {
           int line = -1;
           CheckArgs(sqlStr);
           if (this.cmd == null)
               GetCommand(sqlStr, type, paras);
           this.cmd.Parameters.Clear();
           this.cmd.CommandText = sqlStr;
           this.cmd.CommandType = type;
           if(paras != null)
               this.cmd.Parameters.AddRange(paras);
           try { OpenConn(); line = this.cmd.ExecuteNonQuery(); }
           catch (SqlException e) { throw e; }
           return line;
       }
 
       ///
       /// 使用指定Sql语句获取dataTable
       ///
       /// Sql语句
       /// DataTable对象
       public DataTable GetDataTable(string sqlStr)
       {
           CheckArgs(sqlStr);
           if (this.conn == null)
               this.conn = GetConnection();
           this.adapter = new SqlDataAdapter(sqlStr, this.conn);
           DataTable table = new DataTable();
           try { adapter.Fill(table); }
           catch (SqlException e) { throw e; }
           return table;
       }
 
       ///
       /// 使用指定的Sql语句获取SqlDataReader
       ///
       /// sql语句
       /// SqlDataReader对象
       public SqlDataReader GetSqlDataReader(string sqlStr)
       {
           CheckArgs(sqlStr);
           if (cmd == null)
               GetCommand(sqlStr);
           if(reader != null)
               reader.Dispose();
           try { OpenConn(); this.reader = this.cmd.ExecuteReader(); }
           catch (SqlException e) { throw e; }
           return this.reader;
       }
 
       ///
       /// 使用事务执行多条Sql语句
       ///
       /// sql语句数组
       /// 全部成功则返回true否则返回false
       public bool ExecuteSqls(string[] sqlCommands)
       {
           if (sqlCommands == null)
               throw new ArgumentNullException();
           if(this.cmd == null)
               GetCommand(null);
           SqlTransaction tran = null;
           try
           {
               OpenConn();
               tran = this.conn.BeginTransaction();
               this.cmd.Transaction = tran;
               foreach (string sql in sqlCommands)
               {
                   if (ExecuteNoQuery(sql) == 0)
                   { tran.Rollback(); return false; }
               }
           }
           catch (SqlException e)
           {
               if(tran != null)
                   tran.Rollback();
               throw e;
           }
           tran.Commit();
           return true;
       }
 
       private void OpenConn()
       {
           try
           {
               if (this.conn.State == ConnectionState.Closed)
                   conn.Open();
           }
           catch (SqlException e) { throw e; }
       }
 
       private void CheckArgs(string sqlStr)
       {
           if (sqlStr == null)
               throw new ArgumentNullException();
           if (sqlStr.Length == 0)
               throw new ArgumentOutOfRangeException();
       }
 
    }
}