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