public List <WorkerEntity> GetDataFromDB(ProjectAllocationFramework.Statues.ProgressChangedEventHandler OnProgress, WorkerSearchCondtion searchCondition = null) { List <WorkerEntity> entityList = new List <WorkerEntity>(); string sql = "select WorkerCode, WorkerName FROM [Worker] WHERE 1=1 and WorkerCode like @WorkerCode and WorkerName like @WorkerName ORDER BY WorkerCode"; DbCommand cmd = DatabaseUtil.GetCommand(db.GetSqlStringCommand(sql)); string workerCode = string.Empty; string workerName = string.Empty; if (searchCondition != null) { workerCode = searchCondition.WorkerCode.Trim(); workerName = searchCondition.WorkerName.Trim(); } db.AddInParameter(cmd, "WorkerCode", DbType.String, '%' + workerCode + '%'); db.AddInParameter(cmd, "WorkerName", DbType.String, '%' + workerName + '%'); using (IDataReader reader = db.ExecuteReader(cmd)) { while (reader.Read()) { WorkerEntity item = new WorkerEntity(); item.WorkerCode = ConvertUtil.ToString(reader["WorkerCode"]); item.WorkerName = ConvertUtil.ToString(reader["WorkerName"]); item.Action = Constant.ACTION_UPDATE; item.ReadOnly = true; //throw new Exception(); entityList.Add(item); } } return(entityList); }
public bool Login(string UserCode, string Password, ProjectAllocationFramework.Statues.ProgressChangedEventHandler OnProgress = null) { Database db = EnterpriseLibraryContainer.Current.GetInstance <Database>(); bool ret = false; string sql = "select UserCode, UserName, Password FROM [User] WHERE UserCode = @UserCode and Password = @Password"; DbCommand cmd = DatabaseUtil.GetCommand(db.GetSqlStringCommand(sql)); db.AddInParameter(cmd, "UserCode", DbType.String, UserCode); db.AddInParameter(cmd, "Password", DbType.String, Password); using (IDataReader reader = db.ExecuteReader(cmd)) { while (reader.Read()) { UserEntity item = new UserEntity(); item.UserCode = ConvertUtil.ToString(reader["UserCode"]); item.UserName = ConvertUtil.ToString(reader["UserName"]); item.Password = ConvertUtil.ToString(reader["Password"]); User.Operator = item; ret = true; } } return(ret); }
public static List <string> GetDbInstance( string ProjectAllocationServer, string ProjectAllocationDB, string SQLUserID, string SQLPwd, string TimeOut, ProjectAllocationFramework.Statues.ProgressChangedEventHandler OnProgress = null) { string sql = "select name from master.dbo.sysdatabases where cmptlevel >= '80' and dbid>=5 order by name "; string connectionString = string.Format(Constant.CONNECTIONSTRING, ProjectAllocationServer, ProjectAllocationDB, SQLUserID, SQLPwd, TimeOut); SqlConnection connection = new SqlConnection(connectionString); connection.Open(); DbCommand cmd = connection.CreateCommand(); cmd.CommandText = sql; cmd.CommandType = CommandType.Text; List <string> listDb = new List <string>(); using (IDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection)) { while (reader.Read()) { listDb.Add(ConvertUtil.ToString(reader["Name"])); } } return(listDb); }
public bool SaveDataToDB(ProjectAllocationFramework.Statues.ProgressChangedEventHandler OnProgress, List <WorkerEntity> entityList) { // The default database service is determined through configuration // and passed to the method as a parameter that can be generated // automatically through injection when the application initializes. if (entityList == null || entityList.Count == 0) { return(true); } bool result = false; int count = entityList.Count; using (DbConnection conn = db.CreateConnection()) { conn.Open(); DbTransaction trans = conn.BeginTransaction(); try { int i = 1; foreach (WorkerEntity entity in entityList) { string strWorkerCode = ConvertUtil.ToString(entity.WorkerCode); //Project Code string strWorkerName = ConvertUtil.ToString(entity.WorkerName); //Project Name string strUser = ConvertUtil.ToString(entity.User); //User int intAction = entity.Action; //Action string sql = string.Empty; if (intAction == 2) { sql = "DELETE FROM [Worker] WHERE [WorkerCode]=@WorkerCode"; } else { intAction = 0; sql = "SELECT WorkerCode FROM [Worker] WHERE WorkerCode=@WorkerCode"; DbCommand cmd = DatabaseUtil.GetCommand(db.GetSqlStringCommand(sql)); cmd.Parameters.Clear(); db.AddInParameter(cmd, "WorkerCode", DbType.String, strWorkerCode); using (IDataReader reader = db.ExecuteReader(cmd)) { while (reader.Read()) { intAction = 1; break; } } if (intAction == 0) { sql = "INSERT INTO [Worker](WorkerCode, WorkerName,Create_dt,Create_User,Update_dt,Update_User) VALUES (@WorkerCode, @WorkerName,datetime(),@Create_User,datetime(),@Update_User)"; } else if (intAction == 1) { sql = "UPDATE [Worker] SET [WorkerName] =@WorkerName,[Update_dt] =datetime(),[Update_User] =@Update_User WHERE [WorkerCode]=@WorkerCode"; } } DbCommand userCommand = DatabaseUtil.GetCommand(db.GetSqlStringCommand(sql)); userCommand.Parameters.Clear(); db.AddInParameter(userCommand, "WorkerCode", DbType.String, strWorkerCode); db.AddInParameter(userCommand, "WorkerName", DbType.String, strWorkerName); db.AddInParameter(userCommand, "Create_User", DbType.String, strUser); db.AddInParameter(userCommand, "Update_User", DbType.String, strUser); db.AddInParameter(userCommand, "Action", DbType.Int32, intAction); db.ExecuteNonQuery(userCommand, trans); if (OnProgress != null) { string InfomationMessage = string.Format(ProjectAllocationResource.Message.Common_Master_Save_Info, i); int Percentage = (int)(i * Constant.ProgressBarMaximum / count); ProjectAllocationFramework.Statues.ProgressChangedEventArgs args = new ProjectAllocationFramework.Statues.ProgressChangedEventArgs(InfomationMessage, Percentage, null); OnProgress(this, args); } //if success entity.ReadOnly = true; i++; } // Commit the transaction. trans.Commit(); result = true; } catch { // Roll back the transaction. trans.Rollback(); throw; } conn.Close(); return(result); } }
public void DataValidation(ProjectAllocationFramework.Statues.ProgressChangedEventHandler OnProgress, List <WorkerEntity> entityList) { if (entityList == null || entityList.Count == 0) { return; } List <WorkerEntity> userDBList = GetDataFromDB(OnProgress); var WorkerCodeDBList = from item in userDBList select item.WorkerCode; ListRangeValidator <string> WorkerCodeListRangeValidator = new ListRangeValidator <string>(WorkerCodeDBList.ToList <string>(), true, ProjectAllocationResource.Message.Worker_WorkerCode_Exists); var WorkerCodeList = from item in entityList select item.WorkerCode; UniqueValidator <string> UniqueValidator = new UniqueValidator <string>(WorkerCodeList.ToList <string>(), ProjectAllocationResource.Message.Worker_WorkerCode_Unique); ValidatorFactory valFactory = EnterpriseLibraryContainer.Current.GetInstance <ValidatorFactory>(); Validator <WorkerEntity> entityValidator = valFactory.CreateValidator <WorkerEntity>(); int i = 1; bool noError = true; int count = entityList.Count(); StringBuilder builder = new StringBuilder(); foreach (WorkerEntity entity in entityList) { ValidationResults results = entityValidator.Validate(entity); if (!entity.ReadOnly) { ValidationResults userExistCheckResults = WorkerCodeListRangeValidator.Validate(entity.WorkerCode); results.AddAllResults(userExistCheckResults); } ValidationResults uniqueCheckResult = UniqueValidator.Validate(entity.WorkerCode); results.AddAllResults(uniqueCheckResult); bool isValid = results.IsValid; noError &= isValid; if (!isValid) { builder.AppendLine( string.Format( CultureInfo.CurrentCulture, ProjectAllocationResource.Message.Common_Row_Error, entity.Row)); foreach (ValidationResult result in results) { builder.AppendLine( string.Format( CultureInfo.CurrentCulture, ProjectAllocationResource.Message.Common_Field_Error, result.Message)); } } if (OnProgress != null) { string InfomationMessage = string.Format(ProjectAllocationResource.Message.Common_Master_Validate_Info, i); int Percentage = (int)(i * Constant.ProgressBarMaximum / count); ProjectAllocationFramework.Statues.ProgressChangedEventArgs args = new ProjectAllocationFramework.Statues.ProgressChangedEventArgs(InfomationMessage, Percentage, null); OnProgress(this, args); } i++; } if (!noError) { throw new ProjectAllocationFramework.RuntimeException("Validation Error", builder.ToString()); } }