基于SQL Server的SQL语句生成以及数据库互转换

编写这个类是因为自己太懒了,一些简单的SQL语句自己写闲麻烦,用EF又觉得臃肿,故花了几个小时写了这个基于反射的小东西

目前有几个功能:

1.通过实体类生成简单的SQL语句 添加,更新,删除,查询。

2.通过实体类生成LinqXml节点 目前支持属性模式和节点模式,不支持混合模式。

3.通过LinqXml节点数据,装载实体数据。

4.通过DataRow数据,装载实体。

如果有BUG请回复告知谢谢。

using System;
using System.Collections.Generic<div>using System.Linq;
using System.Text;
using System.Data;
using System.Xml.Linq;
using System.Data.SqlClient;

namespace Ethan.CommonLibrary
{
    /// <summary>
    /// 实体属性装载/拆装工厂
    /// </summary>
    public class PropertyFactory<T>
    {
        #region 属性

        XElement _xElement;

        T _Property;

        string _SqlContent;

        IList<SqlParameter> _listParam;

        /// <summary>
        /// 参数列表
        /// </summary>
        public IList<SqlParameter> ListParam
        {
            get
            {
                if (_listParam != null)
                {
                    return _listParam;
                }
                return null;
            }

            set
            {
                _listParam = value;
            }
        }

        /// <summary>
        /// xml节点信息
        /// </summary>
        public XElement xElement
        {
            get
            {
                if (_xElement != null)
                {
                    return _xElement;
                }

                return null;
            }
        }

        /// <summary>
        /// 实体
        /// </summary>
        public T Property
        {
            get
            {
                if (_Property != null)
                {
                    return _Property;
                }

                return default(T);
            }
        }

        /// <summary>
        /// SQL语句
        /// </summary>
        public string SqlContent
        {
            get
            {
                if (_SqlContent != null)
                {
                    return _SqlContent;
                }
                else
                {
                    return string.Empty;
                }
            }
            set
            {
                _SqlContent = value;
            }
        }

        /// <summary>
        /// 参数模式下的参数符号
        /// <example>SQL语句下的@ 符号；Oracle的：号 </example>
        /// </summary>
        public string ParametersFlag { get; set; }

        #endregion

        #region 空构造函数 

        /// <summary>
        /// 空构造啥事也不做
        /// </summary>
        public PropertyFactory()
        {
           
        }

        #endregion

        #region XML相关

        /// <summary>
        /// 装载实体
        /// </summary>
        public PropertyFactory(XElement xe, T property)
        {
            Type type = typeof(T);
            var props = type.GetProperties();
           
            foreach (var item in props)
            {
                var xA = xe.Attribute(XName.Get(item.Name));
                if (xA != null)
                {
                    object value = null;

                    if (string.IsNullOrEmpty(xA.Value))
                    {
                        continue;
                    }

                    if (item.PropertyType.Equals(typeof(string)))
                    {
                        value = xA.Value;
                    }
                    else if (item.PropertyType.Equals(typeof(bool)))
                    {
                        value = bool.Parse(xA.Value);
                    }
                    else if (item.PropertyType.Equals(typeof(Guid)))
                    {
                        value = new Guid(xA.Value);
                    }

                    else if (item.PropertyType.Equals(typeof(int)))
                    {
                        value = int.Parse(xA.Value);
                    }

                    else if (item.PropertyType.Equals(typeof(double)))
                    {
                        value = double.Parse(xA.Value);
                    }

                    else if (item.PropertyType.Equals(typeof(float)))
                    {
                        value = float.Parse(xA.Value);
                    }

                    else if (item.PropertyType.Equals(typeof(decimal)))
                    {
                        value = decimal.Parse(xA.Value);
                    }

                    else if (item.PropertyType.Equals(typeof(DateTime)))
                    {
                        value = DateTime.Parse(xA.Value);
                    }

                    item.SetValue(property, value, null);
                }
            }
            this._Property = property;

        }

        /// <summary>
        /// 拆分实体为XML
        /// </summary>
        public PropertyFactory(T property)
        {
            Type type = typeof(T);
            var props = type.GetProperties();

            XElement xe = new XElement(type.Name);

            foreach (var item in props)
            {
                var value = item.GetValue(property, null);
                if (value == null)
                {
                    continue;
                }
                xe.SetAttributeValue(item.Name, value);
            }

            this._xElement = xe;
            type = null;
            props = null;
            xe = null;

        }

         /// <summary>
        /// 拆分实体为XML节点
        /// </summary>
        /// <param name="property">待装载的实体数据</param>
        /// <param name="IsValueModel">是否为节点模式</param>
        public PropertyFactory(T property, bool IsNodeModel)
        {
            Type type = typeof(T);
            var props = type.GetProperties();

            XElement xe = new XElement(type.Name);

            if (!IsNodeModel)
            {
                foreach (var item in props)
                {
                    var value = item.GetValue(property, null);
                    if (value == null)
                    {
                        continue;
                    }
                    xe.SetAttributeValue(item.Name, value);
                }
            }
            else
            {
                foreach (var item in props)
                {
                    var value = item.GetValue(property, null);
                    if (value == null)
                    {
                        continue;
                    }

                    if (item.PropertyType.Equals(typeof(string)))
                    {
                        value = value.ToString();
                    }
                    else if (item.PropertyType.Equals(typeof(Guid)))
                    {
                        value = new Guid(value.ToString());
                    }
                    else if (item.PropertyType.Equals(typeof(bool)))
                    {
                        value = bool.Parse(value.ToString());
                    }
                    else if (item.PropertyType.Equals(typeof(int)))
                    {
                        value = int.Parse(value.ToString());
                    }

                    else if (item.PropertyType.Equals(typeof(double)))
                    {
                        value = double.Parse(value.ToString());
                    }

                    else if (item.PropertyType.Equals(typeof(float)))
                    {
                        value = float.Parse(value.ToString());
                    }

                    else if (item.PropertyType.Equals(typeof(decimal)))
                    {
                        value = decimal.Parse(value.ToString());
                    }

                    else if (item.PropertyType.Equals(typeof(DateTime)))
                    {
                        value = DateTime.Parse(value.ToString());
                    }
                    else
                    {
                        var t = value.GetType();

                        value = ToXmlDocument(value);

                        xe.Add(value);

                        continue;
                    }

                    item.SetValue(property, value, null);

                    xe.Add(new XElement(XName.Get(item.Name), value));
                }
            }

            this._xElement = xe;
        }      

        /// <summary>
        ///  装载到XML数据实体
        /// </summary>
        /// <param name="xe">XML元素</param>
        /// <param name="property">实体</param>
        /// <param name="IsNodeModel">是否为节点模式，反之为属性模式</param>
        public PropertyFactory(XElement xe, T property, bool IsNodeModel)
        {
            Type type = typeof(T);
            var props = type.GetProperties();

            foreach (var item in props)
            {
                object xA = xe.Attribute(XName.Get(item.Name));

                if (!IsNodeModel)
                {
                    xA = xe.Attribute(XName.Get(item.Name));
                }
                else
                {
                    xA = xe.Element(XName.Get(item.Name));
                }

                if (xA != null)
                {
                    string strValue = null;
                    if (!IsNodeModel)
                    {

                        strValue = (xA as XAttribute).Value;
                    }
                    else
                    {
                        strValue = (xA as XElement).Value;
                    }

                    object value = null;

                    if (string.IsNullOrEmpty(strValue))
                    {
                        continue;
                    }

                    if (item.PropertyType.Equals(typeof(string)))
                    {
                        value = strValue;
                    }
                    else if (item.PropertyType.Equals(typeof(Guid)))
                    {
                        value = new Guid(strValue);
                    }
                    else if (item.PropertyType.Equals(typeof(bool)))
                    {
                        value = bool.Parse(strValue);
                    }
                    else if (item.PropertyType.Equals(typeof(int)))
                    {
                        value = int.Parse(strValue);
                    }

                    else if (item.PropertyType.Equals(typeof(double)))
                    {
                        value = double.Parse(strValue);
                    }

                    else if (item.PropertyType.Equals(typeof(float)))
                    {
                        value = float.Parse(strValue);
                    }

                    else if (item.PropertyType.Equals(typeof(decimal)))
                    {
                        value = decimal.Parse(strValue);
                    }

                    else if (item.PropertyType.Equals(typeof(DateTime)))
                    {
                        value = DateTime.Parse(strValue);
                    }

                    item.SetValue(property, value, null);
                }
            }
            this._Property = property;

        }

        /// <summary>
        /// 转换为XML
        /// </summary>
        /// <param name="row"></param>
        /// <returns></returns>
        public XElement ToXmlDocument(object p)
        {
            Type type = p.GetType();

            var property = type.Assembly.CreateInstance(type.FullName, false);
            var props = type.GetProperties();

            XElement xe = new XElement(type.Name);

            foreach (var item in props)
            {
                var value = item.GetValue(p, null);
                if (value == null)
                {
                    continue;
                }

                if (item.PropertyType.Equals(typeof(string)))
                {
                    value = value.ToString();
                }
                else if (item.PropertyType.Equals(typeof(Guid)))
                {
                    value = new Guid(value.ToString());
                }
                else if (item.PropertyType.Equals(typeof(bool)))
                {
                    value = bool.Parse(value.ToString());
                }
                else if (item.PropertyType.Equals(typeof(int)))
                {
                    value = int.Parse(value.ToString());
                }

                else if (item.PropertyType.Equals(typeof(double)))
                {
                    value = double.Parse(value.ToString());
                }

                else if (item.PropertyType.Equals(typeof(float)))
                {
                    value = float.Parse(value.ToString());
                }

                else if (item.PropertyType.Equals(typeof(decimal)))
                {
                    value = decimal.Parse(value.ToString());
                }

                else if (item.PropertyType.Equals(typeof(DateTime)))
                {
                    value = DateTime.Parse(value.ToString());
                }

                else if (item.PropertyType.Equals(typeof(System.Collections.Generic.IList<string>)))
                {
                    IList<string> valueList = value as IList<string>;
                    foreach (var subValue in valueList)
                    {
                        xe.Add(new XElement(XName.Get(item.Name), subValue));
                    }

                    continue;
                }

                item.SetValue(property, value, null);

                xe.Add(new XElement(XName.Get(item.Name), value));

            }

            return xe;
        }

        #endregion

        #region 数据库相关

        /// <summary>
        /// 装载DataRow数据到实体
        /// </summary>
        /// <param name="property"></param>
        /// <param name="row"></param>
        public PropertyFactory(T property, DataRow row)
        {
            Type type = typeof(T);

            var props = type.GetProperties();

            if (props == null || props.Count() <= 0)
            {
                throw new IndexOutOfRangeException("SWIET.YL.DataEntity.PropertyFactory(T property, DataRow row) 出现异常 ：异常信息 实体无属性");
            }
            

            foreach (var item in props)
            {
                if (row.Table.Columns.Contains(item.Name))
                {
                    object value = row[item.Name];

                    if (value == DBNull.Value)
                    {
                        continue;
                    }

                    if (item.PropertyType.Equals(typeof(string)))
                    {
                        value = row[item.Name].ToString();
                    }
                    else if (item.PropertyType.Equals(typeof(bool)))
                    {
                        value = bool.Parse(row[item.Name].ToString()=="1"?"true":"false");
                    }
                    else if (item.PropertyType.Equals(typeof(Guid)))
                    {
                        value = new Guid(row[item.Name].ToString());
                    }

                    else if (item.PropertyType.Equals(typeof(int)))
                    {
                        value = int.Parse(row[item.Name].ToString());
                    }

                    else if (item.PropertyType.Equals(typeof(double)))
                    {
                        value = double.Parse(row[item.Name].ToString());
                    }

                    else if (item.PropertyType.Equals(typeof(float)))
                    {
                        value = float.Parse(row[item.Name].ToString());
                    }

                    else if (item.PropertyType.Equals(typeof(decimal)))
                    {
                        value = decimal.Parse(row[item.Name].ToString());
                    }

                    else if (item.PropertyType.Equals(typeof(DateTime)))
                    {
                        value = DateTime.Parse(row[item.Name].ToString());
                    }

                    item.SetValue(property, value, null);                  

                }
            }

            this._Property = property;
        }       

        /// <summary>
        /// 转换为实体
        /// </summary>
        /// <param name="row"></param>
        /// <returns></returns>
        public T ToProperty(DataRow row)
        {
            Type type = typeof(T);

            T property = (T)type.Assembly.CreateInstance(type.FullName, false);
            var props = type.GetProperties();

            foreach (var item in props)
            {
                if (row.Table.Columns.Contains(item.Name))
                {
                    object value = row[item.Name];

                    if (value == DBNull.Value)
                    {
                        continue;
                    }

                    if (item.PropertyType.Equals(typeof(string)))
                    {
                        value = row[item.Name].ToString().Trim();
                    }
                    else if (item.PropertyType.Equals(typeof(bool)))
                    {
                        value = (bool)row[item.Name];
                    }
                    else if (item.PropertyType.Equals(typeof(Guid)))
                    {
                        value = new Guid(row[item.Name].ToString());
                    }

                    else if (item.PropertyType.Equals(typeof(int)))
                    {
                        value = int.Parse(row[item.Name].ToString());
                    }
                    else if (item.PropertyType.Equals(typeof(double)))
                    {
                        value = double.Parse(row[item.Name].ToString());
                    }

                    else if (item.PropertyType.Equals(typeof(float)))
                    {
                        value = float.Parse(row[item.Name].ToString());
                    }

                    else if (item.PropertyType.Equals(typeof(decimal)))
                    {
                        value = decimal.Parse(row[item.Name].ToString());
                    }

                    else if (item.PropertyType.Equals(typeof(DateTime)))
                    {
                        value = DateTime.Parse(row[item.Name].ToString());
                    }

                    item.SetValue(property, value, null);

                }
            }

            return property;
        }

        public T ToProperty(XElement xe, bool IsNodeModel)
        {
            Type type = typeof(T);

            T property = (T)type.Assembly.CreateInstance(type.FullName, false);

            var props = type.GetProperties();

            foreach (var item in props)
            {
                object xA = xe.Attribute(XName.Get(item.Name));

                if (!IsNodeModel)
                {
                    xA = xe.Attribute(XName.Get(item.Name));
                }
                else
                {
                    xA = xe.Element(XName.Get(item.Name));
                }

                if (xA != null)
                {
                    string strValue = null;
                    if (!IsNodeModel)
                    {

                        strValue = (xA as XAttribute).Value;
                    }
                    else
                    {
                        strValue = (xA as XElement).Value;
                    }

                    object value = null;

                    if (string.IsNullOrEmpty(strValue))
                    {
                        continue;
                    }

                    if (item.PropertyType.Equals(typeof(string)))
                    {
                        value = strValue;
                    }
                    else if (item.PropertyType.Equals(typeof(Guid)))
                    {
                        value = new Guid(strValue);
                    }
                    else if (item.PropertyType.Equals(typeof(bool)))
                    {
                        value = bool.Parse(strValue);
                    }
                    else if (item.PropertyType.Equals(typeof(int)))
                    {
                        value = int.Parse(strValue);
                    }

                    else if (item.PropertyType.Equals(typeof(double)))
                    {
                        value = double.Parse(strValue);
                    }

                    else if (item.PropertyType.Equals(typeof(float)))
                    {
                        value = float.Parse(strValue);
                    }

                    else if (item.PropertyType.Equals(typeof(decimal)))
                    {
                        value = decimal.Parse(strValue);
                    }

                    else if (item.PropertyType.Equals(typeof(DateTime)))
                    {
                        value = DateTime.Parse(strValue);
                    }

                    item.SetValue(property, value, null);
                }
            }
            this._Property = property;
            return property;
        }

        #endregion      

        #region SQL语句相关

        /// <summary>
        /// 获取SQL语句
        /// </summary>
        /// <param name="property">实体</param>
        /// <param name="PrimaryKey">主键列表--（说明：多主键请以 逗号(,)分隔;此项用于生成WHERE条件,如果没有请传空字符串；INSERT 语句状态下 此参数无效</param>
        /// <param name="sqltype">SQL语句类型</param>
        public PropertyFactory(T property, SQLType sqltype,string PrimaryKey)
        {
            Type type = typeof(T);
            var props = type.GetProperties();

            string template = GetSQLTemplate(sqltype); //SQL模板
            template = template.Replace("{@TableName}", type.Name);

            KeyValuePair<string, IList<SqlParameter>> item = new KeyValuePair<string, IList<SqlParameter>>();

            switch (sqltype)
            {
                case SQLType.INSERT:
                    item = CreateInsertCommand(property, props, template);
                    break;
                case SQLType.UPDATE:
                    item = CreateUpdateCommand(property, props, template, PrimaryKey);
                    break;
                case SQLType.DELETE:
                    item = CreateDeleteCommand(property, props, template, PrimaryKey);
                    break;
                case SQLType.SELECT:
                    item = CreateSelectCommand(property, props, template, PrimaryKey);
                    break;
                default:

                    break;
            }

            this._SqlContent = item.Key;
            this._listParam = item.Value;
        }

        /// <summary>
        /// 获取SQL语句
        /// </summary>
        /// <param name="property">实体</param>
        /// <param name="PrimaryKey">主键列表--（说明：多主键请以 逗号(,)分隔;INSERT 语句状态下 此参数无效</param>
        /// <param name="sqltype">SQL语句类型</param>
        /// <param name="ParmExtend">此项用于生成WHERE条件,如果没有请传空字符串</param>
        public PropertyFactory(T property, SQLType sqltype, string PrimaryKey, string ParmExtend)
        {
            Type type = typeof(T);
            var props = type.GetProperties();

            string template = GetSQLTemplate(sqltype); //SQL模板
            template = template.Replace("{@TableName}", type.Name);

            KeyValuePair<string, IList<SqlParameter>> item = new KeyValuePair<string, IList<SqlParameter>>();

            switch (sqltype)
            {
                case SQLType.INSERT:
                    item = CreateInsertCommand(property, props, template);
                    break;
                case SQLType.UPDATE:
                    item = CreateUpdateCommand(property, props, template, PrimaryKey);
                    break;
                case SQLType.DELETE:
                    item = CreateDeleteCommand(property, props, template, PrimaryKey);
                    break;
                case SQLType.SELECT:
                    item = CreateSelectCommand(property, props, template, PrimaryKey, ParmExtend);
                    break;
                default:

                    break;
            }

            this._SqlContent = item.Key;
            this._listParam = item.Value;
        }

        #region SQL语句生成处理

        /// <summary>
        /// 生成Select语句
        /// </summary>
        /// <param name="property">实体</param>
        /// <param name="props">实体信息</param>
        /// <param name="template">模板</param>
        /// <param name="PrimaryKey">主键</param>
        /// <returns></returns>
        public KeyValuePair<string, IList<SqlParameter>> CreateSelectCommand(T property, System.Reflection.PropertyInfo[] props, string template, string PrimaryKey)
        {
            StringBuilder str_SQLWhere = new StringBuilder();
            IList<SqlParameter> parms = new List<SqlParameter>();

            if (PrimaryKey.Length > 0) //判断是否有WHERE条件
            {
                str_SQLWhere.Append(" WHERE ");
            }

            var pkList = PrimaryKey.Split(',');

            foreach (var item in props)
            {
                object value = item.GetValue(property, null);
                if (value == null)
                {
                    continue;
                }

                if (pkList != null && pkList.Count() > 0)
                {
                    if (pkList.Contains(item.Name))
                    {
                        str_SQLWhere.AppendFormat(" {0}=@{0} ", item.Name);
                        str_SQLWhere.Append(" AND ");
                        parms.Add(this.CreateSqlParameter(item, value));
                    }
                }
            }

            string where = str_SQLWhere.ToString();

            if (where.Contains("AND"))
            {
                where = where.Remove(where.LastIndexOf("AND"));
            }
            else
            {
                where = string.Empty;
            }

            template = template.Replace("{@WHERE}", where);

            return new KeyValuePair<string, IList<SqlParameter>>(template, parms);
        }

        /// <summary>
        /// 生成Select语句
        /// </summary>
        /// <param name="property">实体</param>
        /// <param name="props">实体信息</param>
        /// <param name="template">模板</param>
        /// <param name="PrimaryKey">主键</param>
        /// <param name="ParmExtend">拓展参数</param>
        /// <returns></returns>
        public KeyValuePair<string, IList<SqlParameter>> CreateSelectCommand(T property, System.Reflection.PropertyInfo[] props, string template, string PrimaryKey, string ParmExtend)
        {
            StringBuilder str_SQLWhere = new StringBuilder();
            IList<SqlParameter> parms = new List<SqlParameter>();

            if (PrimaryKey.Length > 0) //判断是否有WHERE条件
            {
                str_SQLWhere.Append(" WHERE ");
            }

            var pkList = PrimaryKey.Split(',');

            foreach (var item in props)
            {
                object value = item.GetValue(property, null);
                if (value == null)
                {
                    continue;
                }

                if (pkList != null && pkList.Count() > 0)
                {
                    if (pkList.Contains(item.Name))
                    {
                        str_SQLWhere.AppendFormat(" {0}=@{0} ", item.Name);
                        str_SQLWhere.Append(" AND ");
                        parms.Add(this.CreateSqlParameter(item, value));
                    }
                }
            }

            string where = str_SQLWhere.ToString();

            if (where.Contains("AND"))
            {
                where = where.Remove(where.LastIndexOf("AND"));
            }
            else
            {
                where = string.Empty;
            }

            template = template.Replace("{@WHERE}", where);

            template = template + "  " + ParmExtend;

            return new KeyValuePair<string, IList<SqlParameter>>(template, parms);
        }

        /// <summary>
        /// 生成DELETE SQL语句
        /// </summary>
        /// <param name="property">实体数据</param>
        /// <param name="props">属性信息列表</param>
        /// <param name="template">SQL语句模板</param>
        /// <param name="PrimaryKey">主键列表</param>
        /// <returns>键值对</returns>
        public KeyValuePair<string, IList<SqlParameter>> CreateDeleteCommand(T property, System.Reflection.PropertyInfo[] props, string template, string PrimaryKey)
        {
            StringBuilder str_SQLWhere = new StringBuilder();
            IList<SqlParameter> parms = new List<SqlParameter>();

            if (PrimaryKey.Length > 0) //判断是否有WHERE条件
            {
                str_SQLWhere.Append(" WHERE ");
            }

            var pkList = PrimaryKey.Split(',');

            foreach (var item in props)
            {
                object value = item.GetValue(property, null);
                if (value == null)
                {
                    continue;
                }

                if (pkList != null && pkList.Count() > 0)
                {
                    if (pkList.Contains(item.Name))
                    {
                        str_SQLWhere.AppendFormat(" {0}=@{0} ", item.Name);
                        str_SQLWhere.Append(" AND ");
                        parms.Add(this.CreateSqlParameter(item, value));
                    }
                }
            }

            string where = str_SQLWhere.ToString();

            if (where.Contains("AND"))
            {
                where = where.Remove(where.LastIndexOf("AND"));
            }
            else
            {
                where = string.Empty;
            }

            template = template.Replace("{@WHERE}", where);

            return new KeyValuePair<string, IList<SqlParameter>>(template, parms);

        }

        /// <summary>
        /// 生成UPDATE SQL语句
        /// </summary>
        /// <param name="property">实体数据</param>
        /// <param name="props">属性信息列表</param>
        /// <param name="template">SQL语句模板</param>
        /// <param name="PrimaryKey">主键列表</param>
        /// <returns>键值对</returns>
        public KeyValuePair<string, IList<SqlParameter>> CreateUpdateCommand(T property, System.Reflection.PropertyInfo[] props, string template, string PrimaryKey)
        {
            StringBuilder str_TableColumn = new StringBuilder();
            StringBuilder str_SQLWhere = new StringBuilder();
            IList<SqlParameter> parms = new List<SqlParameter>();

            IList<SqlParameter> parmw = new List<SqlParameter>();

            if (PrimaryKey.Length > 0) //判断是否有WHERE条件
            {
                str_SQLWhere.Append(" WHERE ");
            }

            var pkList = PrimaryKey.Split(',');

            foreach (var item in props)
            {
                object value = item.GetValue(property, null);
                if (value == null || value.ToString() == string.Empty)
                {
                    continue;
                }

                if (pkList != null && pkList.Count() > 0)
                {
                    if (pkList.Contains(item.Name))
                    {
                        str_SQLWhere.AppendFormat("{0}=@{0}", item.Name);
                        str_SQLWhere.Append(" AND ");
                        parmw.Add(this.CreateSqlParameter(item, value));
                    }
                    else
                    {
                        str_TableColumn.AppendFormat("{0}=@{0},", item.Name);
                        parms.Add(this.CreateSqlParameter(item, value));
                    }
                }
                else
                {
                    str_TableColumn.AppendFormat("{0}=@{0},", item.Name);
                    parms.Add(this.CreateSqlParameter(item, value));
                }
            }

            string column = str_TableColumn.ToString();
            column = column.Remove(column.LastIndexOf(","));
            template = template.Replace("{@TableColumn&Value}", column);

            string where = str_SQLWhere.ToString();

            if (where.Contains("AND"))
            {
                where = where.Remove(where.LastIndexOf("AND"));

                foreach (var item in parmw)
                {
                    parms.Add(item);
                }
            }
            else
            {
                where = string.Empty;
            }

            template = template.Replace("{@WHERE}", where);

            return new KeyValuePair<string, IList<SqlParameter>>(template, parms);

        }

        /// <summary>
        /// 生成INSERT SQL语句
        /// </summary>
        /// <param name="property">实体数据</param>
        /// <param name="props">属性信息列表</param>
        /// <param name="template">SQL语句模板</param>
        /// <returns>键值对</returns>
        public KeyValuePair<string, IList<SqlParameter>> CreateInsertCommand(T property, System.Reflection.PropertyInfo[] props, string template)
        {
            StringBuilder str_TableColumn = new StringBuilder();
            StringBuilder str_TableColumnValues = new StringBuilder();
            IList<SqlParameter> parms = new List<SqlParameter>();

            foreach (var item in props)
            {
                object value = item.GetValue(property, null);

                if (value == null)
                {
                    continue;
                }

                str_TableColumn.AppendFormat("{0},", item.Name);
                str_TableColumnValues.AppendFormat("@{0},", item.Name);

                parms.Add(this.CreateSqlParameter(item, value));
            }

            string column = str_TableColumn.ToString();
            column = column.Remove(column.LastIndexOf(","));
            template = template.Replace("{@TableColumn}", column);

            string columnvalue = str_TableColumnValues.ToString();
            columnvalue = columnvalue.Remove(columnvalue.LastIndexOf(","));

            template = template.Replace("{@TableColumnValues}", columnvalue);

            return new KeyValuePair<string, IList<SqlParameter>>(template, parms);

        }

        /// <summary>
        /// 创建SqlParameter 参数实体
        /// </summary>
        /// <param name="item">属性信息</param>
        /// <param name="value">属性的值</param>
        /// <returns></returns>
        public SqlParameter CreateSqlParameter(System.Reflection.PropertyInfo item, object value)
        {
            var bol = value.ToString();
            if (bol == "False" || bol == "True")
            {
                switch (bol)
                {
                    case "True":
                        value = 1;
                        break;
                    case "False":
                        value = 0;
                        break;
                    default:
                        break;
                }
            }

            SqlParameter p = new SqlParameter(string.Format("@{0}", item.Name), value);

            if (item.PropertyType.Equals(typeof(string)))
            {
                p.SqlDbType = SqlDbType.VarChar;
            }
            else if (item.PropertyType.Equals(typeof(bool)))
            {
                p.SqlDbType = SqlDbType.Int;
            }
            else if (item.PropertyType.Equals(typeof(Guid)))
            {
                p.SqlDbType = SqlDbType.UniqueIdentifier;
            }
            else if (item.PropertyType.Equals(typeof(int)))
            {
                p.SqlDbType = SqlDbType.BigInt;
            }
            else if (item.PropertyType.Equals(typeof(double)))
            {
                p.SqlDbType = SqlDbType.Decimal;
            }
            else if (item.PropertyType.Equals(typeof(float)))
            {
                p.SqlDbType = SqlDbType.Decimal;
            }
            else if (item.PropertyType.Equals(typeof(decimal)))
            {
                p.SqlDbType = SqlDbType.Decimal;
            }
            else if (item.PropertyType.Equals(typeof(DateTime)))
            {
                p.SqlDbType = SqlDbType.Date;
            }

            return p;

        }

        /// <summary>
        /// 获取SQL操作模板
        /// </summary>
        /// <param name="type">操作类型</param>
        /// <returns>返回SQL语句模板</returns>
        public string GetSQLTemplate(SQLType type)
        {
            string InsertCommand = "INSERT INTO {@TableName}({@TableColumn}) Values({@TableColumnValues})";
            string SelectCommand = "SELECT * FROM {@TableName} {@WHERE} ";
            string UpdateCommand = "UPDATE {@TableName} SET {@TableColumn&Value} {@WHERE}";
            string DeleteCommand = "DELETE FROM {@TableName} {@WHERE} ";

            switch (type)
            {
                case SQLType.INSERT:
                    return InsertCommand;

                case SQLType.SELECT:
                    return SelectCommand;

                case SQLType.UPDATE:
                    return UpdateCommand;

                case SQLType.DELETE:
                    return DeleteCommand;
                default:
                    return string.Empty;

            }
        }

        #endregion

        /// <summary>
        /// 将实体转换为数据库可以执行的SQL语句以及参数列表
        /// </summary>
        /// <param name="property">实体源</param>
        /// <param name="sqltype">生成的SQL语句类型</param>
        /// <param name="PrimaryKey">数据库的主键（插入语句可以忽略）</param>
        /// <returns></returns>
        public SQLContext ToProperty(T property, SQLType sqltype, string PrimaryKey)
        {
            Type type = typeof(T);
            var props = type.GetProperties();

            string template = GetSQLTemplate(sqltype); //SQL模板
            template = template.Replace("{@TableName}", type.Name);

            KeyValuePair<string, IList<SqlParameter>> item = new KeyValuePair<string, IList<SqlParameter>>();

            switch (sqltype)
            {
                case SQLType.INSERT:
                    item = CreateInsertCommand(property, props, template);
                    break;
                case SQLType.UPDATE:
                    item = CreateUpdateCommand(property, props, template, PrimaryKey);
                    break;
                case SQLType.DELETE:
                    item = CreateDeleteCommand(property, props, template, PrimaryKey);
                    break;
                case SQLType.SELECT:
                    item = CreateSelectCommand(property, props, template, PrimaryKey);
                    break;
                default:

                    break;
            }

            this._SqlContent = item.Key;
            this._listParam = item.Value;

            return new SQLContext { SQLContent=item.Key, SQLParamList=item.Value };
        }

        /// <summary>
        /// 将实体转换为数据库可以执行的SQL语句以及参数列表
        /// </summary>
        /// <param name="property">实体源</param>
        /// <param name="sqltype">生成的SQL语句类型</param>
        /// <param name="PrimaryKey">数据库的主键（插入语句可以忽略）</param>
        /// <param name="ParmExtend">此项用于生成WHERE条件,如果没有请传空字符串</param>          
        /// <returns></returns>
        public SQLContext ToProperty(T property, SQLType sqltype, string PrimaryKey, string ParmExtend)
        {
            Type type = typeof(T);
            var props = type.GetProperties();

            string template = GetSQLTemplate(sqltype); //SQL模板
            template = template.Replace("{@TableName}", type.Name);

            KeyValuePair<string, IList<SqlParameter>> item = new KeyValuePair<string, IList<SqlParameter>>();

            switch (sqltype)
            {
                case SQLType.INSERT:
                    item = CreateInsertCommand(property, props, template);
                    break;
                case SQLType.UPDATE:
                    item = CreateUpdateCommand(property, props, template, PrimaryKey);
                    break;
                case SQLType.DELETE:
                    item = CreateDeleteCommand(property, props, template, PrimaryKey);
                    break;
                case SQLType.SELECT:
                    item = CreateSelectCommand(property, props, template, PrimaryKey, ParmExtend);
                    break;
                default:

                    break;
            }

            this._SqlContent = item.Key;
            this._listParam = item.Value;

            return new SQLContext { SQLContent = item.Key, SQLParamList = item.Value };
        }

        #endregion

    }

    /// <summary>
    /// SQL内容
    /// </summary>
    public class SQLContext
    {
        /// <summary>
        /// SQL语句
        /// </summary>
        public string SQLContent { get; set; }

        /// <summary>
        /// SQL语句对应的参数列表
        /// </summary>
        public IList<SqlParameter> SQLParamList { get; set; }
    }

    /// <summary>
    /// SQL语句类型
    /// </summary>
    public enum SQLType
	{
        /// <summary>
        /// 查询
        /// </summary>
        SELECT,

        /// <summary>
        /// 添加
        /// </summary>
        INSERT,

        /// <summary>
        ///更新 
        /// </summary>
        UPDATE,

        /// <summary>
        /// 删除
        /// </summary>
        DELETE	         
    }   

}
</div>