SqlDataHelperr

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

public class SqlDataHelper
{
    private SqlConnection connection;
    private SqlCommand command;
    private DataTable table;
    private SqlDataReader reader;
    private int count;
    private SqlTransaction transaction;

    /// <summary>
    /// 打开数据库连接
    /// </summary>
    /// <returns>SqlConnection 对象</returns>
    private SqlConnection OpenConnection()
    {
        if (connection == null)
        {
            connection = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
        }
        if (connection.State == ConnectionState.Closed)
        {
            connection.Open();
        }
        return connection;
    }

    /// <summary>
    /// 关闭数据库连接
    /// </summary>
    private void CloseConnection()
    {
        if (connection != null && connection.State == ConnectionState.Open)
        {
            connection.Close();
            connection = null;
        }
    }

    /// <summary>
    /// 开始事务
    /// </summary>
    /// <returns>SqlTransaction 对象</returns>
    public void BeginTransaction()
    {
        transaction = this.OpenConnection().BeginTransaction();
    }

    /// <summary>
    /// 提交事务
    /// </summary>
    public void CommitTransaction()
    {
        transaction.Commit();
        this.CloseConnection();
    }

    /// <summary>
    /// 回滚事务
    /// </summary>
    public void RollbackTransaction()
    {
        transaction.Rollback();
        this.CloseConnection();
    }

    /// <summary>
    /// 根据传入的sql语句或存储过程返回一个DataTable
    /// </summary>
    /// <param Name="sqlString">sql语句或存储过程</param>
    /// <param Name="type">CommandType 类型</param>
    /// <param Name="parames">sql语句或存储过程参数</param>
    /// <returns>DataTable对象</returns>
    public DataTable ExecuteDataTable(string sqlString, CommandType type, SqlParameter[] parames)
    {
        if (!string.IsNullOrEmpty(sqlString))
        {
            command = new SqlCommand(sqlString, this.OpenConnection());
            command.CommandType = type;
            if (parames != null)
            {
                foreach (SqlParameter parame in parames)
                {
                    command.Parameters.Add(parame);
                }
            }
            reader = command.ExecuteReader();
            table = new DataTable();
            table.Load(reader);
            reader.Close();
            command.Parameters.Clear();
            this.CloseConnection();
        }
        return table;
    }

    /// <summary>
    /// 返回一个SqlDataReader，读取完数据后请将SqlDataReader关闭
    /// </summary>
    /// <param Name="sqlString">sql语句或存储过程</param>
    /// <param Name="type">CommandType 类型</param>
    /// <param Name="parames">sql语句或存储过程参数</param>
    /// <returns>SqlDataReader对象</returns>
    public SqlDataReader ExecuteDataReader(string sqlString, CommandType type, SqlParameter[] parames)
    {
        if (!string.IsNullOrEmpty(sqlString))
        {
            command = new SqlCommand(sqlString, this.OpenConnection());
            command.CommandType = type;
            if (parames != null)
            {
                foreach (SqlParameter parame in parames)
                {
                    command.Parameters.Add(parame);
                }
            }
            reader = command.ExecuteReader(CommandBehavior.CloseConnection);
        }
        return reader;
    }

    /// <summary>
    /// 返回insert、delete、update语句影响的行数，0失败，非0成功
    /// </summary>
    /// <param Name="sqlString">sql语句或存储过程</param>
    /// <param Name="type">CommandType 类型</param>
    /// <param Name="parames">sql语句或存储过程参数</param>
    /// <returns>int 变量</returns>
    public int ExecuteNonQuery(string sqlString, CommandType type, SqlParameter[] parames)
    {
        if (!string.IsNullOrEmpty(sqlString))
        {
            command = new SqlCommand(sqlString, this.OpenConnection());
            command.CommandType = type;
            if (parames != null)
            {
                foreach (SqlParameter parame in parames)
                {
                    command.Parameters.Add(parame);
                }
            }
            count = command.ExecuteNonQuery();
            command.Parameters.Clear();
            this.CloseConnection();
        }
        return count;
    }

    /// <summary>
    /// 返回insert、delete、update语句影响的行数，0失败，非0成功,带事务
    /// </summary>
    /// <param Name="sqlString">sql语句或存储过程</param>
    /// <param Name="type">CommandType 类型</param>
    /// <param Name="parames">sql语句或存储过程参数</param>
    /// <returns>int 变量</returns>
    public int ExecuteNonQueryTransaction(string sqlString, CommandType type, SqlParameter[] parames)
    {
        if (!string.IsNullOrEmpty(sqlString))
        {
            command = new SqlCommand(sqlString, this.OpenConnection());
            command.CommandType = type;
            command.Transaction = this.transaction;
            if (parames != null)
            {
                foreach (SqlParameter parame in parames)
                {
                    command.Parameters.Add(parame);
                }
            }
            count = command.ExecuteNonQuery();
            command.Parameters.Clear();
        }
        return count;
    }

    /// <summary>
    /// 返回select查询的第一行第一列的值
    /// </summary>
    /// <param Name="sqlString"></param>
    /// <param Name="type"></param>
    /// <param Name="parames"></param>
    /// <returns></returns>
    public object ExecuteScalar(string sqlString, CommandType type, SqlParameter[] parames)
    {
        object obj = null;
        if (!string.IsNullOrEmpty(sqlString))
        {
            command = new SqlCommand(sqlString, this.OpenConnection());
            command.CommandType = type;
            if (parames != null)
            {
                foreach (SqlParameter parame in parames)
                {
                    command.Parameters.Add(parame);
                }
            }
            obj = command.ExecuteScalar();
            command.Parameters.Clear();
            this.CloseConnection();
        }
        return obj;
    }

    /// <summary>
    /// 返回select查询的第一行第一列的值
    /// </summary>
    /// <param Name="sqlString"></param>
    /// <param Name="type"></param>
    /// <param Name="parames"></param>
    /// <returns></returns>
    public object ExecuteScalarTransaction(string sqlString, CommandType type, SqlParameter[] parames)
    {
        object obj = null;
        if (!string.IsNullOrEmpty(sqlString))
        {
            command = new SqlCommand(sqlString, this.OpenConnection());
            command.CommandType = type;
            command.Transaction = transaction;
            if (parames != null)
            {
                foreach (SqlParameter parame in parames)
                {
                    command.Parameters.Add(parame);
                }
            }
            obj = command.ExecuteScalar();
            command.Parameters.Clear();
        }
        return obj;
    }

    /// <summary>
    /// 返回数据集
    /// </summary>
    /// <param Name="sqlString"></param>
    /// <param Name="type"></param>
    /// <param Name="parames"></param>
    /// <returns></returns>
    public DataSet ExecuteDataSet(string sqlString, CommandType type, SqlParameter[] parames)
    {
        DataSet ds = new DataSet();
        if (!string.IsNullOrEmpty(sqlString))
        {
            command = new SqlCommand(sqlString, this.OpenConnection());
            command.CommandType = type;
            if (parames != null)
            {
                foreach (SqlParameter parame in parames)
                {
                    command.Parameters.Add(parame);
                }
            }
            SqlDataAdapter sda = new SqlDataAdapter();
            sda.SelectCommand = command;

            sda.Fill(ds);

            this.CloseConnection();
        }
        return ds;
    }
}