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); }