public DojoSeminarRegistrationOptionCollection GetCollection(int topCount, string whereClause, string sortClause, params DojoSeminarRegistrationOptionFlags[] optionFlags) { StringBuilder query; Database database; DbCommand dbCommand; IDataReader r; DojoSeminarRegistrationOptionCollection dojoSeminarRegistrationOptionCollection; int hashcode; // Cache Handling hashcode = 0; if (cacheEnabled) { hashcode = topCount.GetHashCode() + whereClause.GetHashCode() + sortClause.GetHashCode() + tableName.GetHashCode(); DojoSeminarRegistrationOptionCollection collection = cacheFindCollection(hashcode); if (collection != null) { return(collection); } } int innerJoinOffset; query = new StringBuilder("SELECT "); if (topCount > 0) { query.Append("TOP "); query.Append(topCount); query.Append(" "); } foreach (string columnName in InnerJoinFields) { query.Append("DojoSeminarRegistrationOption."); query.Append(columnName); query.Append(","); } innerJoinOffset = InnerJoinFields.GetUpperBound(0) + 1; int parentOptionOffset = -1; int parentOptionItemOffset = -1; int parentRegistrationOffset = -1; int parentRegistrationParentSeminarOffset = -1; int parentRegistrationContactOffset = -1; int parentRegistrationInvoiceLineOffset = -1; int parentRegistrationSalesOrderLineOffset = -1; int parentRegistrationCustomerOffset = -1; // // Append Option Flag Fields // if (optionFlags != null) { for (int x = 0; x < optionFlags.Length; x++) { switch (optionFlags[x]) { case DojoSeminarRegistrationOptionFlags.ParentOption: for (int i = 0; i <= DojoSeminarOptionManager.InnerJoinFields.GetUpperBound(0); i++) { query.Append("ParentOption."); query.Append(DojoSeminarOptionManager.InnerJoinFields[i]); query.Append(","); } parentOptionOffset = innerJoinOffset; innerJoinOffset = parentOptionOffset + DojoSeminarOptionManager.InnerJoinFields.GetUpperBound(0) + 1; break; case DojoSeminarRegistrationOptionFlags.ParentOptionItem: for (int i = 0; i <= RHItemManager.InnerJoinFields.GetUpperBound(0); i++) { query.Append("ParentOption_Item."); query.Append(RHItemManager.InnerJoinFields[i]); query.Append(","); } parentOptionItemOffset = innerJoinOffset; innerJoinOffset = parentOptionItemOffset + RHItemManager.InnerJoinFields.GetUpperBound(0) + 1; break; case DojoSeminarRegistrationOptionFlags.ParentRegistration: for (int i = 0; i <= DojoSeminarRegistrationManager.InnerJoinFields.GetUpperBound(0); i++) { query.Append("ParentRegistration."); query.Append(DojoSeminarRegistrationManager.InnerJoinFields[i]); query.Append(","); } parentRegistrationOffset = innerJoinOffset; innerJoinOffset = parentRegistrationOffset + DojoSeminarRegistrationManager.InnerJoinFields.GetUpperBound(0) + 1; break; case DojoSeminarRegistrationOptionFlags.ParentRegistrationParentSeminar: for (int i = 0; i <= DojoSeminarManager.InnerJoinFields.GetUpperBound(0); i++) { query.Append("ParentRegistration_ParentSeminar."); query.Append(DojoSeminarManager.InnerJoinFields[i]); query.Append(","); } parentRegistrationParentSeminarOffset = innerJoinOffset; innerJoinOffset = parentRegistrationParentSeminarOffset + DojoSeminarManager.InnerJoinFields.GetUpperBound(0) + 1; break; case DojoSeminarRegistrationOptionFlags.ParentRegistrationContact: for (int i = 0; i <= GreyFoxContactManager.InnerJoinFields.GetUpperBound(0); i++) { query.Append("ParentRegistration_Contact."); query.Append(GreyFoxContactManager.InnerJoinFields[i]); query.Append(","); } parentRegistrationContactOffset = innerJoinOffset; innerJoinOffset = parentRegistrationContactOffset + GreyFoxContactManager.InnerJoinFields.GetUpperBound(0) + 1; break; case DojoSeminarRegistrationOptionFlags.ParentRegistrationInvoiceLine: for (int i = 0; i <= RHInvoiceLineManager.InnerJoinFields.GetUpperBound(0); i++) { query.Append("ParentRegistration_InvoiceLine."); query.Append(RHInvoiceLineManager.InnerJoinFields[i]); query.Append(","); } parentRegistrationInvoiceLineOffset = innerJoinOffset; innerJoinOffset = parentRegistrationInvoiceLineOffset + RHInvoiceLineManager.InnerJoinFields.GetUpperBound(0) + 1; break; case DojoSeminarRegistrationOptionFlags.ParentRegistrationSalesOrderLine: for (int i = 0; i <= RHSalesOrderLineManager.InnerJoinFields.GetUpperBound(0); i++) { query.Append("ParentRegistration_SalesOrderLine."); query.Append(RHSalesOrderLineManager.InnerJoinFields[i]); query.Append(","); } parentRegistrationSalesOrderLineOffset = innerJoinOffset; innerJoinOffset = parentRegistrationSalesOrderLineOffset + RHSalesOrderLineManager.InnerJoinFields.GetUpperBound(0) + 1; break; case DojoSeminarRegistrationOptionFlags.ParentRegistrationCustomer: for (int i = 0; i <= RHCustomerManager.InnerJoinFields.GetUpperBound(0); i++) { query.Append("ParentRegistration_Customer."); query.Append(RHCustomerManager.InnerJoinFields[i]); query.Append(","); } parentRegistrationCustomerOffset = innerJoinOffset; innerJoinOffset = parentRegistrationCustomerOffset + RHCustomerManager.InnerJoinFields.GetUpperBound(0) + 1; break; } } } // // Remove trailing comma // query.Length--; if (optionFlags != null) { query.Append(" FROM "); // // Start INNER JOIN expressions // for (int x = 0; x < optionFlags.Length; x++) { query.Append("("); } query.Append("kitTessen_SeminarRegistrationOptions AS DojoSeminarRegistrationOption"); } else { query.Append(" FROM kitTessen_SeminarRegistrationOptions AS DojoSeminarRegistrationOption"); } // // Finish INNER JOIN expressions // if (optionFlags != null) { for (int x = 0; x < optionFlags.Length; x++) { switch (optionFlags[x]) { case DojoSeminarRegistrationOptionFlags.ParentOption: query.Append(" LEFT JOIN kitTessen_SeminarOptions AS ParentOption ON DojoSeminarRegistrationOption.ParentOptionID = ParentOption.DojoSeminarOptionID)"); break; case DojoSeminarRegistrationOptionFlags.ParentOptionItem: query.Append(" LEFT JOIN RH_Items AS ParentOption_Item ON ParentOption.ItemID = ParentOption_Item.RHItemID)"); break; case DojoSeminarRegistrationOptionFlags.ParentRegistration: query.Append(" LEFT JOIN kitTessen_SeminarRegistrations AS ParentRegistration ON DojoSeminarRegistrationOption.ParentRegistrationID = ParentRegistration.DojoSeminarRegistrationID)"); break; case DojoSeminarRegistrationOptionFlags.ParentRegistrationParentSeminar: query.Append(" LEFT JOIN kitTessen_Seminars AS ParentRegistration_ParentSeminar ON ParentRegistration.ParentSeminarID = ParentRegistration_ParentSeminar.DojoSeminarID)"); break; case DojoSeminarRegistrationOptionFlags.ParentRegistrationContact: query.Append(" LEFT JOIN kitTessen_SeminarRegistrations_Contacts AS ParentRegistration_Contact ON ParentRegistration.ContactID = ParentRegistration_Contact.GreyFoxContactID)"); break; case DojoSeminarRegistrationOptionFlags.ParentRegistrationInvoiceLine: query.Append(" LEFT JOIN RH_InvoiceLines AS ParentRegistration_InvoiceLine ON ParentRegistration.InvoiceLineID = ParentRegistration_InvoiceLine.RHInvoiceLineID)"); break; case DojoSeminarRegistrationOptionFlags.ParentRegistrationSalesOrderLine: query.Append(" LEFT JOIN RH_SalesOrderLines AS ParentRegistration_SalesOrderLine ON ParentRegistration.SalesOrderLineID = ParentRegistration_SalesOrderLine.RHSalesOrderLineID)"); break; case DojoSeminarRegistrationOptionFlags.ParentRegistrationCustomer: query.Append(" LEFT JOIN RH_Customers AS ParentRegistration_Customer ON ParentRegistration.CustomerID = ParentRegistration_Customer.RHCustomerID)"); break; } } } // // Render where clause // if (whereClause != string.Empty) { query.Append(" WHERE "); query.Append(whereClause); } // // Render sort clause // if (sortClause != string.Empty) { query.Append(" ORDER BY "); query.Append(sortClause); } // // Render final semicolon // query.Append(";"); database = DatabaseFactory.CreateDatabase(); dbCommand = database.GetSqlStringCommand(query.ToString()); #if DEBUG try { r = database.ExecuteReader(dbCommand); } catch (Exception e) { string msg = e.Message; throw(new Exception(msg + " --- Query: " + query.ToString())); } #else r = database.ExecuteReader(dbCommand); #endif dojoSeminarRegistrationOptionCollection = new DojoSeminarRegistrationOptionCollection(); while (r.Read()) { DojoSeminarRegistrationOption dojoSeminarRegistrationOption = ParseFromReader(r, 0, 1); // Fill ParentOption if (parentOptionOffset != -1 && !r.IsDBNull(parentOptionOffset)) { DojoSeminarOptionManager.FillFromReader(dojoSeminarRegistrationOption.parentOption, r, parentOptionOffset, parentOptionOffset + 1); // Fill if (parentOptionItemOffset != -1 && !r.IsDBNull(parentOptionItemOffset)) { RHItemManager.FillFromReader(dojoSeminarRegistrationOption.parentOption.Item, r, parentOptionItemOffset, parentOptionItemOffset + 1); } } // Fill ParentRegistration if (parentRegistrationOffset != -1 && !r.IsDBNull(parentRegistrationOffset)) { DojoSeminarRegistrationManager.FillFromReader(dojoSeminarRegistrationOption.parentRegistration, r, parentRegistrationOffset, parentRegistrationOffset + 1); // Fill if (parentRegistrationParentSeminarOffset != -1 && !r.IsDBNull(parentRegistrationParentSeminarOffset)) { DojoSeminarManager.FillFromReader(dojoSeminarRegistrationOption.parentRegistration.ParentSeminar, r, parentRegistrationParentSeminarOffset, parentRegistrationParentSeminarOffset + 1); } // Fill if (parentRegistrationContactOffset != -1 && !r.IsDBNull(parentRegistrationContactOffset)) { GreyFoxContactManager.FillFromReader(dojoSeminarRegistrationOption.parentRegistration.Contact, "kitTessen_SeminarRegistrations_Contacts", r, parentRegistrationContactOffset, parentRegistrationContactOffset + 1); } // Fill Invoice Line if (parentRegistrationInvoiceLineOffset != -1 && !r.IsDBNull(parentRegistrationInvoiceLineOffset)) { RHInvoiceLineManager.FillFromReader(dojoSeminarRegistrationOption.parentRegistration.InvoiceLine, r, parentRegistrationInvoiceLineOffset, parentRegistrationInvoiceLineOffset + 1); } // Fill Sales Order Line if (parentRegistrationSalesOrderLineOffset != -1 && !r.IsDBNull(parentRegistrationSalesOrderLineOffset)) { RHSalesOrderLineManager.FillFromReader(dojoSeminarRegistrationOption.parentRegistration.SalesOrderLine, r, parentRegistrationSalesOrderLineOffset, parentRegistrationSalesOrderLineOffset + 1); } // Fill Registrant if (parentRegistrationCustomerOffset != -1 && !r.IsDBNull(parentRegistrationCustomerOffset)) { RHCustomerManager.FillFromReader(dojoSeminarRegistrationOption.parentRegistration.Customer, r, parentRegistrationCustomerOffset, parentRegistrationCustomerOffset + 1); } } dojoSeminarRegistrationOptionCollection.Add(dojoSeminarRegistrationOption); } // Microsoft DAAB still needs to close readers. r.Close(); if (cacheEnabled) { cacheStoreCollection(hashcode, dojoSeminarRegistrationOptionCollection); } return(dojoSeminarRegistrationOptionCollection); }
public GreyFoxUserCollection GetCollection(int topCount, string whereClause, string sortClause, params GreyFoxUserFlags[] optionFlags) { StringBuilder query; Database database; DbCommand dbCommand; IDataReader r; GreyFoxUserCollection greyFoxUserCollection; int innerJoinOffset; query = new StringBuilder("SELECT "); if (topCount > 0) { query.Append("TOP "); query.Append(topCount); query.Append(" "); } foreach (string columnName in InnerJoinFields) { query.Append("GreyFoxUser."); query.Append(columnName); query.Append(","); } innerJoinOffset = InnerJoinFields.GetUpperBound(0) + 1; int contactOffset = -1; // // Append Option Flag Fields // if (optionFlags != null) { for (int x = 0; x < optionFlags.Length; x++) { switch (optionFlags[x]) { case GreyFoxUserFlags.Contact: for (int i = 0; i <= GreyFoxContactManager.InnerJoinFields.GetUpperBound(0); i++) { query.Append("Contact."); query.Append(GreyFoxContactManager.InnerJoinFields[i]); query.Append(","); } contactOffset = innerJoinOffset; innerJoinOffset = contactOffset + GreyFoxContactManager.InnerJoinFields.GetUpperBound(0) + 1; break; } } } // // Remove trailing comma // query.Length--; if (optionFlags != null) { query.Append(" FROM "); // // Start INNER JOIN expressions // for (int x = 0; x < optionFlags.Length; x++) { query.Append("("); } query.Append("sysGlobal_Users AS GreyFoxUser"); } else { query.Append(" FROM sysGlobal_Users AS GreyFoxUser"); } // // Finish INNER JOIN expressions // if (optionFlags != null) { for (int x = 0; x < optionFlags.Length; x++) { switch (optionFlags[x]) { case GreyFoxUserFlags.Contact: query.Append(" LEFT JOIN sysGlobal_Contacts AS Contact ON GreyFoxUser.ContactID = Contact.GreyFoxContactID)"); break; } } } // // Render where clause // if (whereClause != string.Empty) { query.Append(" WHERE "); query.Append(whereClause); } // // Render sort clause // if (sortClause != string.Empty) { query.Append(" ORDER BY "); query.Append(sortClause); } // // Render final semicolon // query.Append(";"); database = DatabaseFactory.CreateDatabase(); dbCommand = database.GetSqlStringCommand(query.ToString()); #if DEBUG try { r = database.ExecuteReader(dbCommand); } catch (Exception e) { string msg = e.Message; throw(new Exception(msg + " --- Query: " + query.ToString())); } #else r = database.ExecuteReader(dbCommand); #endif greyFoxUserCollection = new GreyFoxUserCollection(); while (r.Read()) { GreyFoxUser greyFoxUser = ParseFromReader(r, 0, 1); // Fill Contact if (contactOffset != -1 && !r.IsDBNull(contactOffset)) { GreyFoxContactManager.FillFromReader(greyFoxUser.contact, "sysGlobal_Contacts", r, contactOffset, contactOffset + 1); } greyFoxUserCollection.Add(greyFoxUser); } // Microsoft DAAB still needs to close readers. r.Close(); return(greyFoxUserCollection); }
public DbContentHitCollection GetCollection(int topCount, string whereClause, string sortClause, params DbContentHitFlags[] optionFlags) { StringBuilder query; Database database; DbCommand dbCommand; IDataReader r; DbContentHitCollection dbContentHitCollection; int innerJoinOffset; query = new StringBuilder("SELECT "); if (topCount > 0) { query.Append("TOP "); query.Append(topCount); query.Append(" "); } foreach (string columnName in InnerJoinFields) { query.Append("DbContentHit."); query.Append(columnName); query.Append(","); } innerJoinOffset = InnerJoinFields.GetUpperBound(0) + 1; int userOffset = -1; int userContactOffset = -1; int requestContentOffset = -1; int requestContentStatusOffset = -1; int requestContentParentCatalogOffset = -1; int requestContentRatingOffset = -1; int requestContentWorkingDraftOffset = -1; // // Append Option Flag Fields // if (optionFlags != null) { for (int x = 0; x < optionFlags.Length; x++) { switch (optionFlags[x]) { case DbContentHitFlags.User: for (int i = 0; i <= GreyFoxUserManager.InnerJoinFields.GetUpperBound(0); i++) { query.Append("User."); query.Append(GreyFoxUserManager.InnerJoinFields[i]); query.Append(","); } userOffset = innerJoinOffset; innerJoinOffset = userOffset + GreyFoxUserManager.InnerJoinFields.GetUpperBound(0) + 1; break; case DbContentHitFlags.UserContact: for (int i = 0; i <= GreyFoxContactManager.InnerJoinFields.GetUpperBound(0); i++) { query.Append("User_Contact."); query.Append(GreyFoxContactManager.InnerJoinFields[i]); query.Append(","); } userContactOffset = innerJoinOffset; innerJoinOffset = userContactOffset + GreyFoxContactManager.InnerJoinFields.GetUpperBound(0) + 1; break; case DbContentHitFlags.RequestContent: for (int i = 0; i <= DbContentClipManager.InnerJoinFields.GetUpperBound(0); i++) { query.Append("RequestContent."); query.Append(DbContentClipManager.InnerJoinFields[i]); query.Append(","); } requestContentOffset = innerJoinOffset; innerJoinOffset = requestContentOffset + DbContentClipManager.InnerJoinFields.GetUpperBound(0) + 1; break; case DbContentHitFlags.RequestContentStatus: for (int i = 0; i <= DbContentStatusManager.InnerJoinFields.GetUpperBound(0); i++) { query.Append("RequestContent_Status."); query.Append(DbContentStatusManager.InnerJoinFields[i]); query.Append(","); } requestContentStatusOffset = innerJoinOffset; innerJoinOffset = requestContentStatusOffset + DbContentStatusManager.InnerJoinFields.GetUpperBound(0) + 1; break; case DbContentHitFlags.RequestContentParentCatalog: for (int i = 0; i <= DbContentCatalogManager.InnerJoinFields.GetUpperBound(0); i++) { query.Append("RequestContent_ParentCatalog."); query.Append(DbContentCatalogManager.InnerJoinFields[i]); query.Append(","); } requestContentParentCatalogOffset = innerJoinOffset; innerJoinOffset = requestContentParentCatalogOffset + DbContentCatalogManager.InnerJoinFields.GetUpperBound(0) + 1; break; case DbContentHitFlags.RequestContentRating: for (int i = 0; i <= DbContentRatingManager.InnerJoinFields.GetUpperBound(0); i++) { query.Append("RequestContent_Rating."); query.Append(DbContentRatingManager.InnerJoinFields[i]); query.Append(","); } requestContentRatingOffset = innerJoinOffset; innerJoinOffset = requestContentRatingOffset + DbContentRatingManager.InnerJoinFields.GetUpperBound(0) + 1; break; case DbContentHitFlags.RequestContentWorkingDraft: for (int i = 0; i <= DbContentClipManager.InnerJoinFields.GetUpperBound(0); i++) { query.Append("RequestContent_WorkingDraft."); query.Append(DbContentClipManager.InnerJoinFields[i]); query.Append(","); } requestContentWorkingDraftOffset = innerJoinOffset; innerJoinOffset = requestContentWorkingDraftOffset + DbContentClipManager.InnerJoinFields.GetUpperBound(0) + 1; break; } } } // // Remove trailing comma // query.Length--; if (optionFlags != null) { query.Append(" FROM "); // // Start INNER JOIN expressions // for (int x = 0; x < optionFlags.Length; x++) { query.Append("("); } query.Append("kitCms_Hits AS DbContentHit"); } else { query.Append(" FROM kitCms_Hits AS DbContentHit"); } // // Finish INNER JOIN expressions // if (optionFlags != null) { for (int x = 0; x < optionFlags.Length; x++) { switch (optionFlags[x]) { case DbContentHitFlags.User: query.Append(" LEFT JOIN sysGlobal_Users AS User ON DbContentHit.UserID = User.GreyFoxUserID)"); break; case DbContentHitFlags.UserContact: query.Append(" LEFT JOIN sysGlobal_Contacts AS User_Contact ON User.ContactID = User_Contact.GreyFoxContactID)"); break; case DbContentHitFlags.RequestContent: query.Append(" LEFT JOIN kitCms_Clips AS RequestContent ON DbContentHit.RequestContentID = RequestContent.DbContentClipID)"); break; case DbContentHitFlags.RequestContentStatus: query.Append(" LEFT JOIN kitCms_Statuses AS RequestContent_Status ON RequestContent.StatusID = RequestContent_Status.DbContentStatusID)"); break; case DbContentHitFlags.RequestContentParentCatalog: query.Append(" LEFT JOIN kitCms_Catalogs AS RequestContent_ParentCatalog ON RequestContent.ParentCatalogID = RequestContent_ParentCatalog.DbContentCatalogID)"); break; case DbContentHitFlags.RequestContentRating: query.Append(" LEFT JOIN kitCms_Ratings AS RequestContent_Rating ON RequestContent.RatingID = RequestContent_Rating.DbContentRatingID)"); break; case DbContentHitFlags.RequestContentWorkingDraft: query.Append(" LEFT JOIN kitCms_Clips AS RequestContent_WorkingDraft ON RequestContent.WorkingDraftID = RequestContent_WorkingDraft.DbContentClipID)"); break; } } } // // Render where clause // if (whereClause != string.Empty) { query.Append(" WHERE "); query.Append(whereClause); } // // Render sort clause // if (sortClause != string.Empty) { query.Append(" ORDER BY "); query.Append(sortClause); } // // Render final semicolon // query.Append(";"); database = DatabaseFactory.CreateDatabase(); dbCommand = database.GetSqlStringCommand(query.ToString()); #if DEBUG try { r = database.ExecuteReader(dbCommand); } catch (Exception e) { string msg = e.Message; throw(new Exception(msg + " --- Query: " + query.ToString())); } #else r = database.ExecuteReader(dbCommand); #endif dbContentHitCollection = new DbContentHitCollection(); while (r.Read()) { DbContentHit dbContentHit = ParseFromReader(r, 0, 1); // Fill User if (userOffset != -1 && !r.IsDBNull(userOffset)) { GreyFoxUserManager.FillFromReader(dbContentHit.user, r, userOffset, userOffset + 1); // Fill if (userContactOffset != -1 && !r.IsDBNull(userContactOffset)) { GreyFoxContactManager.FillFromReader(dbContentHit.user.Contact, "sysGlobal_Contacts", r, userContactOffset, userContactOffset + 1); } } // Fill RequestContent if (requestContentOffset != -1 && !r.IsDBNull(requestContentOffset)) { DbContentClipManager.FillFromReader(dbContentHit.requestContent, r, requestContentOffset, requestContentOffset + 1); // Fill if (requestContentStatusOffset != -1 && !r.IsDBNull(requestContentStatusOffset)) { DbContentStatusManager.FillFromReader(dbContentHit.requestContent.Status, r, requestContentStatusOffset, requestContentStatusOffset + 1); } // Fill Parent Catalog if (requestContentParentCatalogOffset != -1 && !r.IsDBNull(requestContentParentCatalogOffset)) { DbContentCatalogManager.FillFromReader(dbContentHit.requestContent.ParentCatalog, r, requestContentParentCatalogOffset, requestContentParentCatalogOffset + 1); } // Fill if (requestContentRatingOffset != -1 && !r.IsDBNull(requestContentRatingOffset)) { DbContentRatingManager.FillFromReader(dbContentHit.requestContent.Rating, r, requestContentRatingOffset, requestContentRatingOffset + 1); } // Fill if (requestContentWorkingDraftOffset != -1 && !r.IsDBNull(requestContentWorkingDraftOffset)) { DbContentClipManager.FillFromReader(dbContentHit.requestContent.WorkingDraft, r, requestContentWorkingDraftOffset, requestContentWorkingDraftOffset + 1); } } dbContentHitCollection.Add(dbContentHit); } return(dbContentHitCollection); }
public DojoOrganizationCollection GetCollection(int topCount, string whereClause, string sortClause, params DojoOrganizationFlags[] optionFlags) { StringBuilder query; Database database; DbCommand dbCommand; IDataReader r; DojoOrganizationCollection dojoOrganizationCollection; int hashcode; // Cache Handling hashcode = 0; if (cacheEnabled) { hashcode = topCount.GetHashCode() + whereClause.GetHashCode() + sortClause.GetHashCode() + tableName.GetHashCode(); DojoOrganizationCollection collection = cacheFindCollection(hashcode); if (collection != null) { return(collection); } } int innerJoinOffset; query = new StringBuilder("SELECT "); if (topCount > 0) { query.Append("TOP "); query.Append(topCount); query.Append(" "); } foreach (string columnName in InnerJoinFields) { query.Append("DojoOrganization."); query.Append(columnName); query.Append(","); } innerJoinOffset = InnerJoinFields.GetUpperBound(0) + 1; int locationOffset = -1; int administrativeContactOffset = -1; int defaultMemberTypeOffset = -1; // // Append Option Flag Fields // if (optionFlags != null) { for (int x = 0; x < optionFlags.Length; x++) { switch (optionFlags[x]) { case DojoOrganizationFlags.Location: for (int i = 0; i <= GreyFoxContactManager.InnerJoinFields.GetUpperBound(0); i++) { query.Append("Location."); query.Append(GreyFoxContactManager.InnerJoinFields[i]); query.Append(","); } locationOffset = innerJoinOffset; innerJoinOffset = locationOffset + GreyFoxContactManager.InnerJoinFields.GetUpperBound(0) + 1; break; case DojoOrganizationFlags.AdministrativeContact: for (int i = 0; i <= GreyFoxContactManager.InnerJoinFields.GetUpperBound(0); i++) { query.Append("AdministrativeContact."); query.Append(GreyFoxContactManager.InnerJoinFields[i]); query.Append(","); } administrativeContactOffset = innerJoinOffset; innerJoinOffset = administrativeContactOffset + GreyFoxContactManager.InnerJoinFields.GetUpperBound(0) + 1; break; case DojoOrganizationFlags.DefaultMemberType: for (int i = 0; i <= DojoMemberTypeManager.InnerJoinFields.GetUpperBound(0); i++) { query.Append("DefaultMemberType."); query.Append(DojoMemberTypeManager.InnerJoinFields[i]); query.Append(","); } defaultMemberTypeOffset = innerJoinOffset; innerJoinOffset = defaultMemberTypeOffset + DojoMemberTypeManager.InnerJoinFields.GetUpperBound(0) + 1; break; } } } // // Remove trailing comma // query.Length--; if (optionFlags != null) { query.Append(" FROM "); // // Start INNER JOIN expressions // for (int x = 0; x < optionFlags.Length; x++) { query.Append("("); } query.Append("kitTessen_Organizations AS DojoOrganization"); } else { query.Append(" FROM kitTessen_Organizations AS DojoOrganization"); } // // Finish INNER JOIN expressions // if (optionFlags != null) { for (int x = 0; x < optionFlags.Length; x++) { switch (optionFlags[x]) { case DojoOrganizationFlags.Location: query.Append(" LEFT JOIN kitTessen_Locations AS Location ON DojoOrganization.LocationID = Location.GreyFoxContactID)"); break; case DojoOrganizationFlags.AdministrativeContact: query.Append(" LEFT JOIN sysGlobal_Contacts AS AdministrativeContact ON DojoOrganization.AdministrativeContactID = AdministrativeContact.GreyFoxContactID)"); break; case DojoOrganizationFlags.DefaultMemberType: query.Append(" LEFT JOIN kitTessen_MemberTypes AS DefaultMemberType ON DojoOrganization.DefaultMemberTypeID = DefaultMemberType.DojoMemberTypeID)"); break; } } } // // Render where clause // if (whereClause != string.Empty) { query.Append(" WHERE "); query.Append(whereClause); } // // Render sort clause // if (sortClause != string.Empty) { query.Append(" ORDER BY "); query.Append(sortClause); } // // Render final semicolon // query.Append(";"); database = DatabaseFactory.CreateDatabase(); dbCommand = database.GetSqlStringCommand(query.ToString()); #if DEBUG try { r = database.ExecuteReader(dbCommand); } catch (Exception e) { string msg = e.Message; throw(new Exception(msg + " --- Query: " + query.ToString())); } #else r = database.ExecuteReader(dbCommand); #endif dojoOrganizationCollection = new DojoOrganizationCollection(); while (r.Read()) { DojoOrganization dojoOrganization = ParseFromReader(r, 0, 1); // Fill Location if (locationOffset != -1 && !r.IsDBNull(locationOffset)) { GreyFoxContactManager.FillFromReader(dojoOrganization.location, "kitTessen_Locations", r, locationOffset, locationOffset + 1); } // Fill AdministrativeContact if (administrativeContactOffset != -1 && !r.IsDBNull(administrativeContactOffset)) { GreyFoxContactManager.FillFromReader(dojoOrganization.administrativeContact, "sysGlobal_Contacts", r, administrativeContactOffset, administrativeContactOffset + 1); } // Fill DefaultMemberType if (defaultMemberTypeOffset != -1 && !r.IsDBNull(defaultMemberTypeOffset)) { DojoMemberTypeManager.FillFromReader(dojoOrganization.defaultMemberType, r, defaultMemberTypeOffset, defaultMemberTypeOffset + 1); } dojoOrganizationCollection.Add(dojoOrganization); } // Microsoft DAAB still needs to close readers. r.Close(); if (cacheEnabled) { cacheStoreCollection(hashcode, dojoOrganizationCollection); } return(dojoOrganizationCollection); }
public GreyFoxUserPreferenceCollection GetCollection(int topCount, string whereClause, string sortClause, params GreyFoxUserPreferenceFlags[] optionFlags) { StringBuilder query = new StringBuilder("SELECT "); if (topCount > 0) { query.Append("TOP "); query.Append(topCount); query.Append(" "); } foreach (string columnName in InnerJoinFields) { query.Append("sysGlobal_UserPreferences."); query.Append(columnName); query.Append(","); } int innerJoinOffset = InnerJoinFields.GetUpperBound(0) + 1; int userOffset = -1; int userContactOffset = -1; // // Append Option Flag Fields // if (optionFlags != null) { for (int x = 0; x < optionFlags.Length; x++) { switch (optionFlags[x]) { case GreyFoxUserPreferenceFlags.User: for (int i = 0; i <= GreyFoxUserManager.InnerJoinFields.GetUpperBound(0); i++) { query.Append("sysGlobal_Users."); query.Append(GreyFoxUserManager.InnerJoinFields[i]); query.Append(","); } userOffset = innerJoinOffset; innerJoinOffset = userOffset + GreyFoxUserManager.InnerJoinFields.GetUpperBound(0) + 1; break; case GreyFoxUserPreferenceFlags.UserContact: for (int i = 0; i <= GreyFoxContactManager.InnerJoinFields.GetUpperBound(0); i++) { query.Append("sysGlobal_Contacts."); query.Append(GreyFoxContactManager.InnerJoinFields[i]); query.Append(","); } userContactOffset = innerJoinOffset; innerJoinOffset = userContactOffset + GreyFoxContactManager.InnerJoinFields.GetUpperBound(0) + 1; break; } } } // // Remove trailing comma // query.Length--; if (optionFlags != null) { query.Append(" FROM "); // // Start INNER JOIN expressions // for (int x = 0; x < optionFlags.Length; x++) { query.Append("("); } query.Append("sysGlobal_UserPreferences"); } else { query.Append(" FROM sysGlobal_UserPreferences "); } // // Finish INNER JOIN expressions // if (optionFlags != null) { for (int x = 0; x < optionFlags.Length; x++) { switch (optionFlags[x]) { case GreyFoxUserPreferenceFlags.User: query.Append(" LEFT JOIN sysGlobal_Users ON sysGlobal_UserPreferences.UserID = sysGlobal_Users.GreyFoxUserID)"); break; case GreyFoxUserPreferenceFlags.UserContact: query.Append(" LEFT JOIN sysGlobal_Contacts ON sysGlobal_Users.ContactID = sysGlobal_Contacts.GreyFoxContactID)"); break; } } } // // Render where clause // if (whereClause != string.Empty) { query.Append(" WHERE "); query.Append(whereClause); } // // Render sort clause // if (sortClause != string.Empty) { query.Append(" ORDER BY "); query.Append(sortClause); } // // Render final semicolon // query.Append(";"); OleDbConnection dbConnection = new OleDbConnection(connectionString); OleDbCommand dbCommand = new OleDbCommand(query.ToString(), dbConnection); dbConnection.Open(); #if DEBUG OleDbDataReader r; try { r = dbCommand.ExecuteReader(); } catch (Exception e) { throw(new Exception(e.Message + " --- Query: " + query.ToString())); } #else OleDbDataReader r = dbCommand.ExecuteReader(); #endif GreyFoxUserPreferenceCollection greyFoxUserPreferenceCollection = new GreyFoxUserPreferenceCollection(); while (r.Read()) { GreyFoxUserPreference greyFoxUserPreference = ParseFromReader(r, 0, 1); // Fill User if (userOffset != -1 && !r.IsDBNull(userOffset)) { GreyFoxUserManager.FillFromReader(greyFoxUserPreference.user, r, userOffset, userOffset + 1); // Fill if (userContactOffset != -1 && !r.IsDBNull(userContactOffset)) { GreyFoxContactManager.FillFromReader(greyFoxUserPreference.user.Contact, "sysGlobal_Contacts", r, userContactOffset, userContactOffset + 1); } } greyFoxUserPreferenceCollection.Add(greyFoxUserPreference); } r.Close(); dbConnection.Close(); return(greyFoxUserPreferenceCollection); }
public DojoSeminarCollection GetCollection(int topCount, string whereClause, string sortClause, params DojoSeminarFlags[] optionFlags) { StringBuilder query; Database database; DbCommand dbCommand; IDataReader r; DojoSeminarCollection dojoSeminarCollection; int innerJoinOffset; query = new StringBuilder("SELECT "); if (topCount > 0) { query.Append("TOP "); query.Append(topCount); query.Append(" "); } foreach (string columnName in InnerJoinFields) { query.Append("DojoSeminar."); query.Append(columnName); query.Append(","); } innerJoinOffset = InnerJoinFields.GetUpperBound(0) + 1; int locationOffset = -1; int itemOffset = -1; int itemParentItemOffset = -1; int itemPurchaseVendorOffset = -1; int itemPurchaseAccountOffset = -1; int itemInventoryAccountOffset = -1; int itemTaxOffset = -1; int itemSalesIncomeAccountOffset = -1; // // Append Option Flag Fields // if (optionFlags != null) { for (int x = 0; x < optionFlags.Length; x++) { switch (optionFlags[x]) { case DojoSeminarFlags.Location: for (int i = 0; i <= GreyFoxContactManager.InnerJoinFields.GetUpperBound(0); i++) { query.Append("Location."); query.Append(GreyFoxContactManager.InnerJoinFields[i]); query.Append(","); } locationOffset = innerJoinOffset; innerJoinOffset = locationOffset + GreyFoxContactManager.InnerJoinFields.GetUpperBound(0) + 1; break; case DojoSeminarFlags.Item: for (int i = 0; i <= RHItemManager.InnerJoinFields.GetUpperBound(0); i++) { query.Append("Item."); query.Append(RHItemManager.InnerJoinFields[i]); query.Append(","); } itemOffset = innerJoinOffset; innerJoinOffset = itemOffset + RHItemManager.InnerJoinFields.GetUpperBound(0) + 1; break; case DojoSeminarFlags.ItemParentItem: for (int i = 0; i <= RHItemManager.InnerJoinFields.GetUpperBound(0); i++) { query.Append("Item_ParentItem."); query.Append(RHItemManager.InnerJoinFields[i]); query.Append(","); } itemParentItemOffset = innerJoinOffset; innerJoinOffset = itemParentItemOffset + RHItemManager.InnerJoinFields.GetUpperBound(0) + 1; break; case DojoSeminarFlags.ItemPurchaseVendor: for (int i = 0; i <= RHVendorManager.InnerJoinFields.GetUpperBound(0); i++) { query.Append("Item_PurchaseVendor."); query.Append(RHVendorManager.InnerJoinFields[i]); query.Append(","); } itemPurchaseVendorOffset = innerJoinOffset; innerJoinOffset = itemPurchaseVendorOffset + RHVendorManager.InnerJoinFields.GetUpperBound(0) + 1; break; case DojoSeminarFlags.ItemPurchaseAccount: for (int i = 0; i <= RHAccountManager.InnerJoinFields.GetUpperBound(0); i++) { query.Append("Item_PurchaseAccount."); query.Append(RHAccountManager.InnerJoinFields[i]); query.Append(","); } itemPurchaseAccountOffset = innerJoinOffset; innerJoinOffset = itemPurchaseAccountOffset + RHAccountManager.InnerJoinFields.GetUpperBound(0) + 1; break; case DojoSeminarFlags.ItemInventoryAccount: for (int i = 0; i <= RHAccountManager.InnerJoinFields.GetUpperBound(0); i++) { query.Append("Item_InventoryAccount."); query.Append(RHAccountManager.InnerJoinFields[i]); query.Append(","); } itemInventoryAccountOffset = innerJoinOffset; innerJoinOffset = itemInventoryAccountOffset + RHAccountManager.InnerJoinFields.GetUpperBound(0) + 1; break; case DojoSeminarFlags.ItemTax: for (int i = 0; i <= RHTaxTypeManager.InnerJoinFields.GetUpperBound(0); i++) { query.Append("Item_Tax."); query.Append(RHTaxTypeManager.InnerJoinFields[i]); query.Append(","); } itemTaxOffset = innerJoinOffset; innerJoinOffset = itemTaxOffset + RHTaxTypeManager.InnerJoinFields.GetUpperBound(0) + 1; break; case DojoSeminarFlags.ItemSalesIncomeAccount: for (int i = 0; i <= RHAccountManager.InnerJoinFields.GetUpperBound(0); i++) { query.Append("Item_SalesIncomeAccount."); query.Append(RHAccountManager.InnerJoinFields[i]); query.Append(","); } itemSalesIncomeAccountOffset = innerJoinOffset; innerJoinOffset = itemSalesIncomeAccountOffset + RHAccountManager.InnerJoinFields.GetUpperBound(0) + 1; break; } } } // // Remove trailing comma // query.Length--; if (optionFlags != null) { query.Append(" FROM "); // // Start INNER JOIN expressions // for (int x = 0; x < optionFlags.Length; x++) { query.Append("("); } query.Append("kitTessen_Seminars AS DojoSeminar"); } else { query.Append(" FROM kitTessen_Seminars AS DojoSeminar"); } // // Finish INNER JOIN expressions // if (optionFlags != null) { for (int x = 0; x < optionFlags.Length; x++) { switch (optionFlags[x]) { case DojoSeminarFlags.Location: query.Append(" LEFT JOIN kitTessen_Locations AS Location ON DojoSeminar.LocationID = Location.GreyFoxContactID)"); break; case DojoSeminarFlags.Item: query.Append(" LEFT JOIN RH_Items AS Item ON DojoSeminar.ItemID = Item.RHItemID)"); break; case DojoSeminarFlags.ItemParentItem: query.Append(" LEFT JOIN RH_Items AS Item_ParentItem ON Item.ParentItemID = Item_ParentItem.RHItemID)"); break; case DojoSeminarFlags.ItemPurchaseVendor: query.Append(" LEFT JOIN RH_Vendors AS Item_PurchaseVendor ON Item.PurchaseVendorID = Item_PurchaseVendor.RHVendorID)"); break; case DojoSeminarFlags.ItemPurchaseAccount: query.Append(" LEFT JOIN RH_Accounts AS Item_PurchaseAccount ON Item.PurchaseAccountID = Item_PurchaseAccount.RHAccountID)"); break; case DojoSeminarFlags.ItemInventoryAccount: query.Append(" LEFT JOIN RH_Accounts AS Item_InventoryAccount ON Item.InventoryAccountID = Item_InventoryAccount.RHAccountID)"); break; case DojoSeminarFlags.ItemTax: query.Append(" LEFT JOIN RH_TaxTypes AS Item_Tax ON Item.TaxID = Item_Tax.RHTaxTypeID)"); break; case DojoSeminarFlags.ItemSalesIncomeAccount: query.Append(" LEFT JOIN RH_Accounts AS Item_SalesIncomeAccount ON Item.SalesIncomeAccountID = Item_SalesIncomeAccount.RHAccountID)"); break; } } } // // Render where clause // if (whereClause != string.Empty) { query.Append(" WHERE "); query.Append(whereClause); } // // Render sort clause // if (sortClause != string.Empty) { query.Append(" ORDER BY "); query.Append(sortClause); } // // Render final semicolon // query.Append(";"); database = DatabaseFactory.CreateDatabase(); dbCommand = database.GetSqlStringCommand(query.ToString()); #if DEBUG try { r = database.ExecuteReader(dbCommand); } catch (Exception e) { string msg = e.Message; throw(new Exception(msg + " --- Query: " + query.ToString())); } #else r = database.ExecuteReader(dbCommand); #endif dojoSeminarCollection = new DojoSeminarCollection(); while (r.Read()) { DojoSeminar dojoSeminar = ParseFromReader(r, 0, 1); // Fill Location if (locationOffset != -1 && !r.IsDBNull(locationOffset)) { GreyFoxContactManager.FillFromReader(dojoSeminar.location, "kitTessen_Locations", r, locationOffset, locationOffset + 1); } // Fill Item if (itemOffset != -1 && !r.IsDBNull(itemOffset)) { RHItemManager.FillFromReader(dojoSeminar.item, r, itemOffset, itemOffset + 1); // Fill if (itemParentItemOffset != -1 && !r.IsDBNull(itemParentItemOffset)) { RHItemManager.FillFromReader(dojoSeminar.item.ParentItem, r, itemParentItemOffset, itemParentItemOffset + 1); } // Fill Vendor if (itemPurchaseVendorOffset != -1 && !r.IsDBNull(itemPurchaseVendorOffset)) { RHVendorManager.FillFromReader(dojoSeminar.item.PurchaseVendor, r, itemPurchaseVendorOffset, itemPurchaseVendorOffset + 1); } // Fill Purchase Account if (itemPurchaseAccountOffset != -1 && !r.IsDBNull(itemPurchaseAccountOffset)) { RHAccountManager.FillFromReader(dojoSeminar.item.PurchaseAccount, r, itemPurchaseAccountOffset, itemPurchaseAccountOffset + 1); } // Fill Asset Account if (itemInventoryAccountOffset != -1 && !r.IsDBNull(itemInventoryAccountOffset)) { RHAccountManager.FillFromReader(dojoSeminar.item.InventoryAccount, r, itemInventoryAccountOffset, itemInventoryAccountOffset + 1); } // Fill if (itemTaxOffset != -1 && !r.IsDBNull(itemTaxOffset)) { RHTaxTypeManager.FillFromReader(dojoSeminar.item.Tax, r, itemTaxOffset, itemTaxOffset + 1); } // Fill if (itemSalesIncomeAccountOffset != -1 && !r.IsDBNull(itemSalesIncomeAccountOffset)) { RHAccountManager.FillFromReader(dojoSeminar.item.SalesIncomeAccount, r, itemSalesIncomeAccountOffset, itemSalesIncomeAccountOffset + 1); } } dojoSeminarCollection.Add(dojoSeminar); } // Microsoft DAAB still needs to close readers. r.Close(); return(dojoSeminarCollection); }