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);
}
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");
}
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;
}
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;
}
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;
}
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 ISqlQuery Clone()
{
SqlQueryDef query = new SqlQueryDef(QuerySql, Parameters);
return query;
}
public ISqlQuery Clone()
{
SqlQueryDef query = new SqlQueryDef(QuerySql, Parameters);
return(query);
}
