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); }