备忘录（二）ADO.net通用类库之连接模型

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

namespace DataCommon
{
    /// <summary>
    /// ADO.net通用类库
    /// </summary>
    public class Common : IDisposable
    {
        SqlConnection conn;
        SqlCommand command;
        SqlDataReader reader;

        #region 公共方法
        /// <summary>
        /// SqlCommand中添加参数
        /// </summary>
        /// <param name="command">Sqlcommand对象</param>
        /// <param name="sqlparams">Sqlparameter对象集合</param>
        private void AddParams(SqlCommand command, params SqlParameter[] sqlparams)
        {
            if (sqlparams != null)
            {
                foreach (SqlParameter p in sqlparams)
                {
                    command.Parameters.Add(p);
                }
            }
        }
        /// <summary>
        /// 获取SqlCommand对象
        /// </summary>
        /// <param name="Sql">Sql文本或存储过程名称</param>
        /// <param name="commandtype">SqlCommand对象的类型</param>
        /// <param name="sqlparams">Sqlparameter参数集合</param>
        /// <returns></returns>
        public SqlCommand GetCommand(string Sql, CommandType commandtype, params SqlParameter[] sqlparams)
        {
          
            SqlCommand command = new SqlCommand(Sql, conn);
            command.CommandType = commandtype;
            this.AddParams(command, sqlparams);
            return command;
        }
        #endregion

        #region 连接模型实现

        #region 查询实现
        /// <summary>
        /// SqlDataReader的实现
        /// </summary>
        /// <param name="strconn">数据库连接字符串</param>
        /// <param name="Sql">Sql文本或存储过程</param>
        /// <param name="commandtype">SqlCommand的类型</param>
        /// <param name="sqlparams">Sqlparameter参数集合</param>
        /// <returns>SqlDataReader</returns>
        public SqlDataReader GetDataReader(string strconn, string Sql, CommandType commandtype, params SqlParameter[] sqlparams)
        {
            conn = new SqlConnection(strconn);
            try
            {
                conn.Open();
                command = this.GetCommand(Sql, commandtype, sqlparams);
                reader = command.ExecuteReader(CommandBehavior.CloseConnection);
                return reader;
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            catch (Exception ex)
            {
                throw ex;
            }

        }
        /// <summary>
        /// 查询结果集返回一行一列
        /// </summary>
        /// <param name="strconn">数据库连接字符串</param>
        /// <param name="sql">Sql文本或存储过程</param>
        /// <param name="commandtype">Sqlcommand对象类型</param>
        /// <param name="sqlparams">Sqlparameter对象集合</param>
        /// <returns>Object</returns>
        public object GetScalar(string strconn, string sql, CommandType commandtype, params SqlParameter[] sqlparams)
        {
            try
            {
                using (conn = new SqlConnection(strconn))
                {
                    command = this.GetCommand(sql, commandtype, sqlparams);
                    conn.Open();
                    return command.ExecuteScalar();

                }
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            catch (Exception ex)
            {
                throw ex;
            }

        }
        #endregion

        #region 命令执行
        /// <summary>
        /// 执行数据库命令
        /// </summary>
        /// <param name="strconn">数据库连接字符串</param>
        /// <param name="Sql">Sql文本或存储过程</param>
        /// <param name="commandtype">Sqlcommand对象类型</param>
        /// <param name="Isclearpooling">是否清空连接池</param>
        /// <param name="sqlparams">Sqlparameter对象集合</param>
        /// <returns>Int32</returns>
        public int ExecuteCommand(string strconn, string Sql, CommandType commandtype, bool Isclearpooling, params SqlParameter[] sqlparams)
        {
            try
            {
                using (conn = new SqlConnection(strconn))
                {
                    command = this.GetCommand(Sql, commandtype, sqlparams);

                    conn.Open();
                    if (Isclearpooling)
                    {
                        SqlConnection.ClearAllPools();
                        SqlConnection.ClearPool(conn);
                    }
                    return command.ExecuteNonQuery();

                }
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion

        #region 事务处理
        /// <summary>
        /// ADO.net事务处理
        /// </summary>
        /// <param name="connstring">数据库连接字符串</param>
        /// <param name="commands">SqlCommand对象集合</param>
        public void DataBaseTransaction(string connstring, params SqlCommand[] commands)
        {
            SqlTransaction trans = null;
            try
            {

                using (conn = new SqlConnection(connstring))
                {
                    conn.Open();
                    using (trans = conn.BeginTransaction())
                    {
                        foreach (SqlCommand command in commands)
                        {
                            if (command != null)
                            {
                                command.Connection = conn;
                                command.Transaction = trans;
                                command.ExecuteNonQuery();

                            }
                        }
                        trans.Commit();

                    }
                }
            }
            catch (SqlException ex)
            {
                trans.Rollback();
                throw ex;
            }
            catch (Exception ex)
            {
                trans.Rollback();
                throw ex;
            }
        }
        /// <summary>
        /// 分布式显示事务处理
        /// </summary>
        /// <param name="connstring">数据库连接字符串</param>
        /// <param name="commands">SqlCommand命令集合</param>
        public void ShowTransaction(string connstring, params SqlCommand[] commands)
        {
            using (conn = new SqlConnection(connstring))
            {
                CommittableTransaction trans = new CommittableTransaction();
                try
                {
                    conn.Open();
                    conn.EnlistTransaction(trans);
                    foreach (SqlCommand command in commands)
                    {
                        if (command != null)
                        {
                            command.Connection = conn;
                            command.ExecuteNonQuery();
                        }
                    }
                    trans.Commit();

                }
                catch (SqlException ex)
                {
                    trans.Rollback();
                    throw ex;

                }
                catch (Exception ex)
                {
                    trans.Rollback();
                    throw ex;

                }

            }

        }
        /// <summary>
        /// 分布式隐式事务处理
        /// 必须启动MSDTC分布式服务
        /// </summary>
        /// <param name="connstring">数据库连接字符串</param>
        /// <param name="commands">Sqlcommand对象集合</param>
        public void HiddenTransaction(string connstring, params SqlCommand[] commands)
        {
            try
            {
                using (TransactionScope trans = new TransactionScope())
                {
                    using (conn = new SqlConnection(connstring))
                    {
                        conn.Open();
                        foreach (SqlCommand command in commands)
                        {
                            if (command != null)
                            {
                                command.Connection = conn;
                                command.ExecuteNonQuery();
                            }
                        }
                        trans.Complete();

                    }
                }
            }
            catch (SqlException ex)
            {
                throw ex;

            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #endregion

        #endregion

        #region IDisposable 成员
        /// <summary>
        /// 释放资源
        /// </summary>
        public void Dispose()
        {
            if (reader != null)
            {
                reader.Dispose();
            }
            if (command != null)
            {
                command.Dispose();
                if (conn != null)
                {
                    conn.Dispose();
                }
            }
        }

        #endregion
    }
}