【手撸一个ORM】第三步、SQL语句构造器和SqlParameter封装

时间:2023-12-06 11:14:38

既然是数据库工具,自然少不了增删改查的sql语句,在这里将这些常用SQL拼接操作集成到 [SqlServerBuilder.cs] 当中,方便后面调用。

近几年在项目中一直使用Dapper操作数据库,感觉其实现的DynamicParameters特别炫,所以尝试封装了一个类似的方法  [MyDbParameters],功能比较简单,仅覆盖了工作中用到的一部分功能,算是个简陋版吧。

一、SQL语句构造器,用于生成参数化查询、更新、插入的SQL语句

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MyOrm.Commons;
using MyOrm.Reflections; namespace MyOrm.SqlBuilder
{
public class SqlServerBuilder
{
protected readonly string Prefix = "@"; #region 查询
public string Select(string table, string columns, string where, string sort, int top = )
{
if (table == null) throw new ArgumentNullException(nameof(table)); var sb = new StringBuilder("SELECT ");
if (top > )
{
sb.Append("TOP ").Append(top.ToString()).Append(" ");
} sb.Append(string.IsNullOrWhiteSpace(columns) ? "*" : columns); sb.Append(" FROM ").Append(table); if (!string.IsNullOrWhiteSpace(where))
{
sb.Append(" WHERE ").Append(where);
} if (!string.IsNullOrWhiteSpace(sort))
{
sb.Append(" ORDER BY ").Append(sort);
} return sb.ToString();
} public string PagingSelect(string table, string columns, string where, string sort, int pageIndex, int pageSize)
{
pageIndex = pageIndex <= ? : pageIndex;
pageSize = pageSize <= ? : pageSize;
where = string.IsNullOrWhiteSpace(where) ? "1=1" : where; if (pageIndex == )
{
var sql = Select(table, columns, where, sort, pageSize);
sql += $";SELECT @RecordCount=COUNT(0) FROM {table} WHERE {where}";
return sql;
} columns = string.IsNullOrWhiteSpace(columns) ? "*" : columns;
sort = string.IsNullOrWhiteSpace(sort) ? "(SELECT 1)" : sort; var sb = new StringBuilder();
sb.Append("SELECT ")
.Append(columns)
.Append(" FROM ")
.Append(table)
.Append(" WHERE ")
.Append(where)
.Append(" ORDER BY ")
.Append(sort)
.Append(" OFFSET ")
.Append((pageIndex - ) * pageSize)
.Append(" ROWS FETCH NEXT ")
.Append(pageSize)
.Append(" ROWS ONLY;");
sb.Append("SELECT @RecordCount=COUNT(0) FROM ")
.Append(table)
.Append(string.IsNullOrWhiteSpace(where) ? "" : " WHERE " + where); return sb.ToString();
} public string PagingSelect2008(string tables, string cols, string condition, string orderBy, int index, int size)
{
if (string.IsNullOrWhiteSpace(orderBy))
{
orderBy = "(select 1)";
} if (string.IsNullOrWhiteSpace(condition))
{
condition = "1=1";
} if (index == )
{
var sql =
$"SELECT TOP {size} {cols} FROM {tables} WHERE {condition} ORDER BY {orderBy};SELECT {Prefix}RecordCount=COUNT(0) FROM {tables} WHERE {condition}"; return sql;
}
else
{
var sb = new StringBuilder();
sb.Append("FROM ").Append(tables);
sb.Append(" WHERE ").Append(condition);
var sql = $@" WITH PAGEDDATA AS
(
SELECT TOP 100 PERCENT {cols}, ROW_NUMBER() OVER (ORDER BY {@orderBy}) AS FLUENTDATA_ROWNUMBER
{sb}
)
SELECT *
FROM PAGEDDATA
WHERE FLUENTDATA_ROWNUMBER BETWEEN {(index - 1) * size + 1} AND {index * size};
SELECT {Prefix}RecordCount=COUNT(0) FROM {tables} WHERE {condition}";
return sql;
}
}
#endregion #region 插入
public string InsertIfNotExists(MyEntity entityInfo, string where)
{
var sb = new StringBuilder();
sb.Append("IF NOT EXISTS (SELECT 1 FROM [")
.Append(entityInfo.TableName)
.Append("] WHERE ")
.Append(where)
.Append(")");
sb.Append(Insert(entityInfo));
return sb.ToString();
} public string Insert(MyEntity entityInfo)
{
if (entityInfo == null) throw new ArgumentNullException(nameof(entityInfo)); var sb = new StringBuilder();
var columns = new List<string>();
var parameters = new List<string>(); foreach (var prop in entityInfo.Properties.Where(p => !p.InsertIgnore))
{
columns.Add("[" + prop.FieldName + "]");
parameters.Add(Prefix + prop.Name);
} sb.Append("INSERT INTO [")
.Append(entityInfo.TableName)
.Append("] (")
.Append(string.Join(",", columns))
.Append(") VALUES (")
.Append(string.Join(",", parameters))
.Append(");SELECT SCOPE_IDENTITY();");
return sb.ToString();
}
#endregion #region 更新
public string Update(MyEntity entityInfo, string where)
{
if (entityInfo == null) throw new ArgumentNullException(nameof(entityInfo)); var sb = new StringBuilder();
sb.Append("UPDATE [")
.Append(entityInfo.TableName)
.Append("] SET "); var clauses = entityInfo.Properties.Where(p => !p.UpdateIgnore)
.Select(p => $"{p.FieldName}={Prefix}{p.Name}");
sb.Append(string.Join(",", clauses)); sb.Append(string.IsNullOrWhiteSpace(where) ? $" WHERE [{entityInfo.KeyColumn}]={Prefix}Id" : where); return sb.ToString();
} public string UpdateIgnore(MyEntity entityInfo, string[] propertyList, bool ignoreAttribute, string where)
{
if (entityInfo == null) throw new ArgumentNullException(nameof(entityInfo)); var properties = entityInfo.Properties; var updateProperties = ignoreAttribute
? properties.Where(p => !propertyList.Contains(p.Name)).ToList()
: properties.Where(p => !p.UpdateIgnore && !propertyList.Contains(p.Name)).ToList(); if (properties.Count == )
{
throw new ArgumentNullException(nameof(properties), "要更新的列为空");
} var sb = new StringBuilder();
sb.Append("UPDATE [")
.Append(entityInfo.TableName)
.Append("] SET "); var clauses = updateProperties.Select(p => $"{p.FieldName}={Prefix}{p.Name}");
sb.Append(string.Join(",", clauses));
sb.Append(string.IsNullOrWhiteSpace(where) ? $" WHERE [{entityInfo.KeyColumn}]={Prefix}Id" : where);
return sb.ToString();
} public string Update(string table, DbKvs kvs, string where)
{
if (kvs == null || kvs.Count == ) throw new ArgumentNullException(nameof(kvs));
if (string.IsNullOrWhiteSpace(table)) throw new ArgumentNullException(nameof(table)); var sb = new StringBuilder();
sb.Append("UPDATE [")
.Append(table)
.Append("] SET ");
var clauses = kvs.Select(kv => $"[{kv.Key}]={Prefix}{kv.Key}");
sb.Append(string.Join(",", clauses));
if (!string.IsNullOrWhiteSpace(where))
{
sb.Append(" WHERE ").Append(where);
} return sb.ToString();
}
#endregion
}
}

二、MyDbParameters,在Add(object obj)方法中还是用到了反射,没想好怎么优化这部分内容,先留个坑

注意:若添加了同名参数,后面的会覆盖前面的。

using System.Data;

namespace MyOrm.DbParameters
{
public class MyDbParameter
{
public string Name { get; set; } public object Value { get; set; } public ParameterDirection? Direction { get; set; }
} public class MyDbParameters
{
private readonly List<MyDbParameter> _dict = new List<MyDbParameter>(); private readonly string _prefix = "@"; public MyDbParameters()
{ } public MyDbParameters(string prefix)
{
_prefix = prefix;
} public SqlParameter[] Parameters
{
get
{
if (_dict.Count == )
{
return new SqlParameter[]{};
} var list = new List<SqlParameter>();
foreach (var item in _dict)
{
var param = new SqlParameter($@"{item.Name}", item.Value);
if (item.Direction != null)
{
param.Direction = item.Direction.Value;
}
list.Add(param);
} return list.ToArray();
}
} public void Add(string parameterName, object value)
{
var item = _dict.FirstOrDefault(d => d.Name == parameterName);
var newItem = new MyDbParameter {Name = parameterName, Value = value}; if (item == null)
{
_dict.Add(newItem);
}
else
{
item = newItem;
}
} public void Add(string parameterName, object value, ParameterDirection direction)
{
var item = _dict.FirstOrDefault(d => d.Name == parameterName);
var newItem = new MyDbParameter { Name = parameterName, Value = value, Direction = direction}; if (item == null)
{
_dict.Add(newItem);
}
else
{
item = newItem;
}
} public void Add(object obj)
{
if (obj is MyDbParameter parameter)
{
_dict.Add(parameter);
}
else if(obj is IEntity)
{
var entityInfo = MyEntityContainer.Get(obj.GetType());
foreach(var property in entityInfo.Properties.Where(p => p.IsMap))
{
Add(property.Name, property.PropertyInfo.GetValue(obj));
}
}
else
{
var properties = obj.GetType().GetProperties();
foreach (var property in properties.Where(p => p.PropertyType.IsValueType ||
p.PropertyType == typeof(string)))
{
Add(property.Name, property.GetValue(obj));
}
}
} public void AddParameters(MyDbParameters parameters)
{
foreach (var item in parameters._dict)
{
Add(item);
}
} public void Add(SqlParameter parameter)
{
Add(parameter.ParameterName, parameter.Value);
} public void Add(SqlParameter[] parameters)
{
foreach(var item in parameters)
{
Add(item);
}
}
}
}

使用方法:

var student = new Student {
Id = ,
Name = "张三"
} var updateParameters = new MyDbParameters();
updateParameters.Add(student); var searchParameters = new MyDbParameters();
searchParameters.Add("Name", "张三");
searchParameters.Add("Id", ); updateParameters.Add(searchParameters); // 获取SqlParameter[]
var sqlParameters = updateParameters.Parameters;