/// <summary>
/// DisableUserRegistration method implementation
/// </summary>
public override Registration DisableUserRegistration(Registration reg)
{
if (!SQLUtils.HasRegistration(_host, reg.UPN))
{
throw new Exception("The user " + reg.UPN + " cannot be updated ! \r User not found !");
}
string request = "UPDATE REGISTRATIONS SET ENABLED=0 WHERE UPN=@UPN";
SqlConnection con = new SqlConnection(_connectionstring);
SqlCommand sql = new SqlCommand(request, con);
SqlParameter prm5 = new SqlParameter("@UPN", SqlDbType.VarChar);
sql.Parameters.Add(prm5);
prm5.Value = reg.UPN;
con.Open();
try
{
int res = sql.ExecuteNonQuery();
}
catch (Exception ex)
{
DataLog.WriteEntry(ex.Message, System.Diagnostics.EventLogEntryType.Error, 5000);
throw new Exception(ex.Message);
}
finally
{
con.Close();
}
reg.Enabled = false;
return(reg);
}
/// <summary>
/// IsBinaryAttribute method implmentation
/// </summary>
internal static bool IsBinaryAttribute(string domainname, string username, string password, string attributename)
{
try
{
using (Domain domain = ADDSUtils.GetRootDomain(domainname, username, password))
{
using (Forest forest = ADDSUtils.GetForest(domain.Name, username, password))
{
ActiveDirectorySchemaProperty property = forest.Schema.FindProperty(attributename);
if (property != null)
{
if (property.Syntax.Equals(ActiveDirectorySyntax.OctetString))
{
return(true);
}
}
}
}
return(false);
}
catch (Exception ex)
{
DataLog.WriteEntry(ex.Message, System.Diagnostics.EventLogEntryType.Error, 5100);
return(false);
}
}
/// <summary>
/// GetMFAdistinguishedName method implementation
/// </summary>
private string GetMFAdistinguishedName(string upn)
{
string ret = string.Empty;
try
{
using (DirectoryEntry rootdir = ADDSUtils.GetDirectoryEntryForUPN(_host, _host.Account, _host.Password, upn))
{
string qryldap = "(&(objectCategory=user)(objectClass=user)(userprincipalname=" + upn + ")(!(userAccountControl:1.2.840.113556.1.4.803:=2)))";
using (DirectorySearcher dsusr = new DirectorySearcher(rootdir, qryldap))
{
dsusr.PropertiesToLoad.Add("objectGUID");
dsusr.PropertiesToLoad.Add("userPrincipalName");
dsusr.PropertiesToLoad.Add("whenCreated");
dsusr.PropertiesToLoad.Add("distinguishedName");
SearchResult sr = dsusr.FindOne();
if (sr != null)
{
using (DirectoryEntry DirEntry = ADDSUtils.GetDirectoryEntry(_host, sr))
{
ret = DirEntry.Properties["distinguishedName"].Value.ToString();
};
}
}
}
}
catch (Exception ex)
{
DataLog.WriteEntry(ex.Message, System.Diagnostics.EventLogEntryType.Error, 5000);
throw new Exception(ex.Message);
}
return(ret);
}
/// <summary>
/// DeleteUserRegistration method implementation
/// </summary>
public override bool DeleteUserRegistration(Registration reg, bool dropkey = true)
{
if (!SQLUtils.HasRegistration(_host, reg.UPN))
{
throw new Exception("The user " + reg.UPN + " cannot be deleted ! \r User not found !");
}
if (dropkey)
{
this.OnKeyDataEvent(reg.UPN, KeysDataManagerEventKind.Remove);
}
string request = "DELETE FROM REGISTRATIONS WHERE UPN=@UPN";
SqlConnection con = new SqlConnection(_connectionstring);
SqlCommand sql = new SqlCommand(request, con);
SqlParameter prm = new SqlParameter("@UPN", SqlDbType.VarChar);
sql.Parameters.Add(prm);
prm.Value = reg.UPN;
con.Open();
try
{
int res = sql.ExecuteNonQuery();
}
catch (Exception ex)
{
DataLog.WriteEntry(ex.Message, System.Diagnostics.EventLogEntryType.Error, 5000);
throw new Exception(ex.Message);
}
finally
{
con.Close();
}
string request2 = "DELETE FROM NOTIFICATIONS WHERE REGISTRATIONID=@REGISTRATIONID";
SqlCommand sql2 = new SqlCommand(request2, con);
SqlParameter prm2 = new SqlParameter("@REGISTRATIONID", SqlDbType.BigInt);
sql2.Parameters.Add(prm2);
prm2.Value = reg.ID;
con.Open();
try
{
int res = sql2.ExecuteNonQuery();
}
catch (Exception ex)
{
DataLog.WriteEntry(ex.Message, System.Diagnostics.EventLogEntryType.Error, 5000);
throw new Exception(ex.Message);
}
finally
{
con.Close();
}
return(true);
}
/// <summary>
/// DoUpdateUserKey method implmentation
/// </summary>
private string DoInsertUserKey(string upn, string secretkey)
{
string request = "INSERT INTO REGISTRATIONS (UPN, SECRETKEY, METHOD, OVERRIDE, PIN, ENABLED) VALUES (@UPN, @SECRETKEY, 0, null, 0, 1)";
SqlConnection con = new SqlConnection(_connectionstring);
SqlCommand sql = new SqlCommand(request, con);
SqlParameter prm1 = new SqlParameter("@SECRETKEY", SqlDbType.VarChar);
sql.Parameters.Add(prm1);
prm1.Value = secretkey;
SqlParameter prm2 = new SqlParameter("@UPN", SqlDbType.VarChar);
sql.Parameters.Add(prm2);
prm2.Value = upn;
con.Open();
try
{
int res = sql.ExecuteNonQuery();
}
catch (Exception ex)
{
DataLog.WriteEntry(ex.Message, System.Diagnostics.EventLogEntryType.Error, 5000);
throw new Exception(ex.Message);
}
finally
{
con.Close();
}
return(secretkey);
}
/// <summary>
/// HasRegistration method implementation
/// </summary>
internal static bool HasRegistration(SQLServerHost host, string upn)
{
string request = "SELECT ID, UPN FROM REGISTRATIONS WHERE UPN=@UPN";
SqlConnection con = new SqlConnection(host.ConnectionString);
SqlCommand sql = new SqlCommand(request, con);
SqlParameter prm = new SqlParameter("@UPN", SqlDbType.VarChar);
sql.Parameters.Add(prm);
prm.Value = upn;
con.Open();
try
{
SqlDataReader rd = sql.ExecuteReader();
return(rd.Read());
}
catch (Exception ex)
{
DataLog.WriteEntry(ex.Message, System.Diagnostics.EventLogEntryType.Error, 5000);
throw new Exception(ex.Message);
}
finally
{
con.Close();
}
}
/// <summary>
/// RemoveUserKey method implmentation
/// </summary>
public override bool RemoveUserKey(string upn)
{
string request = "UPDATE REGISTRATIONS SET SECRETKEY = NULL WHERE UPN=@UPN";
SqlConnection con = new SqlConnection(_connectionstring);
SqlCommand sql = new SqlCommand(request, con);
SqlParameter prm2 = new SqlParameter("@UPN", SqlDbType.VarChar, 8000);
sql.Parameters.Add(prm2);
prm2.Value = upn.ToLower();
con.Open();
try
{
int res = sql.ExecuteNonQuery();
return(res == 1);
}
catch (Exception ex)
{
DataLog.WriteEntry(ex.Message, System.Diagnostics.EventLogEntryType.Error, 5000);
throw new Exception(ex.Message);
}
finally
{
con.Close();
}
}
/// <summary>
/// DoUpdateUserKey method implmentation
/// </summary>
private string DoUpdateUserKey(string upn, string secretkey)
{
string request = "UPDATE REGISTRATIONS SET SECRETKEY = @SECRETKEY WHERE UPN=@UPN";
SqlConnection con = new SqlConnection(_connectionstring);
SqlCommand sql = new SqlCommand(request, con);
SqlParameter prm1 = new SqlParameter("@SECRETKEY", SqlDbType.VarChar);
sql.Parameters.Add(prm1);
prm1.Value = secretkey;
SqlParameter prm2 = new SqlParameter("@UPN", SqlDbType.VarChar);
sql.Parameters.Add(prm2);
prm2.Value = upn.ToLower();
con.Open();
try
{
int res = sql.ExecuteNonQuery();
}
catch (Exception ex)
{
DataLog.WriteEntry(ex.Message, System.Diagnostics.EventLogEntryType.Error, 5000);
throw new Exception(ex.Message);
}
finally
{
con.Close();
}
return(secretkey);
}
/// <summary>
/// HasNotification method implementation
/// </summary>
internal static bool HasNotification(SQLServerHost host, string regid)
{
string request = "SELECT REGISTRATIONID FROM NOTIFICATIONS WHERE REGISTRATIONID=@REGISTRATIONID";
SqlConnection con = new SqlConnection(host.ConnectionString);
SqlCommand sql = new SqlCommand(request, con);
SqlParameter prm = new SqlParameter("@REGISTRATIONID", SqlDbType.BigInt);
sql.Parameters.Add(prm);
prm.Value = regid;
con.Open();
try
{
SqlDataReader rd = sql.ExecuteReader();
return(rd.Read());
}
catch (Exception ex)
{
DataLog.WriteEntry(ex.Message, System.Diagnostics.EventLogEntryType.Error, 5000);
throw new Exception(ex.Message);
}
finally
{
con.Close();
}
}
/// <summary>
/// GetUserRegistration method implementation
/// </summary>
public override Registration GetUserRegistration(string upn)
{
string request = "SELECT ID, UPN, MAILADDRESS, PHONENUMBER, PIN, ENABLED, METHOD, OVERRIDE FROM REGISTRATIONS WHERE UPN=@UPN";
SqlConnection con = new SqlConnection(_connectionstring);
SqlCommand sql = new SqlCommand(request, con);
SqlParameter prm = new SqlParameter("@UPN", SqlDbType.VarChar);
sql.Parameters.Add(prm);
prm.Value = upn;
Registration reg = new Registration();
con.Open();
try
{
SqlDataReader rd = sql.ExecuteReader();
if (rd.Read())
{
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.PIN = rd.GetInt32(4);
reg.Enabled = rd.GetBoolean(5);
reg.PreferredMethod = (PreferredMethod)rd.GetInt32(6);
if (!rd.IsDBNull(7))
{
reg.OverrideMethod = rd.GetString(7);
}
else
{
reg.OverrideMethod = string.Empty;
}
reg.IsRegistered = true;
return(reg);
}
else
{
return(null);
}
}
catch (Exception ex)
{
DataLog.WriteEntry(ex.Message, System.Diagnostics.EventLogEntryType.Error, 5000);
throw new Exception(ex.Message);
}
finally
{
con.Close();
}
}
/// <summary>
/// LoadForests method implementation
/// </summary>
public static void LoadForests(string domainname, string account, string password, bool usessl, bool reset = false)
{
if (reset)
{
ResetForests();
}
if (_isbinded)
{
return;
}
try
{
_usessl = usessl;
using (Domain domain = ADDSUtils.GetRootDomain(domainname, account, password))
{
using (Forest forest = ADDSUtils.GetForest(domain.Name, account, password))
{
Forests.Clear();
ADDSHostForest root = new ADDSHostForest
{
IsRoot = true,
ForestDNS = forest.Name
};
Forests.Add(root);
foreach (ForestTrustRelationshipInformation trusts in forest.GetAllTrustRelationships())
{
ADDSHostForest sub = new ADDSHostForest
{
IsRoot = false,
ForestDNS = trusts.TargetName
};
foreach (TopLevelName t in trusts.TopLevelNames)
{
if (t.Status == TopLevelNameStatus.Enabled)
{
sub.TopLevelNames.Add(t.Name);
}
}
Forests.Add(sub);
}
}
}
_isbinded = true;
}
catch (Exception ex)
{
DataLog.WriteEntry(ex.Message, System.Diagnostics.EventLogEntryType.Error, 5100);
_isbinded = false;
}
}
/// <summary>
/// UpdateNotification method implementation
/// </summary>
private bool DoUpdateNotification(Notification Notification)
{
string request = "UPDATE NOTIFICATIONS SET OTP=@OTP, CREATIONDATE = @CREATEDATE, VALIDITYDATE=@VALIDITYDATE, CHECKDATE = NULL WHERE REGISTRATIONID=@REGISTRATIONID";
SqlConnection con = new SqlConnection(_connectionstring);
SqlCommand sql = new SqlCommand(request, con);
SqlParameter prmid = new SqlParameter("@REGISTRATIONID", SqlDbType.BigInt);
sql.Parameters.Add(prmid);
prmid.Value = Notification.RegistrationID;
SqlParameter prmotp = new SqlParameter("@OTP", SqlDbType.Int);
sql.Parameters.Add(prmotp);
prmotp.Value = Notification.OTP;
SqlParameter prmdat = new SqlParameter("@CREATEDATE", SqlDbType.DateTime);
sql.Parameters.Add(prmdat);
prmdat.Value = Notification.CreationDate;
SqlParameter prmval = new SqlParameter("@VALIDITYDATE", SqlDbType.DateTime);
sql.Parameters.Add(prmval);
prmval.Value = Notification.ValidityDate;
con.Open();
try
{
int res = sql.ExecuteNonQuery();
return(res == 1);
}
catch (Exception ex)
{
DataLog.WriteEntry(ex.Message, System.Diagnostics.EventLogEntryType.Error, 5000);
throw new Exception(ex.Message);
}
finally
{
con.Close();
}
}
/// <summary>
/// GetUserKey method implmentation
/// </summary>
public override string GetUserKey(string upn)
{
string request = "SELECT SECRETKEY FROM REGISTRATIONS WHERE UPN=@UPN";
SqlConnection con = new SqlConnection(_connectionstring);
SqlCommand sql = new SqlCommand(request, con);
SqlParameter prm = new SqlParameter("@UPN", SqlDbType.VarChar);
sql.Parameters.Add(prm);
prm.Value = upn;
string ret = string.Empty;
con.Open();
try
{
SqlDataReader rd = sql.ExecuteReader();
if (rd.Read())
{
if (!rd.IsDBNull(0))
{
ret = rd.GetString(0);
}
return(ret);
}
else
{
return(null);
}
}
catch (Exception ex)
{
DataLog.WriteEntry(ex.Message, System.Diagnostics.EventLogEntryType.Error, 5000);
throw new Exception(ex.Message);
}
finally
{
con.Close();
}
}
/// <summary>
/// GetNetBiosName method
/// </summary>
private static string GetNetBiosName(ADDSHost host, string username)
{
try
{
using (DirectoryEntry rootdir = ADDSUtils.GetDirectoryEntryForUser(host, host.Account, host.Password, username))
{
string qryldap = "(&(objectCategory=user)(objectClass=user)(userPrincipalName=" + username + ")(!(userAccountControl:1.2.840.113556.1.4.803:=2)))";
using (DirectorySearcher dsusr = new DirectorySearcher(rootdir, qryldap))
{
dsusr.PropertiesToLoad.Clear();
dsusr.PropertiesToLoad.Add("objectGUID");
dsusr.PropertiesToLoad.Add("msDS-PrincipalName");
dsusr.ReferralChasing = ReferralChasingOption.All;
SearchResult sr = dsusr.FindOne();
if (sr != null)
{
using (DirectoryEntry DirEntry = ADDSUtils.GetDirectoryEntry(host, sr))
{
if (DirEntry.Properties["objectGUID"].Value != null)
{
return(sr.Properties["msDS-PrincipalName"][0].ToString());
}
else
{
return(null);
}
};
}
}
}
}
catch (Exception ex)
{
DataLog.WriteEntry(ex.Message, System.Diagnostics.EventLogEntryType.Error, 5000);
throw new Exception(ex.Message);
}
return(null);
}
/// <summary>
/// GetUserRegistrations method implementation
/// </summary>
public override RegistrationList GetUserRegistrations(DataFilterObject filter, DataOrderObject order, DataPagingObject 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 DataOrderField.UserName:
if (order.Direction == SortDirection.Ascending)
{
request = "SELECT ROW_NUMBER() OVER(ORDER BY UPN) AS NUMBER, ID, UPN, MAILADDRESS, PHONENUMBER, PIN, ENABLED, METHOD, OVERRIDE FROM REGISTRATIONS";
}
else
{
request = "SELECT ROW_NUMBER() OVER(ORDER BY UPN DESC) AS NUMBER, ID, UPN, MAILADDRESS, PHONENUMBER, PIN, ENABLED, METHOD, OVERRIDE FROM REGISTRATIONS";
}
break;
case DataOrderField.Email:
if (order.Direction == SortDirection.Ascending)
{
request = "SELECT ROW_NUMBER() OVER(ORDER BY MAILADDRESS) AS NUMBER, ID, UPN, MAILADDRESS, PHONENUMBER, PIN, ENABLED, METHOD, OVERRIDE FROM REGISTRATIONS";
}
else
{
request = "SELECT ROW_NUMBER() OVER(ORDER BY MAILADDRESS DESC) AS NUMBER, ID, UPN, MAILADDRESS, PHONENUMBER, PIN, ENABLED, METHOD, OVERRIDE FROM REGISTRATIONS";
}
break;
case DataOrderField.Phone:
if (order.Direction == SortDirection.Ascending)
{
request = "SELECT ROW_NUMBER() OVER(ORDER BY PHONENUMBER) AS NUMBER, ID, UPN, MAILADDRESS, PHONENUMBER, PIN, ENABLED, METHOD, OVERRIDE FROM REGISTRATIONS";
}
else
{
request = "SELECT ROW_NUMBER() OVER(ORDER BY PHONENUMBER DESC) AS NUMBER, ID, UPN, MAILADDRESS, PHONENUMBER, PIN, ENABLED, METHOD, OVERRIDE FROM REGISTRATIONS";
}
break;
default:
if (order.Direction == SortDirection.Ascending)
{
request = "SELECT ROW_NUMBER() OVER(ORDER BY ID) AS NUMBER, ID, UPN, MAILADDRESS, PHONENUMBER, PIN, ENABLED, METHOD, OVERRIDE FROM REGISTRATIONS";
}
else
{
request = "SELECT ROW_NUMBER() OVER(ORDER BY ID DESC) AS NUMBER, ID, UPN, MAILADDRESS, PHONENUMBER, PIN, ENABLED, METHOD, OVERRIDE 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 != PreferredMethod.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, PIN, ENABLED, METHOD, OVERRIDE 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;
}
RegistrationList regs = new RegistrationList();
con.Open();
try
{
int i = 0;
SqlDataReader rd = sql.ExecuteReader();
while (rd.Read())
{
Registration reg = new Registration();
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.PIN = rd.GetInt32(5);
reg.Enabled = rd.GetBoolean(6);
reg.PreferredMethod = (PreferredMethod)rd.GetInt32(7);
reg.OverrideMethod = rd.GetString(8);
reg.IsRegistered = true;
regs.Add(reg);
i++;
}
return(regs);
}
catch (Exception ex)
{
DataLog.WriteEntry(ex.Message, System.Diagnostics.EventLogEntryType.Error, 5000);
throw new Exception(ex.Message);
}
finally
{
con.Close();
}
}
/// <summary>
/// SetUserRegistration method implementation
/// </summary>
public override Registration SetUserRegistration(Registration reg, bool resetkey = false, bool caninsert = true, bool disableoninsert = false)
{
if (!SQLUtils.HasRegistration(_host, reg.UPN))
{
if (caninsert)
{
return(AddUserRegistration(reg, resetkey, false));
}
else
{
return(GetUserRegistration(reg.UPN));
}
}
string request;
if (disableoninsert)
{
request = "UPDATE REGISTRATIONS SET MAILADDRESS = @MAILADDRESS, PHONENUMBER = @PHONENUMBER, PIN=@PIN, METHOD=@METHOD, OVERRIDE=@OVERRIDE, WHERE UPN=@UPN";
}
else
{
request = "UPDATE REGISTRATIONS SET MAILADDRESS = @MAILADDRESS, PHONENUMBER = @PHONENUMBER, PIN=@PIN, METHOD=@METHOD, OVERRIDE=@OVERRIDE, ENABLED=@ENABLED WHERE UPN=@UPN";
}
SqlConnection con = new SqlConnection(_connectionstring);
SqlCommand sql = new SqlCommand(request, con);
SqlParameter prm1 = new SqlParameter("@MAILADDRESS", SqlDbType.VarChar, 256);
sql.Parameters.Add(prm1);
if (string.IsNullOrEmpty(reg.MailAddress))
{
prm1.Value = DBNull.Value;
}
else
{
prm1.Value = reg.MailAddress;
}
SqlParameter prm2 = new SqlParameter("@PHONENUMBER", SqlDbType.VarChar, 50);
sql.Parameters.Add(prm2);
if (string.IsNullOrEmpty(reg.PhoneNumber))
{
prm2.Value = DBNull.Value;
}
else
{
prm2.Value = reg.PhoneNumber;
}
SqlParameter prm3 = new SqlParameter("@PIN", SqlDbType.Int);
sql.Parameters.Add(prm3);
prm3.Value = reg.PIN;
SqlParameter prm4 = new SqlParameter("@METHOD", SqlDbType.Int);
sql.Parameters.Add(prm4);
prm4.Value = reg.PreferredMethod;
SqlParameter prm5 = new SqlParameter("@OVERRIDE", SqlDbType.VarChar);
sql.Parameters.Add(prm5);
if (reg.OverrideMethod == null)
{
prm5.Value = string.Empty;
}
else
{
prm5.Value = reg.OverrideMethod;
}
if (!disableoninsert)
{
SqlParameter prm6 = new SqlParameter("@ENABLED", SqlDbType.Bit);
sql.Parameters.Add(prm6);
prm6.Value = reg.Enabled;
}
SqlParameter prm7 = new SqlParameter("@UPN", SqlDbType.VarChar);
sql.Parameters.Add(prm7);
prm7.Value = reg.UPN;
con.Open();
try
{
int res = sql.ExecuteNonQuery();
if (resetkey)
{
this.OnKeyDataEvent(reg.UPN, KeysDataManagerEventKind.add);
}
}
catch (Exception ex)
{
DataLog.WriteEntry(ex.Message, System.Diagnostics.EventLogEntryType.Error, 5000);
throw new Exception(ex.Message);
}
finally
{
con.Close();
}
return(GetUserRegistration(reg.UPN));
}
/// <summary>
/// AddUserRegistration method implementation
/// </summary>
public override Registration AddUserRegistration(Registration reg, bool newkey = true)
{
if (SQLUtils.HasRegistration(_host, reg.UPN))
{
return(SetUserRegistration(reg, newkey));
}
string request = "INSERT INTO REGISTRATIONS (UPN, MAILADDRESS, PHONENUMBER, PIN, ENABLED, METHOD, OVERRIDE) VALUES (@UPN, @MAILADDRESS, @PHONENUMBER, @PIN, @ENABLED, @METHOD, @OVERRIDE)";
SqlConnection con = new SqlConnection(_connectionstring);
SqlCommand sql = new SqlCommand(request, con);
SqlParameter prm1 = new SqlParameter("@UPN", SqlDbType.VarChar);
sql.Parameters.Add(prm1);
prm1.Value = reg.UPN;
SqlParameter prm2 = new SqlParameter("@MAILADDRESS", SqlDbType.VarChar);
sql.Parameters.Add(prm2);
if (string.IsNullOrEmpty(reg.MailAddress))
{
prm2.Value = DBNull.Value;
}
else
{
prm2.Value = reg.MailAddress;
}
SqlParameter prm3 = new SqlParameter("@PHONENUMBER", SqlDbType.VarChar);
sql.Parameters.Add(prm3);
if (string.IsNullOrEmpty(reg.PhoneNumber))
{
prm3.Value = DBNull.Value;
}
else
{
prm3.Value = reg.PhoneNumber;
}
SqlParameter prm4 = new SqlParameter("@PIN", SqlDbType.Int);
sql.Parameters.Add(prm4);
prm4.Value = reg.PIN;
SqlParameter prm5 = new SqlParameter("@ENABLED", SqlDbType.Bit);
sql.Parameters.Add(prm5);
prm5.Value = reg.Enabled;
SqlParameter prm6 = new SqlParameter("@METHOD", SqlDbType.Int);
sql.Parameters.Add(prm6);
prm6.Value = reg.PreferredMethod;
SqlParameter prm7 = new SqlParameter("@OVERRIDE", SqlDbType.VarChar);
sql.Parameters.Add(prm7);
prm7.Value = reg.OverrideMethod;
con.Open();
try
{
int res = sql.ExecuteNonQuery();
if (newkey)
{
this.OnKeyDataEvent(reg.UPN, KeysDataManagerEventKind.add);
}
}
catch (Exception ex)
{
DataLog.WriteEntry(ex.Message, System.Diagnostics.EventLogEntryType.Error, 5000);
throw new Exception(ex.Message);
}
finally
{
con.Close();
}
return(GetUserRegistration(reg.UPN));
}
/// <summary>
/// DoCheckNotification method implementation
/// </summary>
private Notification DoCheckNotification(Notification Notification)
{
string request = "UPDATE NOTIFICATIONS SET CHECKDATE = @CHECKDATE WHERE REGISTRATIONID=@REGISTRATIONID";
SqlConnection con = new SqlConnection(_connectionstring);
SqlCommand sql = new SqlCommand(request, con);
SqlParameter prmid = new SqlParameter("@REGISTRATIONID", SqlDbType.BigInt);
sql.Parameters.Add(prmid);
prmid.Value = Notification.RegistrationID;
SqlParameter prmdat = new SqlParameter("@CHECKDATE", SqlDbType.DateTime);
sql.Parameters.Add(prmdat);
prmdat.Value = Notification.CheckDate;
con.Open();
try
{
int res = sql.ExecuteNonQuery();
if (res == 1)
{
string select = "SELECT ID, REGISTRATIONID, OTP, CREATIONDATE, VALIDITYDATE, CHECKDATE FROM NOTIFICATIONS WHERE REGISTRATIONID=@REGISTRATIONID";
SqlCommand ssql = new SqlCommand(select, con);
SqlParameter prm = new SqlParameter("@REGISTRATIONID", SqlDbType.BigInt);
ssql.Parameters.Add(prm);
prm.Value = Notification.RegistrationID;
// if (con.State!= ConnectionState.Open) con.Open();
try
{
SqlDataReader rd = ssql.ExecuteReader();
if (rd.Read())
{
Notification.ID = rd.GetInt64(0).ToString();
Notification.RegistrationID = rd.GetInt64(1).ToString();
Notification.OTP = rd.GetInt32(2);
Notification.CreationDate = rd.GetDateTime(3);
Notification.ValidityDate = rd.GetDateTime(4);
if (!rd.IsDBNull(5))
{
Notification.CheckDate = rd.GetDateTime(5);
}
else
{
Notification.CheckDate = null;
}
return(Notification);
}
else
{
return(null);
}
}
catch (Exception ex)
{
DataLog.WriteEntry(ex.Message, System.Diagnostics.EventLogEntryType.Error, 5000);
throw new Exception(ex.Message);
}
}
else
{
return(null);
}
}
catch (Exception ex)
{
DataLog.WriteEntry(ex.Message, System.Diagnostics.EventLogEntryType.Error, 5000);
throw new Exception(ex.Message);
}
finally
{
con.Close();
}
}
/// <summary>
/// GetUserRegistrationsCount method implmentation
/// </summary>
public override int GetUserRegistrationsCount(DataFilterObject filter)
{
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 = "SELECT COUNT(ID) FROM REGISTRATIONS";
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 != PreferredMethod.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";
}
}
}
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;
}
con.Open();
try
{
return((int)sql.ExecuteScalar());
}
catch (Exception ex)
{
DataLog.WriteEntry(ex.Message, System.Diagnostics.EventLogEntryType.Error, 5000);
throw new Exception(ex.Message);
}
finally
{
con.Close();
}
}
/// <summary>
/// GetAllUserRegistrations method implementation
/// </summary>
public override RegistrationList GetAllUserRegistrations(DataOrderObject order, int maxrows = 20000, bool enabledonly = false)
{
string request = string.Empty;
if (enabledonly)
{
request = "SELECT TOP " + maxrows.ToString() + " ID, UPN, MAILADDRESS, PHONENUMBER, PIN, ENABLED, METHOD, OVERRIDE FROM REGISTRATIONS WHERE ENABLED=1";
}
else
{
request = "SELECT TOP " + maxrows.ToString() + " ID, UPN, MAILADDRESS, PHONENUMBER, PIN, ENABLED, METHOD, OVERRIDE FROM REGISTRATIONS";
}
switch (order.Column)
{
case DataOrderField.UserName:
request += " ORDER BY UPN";
break;
case DataOrderField.Email:
request += " ORDER BY MAILADDRESS";
break;
case DataOrderField.Phone:
request += " ORDER BY PHONENUMBER";
break;
default:
request += " ORDER BY ID";
break;
}
if (order.Direction == SortDirection.Descending)
{
request += " DESC";
}
SqlConnection con = new SqlConnection(_connectionstring);
SqlCommand sql = new SqlCommand(request, con);
RegistrationList regs = new RegistrationList();
con.Open();
try
{
SqlDataReader rd = sql.ExecuteReader();
while (rd.Read())
{
Registration reg = new Registration();
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.PIN = rd.GetInt32(4);
reg.Enabled = rd.GetBoolean(5);
reg.PreferredMethod = (PreferredMethod)rd.GetInt32(6);
reg.OverrideMethod = rd.GetString(7);
reg.IsRegistered = true;
regs.Add(reg);
}
return(regs);
}
catch (Exception ex)
{
DataLog.WriteEntry(ex.Message, System.Diagnostics.EventLogEntryType.Error, 5000);
throw new Exception(ex.Message);
}
finally
{
con.Close();
}
}