public SqlServerConfigInfo Get(int id) { SqlServerConfigInfo dbConfigInfo = null; string sqlText = @" SELECT `Id`, `ServerAddress`, `DBName`, `UserName`, `Password`, `UpdatedDate`, `CreatedDate`, `EquipmentNum`, `PageSize`, `MaxCapacity`, `StoredType` FROM `custom_db_config` WHERE `Id` = @Id;"; MySqlParameter[] parameters = { new MySqlParameter("@Id", MySqlDbType.Int32) { Value = id } }; MySqlDataReader sqlDataReader = MySqlDbHelper.ExecuteReader(sqlText, parameters); if (sqlDataReader.Read()) { dbConfigInfo = new SqlServerConfigInfo(); ReadRecordData(sqlDataReader, dbConfigInfo); } sqlDataReader.Close(); return(dbConfigInfo); }
public bool Exists(int jobId, string jobName) { string sqlWhere = ""; List <MySqlParameter> listParms = new List <MySqlParameter>(); listParms.Add(new MySqlParameter("@JobName", MySqlDbType.VarChar, 100) { Value = jobName }); if (jobId > 0) { sqlWhere = "WHERE JobName = @JobName AND JobId<>@JobId"; listParms.Add(new MySqlParameter("@JobId", MySqlDbType.Int32, 11) { Value = jobId }); } else { sqlWhere = "WHERE JobName = @JobName"; } int recordsTotal = MySqlDbHelper.ExecuteScalar <int>("SELECT COUNT(*) FROM custom_job_details " + sqlWhere, listParms.ToArray()); return(recordsTotal > 0); }
public PageData GetPageList(int pageSize, int curPage, string dbName = "") { string sqlWhere = ""; List <MySqlParameter> listParms = new List <MySqlParameter>(); if (!string.IsNullOrEmpty(dbName)) { sqlWhere = " AND `DBName` LIKE @DBName"; listParms.Add(new MySqlParameter("@DBName", MySqlDbType.VarChar, 45) { Value = "%" + dbName + "%" }); } int recordsTotal = MySqlDbHelper.ExecuteScalar <int>("SELECT COUNT(*) FROM custom_db_config WHERE `ServerState`=" + (byte)ServerStateEnum.Enabled + sqlWhere, listParms.ToArray()); string sqlText = @" SELECT `Id`, `ServerAddress`, `DBName`, `UserName`, `Password`, `UpdatedDate`, `CreatedDate`, `EquipmentNum`, `PageSize`, `MaxCapacity`, `StoredType`, `DBType`, `ServerState`, `AuthenticatedType` FROM `custom_db_config` WHERE `ServerState`=" + (byte)ServerStateEnum.Enabled + sqlWhere + " ORDER BY Id DESC LIMIT " + (curPage - 1) * pageSize + "," + pageSize; List <SqlServerConfigInfo> list = new List <SqlServerConfigInfo>(); MySqlDataReader sqlDataReader = MySqlDbHelper.ExecuteReader(sqlText, listParms.ToArray()); PageData pageData = new PageData(); pageData.PageSize = pageSize; pageData.CurPage = curPage; pageData.RecordCount = Math.Max(1, recordsTotal); if (pageData.RecordCount > 0) { pageData.PageCount = Convert.ToInt32(Math.Ceiling((double)pageData.RecordCount / (double)pageSize)); } while (sqlDataReader.Read()) { SqlServerConfigInfo dbConfigInfo = new SqlServerConfigInfo(); ReadRecordData(sqlDataReader, dbConfigInfo); list.Add(dbConfigInfo); } sqlDataReader.Close(); pageData.PageList = list; return(pageData); }
public PageData GetPageList(int pageSize, int curPage, string jobName = "") { string sqlWhere = ""; List <MySqlParameter> listParms = new List <MySqlParameter>(); if (!string.IsNullOrEmpty(jobName)) { sqlWhere = "WHERE JobName LIKE @JobName"; listParms.Add(new MySqlParameter("@JobName", MySqlDbType.VarChar, 100) { Value = "%" + jobName + "%" }); } int recordsTotal = MySqlDbHelper.ExecuteScalar <int>("SELECT COUNT(*) FROM custom_job_details " + sqlWhere, listParms.ToArray()); string sqlText = @" SELECT JobId, JobName, JobGroup, JobChineseName, JobServiceURL, CreatedDate, UpdatedDate, StartDate, EndDate, PageSize, `Interval`, IntervalType, State, Description, ExecutedFreq FROM custom_job_details " + sqlWhere + " ORDER BY JobId DESC LIMIT " + (curPage - 1) * pageSize + "," + pageSize; List <CustomJobDetail> list = new List <CustomJobDetail>(); MySqlDataReader sqlDataReader = MySqlDbHelper.ExecuteReader(sqlText, listParms.ToArray()); PageData pageData = new PageData(); pageData.PageSize = pageSize; pageData.CurPage = curPage; pageData.RecordCount = Math.Max(1, recordsTotal); if (pageData.RecordCount > 0) { pageData.PageCount = Convert.ToInt32(Math.Ceiling((double)pageData.RecordCount / (double)pageSize)); } while (sqlDataReader.Read()) { CustomJobDetail jobDetail = new CustomJobDetail(); ReadRecordData(sqlDataReader, jobDetail); list.Add(jobDetail); } sqlDataReader.Close(); pageData.PageList = list; return(pageData); }
public int Delete(int id) { string sqlText = "DELETE FROM `custom_db_config` WHERE `Id` = @Id;"; MySqlParameter[] parameters = { new MySqlParameter("@Id", MySqlDbType.Int32) { Value = id } }; return(MySqlDbHelper.ExecuteNonQuery(sqlText, parameters)); }
public int Delete(int jobId, string jobName) { string sqlText = "DELETE FROM custom_job_details WHERE JobId = @JobId AND JobName = @JobName;"; MySqlParameter[] parameters = { new MySqlParameter("@JobId", MySqlDbType.Int32) { Value = jobId }, new MySqlParameter("@JobName", MySqlDbType.VarChar, 100) { Value = jobName } }; return(MySqlDbHelper.ExecuteNonQuery(sqlText, parameters)); }
public CustomJobDetail Get(int jobId, string jobName) { CustomJobDetail jobDetail = null; string sqlText = @" SELECT `JobId`, `JobName`, `JobGroup`, `JobChineseName`, `JobServiceURL`, `CreatedDate`, `UpdatedDate`, `StartDate`, `EndDate`, `ExecutedFreq`, `PageSize`, `Interval`, `State`, `Description`, `IntervalType` FROM `custom_job_details` WHERE `JobId` = @JobId AND `JobName` = @JobName;"; MySqlParameter[] parameters = { new MySqlParameter("@JobId", MySqlDbType.Int32) { Value = jobId }, new MySqlParameter("@JobName", MySqlDbType.VarChar, 100) { Value = jobName } }; MySqlDataReader sqlDataReader = MySqlDbHelper.ExecuteReader(sqlText, parameters); if (sqlDataReader.Read()) { jobDetail = new CustomJobDetail(); ReadRecordData(sqlDataReader, jobDetail); } sqlDataReader.Close(); return(jobDetail); }
public int Insert(CustomJobDetail jobDetail) { jobDetail.UpdatedDate = DateTime.Now; jobDetail.CreatedDate = DateTime.Now; string sqlText = @"INSERT INTO custom_job_details (`JobId`, `JobName`, `JobGroup`, `JobChineseName`, `JobServiceURL`, `CreatedDate`, `UpdatedDate`, `StartDate`, `EndDate`, `ExecutedFreq`, `PageSize`, `Interval`, `State`, `Description`, `IntervalType`) VALUES (@JobId, @JobName, @JobGroup, @JobChineseName, @JobServiceURL, @CreatedDate, @UpdatedDate, @StartDate, @EndDate, @ExecutedFreq, @PageSize, @Interval, @State, @Description, @IntervalType); SELECT LAST_INSERT_ID();"; MySqlParameter[] parameters = { new MySqlParameter("@JobId", MySqlDbType.Int32) { Value = jobDetail.JobId }, new MySqlParameter("@JobName", MySqlDbType.VarChar, 100) { Value = jobDetail.JobName }, new MySqlParameter("@JobGroup", MySqlDbType.VarChar, 50) { Value = jobDetail.JobGroup }, new MySqlParameter("@JobChineseName", MySqlDbType.VarChar, 100) { Value = jobDetail.JobChineseName }, new MySqlParameter("@JobServiceURL", MySqlDbType.VarChar, 200) { Value = jobDetail.JobServiceURL }, new MySqlParameter("@CreatedDate", MySqlDbType.DateTime) { Value = jobDetail.CreatedDate }, new MySqlParameter("@UpdatedDate", MySqlDbType.DateTime) { Value = jobDetail.UpdatedDate }, new MySqlParameter("@StartDate", MySqlDbType.DateTime) { Value = jobDetail.StartDate }, new MySqlParameter("@EndDate", MySqlDbType.DateTime) { Value = jobDetail.EndDate }, new MySqlParameter("@PageSize", MySqlDbType.Int32) { Value = jobDetail.PageSize }, new MySqlParameter("@Interval", MySqlDbType.Int32) { Value = jobDetail.Interval }, new MySqlParameter("@IntervalType", MySqlDbType.Byte) { Value = jobDetail.IntervalType }, new MySqlParameter("@State", MySqlDbType.Byte) { Value = jobDetail.State }, new MySqlParameter("@Description", MySqlDbType.VarChar, 255) { Value = jobDetail.Description }, new MySqlParameter("@ExecutedFreq", MySqlDbType.Byte) { Value = jobDetail.ExecutedFreq } }; return(MySqlDbHelper.ExecuteScalar <int>(sqlText, parameters)); }
public int Update(CustomJobDetail jobDetail) { jobDetail.UpdatedDate = DateTime.Now; string sqlText = @"UPDATE custom_job_details SET `JobName` = @JobName, `JobGroup` = @JobGroup, `JobChineseName` = @JobChineseName, `JobServiceURL` = @JobServiceURL, `CreatedDate` = @CreatedDate, `UpdatedDate` = @UpdatedDate, `StartDate` = @StartDate, `EndDate` = @EndDate, `PageSize` = @PageSize, `Interval` = @Interval, `IntervalType`=@IntervalType, `State` = @State, `Description` = @Description, `ExecutedFreq`=@ExecutedFreq WHERE `JobId` = @JobId;"; MySqlParameter[] parameters = { new MySqlParameter("@JobId", MySqlDbType.Int32) { Value = jobDetail.JobId }, new MySqlParameter("@JobName", MySqlDbType.VarChar, 100) { Value = jobDetail.JobName }, new MySqlParameter("@JobGroup", MySqlDbType.VarChar, 50) { Value = jobDetail.JobGroup }, new MySqlParameter("@JobChineseName", MySqlDbType.VarChar, 100) { Value = jobDetail.JobChineseName }, new MySqlParameter("@JobServiceURL", MySqlDbType.VarChar, 200) { Value = jobDetail.JobServiceURL }, new MySqlParameter("@CreatedDate", MySqlDbType.DateTime) { Value = jobDetail.CreatedDate }, new MySqlParameter("@UpdatedDate", MySqlDbType.DateTime) { Value = jobDetail.UpdatedDate }, new MySqlParameter("@StartDate", MySqlDbType.DateTime) { Value = jobDetail.StartDate }, new MySqlParameter("@EndDate", MySqlDbType.DateTime) { Value = jobDetail.EndDate }, new MySqlParameter("@PageSize", MySqlDbType.Int32) { Value = jobDetail.PageSize }, new MySqlParameter("@Interval", MySqlDbType.Int32) { Value = jobDetail.Interval }, new MySqlParameter("@IntervalType", MySqlDbType.Byte) { Value = jobDetail.IntervalType }, new MySqlParameter("@State", MySqlDbType.Byte) { Value = jobDetail.State }, new MySqlParameter("@Description", MySqlDbType.VarChar, 255) { Value = jobDetail.Description }, new MySqlParameter("@ExecutedFreq", MySqlDbType.Byte) { Value = jobDetail.ExecutedFreq } }; return(MySqlDbHelper.ExecuteNonQuery(sqlText, parameters)); }
public int Insert(SqlServerConfigInfo dbConfigInfo) { dbConfigInfo.UpdatedDate = DateTime.Now; dbConfigInfo.CreatedDate = DateTime.Now; string sqlText = @"INSERT INTO `custom_db_config` (`Id`, `ServerAddress`, `DBName`, `UserName`, `Password`, `UpdatedDate`, `CreatedDate`, `EquipmentNum`, `PageSize`, `MaxCapacity`, `StoredType`, `DBType`, `ServerState`, `AuthenticatedType`) VALUES (@Id, @ServerAddress, @DBName, @UserName, @Password, @UpdatedDate, @CreatedDate, @EquipmentNum, @PageSize, @MaxCapacity, @StoredType, @DBType, @ServerState, @AuthenticatedType); SELECT LAST_INSERT_ID();"; MySqlParameter[] parameters = { new MySqlParameter("@Id", MySqlDbType.Int32) { Value = dbConfigInfo.Id }, new MySqlParameter("@DBName", MySqlDbType.VarChar, 45) { Value = dbConfigInfo.DBName }, new MySqlParameter("@ServerAddress", MySqlDbType.VarChar, 30) { Value = dbConfigInfo.ServerAddress }, new MySqlParameter("@UserName", MySqlDbType.VarChar, 45) { Value = dbConfigInfo.UserName }, new MySqlParameter("@Password", MySqlDbType.VarChar, 45) { Value = dbConfigInfo.Password }, new MySqlParameter("@UpdatedDate", MySqlDbType.DateTime) { Value = dbConfigInfo.UpdatedDate }, new MySqlParameter("@CreatedDate", MySqlDbType.DateTime) { Value = dbConfigInfo.CreatedDate }, new MySqlParameter("@EquipmentNum", MySqlDbType.VarChar, 25) { Value = dbConfigInfo.EquipmentNum }, new MySqlParameter("@PageSize", MySqlDbType.Int32) { Value = dbConfigInfo.PageSize }, new MySqlParameter("@MaxCapacity", MySqlDbType.Int32) { Value = dbConfigInfo.MaxCapacity }, new MySqlParameter("@StoredType", MySqlDbType.Byte) { Value = dbConfigInfo.StoredType }, new MySqlParameter("@DBType", MySqlDbType.Byte) { Value = dbConfigInfo.DBType }, new MySqlParameter("@ServerState", MySqlDbType.Byte) { Value = dbConfigInfo.ServerState }, new MySqlParameter("@AuthenticatedType", MySqlDbType.Byte) { Value = dbConfigInfo.AuthenticatedType } }; return(MySqlDbHelper.ExecuteScalar <int>(sqlText, parameters)); }
public int Update(SqlServerConfigInfo dbConfigInfo) { dbConfigInfo.UpdatedDate = DateTime.Now; string sqlText = @"UPDATE `custom_db_config` SET `ServerAddress` = @ServerAddress, `DBName` = @DBName, `UserName` = @UserName, `Password` = @Password, `UpdatedDate` = @UpdatedDate, `CreatedDate` = @CreatedDate, `EquipmentNum`=@EquipmentNum, `PageSize`=@PageSize, `MaxCapacity`=@MaxCapacity, `StoredType`=@StoredType, `DBType`=@DBType, `ServerState`=@ServerState, `AuthenticatedType`=@AuthenticatedType WHERE `Id` = @Id;"; MySqlParameter[] parameters = { new MySqlParameter("@Id", MySqlDbType.Int32) { Value = dbConfigInfo.Id }, new MySqlParameter("@DBName", MySqlDbType.VarChar, 45) { Value = dbConfigInfo.DBName }, new MySqlParameter("@ServerAddress", MySqlDbType.VarChar, 30) { Value = dbConfigInfo.ServerAddress }, new MySqlParameter("@UserName", MySqlDbType.VarChar, 45) { Value = dbConfigInfo.UserName }, new MySqlParameter("@Password", MySqlDbType.VarChar, 45) { Value = dbConfigInfo.Password }, new MySqlParameter("@UpdatedDate", MySqlDbType.DateTime) { Value = dbConfigInfo.UpdatedDate }, new MySqlParameter("@CreatedDate", MySqlDbType.DateTime) { Value = dbConfigInfo.CreatedDate }, new MySqlParameter("@EquipmentNum", MySqlDbType.VarChar, 25) { Value = dbConfigInfo.EquipmentNum }, new MySqlParameter("@PageSize", MySqlDbType.Int32) { Value = dbConfigInfo.PageSize }, new MySqlParameter("@MaxCapacity", MySqlDbType.Int32) { Value = dbConfigInfo.MaxCapacity }, new MySqlParameter("@StoredType", MySqlDbType.Byte) { Value = dbConfigInfo.StoredType }, new MySqlParameter("@DBType", MySqlDbType.Byte) { Value = dbConfigInfo.DBType }, new MySqlParameter("@ServerState", MySqlDbType.Byte) { Value = dbConfigInfo.ServerState }, new MySqlParameter("@AuthenticatedType", MySqlDbType.Byte) { Value = dbConfigInfo.AuthenticatedType } }; return(MySqlDbHelper.ExecuteNonQuery(sqlText, parameters)); }