public void CanExecuteQueryThroughDataReaderUsingNullCommand()
{
using (IDbConnection connection = this.db.GetConnection())
{
connection.Open();
this.insertCommand = null;
IDataReader reader = this.db.ExecuteReader(this.insertCommand, null);
}
}
public static void CreateDataAdapterCommands(Database db, ref DBCommandWrapper insertCommand, ref DBCommandWrapper updateCommand, ref DBCommandWrapper deleteCommand)
{
insertCommand = db.GetStoredProcCommandWrapper("RegionInsert2");
updateCommand = db.GetStoredProcCommandWrapper("RegionUpdate");
deleteCommand = db.GetStoredProcCommandWrapper("RegionDelete2");
insertCommand.AddInParameter("vRegionID", DbType.Int32, "RegionID", DataRowVersion.Default);
insertCommand.AddInParameter("vRegionDescription", DbType.String, "RegionDescription", DataRowVersion.Default);
updateCommand.AddInParameter("vRegionID", DbType.Int32, "RegionID", DataRowVersion.Default);
updateCommand.AddInParameter("vRegionDescription", DbType.String, "RegionDescription", DataRowVersion.Default);
deleteCommand.AddInParameter("vRegionID", DbType.Int32, "RegionID", DataRowVersion.Default);
}
protected override void AddParametersFromCache(DBCommandWrapper command)
{
CacheUsed = true;
base.AddParametersFromCache(command);
}
/// <summary>
/// <para>Creates and <see cref="OracleDataReader"/> based on the <paramref name="commandWrapper"/>.</para>
/// </summary>
/// <param name="commandWrapper"><para>The command wrapper to execute.</para></param>
/// <param name="transaction"><para>The transaction to participate in when executing this reader.</para></param>
/// <returns><para>An <see cref="OracleDataReader"/> object.</para></returns>
/// <exception cref="ArgumentNullException">
/// <para><paramref name="commandWrapper"/> can not be <see langword="null"/> (Nothing in Visual Basic).</para>
/// <para>- or -</para>
/// <para><paramref name="transaction"/> can not be <see langword="null"/> (Nothing in Visual Basic).</para>
/// </exception>
public override IDataReader ExecuteReader(DBCommandWrapper commandWrapper, IDbTransaction transaction)
{
return(new Sybase9DataReaderWrapper((ULDataReader)base.ExecuteReader(commandWrapper, transaction)));
}
/// <summary>
/// Creates and <see cref="OracleDataReader"/> based on the <paramref name="commandWrapper"/>.
/// </summary>
/// <param name="commandWrapper">The command wrapper to execute.</param>
/// <returns>An <see cref="OracleDataReader"/> object.</returns>
/// <exception cref="ArgumentNullException">
/// <para><paramref name="commandWrapper"/> can not be <see langword="null"/> (Nothing in Visual Basic).</para>
/// </exception>
public override IDataReader ExecuteReader(DBCommandWrapper commandWrapper)
{
return(new Sybase9DataReaderWrapper((ULDataReader)base.ExecuteReader(commandWrapper)));
}
protected override DataSet GetDataSetFromTable()
{
DBCommandWrapper selectCommand = db.GetStoredProcCommandWrapper("RegionSelect2");
return(db.ExecuteDataSet(selectCommand));
}
public int Update(PermissionDTS dsPermission, IDbTransaction trans)
{
#region comando insert
string sql = "INSERT INTO ";
sql += "EF_PERMISSION ";
sql += "(";
sql += BaseDatos.ConfigurationName == Constantes.BDDORACLE ? "PERMISSION_ID, " : ""; //SOLO ORACLE
sql += "PERMISSION_FLOW, PERMISSION_TYPE, PERMISSION_ADMINISTRATOR, PERMISSION_NEW_REQUEST, PERMISSION_PUBLISH_REPORTS," +
" PERMISSION_VIEW_INDICATORS, PERMISSION_SEARCH, PERMISSION_FILETYPES, PERMISSION_REPORTS,)"
+ " VALUES( ";
sql += BaseDatos.ConfigurationName == Constantes.BDDORACLE ? "PERMISSION_ID.NEXTVAL, " : ""; //SOLO ORACLE
sql += "@Permission_Flow, @Permission_Type, @Permission_Administrator, " +
"@Permission_New_Request, @Permission_Publish_Reports, @Permission_View_Indicators, " +
"@Permission_Search, @Permission_Filetypes, @Permission_Reports)";
DBCommandWrapper insertCommand = BaseDatos.GetSqlStringCommandWrapper(sql);
insertCommand.AddInParameter(
"Permission_Flow", DbType.AnsiStringFixedLength, dsPermission.EF_PERMISSION.PERMISSION_FLOWColumn, DataRowVersion.Current);
insertCommand.AddInParameter(
"@Permission_Type", DbType.AnsiStringFixedLength, dsPermission.EF_PERMISSION.PERMISSION_TYPEColumn, DataRowVersion.Current);
insertCommand.AddInParameter(
"@Permission_Administrator", DbType.AnsiStringFixedLength, dsPermission.EF_PERMISSION.PERMISSION_ADMINISTRATORColumn, DataRowVersion.Current);
insertCommand.AddInParameter(
"@Permission_New_Request", DbType.AnsiStringFixedLength, dsPermission.EF_PERMISSION.PERMISSION_NEW_REQUESTColumn, DataRowVersion.Current);
insertCommand.AddInParameter(
"@Permission_Publish_Reports", DbType.AnsiStringFixedLength, dsPermission.EF_PERMISSION.PERMISSION_PUBLISH_REPORTSColumn, DataRowVersion.Current);
insertCommand.AddInParameter(
"@Permission_View_Indicators", DbType.AnsiStringFixedLength, dsPermission.EF_PERMISSION.PERMISSION_VIEW_INDICATORSColumn, DataRowVersion.Current);
insertCommand.AddInParameter(
"@Permission_Search", DbType.AnsiStringFixedLength, dsPermission.EF_PERMISSION.PERMISSION_SEARCHColumn, DataRowVersion.Current);
insertCommand.AddInParameter(
"@Permission_Filetypes", DbType.AnsiStringFixedLength, dsPermission.EF_PERMISSION.PERMISSION_FILETYPESColumn, DataRowVersion.Current);
insertCommand.AddInParameter(
"@Permission_Reports", DbType.Int32, dsPermission.EF_PERMISSION.PERMISSION_REPORTSColumn, DataRowVersion.Current);
#endregion
#region sentencia update
sql = "UPDATE ";
sql += "EF_PERMISSION ";
sql += "SET ";
sql += " PERMISSION_ID = @Permission_Id, "
+ " PERMISSION_FLOW = @Permission_Flow, "
+ " PERMISSION_TYPE = @Permission_Type, "
+ " PERMISSION_VALUE = @Permission_Value, "
+ " PERMISSION_ADMINISTRATOR = @Permission_Administrator, "
+ " PERMISSION_NEW_REQUEST = @Permission_New_Request, "
+ " PERMISSION_PUBLISH_REPORTS = @Permission_Publish_Reports, "
+ " PERMISSION_VIEW_INDICATORS = @Permission_View_Indicators, "
+ " PERMISSION_SEARCH = @Permission_Search, "
+ " PERMISSION_FILETYPES = @Permission_Filetypes, "
+ " PERMISSION_REPORTS = @Permission_Reports, "
+ " WHERE "
+ " PERMISSION_ID = @Permission_idOriginal ";
DBCommandWrapper updateCommand = BaseDatos.GetSqlStringCommandWrapper(sql);
#endregion
#region parametros del update
sql += "PERMISSION_FLOW, PERMISSION_TYPE, PERMISSION_VALUE, PERMISSION_ADMINISTRATOR, PERMISSION_NEW_REQUEST, PERMISSION_PUBLISH_REPORTS," +
" PERMISSION_VIEW_INDICATORS, PERMISSION_SEARCH, PERMISSION_FILETYPES, PERMISSION_REPORTS) ";
updateCommand.AddInParameter(
"@Permission_idOriginal ", DbType.Int32, dsPermission.EF_PERMISSION.PERMISSION_IDColumn, DataRowVersion.Original);
updateCommand.AddInParameter(
"@Permission_Flow", DbType.Int32, dsPermission.EF_PERMISSION.PERMISSION_FLOWColumn, DataRowVersion.Current);
updateCommand.AddInParameter(
"@Permission_Type", DbType.AnsiStringFixedLength, dsPermission.EF_PERMISSION.PERMISSION_TYPEColumn, DataRowVersion.Current);
updateCommand.AddInParameter(
"@Permission_Administrator", DbType.Int32, dsPermission.EF_PERMISSION.PERMISSION_ADMINISTRATORColumn, DataRowVersion.Current);
updateCommand.AddInParameter(
"@Permission_New_Request", DbType.Int32, dsPermission.EF_PERMISSION.PERMISSION_NEW_REQUESTColumn, DataRowVersion.Current);
updateCommand.AddInParameter(
"@Permission_Publish_Reports", DbType.Int32, dsPermission.EF_PERMISSION.PERMISSION_PUBLISH_REPORTSColumn, DataRowVersion.Current);
updateCommand.AddInParameter(
"@Permission_View_Indicators", DbType.Int32, dsPermission.EF_PERMISSION.PERMISSION_VIEW_INDICATORSColumn, DataRowVersion.Current);
updateCommand.AddInParameter(
"@Permission_Search", DbType.AnsiStringFixedLength, dsPermission.EF_PERMISSION.PERMISSION_SEARCHColumn, DataRowVersion.Current);
updateCommand.AddInParameter(
"@Permission_Filetypes", DbType.AnsiStringFixedLength, dsPermission.EF_PERMISSION.PERMISSION_FILETYPESColumn, DataRowVersion.Current);
updateCommand.AddInParameter(
"@Permission_Reports", DbType.AnsiStringFixedLength, dsPermission.EF_PERMISSION.PERMISSION_REPORTSColumn, DataRowVersion.Current);
DBCommandWrapper deleteCommand = null;
#endregion
#region sql del Delete
sql = "DELETE FROM ";
sql += "EF_PERMISSION ";
sql += "WHERE ";
sql += " PERMISSION_ID = @Permission_idOriginal ";
deleteCommand = BaseDatos.GetSqlStringCommandWrapper(sql);
#endregion
#region parametros del delete
deleteCommand.AddInParameter("@Permission_idOriginal", DbType.Int32, dsPermission.EF_PERMISSION.PERMISSION_IDColumn, DataRowVersion.Original);
#endregion
if (trans == null)
{
return(BaseDatos.UpdateDataSet(dsPermission, "EF_PERMISSION", insertCommand, updateCommand, deleteCommand, UpdateBehavior.Standard));
}
else
{
return(BaseDatos.UpdateDataSet(dsPermission, dsPermission.EF_PERMISSION.TableName, insertCommand, updateCommand, deleteCommand, trans));
}
}
public static void DeleteTestData(ConfigurationContext context)
{
Data.Database db = DatabaseFactory.CreateDatabase("EntLibSecurity");
DBCommandWrapper cmd = db.GetSqlStringCommandWrapper("delete Profiles");
db.ExecuteNonQuery(cmd);
UserRoleManager urm = new UserRoleManager("EntLibSecurity", context);
try
{
urm.DeleteUserRole("testuser", "Users");
}
catch
{
}
try
{
urm.DeleteUserRole("testuser", "Managers");
}
catch
{
}
try
{
urm.DeleteRole("Admins");
}
catch
{
}
try
{
urm.DeleteRole("Managers");
}
catch
{
}
try
{
urm.DeleteRole("Users");
}
catch
{
}
try
{
urm.DeleteUser("testuser");
try { urm.DeleteUser("emptyUser"); } catch {}
}
catch
{
}
try
{
urm.DeleteUser("bogususer");
}
catch
{
}
try
{
urm.DeleteUser("emptyUser");
}
catch
{
}
}
/// <summary>
/// Get a list of users.
/// </summary>
/// <returns>DataSet containing a row for each user.</returns>
public DataSet GetAllUsers()
{
DBCommandWrapper cmd = this.securityDb.GetStoredProcCommandWrapper(SPGetAllUsers);
return(this.securityDb.ExecuteDataSet(cmd));
}
private void RunDefault()
{
dbCommandWrapper = dbPrepareForCheck.GetStoredProcCommandWrapper("RunDefault");
dbPrepareForCheck.ExecuteNonQuery(dbCommandWrapper);
}
private void FillContiAbsentStudent()
{
dbCommandWrapper = dbPrepareForCheck.GetStoredProcCommandWrapper("FillContiAbsentStudent", DateTime.Now);
dbPrepareForCheck.ExecuteNonQuery(dbCommandWrapper);
}
public DataSet DoGetStatusList()
{
doGetStatusListCommand = "GetStatusList";
dbCommandWrapper = dbAttendCalc.GetStoredProcCommandWrapper(doGetStatusListCommand);
return(dbAttendCalc.ExecuteDataSet(dbCommandWrapper));
}
public static void CreateDataAdapterCommands(Database db, ref DBCommandWrapper insertCommand, ref DBCommandWrapper updateCommand, ref DBCommandWrapper deleteCommand)
{
insertCommand = db.GetStoredProcCommandWrapper("RegionInsert");
updateCommand = db.GetStoredProcCommandWrapper("RegionUpdate");
deleteCommand = db.GetStoredProcCommandWrapper("RegionDelete");
insertCommand.AddInParameter("vRegionID", DbType.Int32, "RegionID", DataRowVersion.Default);
insertCommand.AddInParameter("vRegionDescription", DbType.String, "RegionDescription", DataRowVersion.Default);
updateCommand.AddInParameter("vRegionID", DbType.Int32, "RegionID", DataRowVersion.Default);
updateCommand.AddInParameter("vRegionDescription", DbType.String, "RegionDescription", DataRowVersion.Default);
deleteCommand.AddInParameter("vRegionID", DbType.Int32, "RegionID", DataRowVersion.Default);
}
protected override void AddParametersFromCache(DBCommandWrapper command)
{
CacheUsed = true;
base.AddParametersFromCache(command);
}
private void BuildWhereClause(DBCommandWrapper dbCommandWrapper, string conjunction, StringBuilder query)
{
if (_whereParameters != null && _whereParameters.Count > 0)
{
query.Append(" WHERE ");
bool first = true;
bool requiresParam;
WhereParameter wItem;
bool skipConjunction = false;
string paramName;
string columnName;
foreach (object obj in _whereParameters)
{
// Maybe we injected text or a WhereParameter
if (obj.GetType().ToString() == "System.String")
{
string text = obj as string;
query.Append(text);
if (text == "(")
{
skipConjunction = true;
}
}
else
{
wItem = obj as WhereParameter;
if (wItem.IsDirty)
{
if (!first && !skipConjunction)
{
if (wItem.Conjunction != WhereParameter.Conj.UseDefault)
{
if (wItem.Conjunction == WhereParameter.Conj.And)
{
query.Append(" AND ");
}
else
{
query.Append(" OR ");
}
}
else
{
query.Append(" " + conjunction + " ");
}
}
requiresParam = true;
columnName = string.Format(FieldFormat, wItem.SchemaItem.FieldName);
paramName = ParameterToken + wItem.SchemaItem.FieldName;
string originalParamName = paramName;
int count = 1;
while (dbCommandWrapper.Command.Parameters.Contains(paramName))
{
paramName = originalParamName + count++;
}
switch (wItem.Operator)
{
case WhereParameter.Operand.Equal:
query.AppendFormat("{0} = {1} ", columnName, paramName);
break;
case WhereParameter.Operand.NotEqual:
query.AppendFormat("{0} <> {1} ", columnName, paramName);
break;
case WhereParameter.Operand.GreaterThan:
query.AppendFormat("{0} > {1} ", columnName, paramName);
break;
case WhereParameter.Operand.LessThan:
query.AppendFormat("{0} < {1} ", columnName, paramName);
break;
case WhereParameter.Operand.LessThanOrEqual:
query.AppendFormat("{0} <= {1} ", columnName, paramName);
break;
case WhereParameter.Operand.GreaterThanOrEqual:
query.AppendFormat("{0} >= {1} ", columnName, paramName);
break;
case WhereParameter.Operand.Like:
query.AppendFormat("{0} LIKE {1} ", columnName, paramName);
break;
case WhereParameter.Operand.NotLike:
query.AppendFormat("{0} NOT LIKE {1} ", columnName, paramName);
break;
case WhereParameter.Operand.IsNull:
query.AppendFormat("{0} IS NULL ", columnName);
requiresParam = false;
break;
case WhereParameter.Operand.IsNotNull:
query.AppendFormat("{0} IS NOT NULL ", columnName);
requiresParam = false;
break;
case WhereParameter.Operand.In:
query.AppendFormat("{0} IN ({1}) ", columnName, wItem.Value);
requiresParam = false;
break;
case WhereParameter.Operand.NotIn:
query.AppendFormat("{0} NOT IN ({1}) ", columnName, wItem.Value);
requiresParam = false;
break;
case WhereParameter.Operand.Between:
query.AppendFormat("{0} BETWEEN {1} AND {2}", columnName, paramName + "Begin", paramName + "End");
dbCommandWrapper.AddInParameter(paramName + "Begin", wItem.SchemaItem.DBType, wItem.BetweenBeginValue.ToString());
dbCommandWrapper.AddInParameter(paramName + "End", wItem.SchemaItem.DBType, wItem.BetweenEndValue.ToString());
requiresParam = false;
break;
}
if (requiresParam)
{
dbCommandWrapper.AddInParameter(paramName, wItem.SchemaItem.DBType, wItem.Value);
}
first = false;
skipConjunction = false;
}
}
}
}
}
/// <summary>
/// Builds a provider-specific SELECT query against the <see cref="EasyObject.QuerySource"/>.
/// <seealso cref="EasyObject.QuerySource"/>
/// </summary>
/// <param name="dbCommandWrapper">An wrapper for an Enterprise Library command object</param>
/// <param name="conjunction">The conjunction to use between multiple <see cref="WhereParameter"/>s</param>
protected override void BuildSelectQuery(DBCommandWrapper dbCommandWrapper, string conjunction)
{
bool hasColumn = false;
bool selectAll = true;
StringBuilder query = new StringBuilder("SELECT ");
if (this.Distinct)
{
query.Append("DISTINCT ");
}
if (this.TopN >= 0)
{
query.AppendFormat("TOP {0} ", this.TopN.ToString());
}
if (this.ResultColumns.Length > 0)
{
query.Append(this.ResultColumns);
hasColumn = true;
selectAll = false;
}
if (this._countAll)
{
if (hasColumn)
{
query.Append(", ");
}
query.Append("COUNT(*)");
if (this._countAllAlias != string.Empty)
{
query.Append(" AS ");
// Need DBMS string delimiter here
query.AppendFormat(AliasFormat, this._countAllAlias);
}
hasColumn = true;
selectAll = false;
}
if (_aggregateParameters != null && _aggregateParameters.Count > 0)
{
bool isFirst = true;
if (hasColumn)
{
query.Append(", ");
}
AggregateParameter wItem;
foreach (object obj in _aggregateParameters)
{
wItem = obj as AggregateParameter;
if (wItem.IsDirty)
{
if (isFirst)
{
query.Append(GetAggregate(wItem, true));
isFirst = false;
}
else
{
query.Append(", " + GetAggregate(wItem, true));
}
}
}
selectAll = false;
}
if (selectAll)
{
query.Append("*");
}
query.Append(" FROM ");
query.AppendFormat(FieldFormat, this._entity.QuerySource);
BuildWhereClause(dbCommandWrapper, conjunction, query);
if (_groupBy.Length > 0)
{
query.Append(" GROUP BY " + _groupBy);
if (this._withRollup)
{
query.Append(" WITH ROLLUP");
}
}
if (this.OrderBy.Length > 0)
{
query.AppendFormat(" ORDER BY {0}", this.OrderBy);
}
dbCommandWrapper.Command.CommandText = query.ToString();
}
