/// <summary>
/// Method needed to re-create parameters on each new execution of
/// the query cause
/// </summary>
private void ResetParameters()
{
foreach (var entryKey in ParametersMap.Keys)
{
QueryParameterEntry entry = (QueryParameterEntry)ParametersMap[entryKey];
// create a new SQL parameter
SqlParameter newDbParameter = CreateParameterFromEntry(entry);
// set the new parameter
entry.Parameter = newDbParameter;
}
}
private void InsertParametersIntoDbCommand(SqlCommand dbCommand)
{
// clear any previous parameters
dbCommand.Parameters.Clear();
// take all parameters and insert them into DB command
foreach (var parameterKey in ParametersMap.Keys)
{
QueryParameterEntry entry = (QueryParameterEntry)ParametersMap[parameterKey];
dbCommand.Parameters.Add(entry.Parameter);
}
}
/// <summary>
/// Check if the parameter is set in a query
/// </summary>
/// <param name="paramName"></param>
/// <returns></returns>
public bool IsParameterSet(string paramName)
{
if (string.IsNullOrEmpty(paramName))
{
throw new ArgumentException("Parameter name is not defined!", "paramName");
}
QueryParameterEntry entry = (QueryParameterEntry)ParametersMap[paramName];
return((entry != null) &&
(entry.Parameter != null) &&
string.Equals(entry.Parameter.ParameterName, paramName));
}
private SqlParameter CreateParameterFromEntry(QueryParameterEntry entry)
{
SqlParameter newDbParameter = DbContext.CreateParameter();
newDbParameter.ParameterName = entry.Config.ParameterName;
newDbParameter.SqlDbType = entry.Config.DbType;
newDbParameter.IsNullable = entry.Config.IsNullable;
newDbParameter.Size = entry.Config.Size;
newDbParameter.Direction = entry.Config.Direction;
newDbParameter.Value = entry.Parameter.Value;
return(newDbParameter);
}
protected bool RemoveParameter(string paramName)
{
if (string.IsNullOrEmpty(paramName))
{
throw new ArgumentException("Parameter name is not defined!", "paramName");
}
QueryParameterEntry entry = (QueryParameterEntry)ParametersMap[paramName];
if (entry == null)
{
return(false);
}
ParametersMap.Remove(paramName);
return(true);
}
private string UnParameterizeSqlQuery(string sql)
{
foreach (var parameterKey in ParametersMap.Keys)
{
// take all entries
QueryParameterEntry entry = (QueryParameterEntry)ParametersMap[parameterKey];
// in case the parameter is set with NoQuotes
bool noQuotes = (entry.Parameter.Value is string) && entry.Config.NoQuotes;
//
sql = sql.Replace(entry.Parameter.ParameterName, ValueToString(entry.Parameter.Value, noQuotes));
}
return(sql);
}
protected virtual TParamType GetParameter <TParamType>(string paramName)
{
if (string.IsNullOrEmpty(paramName))
{
throw new ArgumentException("Parameter name is not defined!", "paramName");
}
QueryParameterEntry entry = (QueryParameterEntry)ParametersMap[paramName];
if (entry == null)
{
// parameter entry was not found by parameter name
string errorMsg = string.Format("Parameter [{0}] was not found within query parameters!", paramName);
throw new InvalidOperationException(errorMsg);
}
object parameterValue = entry.Parameter.Value;
return(((parameterValue != null) && !DBNull.Value.Equals(parameterValue))
? (TParamType)parameterValue
: default(TParamType));
}
protected virtual void SetParameter(string paramName, object paramValue, QueryParameterConfig config)
{
if (string.IsNullOrEmpty(paramName))
{
throw new ArgumentNullException("paramName");
}
if (config == null)
{
throw new ArgumentNullException("config", "Parameter configuration is not defined!");
}
// get a parameter from the parameters hashmap
QueryParameterEntry entry = (QueryParameterEntry)ParametersMap[paramName];
if ((entry != null) && (entry.Config != null) && entry.Config.Equals(config))
{
// entry is found for parameter
// just create a new DB parameter
// leave the parameter config untouched if it did not change
if (ExecutedTimes > 0)
{
SqlParameter newDbParameter = config.IsStoredProcedureParameter
? DbContext.CreateStoredProcParameter(
paramName: paramName, paramValue: paramValue,
direction: config.Direction, sqlDbType: config.DbType,
isNullable: config.IsNullable, size: config.Size)
: DbContext.CreateParameter(paramName, paramValue);
// reset parameter with a one with updated values
entry.Parameter = newDbParameter;
return;
}
// just reset the parameter value
// without re-creating parameter
entry.Parameter.Value = paramValue ?? DBNull.Value;
}
else
{
SqlParameter newDbParameter = config.IsStoredProcedureParameter
? DbContext.CreateStoredProcParameter(
paramName: paramName,
paramValue: paramValue,
direction: config.Direction,
sqlDbType: config.DbType,
isNullable: config.IsNullable,
size: config.Size)
: DbContext.CreateParameter(paramName, paramValue);
// create a new parameter entry, contating the parameter itself and its configuration
QueryParameterEntry newEntry = new QueryParameterEntry
{
Parameter = newDbParameter,
Config = config
};
// save entry in the parameters map
ParametersMap[paramName] = newEntry;
}
}