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