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