/// <summary>
/// Creates and executes query using the given ConnectionProvider.
/// </summary>
/// <param name="conn">Connection provider.</param>
/// <param name="cmdText">Command text.</param>
/// <param name="parameters">Command parameters.</param>
/// <param name="cmdType">Command type (stored procedure or text).</param>
/// <param name="cmdTimeout">Set timeout for specified command.</param>
/// <param name="dataTableName">Name of the datatable that will be returned. May be null.</param>
/// <returns>DataTable containing retrieved data.</returns>
public static DataTable ExecuteQuery(IConnectionProvider conn, string cmdText, DbParameterCollection parameters, CommandType cmdType, string dataTableName, int cmdTimeout)
{
IDbCommand cmd = CreateCommand(conn.DBMS, cmdText, parameters, cmdType, cmdTimeout);
cmd.Connection = conn.Connection;
IDataAdapterBuilder builder = DbmsComponentFactory.GetComponent<IDataAdapterBuilder>(conn.DBMS);
IDataAdapter adapter = builder.BuildAdapter(cmd);
bool connIsOpennedLocally = EnsureOpenConnection(conn);
try
{
AssignExistingPendingTransactionToCommand(conn, cmd);
// Execute query.
DataSet data = new DataSet();
data.Locale = CultureInfo.InvariantCulture;
adapter.Fill(data);
PopulateOutputParameterValues(parameters, cmd);
DataTable table = (data.Tables.Count > 0)
? data.Tables[0]
: new DataTable() { Locale = CultureInfo.InvariantCulture };
if (!string.IsNullOrEmpty(dataTableName))
table.TableName = dataTableName;
return table;
}
finally
{
CloseConnectionIfLocal(conn, connIsOpennedLocally);
cmd.Dispose();
IDisposable disposableAdapter = adapter as IDisposable;
if (disposableAdapter != null)
disposableAdapter.Dispose();
}
}
/// <summary>
/// Renders INSERT statement.
/// </summary>
/// <param name="insert">INSERT statement that is being rendered.</param>
/// <param name="nextSequence">Ignored. SQLite doesn't use sequences.</param>
/// <param name="dbms">Target DBMS. Different auto-id retrieval for SQL 7.0 then in newer versions.</param>
/// <param name="output">StringBuilder to which the SQL code is appended.</param>
/// <param name="parameters">SQL parameter collection to which the object's and its children's
/// parameters are added. After the rendering is done the collection contains all parameters with unique names.</param>
/// <returns><b>null</b> because automatically generated ID must be fetched via SELECT after INSERT.</returns>
public DbParameter RenderInsert(InsertStatement insert, DbParameter nextSequence, DbmsType dbms, StringBuilder output, DbParameterCollection parameters)
{
// Renders INSERT statements for DBMSs that support an auto-identity fields.
// Auto-id field may or may not be in the column-value list.
// If auto-incremented field is in the column-value list it will be skipped.
// Table may have only one auto-identity field.
// Method expects that all errors have been identified and processed in the caller.
// Renders all fields except auto-id field; thus -1 if auto-id is contained in the column-value list.
int numberOfFieldsToRender = GetTotalNumberOfFieldsToRender(insert);
AppendInsertIntoTableName(insert, dbms, output);
if (numberOfFieldsToRender > 0)
{
AppendBracketsWithAllFieldsExceptAutoId(insert, dbms, output, numberOfFieldsToRender);
AppendValuesForAllFieldsExceptAutoId(insert, dbms, output, parameters, numberOfFieldsToRender);
}
else
{
AppendDefaultValuesExpression(output);
}
// Auto ID must be fetched via SELECT after INSERT.
DbParameter autoId = null;
return autoId;
}
private static void RenderSingleQuery(SelectStatement select, DbmsType dbms, StringBuilder output, DbParameterCollection parameters)
{
// Either TOP or ORDER BY is used; but not both.
// SELECT ... FROM ...
// WHERE (ROWNUM <= @top) AND (@this.filter)
// GROUP BY ... HAVING ... ORDER BY ...
AppendSelectAndDistinctKeyWords(select, output);
AppendSelectList(select, dbms, output, parameters);
AppendFromClause(select, dbms, output);
// WHERE. We have made sure that if TOP is used then ORDER BY is not used.
if (select.Top > 0)
{
// WHERE ROWNUM <= this.top AND (this.filter).
output.Append(" WHERE ROWNUM <= " + select.Top.ToString(CultureInfo.InvariantCulture));
if (select.Where != null && !select.Where.IsEmpty)
{
output.Append(" AND (");
select.Where.Render(dbms, output, parameters);
output.Append(")");
}
}
else
{
AppenWhereWoRownum(select, dbms, output, parameters);
}
AppendGroupByClause(select, dbms, output, parameters);
AppendHavingClause(select, dbms, output, parameters);
AppendOrderByClause(select, dbms, output, parameters);
}
public DbCommandDescriptor(string text, CommandType type, IValueSet parameters)
{
_text = text;
_type = type;
_timeout = DEFAULT_TIMEOUT;
_parameters = CreateParameters(parameters);
}
/// <summary>
/// Renders INSERT statement and code that retrieves the new ID.
/// </summary>
/// <param name="insert">INSERT statement that is being rendered.</param>
/// <param name="nextSequence">Ignored. SQL Server doesn't use sequences.</param>
/// <param name="dbms">Target DBMS. Different auto-id retrieval for SQL 7.0 then in newer versions.</param>
/// <param name="output">StringBuilder to which the SQL code is appended.</param>
/// <param name="parameters">SQL parameter collection to which the object's and its children's
/// parameters are added. After the rendering is done the collection contains all parameters with unique names.</param>
/// <returns>Ouput parameter that will contain the value retrieved by RETURNING clause.</returns>
public DbParameter RenderInsert(InsertStatement insert, DbParameter nextSequence, DbmsType dbms, StringBuilder output, DbParameterCollection parameters)
{
// Renders INSERT statements for DBMSs that support an auto-identity fields.
// Auto-id field may or may not be in the column-value list.
// If auto-incremented field is in the column-value list it will be skipped.
// Table may have only one auto-identity field.
// Method expects that all errors have been identified and processed in the caller.
// Renders all fields except auto-id field; thus -1 if auto-id is contained in the column-value list.
int numberOfFieldsToRender = GetTotalNumberOfFieldsToRender(insert);
AppendInsertIntoTableName(insert, dbms, output);
if (numberOfFieldsToRender > 0)
{
AppendBracketsWithAllFieldsExceptAutoId(insert, dbms, output, numberOfFieldsToRender);
AppendValuesForAllFieldsExceptAutoId(insert, dbms, output, parameters, numberOfFieldsToRender);
}
else
{
AppendDefaultValuesExpression(output);
}
IDbColumn autoIdField = GetAutoIdField(insert.Table);
DbParameter autoId = null;
if (autoIdField != null)
{
// RETURNING id
output.Append(" RETURNING ");
autoIdField.RenderColumnName(dbms, output);
autoId = new DbParameter("?", DbType.Int32) { Direction = ParameterDirection.Output };
parameters.Add(autoId);
}
// Return auto-id DB parameter. Callers require it to retrieve the new ID value.
return autoId;
}
private static void AppendSelectList(SelectStatement select, DbmsType dbms, StringBuilder output, DbParameterCollection parameters)
{
// Select list.
if (select.SelectList != null && select.SelectList.Count > 0)
select.SelectList.Render(dbms, output, parameters);
else
output.Append("*");
}
private static void PopulateOutputParameterValues(DbParameterCollection parameters, IDbCommand cmd)
{
// Fetch output values.
for (int paramIdx = 0; paramIdx < parameters.Count; paramIdx++)
{
if (parameters[paramIdx].Direction != ParameterDirection.Input)
parameters[paramIdx].Value = ((IDataParameter)cmd.Parameters[paramIdx]).Value;
}
}
private static void AppendWhereClause(SelectStatement select, DbmsType dbms, StringBuilder output, DbParameterCollection parameters)
{
// WHERE.
if (select.Where != null && !select.Where.IsEmpty)
{
output.Append(" WHERE ");
select.Where.Render(dbms, output, parameters);
}
}
private static void AppendHavingClause(SelectStatement select, DbmsType dbms, StringBuilder output, DbParameterCollection parameters)
{
// HAVING.
if (select.Having != null && !select.Having.IsEmpty)
{
output.Append(" HAVING ");
select.Having.Render(dbms, output, parameters);
}
}
private static void AppendGroupByClause(SelectStatement select, DbmsType dbms, StringBuilder output, DbParameterCollection parameters)
{
// GROUP BY.
if (select.GroupBy != null && select.GroupBy.Fields.Count > 0)
{
output.Append(" ");
select.GroupBy.Render(dbms, output, parameters);
}
}
private static void AppendWhere(SearchCondition where, DbmsType dbms, StringBuilder output, DbParameterCollection parameters)
{
bool hasFilter = (where != null) && !where.IsEmpty;
if (hasFilter)
{
output.Append(" WHERE ");
where.Render(dbms, output, parameters);
}
}
public void Render(SelectStatement select, DbmsType dbms, StringBuilder output, DbParameterCollection parameters)
{
// Uses ROWNUM instead of TOP. If ORDER BY is used then current SELECT statement is rendered
// using sub-queries. ROWNUM is used in outer query.
bool orderByIsUsed = (select.OrderBy != null && select.OrderBy.Count > 0);
if (select.Top > 0 && orderByIsUsed)
RenderOrderByInSubQueryAndRownumInOuterQuery(select, dbms, output, parameters);
else
RenderSingleQuery(select, dbms, output, parameters);
}
public IDbCommand BuildCommand(string cmdText, DbParameterCollection parameters, CommandType cmdType, int cmdTimeout)
{
IDbCommand cmd = new OracleCommand();
cmd.CommandText = cmdText;
cmd.CommandType = cmdType;
cmd.CommandTimeout = cmdTimeout;
foreach (DbParameter par in parameters)
cmd.Parameters.Add(ConvertToNativeParameter(par, cmdType));
return cmd;
}
private static void AppendUpdateList(UpdateList list, DbmsType dbms, StringBuilder output, DbParameterCollection parameters)
{
output.Append(" SET ");
for (int setExpressionIdx = 0; setExpressionIdx < list.Count - 1; setExpressionIdx++)
{
list[setExpressionIdx].Render(dbms, output, parameters);
output.Append(", ");
}
list[list.Count - 1].Render(dbms, output, parameters);
}
/// <summary>Renders UPDATE statement.</summary>
public void RenderUpdate(UpdateStatement update, DbmsType dbms, StringBuilder output, DbParameterCollection parameters)
{
if (update.UpdateList == null || update.UpdateList.Count == 0)
throw new InvalidOperationException(Messages.SqlUpdater_SetListIsEmpty);
output.Append("UPDATE ");
update.Table.RenderTableName(dbms, output);
AppendUpdateList(update.UpdateList, dbms, output, parameters);
AppendRelations(update, dbms, output);
AppendWhere(update.Where, dbms, output, parameters);
}
public void Render(SelectStatement select, DbmsType dbms, StringBuilder output, DbParameterCollection parameters)
{
AppendSelectAndDistinctKeyWords(select, output);
AppendTopExpression(select, output);
AppendSelectList(select, dbms, output, parameters);
AppendFromClause(select, dbms, output);
AppendWhereClause(select, dbms, output, parameters);
AppendGroupByClause(select, dbms, output, parameters);
AppendHavingClause(select, dbms, output, parameters);
AppendOrderByClause(select, dbms, output, parameters);
}
/// <summary>
/// Renders DELETE statement.
/// </summary>
/// <param name="delete">DELETE statement to render.</param>
/// <param name="dbms">Target DBMS.</param>
/// <param name="output">StringBuilder to which SQL is appended.</param>
/// <param name="parameters">SQL parameter collection to which the object's and its children's
/// parameters are added. After the rendering is done the collection contains all parameters with unique names.</param>
public void Render(DeleteStatement delete, DbmsType dbms, StringBuilder output, DbParameterCollection parameters)
{
output.Append("DELETE FROM ");
delete.FromTable.RenderTableName(dbms, output);
if (delete.Where != null && !delete.Where.IsEmpty)
{
output.Append(" WHERE ");
delete.Where.Render(dbms, output, parameters);
}
}
/// <summary>
/// Renders predicate as SQL statement.
/// </summary>
/// <param name="dbms">Target DBMS.</param>
/// <param name="output">StringBuilder to which SQL is appended.</param>
/// <param name="parameters">SQL parameter collection to which the object's and its children's
/// parameters are added. After the rendering is done the collection contains all parameters with unique names.</param>
public override void Render(DbmsType dbms, StringBuilder output, DbParameterCollection parameters)
{
output.Append("(");
if (Negate)
output.Append("NOT EXISTS (");
else
output.Append("EXISTS (");
PredicateItems[0].Render(dbms, output, parameters);
output.Append("))");
}
private int ExecuteSingleTableUpdate(UpdateStatement update, DbmsType dbms, IConnectionProvider conn, out CommandExecutionStatistics lastExecutedCommandInfo, int cmdTimeout = 30)
{
DbParameterCollection parameters = new DbParameterCollection();
StringBuilder cmdText = new StringBuilder();
RenderUpdate(update, dbms, cmdText, parameters);
string command = cmdText.ToString();
lastExecutedCommandInfo = new CommandExecutionStatistics(command);
int rowsAffected = DbUtil.ExecuteNonQuery(conn, command, parameters, CommandType.Text, cmdTimeout);
lastExecutedCommandInfo.StopTime();
return rowsAffected;
}
/// <summary>Renders LIKE predicate as SQL element.</summary>
/// <param name="like">Like predicate.</param>
/// <param name="dbms">Target DBMS.</param>
/// <param name="output">StringBuilder to which SQL is appended.</param>
/// <param name="parameters">SQL parameter collection to which the object's and its children's
/// parameters are added. After the rendering is done the collection contains all parameters with unique names.</param>
public void Render(LikePredicate like, DbmsType dbms, StringBuilder output, DbParameterCollection parameters)
{
output.Append("(");
like.PredicateItems[0].Render(dbms, output, parameters);
if (like.Negate)
output.Append(" NOT LIKE ");
else
output.Append(" LIKE ");
like.PredicateItems[1].Render(dbms, output, parameters);
output.Append(")");
}
/// <summary>
/// Renders function as SQL element.
/// </summary>
/// <param name="function">Function.</param>
/// <param name="dbms">Target DBMS.</param>
/// <param name="output">StringBuilder to which SQL is appended.</param>
/// <param name="allParameters">SQL parameter collection to which the object's and its children's
/// parameters are added. After the rendering is done the collection contains all parameters with unique names.</param>
public void Render(Function function, DbmsType dbms, StringBuilder output, DbParameterCollection allParameters)
{
output.Append(function.Name);
output.Append("(");
for (int paramIdx = 0; paramIdx < function.FunctionArguments.Count - 1; paramIdx++)
{
function.FunctionArguments[paramIdx].Render(dbms, output, allParameters);
output.Append(", ");
}
function.FunctionArguments[function.FunctionArguments.Count - 1].Render(dbms, output, allParameters);
output.Append(")");
}
/// <summary>
/// Renders INSERT statement and code that retrieves the new ID.
/// </summary>
/// <param name="insert">INSERT statement that is being rendered.</param>
/// <param name="nextSequence">Ignored. SQL Server doesn't use sequences.</param>
/// <param name="dbms">Target DBMS. Different auto-id retrieval for SQL 7.0 then in newer versions.</param>
/// <param name="output">StringBuilder to which the SQL code is appended.</param>
/// <param name="parameters">SQL parameter collection to which the object's and its children's
/// parameters are added. After the rendering is done the collection contains all parameters with unique names.</param>
/// <returns>Parameter that contains the ID of the inserted row. <b>null</b> if auto-id field is not used.</returns>
public DbParameter RenderInsert(InsertStatement insert, DbParameter nextSequence, DbmsType dbms, StringBuilder output, DbParameterCollection parameters)
{
// Renders INSERT statements for DBMSs that support an auto-identity fields.
// Auto-id field may or may not be in the column-value list.
// If auto-incremented field is in the column-value list it will be skipped.
// Table may have only one auto-identity field.
// Method expects that all errors have been identified and processed in the caller.
// Renders all fields except auto-id field; thus -1 if auto-id is contained in the column-value list.
int numberOfFieldsToRender = GetTotalNumberOfFieldsToRender(insert);
AppendInsertIntoTableName(insert, dbms, output);
if (numberOfFieldsToRender > 0)
{
AppendBracketsWithAllFieldsExceptAutoId(insert, dbms, output, numberOfFieldsToRender);
AppendValuesForAllFieldsExceptAutoId(insert, dbms, output, parameters, numberOfFieldsToRender);
}
else
{
AppendDefaultValuesExpression(output);
}
DbParameter autoId = null;
if (HasAutoIdField(insert.Table))
{
autoId = new DbParameter("___newAutoIdentity___", DbType.Int32) { Direction = ParameterDirection.Output };
if (dbms == DbmsType.SqlServer_7)
{
// @@IDENTITY
output.Append(" ; set ");
autoId.Render(dbms, output, parameters);
output.Append(" = @@IDENTITY");
}
else
{
// scope_identity()
output.Append(" ; set ");
autoId.Render(dbms, output, parameters);
output.Append(" = scope_identity()");
}
// BatchQuery (if ever implemented) reads this value to assign new ID value to InsertStatement.RetrievedData property.
//insert.NewIdOutputParameterIndexAfterRenderCompleted = parameters.Count - 1;
}
// Return auto-id DB parameter. Callers require it to retrieve the new ID value.
return autoId;
}
/// <summary>Renders CONTAINS predicate as compatible LIKE predicate.</summary>
/// <param name="contains">Contains predicate.</param>
/// <param name="dbms">Target DBMS.</param>
/// <param name="output">StringBuilder to which SQL is appended.</param>
/// <param name="parameters">SQL parameter collection to which the object's and its children's
/// parameters are added. After the rendering is done the collection contains all parameters with unique names.</param>
public void Render(ContainsPredicate contains, DbmsType dbms, StringBuilder output, DbParameterCollection parameters)
{
output.Append("(");
contains.PredicateItems[0].Render(dbms, output, parameters);
if (contains.Negate)
output.Append(" NOT LIKE ");
else
output.Append(" LIKE ");
IDbColumn field = (IDbColumn)contains.PredicateItems[0].Item;
string pattern = (string)(contains.PredicateItems[1].Item as DbParameter).Value;
pattern = "%" + pattern + "%";
PredicateItem parametrizedPattern = new PredicateItem(pattern, DbType.String, field.MaxLengthIfText, field.GetPrefixedAlias());
parametrizedPattern.Render(dbms, output, parameters);
output.Append(")");
}
/// <summary>
/// Renders aggregate function as SQL element.
/// </summary>
/// <param name="aggregateFunction">Aggregate function.</param>
/// <param name="dbms">Target DBMS.</param>
/// <param name="output">StringBuilder to which SQL is appended.</param>
/// <param name="allParameters">SQL parameter collection to which the object's and its children's
/// parameters are added. After the rendering is done the collection contains all parameters with unique names.</param>
public void Render(AggregateFunction aggregateFunction, DbmsType dbms, StringBuilder output, DbParameterCollection allParameters)
{
output.Append(aggregateFunction.Name);
output.Append("(");
if (aggregateFunction.Distinct)
output.Append("DISTINCT ");
if (aggregateFunction.FunctionArguments.Count > 0)
{
for (int paramIdx = 0; paramIdx < aggregateFunction.FunctionArguments.Count - 1; paramIdx++)
{
aggregateFunction.FunctionArguments[paramIdx].Render(dbms, output, allParameters);
output.Append(", ");
}
aggregateFunction.FunctionArguments[aggregateFunction.FunctionArguments.Count - 1].Render(dbms, output, allParameters);
}
output.Append(")");
}
/// <summary>
/// Renders predicate as SQL statement.
/// </summary>
/// <param name="dbms">Target DBMS.</param>
/// <param name="output">StringBuilder to which SQL is appended.</param>
/// <param name="parameters">SQL parameter collection to which the object's and its children's
/// parameters are added. After the rendering is done the collection contains all parameters with unique names.</param>
public override void Render(DbmsType dbms, StringBuilder output, DbParameterCollection parameters)
{
output.Append("(");
PredicateItem lhsItem = PredicateItems[0];
lhsItem.Render(dbms, output, parameters);
if (Negate)
output.Append(" NOT IN (");
else
output.Append(" IN (");
for (int inItemIdx = 1; inItemIdx < PredicateItems.Length - 1; inItemIdx++)
{
PredicateItems[inItemIdx].Render(dbms, output, parameters);
output.Append(", ");
}
PredicateItem lastInItem = PredicateItems[PredicateItems.Length - 1];
lastInItem.Render(dbms, output, parameters);
output.Append("))");
}
private static void RenderOrderByInSubQueryAndRownumInOuterQuery(SelectStatement select, DbmsType dbms, StringBuilder output, DbParameterCollection parameters)
{
// ROWNUM must be placed in outer query when ORDER BY is used.
// SELECT *
// FROM (SELECT ...) subquery
// WHERE ROWNUM <= this.top
output.Append("SELECT * FROM (");
AppendSelectAndDistinctKeyWords(select, output);
AppendSelectList(select, dbms, output, parameters);
AppendFromClause(select, dbms, output);
AppenWhereWoRownum(select, dbms, output, parameters);
AppendGroupByClause(select, dbms, output, parameters);
AppendHavingClause(select, dbms, output, parameters);
AppendOrderByClause(select, dbms, output, parameters);
// ROWNUM.
output.Append(") subquery WHERE ROWNUM <= ");
output.Append(select.Top.ToString(CultureInfo.InvariantCulture));
}
/// <summary>
/// Renders predicate as SQL statement.
/// </summary>
/// <param name="dbms">Target DBMS.</param>
/// <param name="output">StringBuilder to which SQL is appended.</param>
/// <param name="parameters">SQL parameter collection to which the object's and its children's
/// parameters are added. After the rendering is done the collection contains all parameters with unique names.</param>
public override void Render(DbmsType dbms, StringBuilder output, DbParameterCollection parameters)
{
output.Append("(");
if (Negate)
output.Append("NOT (");
PredicateItems[0].Render(dbms, output, parameters);
output.Append(" ");
if (this.comparisonOperator == "!=")
output.Append(ComparisonOperator.NotEqual);
else
output.Append(this.comparisonOperator);
output.Append(" ");
PredicateItems[1].Render(dbms, output, parameters);
if (Negate)
output.Append(")");
output.Append(")");
}
public DbParameter RenderInsert(InsertStatement insert, DbParameter nextSequence, DbmsType dbms, StringBuilder output, DbParameterCollection parameters)
{
// Auto ID: sequence if it is defined, or NULL and hope that a trigger will take care of auto ID field.
IDbColumn autoIdField = GetAutoIdField(insert.Table);
bool hasAutoIdField = (autoIdField != null);
bool autoIdIsAlreadyInInsertList = IsAutoIdFieldInColumnValueList(insert);
bool mustAppendAutoIdField = (hasAutoIdField && !autoIdIsAlreadyInInsertList);
AppendInsertIntoTableName(insert, dbms, output);
AppendColumnListInBrackets(insert, dbms, output, autoIdField, mustAppendAutoIdField);
RenderValueListAndNextSequenceExpression(insert, dbms, output, parameters, autoIdField, mustAppendAutoIdField);
if (autoIdField != null && nextSequence != null)
{
// RETURNING id
output.Append(" RETURNING ");
autoIdField.RenderColumnName(dbms, output);
output.Append(" INTO ");
nextSequence.Render(dbms, output, parameters);
}
return nextSequence;
}
public void Render(ContainsPredicate contains, DbmsType dbms, StringBuilder output, DbParameterCollection parameters)
{
output.Append("(");
if (contains.Negate)
output.Append("NOT CONTAINS(");
else
output.Append("CONTAINS(");
contains.PredicateItems[0].Render(dbms, output, parameters);
output.Append(", ");
IDbColumn field = (IDbColumn)contains.PredicateItems[0].Item;
string term = (string)(contains.PredicateItems[1].Item as DbParameter).Value;
if (contains.TermType == ContainsTermType.PrefixTerm)
term = '"' + term + '"';
else
term = '"' + term + "*\"";
PredicateItem parametrizedTerm = new PredicateItem(term, DbType.String, field.MaxLengthIfText, field.GetPrefixedAlias());
parametrizedTerm.Render(dbms, output, parameters);
output.Append(")");
output.Append(")");
}
public static ShardBatch <IList <TRecord> > Add <TRecord>(this ShardBatch <IList <TRecord> > batch, Query query, DbParameterCollection parameters, string dataColumnName)
{
batch.Add(new ListStep <TRecord>(query, parameters, dataColumnName));
return(batch);
}
/// <summary>
///
/// </summary>
/// <param name="sqlstr"></param>
/// <param name="dbPara"></param>
/// <returns></returns>
public override int ExecuteByParameter(string sqlstr, DbParameterCollection dbPara)
{
int ret1, ret2;;
ret1 = 0;
ret2 = 0;
OracleConnection mainConn = new OracleConnection(mainConnectString);
OracleCommand mainCmd = new OracleCommand();
PrepareCommand(mainCmd, mainConn, CommandType.Text, sqlstr);
try
{
for (int i = 0; i < dbPara.Count; i++)
{
mainCmd.Parameters.Add(dbPara[i].ParameterName, dbPara[i].DbType);
mainCmd.Parameters[dbPara[i].ParameterName].Value = dbPara[i].Value;
}
ret1 = mainCmd.ExecuteNonQuery();
}
catch (Exception e)
{
LogError("主服务器", "ExecuteSqlByParas()<" + sqlstr + ">", e.Message);
}
finally
{
mainCmd.Parameters.Clear();
if (mainConn.State == ConnectionState.Open)
{
mainConn.Close();
}
mainConn.Dispose();
mainCmd.Dispose();
}
if (isDoubleDatabase)
{
OracleConnection slaveConn = new OracleConnection(slaveConnectString);
OracleCommand slaveCmd = new OracleCommand();
PrepareCommand(slaveCmd, slaveConn, CommandType.Text, sqlstr);
try
{
for (int i = 0; i < dbPara.Count; i++)
{
slaveCmd.Parameters.Add(dbPara[i].ParameterName, dbPara[i].DbType);
slaveCmd.Parameters[dbPara[i].ParameterName].Value = dbPara[i].Value;
}
ret2 = slaveCmd.ExecuteNonQuery();
}
catch (Exception e)
{
LogError("备服务器", "ExecuteSqlByParas()<" + sqlstr + ">", e.Message);
}
finally
{
slaveCmd.Parameters.Clear();
if (slaveConn.State == ConnectionState.Open)
{
slaveConn.Close();
}
slaveConn.Dispose();
slaveCmd.Dispose();
}
}
return(ret1 + ret2);
}
public RecordStep(Query query, DbParameterCollection parameters, string recordIdColumnName)
{
_query = query;
_parameters = parameters;
_recordIdColumnName = recordIdColumnName;
}
public static DatabaseBatch <List <TRecord> > Add <TRecord>(this DatabaseBatch <List <TRecord> > batch, Query query, DbParameterCollection parameters, string dataColumnName)
{
((IList)batch).Add(new ListStep <TRecord>(query, parameters, dataColumnName));
return(batch);
}
public static ShardBatch <IList <ShardKey <TRecord> > > Add <TRecord>(this ShardBatch <IList <ShardKey <TRecord> > > batch, Query query, DbParameterCollection parameters, char dataOrigin, string shardIdColumnName, string recordIdColumnName) where TRecord : IComparable
{
batch.Add(new KeyListStep <TRecord>(query, parameters, dataOrigin, shardIdColumnName, recordIdColumnName));
return(batch);
}
public static ShardBatch <TModel> Add <TArg, TModel>(this ShardBatch <TModel> batch, Query query, DbParameterCollection parameters, QueryResultModelHandler <TArg, TModel> resultHandler, TArg optionalArgument) where TModel : class, new()
{
batch.Add(new ModelStep <TArg, TModel>(query, parameters, resultHandler, optionalArgument));
return(batch);
}
/// <summary>
/// Método privado para processamento do método 'user.resetpassword'
/// </summary>
/// <param name="sqlConnection">Conexão com o banco de dados MS-SQL</param>
/// <param name="parameters">Dicionário (String, Object) contendo todos os parâmetros necessários</param>
private Dictionary <String, Object> changepermissions(IAMDatabase database, Dictionary <String, Object> parameters)
{
Dictionary <String, Object> result = new Dictionary <String, Object>();
if (!parameters.ContainsKey("roleid"))
{
Error(ErrorType.InvalidRequest, "Parameter roleid is not defined.", "", null);
return(null);
}
String role = parameters["roleid"].ToString();
if (String.IsNullOrWhiteSpace(role))
{
Error(ErrorType.InvalidRequest, "Parameter roleid is not defined.", "", null);
return(null);
}
Int64 roleid = 0;
try
{
roleid = Int64.Parse(role);
}
catch
{
Error(ErrorType.InvalidRequest, "Parameter roleid is not a long integer.", "", null);
return(null);
}
if (!parameters.ContainsKey("permissions"))
{
Error(ErrorType.InvalidRequest, "Parameter permissions is not defined.", "", null);
return(null);
}
if (!(parameters["permissions"] is ArrayList))
{
Error(ErrorType.InvalidRequest, "Parameter permissions is invalid.", "", null);
return(null);
}
DbParameterCollection par = new DbParameterCollection();
par.Add("@enterprise_id", typeof(Int64)).Value = this._enterpriseId;
par.Add("@role_id", typeof(Int64)).Value = roleid;
DataTable dtSysRole = database.ExecuteDataTable("select r.*, entity_qty = (select COUNT(distinct e.id) from sys_entity_role er inner join entity e with(nolock) on e.id = er.entity_id where er.role_id = r.id) from sys_role r WHERE r.enterprise_id = @enterprise_id and r.id = @role_id order by r.name", CommandType.Text, par, null);
if (dtSysRole == null)
{
Error(ErrorType.InternalError, "", "", null);
return(null);
}
if (dtSysRole.Rows.Count == 0)
{
Error(ErrorType.InvalidRequest, "System role not found.", "", null);
return(null);
}
SqlTransaction trans = (SqlTransaction)database.BeginTransaction();
try
{
List <String> log = new List <String>();
List <String> perList = new List <String>();
List <Object> lst = new List <Object>();
lst.AddRange(((ArrayList)parameters["permissions"]).ToArray());
foreach (String p in lst)
{
try
{
Int64 permissionid = Int64.Parse(p);
//Verifica se a permissão existe
DataTable dtP = database.ExecuteDataTable("select p.*, m.name module_name, sm.name submodule_name, sm.[api_module] + '.' + p.[key] api_key from sys_permission p inner join sys_sub_module sm on sm.id = p.submodule_id inner join sys_module m on m.id = sm.module_id WHERE p.id = " + p, CommandType.Text, null, trans);
if ((dtP == null) || (dtP.Rows.Count == 0))
{
Error(ErrorType.InvalidRequest, "Permission '" + p + "' not found.", "", null);
return(null);
}
database.ExecuteNonQuery("insert into sys_role_permission (role_id, permission_id) select @role_id, " + dtP.Rows[0]["id"] + " WHERE not exists(select 1 from sys_role_permission where role_id = @role_id and permission_id = " + dtP.Rows[0]["id"] + ")", CommandType.Text, par, trans);
perList.Add(dtP.Rows[0]["id"].ToString());
log.Add("Permission linked: " + dtP.Rows[0]["module_name"] + " => " + dtP.Rows[0]["api_key"]);
}
catch
{
Error(ErrorType.InvalidRequest, "Permission '" + p + "' is not a long integer.", "", null);
return(null);
}
}
//Exclui todas as outras não listadas
database.ExecuteNonQuery("delete from sys_role_permission WHERE role_id = @role_id and permission_id not in (" + String.Join(",", perList) + ")", CommandType.Text, par, trans);
database.AddUserLog(LogKey.SystemRolePermission_Changed, null, "API", UserLogLevel.Info, 0, this._enterpriseId, 0, 0, 0, 0, 0, "System role permissions changed", String.Join("\r\n", log), trans);
trans.Commit();
trans = null;
}
finally
{
if (trans != null)
{
trans.Rollback();
}
}
Dictionary <String, Object> parR = new Dictionary <string, object>();
parR.Add("roleid", roleid);
parR.Add("permissions", true);
return(get(database, parR));
}
public List <FeeStructEn> GetFeeSDAmountList(FeeStructEn argEn)
{
List <FeeStructEn> loEnList = new List <FeeStructEn>();
// string sqlCmd = @"
// select fsd.safs_code, fsd.safd_type, fsd.safd_priority, fsd.safs_taxmode, ft.*, local.safa_amount as local_amount, local.safa_gstamount as local_gstamount,
// local.sasc_code as local_cateogry, nonlocal.safa_amount as nonlocal_amount, nonlocal.safa_gstamount as nonlocal_gstamount, nonlocal.sasc_code as nonlocal_cateogry,
// fsd.safd_feefor, fsd.safd_sem
// from sas_feestrdetails fsd
// inner join sas_feetypes ft on ft.saft_code = fsd.saft_code
// left join (select * from sas_feestramount fsa where fsa.sasc_code in ('Local', 'W') )as local on local.safs_code = fsd.safs_code and local.saft_code = fsd.saft_code and local.safd_type = fsd.safd_type
// and local.safd_feefor = fsd.safd_feefor and local.safd_sem = fsd.safd_sem
// left join (select * from sas_feestramount fsa where fsa.sasc_code in ('International', 'BW') )as nonlocal on nonlocal.safs_code = fsd.safs_code and nonlocal.saft_code = fsd.saft_code and nonlocal.safd_type = fsd.safd_type
// and nonlocal.safd_feefor = fsd.safd_feefor and nonlocal.safd_sem = fsd.safd_sem
// where fsd.safs_code = @SAFS_Code";
string sqlCmd = @"
select fsd.safs_code, fsd.safd_type, fsd.safd_priority, fsd.safs_taxmode, ft.*, local.safa_amount as local_amount, local.safa_gstamount as local_gstamount,
local.sasc_code as local_cateogry, nonlocal.safa_amount as nonlocal_amount, nonlocal.safa_gstamount as nonlocal_gstamount, nonlocal.sasc_code as nonlocal_cateogry,
fsd.safd_feefor, fsd.safd_sem, case when fsd.safd_type = 'T' then fs.safd_feebaseon else '-1' end as safd_feebaseon
from SAS_FeeStruct fs inner join sas_feestrdetails fsd on fs.safs_code = fsd.safs_code
inner join sas_feetypes ft on ft.saft_code = fsd.saft_code
left join (select * from sas_feestramount fsa where fsa.sasc_code in ('Local', 'W') )as local on local.safs_code = fsd.safs_code and local.saft_code = fsd.saft_code and local.safd_type = fsd.safd_type
and local.safd_feefor = fsd.safd_feefor and local.safd_sem = fsd.safd_sem
left join (select * from sas_feestramount fsa where fsa.sasc_code in ('International', 'BW') )as nonlocal on nonlocal.safs_code = fsd.safs_code and nonlocal.saft_code = fsd.saft_code and nonlocal.safd_type = fsd.safd_type
and nonlocal.safd_feefor = fsd.safd_feefor and nonlocal.safd_sem = fsd.safd_sem
where fsd.safs_code = @SAFS_Code order by fsd.safd_type";
try
{
if (!FormHelp.IsBlank(sqlCmd))
{
DbCommand cmd = _DatabaseFactory.GetDbCommand(Helper.GetDataBaseType, sqlCmd, DataBaseConnectionString);
_DatabaseFactory.AddInParameter(ref cmd, "@SAFS_Code", DbType.String, argEn.FeeStructureCode);
_DbParameterCollection = cmd.Parameters;
using (IDataReader loReader = _DatabaseFactory.GetIDataReader(Helper.GetDataBaseType, cmd,
DataBaseConnectionString, sqlCmd, _DbParameterCollection).CreateDataReader())
{
while (loReader.Read())
{
FeeStructEn loItem = new FeeStructEn();
loItem.FeeStructureCode = GetValue <string>(loReader, "safs_code");
loItem.FTCode = GetValue <string>(loReader, "saft_code");
loItem.Description = GetValue <string>(loReader, "saft_desc");
loItem.LocalAmount = GetValue <double>(loReader, "local_amount");
loItem.LocalGSTAmount = GetValue <double>(loReader, "local_gstamount");
loItem.NonLocalAmount = GetValue <double>(loReader, "nonlocal_amount");
loItem.NonLocalGSTAmount = GetValue <double>(loReader, "nonlocal_gstamount");
loItem.TaxId = GetValue <int>(loReader, "safs_taxmode");
loItem.NonLocalTempAmount = loItem.NonLocalAmount - loItem.NonLocalGSTAmount;
loItem.LocalTempAmount = loItem.LocalAmount - loItem.LocalGSTAmount;
loItem.LocalCategory = GetValue <string>(loReader, "local_cateogry");
loItem.NonLocalCategory = GetValue <string>(loReader, "nonlocal_cateogry");
loItem.FeeType = GetValue <string>(loReader, "safd_type");
loItem.Priority = GetValue <int>(loReader, "safd_priority");
loItem.FeeFor = GetValue <string>(loReader, "safd_feefor");
loItem.FeeDetailSem = GetValue <int>(loReader, "safd_sem");
loItem.FeeBaseOn = GetValue <string>(loReader, "safd_feebaseon");
loEnList.Add(loItem);
}
loReader.Close();
}
}
}
catch (Exception ex)
{
throw ex;
}
return(loEnList);
}
/// <summary>
/// Método privado para processamento do método 'user.resetpassword'
/// </summary>
/// <param name="sqlConnection">Conexão com o banco de dados MS-SQL</param>
/// <param name="parameters">Dicionário (String, Object) contendo todos os parâmetros necessários</param>
private Boolean adduser(IAMDatabase database, Dictionary <String, Object> parameters)
{
if (!parameters.ContainsKey("roleid"))
{
Error(ErrorType.InvalidRequest, "Parameter roleid is not defined.", "", null);
return(false);
}
String role = parameters["roleid"].ToString();
if (String.IsNullOrWhiteSpace(role))
{
Error(ErrorType.InvalidRequest, "Parameter roleid is not defined.", "", null);
return(false);
}
String userid = parameters["userid"].ToString();
if (String.IsNullOrWhiteSpace(userid))
{
Error(ErrorType.InvalidRequest, "Parameter userid is not defined.", "", null);
return(false);
}
Int64 roleid = 0;
try
{
roleid = Int64.Parse(role);
}
catch
{
Error(ErrorType.InvalidRequest, "Parameter roleid is not a long integer.", "", null);
return(false);
}
List <Int64> users = new List <Int64>();
String[] t = userid.Split(",".ToCharArray());
foreach (String u in t)
{
try
{
Int64 tmp = Int64.Parse(u);
users.Add(tmp);
}
catch
{
Error(ErrorType.InvalidRequest, "Parameter users is not a long integer.", "", null);
return(false);
}
}
DbParameterCollection par = new DbParameterCollection();
par.Add("@enterprise_id", typeof(Int64)).Value = this._enterpriseId;
par.Add("@role_id", typeof(Int64)).Value = roleid;
DataTable dtSysRole = database.ExecuteDataTable("select *, entity_qty = (select COUNT(distinct e.id) from sys_entity_role er inner join entity e with(nolock) on e.id = er.entity_id where er.role_id = r.id), last_admin = case when r.ea = 1 and not exists (select 1 from sys_role r1 where r1.enterprise_id = r.enterprise_id and r1.ea = 1 and r1.id <> r.id) then cast(1 as bit) else cast(0 as bit) end from sys_role r WHERE r.enterprise_id = @enterprise_id and r.id = @role_id and r.sa = 0", CommandType.Text, par, null);
if (dtSysRole == null)
{
Error(ErrorType.InternalError, "", "", null);
return(false);
}
if (dtSysRole.Rows.Count == 0)
{
Error(ErrorType.InvalidRequest, "System role not found.", "", null);
return(false);
}
foreach (Int64 u in users)
{
DbParameterCollection par2 = new DbParameterCollection();
par2.Add("@enterprise_id", typeof(Int64)).Value = this._enterpriseId;
par2.Add("@role_id", typeof(Int64)).Value = roleid;
par2.Add("@entity_id", typeof(Int64)).Value = u;
DataTable dtRet = database.ExecuteDataTable("insert into sys_entity_role (entity_id, role_id) select @entity_id, @role_id WHERE not exists (select 1 from sys_entity_role where entity_id = @entity_id and role_id = @role_id)", CommandType.Text, par2);
database.AddUserLog(LogKey.User_SystemRoleBind, null, "API", UserLogLevel.Info, 0, this._enterpriseId, 0, 0, 0, u, 0, "Entity bind to system role " + dtSysRole.Rows[0]["name"].ToString(), ((Boolean)dtSysRole.Rows[0]["ea"] ? "Enterprise admin" : ""));
}
return(true);
}
/// <summary>
/// Método privado para processamento do método 'user.resetpassword'
/// </summary>
/// <param name="sqlConnection">Conexão com o banco de dados MS-SQL</param>
/// <param name="parameters">Dicionário (String, Object) contendo todos os parâmetros necessários</param>
private Dictionary <String, Object> change(IAMDatabase database, Dictionary <String, Object> parameters)
{
Dictionary <String, Object> result = new Dictionary <String, Object>();
if (!parameters.ContainsKey("roleid"))
{
Error(ErrorType.InvalidRequest, "Parameter roleid is not defined.", "", null);
return(null);
}
String role = parameters["roleid"].ToString();
if (String.IsNullOrWhiteSpace(role))
{
Error(ErrorType.InvalidRequest, "Parameter roleid is not defined.", "", null);
return(null);
}
Int64 roleid = 0;
try
{
roleid = Int64.Parse(role);
}
catch
{
Error(ErrorType.InvalidRequest, "Parameter roleid is not a long integer.", "", null);
return(null);
}
DbParameterCollection par = new DbParameterCollection();
par.Add("@enterprise_id", typeof(Int64)).Value = this._enterpriseId;
par.Add("@role_id", typeof(Int64)).Value = roleid;
DataTable dtSysRole = database.ExecuteDataTable("select r.*, entity_qty = (select COUNT(distinct e.id) from sys_entity_role er inner join entity e with(nolock) on e.id = er.entity_id where er.role_id = r.id) from sys_role r WHERE r.enterprise_id = @enterprise_id and r.id = @role_id order by r.name", CommandType.Text, par, null);
if (dtSysRole == null)
{
Error(ErrorType.InternalError, "", "", null);
return(null);
}
if (dtSysRole.Rows.Count == 0)
{
Error(ErrorType.InvalidRequest, "System role not found.", "", null);
return(null);
}
List <String> log = new List <String>();
String updateSQL = "";
Boolean update = false;
if (parameters["name"] != null)
{
String name = parameters["name"].ToString();
if (!String.IsNullOrWhiteSpace(name))
{
par.Add("@name", typeof(String)).Value = name;
if (updateSQL != "")
{
updateSQL += ", ";
}
updateSQL += " name = @name";
update = true;
log.Add("Name changed from '" + dtSysRole.Rows[0]["name"] + "' to '" + name + "'");
}
}
if ((parameters["enterprise_admin"] != null) && (parameters["enterprise_admin"] is Boolean))
{
par.Add("@enterprise_admin", typeof(Boolean)).Value = (Boolean)parameters["enterprise_admin"];
if (updateSQL != "")
{
updateSQL += ", ";
}
updateSQL += " ea = @enterprise_admin";
update = true;
log.Add("Enterprise admin changed from '" + (Boolean)dtSysRole.Rows[0]["ea"] + "' to '" + (Boolean)parameters["enterprise_admin"] + "'");
}
if (update)
{
updateSQL = "update sys_role set " + updateSQL + " where id = @role_id";
database.ExecuteNonQuery(updateSQL, CommandType.Text, par);
database.AddUserLog(LogKey.SystemRole_Changed, null, "API", UserLogLevel.Info, 0, this._enterpriseId, 0, 0, 0, 0, 0, "System role changed", String.Join("\r\n", log));
}
return(get(database, parameters));
}
/// <summary>
/// Método privado para processamento do método 'user.resetpassword'
/// </summary>
/// <param name="sqlConnection">Conexão com o banco de dados MS-SQL</param>
/// <param name="parameters">Dicionário (String, Object) contendo todos os parâmetros necessários</param>
private Boolean deleteuser(IAMDatabase database, Dictionary <String, Object> parameters)
{
if (!parameters.ContainsKey("roleid"))
{
Error(ErrorType.InvalidRequest, "Parameter roleid is not defined.", "", null);
return(false);
}
String role = parameters["roleid"].ToString();
if (String.IsNullOrWhiteSpace(role))
{
Error(ErrorType.InvalidRequest, "Parameter roleid is not defined.", "", null);
return(false);
}
Int64 roleid = 0;
try
{
roleid = Int64.Parse(role);
}
catch
{
Error(ErrorType.InvalidRequest, "Parameter roleid is not a long integer.", "", null);
return(false);
}
String user = parameters["userid"].ToString();
if (String.IsNullOrWhiteSpace(user))
{
Error(ErrorType.InvalidRequest, "Parameter userid is not defined.", "", null);
return(false);
}
Int64 userid = 0;
try
{
userid = Int64.Parse(user);
}
catch
{
Error(ErrorType.InvalidRequest, "Parameter userid is not a long integer.", "", null);
return(false);
}
DbParameterCollection par = new DbParameterCollection();
par.Add("@enterprise_id", typeof(Int64)).Value = this._enterpriseId;
par.Add("@role_id", typeof(Int64)).Value = roleid;
par.Add("@entity_id", typeof(Int64)).Value = userid;
DataTable dtSysRole = database.ExecuteDataTable("select *, entity_qty = (select COUNT(distinct e.id) from sys_entity_role er inner join entity e with(nolock) on e.id = er.entity_id where er.role_id = r.id), last_admin = case when r.ea = 1 and not exists (select 1 from sys_role r1 where r1.enterprise_id = r.enterprise_id and r1.ea = 1 and r1.id <> r.id) then cast(1 as bit) else cast(0 as bit) end from sys_role r WHERE r.enterprise_id = @enterprise_id and r.id = @role_id and r.sa = 0", CommandType.Text, par, null);
if (dtSysRole == null)
{
Error(ErrorType.InternalError, "", "", null);
return(false);
}
if (dtSysRole.Rows.Count == 0)
{
Error(ErrorType.InvalidRequest, "System role not found.", "", null);
return(false);
}
DataTable dtSysRoleUsers = database.ExecuteDataTable("select e.id entity_id, r.* from entity e with(nolock) inner join sys_entity_role er on e.id = er.entity_id inner join sys_role r on r.id = er.role_id WHERE r.enterprise_id = @enterprise_id and r.id = @role_id", CommandType.Text, par, null);
if (dtSysRoleUsers == null)
{
Error(ErrorType.InternalError, "", "", null);
return(false);
}
if (dtSysRoleUsers.Rows.Count > 0)
{
if ((Boolean)dtSysRole.Rows[0]["last_admin"] && ((Int32)dtSysRole.Rows[0]["entity_qty"] == 1))
{
Error(ErrorType.SystemError, "Entity " + dtSysRoleUsers.Rows[0]["name"] + " is a last user on a system role and this role is the last role with enterprise admin permission, can not be delete this user.", "", null);
return(false);
}
database.ExecuteNonQuery("delete from sys_entity_role where role_id = @role_id and entity_id = @entity_id", CommandType.Text, par);
foreach (DataRow dr in dtSysRoleUsers.Rows)
{
if (dr["entity_id"] != DBNull.Value)
{
database.AddUserLog(LogKey.User_SystemRoleUnbind, null, "API", UserLogLevel.Info, 0, this._enterpriseId, 0, 0, 0, (Int64)dr["entity_id"], 0, "Entity unbind to system role " + dtSysRole.Rows[0]["name"], ((Boolean)dtSysRole.Rows[0]["ea"] ? "Enterprise admin" : ""));
}
}
}
return(true);
}
/// <summary>
/// Método privado para processamento do método 'user.resetpassword'
/// </summary>
/// <param name="sqlConnection">Conexão com o banco de dados MS-SQL</param>
/// <param name="parameters">Dicionário (String, Object) contendo todos os parâmetros necessários</param>
private Boolean delete(IAMDatabase database, Dictionary <String, Object> parameters)
{
if (!parameters.ContainsKey("roleid"))
{
Error(ErrorType.InvalidRequest, "Parameter roleid is not defined.", "", null);
return(false);
}
String role = parameters["roleid"].ToString();
if (String.IsNullOrWhiteSpace(role))
{
Error(ErrorType.InvalidRequest, "Parameter roleid is not defined.", "", null);
return(false);
}
Int64 roleid = 0;
try
{
roleid = Int64.Parse(role);
}
catch
{
Error(ErrorType.InvalidRequest, "Parameter roleid is not a long integer.", "", null);
return(false);
}
DbParameterCollection par = new DbParameterCollection();
par.Add("@enterprise_id", typeof(Int64)).Value = this._enterpriseId;
par.Add("@role_id", typeof(Int64)).Value = roleid;
DataTable dtSysRole = database.ExecuteDataTable("select *, entity_qty = (select COUNT(distinct e.id) from sys_entity_role er inner join entity e with(nolock) on e.id = er.entity_id where er.role_id = r.id), last_admin = case when r.ea = 1 and not exists (select 1 from sys_role r1 where r1.enterprise_id = r.enterprise_id and r1.ea = 1 and r1.id <> r.id) then cast(1 as bit) else cast(0 as bit) end, last_sa = case when r.sa = 1 and not exists (select 1 from sys_role r1 where r1.enterprise_id = r.enterprise_id and r1.sa = 1 and r1.id <> r.id) then cast(1 as bit) else cast(0 as bit) end from sys_role r WHERE r.enterprise_id = @enterprise_id and r.id = @role_id", CommandType.Text, par, null);
if (dtSysRole == null)
{
Error(ErrorType.InternalError, "", "", null);
return(false);
}
if (dtSysRole.Rows.Count == 0)
{
Error(ErrorType.InvalidRequest, "System role not found.", "", null);
return(false);
}
//Verifica se está sendo usado
if ((Int32)dtSysRole.Rows[0]["entity_qty"] > 0)
{
Error(ErrorType.SystemError, "System role is being used and can not be deleted.", "", null);
return(false);
}
if ((Boolean)dtSysRole.Rows[0]["last_admin"])
{
Error(ErrorType.SystemError, "System role is the last role with enterprise admin permission, can not be deleted.", "", null);
return(false);
}
if ((Boolean)dtSysRole.Rows[0]["last_sa"])
{
Error(ErrorType.SystemError, "System role is the last role with system admin permission, can not be deleted.", "", null);
return(false);
}
database.ExecuteNonQuery("delete from sys_role where id = @role_id", CommandType.Text, par);
database.AddUserLog(LogKey.SystemRole_Deleted, null, "API", UserLogLevel.Info, 0, this._enterpriseId, 0, 0, 0, 0, 0, "System role " + dtSysRole.Rows[0]["name"] + " deleted", "");
return(true);
}
/// <summary>
/// Método privado para processamento do método 'user.resetpassword'
/// </summary>
/// <param name="sqlConnection">Conexão com o banco de dados MS-SQL</param>
/// <param name="parameters">Dicionário (String, Object) contendo todos os parâmetros necessários</param>
private Dictionary <String, Object> get(IAMDatabase database, Dictionary <String, Object> parameters)
{
Dictionary <String, Object> result = new Dictionary <String, Object>();
if (!parameters.ContainsKey("roleid"))
{
Error(ErrorType.InvalidRequest, "Parameter roleid is not defined.", "", null);
return(null);
}
String role = parameters["roleid"].ToString();
if (String.IsNullOrWhiteSpace(role))
{
Error(ErrorType.InvalidRequest, "Parameter roleid is not defined.", "", null);
return(null);
}
Int64 roleid = 0;
try
{
roleid = Int64.Parse(role);
}
catch
{
Error(ErrorType.InvalidRequest, "Parameter roleid is not a long integer.", "", null);
return(null);
}
DbParameterCollection par = new DbParameterCollection();
par.Add("@enterprise_id", typeof(Int64)).Value = this._enterpriseId;
par.Add("@role_id", typeof(Int64)).Value = roleid;
DataTable dtUsers = database.ExecuteDataTable("select r.*, entity_qty = (select COUNT(distinct e.id) from sys_entity_role er inner join entity e with(nolock) on e.id = er.entity_id where er.role_id = r.id) from sys_role r WHERE r.enterprise_id = @enterprise_id and r.id = @role_id order by r.name", CommandType.Text, par, null);
if (dtUsers == null)
{
Error(ErrorType.InternalError, "", "", null);
return(null);
}
if (dtUsers.Rows.Count == 0)
{
Error(ErrorType.InvalidRequest, "System role not found.", "", null);
return(null);
}
DataRow dr1 = dtUsers.Rows[0];
Dictionary <string, object> newItem = new Dictionary <string, object>();
newItem.Add("enterprise_id", dr1["enterprise_id"]);
newItem.Add("role_id", dr1["id"]);
newItem.Add("parent_id", dr1["parent_id"]);
newItem.Add("name", dr1["name"]);
newItem.Add("entity_qty", dr1["entity_qty"]);
newItem.Add("enterprise_admin", dr1["ea"]);
newItem.Add("create_date", (dr1["create_date"] != DBNull.Value ? (Int32)((((DateTime)dr1["create_date"]) - new DateTime(1970, 1, 1)).TotalSeconds) : 0));
if (parameters.ContainsKey("permissions") && (parameters["permissions"] is Boolean) && (((Boolean)parameters["permissions"])))
{
Dictionary <String, Object> per = new Dictionary <string, object>();
per.Add("roleid", dr1["id"]);
newItem.Add("permissions", permissions(database, per));
}
result.Add("info", newItem);
return(result);
}
private static DbParameterReadOnlyCollection GetReadOnlyCollection(this DbParameterCollection collection) => new DbParameterReadOnlyCollection(collection);
/// <summary>
/// Método privado para processamento do método 'user.resetpassword'
/// </summary>
/// <param name="sqlConnection">Conexão com o banco de dados MS-SQL</param>
/// <param name="parameters">Dicionário (String, Object) contendo todos os parâmetros necessários</param>
private List <Object> list(IAMDatabase database, Dictionary <String, Object> parameters)
{
List <Object> result = new List <Object>();
String text = "";
if (parameters.ContainsKey("text"))
{
text = (String)parameters["text"];
}
if (String.IsNullOrWhiteSpace(text))
{
text = "";
}
DbParameterCollection par = new DbParameterCollection();
par.Add("@enterprise_id", typeof(Int64)).Value = this._enterpriseId;
par.Add("@text", typeof(String)).Value = text;
Int32 page = 1;
Int32 pageSize = 10;
if (parameters.ContainsKey("page"))
{
Int32.TryParse(parameters["page"].ToString(), out page);
}
if (parameters.ContainsKey("page_size"))
{
Int32.TryParse(parameters["page_size"].ToString(), out pageSize);
}
if (pageSize < 1)
{
pageSize = 1;
}
if (page < 1)
{
page = 1;
}
Int32 rStart = ((page - 1) * pageSize) + 1;
Int32 rEnd = rStart + (pageSize - 1);
String sql = "";
sql += "WITH result_set AS (";
sql += " SELECT ";
sql += " ROW_NUMBER() OVER (ORDER BY r.name) AS [row_number], r.*, entity_qty = (select COUNT(distinct e.id) from sys_entity_role er inner join entity e with(nolock) on e.id = er.entity_id where er.role_id = r.id) ";
sql += " from sys_role r ";
sql += " where r.enterprise_id = @enterprise_id " + (String.IsNullOrWhiteSpace(text) ? "" : " and r.name like '%'+@text+'%'");
sql += ") SELECT";
sql += " *";
sql += " FROM";
sql += " result_set";
sql += " WHERE";
sql += " [row_number] BETWEEN " + rStart + " AND " + rEnd;
DataTable dtRoles = database.ExecuteDataTable(sql, CommandType.Text, par, null);
if ((dtRoles != null) && (dtRoles.Rows.Count > 0))
{
foreach (DataRow dr1 in dtRoles.Rows)
{
Dictionary <string, object> newItem = new Dictionary <string, object>();
newItem.Add("enterprise_id", dr1["enterprise_id"]);
newItem.Add("role_id", dr1["id"]);
newItem.Add("parent_id", dr1["parent_id"]);
newItem.Add("name", dr1["name"]);
newItem.Add("entity_qty", dr1["entity_qty"]);
newItem.Add("enterprise_admin", dr1["ea"]);
newItem.Add("create_date", (dr1["create_date"] != DBNull.Value ? (Int32)((((DateTime)dr1["create_date"]) - new DateTime(1970, 1, 1)).TotalSeconds) : 0));
if (parameters.ContainsKey("permissions") && (parameters["permissions"] is Boolean) && (((Boolean)parameters["permissions"])))
{
Dictionary <String, Object> per = new Dictionary <string, object>();
per.Add("roleid", dr1["id"]);
newItem.Add("permissions", permissions(database, per));
}
result.Add(newItem);
}
}
return(result);
}
/// <summary>
/// Method to Update FeeStrDetails
/// </summary>
/// <param name="argEn">FeeStrDetails Entity is an Input.</param>
/// <returns>Returns Boolean</returns>
public bool Update(FeeStrDetailsEn argEn)
{
bool lbRes = false;
int iOut = 0;
string sqlCmd = "Select count(*) as cnt From SAS_FeeStrDetails WHERE SAFS_Code = @SAFS_Code";
try
{
if (!FormHelp.IsBlank(sqlCmd))
{
DbCommand cmdSel = _DatabaseFactory.GetDbCommand(Helper.GetDataBaseType, sqlCmd, DataBaseConnectionString);
_DatabaseFactory.AddInParameter(ref cmdSel, "@SAFS_Code", DbType.String, argEn.FSCode);
_DbParameterCollection = cmdSel.Parameters;
using (IDataReader dr = _DatabaseFactory.GetIDataReader(Helper.GetDataBaseType, cmdSel,
DataBaseConnectionString, sqlCmd, _DbParameterCollection).CreateDataReader())
{
if (dr.Read())
{
iOut = clsGeneric.NullToInteger(dr["cnt"]);
}
if (iOut > 0)
{
throw new Exception("Record Already Exist");
}
}
if (iOut == 0)
{
sqlCmd = "UPDATE SAS_FeeStrDetails SET SAFS_Code = @SAFS_Code,SAFD_Type = @SAFD_Type, SAFT_Code = @SAFT_Code, SAFD_Priority = @SAFD_Priority, SAFD_FeeFor = @SAFD_FeeFor, SAFD_Sem = @SAFD_Sem,safs_taxmode=@safs_taxmode WHERE SAFS_Code = @SAFS_Code";
if (!FormHelp.IsBlank(sqlCmd))
{
DbCommand cmd = _DatabaseFactory.GetDbCommand(Helper.GetDataBaseType, sqlCmd, DataBaseConnectionString);
_DatabaseFactory.AddInParameter(ref cmd, "@SAFS_Code", DbType.String, argEn.FSCode);
_DatabaseFactory.AddInParameter(ref cmd, "@SAFD_Type", DbType.String, argEn.Type);
_DatabaseFactory.AddInParameter(ref cmd, "@SAFT_Code", DbType.String, argEn.FTCode);
_DatabaseFactory.AddInParameter(ref cmd, "@SAFD_Priority", DbType.Int32, argEn.Priority);
_DatabaseFactory.AddInParameter(ref cmd, "@SAFD_FeeFor", DbType.String, argEn.FeeFor);
_DatabaseFactory.AddInParameter(ref cmd, "@SAFD_Sem", DbType.Int32, argEn.Sem);
_DatabaseFactory.AddInParameter(ref cmd, "@safs_taxmode", DbType.Int16, argEn._TaxId);
_DbParameterCollection = cmd.Parameters;
int liRowAffected = _DatabaseFactory.ExecuteNonQuery(Helper.GetDataBaseType, cmd,
DataBaseConnectionString, sqlCmd, _DbParameterCollection);
if (liRowAffected > -1)
{
lbRes = true;
}
else
{
throw new Exception("Update Failed! No Row has been updated...");
}
}
}
}
}
catch (Exception ex)
{
throw ex;
}
return(lbRes);
}
/// <summary>
/// Método privado para processamento do método 'user.search'
/// </summary>
/// <param name="sqlConnection">Conexão com o banco de dados MS-SQL</param>
/// <param name="parameters">Dicionário (String, Object) contendo todos os parâmetros necessários</param>
private List <Dictionary <String, Object> > users(IAMDatabase database, Dictionary <String, Object> parameters)
{
List <Dictionary <String, Object> > result = new List <Dictionary <String, Object> >();
if (!parameters.ContainsKey("roleid"))
{
Error(ErrorType.InvalidRequest, "Parameter roleid is not defined.", "", null);
return(null);
}
String role = parameters["roleid"].ToString();
if (String.IsNullOrWhiteSpace(role))
{
Error(ErrorType.InvalidRequest, "Parameter roleid is not defined.", "", null);
return(null);
}
Int64 roleid = 0;
try
{
roleid = Int64.Parse(role);
}
catch
{
Error(ErrorType.InvalidRequest, "Parameter roleid is not a long integer.", "", null);
return(null);
}
DbParameterCollection par = new DbParameterCollection();
par.Add("@enterprise_id", typeof(Int64)).Value = this._enterpriseId;
par.Add("@role_id", typeof(Int64)).Value = roleid;
Boolean deleted = false;
if ((parameters.ContainsKey("deleted")) && (parameters["deleted"] is Boolean))
{
deleted = (Boolean)parameters["deleted"];
}
Int32 page = 1;
Int32 pageSize = 10;
if (parameters.ContainsKey("page"))
{
Int32.TryParse(parameters["page"].ToString(), out page);
}
if (parameters.ContainsKey("page_size"))
{
Int32.TryParse(parameters["page_size"].ToString(), out pageSize);
}
if (pageSize < 1)
{
pageSize = 1;
}
if (page < 1)
{
page = 1;
}
Int32 rStart = ((page - 1) * pageSize) + 1;
Int32 rEnd = rStart + (pageSize - 1);
String sql = "";
sql += "WITH result_set AS (";
sql += " SELECT";
sql += " ROW_NUMBER() OVER (ORDER BY e.full_name) AS [row_number], e.*";
sql += " from entity e with(nolock) inner join sys_entity_role er on e.id = er.entity_id inner join sys_role r on r.id = er.role_id";
sql += " WHERE ";
sql += " (" + (deleted ? "" : "e.deleted = 0 and") + " r.enterprise_id = @enterprise_id and r.id = @role_id)";
sql += " ) SELECT";
sql += " *";
sql += " FROM";
sql += " result_set";
sql += " WHERE";
sql += " [row_number] BETWEEN " + rStart + " AND " + rEnd;
//DataTable dtUsers = database.ExecuteDataTable( "select * from entity e with(nolock) inner join context c with(nolock) on c.id = e.context_id where " + (deleted ? "" : "e.deleted = 0 and") + " r.enterprise_id = @enterprise_id " + (String.IsNullOrWhiteSpace(text) ? "" : " and e.full_name like '%'+@text+'%' or e.login like '%'+@text+'%' ") + " order by e.full_name", CommandType.Text, par, null);
DataTable dtUsers = database.ExecuteDataTable(sql, CommandType.Text, par, null);
if (dtUsers == null)
{
Error(ErrorType.InternalError, "", "", null);
return(null);
}
if (dtUsers.Rows.Count == 0)
{
Error(ErrorType.InvalidRequest, "User list is empty.", "", null);
return(null);
}
foreach (DataRow dr in dtUsers.Rows)
{
Dictionary <String, Object> newItem = new Dictionary <string, object>();
newItem.Add("userid", dr["id"]);
newItem.Add("alias", dr["alias"]);
newItem.Add("login", dr["login"]);
newItem.Add("full_name", dr["full_name"]);
newItem.Add("create_date", (Int32)((((DateTime)dr["create_date"]) - new DateTime(1970, 1, 1)).TotalSeconds));
newItem.Add("change_password", (dr["change_password"] != DBNull.Value ? (Int32)((((DateTime)dr["change_password"]) - new DateTime(1970, 1, 1)).TotalSeconds) : 0));
newItem.Add("last_login", (dr["last_login"] != DBNull.Value ? (Int32)((((DateTime)dr["last_login"]) - new DateTime(1970, 1, 1)).TotalSeconds) : 0));
newItem.Add("must_change_password", dr["must_change_password"]);
newItem.Add("locked", dr["locked"]);
result.Add(newItem);
}
return(result);
}
public CreateEntityRequestBuilderVisitor(DbParameterCollection parameters, ICrmMetaDataProvider metadataProvider, ConnectionSettings settings)
: this(parameters, metadataProvider, new CrmAdoCrmMetadataNamingProvider(), settings)
{
}
/// <summary>
/// Method to Update Bankprofiles
/// </summary>
/// <param name="argEn">Bankprofile Entity is an Input.</param>
/// <returns>Returns Boolean</returns>
public bool Update(BankProfileEn argEn)
{
bool lbRes = false;
int iOut = 0;
string sqlCmd = "Select count(*) as cnt From SAS_BankDetails WHERE SABD_Code != @SABD_Code and SABD_Desc = @SABD_Desc";
try
{
if (!FormHelp.IsBlank(sqlCmd))
{
DbCommand cmdSel = _DatabaseFactory.GetDbCommand(Helper.GetDataBaseType, sqlCmd, DataBaseConnectionString);
_DatabaseFactory.AddInParameter(ref cmdSel, "@SABD_Code", DbType.String, argEn.BankDetailsCode);
_DatabaseFactory.AddInParameter(ref cmdSel, "@SABD_Desc", DbType.String, argEn.Description);
_DbParameterCollection = cmdSel.Parameters;
using (IDataReader dr = _DatabaseFactory.GetIDataReader(Helper.GetDataBaseType, cmdSel,
DataBaseConnectionString, sqlCmd, _DbParameterCollection).CreateDataReader())
{
if (dr.Read())
{
iOut = clsGeneric.NullToInteger(dr["cnt"]);
}
if (iOut > 0)
{
throw new Exception("Record Already Exist");
}
}
if (iOut == 0)
{
sqlCmd = "UPDATE SAS_BankDetails SET SABD_Code = @SABD_Code, SABD_Desc = @SABD_Desc, SABD_ACCode = @SABD_ACCode, SABD_GLCode = @SABD_GLCode, SABD_Status = @SABD_Status, SABR_Code = @SABR_Code, SABD_UpdatedBy = @SABD_UpdatedBy, SABD_UpdatedDtTm = @SABD_UpdatedDtTm WHERE SABD_Code = @SABD_Code";
if (!FormHelp.IsBlank(sqlCmd))
{
DbCommand cmd = _DatabaseFactory.GetDbCommand(Helper.GetDataBaseType, sqlCmd, DataBaseConnectionString);
_DatabaseFactory.AddInParameter(ref cmd, "@SABD_Code", DbType.String, argEn.BankDetailsCode);
_DatabaseFactory.AddInParameter(ref cmd, "@SABD_Desc", DbType.String, argEn.Description);
_DatabaseFactory.AddInParameter(ref cmd, "@SABD_ACCode", DbType.String, argEn.ACCode);
_DatabaseFactory.AddInParameter(ref cmd, "@SABD_GLCode", DbType.String, argEn.GLCode);
_DatabaseFactory.AddInParameter(ref cmd, "@SABD_Status", DbType.Boolean, argEn.Status);
_DatabaseFactory.AddInParameter(ref cmd, "@SABR_Code", DbType.Int32, argEn.Code);
_DatabaseFactory.AddInParameter(ref cmd, "@SABD_UpdatedBy", DbType.String, argEn.UpdatedBy);
_DatabaseFactory.AddInParameter(ref cmd, "@SABD_UpdatedDtTm", DbType.String, argEn.UpdatedDtTm);
_DbParameterCollection = cmd.Parameters;
int liRowAffected = _DatabaseFactory.ExecuteNonQuery(Helper.GetDataBaseType, cmd,
DataBaseConnectionString, sqlCmd, _DbParameterCollection);
if (liRowAffected > -1)
{
lbRes = true;
}
else
{
throw new Exception("Update Failed! No Row has been updated...");
}
}
}
}
}
catch (Exception ex)
{
throw ex;
}
return(lbRes);
}
public static ShardBatch <TModel> Add <TModel>(this ShardBatch <TModel> batch, Query query, DbParameterCollection parameters, QueryResultModelHandler <object, TModel> resultHandler) where TModel : class, new()
{
batch.Add(new ModelStep <object, TModel>(query, parameters, resultHandler, null));
return(batch);
}
public override int ExecuteByParameter(string sqlstr, DbParameterCollection dbPara)
{
return(-1);
}
public ListStep(Query query, DbParameterCollection parameters, string dataColumnName)
{
_query = query;
_parameters = parameters;
_dataColumnName = dataColumnName;
}
static IEnumerator \u206F([In] DbParameterCollection obj0)
{
// ISSUE: unable to decompile the method.
}
public static ShardBatch <ShardKey <TRecord, TChild> > Add <TRecord, TChild>(this ShardBatch <ShardKey <TRecord, TChild> > batch, Query query, DbParameterCollection parameters, char dataOrigin, string recordIdColumnName, string childIdColumnName) where TRecord : IComparable where TChild : IComparable
{
batch.Add(new ChildStep <TRecord, TChild>(query, parameters, dataOrigin, null, recordIdColumnName, childIdColumnName));
return(batch);
}
/// <summary>
/// This is an internal API that supports the Entity Framework Core infrastructure and not subject to
/// the same compatibility standards as public APIs. It may be changed or removed without notice in
/// any release. You should only use it directly in your code with extreme caution and knowing that
/// doing so can result in application failures when updating to a new Entity Framework Core release.
/// </summary>
public static string FormatParameters(
[NotNull] this DbParameterCollection parameters,
bool logParameterValues)
=> FormatParameterList(parameters, logParameterValues).Join();
public static DatabaseBatch <TRecord> Add <TRecord>(this DatabaseBatch <TRecord> batch, Query query, DbParameterCollection parameters, string dataColumnName) where TRecord : IComparable
{
batch.Add(new RecordStep <TRecord>(query, parameters, dataColumnName));
return(batch);
}
public DbParameterReadOnlyCollection(DbParameterCollection parameterCollection)
{
_paramCollection = parameterCollection;
}
/// <summary>
/// Método privado para processamento do método 'user.search'
/// </summary>
/// <param name="sqlConnection">Conexão com o banco de dados MS-SQL</param>
/// <param name="parameters">Dicionário (String, Object) contendo todos os parâmetros necessários</param>
private List <Dictionary <String, Object> > permissions(IAMDatabase database, Dictionary <String, Object> parameters)
{
List <Dictionary <String, Object> > result = new List <Dictionary <String, Object> >();
if (!parameters.ContainsKey("roleid"))
{
Error(ErrorType.InvalidRequest, "Parameter roleid is not defined.", "", null);
return(null);
}
String role = parameters["roleid"].ToString();
if (String.IsNullOrWhiteSpace(role))
{
Error(ErrorType.InvalidRequest, "Parameter roleid is not defined.", "", null);
return(null);
}
Int64 roleid = 0;
try
{
roleid = Int64.Parse(role);
}
catch
{
Error(ErrorType.InvalidRequest, "Parameter roleid is not a long integer.", "", null);
return(null);
}
DbParameterCollection par = new DbParameterCollection();
par.Add("@enterprise_id", typeof(Int64)).Value = this._enterpriseId;
par.Add("@role_id", typeof(Int64)).Value = roleid;
String sql = "";
sql += " SELECT";
sql += " p.*, m.name module_name, sm.name sub_module_name";
sql += " from sys_permission p inner join sys_role_permission rp on p.id = rp.permission_id";
sql += " inner join sys_role r on r.id = rp.role_id";
sql += " inner join sys_sub_module sm on sm.id = p.submodule_id";
sql += " inner join sys_module m on m.id = sm.module_id";
sql += " WHERE ";
sql += " r.enterprise_id = @enterprise_id and r.id = @role_id";
DataTable dtRolePermission = database.ExecuteDataTable(sql, CommandType.Text, par, null);
if (dtRolePermission == null)
{
Error(ErrorType.InternalError, "", "", null);
return(null);
}
foreach (DataRow dr in dtRolePermission.Rows)
{
Dictionary <String, Object> newItem = new Dictionary <string, object>();
newItem.Add("permission_id", dr["id"]);
newItem.Add("name", dr["name"]);
newItem.Add("key", dr["key"]);
newItem.Add("module_name", dr["module_name"]);
newItem.Add("sub_module_name", dr["sub_module_name"]);
result.Add(newItem);
}
return(result);
}
public DataSet ExecuteProcDataSet(string procName, out DbParameterCollection dbpc, params DbParameter[] param)
{
if (IsBeginTransaction)
{
try
{
Cmd.CommandType = CommandType.StoredProcedure;
Cmd.CommandText = procName;
Cmd.Parameters.AddRange(param);
using (DbDataAdapter da = Factory.CreateDataAdapter())
{
da.SelectCommand = Cmd;
DataSet ds = new DataSet();
da.Fill(ds);
dbpc = da.SelectCommand.Parameters;
return(ds);
}
}
catch
{
try
{
RollbackTransaction();
throw;
}
catch
{
throw;
}
}
}
else
{
using (Con = Factory.CreateConnection())
{
using (Cmd = Con.CreateCommand())
{
try
{
Con.ConnectionString = ConnectionString;
Cmd.CommandType = CommandType.StoredProcedure;
Cmd.CommandText = procName;
Cmd.Parameters.AddRange(param);
using (DbDataAdapter da = Factory.CreateDataAdapter())
{
da.SelectCommand = Cmd;
DataSet ds = new DataSet();
da.Fill(ds);
dbpc = da.SelectCommand.Parameters;
return(ds);
}
}
catch
{
Con.Close();
throw;
}
}
}
}
}
/// <summary>
/// Renders function as SQL statement.
/// </summary>
/// <param name="dbms">Target DBMS.</param>
/// <param name="output">StringBuilder to which SQL is appended.</param>
/// <param name="parameters">SQL parameter collection to which the object's and its children's
/// parameters are added. After the rendering is done the collection contains all parameters with unique names.</param>
public virtual void Render(DbmsType dbms, StringBuilder output, DbParameterCollection parameters)
{
var renderer = DbmsComponentFactory.GetComponent<IFunctionRenderer>(dbms);
renderer.Render(this, dbms, output, parameters);
}
/// <summary>
/// Método privado para processamento do método 'user.search'
/// </summary>
/// <param name="sqlConnection">Conexão com o banco de dados MS-SQL</param>
/// <param name="parameters">Dicionário (String, Object) contendo todos os parâmetros necessários</param>
private List <Dictionary <String, Object> > permissionstree(IAMDatabase database, Dictionary <String, Object> parameters)
{
List <Dictionary <String, Object> > result = new List <Dictionary <String, Object> >();
DbParameterCollection par = new DbParameterCollection();
par.Add("@enterprise_id", typeof(Int64)).Value = this._enterpriseId;
DataTable dtModules = database.ExecuteDataTable("select * from sys_module m order by name", CommandType.Text, null, null);
if (dtModules == null)
{
Error(ErrorType.InternalError, "", "", null);
return(null);
}
foreach (DataRow dr in dtModules.Rows)
{
Dictionary <String, Object> newItem = new Dictionary <string, object>();
List <Dictionary <String, Object> > submodules = new List <Dictionary <string, object> >();
newItem.Add("module_id", dr["id"]);
newItem.Add("name", dr["name"]);
newItem.Add("key", dr["key"]);
DataTable dtSubModules = database.ExecuteDataTable("select * from sys_sub_module m where m.module_id = " + dr["id"] + " order by m.name", CommandType.Text, null, null);
if ((dtSubModules != null) && (dtSubModules.Rows.Count > 0))
{
foreach (DataRow drS in dtSubModules.Rows)
{
Dictionary <String, Object> s = new Dictionary <string, object>();
List <Dictionary <String, Object> > permissions = new List <Dictionary <string, object> >();
s.Add("submodule_id", drS["id"]);
s.Add("name", drS["name"]);
s.Add("key", drS["key"]);
s.Add("api_module", drS["api_module"]);
DataTable dtPermissions = database.ExecuteDataTable("select * from sys_permission p where p.submodule_id = " + drS["id"] + " order by p.name", CommandType.Text, null, null);
if ((dtPermissions != null) && (dtPermissions.Rows.Count > 0))
{
foreach (DataRow drP in dtPermissions.Rows)
{
Dictionary <String, Object> np = new Dictionary <string, object>();
np.Add("permission_id", drP["id"]);
np.Add("name", drP["name"]);
np.Add("key", drP["key"]);
permissions.Add(np);
}
}
s.Add("permissions", permissions);
submodules.Add(s);
}
}
newItem.Add("submodules", submodules);
result.Add(newItem);
}
return(result);
}
