usingSystem;
usingSystem.Collections.Generic;
usingSystem.Data;
usingSystem.Data.SqlClient;
usingSystem.Linq;
usingSystem.Reflection;
usingSystem.Text;
namespaceCR
{
#region 定义的特性
/// <summary>
/// 定义实体与数据库的映射关系 (tabName:表名 primaryKey:主键列名 autoPrimarykey:主键是否自动编号)
/// </summary>
[AttributeUsage(AttributeTargets.Class, AllowMultiple = true)] // multiuse attribute
publicclassTab : Attribute //从Attribute 继承，写一个自定义属性
{
/// <summary>
/// 定义实体与数据库的映射关系 (tabName:表名 primaryKey:主键列名 autoPrimarykey:主键是否自动编号)
/// </summary>
/// <param name="tabName"></param>
/// <param name="primaryKey"></param>
/// <param name="autoPrimarykey"></param>
publicTab(stringtabName, stringprimaryKey, boolautoPrimarykey)
{
this.TabName = tabName;
this.Primarykey = primaryKey;
this.AutoPrimarykey = autoPrimarykey;
}
/// <summary>
/// 表名
/// </summary>
publicstringTabName { get; set; }
/// <summary>
/// 主键名
/// </summary>
publicstringPrimarykey { get; set; }
/// <summary>
/// 主键是否为自动编号，即不需要程序生成
/// </summary>
publicboolAutoPrimarykey { get; set; }
}
/// <summary>
/// 指定该属性绑定的字段名
/// </summary>
[AttributeUsage(AttributeTargets.Property, AllowMultiple = true)] // multiuse attribute
publicclassBind : Attribute //从Attribute 继承，写一个自定义属性
{
/// <summary>
/// 指定该属性绑定的字段名
/// </summary>
/// <param name="name"></param>
publicBind(stringname)
{
this.Name = name;
}
/// <summary>
/// 绑定名
/// </summary>
publicstringName { get; set; }
}
#endregion
/// <summary>
/// 数据持久化相关操作，可用于简单的实体与数据库对象的转换,简单的增删改查操作
/// </summary>
publicclassORM
{
#region 实体与ADO.net对象的转换
/// <summary>
/// 把实体属性生成键值对
/// </summary>
/// <param name="entity"></param>
/// <returns></returns>
privatestaticDictionary<string, object> GetPropertyList(objectentity)
{
//第一步 ，把实体属性生成键值对
Dictionary<string, object> list = newDictionary<string, object>();
PropertyInfo[] properties = entity.GetType().GetProperties();
foreach(PropertyInfo item inproperties)
{
stringattrName = item.Name;
Type type = item.PropertyType;
//只绑定普通类型
if(type == typeof(int) || type == typeof(string) || type == typeof(DateTime) || type == typeof(Guid) || type == typeof(bool) || type== typeof(byte))
{
var bind = item.GetCustomAttributes(typeof(Bind), true);
if(bind.Length > 0)
attrName = ((Bind)bind[0]).Name;
list.Add(attrName.ToLower(), item.GetValue(entity, null));
}
}
returnlist;
}
/// <summary>
/// 根据带参数形式的sql语句和实体 生成参数数组
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="sql"></param>
/// <returns></returns>
publicstaticSqlParameter[] BindSqlParameters(stringsql, objectentity)
{
//第一步 ，把实体属性生成键值对
Dictionary<string, object> val = GetPropertyList(entity);
List<SqlParameter> parmes = newList<SqlParameter>();
string[] strArr = sql.Split(newchar[] { ',', '=', ')', '(', ' ', '%','\r'});
//查找
foreach(strings instrArr)
{
if(s.StartsWith("@"))
{
if(parmes.Exists(m => m.ParameterName == s.ToLower()))
{
continue;
}
stringkey = s.Replace("@", "").ToLower();
if(val.ContainsKey(key))
parmes.Add(newSqlParameter(s, val[key] ?? DBNull.Value));
else
thrownewArgumentException("实体属性中没有找到与数据库对应的字段", key);
}
}
if(parmes.Count == 0)
returnnull;
returnparmes.ToArray();
}
publicstaticList<T> BindEntityList<T>(DataTable tb) where T : new()
{
if(tb.Rows.Count == 0)
returndefault(List<T>);
List<T> list = newList<T>();
foreach(DataRow dr intb.Rows)
{
list.Add(BindEntity<T>(dr));
}
returnlist;
}
/// <summary>
/// 通过反射绑定一个实体,默认绑定规则： 属性名=字段名 | 特殊规则：自定义特性Bind("name")=字段名
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="tb"></param>
/// <returns></returns>
publicstaticT BindEntity<T>(DataTable tb) where T : new()
{
if(tb.Rows.Count == 0)
returndefault(T);
returnBindEntity<T>(tb.Rows[0]);
}
/// <summary>
/// 通过反射绑定一个实体,默认绑定规则： 属性名=字段名 | 特殊规则：自定义特性Bind("name")=字段名
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="row"></param>
/// <returns></returns>
publicstaticT BindEntity<T>(DataRow row) where T : new()
{
if(row == null) returndefault(T);
T entity = newT();
PropertyInfo[] properties = entity.GetType().GetProperties();
foreach(PropertyInfo item inproperties)
{
stringattrName = item.Name;
Type type = item.PropertyType;
var bind = item.GetCustomAttributes(typeof(Bind), true);
if(bind.Length > 0)
attrName = ((Bind)bind[0]).Name;
//如果DataRow列名包含此属性
if(row.Table.Columns.Contains(attrName))
{
//获取值
objectvalue = Convert.ChangeType(row[attrName], type);
item.SetValue(entity, value, null);
}
}
returnentity;
}
#endregion
#region CRUD操作
/// <summary>
/// 获取所有数据，为了性能，最多1000条
/// </summary>
/// <typeparam name="T"></typeparam>
/// <returns></returns>
publicstaticList<T> GetList<T>() where T : new()
{
//获取主键
var tabs = typeof(T).GetCustomAttributes(typeof(Tab), true);
if(tabs.Length > 0)
{
var tab = (Tab)tabs[0];
stringsql = "select top 1000 * from [{0}]";
sql = string.Format(sql, tab.TabName);
DataTable tb = CR.SqlHelper.ExecuteDataTable(sql);
returnBindEntityList<T>(tb);
}
else
{
thrownewArgumentException("必须为实体类声明tab特性才能使用这个方法", typeof(T).Name);
}
}
/// <summary>
/// 向数据库插入数据,实体必须声明 Tab特性
/// </summary>
/// <param name="entity"></param>
/// <returns></returns>
publicstaticintInsert(objectentity)
{
//获取主键
var tabs = entity.GetType().GetCustomAttributes(typeof(Tab), true);
if(tabs.Length > 0)
{
var tab = (Tab)tabs[0];
stringsql = "insert into [{0}] ({1}) values ({2}) ;";
var list = GetPropertyList(entity);
//生成set部分
StringBuilder sb = newStringBuilder();
StringBuilder sb2 = newStringBuilder();
foreach(var s inlist)
{
if(tab.AutoPrimarykey && s.Key.ToLower() == tab.Primarykey.ToLower())
continue;
sb.AppendFormat("[{0}],", s.Key);
sb2.AppendFormat("@{0},", s.Key);
}
if(sb.Length > 0)
{
sb.Length--;
sb2.Length--;
}
sql = string.Format(sql, tab.TabName, sb.ToString(), sb2.ToString());
var p = BindSqlParameters(sql, entity);
returnCR.SqlHelper.ExecuteNonQuery(CommandType.Text, sql, p);
}
else
{
thrownewArgumentException("必须为实体类声明tab特性才能使用这个方法", entity.GetType().Name);
}
}
/// <summary>
/// 修改实体,实体必须声明 Tab特性
/// </summary>
/// <param name="entity"></param>
publicstaticintUpdate(objectentity)
{
//获取主键
var tabs = entity.GetType().GetCustomAttributes(typeof(Tab), true);
if(tabs.Length > 0)
{
var tab = (Tab)tabs[0];
stringsql = "update [{0}] set {1} where {2}=@{2}";
var list = GetPropertyList(entity);
//生成set部分
StringBuilder sb = newStringBuilder();
foreach(var s inlist)
{
if(s.Key.ToLower() == tab.Primarykey.ToLower())
continue;
sb.AppendFormat("[{0}]=@{0},", s.Key);
}
if(sb.Length > 0)
sb.Length--;
sql = string.Format(sql, tab.TabName, sb.ToString(), tab.Primarykey);
var p = BindSqlParameters(sql, entity);
returnCR.SqlHelper.ExecuteNonQuery(CommandType.Text, sql, p);
}
else
{
thrownewArgumentException("必须为实体类声明tab特性才能使用这个方法", entity.GetType().Name);
}
}
/// <summary>
/// 通过主键删除一个实体对应的数据,实体必须声明 Tab特性
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="key"></param>
/// <returns></returns>
publicstaticintDelete<T>(objectkey)
{
var tabs = typeof(T).GetCustomAttributes(typeof(Tab), true);
if(tabs.Length > 0)
{
var tab = (Tab)tabs[0];
stringsql = "delete from [{0}] where [{1}]=@{1} ";
sql = string.Format(sql, tab.TabName, tab.Primarykey);
SqlParameter[] p = newSqlParameter[1];
p[0] = newSqlParameter("@"+ tab.Primarykey, key);
returnCR.SqlHelper.ExecuteNonQuery(CommandType.Text, sql, p);
}
else
{
thrownewArgumentException("必须为实体类声明tab特性才能使用这个方法", typeof(T).Name);
}
}
/// <summary>
/// 通过主键获取一个实体 ,实体必须声明 Tab特性
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="key"></param>
/// <returns></returns>
publicstaticT Get<T>(objectkey) where T : new()
{
var tabs = typeof(T).GetCustomAttributes(typeof(Tab), true);
if(tabs.Length > 0)
{
var tab = (Tab)tabs[0];
stringsql = "select top 1 * from [{0}] where [{1}]=@{1} ";
sql = string.Format(sql, tab.TabName, tab.Primarykey);
SqlParameter[] p = newSqlParameter[1];
p[0] = newSqlParameter("@"+ tab.Primarykey, key);
DataTable tb = CR.SqlHelper.ExecuteDataTable(sql, p);
returnBindEntity<T>(tb);
}
else
{
thrownewArgumentException("必须为实体类���明tab特性才能使用这个方法", typeof(T).Name);
}
}
#endregion
}
}
[HttpPost]
[ValidateInput(false)]
publicActionResult AddNews(CR.Entity.News news)
{
news.CreateTime = DateTime.Now;
CR.ORM.Insert(news);
returnRedirect("newslist");
}
[HttpPost]
[ValidateInput(false)]
publicActionResult EditNews(CR.Entity.News news)
{
var n = ORM.Get<News>(news.Id);
n.KeyWords = news.KeyWords;
n.Title = news.Title;
n.Source = news.Source;
n.Content = news.Content;
n.SortId = news.SortId;
CR.ORM.Update(n);
}
// 自定义的sql语句：
/// <summary>
/// 通过用户名查找一个账户
/// </summary>
/// <param name="username"></param>
/// <returns></returns>
publicstaticUser FindUser(stringusername)
{
stringsql = "select * from t_user where username=@username";
List<SqlParameter> p = newList<SqlParameter>();
p.Add(newSqlParameter("@username", username));
DataTable tb = CR.SqlHelper.ExecuteDataTable(sql, p.ToArray());
returnCR.ORM.BindEntity<User>(tb);
}
usingSystem;
usingSystem.Collections.Generic;
usingSystem.Linq;
usingSystem.Text;
usingCR;
namespaceCR.Entity
{
/// <summary>
/// 新闻信息
/// </summary>
[Tab("t_news","newsid",true)]
publicclassNews
{
/// <summary>
/// Id
/// </summary>
[Bind("newsid")]
publicintId { get; set; }
/// <summary>
/// 标题
/// </summary>
publicstringTitle { get; set; }
/// <summary>
/// 内容
/// </summary>
publicstringContent { get; set; }
}
}
发起投票
技术讨论
加好友
发消息
2013-11-19 10:00:31 |
赞(
操作
