反射

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;
using Newtonsoft.Json;
using System.Globalization;

namespace ConsoleApp
{
    public class SqlHelper
    {
        private static string ConnStr = @"Password=123321;Persist Security Info=True;User ID=sa;Initial Catalog=**;Data Source=**";
        public static bool Save<T>(T obj)//新增一个
        {
            List<string> sqls = new List<string>() { GetSaveSql(obj) };
            return ExecuteSQLS(sqls);
        }
        public static bool Saves<T>(List<T> objs)//新增多个
        {
            List<string> sqls = new List<string>();
            foreach (var item in objs)
            {
                sqls.Add(GetSaveSql(item));
            }
            return ExecuteSQLS(sqls);
        }
        public static bool ExecuteSQLS(List<string> sqls)//批量操作数据
        {
            using (SqlConnection conn = new SqlConnection(ConnStr))
            {
                conn.Open();
                using (SqlTransaction trans = conn.BeginTransaction())
                {
                    try
                    {
                        SqlCommand cmd = new SqlCommand();
                        cmd.Connection = conn;
                        cmd.Transaction = trans;
                        foreach (var sql in sqls)
                        {
                            cmd.CommandText = sql;
                            cmd.ExecuteNonQuery();
                        }
                        trans.Commit();
                        return true;
                    }
                    catch
                    {
                        trans.Rollback();
                        return false;
                    }
                }
            }

        }
        public static DataSet GetResult<T>(string sql)//查询数据，返回DataSet
        {
            SqlCommand command = new SqlCommand();
            command.CommandType = CommandType.Text;
            command.CommandText = sql;
            DataSet ds = new DataSet();
            using (SqlConnection conn = new SqlConnection(ConnStr))
            {
                command.Connection = conn;
                SqlDataAdapter dataAdapter = new SqlDataAdapter();
                dataAdapter.SelectCommand = command;
                try
                {
                    conn.Open();
                    dataAdapter.Fill(ds);
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                finally
                {
                    conn.Close();
                }
            }
            return ds;
        }
        public static IList<T> Query<T>(string sql)
        {
            DataSet ds = GetResult<T>(sql);
            List<string> listName = GetDisPlayName<T>();
            object obj = new object();
            if (ds.Tables[0].Rows.Count > 0)
            {
                IList<T> list = DataSetToEntityList<T>(ds, 0);
                return list;
            }
            return null;
        }
        public static T SingleOrDefault<T>(string sql)
        {
            DataSet ds = GetResult<T>(sql);
            List<string> listName = GetDisPlayName<T>();
            object obj = new object();
            if (ds.Tables[0].Rows.Count > 0)
            {
                IList<T> list = DataSetToEntityList<T>(ds, 0);
                if (list.Count > 0)
                {
                    return list[0];
                }
                else
                {
                    return default(T);
                }
            }
            return default(T);
        }
        public static IList<T> DataSetToEntityList<T>(DataSet ds, int index)//DataSet转换为实体列表
        {
            // 返回值初始化
            IList<T> result = new List<T>();
            if (ds == null || ds.Tables.Count <= 0 || ds.Tables[0].Rows.Count <= 0)
            {
                return result;
            }
            foreach (DataTable dt in ds.Tables)
            {
                foreach (DataRow dr in dt.Rows)
                {
                    string json = string.Empty;
                    string[] strs = GetJson(ref json, dr);
                    T _t;
                    if (json.Split(':').Length > 2)
                    {
                        for (int i = 0; i < strs.Length; i++)
                        {
                            string s = "@" + i;
                            if (json.IndexOf(s) > 0)
                            {
                                json = json.Replace(s, strs[i]);
                            }
                        }
                        _t = JsonConvert.DeserializeObject<T>(json);
                        result.Add(_t);
                    }
                    else
                    {
                        object o = json.Split(':')[1].Replace("\"", "").Replace("}", "");
                        _t = (T)Convert.ChangeType(o, typeof(T), CultureInfo.InvariantCulture);
                        result.Add(_t);
                    }
                }
            }
            return result;
        }
        private static object SwitchData(string type, object obj)//转换数据类型
        {
            object o = new object();
            switch (type)
            {
                case "String": o = obj.ToString(); break;
                case "Int32": o = Convert.ToInt32(obj); break;
                case "DateTime": o = Convert.ToDateTime(obj); break;
                case "Char": o = Convert.ToChar(obj); break;
            }
            return o;
        }
        #region 基础数据
        public static string GetSaveSql<T>(T obj)//获取新增sql
        {
            List<string> listName = GetDisPlayName<T>();
            List<string> listValue = GetDisPlayValue<T>(obj);
            Data data = GetAttrivutes<T>();
            string sql = string.Format("INSERT INTO {0} ({1}) VALUES ({2})", data.TableName, string.Join(",", listName.ToArray()), string.Join(",", listValue.ToArray()));
            return sql;
        }
        public static List<string> GetDisPlayName<T>()// 反射获取字段
        {
            List<string> list = new List<string>();
            Type type = typeof(T);
            TypeAttributes types = type.Attributes;
            PropertyInfo[] Propers = type.GetProperties();
            list = Propers.Select(n => n.Name).ToList();
            return list;
        }
        public static List<string> GetDisPlayValue<T>(T obj)// 反射获取字段值
        {
            List<string> list = new List<string>();
            if (obj != null)
            {
                Type type = typeof(T);
                TypeAttributes types = type.Attributes;
                PropertyInfo[] Propers = type.GetProperties();
                list = Propers.Select(n => n.GetValue(obj) == null ? null : n.GetValue(obj).ToString()).Select(n => "'" + n + "'").ToList();
            }
            return list;
        }
        public static Data GetAttrivutes<T>()// 反射获取特性
        {
            Data data = new Data();
            IList<CustomAttributeData> datas = typeof(T).GetCustomAttributesData();
            foreach (var item in datas)
            {
                if (item.AttributeType.Name.Equals("TableNameAttribute"))
                {
                    data.TableName = item.ConstructorArguments[0].Value.ToString();
                }
                if (item.AttributeType.Name.Equals("PrimaryKeyAttribute"))
                {
                    data.PrimaryKey = item.ConstructorArguments[0].Value.ToString();
                }
            }
            return data;
        }
        #endregion
        public static string[] GetJson(ref string json, DataRow dr)//把dataset数据转换成json的格式
        {
            string[] strs = new string[1000];
            StringBuilder sb = new StringBuilder();
            sb.Append("{");
            for (int i = 0; i < dr.Table.Columns.Count; i++)
            {

                sb.AppendFormat("\"{0}\":\"{1}\",", dr.Table.Columns[i].ColumnName.Replace("\"", "\\\"").Replace("\'", "\\\'"), "@" + i)
                    .Replace(Convert.ToString((char)13), "\\r\\n").Replace(Convert.ToString((char)10), "\\r\\n");
                strs[i] = ObjToStr(dr[i]).Replace("\"", "\\\"").Replace("\'", "\\\'");
            }
            sb.Remove(sb.ToString().LastIndexOf(','), 1);
            sb.Append("}");
            json = sb.ToString();
            return strs;
        }
        /// <summary>
        /// 将object转换成为string
        /// </summary>
        /// <param name="ob">obj对象</param>
        /// <returns></returns>
        public static string ObjToStr(object ob)
        {
            if (ob == null)
            {
                return string.Empty;
            }
            else
                return ob.ToString();
        }
    }
    public class TableNameAttribute : Attribute
    {
        public string TableName { set; get; }
        public TableNameAttribute(string tableName)
        {
            this.TableName = tableName;
        }
    }
    public class PrimaryKeyAttribute : Attribute
    {
        public PrimaryKeyAttribute(string PrimaryKey)
        {
            this.PrimaryKey = PrimaryKey;
            this.autoIncrement = true;
        }
        public string PrimaryKey { private set; get; }
        public bool autoIncrement { set; get; }
    }
    public class Data
    {
        public string TableName { set; get; }
        public string PrimaryKey { set; get; }
    }
}