通用baseDAL类（2）

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

namespace XmlDom
{
    public class DBHelper
    {
        public static readonly string connStr = "Data Source=.;Initial Catalog=pubs;Persist Security Info=True;User ID=sa;Password=syaccp";
        private DataSet ds;
        public static T CreateInstance<T>()
        {
            return Activator.CreateInstance<T>();
        }
        #region 数据库的增删改查
        /// <summary>
        /// 验证一条数据是否存在(返回 1：存在;0：不存在;-2：出现异常)
        /// </summary>
        /// <param name="sql">要查询的T-SQL</param>
        /// <returns> int</returns>
        protected int Exists(string sql)
        {
            //用using指令,来控制SqlConnection的运用
            using (SqlConnection connection = new SqlConnection(connStr))
            {
                //用using指令,来控制SqlCommand的运用
                using (SqlCommand cmd = new SqlCommand(sql, connection))
                {
                    try
                    {
                        //打开数据库
                        connection.Open();
                        //声明一个int类型的变量,用于接收comm对象调用ExecuteScalar方法返回的值
                        int x = (int)cmd.ExecuteScalar();
                        //返回int类型的值
                        return x >= 1 ? 1 : 0;
                    }
                    catch (SqlException e)
                    {
                        //关闭数据库
                        connection.Close();
                        Console.WriteLine(e);
                        //返回-2
                        return -2;
                    }
                }
            }
        }
        /// <summary>
        /// 添加、修改、删除（一条）数据的方法
        /// </summary>
        /// <param name="sql"></param>
        /// <returns>bool类型 true:成功！ false:失败！</returns>
        protected bool DelUpAdd(string sql)
        {
            //用using指令,来控制SqlConnection的运用
            using (SqlConnection connection = new SqlConnection(connStr))
            {
                //用using指令,来控制SqlCommand的运用
                using (SqlCommand cmd = new SqlCommand(sql, connection))
                {
                    try
                    {
                        //打开数据库
                        connection.Open();
                        //声明一个int类型的变量,用于接收comm对象调用ExecuteNonQuery方法返回的值
                        int x = (int)cmd.ExecuteNonQuery();
                        //声明一个bool类型的值
                        bool fang = x >= 1 ? true : false;
                        //返回bool类型的值
                        return fang;
                    }
                    catch (SqlException e)
                    {
                        //关闭数据库
                        connection.Close();
                        Console.WriteLine(e);
                        //返回false
                        return false;
                    }
                }
            }
        }
        /// <summary>
        /// 增加一条数据(返回 2：成功;-1：失败;-2:出现异常)
        /// </summary>
        /// <param name="sql">要查询的T-SQL</param>
        /// <returns> int</returns>
        protected int Insert(string sql)
        {
            //用using指令,来控制SqlConnection的运用
            using (SqlConnection connection = new SqlConnection(connStr))
            {
                //用using指令,来控制SqlCommand的运用
                using (SqlCommand cmd = new SqlCommand(sql, connection))
                {
                    try
                    {
                        //打开数据库
                        connection.Open();
                        //声明一个int类型的变量,用于接收comm对象调用ExecuteScalar方法返回的值
                        int x = (int)cmd.ExecuteNonQuery();
                        //返回int类型的值
                        return x >= 1 ? 2 : -1;
                    }
                    catch (SqlException e)
                    {
                        //关闭数据库
                        connection.Close();
                        Console.WriteLine(e);
                        //返回-2
                        return -2;
                    }
                }
            }
        }
        /// <summary>
        /// 插入多条数据
        /// </summary>
        /// <param name="sql">要查询的T-SQL集合</param>
        /// <returns>插入数据的条数(int)</returns>
        protected int Inserts(IList<string> sql)
        {
            //用using指令,来控制SqlConnection的运用
            using (SqlConnection connection = new SqlConnection(connStr))
            {
                //用using指令,来控制SqlCommand的运用
                using (SqlCommand cmd = new SqlCommand())
                {
                    //打开数据库
                    connection.Open();
                    //给cmd对象的Connection属性赋值
                    cmd.Connection = connection;
                    //声明一个SqlTransaction的变量
                    SqlTransaction tx = connection.BeginTransaction();
                    //给cmd对象的Transaction属性赋值
                    cmd.Transaction = tx;
                    try
                    {
                        //声明一个int类型的变量,并赋值为0
                        int count = 0;
                        //用for循环迭代数据
                        for (int n = 0; n < sql.Count; n++)
                        {
                            //声明一个string类型的变量,并赋值
                            string strsql = sql[n];
                            //用if判断strsql变量是否有值
                            if (strsql.Trim().Length > 1)
                            {
                                //给cmd对象的CommandText属性赋值
                                cmd.CommandText = strsql;
                                //循环调用cod的ExecuteNonQuery方法
                                count += cmd.ExecuteNonQuery();
                            }
                        }
                        //调用tx的Commit方法
                        tx.Commit();
                        //返回int类型
                        return 3;
                    }
                    catch (SqlException e)
                    {
                        //调用tx的Rollback方法
                        tx.Rollback();
                        //关闭数据库
                        connection.Close();
                        Console.WriteLine(e);
                        //返回-2
                        return -2;
                    }
                }
            }
        }
        /// <summary>
        /// 更新一条数据(返回 2：成功;-1：失败;-2:出现异常)
        /// </summary>
        /// <param name="sql">要查询的T-SQL</param>
        /// <returns>int</returns>
        protected int Update(string sql)
        {
            //用using指令,来控制SqlConnection的运用
            using (SqlConnection connection = new SqlConnection(connStr))
            {
                //用using指令,来控制SqlCommand的运用
                using (SqlCommand cmd = new SqlCommand(sql, connection))
                {
                    try
                    {
                        //打开数据库
                        connection.Open();
                        //声明一个int类型的变量,用于接收comm对象调用ExecuteNonQuery方法返回的值
                        int x = (int)cmd.ExecuteNonQuery();
                        //返回int类型的值
                        return x >= 1 ? 2 : -1;
                    }
                    catch (SqlException e)
                    {
                        //关闭数据库
                        connection.Close();
                        Console.WriteLine(e);
                        //返回-2
                        return -2;
                    }
                }
            }
        }
        /// <summary>
        /// 更新多条数据
        /// </summary>
        /// <param name="sql">要更新的T-SQL集合</param>
        /// <returns>返回受影响的行数(int)</returns>
        protected int Updates(IList<string> sql)
        {
            //用using指令,来控制SqlConnection的运用
            using (SqlConnection connection = new SqlConnection(connStr))
            {
                //用using指令,来控制SqlCommand的运用
                using (SqlCommand cmd = new SqlCommand())
                {
                    //打开数据库
                    connection.Open();
                    //给cmd对象的Connection属性赋值
                    cmd.Connection = connection;
                    //声明一个SqlTransaction的变量
                    SqlTransaction tx = connection.BeginTransaction();
                    //给cmd对象的Transaction属性赋值
                    cmd.Transaction = tx;
                    try
                    {
                        //声明一个int类型的变量,并赋值为0
                        int count = 0;
                        //用for循环迭代数据
                        for (int n = 0; n < sql.Count; n++)
                        {
                            //声明一个string类型的变量,并赋值
                            string strsql = sql[n];
                            //用if判断strsql变量是否有值
                            if (strsql.Trim().Length > 1)
                            {
                                //给cmd对象的CommandText属性赋值
                                cmd.CommandText = strsql;
                                //循环调用cod的ExecuteNonQuery方法
                                count += cmd.ExecuteNonQuery();
                            }
                        }
                        //调用tx的Commit方法
                        tx.Commit();
                        //返回int类型
                        return 3;
                    }
                    catch (SqlException e)
                    {
                        //调用tx的Rollback方法
                        tx.Rollback();
                        //关闭数据库
                        connection.Close();
                        Console.WriteLine(e);
                        //返回-2
                        return -2;
                    }
                }
            }
        }
        /// <summary>
        /// 删除多条数据
        /// </summary>
        /// <param name="sql">要删除的T-SQL集合</param>
        /// <returns>删除数据的条数(int)</returns>
        protected int Deletes(IList<string> sql)
        {
            //用using指令,来控制SqlConnection的运用
            using (SqlConnection connection = new SqlConnection(connStr))
            {
                //用using指令,来控制SqlCommand的运用
                using (SqlCommand cmd = new SqlCommand())
                {
                    //打开数据库
                    connection.Open();
                    //给cmd对象的Connection属性赋值
                    cmd.Connection = connection;
                    //声明一个SqlTransaction的变量
                    SqlTransaction tx = connection.BeginTransaction();
                    //给cmd对象的Transaction属性赋值
                    cmd.Transaction = tx;
                    try
                    {
                        //声明一个int类型的变量,并赋值为0
                        int count = 0;
                        //用for循环迭代数据
                        for (int n = 0; n < sql.Count; n++)
                        {
                            //声明一个string类型的变量,并赋值
                            string strsql = sql[n];
                            //用if判断strsql变量是否有值
                            if (strsql.Trim().Length > 1)
                            {
                                //给cmd对象的CommandText属性赋值
                                cmd.CommandText = strsql;
                                //循环调用cod的ExecuteNonQuery方法
                                count += cmd.ExecuteNonQuery();
                            }
                        }
                        //调用tx的Commit方法
                        tx.Commit();
                        //返回int类型
                        return count > 0 ? 3 : -1;
                    }
                    catch (SqlException)
                    {
                        //调用tx的Rollback方法
                        tx.Rollback();
                        //关闭数据库
                        connection.Close();
                        //返回-2
                        return -2;
                    }
                }
            }
        }
        /// <summary>
        /// 删除一条数据(返回 2：成功;-1：失败;-2:出现异常)
        /// </summary>
        /// <param name="sql">要查询的T-SQL</param>
        /// <returns>int</returns>
        protected int Delete(string sql)
        {
            //用using指令,来控制SqlConnection的运用
            using (SqlConnection connection = new SqlConnection(connStr))
            {
                //用using指令,来控制SqlCommand的运用
                using (SqlCommand cmd = new SqlCommand(sql, connection))
                {
                    try
                    {
                        //打开数据库
                        connection.Open();
                        //声明一个int类型的变量,用于接收comm对象调用ExecuteNonQuery方法返回的值
                        int x = (int)cmd.ExecuteNonQuery();
                        //返回int类型的值
                        return x >= 1 ? 2 : -1;
                    }
                    catch (SqlException e)
                    {
                        //关闭数据库
                        connection.Close();
                        Console.WriteLine(e);
                        //返回-2
                        return -2;
                    }
                }
            }
        }
        /// <summary>
        /// 读取一行一列数据
        /// </summary>
        /// <param name="sql">要查询的T-SQL</param>
        /// <returns>返回 object 类型</returns>
        protected object Select(string sql)
        {
            //用using指令,来控制SqlConnection的运用
            using (SqlConnection connection = new SqlConnection(connStr))
            {
                //用using指令,来控制SqlCommand的运用
                using (SqlCommand cmd = new SqlCommand(sql, connection))
                {
                    try
                    {
                        //打开数据库
                        connection.Open();
                        //声明一个object类型的变量,用于接收comm对象调用ExecuteScalar方法返回的值
                        object x = cmd.ExecuteScalar();
                        //返回object类型的值
                        return x;
                    }
                    catch (SqlException)
                    {
                        //关闭数据库
                        connection.Close();
                        //返回null
                        return null;
                    }
                }
            }
        }
        /// <summary>
        /// 读取多条数据
        /// </summary>
        /// <typeparam name="T">泛型</typeparam>
        /// <param name="sql">要查询的T-SQL</param>
        /// <returns>返回 泛型集合</returns>
        protected IList<T> SelectIn<T>(string sql)
        {
            //用using指令,来控制SqlConnection的运用
            using (SqlConnection connection = new SqlConnection(connStr))
            {
                //用using指令,来控制SqlDataAdapter的运用
                using (SqlDataAdapter sda = new SqlDataAdapter(sql, connection))
                {
                    //创建一个DataSet的对象
                    ds = new DataSet();
                    //用sda填充dataset
                    sda.Fill(ds);
                    //返回调用DataSetToIList方法返回的值
                    return DataSetToIList<T>(ds, 0);
                }
            }
        }
        /// <summary>
        /// 读取多条数据
        /// </summary>
        /// <param name="sql">要查询的T-SQL</param>
        /// <returns>返回DateSet数据集</returns>
        protected DataSet SelectAll(string sql)
        {
            //用using指令,来控制SqlConnection的运用
            using (SqlConnection connection = new SqlConnection(connStr))
            {
                //用using指令,来控制SqlDataAdapter的运用
                using (SqlDataAdapter sda = new SqlDataAdapter(sql, connection))
                {
                    //创建一个DataSet的对象
                    ds = new DataSet();
                    //用sda填充dataset
                    sda.Fill(ds);
                    //返回调用ds
                    return ds;
                }
            }
        }
        /// <summary>
        /// 读取一行数据
        /// </summary>
        /// <typeparam name="T">泛型T-SQL</typeparam>
        /// <param name="sql">要查询的T-SQL</param>
        /// <returns>泛型集合实体</returns>
        protected T SelectByObject<T>(string sql)
        {
            //用using指令,来控制SqlConnection的运用
            using (SqlConnection connection = new SqlConnection(connStr))
            {
                //用using指令,来控制SqlDataAdapter的运用
                using (SqlDataAdapter sda = new SqlDataAdapter(sql, connection))
                {
                    //创建一个DataSet的对象
                    ds = new DataSet();
                    //用sda填充dataset
                    sda.Fill(ds);
                    //返回调用DataSetToT方法返回的值
                    return DataSetToT<T>(ds, 0);
                }
            }
        }
        #endregion

        #region 泛型和DataSet互换方法
        /// <summary>
        /// 泛型集合转换成DataSet
        /// </summary>
        /// <param name="p_List">泛型</param>
        /// <returns>返回DataSet</returns>
        private static DataSet ToDataSet(IList p_List)
        {
            DataSet result = new DataSet();
            DataTable _DataTable = new DataTable();
            if (p_List.Count > 0)
            {
                PropertyInfo[] propertys = p_List[0].GetType().GetProperties();
                foreach (PropertyInfo pi in propertys)
                {
                    _DataTable.Columns.Add(pi.Name, pi.PropertyType);
                }

                for (int i = 0; i < p_List.Count; i++)
                {
                    ArrayList tempList = new ArrayList();
                    foreach (PropertyInfo pi in propertys)
                    {
                        object obj = pi.GetValue(p_List[i], null);
                        tempList.Add(obj);
                    }
                    object[] array = tempList.ToArray();
                    _DataTable.LoadDataRow(array, true);
                }
            }
            result.Tables.Add(_DataTable);
            return result;
        }
        /// <summary>
        /// 泛型集合转换DataSet
        /// </summary>
        /// <typeparam name="T">泛型</typeparam>
        /// <param name="list">泛型集合</param>
        /// <returns></returns>
        private static DataSet ToDataSet<T>(IList<T> list)
        {
            //返回调用ToDataSet方法的值
            return ToDataSet<T>(list, null);
        }
        /// <summary>
        /// 泛型集合转换DataSet
        /// </summary>
        /// <typeparam name="T">泛型</typeparam>
        /// <param name="p_List">泛型集合</param>
        /// <param name="p_PropertyName">待转换属性名数组</param>
        /// <returns>返回DataSet</returns>
        private static DataSet ToDataSet<T>(IList<T> p_List, params string[] p_PropertyName)
        {
            List<string> propertyNameList = new List<string>();
            if (p_PropertyName != null)
                propertyNameList.AddRange(p_PropertyName);

            DataSet result = new DataSet();
            DataTable _DataTable = new DataTable();
            if (p_List.Count > 0)
            {
                PropertyInfo[] propertys = p_List[0].GetType().GetProperties();
                foreach (PropertyInfo pi in propertys)
                {
                    if (propertyNameList.Count == 0)
                    {
                        // 没有指定属性的情况下全部属性都要转换
                        _DataTable.Columns.Add(pi.Name, pi.PropertyType);
                    }
                    else
                    {
                        if (propertyNameList.Contains(pi.Name))
                            _DataTable.Columns.Add(pi.Name, pi.PropertyType);
                    }
                }

                for (int i = 0; i < p_List.Count; i++)
                {
                    ArrayList tempList = new ArrayList();
                    foreach (PropertyInfo pi in propertys)
                    {
                        if (propertyNameList.Count == 0)
                        {
                            object obj = pi.GetValue(p_List[i], null);
                            tempList.Add(obj);
                        }
                        else
                        {
                            if (propertyNameList.Contains(pi.Name))
                            {
                                object obj = pi.GetValue(p_List[i], null);
                                tempList.Add(obj);
                            }
                        }
                    }
                    object[] array = tempList.ToArray();
                    _DataTable.LoadDataRow(array, true);
                }
            }
            result.Tables.Add(_DataTable);
            return result;
        }
        /// <summary>
        /// DataSet装换为泛型集合
        /// </summary>
        /// <typeparam name="T">泛型</typeparam>
        /// <param name="p_DataSet">DataSet</param>
        /// <param name="p_TableIndex">待转换数据表索引</param>
        /// <returns>泛型集合</returns>
        private static IList<T> DataSetToIList<T>(DataSet p_DataSet, int p_TableIndex)
        {
            if (p_DataSet == null || p_DataSet.Tables.Count < 0)
                return null;
            if (p_TableIndex > p_DataSet.Tables.Count - 1)
                return null;
            if (p_TableIndex < 0)
                p_TableIndex = 0;

            DataTable p_Data = p_DataSet.Tables[p_TableIndex];
            // 返回值初始化
            IList<T> result = new List<T>();
            for (int j = 0; j < p_Data.Rows.Count; j++)
            {
                T _t = (T)Activator.CreateInstance(typeof(T));
                PropertyInfo[] propertys = _t.GetType().GetProperties();
                foreach (PropertyInfo pi in propertys)
                {
                    for (int i = 0; i < p_Data.Columns.Count; i++)
                    {
                        // 属性与字段名称一致的进行赋值
                        if (pi.Name.ToLower().Equals(p_Data.Columns[i].ColumnName.ToLower()))
                        {
                            // 数据库NULL值单独处理
                            if (p_Data.Rows[j][i] != DBNull.Value)
                                pi.SetValue(_t, p_Data.Rows[j][i], null);
                            else
                                pi.SetValue(_t, null, null);
                            break;
                       }
                    }
                }
                result.Add(_t);
            }
            return result;
        }
        /// <summary>
        /// DataSet装换为泛型集合
        /// </summary>
        /// <typeparam name="T">泛型</typeparam>
        /// <param name="p_DataSet">DataSet</param>
        /// <param name="p_TableIndex">待转换数据表索引</param>
        /// <returns>泛型集合</returns>
        private static T DataSetToT<T>(DataSet p_DataSet, int p_TableIndex)
        {
            if (p_DataSet == null || p_DataSet.Tables.Count < 0)
                return default(T);
            if (p_TableIndex > p_DataSet.Tables.Count - 1)
                return default(T);
            if (p_TableIndex < 0)
                p_TableIndex = 0;

            DataTable p_Data = p_DataSet.Tables[p_TableIndex];
            // 返回值初始化
            IList<T> result = new List<T>();
            for (int j = 0; j < p_Data.Rows.Count; j++)
            {
                T _t = (T)Activator.CreateInstance(typeof(T));
                PropertyInfo[] propertys = _t.GetType().GetProperties();
                foreach (PropertyInfo pi in propertys)
                {
                    for (int i = 0; i < p_Data.Columns.Count; i++)
                    {
                        // 属性与字段名称一致的进行赋值
                        if (pi.Name.ToLower().Equals(p_Data.Columns[i].ColumnName.ToLower()))
                        {
                            // 数据库NULL值单独处理
                            if (p_Data.Rows[j][i] != DBNull.Value)
                                pi.SetValue(_t, p_Data.Rows[j][i], null);
                            else
                                pi.SetValue(_t, null, null);
                            break;
                        }
                    }
                }
                result.Add(_t);
            }
            return result[0];
        }
        /// <summary>
        /// DataSet装换为泛型集合
        /// </summary>
        /// <typeparam name="T">泛型</typeparam>
        /// <param name="p_DataSet">DataSet</param>
        /// <param name="p_TableName">待转换数据表名称</param>
        /// <returns>泛型集合</returns>
        private static IList<T> DataSetToIList<T>(DataSet p_DataSet, string p_TableName)
        {
            int _TableIndex = 0;
            if (p_DataSet == null || p_DataSet.Tables.Count < 0)
                return null;
            if (string.IsNullOrEmpty(p_TableName))
                return null;
            for (int i = 0; i < p_DataSet.Tables.Count; i++)
            {
                // 获取Table名称在Tables集合中的索引值
                if (p_DataSet.Tables[i].TableName.ToLower().Equals(p_TableName.ToLower()))
                {
                    _TableIndex = i;
                    break;
                }
            }
            return DataSetToIList<T>(p_DataSet, _TableIndex);
        }
        #endregion

        #region  执行简单SQL语句
        /// <summary>
        /// 执行SQL语句，返回影响的记录数
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <returns>影响的记录数</returns>
        public static int ExecuteSql(string sql)
        {
            //用using指令,来控制SqlConnection的运用
            using (SqlConnection connection = new SqlConnection(connStr))
            {
                //用using指令,来控制SqlCommand的运用
                using (SqlCommand cmd = new SqlCommand(sql, connection))
                {
                    try
                    {
                        //打开数据库
                        connection.Open();
                        //声明一个int类型的变量,用于接收comm对象调用ExecuteNonQuery方法返回的值
                        int rows = cmd.ExecuteNonQuery();
                        //返回int类型的值
                        return rows;
                    }
                    catch (System.Data.SqlClient.SqlException e)
                    {
                        connection.Close();
                        throw e;
                    }
                }
            }
        }
        #endregion
    }
}