//------------------------------------MySQlServerORM 【简单 CURD】
using System;
using System.Collections.Generic;
using System.Linq;
namespace COMMOM
{
using C10.ZRF.Model.Filter;
using System.Configuration;
using System.Data.SqlClient;
using Dapper;
using System.Reflection;
using static COMMOM.SqlEnum;
public static class MySQlServerORM
{
private static readonly string con = ConfigurationManager.ConnectionStrings[
"sqlc"].ConnectionString;
#region 查询单个实体 T GetEntityByID<T>(int id)
public static T GetEntityByID<T>(
int id)
where T :
class,
new()
{
string pkName = ReflectionAttriHelper.GetPrimaryKey(
typeof(T));
string sql = SuperSQlGet<T>
.GetSQL(SQLType.GetEntityByID_SQL);
using (SqlConnection conn =
new SqlConnection(con))
{
DynamicParameters parem =
new DynamicParameters();
parem.Add(pkName, id);
T result = conn.QueryAsync<T>
(sql, parem).Result.FirstOrDefault();
return result ??
default(T);
}
}
#endregion
#region 查询一张表的所有集合数据 IEnumerable<T> GetAllList<T>()
public static List<T> GetAllList<T>()
where T :
class,
new()
{
string sql = SuperSQlGet<T>
.GetSQL(SQLType.GetAllList_SQL);
using (SqlConnection conn =
new SqlConnection(con))
{
return (conn.QueryAsync<T>(sql).Result.ToList()) ??
default(List<T>
);
}
}
#endregion
#region 新增 bool Insert<T>(T entity)
public static bool Insert<T>(T entity)
where T :
class,
new()
{
string sql = SuperSQlGet<T>
.GetSQL(SQLType.Insert_SQl);
PropertyInfo[] pylist =
typeof(T).GetProperties().IgnorePKID();
using (SqlConnection conn =
new SqlConnection(con))
{
DynamicParameters pa =
new DynamicParameters();
pylist.ToList().ForEach(p => { pa.Add($
"{p.Name}", p.GetValue(entity)); });
return conn.ExecuteAsync(sql,pa).Result >
0 ?
true :
false;
}
}
#endregion
#region 删除操作DeleteByPrimaryKey<T>(int id)
public static bool DeleteByPrimaryKey<T>(
int id)
where T :
class,
new()
{
string sql = SuperSQlGet<T>
.GetSQL(SQLType.DeleteByPrimaryKey_SQL);
using (SqlConnection conn =
new SqlConnection(con))
{
DynamicParameters parameters =
new DynamicParameters();
parameters.Add(ReflectionAttriHelper.GetPrimaryKey(typeof(T)), id);
return conn.ExecuteAsync(sql, parameters).Result >
0;
}
}
//删除操作DeleteByEntity<T>(T entity)
public static bool DeleteByEntity<T>(T entity)
where T :
class,
new()
{
if (entity !=
null)
{
try
{
Type ty =
entity.GetType();
object obj =
null;
// ty.GetProperties().Any(c =>..... 两个都可以，还是使用下面的防止报错，效率也高些
ty.GetProperties().FirstOrDefault(c =>
{
if (c.IsDefined(
typeof(PKAttribute)))
{
obj = c.GetValue(entity);
return true;
}
else {
return false; }
});
return obj !=
null ? DeleteByPrimaryKey<T>(
int.Parse(obj.ToString())) :
false;
}
catch (Exception ex) {
throw new Exception(
"删除操作失败，原因：" +
ex.Message); }
}
return false;
}
#endregion
}
}
//--------------------------------------SuperSQlGet<T> 静态构造函数来初始化SQL语句 【获取SQL语句】
using System;
using System.Collections.Generic;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;
using static COMMOM.SqlEnum;
namespace COMMOM
{
public static class SuperSQlGet<T>
where T :
class,
new()
{
private static string GetEntityByID_SQL =
string.Empty;
private static string GetAllList_SQL =
string.Empty;
private static string Insert_SQl =
string.Empty;
private static string DeleteByPrimaryKey_SQL =
string.Empty;
static SuperSQlGet()
{
//-----------1GetEntityByID
Type ty =
typeof(T);
string pkName =
ReflectionAttriHelper.GetPrimaryKey(ty);
GetEntityByID_SQL = $
"select top 1 * from [{ReflectionAttriHelper.GetTBName(ty)}] where {pkName}=@{pkName}";
//-----------2 GetAllList
GetAllList_SQL = $
"select * from [{ReflectionAttriHelper.GetTBName(ty)}] ";
//------------3 insert
PropertyInfo[] pylist =
ty.GetProperties().IgnorePKID();
string tabPro =
string.Join(
",", pylist.Select(c => $
"{c.Name}"));
string vastrSafe =
string.Join(
",", pylist.Select(c => $
"@{c.Name}"));
Insert_SQl = $
"insert into [{ReflectionAttriHelper.GetTBName(ty)}]({tabPro}) values({vastrSafe})";
//----------4 DeleteByPrimaryKey
DeleteByPrimaryKey_SQL = $
"delete from [{ReflectionAttriHelper.GetTBName(ty)}] where {pkName}=@{pkName}";
}
public static string GetSQL(SQLType sqltype)
{
switch (sqltype)
{
case SQLType.GetEntityByID_SQL:
return GetEntityByID_SQL;
case SQLType.GetAllList_SQL:
return GetAllList_SQL;
case SQLType.Insert_SQl:
return Insert_SQl;
case SQLType.DeleteByPrimaryKey_SQL:
return DeleteByPrimaryKey_SQL;
default:
throw new Exception(
"SQl获取异常.....");
}
}
}
}
//----------------------------SqlEnum 【生成SQL使用的枚举】
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace COMMOM
{
public static class SqlEnum
{
public enum SQLType
{
GetEntityByID_SQL,
GetAllList_SQL,
Insert_SQl,
DeleteByPrimaryKey_SQL
}
}
}
//---------------------------------------ReflectionAttriHelper 【帮助类】
using C10.ZRF.Model.Filter;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;
namespace COMMOM
{
public static class ReflectionAttriHelper
{
#region GetPrimaryKey(Type ty) 获取主键的属性
public static string GetPrimaryKey(Type ty)
{
var prolist =
ty.GetProperties();
string proName =
string.Empty;
prolist.FirstOrDefault(c =>
{
if (c.IsDefined(
typeof(PKAttribute),
false))
{
proName = c.Name;
return true;
}
else {
return false; }
});
return !
string.IsNullOrEmpty(proName) ? proName :
"id";
}
#endregion
#region 获取表的映射名称 string GetTBName(Type type)
public static string GetTBName(Type type)
{
Type ty =
typeof(TabNameAttribute);
return type.IsDefined(ty) ? ((TabNameAttribute)type.GetCustomAttribute(ty,
false)).name : type.Name;
}
#endregion
#region 去掉主键的属性 PropertyInfo[] IgnorePKID(this PropertyInfo[] py )
public static PropertyInfo[] IgnorePKID(
this PropertyInfo[] py)
{
List<PropertyInfo> pylist =
new List<PropertyInfo>
();
py.ToList().ForEach(c => {
if (!c.IsDefined(
typeof(PKAttribute))) pylist.Add(c); });
return pylist.ToArray();
}
#endregion
}
}
//---------------------------Filter 【过滤器及Attribute】
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace C10.ZRF.Model.Filter
{
using System.Reflection;
[AttributeUsage(AttributeTargets.Class)]
public class TabNameAttribute : Attribute
{
public string name {
get;
set; }
public TabNameAttribute(
string tabName)
{
this.name =
tabName;
}
}
[AttributeUsage(AttributeTargets.Property)]
public class PKAttribute : Attribute
{
}
[AttributeUsage(AttributeTargets.Property)]
public class ProNameAttribute : Attribute
{
public string pname {
get;
set; }
public ProNameAttribute(
string pname)
{
this.pname =
pname;
}
}
}
//-------------------model【实体】
using System;
namespace C10.ZRF.Model.ModelView
{
using Filter;
[TabName("User")]
public class UserView
{
[PK]
public int uid {
get;
set; }
public string userName {
get;
set; }
public string userPwd {
get;
set; }
public string userPhone {
get;
set; }
public int? userAge {
get;
set; }
public bool isdel {
get;
set; }
[ProName("creatime")]
public DateTime? addTime {
get;
set; }
public override string ToString()
{
return $
"uid={this.uid},userName={this.userName},userPwd={this.userPwd},userPhone={this.userPhone},userAge={this.userAge},";
}
}
}
//-----------------------UI 【显示的界面】
#region ORM
public ActionResult GetEntityByID()
{
UserView obj = MySQlServerORM.GetEntityByID<UserView>(
6);
List<UserView> ulist = MySQlServerORM.GetAllList<UserView>
();
ViewBag.objinfo =obj==
null?
"没有查找到": $
"uid={obj.uid},姓名={obj.userName},TEL={obj.userPhone}";
string str =
string.Empty;
ulist.ForEach(c => { str += (c.ToString() +
"<br/>"); });
ViewBag.list =
str;
return View();
}
public ActionResult AddEntity()
{ //------参数需要全部提供 需要参数 ‘@userName‘，但未提供该参数
bool flag = MySQlServerORM.Insert<UserView>(
new UserView()
{
addTime =
DateTime.Now,
isdel =
false,
userAge =
19,
userName =
"qqAdd",
userPhone =
"182191777668",
userPwd =
"pwd123"
});
ViewBag.addflag = flag ?
"Addok" :
"AddError";
return View();
}
public ActionResult DeleteORM()
{
// string deleteResult= MySQlServerORM.DeleteByPrimaryKey<UserView>(5) ? "成功" : "失败";//--根据PK来删除
UserView obj =
new UserView { uid =
22 };
//--------根据实体来删除
string deleteResult = MySQlServerORM.DeleteByEntity<UserView>(obj) ?
"成功" :
"失败";
ViewBag.deleteok = $
"数据删除{deleteResult}";
return View();
}
#endregion
通过反射来手写简单的ORM SQlserver
标签：exec lis span sel tab reac targe rom color
