DBHelper

using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;

namespace FerexTestSystem
{
    public class DBHelper
    {
        //private static string server;
        //private static string uid;
        //private static string pwd;
        //private static string database;
        //private static string connStr;
        //public DBHelper(string server1, string uid1, string pwd1, string database1)
        //{
        //    server = server1;
        //    pwd = pwd1;
        //    database = database1;
        //    uid = uid1;
        //    connStr = @"server=" + server + ";uid=" + uid + ";pwd=" + pwd + ";database=" + database;

        //}
        //public static string connStr = @"server=PE01\PE01;uid=sa;pwd=adminsystem;database=QQDB";
        ///执行添加、删除、修改通用方法
        ///</summary>
        ///<param name="sql"></param>
        ///<param name="paras"></param>
        ///<returns></returns>
        public static int ExecuteNonQuery(string connStr,string sql, params SqlParameter[] paras)
        {
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                //打开数据库连接
                conn.Open();
                //创建执行脚本的对象
                SqlCommand command = new SqlCommand(sql, conn);
                command.Parameters.AddRange(paras);
                int result = command.ExecuteNonQuery();
                return result;
            }
        }

        ///
        /// 使用存储过程
        /// 
        public static Boolean Stored_Procedure(string connStr, string sql, params SqlParameter[] paras)
        {
            Boolean Flag = false;
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                conn.Open();
                SqlCommand command = new SqlCommand(sql,conn);
                command.CommandType = CommandType.StoredProcedure;
                foreach (SqlParameter ps in paras)
                    command.Parameters.Add(ps);
                command.Parameters.Add("@rs", SqlDbType.Int).Direction = ParameterDirection.Output;
                command.ExecuteScalar();
                if ((int)command.Parameters["@rs"].Value == 0)
                    return true;
            }
            return Flag;
        }
        ///<summary>
        ///执行SQL并返回第一行第一列
        ///</summary>>
        ///<param name="sql""></param>
        ///<returns></returns>returns>
        ///
        public static object ExecuteScalar(string connStr,String sql, params SqlParameter[] paras)
        {
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                conn.Open();
                SqlCommand command = new SqlCommand(sql, conn);
                command.Parameters.AddRange(paras);
                object obj = command.ExecuteScalar();
                return obj;
            }
        }

        ///<summary>
        ///根据SQL和泛型方法返回泛型【集合】
        ///</summary>>
        ///<typeparam name="T"></typeparam>
        ///<param name="sql"></param>
        ///<param name="paras"></param>
        ///<returns></returns>
        public static List<T> GetListByColumns<T>(string connStr, string sql, params SqlParameter[] paras)
        {
            List<T> list = new List<T>();
            //获取select和form中间的字段
            string columnsStr = sql.ToLower().Substring(sql.IndexOf("select ") + 7, sql.IndexOf("from") - 7)
                .Replace(" ", "")
                .Replace("\r\n", "")
                .Replace("[", "")
                .Replace("]", "");
            //保存字段
            ArrayList columns = new ArrayList(columnsStr.Split(','));
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                conn.Open();
                SqlCommand command = new SqlCommand(sql, conn);
                command.Parameters.AddRange(paras);
                using (SqlDataReader reader = command.ExecuteReader())
                {
                    //typeof()检测类型
                    Type type = typeof(T);//类型的声明(可声明一个不确定的类型 )
                    if (columnsStr == "*")//查询所有(里面不用判断)
                    {
                        while (reader.Read())
                        {
                            T t = (T)Activator.CreateInstance(type);
                            //通过反射去遍历属性
                            foreach (PropertyInfo info in type.GetProperties())
                            {
                                info.SetValue(t, reader[info.Name] is DBNull ? null : reader[info.Name]);
                            }
                            list.Add(t);
                        }
                    }
                    else//根据查询的列遍历
                    {
                        while (reader.Read())
                        {
                            T t = (T)Activator.CreateInstance(type);
                            //通过反射去遍历属性
                            foreach (PropertyInfo info in type.GetProperties())
                            {
                                if (columns.Contains(info.Name.ToLower()))//判断是否存在
                                {
                                    info.SetValue(t, reader[info.Name] is DBNull ? null : reader[info.Name]);
                                }
                            }
                            list.Add(t);
                        }
                    }
                }
            }
            return list;//命令行为
        }

        public static List<T> GetList<T>(string connStr, String sql, params SqlParameter[] paras)
        {
            List<T> list = new List<T>();
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                conn.Open();
                SqlCommand command = new SqlCommand(sql,conn);
                command.Parameters.AddRange(paras);
                using (SqlDataReader reader = command.ExecuteReader())
                {
                    //typeof()检测类型
                    Type type = typeof(T);//类型的声明(可声明一个不确定的类型)
                    while (reader.Read())
                    {
                        T t = (T)Activator.CreateInstance(type);
                        //通过反射去遍历属性
                        foreach (PropertyInfo info in type.GetProperties())
                        {
                            info.SetValue(t, reader[info.Name] is DBNull ? null : reader[info.Name]);
                        }
                        list.Add(t);
                    }
                }
            }
            return list;//命令行为
        }

        ///<summary>
        ///根据SQL和泛型方法返回泛型[对象]
        ///</summary>>
        ///<typeparam name="T"></typeparam>
        ///<param name="sql"></param>
        ///<param name="="paras"></param>
        ///<returns></returns>
        public static T GetModel<T>(string connStr, String sql, params SqlParameter[] paras)
        {
            Type type = typeof(T);//赋默认值null，可能是值类型
            T t = default(T);//赋默认值null,可能是值类型
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                conn.Open();
                SqlCommand command = new SqlCommand(sql, conn);
                command.Parameters.AddRange(paras);
                using (SqlDataReader reader = command.ExecuteReader())
                {
                    if (reader.Read())
                    {
                        t = (T)Activator.CreateInstance(type);
                        //通过反射去遍历属性
                        foreach (PropertyInfo info in type.GetProperties())
                        {
                            info.SetValue(t, reader[info.Name] is DBNull ?
                                null : reader[info.Name]);
                        }
                    }
                }
            }
            return t;//命令行为
        }

        /// <summary>
        /// 查询返回临时表
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="paras"></param>
        /// <returns></returns>
        public static DataTable GetDataTable(string connStr, string sql, params SqlParameter[] paras)
        {
            DataTable dt = null;
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                SqlCommand command = new SqlCommand(sql, conn);
                command.Parameters.AddRange(paras);
                SqlDataAdapter adapter = new SqlDataAdapter(command);
                dt = new DataTable();
                adapter.Fill(dt);
            }
            return dt;
        }

        /// <summary>
        /// 执行SQL返回SqlDataReader对象(游标)
        /// </summary>>
        /// <param name="sql"></param>
        /// <param name="paras"></param>
        /// <returns></returns>

        public static SqlDataReader ExecuteReader(string connStr, string sql, params SqlParameter[] paras)
        {
            SqlConnection conn = new SqlConnection(connStr);
            conn.Open();
            SqlCommand command = new SqlCommand(sql,conn);
            command.Parameters.AddRange(paras);
            return command.ExecuteReader(CommandBehavior.CloseConnection);//命令行为
        }

        ///<summary>
        ///根据SQL执行返回数据集(多临时表)
        ///<param name="sql"></param>
        ///<param name="paras"></param>
        ///<returns></returns>
        public static DataSet GetDataSet(string connStr, string sql, params SqlParameter[] paras)
        {
            DataSet ds = null;
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                SqlCommand command = new SqlCommand(sql, conn);
                command.Parameters.AddRange(paras);
                SqlDataAdapter adapter = new SqlDataAdapter(command);
                ds = new DataSet();
                adapter.Fill(ds);
            }
            return ds;
        }

        ///执行事务的通用方法
        public static bool ExecutTransaction(string connStr, string[] sql, params SqlParameter[] paras)
        {
            bool result = false;
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                conn.Open();
                SqlCommand command = new SqlCommand();
                command.Parameters.AddRange(paras);
                command.Connection = conn;//关联联接对象
                command.Transaction = conn.BeginTransaction();//开始事务
                try
                {
                    for (int i = 0; i < sql.Length; i++)
                    {
                        command.CommandText = sql[1];
                        command.ExecuteNonQuery();//执行
                    }
                    command.Transaction.Commit();//提交
                    result = true;
                }
                catch (Exception ex)
                {
                    command.Transaction.Rollback();//回滚
                    result = false;
                }
            }
            return result;
        }

        //事务批量添加
        public static bool ExecuteTransaction(string connStr, string[] sql, List<SqlParameter[]> list)
        {
            bool result = false;
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                conn.Open();
                SqlCommand command = new SqlCommand();
                foreach (SqlParameter[] item in list)
                {
                    command.Parameters.AddRange(item);
                }
                command.Connection = conn;//关联联接对象
                command.Transaction = conn.BeginTransaction();//开始事务
                try
                {
                    for (int i = 0; i < sql.Length; i++)
                    {
                        command.CommandText = sql[i];
                        command.ExecuteNonQuery();//执行
                    }
                    command.Transaction.Commit();//提交
                    result= true;
                }
                catch (Exception ex)
                {
                    command.Transaction.Rollback();//回滚
                    result = false;
                }
            }
            return result;
        }
    }
}