/// <summary>
/// 取INSERT语句的定义串
/// </summary>
/// <param name="t"></param>
/// <returns></returns>
public static string GetInsertSql <T>(this DbContext connInfo, SqlFunction sqlFunc = null, IEnumerable <string> onlyFields = null, string tableName = "")
where T : new()
{
Type t = typeof(T);
string key = $"{t.FullName}:insert:{connInfo.DbType.ToString()}:{tableName}:{GetFieldsKey(onlyFields)}:{sqlFunc?.ToString()}";
string result = "";
if (insertStr.TryGetValue(key, out result))
{
return(result);
}
TableInfo tableInfo = EntityReader.GetEntityMapping(t, true);
bool specFields = (0 < (onlyFields?.Count() ?? 0)); //指定栏位
Dictionary <string, PropertyInfo> fieldDefine = tableInfo.PropertyMappings;
StringBuilder strbld = new StringBuilder(256);
strbld.Concat("INSERT INTO ", GetTableName <T>(connInfo.DbType, tableName, true), " (");
if (!specFields)
{
strbld.Append(string.Join(",", fieldDefine.Select(c => connInfo.DbDelimiter(c.Key)).ToArray()));
}
else
{
strbld.Append(string.Join(",", onlyFields));
}
strbld.Append(") VALUES (");
if (!specFields)
{
strbld.Append(string.Join(",", fieldDefine.Select(c =>
tableInfo.IsCurrentTimeField(c.Key) ? connInfo.DbType.GetCurrentTimeFuncName() :
(sqlFunc?.ExistsField(c.Key) ?? false) ? sqlFunc?.GetSqlFunction(c.Key) :
connInfo.DbType.TreatParaName(c.Key)
).ToArray()));
}
else
{
strbld.Append(string.Join(",", onlyFields.Select(c =>
tableInfo.IsCurrentTimeField(c) ? connInfo.DbType.GetCurrentTimeFuncName() :
(sqlFunc?.ExistsField(c) ?? false) ? sqlFunc?.GetSqlFunction(c) :
connInfo.DbType.TreatParaName(c)
).ToArray()));
}
strbld.Append(" )");
result = strbld.ToString();
insertStr.TryAdd(key, result);
return(result);
}
/// <summary>
/// Start下标从零开始
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="connInfo"></param>
/// <param name="Start">0--></param>
/// <param name="Rows"></param>
/// <param name="tableName"></param>
/// <returns></returns>
public static IEnumerable <T> Select <T>(this DbContext connInfo, string sql, object param, int Start, int Rows)
where T : new()
{
if (connInfo.IsMySQL)
{
return(connInfo.Query <T>(SqlBuilder.ConvertToMySqlPagingSql(sql, Start, Rows), param));
}
else if (connInfo.DbType == DbConnType.SQLITE)
{
return(connInfo.Query <T>(SqlBuilder.ConvertToSqlitePagingSql(sql, Start, Rows), param));
}
else if (connInfo.DbType == DbConnType.POSTGRESQL)
{
return(connInfo.Query <T>(SqlBuilder.ConvertToPostgreSqlPagingSql(sql, Start, Rows), param));
}
else if (connInfo.DbType == DbConnType.MSSQL)
{
bool converted = false;
string newSql = SqlBuilder.ConvertToMsSqlPagingSql(sql, out converted);
if (converted)
{
RequestBase para = SqlBuilder.ConvertParameter(param).SetValue("pageSize", Rows).SetValue("startIndex", Start);
return(connInfo.Query <T>(newSql, para));
}
}
else if (connInfo.IsOracleDb /*&& Start < 300000*/)
{
bool converted = false;
string newSql = SqlBuilder.ConvertToOraclePagingSql(sql, out converted);
if (converted)
{
RequestBase para = SqlBuilder.ConvertParameter(param).SetValue("endIndex", Start + Rows).SetValue("startIndex", Start);
return(connInfo.Query <T>(newSql, para));
}
}
DataTable dt = null;
try
{
dt = connInfo.GetDataTablePage(sql, Start, Rows, param);
return(EntityReader.GetEntities <T>(dt));
}
finally
{
dt?.Dispose();
}
}
/// <summary>
/// 获取实体类的属性
/// </summary>
/// <param name="t"></param>
/// <returns></returns>
public static List <string> GetFields(Type t, bool isExceptField = false, params string[] onlyFields)
{
bool specFields = 0 < (onlyFields?.Length ?? 0);
if (isExceptField && !specFields)
{
throw new ArgumentException("isExceptField/onlyFields");
}
Dictionary <string, PropertyInfo> pi = EntityReader.GetTypePropertyMapping(t);
if (null == pi || 0 == pi.Count)
{
return(null);
}
List <string> result = new List <string>();
foreach (var ele in pi)
{
if (!specFields)
{
result.Add(ele.Key);
}
else
{
if (isExceptField) //排除字段
{
if (onlyFields.Contains(ele.Key, StringComparer.OrdinalIgnoreCase))
{
continue;
}
}
else
{
if (!onlyFields.Contains(ele.Key, StringComparer.OrdinalIgnoreCase))
{
continue;
}
}
result.Add(ele.Key);
}
}
return(result);
}