public static void AddUser(User user) { if (user.Id > 0) { //Edit EditUserDetails(user); } else { //add AddNewUser(user); } }
/// <summary> /// call Add and edit method /// </summary> /// <param name="user"></param> public static void AddUser(User user) { if (user.Id > 0) EditUserDetails(user); else AddNewUser(user); }
/// <summary> /// edit user details in database /// </summary> /// <param name="user"></param> private static void EditUserDetails(User user) { SqlConnection conn = new SqlConnection(SqlConString); try { //Create the SqlCommand object SqlCommand cmd = new SqlCommand("sp_addeditdeleteUser", conn); //Specify that the SqlCommand is a stored procedure cmd.CommandType = System.Data.CommandType.StoredProcedure; //Add the input parameters to the command object cmd.Parameters.AddWithValue("@Id", user.Id); cmd.Parameters.AddWithValue("@UserId", user.UserId); // cmd.Parameters.AddWithValue("@Password", "TestDTS"); cmd.Parameters.AddWithValue("@FirstName", user.FirstName); cmd.Parameters.AddWithValue("@LastName", user.LastName); cmd.Parameters.AddWithValue("@EmailId", user.EmailId); cmd.Parameters.AddWithValue("@RoleID", user.RoleId); cmd.Parameters.AddWithValue("@StatementType", "Update"); //Open the connection and execute the query conn.Open(); cmd.ExecuteNonQuery(); } catch(Exception ex) { throw ex; } finally { conn.Close(); } }
/// <summary> /// Add new user to database and send mail to user with loginId and password /// </summary> /// <param name="user"></param> private static void AddNewUser(User user) { SqlConnection conn = new SqlConnection(SqlConString); SqlTransaction transaction; conn.Open(); transaction = conn.BeginTransaction(); try { //Create the SqlCommand object SqlCommand cmd = new SqlCommand("sp_addeditdeleteUser", conn, transaction); //Specify that the SqlCommand is a stored procedure cmd.CommandType = System.Data.CommandType.StoredProcedure; //Add the input parameters to the command object cmd.Parameters.AddWithValue("@Id", user.Id); cmd.Parameters.AddWithValue("@UserId", user.UserId); cmd.Parameters.AddWithValue("@Password", user.Password); cmd.Parameters.AddWithValue("@FirstName", user.FirstName); cmd.Parameters.AddWithValue("@LastName", user.LastName); cmd.Parameters.AddWithValue("@EmailId", user.EmailId); cmd.Parameters.AddWithValue("@RoleID", user.RoleId); cmd.Parameters.AddWithValue("@StatementType", "Insert"); cmd.ExecuteNonQuery(); //If new user added to system, then send mail to User string msgBody = "Your manager has given access to use Defect Tracking application. Please use the given login Id and password \n"+ "to log into system. \n Application url: "+ ConfigurationManager.AppSettings["webSiteURL"] + " LoginID: "+user.UserId + " password: "+ user.Password; Email.SendMail(user.EmailId, msgBody); transaction.Commit(); } catch(Exception ex) { transaction.Rollback(); throw ex; } finally { conn.Close(); } }
/// <summary> /// Get user by dbid /// </summary> /// <param name="Id"></param> /// <returns></returns> public static User GetUserById(int Id) { SqlConnection con = new SqlConnection(SqlConString); User usr = new User(); try { string strquery = "Select u.Id,u.UserId,u.FirstName,u.LastName,u.Email, u.Id_Role,r.Txt_Role, u.password from DTSUserProfile u inner join DTSRole r " + " on u.Id_Role = r.Id_Role where r.IsActive =1 and u.Id = " + Id ; SqlCommand cmd = new SqlCommand(strquery, con); con.Open(); SqlDataReader reader = cmd.ExecuteReader(); if (reader != null) { while (reader.Read()) { usr.Id = int.Parse(reader[0].ToString()); usr.UserId = reader[1].ToString(); usr.FirstName = reader[2].ToString(); usr.LastName = reader[3].ToString(); usr.EmailId = reader[4].ToString(); usr.RoleId = int.Parse(reader[5].ToString()); usr.Role = reader[6].ToString(); usr.Password = reader[7].ToString(); } } reader.Close(); } catch (Exception ex) { throw (ex); } finally { if (con != null) con.Close(); } return usr; }
/// <summary> /// Get all user details from database /// </summary> /// <returns></returns> public static IEnumerable<User> GetAllUser() { SqlConnection con = new SqlConnection(SqlConString); List<User> lst = new List<User>(); try { string strquery = "Select u.Id, u.UserId,u.FirstName,u.LastName,u.Email, u.Id_Role,r.Txt_Role from DTSUserProfile u inner join DTSRole r " + " on u.Id_Role = r.Id_Role where r.IsActive =1 and u.Id_Role <> 1 "; SqlCommand cmd = new SqlCommand(strquery, con); con.Open(); SqlDataReader reader = cmd.ExecuteReader(); if (reader != null) { while (reader.Read()) { User usr = new User(); usr.Id = int.Parse(reader[0].ToString()); usr.UserId = reader[1].ToString(); usr.FirstName = reader[2].ToString(); usr.LastName = reader[3].ToString(); usr.EmailId = reader[4].ToString(); usr.RoleId = int.Parse(reader[5].ToString()); usr.Role = reader[6].ToString(); lst.Add(usr); } } reader.Close(); } catch (Exception ex) { throw (ex); } finally { if (con != null) con.Close(); } return lst; }
/// <summary> /// Get user list user under the manager /// </summary> /// <returns></returns> public static IEnumerable<User> GetAssignUserList(string projOwner) { SqlConnection con = new SqlConnection(SqlConString); List<User> usrList = new List<User>(); try { string strquery = " Select distinct (usr.UserId) from DTSUserProject up inner join DTSUserProfile usr on up.UserID =usr.Id "+ " where up.ProjectMgrId='" + projOwner +"'"; SqlCommand cmd = new SqlCommand(strquery, con); con.Open(); SqlDataReader reader = cmd.ExecuteReader(); if (reader != null) { while (reader.Read()) { var usr = new User(); // usr.Id = int.Parse(reader["id"].ToString()); usr.UserId = reader["UserId"].ToString(); usrList.Add(usr); } } reader.Close(); } catch (Exception ex) { throw (ex); } finally { if (con != null) con.Close(); } return usrList; }
private static void AddNewUser(User user) { SqlConnection conn = new SqlConnection(SqlConString); //Create the SqlCommand object SqlCommand cmd = new SqlCommand("sp_addeditdeleteUser", conn); //Specify that the SqlCommand is a stored procedure cmd.CommandType = System.Data.CommandType.StoredProcedure; //Add the input parameters to the command object cmd.Parameters.AddWithValue("@Id", user.Id); cmd.Parameters.AddWithValue("@UserId", user.UserId); cmd.Parameters.AddWithValue("@Password", user.Password); cmd.Parameters.AddWithValue("@FirstName", user.FirstName); cmd.Parameters.AddWithValue("@LastName", user.LastName); cmd.Parameters.AddWithValue("@EmailId", user.EmailId); cmd.Parameters.AddWithValue("@RoleID", user.RoleId); cmd.Parameters.AddWithValue("@StatementType", "Insert"); //Open the connection and execute the query conn.Open(); cmd.ExecuteNonQuery(); }
private static User GetUser(object user) { var objUser = new User(); var tmp = (Dictionary<string, object>)user; object objId = null; object objUserId = null; object objFirstName = null; object objLastName = null; object objEmailId = null; object objRoleId = null; if (tmp.TryGetValue("Id", out objId)) objUser.Id = int.Parse(objId.ToString()); if (tmp.TryGetValue("UserId", out objUserId)) objUser.UserId = objUserId.ToString(); if (tmp.TryGetValue("FirstName", out objFirstName)) objUser.FirstName = objFirstName.ToString(); if (tmp.TryGetValue("LastName", out objLastName)) objUser.LastName = objLastName.ToString(); if (tmp.TryGetValue("EmailId", out objEmailId)) objUser.EmailId = objEmailId.ToString(); if (tmp.TryGetValue("RoleId", out objRoleId)) objUser.RoleId = int.Parse(objRoleId.ToString()); objUser.Password = UserDatasource.PasswordGenerator(); return objUser; }