/// <summary> /// GetAllUserRegistration method implementation /// </summary> public MMCRegistrationList GetAllUserRegistrations(UsersOrderObject order, int maxrows = 20000, bool enabledonly = false) { string request = string.Empty; if (enabledonly) { request = "SELECT TOP " + maxrows.ToString() + " ID, UPN, MAILADDRESS, PHONENUMBER, ENABLED, CREATIONDATE, METHOD FROM REGISTRATIONS WHERE ENABLED=1"; } else { request = "SELECT TOP " + maxrows.ToString() + " ID, UPN, MAILADDRESS, PHONENUMBER, ENABLED, CREATIONDATE, METHOD FROM REGISTRATIONS"; } switch (order.Column) { case UsersOrderField.UserName: request += " ORDER BY UPN"; break; case UsersOrderField.Email: request += " ORDER BY MAILADDRESS"; break; case UsersOrderField.Phone: request += " ORDER BY PHONENUMBER"; break; case UsersOrderField.CreationDate: request += " ORDER BY ID"; break; default: request += " ORDER BY CREATIONDATE"; break; } if (order.Direction == SortDirection.Descending) { request += " DESC"; } SqlConnection con = new SqlConnection(_connectionstring); SqlCommand sql = new SqlCommand(request, con); MMCRegistrationList regs = new MMCRegistrationList(); con.Open(); try { SqlDataReader rd = sql.ExecuteReader(); while (rd.Read()) { MMCRegistration reg = new MMCRegistration(); reg.ID = rd.GetInt64(0).ToString(); reg.UPN = rd.GetString(1); if (!rd.IsDBNull(2)) { reg.MailAddress = rd.GetString(2); } if (!rd.IsDBNull(3)) { reg.PhoneNumber = rd.GetString(3); } reg.Enabled = rd.GetBoolean(4); reg.CreationDate = rd.GetDateTime(5); reg.PreferredMethod = (RegistrationPreferredMethod)rd.GetInt32(6); reg.IsRegistered = true; regs.Add(reg); } return(regs); } catch (Exception ex) { Log.WriteEntry(ex.Message, System.Diagnostics.EventLogEntryType.Error, 5000); throw new Exception(ex.Message); } finally { con.Close(); } }
/// <summary> /// GetUserRegistration method implementation /// </summary> public MMCRegistrationList GetUserRegistrations(UsersFilterObject filter, UsersOrderObject order, UsersPagingObject paging, int maxrows = 20000) { Dictionary <int, string> fliedlsvalues = new Dictionary <int, string> { { 0, " UPN " }, { 1, " MAILADDRESS " }, { 2, " PHONENUMBER " } }; Dictionary <int, string> operatorsvalues = new Dictionary <int, string> { { 0, " = @FILTERVALUE " }, { 1, " LIKE @FILTERVALUE +'%' " }, { 2, " LIKE '%'+ @FILTERVALUE +'%' " }, { 3, " <> @FILTERVALUE " }, { 4, " LIKE '%'+ @FILTERVALUE " }, { 5, " NOT LIKE '%' + @FILTERVALUE +'%' " } }; Dictionary <int, string> nulloperatorsvalues = new Dictionary <int, string> { { 0, " IS NULL " }, { 1, " IS NULL " }, { 2, " IS NULL " }, { 3, " IS NOT NULL " }, { 4, " IS NOT NULL " }, { 5, " IS NOT NULL " } }; Dictionary <int, string> methodvalues = new Dictionary <int, string> { { 0, " METHOD = 0 " }, { 1, " METHOD = 1 " }, { 2, " METHOD = 2 " }, { 3, " METHOD = 3 " } }; string request = string.Empty; switch (order.Column) { case UsersOrderField.UserName: if (order.Direction == SortDirection.Ascending) { request = "SELECT ROW_NUMBER() OVER(ORDER BY UPN) AS NUMBER, ID, UPN, MAILADDRESS, PHONENUMBER, ENABLED, CREATIONDATE, METHOD FROM REGISTRATIONS"; } else { request = "SELECT ROW_NUMBER() OVER(ORDER BY UPN DESC) AS NUMBER, ID, UPN, MAILADDRESS, PHONENUMBER, ENABLED, CREATIONDATE, METHOD FROM REGISTRATIONS"; } break; case UsersOrderField.Email: if (order.Direction == SortDirection.Ascending) { request = "SELECT ROW_NUMBER() OVER(ORDER BY MAILADDRESS) AS NUMBER, ID, UPN, MAILADDRESS, PHONENUMBER, ENABLED, CREATIONDATE, METHOD FROM REGISTRATIONS"; } else { request = "SELECT ROW_NUMBER() OVER(ORDER BY MAILADDRESS DESC) AS NUMBER, ID, UPN, MAILADDRESS, PHONENUMBER, ENABLED, CREATIONDATE, METHOD FROM REGISTRATIONS"; } break; case UsersOrderField.Phone: if (order.Direction == SortDirection.Ascending) { request = "SELECT ROW_NUMBER() OVER(ORDER BY PHONENUMBER) AS NUMBER, ID, UPN, MAILADDRESS, PHONENUMBER, ENABLED, CREATIONDATE, METHOD FROM REGISTRATIONS"; } else { request = "SELECT ROW_NUMBER() OVER(ORDER BY PHONENUMBER DESC) AS NUMBER, ID, UPN, MAILADDRESS, PHONENUMBER, ENABLED, CREATIONDATE, METHOD FROM REGISTRATIONS"; } break; case UsersOrderField.CreationDate: if (order.Direction == SortDirection.Ascending) { request = "SELECT ROW_NUMBER() OVER(ORDER BY CREATIONDATE) AS NUMBER, ID, UPN, MAILADDRESS, PHONENUMBER, ENABLED, CREATIONDATE, METHOD FROM REGISTRATIONS"; } else { request = "SELECT ROW_NUMBER() OVER(ORDER BY CREATIONDATE DESC) AS NUMBER, ID, UPN, MAILADDRESS, PHONENUMBER, ENABLED, CREATIONDATE, METHOD FROM REGISTRATIONS"; } break; default: if (order.Direction == SortDirection.Ascending) { request = "SELECT ROW_NUMBER() OVER(ORDER BY ID) AS NUMBER, ID, UPN, MAILADDRESS, PHONENUMBER, ENABLED, CREATIONDATE, METHOD FROM REGISTRATIONS"; } else { request = "SELECT ROW_NUMBER() OVER(ORDER BY ID DESC) AS NUMBER, ID, UPN, MAILADDRESS, PHONENUMBER, ENABLED, CREATIONDATE, METHOD FROM REGISTRATIONS"; } break; } bool hasparameter = (string.Empty != filter.FilterValue); bool hasmethod = false; if (filter.FilterisActive) { if (hasparameter) { request += " WHERE"; string strfields = string.Empty; string stroperator = string.Empty; fliedlsvalues.TryGetValue((int)filter.FilterField, out strfields); request += strfields; if (filter.FilterValue != null) { operatorsvalues.TryGetValue((int)filter.FilterOperator, out stroperator); } else { nulloperatorsvalues.TryGetValue((int)filter.FilterOperator, out stroperator); } request += stroperator; } if (filter.FilterMethod != UsersPreferredMethod.None) { string strmethod = string.Empty; methodvalues.TryGetValue((int)filter.FilterMethod, out strmethod); if (hasparameter) { request += " AND " + strmethod; } else { request += " WHERE " + strmethod; } hasmethod = true; } if (filter.EnabledOnly) { if ((hasparameter) || (hasmethod)) { request += " AND ENABLED=1"; } else { request += " WHERE ENABLED=1"; } } } if (paging.isActive) { request = "SELECT TOP " + maxrows.ToString() + " NUMBER, ID, UPN, MAILADDRESS, PHONENUMBER, ENABLED, CREATIONDATE, METHOD FROM (" + request; request += ") AS TBL WHERE NUMBER BETWEEN " + ((paging.CurrentPage - 1) * paging.PageSize + 1) + " AND " + (paging.CurrentPage) * paging.PageSize; } SqlConnection con = new SqlConnection(_connectionstring); SqlCommand sql = new SqlCommand(request, con); if ((hasparameter) && (filter.FilterValue != null)) { SqlParameter prm = new SqlParameter("@FILTERVALUE", SqlDbType.VarChar); sql.Parameters.Add(prm); prm.Value = filter.FilterValue; } MMCRegistrationList regs = new MMCRegistrationList(); con.Open(); try { int i = 0; SqlDataReader rd = sql.ExecuteReader(); while (rd.Read()) { MMCRegistration reg = new MMCRegistration(); reg.ID = rd.GetInt64(1).ToString(); reg.UPN = rd.GetString(2); if (!rd.IsDBNull(3)) { reg.MailAddress = rd.GetString(3); } if (!rd.IsDBNull(4)) { reg.PhoneNumber = rd.GetString(4); } reg.Enabled = rd.GetBoolean(5); reg.CreationDate = rd.GetDateTime(6); reg.PreferredMethod = (RegistrationPreferredMethod)rd.GetInt32(7); reg.IsRegistered = true; regs.Add(reg); i++; } return(regs); } catch (Exception ex) { Log.WriteEntry(ex.Message, System.Diagnostics.EventLogEntryType.Error, 5000); throw new Exception(ex.Message); } finally { con.Close(); } }