/// <summary>
/// 执行一条SQL语句，返回第一行第一列object
/// </summary>
/// <param name="dbID"></param>
/// <param name="sql"></param>
/// <returns></returns>
public object ExecuteScalar(int dbID, string sql)
{
object obj = 0;
if (dbID < 0)
{
throw new Exception("错误的数据库ID");
}
if (string.IsNullOrWhiteSpace(sql))
{
throw new Exception("SQL语句不可为空");
}
using (BDBHelper dbHelper = GetBDBHelper(dbID))
{
try
{
obj = dbHelper.ExecuteScalar(sql);
}
catch (Exception ex)
{
throw new Exception("在数据库" + dbID + "执行SQL查询出错：" + ex.Message);
}
}
return(obj);
}
/// <summary>
/// 限定和处理待
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private static void DoWork3(object sender, DoWorkEventArgs e)
{
while (Main.IsRun)
{
try
{
BLog.Write(BLog.LogLevel.INFO, "开始处理限定的监控文件夹任务");
#region 查询当前非并行执行中的任务实例的数量，如果数量小于MonitFolderCount，则补齐执行中的数量。
//修改等待中的任务为执行中(补齐差量)
string sql = string.Format(@"SELECT COUNT (1)
FROM EM_SCRIPT_CASE
WHERE IS_SUPERVENE <> 1 AND RUN_STATUS = 2");
object obj = null;
using (BDBHelper dbop = new BDBHelper())
{
obj = dbop.ExecuteScalar(sql);//获得执行中的非并行任务数
BLog.Write(BLog.LogLevel.INFO, "获取到执行中任务数：" + obj);
if (obj != null && Convert.ToInt32(obj) < Main.MaxMonitCount) //当执行中的数量小于MaxMonitCount
{
int difCount = Main.MaxMonitCount - Convert.ToInt32(obj); //差量
sql = string.Format(@"SELECT COUNT(1)
FROM (SELECT A.ID,
ROW_NUMBER () OVER (ORDER BY ID) RN
FROM EM_SCRIPT_CASE A WHERE RUN_STATUS = 1)
WHERE RN <= {0}", difCount);
object o2 = o2 = dbop.ExecuteScalar(sql);
BLog.Write(BLog.LogLevel.INFO, "按差量获取等待中任务数：" + o2);
if (o2 != null && Convert.ToInt32(o2) > 0)
{
sql = string.Format(@"MERGE INTO EM_SCRIPT_CASE A
USING (SELECT ID
FROM (SELECT ID,
ROW_NUMBER ()
OVER ( ORDER BY ID)
RN
FROM EM_SCRIPT_CASE
WHERE RUN_STATUS = 1)
WHERE RN <= {0}) B
ON (A.ID = B.ID)
WHEN MATCHED
THEN
UPDATE SET RUN_STATUS = 2", difCount);
dbop.ExecuteNonQuery(sql);//修改等待的任务为执行中
BLog.Write(BLog.LogLevel.INFO, "执行把等待中任务改为执行中");
}
}
dbop.Close();//关闭连接
}
#endregion
}
catch (Exception ex)
{
BLog.Write(BLog.LogLevel.ERROR, "限定监控的文件夹任务出现异常：" + ex.ToString());
}
Thread.Sleep(100000);//100秒执行一次
}
}