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;
}
