private List<string> _LoadIdsByConditions(DbCommand command, ITableSpec table, penartur.Web.Core.DB.conditions.AbstractCondition conditions, Diapasone diapasone, JoinSpec[] joins, SortSpec[] sorts, ColumnSpec idSpec, bool allowHugeLists)
{
using(var logger = this.CreateCommandExecutionLogger()) {
command.CommandType = System.Data.CommandType.Text;
var conditionsCompiled = ConditionCompiler.Compile(conditions, this.traits);
string queryConditions = "";
if(conditionsCompiled.Key != "") queryConditions = "WHERE " + conditionsCompiled.Key;
ParamsHolder paramsHolder = conditionsCompiled.Value;
StringBuilder queryJoins = new StringBuilder();
{
foreach(var join in joins) {
queryJoins.Append(" JOIN ");
queryJoins.Append(join.additionalTableRaw.compile(this.traits));
queryJoins.Append(" ");
queryJoins.Append(join.additionalTable.compile(this.traits));
queryJoins.Append(" ON ");
queryJoins.Append(join.mainColumn.compile(this.traits));
queryJoins.Append(" = ");
queryJoins.Append(join.additionalTable.getIdSpec().compile(this.traits));
}
}
string querySorts = "";
if(sorts.Length > 0) {
List<string> sortParts = new List<string>();
foreach(SortSpec sortSpec in sorts) {
if(sortSpec.ascending) {
sortParts.Add(sortSpec.column.compile(this.traits) + " ASC");
} else {
sortParts.Add(sortSpec.column.compile(this.traits) + " DESC");
}
}
querySorts = "ORDER BY " + string.Join(", ", sortParts.ToArray());
}
string queryMain = "FROM " + table.compile(this.traits) + " " + queryJoins + " " + queryConditions;
foreach(KeyValuePair<string, string> kvp in paramsHolder.data) {
command.AddParameter(kvp.Key, kvp.Value);
}
if(!diapasone.total.HasValue) {
command.CommandText = logger.commandText = "SELECT COUNT(*) " + queryMain;
object rawCount;
//try {
rawCount = command.ExecuteScalar();
//} catch(Npgsql.NpgsqlException e) {
//throw new FLocalException("Error while trying to execute " + command.CommandText + ": " + e.Message);
//}
long count = (long)rawCount;
if(count < 1) {
diapasone.total = 0;
} else {
diapasone.total = count;
}
}
if(diapasone.total.Value < 1) {
return new List<string>();
} else {
if(diapasone.total.Value > 1000 && diapasone.count < 0 && !allowHugeLists) {
throw new CriticalException("huge list");
}
string queryLimits = "";
if(diapasone.count >= 0) {
queryLimits = "LIMIT " + diapasone.count + " OFFSET " + diapasone.start;
}
command.CommandText = logger.commandText = "SELECT " + idSpec.compile(this.traits) + " " + queryMain + " " + querySorts + " " + queryLimits;
List<string> result = new List<string>();
using(DbDataReader reader = command.ExecuteReader()) {
while(reader.Read()) {
result.Add(reader.GetValue(0).ToString());
}
}
return result;
}
}
}
private static void SetParameters(Procedure procedure, DbCommand cmd, IDictionary<string, object> suppliedParameters)
{
AddReturnParameter(cmd);
int i = 0;
foreach (var parameter in procedure.Parameters)
{
object value;
if (!suppliedParameters.TryGetValue(parameter.Name.Replace("@", ""), out value))
{
suppliedParameters.TryGetValue("_" + i, out value);
}
cmd.AddParameter(parameter.Name, value);
i++;
}
}
/// <summary>
/// 根据查询条件分页返回数据。
/// </summary>
/// <param name="command">SQL命令执行对象</param>
/// <param name="tableName">表名称</param>
/// <param name="allColumns">所有列信息</param>
/// <param name="pageIndex">当前页数</param>
/// <param name="pageSize">每页显示记录数</param>
/// <param name="totalRecords">总记录数</param>
/// <param name="criteria"></param>
/// <param name="columns">数据库信息返回列表</param>
/// <returns>命令执行对象</returns>
protected override void ConfigurePagedListCommand(DbCommand command, string tableName, Columns allColumns, int pageIndex, int pageSize, out int totalRecords, Criteria criteria = null, string[] columns = null)
{
int index;
var returnColumns = columns ?? allColumns.Select(c => c.Name);
var commandTextBuilder = new StringBuilder();
var totalRecordCommandTextBuilder = new StringBuilder();
var conditionTextBuilder = new StringBuilder();
commandTextBuilder.Append("SELECT ");
totalRecordCommandTextBuilder.AppendFormat("SELECT COUNT(*) FROM {0} ", this.GetSafeTableName(tableName));
commandTextBuilder.Append("ROW_NUMBER() OVER(ORDER BY ");
var primaryKeys = allColumns.Where(c => c.IsPrimaryKey);
// 添加排序条件。
if (criteria != null && !criteria.Orders.IsEmpty())
{
index = 1;
foreach (var order in criteria.Orders)
{
commandTextBuilder.AppendFormat((criteria.Orders.Count == index++) ? "\"{0}\" {1}" : "\"{0}\" {1},",
allColumns[order.PropertyName].Name, order.OrderBy);
}
}
else if (!primaryKeys.IsEmpty())
{
index = 1;
foreach (var item in primaryKeys)
{
commandTextBuilder.AppendFormat(primaryKeys.Count() == index ? "\"{0}\"" : "\"{0}\",", item.Name);
index++;
}
}
else
{
commandTextBuilder.AppendFormat("\"{0}\"", allColumns.FirstOrDefault().Name);
}
commandTextBuilder.Append(") AS \"RowIndex\"");
foreach (var item in returnColumns)
{
commandTextBuilder.AppendFormat(",\"{0}\"", allColumns[item].Name);
}
commandTextBuilder.AppendFormat(" FROM {0} ", this.GetSafeTableName(tableName));
var effectiveConditions = criteria?.EffectiveConditions;
// 添加查询条件。
if (!effectiveConditions.IsEmpty())
{
index = 1;
foreach (var item in effectiveConditions)
{
var fragment = this.CreateFragment(allColumns[item.PropertyName].Name, item.Op);
conditionTextBuilder.Append(fragment.Item1);
if (index < effectiveConditions.Count)
{
conditionTextBuilder.AppendFormat(" {0} ", effectiveConditions[index].JoinType);
}
if ((int)item.Op <= 5)
{
command.AddParameter($":{fragment.Item2}", item.Value);
}
index++;
}
}
if (!string.IsNullOrWhiteSpace(conditionTextBuilder.ToString()))
{
commandTextBuilder.Append("WHERE ");
commandTextBuilder.Append(conditionTextBuilder);
totalRecordCommandTextBuilder.Append("WHERE ");
totalRecordCommandTextBuilder.Append(conditionTextBuilder);
}
commandTextBuilder.Append(" Order By \"RowIndex\" ASC ");
commandTextBuilder.AppendFormat("OFFSET (:pageIndex-1)*:pageSize LIMIT :pageSize");
command.CommandType = CommandType.Text;
command.CommandText = totalRecordCommandTextBuilder.ToString();
this.RecordCommandInfo("获取总记录数", command);
command.Connection.SafeOpen();
totalRecords = Convert.ToInt32(command.ExecuteScalar());
command.AddParameter(":pageIndex", pageIndex).AddParameter(":pageSize", pageSize);
command.CommandText = commandTextBuilder.ToString();
this.RecordCommandInfo("分页查询", command);
}
private List<Dictionary<string, string>> _LoadByIds(DbCommand command, ITableSpec table, List<string> ids, bool forUpdate)
{
using(var logger = this.CreateCommandExecutionLogger()) {
command.CommandType = System.Data.CommandType.Text;
ParamsHolder paramsHolder = new ParamsHolder();
List<string> placeholder = new List<string>();
foreach(string id in ids) {
placeholder.Add(this.traits.markParam(paramsHolder.Add(id)));
}
command.CommandText = logger.commandText = "SELECT * FROM " + table.compile(this.traits) + " WHERE " + table.getIdSpec().compile(this.traits) + " IN (" + string.Join(", ", placeholder.ToArray()) + ")" + (forUpdate ? " FOR UPDATE" : "");
//command.Prepare();
foreach(KeyValuePair<string, string> kvp in paramsHolder.data) {
command.AddParameter(kvp.Key, kvp.Value);
}
Dictionary<string, Dictionary<string, string>> rawResult = new Dictionary<string, Dictionary<string, string>>();
using(DbDataReader reader = command.ExecuteReader()) {
while(reader.Read()) {
Dictionary<string, string> row = new Dictionary<string, string>();
for(int i=0; i<reader.FieldCount; i++) {
//throw new CriticalException("Name: " + reader.GetName(i));
object value = reader.GetValue(i);
string sValue;
if(value is DateTime) {
sValue = ((DateTime)value).Ticks.ToString();
} else if(value is TimeSpan) {
sValue = ((TimeSpan)value).Ticks.ToString();
} else {
sValue = value.ToString();
}
row.Add(reader.GetName(i), sValue);
}
rawResult.Add(row[table.idName], row);
}
}
List<Dictionary<string, string>> result = new List<Dictionary<string, string>>();
foreach(string id in ids) {
if(rawResult.ContainsKey(id)) {
result.Add(rawResult[id]);
}
}
return result;
}
}
/// <summary>
/// 删除数据。
/// </summary>
/// <param name="command">SQL命令执行对象</param>
/// <param name="tableName">表名称</param>
/// <param name="allColumns">所有列信息</param>
/// <param name="criteria"></param>
/// <returns>命令执行对象</returns>
protected override void ConfigureRemoveCommand(DbCommand command, string tableName, Columns allColumns, Criteria criteria = null)
{
var commandTextBuilder = new StringBuilder();
commandTextBuilder.AppendFormat("DELETE FROM {0}", this.GetSafeTableName(tableName));
var effectiveConditions = criteria?.EffectiveConditions;
if (!effectiveConditions.IsEmpty())
{
commandTextBuilder.Append(" WHERE ");
var index = 1;
foreach (var item in effectiveConditions)
{
var fragment = this.CreateFragment(allColumns[item.PropertyName].Name, item.Op);
commandTextBuilder.Append(fragment.Item1);
if (index < effectiveConditions.Count)
{
commandTextBuilder.AppendFormat(" {0} ", effectiveConditions[index].JoinType);
}
if ((int)item.Op <= 5)
{
command.AddParameter($":{fragment.Item2}", item.Value, this._dbTypeResolve.GetDbType(allColumns[item.PropertyName].DataType));
}
index++;
}
}
command.CommandType = CommandType.Text;
command.CommandText = commandTextBuilder.ToString();
this.RecordCommandInfo("删除记录", command);
}
/// <summary>
/// 根据查询条件返回所有结果。
/// </summary>
/// <param name="command">SQL命令执行对象</param>
/// <param name="tableName">表名称</param>
/// <param name="allColumns">所有列信息</param>
/// <param name="criteria"></param>
/// <param name="columns">数据库信息返回列表</param>
/// <returns>命令执行对象</returns>
protected override void ConfigureListCommand(DbCommand command, string tableName, Columns allColumns, Criteria criteria = null, string[] columns = null)
{
var commandTextBuilder = new StringBuilder();
if (columns != null && columns.Length > 0)
{
commandTextBuilder.Append("SELECT ");
var index = 0;
foreach (var item in columns)
{
index++;
commandTextBuilder.AppendFormat(columns.Length == index ? "\"{0}\"" : "\"{0}\",", allColumns[item].Name);
}
commandTextBuilder.AppendFormat(" FROM {0}", this.GetSafeTableName(tableName));
}
else
{
commandTextBuilder.AppendFormat("SELECT * FROM {0}", this.GetSafeTableName(tableName));
}
var effectiveConditions = criteria?.EffectiveConditions;
// 添加查询条件。
if (!effectiveConditions.IsEmpty())
{
commandTextBuilder.Append(" WHERE ");
var index = 1;
foreach (var item in effectiveConditions)
{
var fragment = this.CreateFragment(allColumns[item.PropertyName].Name, item.Op);
commandTextBuilder.Append(fragment.Item1);
if (index < effectiveConditions.Count)
{
commandTextBuilder.AppendFormat(" {0} ", effectiveConditions[index].JoinType);
}
if ((int)item.Op <= 5)
{
command.AddParameter($":{fragment.Item2}", item.Value, this._dbTypeResolve.GetDbType(allColumns[item.PropertyName].DataType));
}
index++;
}
}
// 添加排序条件。
if (criteria != null && !criteria.Orders.IsEmpty())
{
var index = 1;
commandTextBuilder.Append(" ORDER BY ");
foreach (var order in criteria.Orders)
{
commandTextBuilder.AppendFormat((criteria.Orders.Count == index++) ? "\"{0}\" {1}" : "\"{0}\" {1},",
allColumns[order.PropertyName].Name, order.OrderBy);
}
}
command.CommandType = CommandType.Text;
command.CommandText = commandTextBuilder.ToString();
this.RecordCommandInfo("查询所有", command);
}
/// <summary>
/// 添加或者更新数据。
/// </summary>
/// <param name="command">SQL命令执行对象</param>
/// <param name="tableName">表名称</param>
/// <param name="allColumns">所有列信息</param>
/// <returns>命令执行对象</returns>
protected override void ConfigureCreateOrUpdateCommand(DbCommand command, string tableName, Columns allColumns)
{
var primaryKeys = allColumns.Where(c => c.IsPrimaryKey);
var updateColumns = allColumns.Where(c => !c.IsPrimaryKey && !c.IsIdentity);
var insertColumns = allColumns.Where(c => !c.IsIdentity);
var commandTextBuilder = new StringBuilder();
if (primaryKeys.IsEmpty())
{
throw new Exception("实体没有设置主键！");
}
commandTextBuilder.Append("BEGIN;");
commandTextBuilder.AppendFormat("UPDATE {0} SET ", this.GetSafeTableName(tableName));
var index = 1;
foreach (var item in updateColumns)
{
var format = index++ == updateColumns.Count() ? "\"{0}\"=:{0}1" : "\"{0}\"=:{0}1,";
commandTextBuilder.AppendFormat(format, item.Name);
command.AddParameter($":{item.Name}1", item.Value, this._dbTypeResolve.GetDbType(item.DataType));
}
commandTextBuilder.Append(" WHERE ");
index = 1;
foreach (var item in primaryKeys)
{
commandTextBuilder.AppendFormat(primaryKeys.Count() == index++ ? "\"{0}\"=:{0}1" : "\"{0}\"=:{0}1 AND ", item.Name);
command.AddParameter($":{item.Name}1", item.Value, this._dbTypeResolve.GetDbType(item.DataType));
}
commandTextBuilder.Append(";");
commandTextBuilder.AppendFormat("INSERT INTO {0} (", this.GetSafeTableName(tableName));
index = 1;
foreach (var column in insertColumns)
{
commandTextBuilder.AppendFormat(index++ == insertColumns.Count() ? "\"{0}\"" : "\"{0}\",", column.Name);
}
commandTextBuilder.AppendFormat(") SELECT ");
index = 1;
foreach (var column in insertColumns)
{
var format = index++ == insertColumns.Count() ? ":{0}2" : ":{0}2,";
commandTextBuilder.AppendFormat(format, column.Name);
command.AddParameter($":{column.Name}2", column.Value, this._dbTypeResolve.GetDbType(column.DataType));
}
commandTextBuilder.AppendFormat(" WHERE NOT EXISTS(SELECT 1 FROM {0} WHERE ", this.GetSafeTableName(tableName));
index = 1;
foreach (var primaryKey in primaryKeys)
{
commandTextBuilder.AppendFormat(primaryKeys.Count() == (index++) ? "\"{0}\"=:{0}" : "\"{0}\"=:{0} AND ", primaryKey.Name);
command.AddParameter($":{primaryKey.Name}", primaryKey.Value, this._dbTypeResolve.GetDbType(primaryKey.DataType));
}
commandTextBuilder.Append("); ");
commandTextBuilder.Append("COMMIT;");
command.CommandType = CommandType.Text;
command.CommandText = commandTextBuilder.ToString();
this.RecordCommandInfo("添加或者更新数据", command);
}
/// <summary>
/// 更新数据。
/// </summary>
/// <param name="command">SQL命令执行对象</param>
/// <param name="tableName">表名称</param>
/// <param name="allColumns">所有列信息</param>
/// <param name="columns">更新列集合</param>
/// <param name="criteria"></param>
/// <returns>命令执行对象</returns>
protected override void ConfigureUpdateCommand(DbCommand command, string tableName, Columns allColumns, Columns columns, Criteria criteria = null)
{
var index = 1;
var commandTextBuilder = new StringBuilder();
commandTextBuilder.AppendFormat("UPDATE {0} SET ", this.GetSafeTableName(tableName));
foreach (var item in columns)
{
var format = index++ == columns.Count() ? "\"{0}\"=:{0}" : "\"{0}\"=:{0},";
commandTextBuilder.AppendFormat(format, columns[item.Name].Name);
command.AddParameter($":{columns[item.Name].Name}", item.Value, this._dbTypeResolve.GetDbType(columns[item.Name].DataType));
}
var effectiveConditions = criteria?.EffectiveConditions;
if (effectiveConditions.IsEmpty())
{
var primaryKeys = columns.Where(c => c.IsPrimaryKey).Select(c => c.Name);
if (primaryKeys.Any())
{
commandTextBuilder.Append(" WHERE ");
index = 1;
foreach (var item in primaryKeys)
{
commandTextBuilder.AppendFormat(primaryKeys.Count() == index++ ? " \"{0}\"=:{0}" : "\"{0}\"=:{0} AND ", columns[item].Name);
command.AddParameter($":{columns[item].Name}", columns[item].Value, this._dbTypeResolve.GetDbType(columns[item].DataType));
}
}
}
else
{
index = 1;
commandTextBuilder.Append(" WHERE ");
foreach (var item in effectiveConditions)
{
var columnName = allColumns[item.PropertyName].Name;
var fragment = this.CreateFragment(columnName, item.Op);
commandTextBuilder.Append(fragment.Item1);
if (index < effectiveConditions.Count)
{
commandTextBuilder.AppendFormat(" {0} ", effectiveConditions[index].JoinType);
}
if ((int)item.Op <= 5)
{
command.AddParameter($":{fragment.Item2}", item.Value, this._dbTypeResolve.GetDbType(columns[item.PropertyName].DataType));
}
index++;
}
}
command.CommandType = CommandType.Text;
command.CommandText = commandTextBuilder.ToString();
this.RecordCommandInfo("更新记录", command);
}
/// <summary>
/// 添加数据。
/// </summary>
/// <param name="command">SQL命令执行对象</param>
/// <param name="tableName">表名称</param>
/// <param name="allColumns">所有列信息</param>
/// <returns>命令执行对象</returns>
protected override void ConfigureCreateCommand(DbCommand command, string tableName, Columns allColumns)
{
var index = 1;
var insertColumns = allColumns.Where(c => !c.IsIdentity);
var commandTextBuilder = new StringBuilder();
commandTextBuilder.AppendFormat("INSERT INTO {0} (", this.GetSafeTableName(tableName));
foreach (var column in insertColumns)
{
commandTextBuilder.AppendFormat(index++ == insertColumns.Count() ? "\"{0}\"" : "\"{0}\",", column.Name);
}
commandTextBuilder.AppendFormat(") VALUES (");
index = 1;
foreach (var item in insertColumns)
{
var format = index++ == insertColumns.Count() ? ":{0}" : ":{0}, ";
commandTextBuilder.AppendFormat(format, item.Name);
command.AddParameter($":{item.Name}", item.Value, this._dbTypeResolve.GetDbType(item.DataType));
}
commandTextBuilder.Append(")");
if (allColumns.Any(c => c.IsIdentity))
{
var identity = allColumns.First(i => i.IsIdentity);
commandTextBuilder.AppendFormat(";SELECT currval(pg_get_serial_sequence('{0}', '{1}'));", this.GetSafeTableName(tableName), identity.Name);
}
command.CommandType = CommandType.Text;
command.CommandText = commandTextBuilder.ToString();
this.RecordCommandInfo("添加记录", command);
}
/// <summary>
/// 添加数据。
/// </summary>
/// <param name="command">SQL命令执行对象</param>
/// <param name="tableName">表名称</param>
/// <param name="allColumns">所有列信息</param>
/// <returns>命令执行对象</returns>
protected override void ConfigureCreateCommand(DbCommand command, string tableName, Columns allColumns)
{
var index = 1;
var insertColumns = allColumns.Where(c => !c.IsIdentity);
var commandTextBuilder = new StringBuilder();
commandTextBuilder.AppendFormat("INSERT INTO {0} (", this.GetSafeTableName(tableName));
foreach (var column in insertColumns)
{
commandTextBuilder.AppendFormat(index++ == insertColumns.Count() ? "[{0}]" : "[{0}],", column.Name);
}
commandTextBuilder.AppendFormat(") VALUES (");
index = 1;
foreach (var item in insertColumns)
{
var format = index++ == insertColumns.Count() ? "@{0}" : "@{0}, ";
commandTextBuilder.AppendFormat(format, item.Name);
command.AddParameter($"@{item.Name}", item.Value);
}
commandTextBuilder.Append(")");
if (allColumns.Any(c => c.IsIdentity))
{
commandTextBuilder.Append(";SELECT @@IDENTITY;");
}
command.CommandType = CommandType.Text;
command.CommandText = commandTextBuilder.ToString();
this.RecordCommandInfo("添加记录", command);
}
/// <summary>
/// 添加或者更新数据。
/// </summary>
/// <param name="command">SQL命令执行对象</param>
/// <param name="tableName">表名称</param>
/// <param name="allColumns">所有列信息</param>
/// <returns>命令执行对象</returns>
protected override void ConfigureCreateOrUpdateCommand(DbCommand command, string tableName, Columns allColumns)
{
var index = 1;
var primaryKeys = allColumns.Where(c => c.IsPrimaryKey);
var updateColumns = allColumns.Where(c => !c.IsPrimaryKey && !c.IsIdentity);
var insertColumns = allColumns.Where(c => !c.IsIdentity);
var commandTextBuilder = new StringBuilder();
if (primaryKeys.IsEmpty())
{
throw new Exception("实体没有设置主键！");
}
commandTextBuilder.AppendFormat("IF EXISTS(SELECT * FROM {0} WHERE ", this.GetSafeTableName(tableName));
foreach (var primaryKey in primaryKeys)
{
commandTextBuilder.AppendFormat(primaryKeys.Count() == (index++) ? "[{0}]=@{0}" : " [{0}]=@{0} AND", primaryKey.Name);
command.AddParameter($"@{primaryKey.Name}", primaryKey.Value);
}
commandTextBuilder.Append(") ");
commandTextBuilder.AppendFormat("UPDATE {0} SET ", this.GetSafeTableName(tableName));
index = 1;
foreach (var item in updateColumns)
{
var format = index++ == updateColumns.Count() ? "[{0}]=@{0}1" : "[{0}]=@{0}1,";
commandTextBuilder.AppendFormat(format, item.Name);
command.AddParameter($"@{item.Name}1", item.Value);
}
commandTextBuilder.Append(" WHERE ");
index = 1;
foreach (var item in primaryKeys)
{
commandTextBuilder.AppendFormat(primaryKeys.Count() == index++ ? "[{0}]=@{0}1" : "[{0}]=@{0}1 AND ", item.Name);
command.AddParameter($"@{item.Name}1", item.Value);
}
commandTextBuilder.Append(" ELSE ");
commandTextBuilder.AppendFormat("INSERT INTO {0} (", this.GetSafeTableName(tableName));
index = 1;
foreach (var column in insertColumns)
{
commandTextBuilder.AppendFormat(index++ == insertColumns.Count() ? "[{0}]" : "[{0}],", column.Name);
}
commandTextBuilder.AppendFormat(") VALUES (");
index = 1;
foreach (var column in insertColumns)
{
var format = index++ == insertColumns.Count() ? "@{0}2" : "@{0}2,";
commandTextBuilder.AppendFormat(format, column.Name);
command.AddParameter($"@{column.Name}2", column.Value);
}
commandTextBuilder.Append(")");
command.CommandType = CommandType.Text;
command.CommandText = commandTextBuilder.ToString();
this.RecordCommandInfo("添加或者更新数据", command);
}
