// Map @@paramaters directly (e.g. for "select in")
protected string ProcessSql(string querySql, IEnumerable <IDataParameter> parameters)
{
// Get rid of whitespace
string sql = CleanSql(querySql);
if (parameters != null)
{
List <string> parmList = new List <string>(SqlQueryUtility.GetParameterNames(querySql));
foreach (var parm in parameters)
{
// turn @@x into the actual value
if (sql.Contains("@" + parm.ParameterName))
{
sql = querySql.Replace("@" + parm.ParameterName, parm.Value.ToString());
}
// Remove null-valued parameters
if (parm.Value == null || parm.Value == DBNull.Value)
{
// TODO: Look for expression on either side of parameter, replace = with IS
}
}
}
return(sql);
}
public CacheKey(ISqlQuery query)
{
// The key must always be made from a clone - otherwise, if the original query is changed & rerun, it will still
// match the cached value (being the same object)
Query = SqlQueryUtility.QueryAsSql(query.GetQuery(), query.Parameters);
ISqlQueryMaker comp = query as ISqlQueryMaker;
if (comp != null)
{
TableName = comp.TableName;
}
}
public string ValueString(string template)
{
string dataValue;
bool quoted = false;
switch (SqlDataFormat)
{
case SqlDataFormat.Null:
dataValue = "NULL";
break;
case SqlDataFormat.DateTime:
dataValue = String.Format("{0:d/M/yyyy HH:mm:ss}", Value);
quoted = true;
break;
case SqlDataFormat.Numeric:
dataValue = Value.ToString();
break;
case SqlDataFormat.String:
dataValue = SqlQueryUtility.CleanSql((string)Value);
quoted = true;
break;
case SqlDataFormat.Parameter:
dataValue = (string)Value;
quoted = false;
if (!String.IsNullOrEmpty(template) && template != "{0}")
{
throw new ArgumentException("This is a parameter-type value, the template is invalid");
}
break;
default:
throw new InvalidCastException("Unhandled data type");
}
string quoteChar = quoted ? "'" : "";
if (String.IsNullOrEmpty(template))
{
return(quoteChar + dataValue + quoteChar);
}
else
{
return(quoteChar + String.Format(template, dataValue) + quoteChar);
}
}
/// <summary>
/// Only works for MSSQL - trys to map the query replacing parameter values with actual values
/// </summary>
/// <returns></returns>
public virtual string LastQueryAudit()
{
return(SqlQueryUtility.QueryAsSql(LastQuery.GetQuery(), LastQuery.Parameters.Select(item => (System.Data.SqlClient.SqlParameter)item)));
}
protected void ProcessParameters(IEnumerable <object> parameters)
{
List <string> parmNames = new List <string>(SqlQueryUtility.GetParameterNames(Query));
var parms = new List <object>(UnwrapParameters(parameters));
_Parameters = new List <IDataParameter>();
var interim = new List <object>();
if (parms.Count == 0)
{
return;
}
ParameterMode mode = 0;
int index;
for (index = 0; index < parms.Count; index++)
{
object cur = parms[index];
if (cur is IQSqlDirectiveType)
{
IsStoredProcedure = (IQSqlDirectiveType)cur == IQSqlDirectiveType.StoredProcedure ? true : false;
}
else if (cur is IDataParameter)
{
if (mode != 0 && mode != ParameterMode.KVPs)
{
throw new ArgumentException("An IDataParameter was found after a value-typed parameter. You can't mix parameter types.");
}
_Parameters.Add((IDataParameter)cur);
}
else if (!IsParameterType(cur))
{
// An object; treat it's properties as key/value pairs
if (mode != 0 && mode != ParameterMode.KVPs)
{
throw new ArgumentException("An object parameter was found after a value-typed parameter. To map object properties " +
"as parameters, only objects can be passed.");
}
mode = ParameterMode.KVPs;
foreach (var kvp in GetProperties(cur))
{
int parmIndex = parmNames.IndexOf(CleanParmName(kvp.Key), IQ.Config.ParmNameComparer);
if (parmIndex >= 0)
{
//Use name from the list of parms to ensure proper case
_Parameters.Add(CreateParameter(parmNames[parmIndex], kvp.Value));
}
}
}
else
{
if (mode != 0 && mode != ParameterMode.Mapped)
{
throw new ArgumentException("Can't mix parameter types.");
}
mode = ParameterMode.Mapped;
interim.Add(cur);
}
}
// now address interim parameters, if any
if (interim.Count > 0)
{
bool isNamed = false;
if ((interim.Count % 2) == 0)
{
isNamed = true;
List <IDataParameter> tempList = new List <IDataParameter>();
for (int i = 0; i < interim.Count; i += 2)
{
var val = interim[i];
if (val != null && interim[i].GetType() == typeof(string))
{
string parmName = (string)interim[i];
// Don't check parm name list for stored procedures
if (!IsStoredProcedure)
{
int parmIndex = parmNames.IndexOf(CleanParmName(parmName), IQ.Config.ParmNameComparer);
if (parmIndex >= 0)
{
tempList.Add(CreateParameter(parmNames[parmIndex], interim[i + 1]));
}
else
{
isNamed = false;
break;
}
}
else
{
tempList.Add(CreateParameter((string)interim[i], interim[i + 1]));
}
}
else
{
isNamed = false;
break;
}
}
if (isNamed)
{
_Parameters.AddRange(tempList);
return;
}
}
// Final type of query to parse: a field name passed with a single parameter. If there are no spaces,
// no parameters in the query, and one parm passed, make it an "equals"
else if (!IsStoredProcedure && interim.Count == 1 &&
parmNames.Count == 0 &&
Query.IndexOfAny(new char[] { ' ', '=' }) <= 0)
{
WhereClause = QueryBuilder.WhereEquals(Query, interim[0]);
//Query = Query + "=@val";
//_Parameters.Add(CreateParameter("@val", interim[0]));
return;
}
if (!IsStoredProcedure)
{
// Final validation: if #s match, make a query
if (interim.Count == parmNames.Count)
{
for (int i = 0; i < interim.Count; i++)
{
_Parameters.Add(CreateParameter(parmNames[i], interim[i]));
}
return;
}
else
{
string names = String.Join(",", parmNames);
string values = String.Join(",", interim);
throw new ArgumentException(String.Format("Parameters must match the query parameter names or count. Names: [{0}] Values: [{1}]", names, values));
}
}
}
else
{
if (_Parameters.Count != parmNames.Count)
{
string names = String.Join(",", parmNames);
string values = String.Join(",", _Parameters.Select(item => item.ParameterName));
throw new ArgumentException(String.Format("Parameters must match the query parameter names or count. Names: [{0}] Values: [{1}]", names, values));
}
}
}
