C#开发ETL
控制模块(iJedha.ETL.Console)
该模块为windows service方案
- Program.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.ServiceProcess;
using System.Text;
using System.Threading.Tasks;
namespace iJedha.ETL.Console
{
static class Program
{
/// <summary>
/// 应用程序的主入口点。
/// </summary>
static void Main()
{
ServiceBase[] ServicesToRun;
ServicesToRun = new ServiceBase[]
{
new ETLService()
};
ServiceBase.Run(ServicesToRun);
}
}
}
- ETLService.cs
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Diagnostics;
using System.Linq;
using System.ServiceProcess;
using System.Text;
using System.Threading.Tasks;
using System.Configuration;
using iJedha.Log;
using System.Timers;
using System.Reflection;
using iJedha.Utility;
using System.Threading;
namespace iJedha.ETL.Console
{
/// <summary>
/// 数据同步WindowsService服务
/// </summary>
public partial class ETLService : ServiceBase
{
/// <summary>
///
/// </summary>
public ETLService()
{
InitializeComponent();
}
/// <summary>
/// 定时器
/// </summary>
private System.Timers.Timer timer1;
/// <summary>
/// WindowsService开始
/// </summary>
/// <param name="args"></param>
protected override void OnStart(string[] args)
{
timer1 = new System.Timers.Timer();
timer1.Interval = int.Parse(ConfigurationManager.AppSettings["Interval"]);
timer1.Elapsed += new System.Timers.ElapsedEventHandler(TimerEvent);
timer1.Enabled = true;
uLog.AddLog(LogLevel.Trace, "SyncInterfaceService Start!");
ThreadPool.SetMaxThreads(20, 20);
}
/// <summary>
/// WindowsService结束
/// </summary>
protected override void OnStop()
{
timer1.Enabled = false;
uLog.AddLog(LogLevel.Trace, "SyncInterfaceService Stop!");
}
private void TimerEvent(object sender, System.Timers.ElapsedEventArgs e)
{
try
{
string msg = string.Empty;
//获取要跑的服务基本设定信息
List<JobInfo> lstConfig = new List<JobInfo>();
lstConfig = Common.GetConfigInfo();
if (lstConfig.Count > 0)
{
foreach (JobInfo config in lstConfig)
{
//状态为启用和状态是S的设定
if (config.IsEnableJob.Trim().Equals("Y")&&config.JobStatus.ToUpper().Trim().Equals("S"))
{
//下次执行的时间必须小于当前时间
if (!string.IsNullOrEmpty(config.NextExcuteTime))
{
if (Convert.ToDateTime(config.NextExcuteTime) > DateTime.Now)
continue;
}
//当前线程池的线程状态：true为在运行，false或者不存在表示未运行
//设定线程状态为True
if (PublicAgent.dicCurrentThread.ContainsKey(config.JobID))
{
if ((bool)PublicAgent.dicCurrentThread[config.JobID] == true)
continue;
else PublicAgent.ChangeThreadStatus(config.JobID, true);
}
else
{
PublicAgent.AddThreadStatus(config.JobID, true);
}
//将当前任务加入到线程中去
ProcessAgent pa = new ProcessAgent(config);
Thread.Sleep(10);
bool bReturn = ThreadPool.QueueUserWorkItem(new WaitCallback(pa.Process));
if (bReturn == true)
{
Interlocked.Increment(ref PublicAgent.iRunThreadCnt);
}
}
}
}
}
catch (Exception ex)
{
uLog.AddLog(LogLevel.Error, "SyncInterfaceService Get an Error:" + ex.ToString());
}
finally
{
}
}
}
}
- APP.config
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<startup>
<supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.6.1" />
</startup>
<connectionStrings>
<!--測試-->
<add name="ConfigDB" connectionString="password=HA03TESTCAMRPT;User ID=HA03TESTCAMRPT;Data Source=(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = 10.89.164.89)(PORT = 1521))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = mesdb06)))" providerName="Oracle.ManagedDataAccess.Client"/>
<add name="EAPDB" connectionString="password=HA03TESTEAP;User ID=HA03TESTEAP;Data Source=(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = 10.89.164.89)(PORT = 1521)))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = mesdb06)))" providerName="Oracle.ManagedDataAccess.Client"/>
</connectionStrings>
<appSettings>
<add key="Interval" value="300000"/>
<add key ="Category" value="ETL"/>
</appSettings>
<system.data>
<DbProviderFactories>
<remove invariant="Oracle.ManagedDataAccess.Client"/>
<add name="ODP.NET, Managed Driver" invariant="Oracle.ManagedDataAccess.Client" description="Oracle Data Provider for .NET, Managed Driver" type="Oracle.ManagedDataAccess.Client.OracleClientFactory, Oracle.ManagedDataAccess, Version=4.122.18.3, Culture=neutral, PublicKeyToken=89b483f429c47342"/>
</DbProviderFactories>
</system.data>
</configuration>
工具模块(iJedha.Utility)
- BModuleBase.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using iJedha.Log;
using iJedha.Mail;
using Avary.Utils.DB;
namespace iJedha.Utility
{
/// <summary>
///
/// </summary>
/// <param name="param"></param>
/// <returns></returns>
public delegate List<RequestInfo> ExecuteHandle(ref JobInfo param);
/// <summary>
///
/// </summary>
public abstract class BModuleBase
{
/// <summary>
///
/// </summary>
public ExecuteHandle ExecuteProcess;
/// <summary>
///
/// </summary>
public DBTrans FromDBHelp = null;
/// <summary>
///
/// </summary>
public DBTrans ToDBHelp = null;
/// <summary>
///
/// </summary>
/// <param name="exeHandle"></param>
/// <param name="param"></param>
/// <returns></returns>
private List<RequestInfo> InvokeExecute(ExecuteHandle exeHandle, ref JobInfo param)
{
List<RequestInfo> lstReturn = new List<RequestInfo>();
try
{
if (exeHandle != null)
{
foreach (ExecuteHandle eh in exeHandle.GetInvocationList())
{
lstReturn.AddRange(eh(ref param));
if (lstReturn.Count > 0 && RequestProcess.CheckRequest(lstReturn) == RequestResult.NG) return lstReturn;
}
}
}
catch (Exception e)
{
lstReturn.Add(new RequestInfo(e.Message, e.StackTrace));
uLog.AddLog(LogLevel.Error, e.Message);
}
return lstReturn;
}
/// <summary>
///
/// </summary>
/// <param name="config"></param>
/// <returns></returns>
public List<RequestInfo> InitDBConnect(ref JobInfo config)
{
List<RequestInfo> lstReturn = new List<RequestInfo>();
string ToDBCon = "password ="+ config.TODBPASSWORD+ "; User ID = " + config.TODBUSERID + "; Data Source =("+config.TODBDATASOURCE+")";
string FromDBCon = "password = " + config.FROMPASSWORD + "; User ID = " + config.FROMUSERID + "; Data Source =("+config.FROMDATASOURCE + ")";
FromDBHelp = new DBTrans(config.FROMPROVIDERNAME, FromDBCon);
ToDBHelp = new DBTrans(config.TOPROVIDERNAME, ToDBCon);
return lstReturn;
}
/// <summary>
///
/// </summary>
/// <param name="config"></param>
/// <returns></returns>
public List<RequestInfo> Execute(ref JobInfo config)
{
List<RequestInfo> lstReturn = new List<RequestInfo>();
lstReturn = InitDBConnect(ref config);
try
{
lstReturn.AddRange(InvokeExecute(ExecuteProcess, ref config));
if (lstReturn.Count > 0 && RequestProcess.CheckRequest(lstReturn) == RequestResult.NG) return lstReturn;
}
catch (Exception e)
{
lstReturn.Add(new RequestInfo(e.Message, e.StackTrace));
uLog.AddLog(LogLevel.Error, e.Message);
}
finally
{
}
return lstReturn;
}
}
/// <summary>
///
/// </summary>
public interface IFBase
{
/// <summary>
///
/// </summary>
/// <param name="config"></param>
void ProcessModule(JobInfo config);
}
}
- Common.cs
using System;
using System.Globalization;
using System.IO;
using System.Linq;
using System.Net;
using System.Security.Cryptography;
using System.Text;
using System.Text.RegularExpressions;
using System.Collections.Generic;
using System.Xml;
using System.Reflection;
using System.Data;
using System.Configuration;
using iJedha.Log;
using Avary.Utils.DB;
namespace iJedha.Utility
{
/// <summary>
///
/// </summary>
public static class Common
{
/// <summary>
///
/// </summary>
/// <returns></returns>
public static List<JobInfo> GetConfigInfo()
{
List<JobInfo> config = new List<JobInfo>();
string sql = string.Format(@"select * from JOBINFO");
DBHelper dbHelp = new DBHelper("ConfigDB");
try
{
config = dbHelp.ExecuteList<JobInfo>(sql);
}
catch (Exception)
{
uLog.AddLog(LogLevel.Error, "获取配置信息异常");
}
finally
{
}
return config;
}
/// <summary>
/// 更新JOB时间和状态
/// </summary>
/// <param name="Acc">JOB对象</param>
/// <param name="Status">JOB状态</param>
/// <param name="errMsg">JOB执行信息</param>
/// <returns></returns>
public static List<RequestInfo> UpdateJobTimeandStatus(JobInfo Acc, string Status, string errMsg)
{
List<RequestInfo> ReList = new List<RequestInfo>();
try
{
if (Acc != null)
{
DBHelper dbHelper = new DBHelper("ConfigDB");
if (Acc.IsEnableJob.Trim().Equals("Y"))
{
if (Acc.JobStatus.Trim().Equals("S") && !string.IsNullOrEmpty(Acc.IntervalTime.ToString()))
{
string sql = string.Format(@"Update JobInfo a set a.jobstatus='{0}' , a.lastexcutetime = '{1}'
, a.nextexcutetime='{2}' , a.jobmessage='{3}' where a.JobID='{4}'", Status, Acc.LastExcuteTime, Acc.NextExcuteTime, errMsg, Acc.JobID);
dbHelper.ExecuteNonQuery(sql);
}
}
}
}
catch (Exception)
{
ReList.Add(new RequestInfo("", ""));
}
return ReList;
}
}
}
- DBTrans.cs
Avary.Utils.DB.dll
using System;
using System.Collections.Generic;
using System.Data.Common;
using System.Linq;
using System.Web;
using Avary.Utils.DB;
namespace iJedha.Utility
{
/// <summary>
///
/// </summary>
public class DBTrans
{
private DbConnection conn;
private DbCommand Cmd;
private DbTransaction Tran;
/// <summary>
///
/// </summary>
public DBHelper dbHelper = null;
/// <summary>
///
/// </summary>
/// <param name="providerName"></param>
/// <param name="connectionString"></param>
public DBTrans(string providerName, string connectionString)
{
dbHelper = new DBHelper(providerName, connectionString);
conn = dbHelper.CreateConnection();
conn.Open();
Cmd = conn.CreateCommand();
Tran = conn.BeginTransaction();
Cmd.Connection = conn;
//Cmd.CommandTimeout = 5;
Cmd.Transaction = Tran;
}
/// <summary>
/// 批量执行
/// </summary>
/// <param name="lst"></param>
/// <returns></returns>
public bool ExcuteSqlList(List<string> lst)
{
try
{
foreach (string s in lst)
{
ExecuteCommand(s);
}
Commit();
}
catch (Exception e)
{
Rollback();
return false;
}
finally
{
Dispose();
}
return true;
}
public bool ExcuteSql(string sql)
{
try
{
ExecuteCommand(sql);
Commit();
}
catch (Exception)
{
Rollback();
return false;
}
finally
{
Dispose();
}
return true;
}
private void Commit()
{
Tran.Commit();
conn.Close();
}
private void Rollback()
{
Tran.Rollback();
conn.Close();
}
private void ExecuteCommand(string CmdStr)
{
Cmd.CommandText = CmdStr;
Cmd.ExecuteNonQuery();
}
private void Dispose()
{
conn.Close();
}
}
}
- JobInfo.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace iJedha.Utility
{
/// <summary>
///
/// </summary>
public class JobInfo
{
/// <summary>
/// 唯一标识符
/// </summary>
public string JobID { get; set; }
/// <summary>
/// Job名称
/// </summary>
public string JobName { get; set; }
/// <summary>
/// Job的动态运行库
/// </summary>
public string JobDLL { get; set; }
/// <summary>
/// Job类型
/// </summary>
public string JobClass { get; set; }
/// <summary>
///
/// </summary>
public string FROMPROVIDERNAME { get; set; }
/// <summary>
///
/// </summary>
public string FROMUSERID { get; set; }
/// <summary>
///
/// </summary>
public string FROMPASSWORD { get; set; }
/// <summary>
///
/// </summary>
public string FROMDATASOURCE { get; set; }
/// <summary>
///
/// </summary>
public string TOPROVIDERNAME { get; set; }
/// <summary>
///
/// </summary>
public string TODBUSERID { get; set; }
/// <summary>
///
/// </summary>
public string TODBPASSWORD { get; set; }
/// <summary>
///
/// </summary>
public string TODBDATASOURCE { get; set; }
/// <summary>
/// 间隔时间
/// </summary>
public int IntervalTime { get; set; }
/// <summary>
/// 上一次执行时间
/// </summary>
public string LastExcuteTime { get; set; }
/// <summary>
/// 下一次执行时间
/// </summary>
public string NextExcuteTime { get; set; }
/// <summary>
/// Job状态 S=成功(上一次运行) F=错误(上一次运行) R=运行中(当前任务状态)
/// </summary>
public string JobStatus { get; set; }
/// <summary>
/// Y 启用 N不启用
/// </summary>
public string IsEnableJob { get; set; }
/// <summary>
/// Job允许情况消息
/// </summary>
public string JobMessage { get; set; }
/// <summary>
/// 类别
/// </summary>
public string Category { get; set; }
/// <summary>
/// 上一次执行时间
/// </summary>
public string LastDataStartTime { get; set; }
/// <summary>
/// 上一次执行时间
/// </summary>
public string NextDataStartTime { get; set; }
}
}
- PublicAgent.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading;
using iJedha.Log;
using System.Reflection;
namespace iJedha.Utility
{
/// <summary>
///
/// </summary>
public static class PublicAgent
{
/// <summary>
/// 当前线程
/// </summary>
public static Dictionary<string, bool> dicCurrentThread = new Dictionary<string, bool>();
/// <summary>
/// 当前线程
/// </summary>
public static int iRunThreadCnt = 0;
/// <summary>
/// 变更线程
/// </summary>
/// <param name="functionID"></param>
/// <param name="bstatus"></param>
public static void ChangeThreadStatus(string functionID, bool bstatus)
{
lock (dicCurrentThread)
{
dicCurrentThread[functionID] = bstatus;
}
}
/// <summary>
/// 添加新线程
/// </summary>
/// <param name="functionID"></param>
/// <param name="bstatus"></param>
public static void AddThreadStatus(string functionID, bool bstatus)
{
lock (dicCurrentThread)
{
dicCurrentThread.Add(functionID, bstatus);
}
}
}
/// <summary>
/// 执行
/// </summary>
public class ProcessAgent
{
/// <summary>
///
/// </summary>
private JobInfo config;
/// <summary>
/// 服务配置
/// </summary>
/// <param name="_config"></param>
public ProcessAgent(JobInfo _config)
{
config = _config;
}
/// <summary>
/// 执行线程方法
/// </summary>
/// <param name="config"></param>
public void ProcessModule(JobInfo config)
{
uLog.AddLog(LogLevel.Trace, config.JobID + ":Start");
List<RequestInfo> lstRequestInfo = new List<RequestInfo>();
Dictionary<string, object> dicParam = new Dictionary<string, object>();
try
{
//反射获取dll中执行方法，传入参数执行
string strPath = System.AppDomain.CurrentDomain.BaseDirectory;
Assembly assembly = Assembly.LoadFile(strPath + config.JobDLL);
Type type = assembly.GetType(config.JobClass);
BModuleBase @object = (BModuleBase)Activator.CreateInstance(type);
JobInfo param = config;
List<RequestInfo> lstRquestInfo = @object.Execute(ref param);
if (lstRquestInfo.Count > 0)
{
//
DateTime dtNow = Convert.ToDateTime(config.NextExcuteTime);
uLog.AddLog(LogLevel.Error, lstRquestInfo[0].Message);
//config.NextExcuteTime = dtNow.AddMinutes(Convert.ToInt32(config.IntervalTime)).ToString("yyyy-MM-dd HH:mm:ss");
//更新Job任务状态
Common.UpdateJobTimeandStatus(config, "F", lstRquestInfo[0].Message);
}
else
{
uLog.AddLog(LogLevel.Trace, config.JobName + "执行成功！");
DateTime dtNow = Convert.ToDateTime(config.NextExcuteTime);
config.LastExcuteTime = config.NextExcuteTime;
config.NextExcuteTime = dtNow.AddMinutes(Convert.ToInt32(config.IntervalTime)).ToString("yyyy-MM-dd HH:mm:ss");
//更新Job任务状态
Common.UpdateJobTimeandStatus(config, "S","");
}
}
catch (Exception ex)
{
uLog.AddLog(LogLevel.Error, config.JobName + ex.Message);
}
finally
{
uLog.AddLog(LogLevel.Trace, config.JobName + ":End");
}
}
/// <summary>
///
/// </summary>
/// <param name="obj"></param>
public void Process(object obj)
{
try
{
ProcessModule(config);
}
catch (Exception ex)
{
uLog.AddLog(LogLevel.Error,ex.Message);
}
finally
{
PublicAgent.ChangeThreadStatus(config.JobID, false);
Interlocked.Decrement(ref PublicAgent.iRunThreadCnt);
}
}
}
}
- RequestInfo.cs
using System;
using System.Collections.Generic;
using System.Text;
namespace iJedha.Utility
{
/// <summary>
///
/// </summary>
public enum RequestResult
{
/// <summary>
///
/// </summary>
OK,
/// <summary>
///
/// </summary>
NG
}
/// <summary>
///
/// </summary>
public struct RequestInfo
{
/// <summary>
///
/// </summary>
public string Code;
/// <summary>
///
/// </summary>
public string Message;
/// <summary>
///
/// </summary>
public string Stack;
/// <summary>
///
/// </summary>
public RequestResult requestResult;
/// <summary>
///
/// </summary>
public Dictionary<string, object> dicReturn;
/// <summary>
///
/// </summary>
/// <param name="_message"></param>
/// <param name="_stack"></param>
/// <param name="_result"></param>
/// <param name="_dicReturn"></param>
public RequestInfo(string _message, string _stack, RequestResult _result = RequestResult.NG, Dictionary<string, object> _dicReturn = null)
{
this.Code = "";
this.Message = _message;
this.Stack = _stack;
this.requestResult = _result;
this.dicReturn = _dicReturn;
}
/// <summary>
///
/// </summary>
/// <param name="_Code"></param>
/// <param name="_message"></param>
/// <param name="_stack"></param>
/// <param name="_result"></param>
/// <param name="_dicReturn"></param>
public RequestInfo(string _Code, string _message, string _stack, RequestResult _result = RequestResult.NG, Dictionary<string, object> _dicReturn = null)
{
this.Code = _Code;
this.Message = _message;
this.Stack = _stack;
this.requestResult = _result;
this.dicReturn = _dicReturn;
}
}
/// <summary>
///
/// </summary>
public static class RequestProcess
{
/// <summary>
///
/// </summary>
/// <param name="lsRequest"></param>
/// <returns></returns>
public static RequestResult CheckRequest(List<RequestInfo> lsRequest)
{
RequestResult requestResult;
requestResult = RequestResult.OK;
if (lsRequest != null && lsRequest.Count > 0)
{
for (int i = 0; i < lsRequest.Count; i++)
{
if (lsRequest[i].requestResult == RequestResult.NG)
{
requestResult = RequestResult.NG;
break;
}
}
}
return requestResult;
}
}
}
日志模块(iJedha.Log)
- Log.cs
using System;
using System.Collections.Generic;
using System.Xml;
using System.IO;
using System.Threading;
using System.Diagnostics;
using System.Text;
using NLog;
namespace iJedha.Log
{
/// <summary>
///
/// </summary>
public enum LogLevel
{
/// <summary>
///
/// </summary>
Trace,
/// <summary>
///
/// </summary>
Warning,
/// <summary>
///
/// </summary>
Error,
}
/// <summary>
///
/// </summary>
public struct LogStruct
{
/// <summary>
///
/// </summary>
public string Message;
/// <summary>
///
/// </summary>
public LogLevel logLevel;
/// <summary>
///
/// </summary>
public string DateTime;
}
/// <summary>
///
/// </summary>
public static class uLog
{
/// <summary>
///
/// </summary>
private static NLog.Logger _logger = NLog.LogManager.GetCurrentClassLogger();
/// <summary>
///
/// </summary>
/// <param name="loglevel"></param>
/// <param name="strMessage"></param>
/// <param name="strTraceStack"></param>
public static void AddLog(LogLevel loglevel, string strMessage,string strTraceStack="")
{
strMessage = string.IsNullOrEmpty(strTraceStack) ? strMessage : strMessage + "---------" + strTraceStack;
if (loglevel == LogLevel.Trace)
{
_logger.Trace(strMessage );
}
else
{
_logger.Trace(strMessage);
_logger.Error(strMessage);
}
}
}
}
浙公网安备 33010602011771号