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 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 IDbCommand GetCommand(IDbConnection connection,
ISqlQuery query,
IDbTransaction transaction)
{
LastQuery = query;
var lastParameters = new List <IDataParameter>();
IDbCommand cmd = connection.CreateCommand();
cmd.CommandText = query.GetQuery();
if (query.Parameters != null)
{
foreach (var item in query.Parameters)
{
if (HasParameter(query.GetQuery(), item.ParameterName))
{
IDataParameter parm;
if (item is QueryParameter)
{
parm = cmd.CreateParameter();
parm.ParameterName = item.ParameterName;
parm.Value = item.Value == null ? DBNull.Value : item.Value;
parm.DbType = item.DbType;
parm.Direction = item.Direction;
OnMapParameter(item, parm);
}
else
{
parm = item;
}
cmd.Parameters.Add(parm);
lastParameters.Add(parm);
}
}
}
if (transaction != null)
{
cmd.Transaction = transaction;
}
return(cmd);
}
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 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 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);
}
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;
}
/// <summary>
/// Note that FirstRow is zero-indexed, so 0 means ignore (or the first row should be the usual first row).
/// </summary>
/// <param name="query"></param>
/// <returns></returns>
protected override string GetQueryForRows(ISqlQuery query)
{
if (query.FirstRow == 0)
{
SqlClause clause = SqlClause.From | SqlClause.Where |
SqlClause.Having | SqlClause.GroupBy | SqlClause.OrderBy;
if (query is ISqlQueryMaker)
{
var comp = (ISqlQueryMaker)query;
return String.Format("SELECT TOP {0} {1} {2}",
comp.TotalRows,
comp.Select,
comp.GetQuery( clause));
} else {
string sql = query.GetQuery();
int pos = sql.ToLower().LastIndexOf("order by");
string order="";
if (pos>0) {
order = sql.Substring(pos);
sql = sql.Substring(0,pos-1);
}
return String.Format("SELECT TOP {0} * FROM ({1}) __subquery {2}",
query.TotalRows,
sql,
order);
}
}
else
{
if (query is ISqlQueryMaker)
{
var comp = (ISqlQueryMaker)query;
string querySQL = "";
string where = comp.Where.ToString();
// always us PK as the final sort to eliminate possible paging problems
string orderByClause = " ORDER BY " + comp.OrderBy.Clone().AddIfNotPresent(comp.PrimaryKey).ToString();
// Add TablePrimaryKey to sort phrase to ensure that there is a consistent sort order, if the user-defined order is not explicit for every record.
// Pagination can be wrong otherwise.
//SortPhrase += (SortOrder==""?"":",")+TablePrimaryKey;
if (comp.TotalRows > 0)
{
querySQL = @"SELECT " + comp.Select + " FROM " + comp.From + " WHERE " + comp.PrimaryKey + " IN " +
"(SELECT TOP " + comp.TotalRows + " " + comp.PrimaryKey + " FROM " + comp.From + " WHERE [WhereSubstitution1]" + comp.PrimaryKey + " NOT IN " +
"(SELECT TOP " + comp.FirstRow + " " + comp.PrimaryKey + " FROM " + comp.From + "[WhereSubstitution2]" + orderByClause + ")" +
orderByClause + ")" +
orderByClause;
}
else
{
querySQL = @"SELECT " + comp.Select + " FROM " + comp.From + " WHERE " + comp.PrimaryKey + " NOT IN " +
"(SELECT TOP " + comp.FirstRow + " " + comp.PrimaryKey + " FROM " + comp.From + "[WhereSubstitution2]" + orderByClause + ")" +
orderByClause;
}
querySQL = querySQL.Replace("[WhereSubstitution1]", where!="" ?
where + " AND " : "");
querySQL = querySQL.Replace("[WhereSubstitution2]", where != "" ?
" WHERE " + where : "");
return querySQL;
}
else
{
throw new InvalidOperationException("I can't do paging in an ad-hoc query.");
}
}
}
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 IDbCommand GetCommand(IDbConnection connection,
ISqlQuery query,
IDbTransaction transaction)
{
LastQuery = query;
var lastParameters = new List<IDataParameter>();
IDbCommand cmd = connection.CreateCommand();
cmd.CommandText = query.GetQuery();
if (query.Parameters != null)
{
foreach (var item in query.Parameters)
{
if (HasParameter(query.GetQuery(), item.ParameterName)) {
IDataParameter parm;
if (item is QueryParameter)
{
parm = cmd.CreateParameter();
parm.ParameterName = item.ParameterName;
parm.Value = item.Value == null ? DBNull.Value : item.Value;
parm.DbType = item.DbType;
parm.Direction = item.Direction;
OnMapParameter(item, parm);
}
else
{
parm = item;
}
cmd.Parameters.Add(parm);
lastParameters.Add(parm);
}
}
}
if (transaction != null) {
cmd.Transaction = transaction;
}
return cmd;
}
public SqlQueryDef(ISqlQuery query )
{
QuerySql = query.GetQuery();
QueryParameters.AddRange(query.Parameters);
}
/// <summary>
/// Note that FirstRow is zero-indexed, so 0 means ignore (or the first row should be the usual first row).
/// </summary>
/// <param name="query"></param>
/// <returns></returns>
protected override string GetQueryForRows(ISqlQuery query)
{
if (query.FirstRow == 0)
{
SqlClause clause = SqlClause.From | SqlClause.Where |
SqlClause.Having | SqlClause.GroupBy | SqlClause.OrderBy;
if (query is ISqlQueryMaker)
{
var comp = (ISqlQueryMaker)query;
return(String.Format("SELECT TOP {0} {1} {2}",
comp.TotalRows,
comp.Select,
comp.GetQuery(clause)));
}
else
{
string sql = query.GetQuery();
int pos = sql.ToLower().LastIndexOf("order by");
string order = "";
if (pos > 0)
{
order = sql.Substring(pos);
sql = sql.Substring(0, pos - 1);
}
return(String.Format("SELECT TOP {0} * FROM ({1}) __subquery {2}",
query.TotalRows,
sql,
order));
}
}
else
{
if (query is ISqlQueryMaker)
{
var comp = (ISqlQueryMaker)query;
string querySQL = "";
string where = comp.Where.ToString();
// always us PK as the final sort to eliminate possible paging problems
string orderByClause = " ORDER BY " + comp.OrderBy.Clone().AddIfNotPresent(comp.PrimaryKey).ToString();
// Add TablePrimaryKey to sort phrase to ensure that there is a consistent sort order, if the user-defined order is not explicit for every record.
// Pagination can be wrong otherwise.
//SortPhrase += (SortOrder==""?"":",")+TablePrimaryKey;
if (comp.TotalRows > 0)
{
querySQL = @"SELECT " + comp.Select + " FROM " + comp.From + " WHERE " + comp.PrimaryKey + " IN " +
"(SELECT TOP " + comp.TotalRows + " " + comp.PrimaryKey + " FROM " + comp.From + " WHERE [WhereSubstitution1]" + comp.PrimaryKey + " NOT IN " +
"(SELECT TOP " + comp.FirstRow + " " + comp.PrimaryKey + " FROM " + comp.From + "[WhereSubstitution2]" + orderByClause + ")" +
orderByClause + ")" +
orderByClause;
}
else
{
querySQL = @"SELECT " + comp.Select + " FROM " + comp.From + " WHERE " + comp.PrimaryKey + " NOT IN " +
"(SELECT TOP " + comp.FirstRow + " " + comp.PrimaryKey + " FROM " + comp.From + "[WhereSubstitution2]" + orderByClause + ")" +
orderByClause;
}
querySQL = querySQL.Replace("[WhereSubstitution1]", where != "" ?
where + " AND " : "");
querySQL = querySQL.Replace("[WhereSubstitution2]", where != "" ?
" WHERE " + where : "");
return(querySQL);
}
else
{
throw new InvalidOperationException("I can't do paging in an ad-hoc query.");
}
}
}
public SqlQueryDef(ISqlQuery query)
{
QuerySql = query.GetQuery();
QueryParameters.AddRange(query.Parameters);
}
/// <summary>
/// options may include: an IDbConnection, an IDbTransaction, CommandBehavior. Save queries should not
/// include any other parameters
/// </summary>
/// <param name="obj"></param>
/// <param name="options"></param>
/// <returns></returns>
public bool Save(object obj, params object[] options)
{
ParameterParser pp = new ParameterParser("", options);
if (pp.Parameters.Count > 0)
{
throw new IQException("The only allowed parameters for a Save are IDbConnection, IDbTransaction, and CommandBehavior");
}
IDBObjectData dbData = IQ.DBData(obj);
DBClassInfo info = dbData.ClassInfo;
IQEventType eventType = IQEventType.Save;
var eventHandler = dbData.IQEventHandlerFunc;
if (eventHandler != null)
{
eventHandler(eventType | IQEventType.Before, dbData);
}
QueryType queryType = dbData.IsNew() ? QueryType.Insert : QueryType.Update;
ISqlQuery query = IQ.CreateQuery(queryType);
query.AddFieldMap(info.FieldNameMap);
query.TableName = dbData.TableName;
bool isDirty = false;
bool isNew = dbData.IsNew();
string pk = dbData.ClassInfo.PrimaryKey.Name;
foreach (var item in dbData.ClassInfo.FieldInfo)
{
string name = item.Name;
if (!item.IsPrimaryKey && !item.IsReadOnly &&
(isNew || dbData.IsDirty(name)))
{
query.AddUpdateData(name, info[name].GetValue(obj));
isDirty = true;
}
}
bool success = false;
if (isDirty)
{
if (queryType == QueryType.Insert)
{
eventType |= IQEventType.Insert;
int newPK = StorageController.RunQueryInsert(pp.Connection, query.GetQuery(), query.Parameters,
transaction: pp.Transaction);
if (newPK <= 0)
{
throw new Exception("The record could not be inserted.");
}
dbData.ClassInfo[pk].SetValue(obj, newPK);
success = true;
}
else
{
eventType |= IQEventType.Update;
query.AddWhereParam(dbData.ClassInfo.PrimaryKey.Name, dbData.ClassInfo.PrimaryKey.GetValue(obj));
success = StorageController.RunQueryScalar(pp.Connection, query.GetQuery(), query.Parameters,
transaction: pp.Transaction) > 0;
}
}
else
{
success = false;
}
if (eventHandler != null)
{
eventHandler(eventType | IQEventType.After, dbData);
}
if (success)
{
dbData.Clean();
}
if (pp.CommandBehavior == CommandBehavior.CloseConnection)
{
pp.Connection.Close();
}
return(success);
}
