IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[studet_info]') AND type IN ('U'))
DROP TABLE [dbo].[studet_info]
GO
CREATE TABLE [dbo].[studet_info] (
[StuNo] varchar(225) COLLATE Chinese_PRC_CI_AS NOT NULL,
[Name] varchar(255) COLLATE Chinese_PRC_CI_AS NULL,
[SchoolName] varchar(255) COLLATE Chinese_PRC_CI_AS NULL,
[isDelect] bigint DEFAULT 0 NULL
)
GO
ALTER TABLE [dbo].[studet_info] SET (LOCK_ESCALATION = TABLE)
GO
-- ----------------------------
-- Primary Key structure for table studet_info
-- ----------------------------
ALTER TABLE [dbo].[studet_info] ADD CONSTRAINT [PK__studet_i__6CDFC02137E6C2E6] PRIMARY KEY CLUSTERED ([StuNo])
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ON [PRIMARY]
GO
3.下面是封装的Utils工具类(可以就行二次改进)
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Linq.Expressions;
using System.Reflection;
using System.Text;
using Dapper;
namespace ConsoleTest.Properties;
public enum OrderType
{
DESC,ASC
}
public enum AggregateType
{
SUM,AVG,MAX,MIN
}
public class IdAttribute : Attribute
{
public bool isSnowflake { get; set; }
public bool isAutoSetp { get; set; }
}
public class TableNameAttribute : Attribute
{
public string? name { get; set; }
}
/// <summary>
/// Author:BertXie
/// </summary>
/// <typeparam name="T"></typeparam>
public class LambdaExpressions<T>
{
//SQL语句
private StringBuilder sqlExecStatement = new StringBuilder();
//反射获取属性
private Type type;
private string field;
private string? tableName;
//属性名集合
private List<string> propertyName = new List<string>();
public LambdaExpressions()
{
type = typeof(T);
tableName = type.Name;
foreach (PropertyInfo propertyInfo in type.GetProperties())
{
propertyName.Add(propertyInfo.Name);
}
foreach (Attribute customAttribute in type.GetCustomAttributes())
{
Type typeAttribut = customAttribute.GetType();
if (typeAttribut.Name.Contains("TableNameAttribute"))
{
TableNameAttribute tableAttribute = type.GetCustomAttribute<TableNameAttribute>();
tableName = tableAttribute.name;
}
}
field = string.Join(",", propertyName);
sqlExecStatement.Append("SELECT " + field + " FROM " + tableName);
}
public int InserterOrUpdata(T target)
{
Dictionary<string,Object> dictionary = new Dictionary<string, Object>();
List<string> propertys = new List<string>();
List<Object> propertyValues = new List<Object>();
List<string> updataExecStatement = new List<string>();
bool status = false;
string fieldName = "";
Object fieldValue = new object();
foreach (PropertyInfo propertyInfo in type.GetProperties())
{
if (propertyInfo.GetValue(target) != null || (string)propertyInfo.GetValue(target) != "")
{
dictionary.Add(propertyInfo.Name,propertyInfo.GetValue(target).ToString());
if (propertyInfo.GetValue(target) is string)
{
updataExecStatement.Add(propertyInfo.Name + "=" + "'" + propertyInfo.GetValue(target) + "'");
}
else
{
updataExecStatement.Add(propertyInfo.Name + "=" + propertyInfo.GetValue(target));
}
foreach (Attribute customAttribute in propertyInfo.GetCustomAttributes())
{
Type typeAttribut = customAttribute.GetType();
if (typeAttribut.Name.Contains("IdAttribute"))
{
updataExecStatement.Remove(propertyInfo.Name + "=" + propertyInfo.GetValue(target));
IdAttribute idAttribute = propertyInfo.GetCustomAttribute<IdAttribute>();
if (idAttribute.isSnowflake)
{
dictionary[propertyInfo.Name] = Guid.NewGuid().ToString();
}
if (idAttribute.isAutoSetp)
{
dictionary.Remove(propertyInfo.Name);
}
fieldName = propertyInfo.Name;
fieldValue = propertyInfo.GetValue(target);
if (propertyInfo.GetValue(target) != null && Convert.ToInt32(propertyInfo.GetValue(target)) != 0)
{
status = true;
Console.WriteLine(propertyInfo.GetValue(target));
}
break;
}
}
}
}
if (status)
{
sqlExecStatement.Remove(0, sqlExecStatement.Length);
sqlExecStatement.Append("UPDATE " + tableName +" SET " + string.Join(",",updataExecStatement) + " WHERE " + fieldName + " = " + fieldValue + ";");
}
else
{
foreach (KeyValuePair<string,object> keyValuePair in dictionary)
{
propertys.Add(keyValuePair.Key);
if ( keyValuePair.Value is string )
{
propertyValues.Add("'"+keyValuePair.Value+"'");
}
else
{
propertyValues.Add(keyValuePair.Value);
}
}
sqlExecStatement.Remove(0, sqlExecStatement.Length);
sqlExecStatement.Append("INSERT INTO " + tableName + "(" + string.Join(",", propertys) + ")" +
" VALUES " + "(" + string.Join(",", propertyValues) + ");");
}
SqlConnection connection = null;
try
{
connection = sqlConnection();
int execute = connection.Execute(sqlExecStatement.ToString());
connection.Close();
return execute;
}
catch (Exception e)
{
connection?.Close();
throw;
}
}
public int delectById(long identification)
{
string fieldName = null;
foreach (PropertyInfo propertyInfo in type.GetProperties())
{
foreach (Attribute customAttribute in propertyInfo.GetCustomAttributes())
{
Type attribute = customAttribute.GetType();
if (attribute.Name.Contains("IdAttribute"))
{
fieldName = propertyInfo.Name;
}
}
}
sqlExecStatement.Remove(0, sqlExecStatement.Length);
sqlExecStatement.Append("DELETE FROM " + tableName + " WHERE " + fieldName + " = " + identification + ";");
SqlConnection connection = null!;
try
{
connection = sqlConnection();
int execute = connection.Execute(sqlExecStatement.ToString());
connection.Close();
return execute;
}
catch (Exception e)
{
connection?.Close();
throw;
}
}
/// <summary>
/// 获取表达树的属性名
/// </summary>
/// <param name="propery"></param>
/// <typeparam name="T"></typeparam>
/// <returns></returns>
public static string GetPropertyName(Expression<Func<T, Object>> propery)
{
var body = propery.Body.ToString();
return body.Substring(body.LastIndexOf(".", StringComparison.Ordinal) + 1).Replace(")","");
}
public LambdaExpressions<T> ignore(List<Expression<Func<T,Object>>> args)
{
foreach (Expression<Func<T,object>> expression in args)
{
propertyName.Remove(GetPropertyName(expression));
sqlExecStatement.Remove(0, sqlExecStatement.Length);
}
field = string.Join(",", propertyName);
sqlExecStatement.Append("SELECT " + field + " FROM " + tableName);
return this;
}
/// <summary>
/// 需要改进，暂时不能使用
/// </summary>
/// <param name="filds"></param>
/// <param name="asFilds"></param>
/// <param name="aggregateType"></param>
/// <returns></returns>
public LambdaExpressions<T> aggregateFunc(Expression<Func<T,Object>> filds,Expression<Func<T,Object>> asFilds,AggregateType aggregateType)
{
sqlExecStatement.Remove(0, sqlExecStatement.Length);
if (aggregateType == AggregateType.SUM)
{
propertyName.Add("SUM("+ GetPropertyName(filds) + ")" + " AS " + GetPropertyName(asFilds));
}else if (aggregateType == AggregateType.AVG)
{
propertyName.Add("AVG("+ GetPropertyName(filds) + ")" + " AS " + GetPropertyName(asFilds));
}else if (aggregateType == AggregateType.MIN)
{
propertyName.Add("MIN("+ GetPropertyName(filds) + ")" + " AS " + GetPropertyName(asFilds));
}else if (aggregateType == AggregateType.MAX)
{
propertyName.Add("MAX("+ GetPropertyName(filds) + ")" + " AS " + GetPropertyName(asFilds));
}
field = string.Join(",", propertyName);
sqlExecStatement.Append("SELECT " + field + " FROM " + tableName);
return this;
}
public LambdaExpressions<T> whereSon(Expression<Func<T, Object>> field,string whereSql,string peratorSymbol)
{
if (isOr(sqlExecStatement.ToString()))
{
sqlExecStatement.Append(GetPropertyName(field) + " " + peratorSymbol + " " + "(" + whereSql + " )");
}
else
{
sqlExecStatement.Append((sqlExecStatement.ToString().Contains("WHERE") ? " AND " : " WHERE ") +
GetPropertyName(field) + " " + peratorSymbol + " " + "(" + whereSql + " )");
}
return this;
}
public LambdaExpressions<T> delectOperation()
{
sqlExecStatement.Remove(0, sqlExecStatement.Length);
sqlExecStatement.Append("DELETE FROM " + tableName);
return this;
}
public LambdaExpressions<T> updateOperation(T soure)
{
List<string> updateField = new List<string>();
PropertyInfo[] propertyInfos = type.GetProperties();
foreach (PropertyInfo propertyInfo in propertyInfos)
{
if (propertyInfo.GetValue(soure) != null)
{
if ( propertyInfo.GetValue(soure) is string)
{
updateField.Add(propertyInfo.Name + "=" + "'" + propertyInfo.GetValue(soure) + "'");
}
else
{
updateField.Add(propertyInfo.Name + "=" + propertyInfo.GetValue(soure));
}
}
foreach (Attribute customAttribute in propertyInfo.GetCustomAttributes())
{
Type attribute = customAttribute.GetType();
if (attribute.Name.Contains("IdAttribute"))
{
updateField.Remove(propertyInfo.Name + "=" + propertyInfo.GetValue(soure));
}
}
}
sqlExecStatement.Remove(0, sqlExecStatement.Length);
sqlExecStatement.Append("UPDATE " + tableName + " SET " + string.Join(",",updateField));
return this;
}
public LambdaExpressions<T> orderBy(Expression<Func<T, Object>> field, OrderType type)
{
sqlExecStatement.Append(" ORDER BY " + GetPropertyName(field) + (type == OrderType.DESC ? " DESC" : " ASC"));
return this;
}
/// <summary>
/// 待改进
/// </summary>
/// <param name="field"></param>
/// <returns></returns>
public long countRecord(Expression<Func<T, Object>> field = null)
{
string sqlCount;
if (field == null)
{
sqlCount = "SELECT COUNT(0) FROM " + tableName;
}else
{
sqlCount = "SELECT COUNT" + "(" + GetPropertyName(field) + ") " + "FROM " + tableName;
}
Console.WriteLine("当前SQl：" + sqlCount);
return 1;
}
public LambdaExpressions<T> or()
{
sqlExecStatement.Append(" OR ");
return this;
}
public LambdaExpressions<T> pageInfo(long page,long pageSize)
{
sqlExecStatement.Append(" LIMIT " + page + "," + pageSize);
return this;
}
public LambdaExpressions<T> ge(Expression<Func<T, Object>> field, Object comparisonValue)
{
if (isOr(sqlExecStatement.ToString()))
{
sqlExecStatement.Append(GetPropertyName(field) + " >= " + comparisonValue);
}
else
{
sqlExecStatement.Append((sqlExecStatement.ToString().Contains("WHERE") ? " AND " : " WHERE ") +
GetPropertyName(field) + " >= " + comparisonValue);
}
return this;
}
public LambdaExpressions<T> le(Expression<Func<T, Object>> field, Object comparisonValue)
{
if (isOr(sqlExecStatement.ToString()))
{
sqlExecStatement.Append(GetPropertyName(field) + " <= " + comparisonValue);
}
else
{
sqlExecStatement.Append((sqlExecStatement.ToString().Contains("WHERE") ? " AND " : " WHERE ") +
GetPropertyName(field) + " <= " + comparisonValue);
}
return this;
}
public LambdaExpressions<T> net(Expression<Func<T, Object>> field, Object comparisonValue)
{
if (isOr(sqlExecStatement.ToString()))
{
sqlExecStatement.Append(GetPropertyName(field) + " <> " + comparisonValue);
}
else
{
sqlExecStatement.Append((sqlExecStatement.ToString().Contains("WHERE") ? " AND " : " WHERE ") +
GetPropertyName(field) + " <> " + comparisonValue);
}
return this;
}
public LambdaExpressions<T> gt(Expression<Func<T, Object>> field, Object comparisonValue)
{
if (isOr(sqlExecStatement.ToString()))
{
sqlExecStatement.Append(GetPropertyName(field) + " > " + comparisonValue);
}else
{
sqlExecStatement.Append((sqlExecStatement.ToString().Contains("WHERE") ? " AND " : " WHERE ") +
GetPropertyName(field) + " > " + comparisonValue);
}
return this;
}
public LambdaExpressions<T> eq(Expression<Func<T, Object>> field, Object comparisonValue)
{
if (isOr(sqlExecStatement.ToString()))
{
if ( comparisonValue is string )
{
sqlExecStatement.Append(GetPropertyName(field) + " = " + "'"+ comparisonValue +"'");
}
else
{
sqlExecStatement.Append(GetPropertyName(field) + " = " + comparisonValue);
}
}else
{
if ( comparisonValue is string)
{
sqlExecStatement.Append((sqlExecStatement.ToString().Contains("WHERE") ? " AND " : " WHERE ") +
GetPropertyName(field) + " = " + "'" + comparisonValue + "'");
}
else
{
sqlExecStatement.Append((sqlExecStatement.ToString().Contains("WHERE") ? " AND " : " WHERE ") +
GetPropertyName(field) + " = " + comparisonValue);
}
}
return this;
}
/// <summary>
/// 小于判断的条件
/// </summary>
/// <param name="field"></param>
/// <param name="comparisonValue">比较值</param>
/// <returns></returns>
public LambdaExpressions<T> lt(Expression<Func<T, Object>> field, Object comparisonValue)
{
if (isOr(sqlExecStatement.ToString()))
{
sqlExecStatement.Append(GetPropertyName(field) + " < " + comparisonValue);
}
else
{
sqlExecStatement.Append((sqlExecStatement.ToString().Contains("WHERE") ? " AND " : " WHERE ") +
GetPropertyName(field) + " < " + comparisonValue);
}
return this;
}
/// <summary>
/// 模糊查询
/// </summary>
/// <param name="field"></param>
/// <param name="likeName">模糊查询字段</param>
/// <returns></returns>
public LambdaExpressions<T> like(Expression<Func<T,Object>> field,string likeName)
{
if (likeName.Contains("WHERE") || likeName.Contains("DELETE") || likeName.Contains("INSERT") || likeName.Contains("UPDATE"))
{
return this;
}
if (!string.IsNullOrEmpty(likeName))
{
if (isOr(sqlExecStatement.ToString()))
{
sqlExecStatement.Append(GetPropertyName(field) +" LIKE " +"'"+likeName+"'");
}
else
{
sqlExecStatement.Append((sqlExecStatement.ToString().Contains("WHERE") ? " AND " : " WHERE ")+ GetPropertyName(field) +" LIKE " +"'"+likeName+"'");
}
}
return this;
}
public LambdaExpressions<T> notIn(Expression<Func<T, Object>> field, List<Object> list)
{
if (isOr(sqlExecStatement.ToString()))
{
sqlExecStatement.Append(GetPropertyName(field)+" NOT IN" + "(" + string.Join(",", list) + ")");
}
else
{
sqlExecStatement.Append((sqlExecStatement.ToString().Contains("WHERE") ? " AND " : " WHERE ") + GetPropertyName(field) +" NOT IN" + "(" + string.Join(",", list) + ")");
}
return this;
}
public LambdaExpressions<T> In(Expression<Func<T, Object>> field, List<Object> list)
{
if (isOr(sqlExecStatement.ToString()))
{
sqlExecStatement.Append(GetPropertyName(field)+" IN" + "(" + string.Join(",", list) + ")");
}
else
{
sqlExecStatement.Append((sqlExecStatement.ToString().Contains("WHERE") ? " AND " : " WHERE ") + GetPropertyName(field) +" IN" + "(" + string.Join(",", list) + ")");
}
return this;
}
public LambdaExpressions<T> between(Expression<Func<T, Object>> field,Object openInterval,Object closedInterval)
{
if (isOr(sqlExecStatement.ToString()))
{
sqlExecStatement.Append(GetPropertyName(field)+" BETWEEN " + openInterval + " AND " + closedInterval);
}
else
{
sqlExecStatement.Append((sqlExecStatement.ToString().Contains("WHERE") ? " AND " : " WHERE ") + GetPropertyName(field)+" BETWEEN " + openInterval + " AND " + closedInterval);
}
return this;
}
public LambdaExpressions<T> isNull(Expression<Func<T, Object>> field)
{
if (isOr(sqlExecStatement.ToString()))
{
sqlExecStatement.Append(GetPropertyName(field) + " IS NULL");
}
else
{
sqlExecStatement.Append((sqlExecStatement.ToString().Contains("WHERE") ? " AND " : " WHERE ") + GetPropertyName(field) + " IS NULL");
}
return this;
}
/// <summary>
/// 判断字段是否为空
/// </summary>
/// <param name="field">字段名</param>
/// <returns></returns>
public LambdaExpressions<T> isNotNull(Expression<Func<T, Object>> field)
{
if (isOr(sqlExecStatement.ToString()))
{
sqlExecStatement.Append(GetPropertyName(field) + " IS NOT NULL");
}
else
{
sqlExecStatement.Append((sqlExecStatement.ToString().Contains("WHERE") ? " AND " : " WHERE ") + GetPropertyName(field) + " IS NOT NULL");
}
return this;
}
public void lastOf(string sql)
{
sqlExecStatement.Append(sql);
}
/// <summary>
/// 判断前面的条件是不是有OR
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
private bool isOr(string sql)
{
if (sql.Substring(sql.Length - 3).Trim().Equals("OR",StringComparison.CurrentCultureIgnoreCase))
{
return true;
}
return false;
}
public SqlConnection sqlConnection()
{
//这里可以从配置文件读取
var connection = new SqlConnection("server=127.0.0.1;database=yunkao;User=sa;password=123456;");
connection.Open();
return connection;
}
public T takeAStrip()
{
Console.WriteLine("Current statement："+sqlExecStatement);
SqlConnection connection = null;
try
{
connection = sqlConnection();
T enumerable = connection.Query<T>(sqlExecStatement.ToString()).First();
connection.Close();
return enumerable;
}
catch (Exception e)
{
connection?.Close();
throw;
}
}
/// <summary>
/// 返回数据集合
/// </summary>
public List<T> dataList()
{
Console.WriteLine("Current statement："+sqlExecStatement);
SqlConnection connection = null;
try
{
connection = sqlConnection();
List<T> enumerable = connection.Query<T>(sqlExecStatement.ToString()).ToList();
connection.Close();
return enumerable;
}
catch (Exception e)
{
connection.Close();
throw;
}
}
/// <summary>
/// 操作执行器
/// </summary>
/// <returns></returns>
public long Execution()
{
Console.WriteLine("Current statement："+sqlExecStatement);
SqlConnection connection = null;
try
{
connection = sqlConnection();
int rows = connection.Execute(sqlExecStatement.ToString());
connection.Close();
return rows;
}
catch (Exception e)
{
connection?.Close();
throw;
}
}
/// <summary>
/// 浅拷贝
/// </summary>
/// <param name="soure"></param>
/// <param name="target"></param>
/// <typeparam name="S"></typeparam>
/// <typeparam name="T"></typeparam>
/// <returns></returns>
public T shallowCopyObject<S, T>(S soure, T target)
{
Type soureType = typeof(S);
PropertyInfo[] propertyInfos = soureType.GetProperties();
foreach (var propertyInfo in propertyInfos)
{
target.GetType().GetProperty(propertyInfo.Name).SetValue(target, propertyInfo.GetValue(soure));
}
return target;
}
}
4.测试代码
using System;
using System.Collections.Generic;
namespace ConsoleTest.Properties
{
[TableName(name = "studet_info")]
public class Studet
{
[Id(isSnowflake = true)]
public string StuNo { get; set; }
public string Name { get; set; }
public string SchoolName { get; set; }
}
public class Test
{
public static void main()
{
LambdaExpressions<Studet> lambdaExpressions = new LambdaExpressions<Studet>();
List<Studet> dataList = lambdaExpressions
.eq(studet => studet.Name, "Tom")
.dataList();
foreach (Studet studet1 in dataList)
{
Console.WriteLine("大学：" + studet1.SchoolName);
}
}
}
}
扫一扫
6880
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
