protected string GetBaseSqlString(SqlStringType sqlStringType, TEntity entity = default)
{
var sb = new StringBuilder();
switch (sqlStringType)
{
case SqlStringType.Select:
sb.AppendLine($"SELECT {GetSelectValues()}");
sb.AppendLine($"FROM {TablePath}");
break;
case SqlStringType.Insert:
sb.AppendLine($"INSERT INTO {TablePath} ({GetNameColumnsForInsert()})");
sb.AppendLine($"OUTPUT INSERTED.[Id]");
sb.AppendLine($"VALUES ({GetValuesForInsert(entity)})");
break;
case SqlStringType.Update:
sb.AppendLine($"UPDATE {TablePath}");
sb.AppendLine($"SET {GetValuesForUpdate(entity)}");
sb.AppendLine($"WHERE id = {entity.id}");
break;
case SqlStringType.Delete:
sb.AppendLine($"DELETE FROM {TablePath}");
break;
}
return(sb.ToString());
}
/// <summary>
/// 获取sql的过滤条件字符串
/// </summary>
/// <param name="sqlString"></param>
/// <param name="filter"></param>
/// <param name="sqlStringType"></param>
/// <returns></returns>
private SqlCommand GetFilterSqlCommand(string sqlString, FilterBase filter, SqlStringType sqlStringType)
{
SqlCommand result = new SqlCommand(sqlString);
if (filter == null)
{
return(result);
}
//查询字段处理
if (filter.QueryFields != null && filter.QueryFields.Count > 0)
{
string queryFieldContactString = string.Join(',', filter.QueryFields);
result.Sql = result.Sql.Replace("*", queryFieldContactString);
}
//查询条件的处理
if (filter.Conditons != null && filter.Conditons.Count > 0)
{
result = GetConditionSqlCommand(result.Sql, filter.Conditons);
}
if (filter.FilterSegments != null && filter.FilterSegments.Count > 0)
{
result = GetFilterSegmentSqlCommand(result.Sql, filter.FilterSegments);
}
//处理分页和排序
result = GetPageAndSortSqlCommand(result, filter);
return(result);
}
/// <summary>
/// 获取sqlstirng
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="sqlType">insert ， update ， delete ， select</param>
/// <returns></returns>
private SqlCommand GetSqlCommand <T>(SqlStringType sqlStringType, FilterBase filter)
{
SqlCommand result = null;
string baseSqlString = string.Empty;
Dictionary <SqlStringType, string> tempSqlStringDic = null;
if (_sqlStringCache.TryGetValue(typeof(T).FullName, out tempSqlStringDic))
{
tempSqlStringDic.TryGetValue(sqlStringType, out baseSqlString);
//如果没有，标识需要重新初始化该model的sqlstring
if (string.IsNullOrWhiteSpace(baseSqlString))
{
throw new NullReferenceException("没有找到model下面的sql语句");
}
}
else
{
//如果没有，需要重新初始化该model的sqlstring
baseSqlString = InitSqlString <T>(sqlStringType);
}
result = GetFilterSqlCommand(baseSqlString, filter, sqlStringType);
return(result);
}
/// <summary>
/// 构造函数
/// </summary>
/// <param name="sStringType">SQL语句类型</param>
/// <param name="dbName">操作表名称</param>
public MakeSql(SqlStringType sStringType, string dbName)
{
//if (DbHelper == null)
//{
// DbHelper = new SqlDbHelper(ConfigurationManager.AppSettings["ConnStr"].ToString());
//}
_SqlStringType = sStringType;
_DBName = dbName;
}
/// <summary>
/// 初始化model的sql语句
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="t"></param>
/// <param name="sqlStringType"></param>
/// <returns></returns>
private string InitialSqlString <T>(SqlStringType sqlStringType, T value)
{
string result = string.Empty;
switch (sqlStringType)
{
case SqlStringType.Select:
result = GetSelectSqlString <T>();
break;
case SqlStringType.Insert:
result = GetInsertSqlString <T>();
break;
case SqlStringType.Delete:
result = GetDeleteSqlString <T>();
break;
case SqlStringType.Update:
result = GetUpdateSqlString <T>();
break;
}
Dictionary <SqlStringType, string> tempSqlStringDic;
//取不到，重新新增
if (!_sqlStringCache.TryGetValue(typeof(T).FullName, out tempSqlStringDic))
{
tempSqlStringDic = new Dictionary <SqlStringType, string>();
tempSqlStringDic.Add(sqlStringType, result);
_sqlStringCache.Add(typeof(T).FullName, tempSqlStringDic);
}
else
{
tempSqlStringDic = tempSqlStringDic ?? new Dictionary <SqlStringType, string>();
tempSqlStringDic.Add(sqlStringType, result);
}
return(result);
}
/// <summary>
/// 获取sqlstirng
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="sqlType">insert ， update ， delete ， select</param>
/// <param name="value">需要传入的对象 insert和update可用</param>
/// <returns></returns>
private SqlCommand GetSqlCommand <T>(SqlStringType sqlStringType, FilterBase filter, T value)
{
SqlCommand result = null;
string baseSqlString = string.Empty;
Dictionary <SqlStringType, string> tempSqlStringDic = null;
if (_sqlStringCache.TryGetValue(typeof(T).FullName, out tempSqlStringDic) && tempSqlStringDic.TryGetValue(sqlStringType, out baseSqlString))
{
//如果没有，标识需要重新初始化该model的sqlstring
if (string.IsNullOrWhiteSpace(baseSqlString))
{
//重新初始化数据
throw new NullReferenceException("没有找到model下面的sql语句");
}
}
else
{
//如果没有，需要重新初始化该model的sqlstring
baseSqlString = InitialSqlString <T>(sqlStringType, value);
}
//只有查询，才做过滤条件的处理
if (sqlStringType == SqlStringType.Select)
{
result = GetFilterSqlCommand(baseSqlString, filter, sqlStringType);
}
else if (sqlStringType == SqlStringType.Insert)
{
result = GetInsertSqlCommand <T>(baseSqlString, value);
}
else if (sqlStringType == SqlStringType.Delete)
{
result = GetDeleteSqlCommand <T>(baseSqlString, value);
}
else if (sqlStringType == SqlStringType.Update)
{
result = GetUpdateSqlCommand <T>(baseSqlString, value);
}
return(result);
}
/// <summary>
/// 初始化model的sql语句
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="t"></param>
/// <param name="sqlStringType"></param>
/// <returns></returns>
private string InitSqlString <T>(SqlStringType sqlStringType)
{
string result = string.Empty;
Type type = typeof(T);
var tableNamePoperty = type.GetProperty("TableName");
//如果没有这个属性，就不进行处理
if (tableNamePoperty == null)
{
throw new Exception("model 不存在TableName的属性");
}
string tableName = tableNamePoperty.GetValue(Activator.CreateInstance <T>()).ToString();
string selectSqlString = $"select * from {tableName}";
switch (sqlStringType)
{
case SqlStringType.Select:
result = selectSqlString;
break;
}
Dictionary <SqlStringType, string> tempSqlStringDic = new Dictionary <SqlStringType, string>();
tempSqlStringDic.Add(SqlStringType.Select, selectSqlString);
_sqlStringCache.Add(type.FullName, tempSqlStringDic);
return(result);
}
/// <summary>
/// 构造函数
/// </summary>
/// <param name="sStringType">SQL语句类型</param>
/// <param name="dbName">操作表名称</param>
public PG_MakeSql(SqlStringType sStringType, string dbName)
{
_SqlStringType = sStringType;
_DBName = dbName;
}
