编写这个类是因为自己太懒了,一些简单的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>