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; } }
Latest posts by DuttaluruVijayakumar (see all)
- How to create dynamic form fields using jQuery - January 4, 2021
- What is CTS (Common Type System) in .Net - October 16, 2020
- What is main method in c# - October 13, 2020