c#SqlHelper

using System.Configuration;

namespace DBUtility
{
    using Powertech.Model;
    using System;
    using System.Collections;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.SqlClient;
    using System.Reflection;
    using System.Text;

    public static class SqlHelper
    {
        public static readonly string Constr = ConfigurationManager.AppSettings["ConnectionString"];
        private static Hashtable parmCache = Hashtable.Synchronized(new Hashtable());


        public static DataTable ExenonQuery(string sql)
        {
            using (SqlConnection conn = new SqlConnection(Constr))
            {
                if (conn.State != ConnectionState.Open)
                {
                    conn.Open();
                }
                SqlCommand cmd = new SqlCommand(sql, conn);
              
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                DataTable dt = new DataTable();
                adapter.Fill(dt);
                return dt;
            }
        }
        public static void CacheParameters(string cacheKey, params SqlParameter[] commandParameters)
        {
            parmCache[cacheKey] = commandParameters;
        }

        public static int ExecuteNonQuery(SqlConnection connection, CommandType cmdType, string cmdText,
            params SqlParameter[] commandParameters)
        {
            SqlCommand cmd = new SqlCommand();
            PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
            int num = cmd.ExecuteNonQuery();
            cmd.Parameters.Clear();
            return num;
        }

        public static int ExecuteNonQuery(SqlTransaction trans, CommandType cmdType, string cmdText,
            params SqlParameter[] commandParameters)
        {
            SqlCommand cmd = new SqlCommand();
            PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, commandParameters);
            int num = cmd.ExecuteNonQuery();
            cmd.Parameters.Clear();
            return num;
        }

        public static int ExecuteNonQuery(string connectionString, CommandType cmdType, string cmdText,
            params SqlParameter[] commandParameters)
        {
            SqlCommand cmd = new SqlCommand();
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
                int num = cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                return num;
            }
        }

        public static SqlDataReader ExecuteReader(string connectionString, CommandType cmdType, string cmdText,
            params SqlParameter[] commandParameters)
        {
            SqlDataReader reader2;
            SqlCommand cmd = new SqlCommand();
            SqlConnection conn = new SqlConnection(connectionString);
            try
            {
                PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
                SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                cmd.Parameters.Clear();
                reader2 = reader;
            }
            catch
            {
                conn.Close();
                throw;
            }
            return reader2;
        }

        public static object ExecuteScalar(SqlConnection connection, CommandType cmdType, string cmdText,
            params SqlParameter[] commandParameters)
        {
            SqlCommand cmd = new SqlCommand();
            PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
            object obj2 = cmd.ExecuteScalar();
            cmd.Parameters.Clear();
            return obj2;
        }

        public static object ExecuteScalar(string connectionString, CommandType cmdType, string cmdText,
            params SqlParameter[] commandParameters)
        {
            SqlCommand cmd = new SqlCommand();
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
                object obj2 = cmd.ExecuteScalar();
                cmd.Parameters.Clear();
                return obj2;
            }
        }

        public static SqlParameter[] GetCachedParameters(string cacheKey)
        {
            SqlParameter[] parameterArray = (SqlParameter[])parmCache[cacheKey];
            if (parameterArray == null)
            {
                return null;
            }
            SqlParameter[] parameterArray2 = new SqlParameter[parameterArray.Length];
            int index = 0;
            int length = parameterArray.Length;
            while (index < length)
            {
                parameterArray2[index] = (SqlParameter)((ICloneable)parameterArray[index]).Clone();
                index++;
            }
            return parameterArray2;
        }

        private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans,
            CommandType cmdType, string cmdText, SqlParameter[] cmdParms)
        {
            if (conn.State != ConnectionState.Open)
            {
                conn.Open();
            }
            cmd.Connection = conn;
            cmd.CommandText = cmdText;
            if (trans != null)
            {
                cmd.Transaction = trans;
            }
            cmd.CommandType = cmdType;
            if (cmdParms != null)
            {
                foreach (SqlParameter parameter in cmdParms)
                {
                    cmd.Parameters.Add(parameter);
                }
            }
        }

        public static T DataReaderToEntity<T>(this SqlDataReader reader) where T : Entity, new()
        {
            T t = new T();
            if (reader.Read())
            {
                do
                {
                    Type type = t.GetType();
                    var properties = type.GetProperties();
                    foreach (PropertyInfo p in properties)
                    {
                        // p.SetValue(t, Convert.ChangeType(reader[p.Name], p.PropertyType));
                        var value = reader[p.Name];
                        if (!p.PropertyType.IsGenericType)
                        {
                            p.SetValue(t, value == null ? null : Convert.ChangeType(value, p.PropertyType));
                        }
                        else
                        {
                            Type genericTypeDefinition = p.PropertyType.GetGenericTypeDefinition();
                            if (genericTypeDefinition == typeof(Nullable<>))
                            {
                                p.SetValue(t, value == null ? null : Convert.ChangeType(value, Nullable.GetUnderlyingType(p.PropertyType)));
                            }
                        }
                    }
                } while (reader.Read());
            }
            return t;
        }

        public static List<T> DataTableToList<T>(this DataTable dt) where T : Entity, new()
        {
            List<T> list = new List<T>();
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                T t = new T();
                foreach (PropertyInfo p in typeof(T).GetProperties())
                {
               
                    //p.SetValue(t, Convert.ChangeType(dt.Rows[i][p.Name], p.PropertyType));
                    var value = dt.Rows[i][p.Name];
                    if (!p.PropertyType.IsGenericType)
                    {
                        p.SetValue(t, value == null ? null : Convert.ChangeType(value, p.PropertyType));
                    }
                    else
                    {
                        Type genericTypeDefinition = p.PropertyType.GetGenericTypeDefinition();
                        if (genericTypeDefinition == typeof(Nullable<>))
                        {
                            p.SetValue(t, value == null ? null : Convert.ChangeType(value, Nullable.GetUnderlyingType(p.PropertyType)));
                        }
                    }
                }

               
                list.Add(t);
            }
            return list;
        }


        #region 增删改查

        /// <summary>
        /// 增
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="t"></param>
        /// <returns></returns>
        public static bool Insert<T>(T t) where T : Entity, new()
        {
            return ExecuteNonQuery(Constr, CommandType.Text, GetInsertSql<T>(t)) > 0;
        }




        #endregion

        #region 私有

        /// <summary>
        /// 获取添加sql
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="t"></param>
        /// <returns></returns>
        private static string GetInsertSql<T>(T t) where T : Entity, new()
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("Insert into T_" + t.GetType().Name);
            PropertyInfo[] props = typeof(T).GetProperties();
            StringBuilder pros = new StringBuilder("");
            StringBuilder values = new StringBuilder("");
            for (int i = 0; i < props.Length; i++)
            {
                if (!props[i].Name.Equals("Id",StringComparison.OrdinalIgnoreCase))
                {
                    pros.Append(props[i].Name + ",");
                    values.Append("'" + props[i].GetValue(t) + "',");
                } 
            }
            sb.Append("(" + pros.ToString().TrimEnd(',') + ")values (" + values.ToString().TrimEnd(',') + " )");
            return sb.ToString();
        }

        private static string GetDeleteSql<T>(int Id) where T : Entity, new()
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("Delete from T_" + typeof(T).Name + " where Id=" + Id);
            return sb.ToString();
        }

        private static string GetUpdateSql<T>(T t) where T : Entity, new()
        {
            StringBuilder sb = new StringBuilder();

            StringBuilder values = new StringBuilder("");
            PropertyInfo[] props = typeof(T).GetProperties();
            for (int i = 1; i < props.Length; i++)
            {

                values.Append("" + props[i].Name + "='" + props[i].GetValue(t) + "',");
            }
            sb.Append("Update T_" + typeof(T).FullName + "  set " + values + " where Id=" + props[0].GetValue(t) + " ");
            return sb.ToString();
        }

        #endregion

        public static bool Delete<T>(int Id) where T : Entity, new()
        {
            return ExecuteNonQuery(Constr, CommandType.Text, GetDeleteSql<T>(Id)) > 0;
        }

        public static bool Update<T>(T t) where T : Entity, new()
        {
            return ExecuteNonQuery(Constr, CommandType.Text, GetUpdateSql<T>(t)) > 0;
        }

        public static T GetModel<T>(int Id) where T : Entity, new()
        {
            string sql = "select * from T_" + typeof(T).Name + " where Id= " + Id;
            return ExecuteReader(Constr, CommandType.Text, sql).DataReaderToEntity<T>();
        }

        public static List<T> Set<T>() where T : Entity, new()
        {
            string sql = "select * from T_" + typeof(T).Name;
            return ExenonQuery(sql).DataTableToList<T>();
        }

    }
}