/// <summary> /// Build the sql statement including its filters to avoid using the .NET filtering. This improves performance greatly. /// </summary> /// <param name="limit">Return the specified number of rows. E.g. "TOP 1"</param> /// <param name="parentID">Organization Id.</param> /// <param name="orderBy">Fields in sql format to sort the results by. E.g. "LastName, FirstName"</param> /// <param name="filters">Filters to be applied. Specified in the URL request.</param> /// <param name="filterParameters">SqlParamenterCollection for the input parameters of the sql query.</param> /// <returns>A string with the full sql statement.</returns> public string BuildLoadByParentOrganizationIdSql(string limit, int organizationParentId, string orderBy, NameValueCollection filters, SqlParameterCollection filterParameters, bool isCustomer = false, bool useMaxDop = false) { StringBuilder result = new StringBuilder(); //check if it has the PhoneNumber filter by itself and also if it has a square bracket for the contains, lt, gt,... extra operators bool hasPhoneNumberFilter = filters.AllKeys.Where(p => p.ToLower() == "phonenumber").Any() || filters.AllKeys.Where(p => p.ToLower().Contains("phonenumber[")).Any(); //If phoneNumber filter is used then SELECT needs to be DISTINCT to avoid identity exceptions on the DataTable when a contact has multiple phonenumbers (one-to-many relationship between ContactsView and PhoneNumbers) if (hasPhoneNumberFilter) { result.Append("SELECT DISTINCT " + limit + " ContactsView.* "); } else { result.Append("SELECT " + limit + " * "); } result.Append("FROM ContactsView "); if (hasPhoneNumberFilter) { result.Append("LEFT JOIN PhoneNumbers ON ContactsView.UserID = PhoneNumbers.RefID "); } result.Append("WHERE " + (isCustomer ? "OrganizationID" : "OrganizationParentID") + " = @OrganizationParentId AND (MarkDeleted = 0) "); result.Append(DataUtils.BuildWhereClausesFromFilters(this.LoginUser, this, organizationParentId, filters, ReferenceType.Contacts, "UserID", null, ref filterParameters) + " "); result.Append("ORDER BY " + orderBy); if (useMaxDop) { result.Append(" OPTION (MAXDOP 1);"); } return(result.ToString()); }
public void LoadByOrganizationID(int organizationId, NameValueCollection filters, int?PageNumber = null, int?PageSize = null) { //Get the column names LoadColumns("DeletedTickets"); string orderBy = "ORDER BY DateDeleted DESC"; string sql = string.Empty; using (SqlCommand command = new SqlCommand()) { SqlParameterCollection filterParameters = command.Parameters; string whereClause = DataUtils.BuildWhereClausesFromFilters(this.LoginUser, this, organizationId, filters, ReferenceType.DeletedTickets, "ID", null, ref filterParameters); sql = @" SELECT * FROM DeletedTickets WHERE OrganizationID = @organizationId " + whereClause + " " + orderBy; sql = DataUtils.AddPaging(sql, PageSize, PageNumber, command); command.CommandText = sql; command.CommandType = CommandType.Text; command.Parameters.AddWithValue("@organizationId", organizationId); Fill(command); } }
/// <summary> /// Build the sql statement including its filters to avoid using the .NET filtering. This improves performance greatly. /// </summary> /// <param name="limit">Return the specified number of rows. E.g. "TOP 1"</param> /// <param name="parentID">Organization Id.</param> /// <param name="orderBy">Fields in sql format to sort the results by. E.g. "LastName, FirstName"</param> /// <param name="filters">Filters to be applied. Specified in the URL request.</param> /// <param name="filterParameters">SqlParamenterCollection for the input parameters of the sql query.</param> /// <returns>A string with the full sql statement.</returns> public string BuildLoadByParentOrganizationIdSql(string limit, int organizationParentId, string orderBy, NameValueCollection filters, SqlParameterCollection filterParameters) { StringBuilder result = new StringBuilder(); result.Append("SELECT " + limit + " * "); result.Append("FROM AssetsView "); result.Append("WHERE OrganizationID = @OrganizationId "); result.Append(DataUtils.BuildWhereClausesFromFilters(this.LoginUser, this, organizationParentId, filters, ReferenceType.Assets, "AssetID", null, ref filterParameters) + " "); result.Append("ORDER BY " + orderBy); return(result.ToString()); }
private string BuildLoadByParentIDSql(string limit, int parentID, string orderBy, NameValueCollection filters, SqlParameterCollection filterParameters) { StringBuilder whereClauses = new StringBuilder(); StringBuilder result = new StringBuilder(); result.Append("SELECT " + limit + " * "); result.Append("FROM OrganizationsView "); result.Append("WHERE ParentID = @ParentID " + DataUtils.BuildWhereClausesFromFilters(this.LoginUser, this, parentID, filters, ReferenceType.Organizations, "OrganizationID", null, ref filterParameters) + " "); result.Append("ORDER BY " + orderBy); filterParameters.AddWithValue("@ParentID", parentID); return(result.ToString()); }