In this article we give you some important Dbhelper class for login and registration in C#
The given below DB helper class are useful for login and registration for building a different type of Applications like Asp.net mvc ,Web Forms Windows Application
login Dbhelper class
public class LoginModel
{
private string sqlQry;
public bool ValidateUserLoginDetails(string userName, string password, out int iUserID)
{
sqlQry = "";
bool loginFlag = false;
try
{
sqlQry = "SELECT COUNT(1) FROM UserDetails";
sqlQry = sqlQry + " " + "WHERE [Login Name]='" + userName + "' AND [usr_Password]='" + password + " ' AND DeleteFlag<>'D'";
sqlQry = sqlQry + " " + "COLLATE SQL_Latin1_General_CP1_CS_AS";
Object objQty = SqlHelper.SqlExecuteScalar(sqlQry);
if (objQty != null && DBNull.Value != objQty)
{
if (Convert.ToInt32(objQty) == 1)
{
loginFlag = true;
}
}
iUserID = GetUserID(userName);
return loginFlag;
}
catch
{
throw;
}
}
// For ActiveDirectory
public bool ValidateUserCredentialsAgainstAD(string loginName, string password, out int iUserID, out string userName)
{
bool loginFlag = false;
userName = "";
iUserID = 0;
try
{
PrincipalContext ctx = new PrincipalContext(ContextType.Domain);
UserPrincipal user = UserPrincipal.FindByIdentity(ctx, loginName);
if (user != null)
{
userName = user.GivenName + " " + user.Surname;
iUserID = GetADUserID(loginName);
//loginFlag = ctx.ValidateCredentials(loginName, password);
if (ctx.ValidateCredentials(loginName, password) && CheckUserActive(iUserID))
{
loginFlag = true;
}
}
return loginFlag;
}
catch { throw; }
}
private int GetADUserID(string userName)
{
sqlQry = "";
int usrID = 0;
try
{
sqlQry = "SELECT EmpID FROM User_Details WHERE LoginID='" + userName + "'";
Object objUserID = SqlHelper.SqlExecuteScalar(sqlQry);
if (objUserID != null && DBNull.Value != objUserID)
{
usrID = Convert.ToInt32(objUserID);
}
return usrID;
}
catch
{
throw;
}
}
private int GetUserID(string userName)
{
sqlQry = "";
int usrID = 0;
try
{
sqlQry = "SELECT UserID FROM UserDetails";
sqlQry = sqlQry + " " + "WHERE [Login Name]='" + userName + "' AND DeleteFlag<>'D'";
sqlQry = sqlQry + " " + "COLLATE SQL_Latin1_General_CP1_CS_AS";
Object objUserID = SqlHelper.SqlExecuteScalar(sqlQry);
if (objUserID != null && DBNull.Value != objUserID)
{
usrID = Convert.ToInt32(objUserID);
}
return usrID;
}
catch
{
throw;
}
}
private bool CheckUserActive(int empID)
{
sqlQry = "";
bool active = false;
try
{
sqlQry = "SELECT COUNt(*) FROM User_Details WHERE IsActive=1 AND DeleteFlag<>'D' AND EmpID=" + empID;
Object objActive = SqlHelper.SqlExecuteScalar(sqlQry);
if (objActive != null && DBNull.Value != objActive)
{
if (Convert.ToInt32(objActive) == 1)
{
active = true;
}
}
return active;
}
catch { throw; }
}
#region For Dublicate Login
public bool ValidateUserCredentialsForDublicate(string loginName, string password, out int iUserID, out string userName)
{
bool loginFlag = false;
userName = "";
iUserID = 0;
try
{
sqlQry = "SELECT COUNT(1) FROM User_Details";
sqlQry = sqlQry + " " + "WHERE [LoginID]='" + loginName + "' AND [EmpID]='" + password + "'";
sqlQry = sqlQry + " " + "COLLATE SQL_Latin1_General_CP1_CS_AS";
Object objQty = SqlHelper.SqlExecuteScalar(sqlQry);
if (objQty != null && DBNull.Value != objQty)
{
if (Convert.ToInt32(objQty) == 1)
{
loginFlag = true;
}
}
iUserID = GetADUserID(loginName);
userName = GetADUserName(loginName);
//PrincipalContext ctx = new PrincipalContext(ContextType.Domain);
//UserPrincipal user = UserPrincipal.FindByIdentity(ctx, loginName);
//if (user != null)
//{
// userName = user.GivenName + " " + user.Surname;
// loginFlag = ctx.ValidateCredentials(loginName, password);
// iUserID = GetADUserID(loginName);
//}
return loginFlag;
}
catch { throw; }
}
private string GetADUserName(string loginName)
{
sqlQry = "";
string userName = "";
try
{
sqlQry = "SELECT EmpName FROM User_Details WHERE LoginID='" + loginName + "'";
Object objUserID = SqlHelper.SqlExecuteScalar(sqlQry);
if (objUserID != null && DBNull.Value != objUserID)
{
userName = Convert.ToString(objUserID);
}
return userName;
}
catch
{
throw;
}
}
#endregion
//public bool CheckUserLoginName(string loginName)
//{
// sqlQry = "";
// bool existsFlag = false;
// try
// {
// sqlQry = "SELECT COUNT(1) FROM UserDetails";
// sqlQry = sqlQry + " " + "WHERE [USER_NAME]='" + loginName + "'";
// sqlQry = sqlQry + " " + "COLLATE SQL_Latin1_General_CP1_CS_AS";
// Object objQty = SqlHelper.SqlExecuteScalar(sqlQry);
// if (objQty != null && DBNull.Value != objQty)
// {
// if (Convert.ToInt32(objQty) == 1)
// {
// existsFlag = true;
// }
// }
// return existsFlag;
// }
// catch
// {
// throw;
// }
//}
}
registration Dbhelper class
public class UserDetailModel
{
LoginModel objLoginModel = new LoginModel();
private string sqlQry = "";
DataTable dtValues = new DataTable();
bool saveFlag = false;
public DataTable GetUserDetailsByName(string usrName)
{
sqlQry = "";
try
{
sqlQry = @"SELECT UserID,[First Name] + ' ' + [Last Name] AS UsrName,";
sqlQry = sqlQry + " " + "CASE";
sqlQry = sqlQry + " " + "WHEN IsActive = 0 THEN 'InActive' ";
sqlQry = sqlQry + " " + "WHEN IsActive = 1 THEN 'Active' ";
sqlQry = sqlQry + " " + " END AS Active,usr_Mobile AS Mob,usr_Phone AS Ph,usr_Email AS Email,";
sqlQry = sqlQry + " " + "[Status], CreatedDate AS CrDate,CreatedBy AS CrBy,GroupID AS GrpID,usr_Title AS Title,[Login Name] AS LoginName";
sqlQry = sqlQry + " " + "FROM UserDetails WHERE [Login Name] Like '%" + usrName + "%' AND DeleteFlag<>'D'";
dtValues = SqlHelper.SqlExecuteDataTable(sqlQry);
return dtValues;
}
catch { throw; }
}
public bool CheckUserLoginName(string loginName)
{
sqlQry = "";
bool existsFlag = false;
try
{
sqlQry = "SELECT COUNT(1) FROM UserDetails";
sqlQry = sqlQry + " " + "WHERE [Login Name]='" + loginName + "'";
sqlQry = sqlQry + " " + "COLLATE SQL_Latin1_General_CP1_CS_AS";
Object objQty = SqlHelper.SqlExecuteScalar(sqlQry);
if (objQty != null && DBNull.Value != objQty)
{
if (Convert.ToInt32(objQty) == 1)
{
existsFlag = true;
}
}
return existsFlag;
}
catch
{
throw;
}
}
private int GetLatestUserID()
{
sqlQry = "";
int usrID = 0;
try
{
sqlQry = @"SELECT MAX(UserID) FROM [UserDetails]";
Object objUserID = SqlHelper.SqlExecuteScalar(sqlQry);
if (objUserID != null && DBNull.Value != objUserID)
{
usrID = Convert.ToInt32(objUserID);
}
return usrID + 1;
}
catch
{
throw;
}
}
public bool CreateUser(List<UserModel> lstUserModel)
{
sqlQry = "";
try
{
foreach (var UserItem in lstUserModel)
{
sqlQry = "INSERT INTO [dbo].[UserDetails] (";
sqlQry = sqlQry + " " + "[UserID],[First Name] ,[Last Name]";
sqlQry = sqlQry + ",[Login Name],[usr_Password],[IsActive]";
sqlQry = sqlQry + ",[CreatedDate],[CreatedBy]";
sqlQry = sqlQry + ",[GroupID],[usr_Title] ,[usr_Mobile] ,[usr_Phone]";
sqlQry = sqlQry + ",[usr_Email] ,[Address1],[Address2])";
sqlQry = sqlQry + " " + "VALUES (";
sqlQry = sqlQry + " " + GetLatestUserID();
if (!string.IsNullOrEmpty(UserItem.FirstName))
{
sqlQry = sqlQry + " " + ",'" + UserItem.FirstName + "'";
}
else
{
sqlQry = sqlQry + " " + ",NULL";
}
if (!string.IsNullOrEmpty(UserItem.LastName))
{
sqlQry = sqlQry + " " + ",'" + UserItem.LastName + "'";
}
else
{
sqlQry = sqlQry + " " + ",NULL";
}
if (!string.IsNullOrEmpty(UserItem.LogName))
{
sqlQry = sqlQry + " " + ",'" + UserItem.LogName + "'";
}
else
{
sqlQry = sqlQry + " " + ",NULL";
}
if (!string.IsNullOrEmpty(UserItem.Password))
{
sqlQry = sqlQry + " " + ",'" + UserItem.Password + "'";
}
else
{
sqlQry = sqlQry + " " + ",NULL";
}
if (UserItem.Status.HasValue)
{
sqlQry = sqlQry + " " + "," + UserItem.Status;
}
else
{
sqlQry = sqlQry + " " + ",NULL";
}
sqlQry = sqlQry + " " + ",GETDATE()";
if (!string.IsNullOrEmpty(UserItem.CreatedBy))
{
sqlQry = sqlQry + " " + ",'" + UserItem.CreatedBy + "'";
}
else
{
sqlQry = sqlQry + " " + ",NULL";
}
if (!string.IsNullOrEmpty(UserItem.GroupID) && UserItem.GroupID.ToUpper()!="SELECT")
{
sqlQry = sqlQry + " " + "," + Convert.ToInt32(UserItem.GroupID);
}
else
{
sqlQry = sqlQry + " " + ",NULL";
}
if (!string.IsNullOrEmpty(UserItem.Title))
{
sqlQry = sqlQry + " " + ",'" + UserItem.Title + "'";
}
else
{
sqlQry = sqlQry + " " + ",NULL";
}
if (!string.IsNullOrEmpty(UserItem.MobileNo))
{
sqlQry = sqlQry + " " + "," + Convert.ToInt64(UserItem.MobileNo);
}
else
{
sqlQry = sqlQry + " " + ",NULL";
}
if (!string.IsNullOrEmpty(UserItem.PhNo))
{
sqlQry = sqlQry + " " + ", " + Convert.ToInt64(UserItem.PhNo);
}
else
{
sqlQry = sqlQry + " " + ",NULL";
}
if (!string.IsNullOrEmpty(UserItem.Email))
{
sqlQry = sqlQry + " " + ",'" + UserItem.Email + "'";
}
else
{
sqlQry = sqlQry + " " + ",NULL";
}
if (!string.IsNullOrEmpty(UserItem.Address1))
{
sqlQry = sqlQry + " " + ",'" + UserItem.Address1 + "'";
}
else
{
sqlQry = sqlQry + " " + ",NULL";
}
if (!string.IsNullOrEmpty(UserItem.Address2))
{
sqlQry = sqlQry + " " + ",'" + UserItem.Address2 + "'";
}
else
{
sqlQry = sqlQry + " " + ",NULL";
}
sqlQry = sqlQry + " )";
SqlHelper.SqlExecuteNonQuery(sqlQry);
saveFlag = true;
}
return saveFlag;
}
catch { throw; }
}
public bool UpdateUser(List<UserModel> lstUserModel,int userID)
{
sqlQry = "";
try
{
foreach (var UserItem in lstUserModel)
{
sqlQry = "UPDATE [dbo].[UserDetails] SET";
if (!string.IsNullOrEmpty(UserItem.FirstName))
{
sqlQry = sqlQry + " " + "[First Name] = '" + UserItem.FirstName + "'";
}
if (!string.IsNullOrEmpty(UserItem.LastName))
{
sqlQry = sqlQry + " " + ",[Last Name] = '" + UserItem.LastName + "'";
}
if (!string.IsNullOrEmpty(UserItem.LogName))
{
sqlQry = sqlQry + " " + ",[Login Name] = '" + UserItem.LogName + "'";
}
if (UserItem.Status.HasValue)
{
sqlQry = sqlQry + " " + ",[IsActive] = " + UserItem.Status ;
}
if (!string.IsNullOrEmpty(UserItem.GroupID))
{
sqlQry = sqlQry + " " + ",[GroupID] = " + Convert.ToInt32(UserItem.GroupID);
}
if (!string.IsNullOrEmpty(UserItem.ModiBy))
{
sqlQry = sqlQry + " " + ",[Modified By] = '" + UserItem.ModiBy + "'";
}
sqlQry = sqlQry + " " + ",[Modified Date] = GETDATE()";
if (!string.IsNullOrEmpty(UserItem.Title))
{
sqlQry = sqlQry + " " + ",[usr_Title] = '" + UserItem.Title + "'";
}
if (!string.IsNullOrEmpty(UserItem.MobileNo))
{
sqlQry = sqlQry + " " + ",[usr_Mobile] =" + Convert.ToInt64(UserItem.MobileNo);
}
if (!string.IsNullOrEmpty(UserItem.PhNo))
{
sqlQry = sqlQry + " " + ",[usr_Phone] = " + Convert.ToInt64(UserItem.PhNo);
}
if (!string.IsNullOrEmpty(UserItem.Email))
{
sqlQry = sqlQry + " " + ",[usr_Email] = '" + UserItem.Email + "'";
}
if (!string.IsNullOrEmpty(UserItem.Address1))
{
sqlQry = sqlQry + " " + ",[Address1]='" + UserItem.Address1 + "'";
}
if (!string.IsNullOrEmpty(UserItem.Address2))
{
sqlQry = sqlQry + " " + ",[Address2]='" + UserItem.Address2 + "'";
}
sqlQry = sqlQry + " " + "WHERE UserID=" + userID;
SqlHelper.SqlExecuteNonQuery(sqlQry);
saveFlag = true;
}
return saveFlag;
}
catch { throw; }
}
public string ChangePassWord(string userName, string oldPassword, string newPassword,bool resetFlag=false)
{
sqlQry = "";
int iUserID = 0;
string msg="";
try
{
sqlQry = "UPDATE [dbo].[UserDetails] SET";
sqlQry = sqlQry + " " + "[usr_Password] = '" + newPassword + "'";
sqlQry = sqlQry + " " + ",[Modified By] = '" + userName + "'";
sqlQry = sqlQry + " " + ",[Modified Date] = GETDATE()";
if (resetFlag)
{
sqlQry = sqlQry + " " + "WHERE [Login Name]='" + userName + "'";
sqlQry = sqlQry + " " + "COLLATE SQL_Latin1_General_CP1_CS_AS";
SqlHelper.SqlExecuteNonQuery(sqlQry);
msg = "Password Changed Successfully!!";
}
else
{
if (objLoginModel.ValidateUserLoginDetails(userName, oldPassword, out iUserID))
{
//sqlQry = "UPDATE [dbo].[UserDetails] SET";
//sqlQry = sqlQry + " " + "[usr_Password] = '" + newPassword + "'";
//sqlQry = sqlQry + " " + ",[Modified By] = '" + userName + "'";
//sqlQry = sqlQry + " " + ",[Modified Date] = GETDATE()";
//sqlQry = sqlQry + " " + "WHERE [UserID] = " + iUserID;
sqlQry = sqlQry + " " + "WHERE [UserID] = " + iUserID;
SqlHelper.SqlExecuteNonQuery(sqlQry);
msg = "Password Changed Successfully!!";
}
else
{
msg = "Invalid Password!";
}
}
return msg;
}
catch (Exception ex) {
ExceptionLogger.LogError(ex);
return ex.ToString(); }
}
public bool DeleteUser(int userID,string userName)
{
sqlQry = "";
try
{
sqlQry = "UPDATE [dbo].[UserDetails] SET DeleteFlag='D', [Modified By]='" + userName + "', [Modified Date]=GETDATE() WHERE UserID=" + userID;
SqlHelper.SqlExecuteNonQuery(sqlQry);
saveFlag = true;
return saveFlag;
}
catch
{
throw;
}
}
#region AD User
public DataTable GetADUserDetails(string empID = "", string empName = "")
{
sqlQry = "";
try
{
sqlQry = "SELECT UserID,EmpID,EmpName,Department,Office,EMail,GroupID,";
sqlQry = sqlQry + " " + "CASE WHEN IsActive=1 THEN 'Active' ELSE 'InActive' END AS Active,LoginID";
sqlQry = sqlQry + " " + "FROM User_Details";
if (empID != "" && empID != null)
{
sqlQry = SqlHelper.AppendWhereClause(sqlQry);
sqlQry = sqlQry + " " + "EmpID=" + Convert.ToInt32(empID) + "";
}
if (empName != "" && empName != null)
{
sqlQry = SqlHelper.AppendWhereClause(sqlQry);
sqlQry = sqlQry + " " + "EmpName LIKE '%" + empName + "%'";
}
sqlQry = SqlHelper.AppendWhereClause(sqlQry);
sqlQry = sqlQry + " " + "DeleteFlag<>'D'";
dtValues = SqlHelper.SqlExecuteDataTable(sqlQry);
return dtValues;
}
catch { throw; }
}
public bool CheckEmpID(int empID)
{
sqlQry = "";
bool existsFlag = false;
try
{
sqlQry = "SELECT COUNT(*) FROM User_Details WHERE EmpID=" + empID;
Object objQty = SqlHelper.SqlExecuteScalar(sqlQry);
if (objQty != null && DBNull.Value != objQty)
{
if (Convert.ToInt32(objQty) == 1)
{
existsFlag = true;
}
}
return existsFlag;
}
catch
{
throw;
}
}
public bool CheckEmpLoginID(string loginID)
{
sqlQry = "";
bool existsFlag = false;
try
{
sqlQry = "SELECT COUNT(*) FROM User_Details WHERE LoginID='" + loginID + "'";
Object objQty = SqlHelper.SqlExecuteScalar(sqlQry);
if (objQty != null && DBNull.Value != objQty)
{
if (Convert.ToInt32(objQty) == 1)
{
existsFlag = true;
}
}
return existsFlag;
}
catch
{
throw;
}
}
public bool CreateADUser(List<ADUserModel> lstADUserModel)
{
sqlQry = "";
try
{
foreach (var UserItem in lstADUserModel)
{
sqlQry = "INSERT INTO [dbo].[User_Details]";
sqlQry = sqlQry + " " + "([EmpID],[LoginID],[EmpName],[IsActive]";
sqlQry = sqlQry + ",[GroupID],[Department],[EMail],[Office])";
sqlQry = sqlQry + " " + "VALUES (";
if (UserItem.EmpID != 0)
{
sqlQry = sqlQry + UserItem.EmpID;
}
else
{
sqlQry = sqlQry + " " + "NULL";
}
if (!string.IsNullOrEmpty(UserItem.LoginID))
{
sqlQry = sqlQry + ",'" + UserItem.LoginID + "'";
}
else
{
sqlQry = sqlQry + " " + ",NULL";
}
if (!string.IsNullOrEmpty(UserItem.EmpName))
{
sqlQry = sqlQry + " " + ",'" + UserItem.EmpName + "'";
}
else
{
sqlQry = sqlQry + " " + ",NULL";
}
if (UserItem.Status.HasValue)
{
sqlQry = sqlQry + " " + "," + UserItem.Status;
}
else
{
sqlQry = sqlQry + " " + ",NULL";
}
if (UserItem.GroupID != 0)
{
sqlQry = sqlQry + " " + "," + UserItem.GroupID;
}
else
{
sqlQry = sqlQry + " " + ",NULL";
}
if (!string.IsNullOrEmpty(UserItem.Department))
{
sqlQry = sqlQry + " " + ",'" + UserItem.Department + "'";
}
else
{
sqlQry = sqlQry + " " + ",NULL";
}
if (!string.IsNullOrEmpty(UserItem.EMail))
{
sqlQry = sqlQry + " " + ",'" + UserItem.EMail + "'";
}
else
{
sqlQry = sqlQry + " " + ",NULL";
}
if (!string.IsNullOrEmpty(UserItem.Office))
{
sqlQry = sqlQry + " " + ",'" + UserItem.Office + "'";
}
else
{
sqlQry = sqlQry + " " + ",NULL";
}
sqlQry = sqlQry + " )";
SqlHelper.SqlExecuteNonQuery(sqlQry);
saveFlag = true;
}
return saveFlag;
}
catch { throw; }
}
public bool UpdateADUser(List<ADUserModel> lstADUserModel, int empID)
{
sqlQry = "";
try
{
foreach (var UserItem in lstADUserModel)
{
sqlQry = "UPDATE [dbo].[User_Details] SET";
if (!string.IsNullOrEmpty(UserItem.LoginID))
{
sqlQry = sqlQry + " " + "[LoginID] = '" + UserItem.LoginID + "'";
}
if (!string.IsNullOrEmpty(UserItem.EmpName))
{
sqlQry = sqlQry + " " + ",[EmpName] = '" + UserItem.EmpName + "'";
}
if (UserItem.Status.HasValue)
{
sqlQry = sqlQry + " " + ",[IsActive] = " + UserItem.Status;
}
if (UserItem.GroupID != 0)
{
sqlQry = sqlQry + " " + ",[GroupID] = " + Convert.ToInt32(UserItem.GroupID);
}
if (!string.IsNullOrEmpty(UserItem.Department))
{
sqlQry = sqlQry + " " + ",[Department] = '" + UserItem.Department + "'";
}
if (!string.IsNullOrEmpty(UserItem.EMail))
{
sqlQry = sqlQry + " " + ",[EMail] = '" + UserItem.EMail + "'";
}
if (!string.IsNullOrEmpty(UserItem.Office))
{
sqlQry = sqlQry + " " + ",[Office] = '" + UserItem.Office + "'";
}
if (!string.IsNullOrEmpty(UserItem.ModiBy))
{
sqlQry = sqlQry + " " + ",[Modified By] = '" + UserItem.ModiBy + "'";
}
sqlQry = sqlQry + " " + ",[Modified Date] = GETDATE()";
sqlQry = sqlQry + " " + "WHERE EmpID=" + empID;
SqlHelper.SqlExecuteNonQuery(sqlQry);
saveFlag = true;
}
return saveFlag;
}
catch { throw; }
}
public bool DeleteADUser(int empID, string userName)
{
sqlQry = "";
try
{
sqlQry = "UPDATE [dbo].[User_Details] SET DeleteFlag='D', [Modified By]='" + userName + "', [Modified Date]=GETDATE() WHERE EmpID=" + empID;
SqlHelper.SqlExecuteNonQuery(sqlQry);
saveFlag = true;
return saveFlag;
}
catch
{
throw;
}
}
#endregion
}
public class UserModel
{
public string FirstName { get; set; }
public string LastName { get; set; }
public string LogName { get; set; }
public string Password { get; set; }
public string Title { get; set; }
public string ModiBy { get; set; }
public string CreatedBy { get; set; }
public string Email { get; set; }
public string MobileNo { get; set; }
public string PhNo { get; set; }
public string Address1 { get; set; }
public string Address2 { get; set; }
public int? Status { get; set; }
public string GroupID { get; set; }
}
public class ADUserModel
{
public int EmpID { get; set; }
public string LoginID { get; set; }
public string EmpName { get; set; }
public int? Status { get; set; }
public int GroupID { get; set; }
public string Department { get; set; }
public string EMail { get; set; }
public string Office { get; set; }
public string ModiBy { get; set; }
}