sql代码反射

sql代码反射

说明：自己封装的的把table转换成object对象或对象集，把对象转换成sql插入语句,用于个人简单的小工具对数据库操作

sql语句封装方法

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

namespace RDPD_Common
{
    /// <summary>
    /// sql反射
    /// </summary>
    public static class SqlReflection
    {
        /// <summary>
        /// sql语句对象替换，单个对象
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="info">对象</param>
        /// <returns></returns>
        public static string SetParamInfo(this string sql, object info)
        {
            try
            {
                PropertyInfo[] shuxing = info.GetType().GetProperties();
                foreach (PropertyInfo item in shuxing)
                {
                    if (sql.Contains("@" + item.Name))
                    {
                        if (item.PropertyType == typeof(string[]))
                        {
                            string[] fenge = (string[])item.GetValue(info, null);
                            string one = string.Join(",", fenge);
                            string two = one.Insert(0, "'").Replace(",", "','");
                            string three = two.Insert(two.Length, "'");
                            sql = sql.Replace("@" + item.Name, three);
                        }
                        if (item.PropertyType == typeof(string) || item.PropertyType == typeof(int))
                        {
                            sql = sql.Replace("@" + item.Name, "'" + (item.GetValue(info, null)??string.Empty).ToString() + "'");
                        }
                        if (item.PropertyType == typeof(DateTime))
                        {
                            sql = sql.Replace("@" + item.Name, "'" + item.GetValue(info, null).ToString() + "'");
                        }
                    }
                }
                return sql;
            }
            catch (Exception ex)
            {

                throw ex;
            }
        }
        /// <summary>
        /// sql语句对象替换，多个对象 select * from table t left join info i where t.id=@t.Id(对象属性大小写一致) and i.name=@i.Name ,new string[] { "t", "i" }, person(t),student(i)
        /// 如果属性是string[]会变成string: 'a','b','c',集合类型待完善
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="param">表简称</param>
        /// <param name="info">对象 要表排序一致</param>
        /// <returns></returns>
        public static string SetParamInfo(this string sql, string[] param, params object[] info)
        {
            try
            {
                if (info.Length != param.Length)
                {
                    return "参数不匹配";
                }
                if (info.Length > 0)
                {
                    for (int i = 0; i < info.Length; i++)
                    {
                        PropertyInfo[] shuxing = info[i].GetType().GetProperties();
                        foreach (PropertyInfo item in shuxing)
                        {

                            if (sql.Contains("@" + param[i] + "." + item.Name))
                            {
                                if (item.PropertyType == typeof(string[]))
                                {
                                    string[] fenge = (string[])item.GetValue(info[i], null);
                                    string one = string.Join(",", fenge);
                                    string two = one.Insert(0, "'").Replace(",", "','");
                                    string three = two.Insert(two.Length, "'");
                                    sql = sql.Replace("@" + param[i] + "." + item.Name, three);
                                }
                                if (item.PropertyType == typeof(string) || item.PropertyType == typeof(int))
                                {
                                    sql = sql.Replace("@" + param[i] + "." + item.Name, "'" + (item.GetValue(info[i], null)??string.Empty).ToString() + "'");
                                }
                                if (item.PropertyType == typeof(DateTime))
                                {
                                    sql = sql.Replace("@" + param[i] + "." + item.Name, "'" + item.GetValue(info[i], null).ToString() + "'");
                                }
                            }
                        }

                    }
                    return sql;
                }
                return sql;
            }
            catch (Exception ex)
            {

                throw ex;
            }
        }

        /// <summary>
        /// table 装载对象 不区分大小写，适合单个对象
        /// </summary>
        /// <typeparam name="T">对象类型</typeparam>
        /// <param name="o"></param>
        /// <param name="table"></param>
        /// <returns>单个对象</returns>
        public static T GetObjectInfo<T>(this DataTable table) where T : new()
        {
            try
            {
                T obj = new T();
                Type model = typeof(T);
                List<string> list = new List<string>();
                BindingFlags flag = BindingFlags.Public | BindingFlags.IgnoreCase | BindingFlags.Instance;
                foreach (DataColumn item in table.Columns)
                {
                    if (obj.ContainPropertyAll(item.ColumnName))
                    {
                        list.Add(item.ColumnName);

                    }
                }
                for (int i = 0; i < table.Rows.Count; i++)
                {

                    foreach (string columname in list)
                    {
                        if (table.Rows[i][columname] != null && table.Rows[i][columname].ToString() != "")
                        {
                            PropertyInfo infotype = model.GetProperty(columname, flag);
                            if (infotype.PropertyType == typeof(string))
                            {
                                infotype.SetValue(obj, table.Rows[i][columname].ToString(), null);
                            }
                            else if (infotype.PropertyType == typeof(int))
                            {
                                infotype.SetValue(obj, Convert.ToInt32(table.Rows[i][columname]), null);
                            }
                            else if (infotype.PropertyType == typeof(DateTime))
                            {
                                infotype.SetValue(obj, Convert.ToDateTime(table.Rows[i][columname]), null);
                            }
                        }

                    }


                }
                return obj;
            }
            catch (Exception ex)
            {

                throw ex;
            }
        }
        /// <summary>
        /// table 装载对象 不区分大小写，适合返回多个对象
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="o"></param>
        /// <param name="table"></param>
        /// <returns></returns>
        public static List<T> GetObjectInfos<T>(this DataTable table) where T : new()
        {
            try
            {
                T objectmodel = new T();
                List<T> listobject = new List<T>();
                Type model = typeof(T);
                List<string> list = new List<string>();
                BindingFlags flag = BindingFlags.Public | BindingFlags.IgnoreCase | BindingFlags.Instance;
                foreach (DataColumn item in table.Columns)
                {
                    if (objectmodel.ContainPropertyAll(item.ColumnName))
                    {
                        list.Add(item.ColumnName);

                    }
                }
                for (int i = 0; i < table.Rows.Count; i++)
                {
                    T obj = new T();//使用开辟新的堆空间，否则他们的栈会指向同一个堆空间，导致对象覆盖
                    foreach (string columname in list)
                    {
                        if (table.Rows[i][columname] != null && table.Rows[i][columname].ToString() != "")
                        {
                            PropertyInfo infotype = model.GetProperty(columname, flag);
                            if (infotype.PropertyType == typeof(string))
                            {
                                infotype.SetValue(obj, table.Rows[i][columname].ToString(), null);
                            }
                            else if (infotype.PropertyType == typeof(int))
                            {
                                infotype.SetValue(obj, Convert.ToInt32(table.Rows[i][columname]), null);
                            }
                            else if (infotype.PropertyType == typeof(DateTime))
                            {
                                infotype.SetValue(obj, Convert.ToDateTime(table.Rows[i][columname]), null);
                            }
                        }
                    }

                    listobject.Add(obj);
                }
                return listobject;
            }
            catch (Exception ex)
            {

                throw ex;
            }
        }
        /// <summary>
        /// 判断属性是否在对象中存在 区分大小写
        /// </summary>
        /// <param name="instance"></param>
        /// <param name="propertyName"></param>
        /// <returns></returns>
        public static bool ContainProperty(this object instance, string propertyName)
        {
            if (instance != null && !string.IsNullOrEmpty(propertyName))
            {
                PropertyInfo _findedPropertyInfo = instance.GetType().GetProperty(propertyName);
                return (_findedPropertyInfo != null);
            }
            return false;
        }
        /// <summary>
        /// 判断属性是否在对象中存在 不区分大小写
        /// </summary>
        /// <param name="instance"></param>
        /// <param name="propertyName"></param>
        /// <returns></returns>
        public static bool ContainPropertyAll(this object instance, string propertyName)
        {
            BindingFlags flag = BindingFlags.Public | BindingFlags.IgnoreCase | BindingFlags.Instance;
            if (instance != null && !string.IsNullOrEmpty(propertyName))
            {
                PropertyInfo _findedPropertyInfo = instance.GetType().GetProperty(propertyName, flag);
                return (_findedPropertyInfo != null);
            }
            return false;
        }
    }
}

调用方法

//生成sql语句
//有缺点 不要有Name,NameQQQ 这种一个字段前面包含另一个字段的
string sql= 'insert into Lz_Hdjl(Id,Title) values(@Id,@Title)';
string ready = sql.SetParamInfo(info);

//table转对象
DataTable table = new DataTable();//查询的table结果集
MyObject info = new MyObject();
info = tablet.GetObjectInfo<MyObject>();

//table转对象集
DataTable table = new DataTable();//查询的table结果集
List<MyObject> info = tablet.GetObjectInfos<MyObject>();