/// <summary> /// /// </summary> /// <param name="userName"></param> /// <param name="key"></param> /// <returns></returns> public List <ModuleAccess> GetModuleDataForUser(string userName, string key) { List <ModuleAccess> moduleList = new List <ModuleAccess>(); ConnectDB db = new ConnectDB(); SqlCommand cmd; SqlDataReader dr; cmd = new SqlCommand(); cmd.CommandType = CommandType.Text; if (key == null) { cmd.CommandText = "SELECT M.* FROM Module AS M" + " WHERE ((M.expiryDate > GETDATE())" + " OR (M.moduleId IN (" + " SELECT EX.moduleId FROM Exception AS EX JOIN Account AS A ON EX.accountId = A.accountId" + " WHERE A.userName= @userName" + " AND EX.expiryDate > GETDATE())))" + " AND (M.moduleId NOT IN (" + " SELECT AM.moduleId FROM Account_Module AS AM JOIN Account AS A ON A.accountId = AM.accountId" + " WHERE A.userName = @userName1));"; cmd.Parameters.Add("@userName", SqlDbType.VarChar).Value = userName; cmd.Parameters.Add("@userName1", SqlDbType.VarChar).Value = userName; } else { cmd.CommandText = "SELECT M.* FROM Module AS M " + " WHERE ((M.moduleName LIKE @moduleName" + " AND M.expiryDate > GETDATE())" + " OR (M.moduleId IN (" + " SELECT EX.moduleId FROM Exception AS EX JOIN Account AS A ON EX.accountId = A.accountId" + " JOIN Module AS M ON EX.moduleId = M.moduleId" + " WHERE A.userName= @userName" + " AND M.moduleName LIKE @moduleName1" + " AND EX.expiryDate > GETDATE())))" + " AND (M.moduleId NOT IN (" + " SELECT AM.moduleId FROM Account_Module AS AM JOIN Account AS A ON A.accountId = AM.accountId" + " WHERE A.userName = @userName1));"; cmd.Parameters.Add("@moduleName", SqlDbType.VarChar).Value = "%" + key + "%"; cmd.Parameters.Add("@userName", SqlDbType.VarChar).Value = userName; cmd.Parameters.Add("@moduleName1", SqlDbType.VarChar).Value = "%" + key + "%"; cmd.Parameters.Add("@userName1", SqlDbType.VarChar).Value = userName; } dr = db.searchData(cmd); if (dr.HasRows) { while (dr.Read()) { ModuleAccess module = new ModuleAccess(); if (System.DBNull.Value.Equals(dr[0])) { module.ModuleId = 0; } else { module.ModuleId = Convert.ToInt32(dr[0]); } if (System.DBNull.Value.Equals(dr[1])) { module.ModuleName = null; } else { module.ModuleName = Convert.ToString(dr[1]); } if (System.DBNull.Value.Equals(dr[2])) { module.Description = null; } else { module.Description = Convert.ToString(dr[2]); } if (System.DBNull.Value.Equals(dr[3])) { module.ExpiryDate = default(DateTime); } else { module.ExpiryDate = Convert.ToDateTime(dr[3]); } if (System.DBNull.Value.Equals(dr[3])) { module.CreationDate = default(DateTime); } else { module.CreationDate = Convert.ToDateTime(dr[3]); } moduleList.Add(module); } } if ((dr != null) && (!dr.IsClosed)) { dr.Close(); } db.Close(); return(moduleList); }
public bool UpdateModule(ModuleAccess module, AgreementInfoAccess agreement) { ConnectDB db = new ConnectDB(); SqlCommand cmd; cmd = new SqlCommand("UpdateModule"); cmd.CommandType = CommandType.StoredProcedure; SqlParameter RetVal = cmd.Parameters.Add("RetVal", SqlDbType.Int); RetVal.Direction = ParameterDirection.ReturnValue; //@moduleName VARCHAR(255) , // @description VARCHAR(255) = NULL, // @expiryDate DATETIME = NULL, // @creationDate DATETIME = null, // @userType VARCHAR(255), // @content VARCHAR(2048) = NULL SqlParameter moduleId = cmd.Parameters.Add("@moduleId", SqlDbType.Int); moduleId.Direction = ParameterDirection.Input; SqlParameter moduleName = cmd.Parameters.Add("@moduleName", SqlDbType.VarChar, 255); moduleName.Direction = ParameterDirection.Input; SqlParameter description = cmd.Parameters.Add("@description", SqlDbType.VarChar, 255); description.Direction = ParameterDirection.Input; SqlParameter expiryDate = cmd.Parameters.Add("@expiryDate", SqlDbType.DateTime); expiryDate.Direction = ParameterDirection.Input; SqlParameter creationDate = cmd.Parameters.Add("@creationDate", SqlDbType.DateTime); creationDate.Direction = ParameterDirection.Input; SqlParameter userType = cmd.Parameters.Add("@userType", SqlDbType.VarChar, 255); userType.Direction = ParameterDirection.Input; SqlParameter content = cmd.Parameters.Add("@content", SqlDbType.VarChar, 2048); content.Direction = ParameterDirection.Input; if (module.ModuleId != 0) { return(false); } moduleId.Value = module.ModuleId; if (module.ModuleName != null) { moduleName.Value = module.ModuleName; } if (module.Description != null) { description.Value = module.Description; } if (module.ExpiryDate != null) { expiryDate.Value = module.ExpiryDate; } creationDate.Value = DateTime.Now; if (agreement.UserType != null) { userType.Value = agreement.UserType; } if (agreement.Content != null) { content.Value = agreement.Content; } db.changeData(cmd); db.Close(); if (Int32.Parse(RetVal.Value.ToString()) == 0) { return(true); } else { return(false); } }
/// <summary> /// /// </summary> /// <param name="key"></param> /// <returns></returns> public List <ModuleAccess> GetModuleData(string key = null) { List <ModuleAccess> moduleList = new List <ModuleAccess>(); ConnectDB db = new ConnectDB(); SqlCommand cmd; SqlDataReader dr; cmd = new SqlCommand(); cmd.CommandType = CommandType.Text; if (key == null) { cmd.CommandText = "SELECT moduleId, moduleName, description, expiryDate, creationDate FROM Module; "; } else { cmd.CommandText = "SELECT moduleId, moduleName, description, expiryDate, creationDate FROM Module WHERE moduleName LIKE @moduleName;"; cmd.Parameters.Add("@moduleName", SqlDbType.VarChar).Value = "%" + key + "%"; } dr = db.searchData(cmd); if (dr.HasRows) { while (dr.Read()) { ModuleAccess module = new ModuleAccess(); if (System.DBNull.Value.Equals(dr[0])) { module.ModuleId = 0; } else { module.ModuleId = Convert.ToInt32(dr[0]); } if (System.DBNull.Value.Equals(dr[1])) { module.ModuleName = null; } else { module.ModuleName = Convert.ToString(dr[1]); } if (System.DBNull.Value.Equals(dr[2])) { module.Description = null; } else { module.Description = Convert.ToString(dr[2]); } if (System.DBNull.Value.Equals(dr[3])) { module.ExpiryDate = default(DateTime); } else { module.ExpiryDate = Convert.ToDateTime(dr[3]); } if (System.DBNull.Value.Equals(dr[3])) { module.CreationDate = default(DateTime); } else { module.CreationDate = Convert.ToDateTime(dr[3]); } moduleList.Add(module); } } if ((dr != null) && (!dr.IsClosed)) { dr.Close(); } db.Close(); return(moduleList); }