public virtual IDataReader RunQuery(IDbConnection connection,
ISqlQuery query,
IDbTransaction transaction = null,
CommandBehavior commandBehavior = CommandBehavior.Default)
{
IDataReader dataReader = null;
string sql;
if (query.FirstRow > 0 || query.TotalRows > 0)
{
sql = GetQueryForRows(query);
}
else
{
sql = query.GetQuery();
}
var newQuery = new SqlQueryDef(sql, query.Parameters);
BeforeRunQuery(query);
using (IDbCommand cmd = GetCommand(connection, newQuery, transaction))
{
ExecuteSqlFinal(new Action(() =>
{
dataReader = cmd.ExecuteReader(commandBehavior);
}));
cmd.Parameters.Clear();
}
OnQueryComplete();
return(dataReader);
}
public IQuery <dynamic> From(string query, params object[] parameters)
{
var op = new OptionParser(parameters);
var raw = new SqlQueryDef(query, parameters);
return((IQuery <dynamic>)(new Query(this, raw)));
}
public virtual int Count(IDbConnection connection,
ISqlQuery query,
IDbTransaction transaction = null,
CommandBehavior commandBehavior = CommandBehavior.Default)
{
int count;
if (query is ISqlQueryMaker)
{
var comp = (ISqlQueryMaker)query;
count = Count(connection, comp.TableName, comp.Where.ToString(), comp.Parameters, transaction, commandBehavior);
}
else
{
ValidateQueryType(query.GetQuery(), "select");
var countQuery = new SqlQueryDef("SELECT COUNT(*) FROM (" + query + ") q", query.Parameters);
count = RunQueryScalar(connection, countQuery, transaction, commandBehavior);
}
count -= query.FirstRow;
if (query.TotalRows > 0)
{
count = Math.Min(count, query.TotalRows);
}
return(count);
}
public int QueryScalar(string query, params object[] parameters)
{
var raw = new SqlQueryDef(query, parameters);
return(DataStorageController.RunQueryScalar(Connection,
raw,
Transaction,
CommandBehavior));
}
protected virtual int Count(IDbConnection connection,
string tableName,
string where,
IEnumerable <IDataParameter> parameters,
IDbTransaction transaction,
CommandBehavior commandBehavior = CommandBehavior.Default)
{
var countQuery = new SqlQueryDef(String.Format("SELECT COUNT(*) FROM {0}{1}", tableName,
(!String.IsNullOrEmpty(where) ?
" WHERE " + where :
"")),
parameters);
return(RunQueryScalar(connection, countQuery, transaction, commandBehavior));
}
public void NullHandling()
{
/// Nulls should be stripped
var testQ = new SqlQueryDef("someField=@val", new object[] { null });
Assert.AreEqual("someField=@val", testQ.GetQuery());
Assert.AreEqual(1, testQ.Parameters.Count());
testQ = new SqlQueryDef("someField=@p1 and someField2=@p2", new object[] { null, DBNull.Value });
Assert.AreEqual("someField=@p1 and someField2=@p2", testQ.GetQuery());
Assert.AreEqual(testQ.Parameters.Count(), 2);
Assert.AreEqual(DBNull.Value, testQ.Parameters.ElementAt(0).Value);
Assert.AreEqual(DBNull.Value, testQ.Parameters.ElementAt(1).Value);
}
/// <summary>
/// When passing type=0, it's never going to try to build a query - only fully formed query SQl will work.
/// </summary>
/// <param name="type"></param>
/// <param name="query"></param>
/// <param name="parameters"></param>
protected void ParseQuery(object query, IEnumerable <object> parameters)
{
ISqlQuery outputQuery = null;
if (query == null)
{
throw new IQMissingQueryException();
}
else if (query is SqlQueryMaker)
{
ExpectNoParameters(parameters);
if (QueryType != 0 && ((SqlQueryMaker)query).QueryType != QueryType)
{
throw new IQException("The query passed was not of type '" + QueryType.ToString() + "' as required for this operation.");
}
outputQuery = (SqlQueryMaker)query;
}
else if (QueryType != 0)
{
outputQuery = ParseComplexQuery(query, parameters);
}
else if (query is string)
{
string queryString = (string)query;
ParameterParser pp = new ParameterParser(queryString, parameters);
outputQuery = new SqlQueryDef(queryString, pp.Parameters);
}
else
{
throw new IQException("The type of object '" + query.GetType().ToString() + "' passed as a query isn't something I know how to handle.");
}
if (outputQuery == null)
{
throw new IQMissingQueryException();
}
if (outputQuery.QueryType == QueryType.Invalid && QueryType != 0)
{
throw new IQException("Couldn't make a valid query out of the mess you passed me. Check the SQL and the parameters.");
}
Query = outputQuery;
}
public void Basic()
{
var testQ = new SqlQueryDef("someField=@val", new object[] { 123 });
Assert.AreEqual("someField=@val", testQ.GetQuery());
Assert.AreEqual("@val", testQ.Parameters.First().ParameterName, "Parm name was correct when created for a simple field compariosn");
Assert.AreEqual(123, testQ.Parameters.First().Value, "Parm value correct");
testQ = new SqlQueryDef("someField=@valParm", new object[] { "abc" });
Assert.AreEqual("someField=@valParm", testQ.GetQuery());
Assert.AreEqual("@valParm", testQ.Parameters.First().ParameterName, "Parm name was correct when created for a simple field compariosn");
Assert.AreEqual("abc", testQ.Parameters.First().Value, "Parm value correct");
string query = "someField=@val1 and someOtherField=@val2";
testQ = new SqlQueryDef(query, new object[] { "@val2", "abc", "@val1", 678 });
Assert.AreEqual(testQ.GetQuery(), query);
Assert.AreEqual(testQ.Parameters.ElementAt(1).ParameterName, "@val1", "Parm name was correct when created for a simple field compariosn");
Assert.AreEqual(testQ.Parameters.ElementAt(1).Value, 678, "Parm value correct");
}
protected override int InsertAndReturnNewID(IDbConnection conn,
ISqlQuery query,
IDbTransaction transaction = null,
CommandBehavior commandBehavior = CommandBehavior.Default)
{
int result = 0;
BeforeRunQuery(query);
var newQuery = new SqlQueryDef(query.GetQuery() + "; SET @___ID=SCOPE_IDENTITY();", query.Parameters);
using (IDbCommand cmd = GetCommand(conn, newQuery, transaction))
{
IDataParameter ID = cmd.CreateParameter();
ID.ParameterName = "@___ID";
ID.DbType = System.Data.DbType.Int32;
ID.Direction = ParameterDirection.Output;
ID.Value = -1;
cmd.Parameters.Add(ID);
ExecuteSqlFinal(new Action(() =>
{
cmd.ExecuteScalar();
}));
result = Convert.ToInt32(ID.Value.ToString());
cmd.Parameters.Clear();
cmd.Dispose();
}
if (commandBehavior == CommandBehavior.CloseConnection)
{
conn.Close();
}
OnQueryComplete();
return(result);
}
protected ISqlQuery ParseComplexQuery(object query, IEnumerable <object> parms)
{
ISqlQuery outputQuery = null;
// We always want to parse the parameters. But if the thing passed to us as "query" is not a string, then
// just assume that all the parms are option type parameters and don't pass a query to ParameterParser
string querySource = query is string?
(string)query:
"";
ParameterParser pp = new ParameterParser(querySource, parms);
if (Types.IsNumericType(query))
{
// It's a single numeric value - assume it's a primary key
ExpectNoParameters(pp.Parameters);
var classInfo = IQ.ClassInfo <T>();
ISqlQueryMaker queryPK = classInfo.GetQuery();
queryPK.Where.Add(classInfo.PrimaryKeyField.Name, query);
outputQuery = queryPK;
}
else if (query is string)
{
bool isMappable = Types.IsMappable(typeof(T));
// First check if its a single named field
if (isMappable)
{
var classInfo = IQ.ClassInfo <T>();
// Try to create a valid raw query.. if it's not valid, assume it's a where
if (QueryType == QueryType.Where || pp.QueryType == QueryType.Invalid)
{
ISqlQueryMaker queryPK = classInfo.GetQuery();
//var whereString = new WhereString(pp.Query,
// pp.Parameters.Count > 0 ?
// pp.Parameters.ToArray():
// null);
queryPK.Where.Add(pp.GetWhereClause());
outputQuery = queryPK;
}
else
{
outputQuery = new SqlQueryDef(pp.GetQuery(QueryType), pp.Parameters);
}
}
else
{
// it's mapped to a primitive type -
outputQuery = new SqlQueryDef(pp.GetQuery(QueryType), pp.Parameters);
}
}
if (outputQuery.QueryType != QueryType)
{
throw new IQException("Wrong type of query passed to method: was " + outputQuery.ToString() + ", expected " + QueryType.ToString());
}
return(outputQuery);
}
