/// <summary>
/// 執行非查詢的SQL語句
/// </summary>
/// <param name="SQL">SQL語句</param>
/// <param name="DBUrl">數據庫鏈接地址</param>
/// <returns></returns>
public int GetNonQueryBySQL(string SQL, string DBUrl)
{
if (DBUrl.ToString().Trim() == "" || DBUrl == null)
{
throw new Exception("數據庫鏈接地址不能為空");
}
// 获取与数据库的连接对象並且绑定连接字符串
Oracle.ManagedDataAccess.Client.OracleConnection conn = new Oracle.ManagedDataAccess.Client.OracleConnection(DBUrl);
conn.Open(); //打開資源
//获取数据库操作对象
Oracle.ManagedDataAccess.Client.OracleCommand cmd = conn.CreateCommand();
try
{
cmd.CommandText = SQL;
int num = cmd.ExecuteNonQuery();
cmd.Dispose(); //釋放資源
conn.Dispose(); //釋放資源
conn.Close(); //關閉
return(num);
}
catch (Exception ex)
{
cmd.Dispose(); //釋放資源
conn.Dispose(); //釋放資源
conn.Close(); //關閉
throw ex;
}
}
/// <summary>
/// 執行SQL語句返回DataTable
/// </summary>
/// <param name="SQL">SQL語句</param>
/// <param name="DBUrl">數據庫鏈接地址</param>
/// <returns></returns>
public DataTable GetDataTableBySQL(string SQL, string DBUrl)
{
if (DBUrl.ToString().Trim() == "" || DBUrl == null)
{
throw new Exception("數據庫鏈接地址不能為空");
}
// 获取与数据库的连接对象並且绑定连接字符串
Oracle.ManagedDataAccess.Client.OracleConnection conn = new Oracle.ManagedDataAccess.Client.OracleConnection(DBUrl);
conn.Open(); //打開資源
//获取数据库操作对象
Oracle.ManagedDataAccess.Client.OracleCommand cmd = conn.CreateCommand();
try
{
cmd.CommandText = SQL;
Oracle.ManagedDataAccess.Client.OracleDataAdapter adapter = new Oracle.ManagedDataAccess.Client.OracleDataAdapter(cmd);
DataTable dataTable = new DataTable();
adapter.Fill(dataTable);
dataTable.TableName = "數據集";
cmd.Dispose(); //釋放資源
conn.Dispose(); //釋放資源
conn.Close(); //關閉
return(dataTable);
}
catch (Exception ex)
{
cmd.Dispose(); //釋放資源
conn.Dispose(); //釋放資源
conn.Close(); //關閉
throw ex;
}
}
/// <summary>
/// 調用存儲返回String字符串信息和DataTable數據表格（最後兩個位置必須為返回參數，一個為輸出字符串另一個為游標，位置不能顛倒）
/// <para>obj使用方法：new{ v_data=value, v_data1=value1,out_string="",out_cursor=""}</para>
/// <para>注意：obj中v_data為存儲參數名稱，value為對應的值，out_string為輸出參數不需要輸入值， out_cursor為游標不需要輸入值</para>
/// </summary>
/// <param name="storageName">存儲名稱</param>
/// <param name="DBUrl">數據庫鏈接地址</param>
/// <param name="obj">存儲參數對象</param>
/// <param name="dataTable">返回結果集</param>
/// <returns></returns>
public string GetStringAndDataTableByStorageName(string storageName, string DBUrl, object obj, out DataTable dataTable)
{
if (DBUrl.ToString().Trim() == "" || DBUrl == null)
{
throw new Exception("數據庫鏈接地址不能為空");
}
// 获取与数据库的连接对象並且绑定连接字符串
Oracle.ManagedDataAccess.Client.OracleConnection conn = new Oracle.ManagedDataAccess.Client.OracleConnection(DBUrl);
conn.Open();//打開資源
//获取数据库操作对象
Oracle.ManagedDataAccess.Client.OracleCommand cmd = conn.CreateCommand();
try
{
cmd.CommandText = storageName; //存儲名稱
cmd.CommandType = CommandType.StoredProcedure;
PropertyInfo[] properties = obj.GetType().GetProperties(BindingFlags.Instance | BindingFlags.Public); //獲取object中的字段名和值
for (int i = 0; i < properties.Length; i++)
{
if (i == (properties.Length - 2))
{ //設定輸出的類型和值
cmd.Parameters.Add(properties[i].Name, Oracle.ManagedDataAccess.Client.OracleDbType.Varchar2, short.MaxValue).Direction = ParameterDirection.Output;
cmd.Parameters[properties[i].Name].Value = DBNull.Value; //賦值
}
else if (i == (properties.Length - 1))
{ //設定輸出的類型和值
cmd.Parameters.Add(properties[i].Name, Oracle.ManagedDataAccess.Client.OracleDbType.RefCursor).Direction = ParameterDirection.Output;
cmd.Parameters[properties[i].Name].Value = DBNull.Value; //賦值
}
else
{ //設定輸入的類型和值
cmd.Parameters.Add(properties[i].Name, GetOracleDbType(properties[i], obj)).Direction = ParameterDirection.Input;
cmd.Parameters[properties[i].Name].Value = properties[i].GetValue(obj, null); //賦值
}
}
DataTable dt = new DataTable();
dt.TableName = "數據集";
Oracle.ManagedDataAccess.Client.OracleDataAdapter oda = new Oracle.ManagedDataAccess.Client.OracleDataAdapter(cmd);
oda.Fill(dt);
dataTable = dt; //返回數據結果集
string message = cmd.Parameters[properties[properties.Length - 2].Name].Value.ToString(); //獲取輸出的字符串
cmd.Dispose(); //釋放資源
conn.Dispose(); //釋放資源
conn.Close(); //關閉
return(message);
}
catch (Exception ex)
{
cmd.Dispose(); //釋放資源
conn.Dispose(); //釋放資源
conn.Close(); //關閉
throw ex;
}
}
/// <summary>
/// 調用存儲返回String字符串信息（最後一個位置必須為String類型字符，位置不能顛倒）
/// <para>obj使用方法：new{ v_data=value, v_data1=value1,out_string=""}</para>
/// <para>注意：obj中v_data為存儲參數名稱，value為對應的值，out_string為輸出參數不需要輸入值</para>
/// </summary>
/// <param name="storageName"></param>
/// <param name="DBUrl"></param>
/// <param name="obj"></param>
/// <returns></returns>
public string GetStringDataByStorageName(string storageName, string DBUrl, object obj)
{
if (DBUrl.ToString().Trim() == "" || DBUrl == null)
{
throw new Exception("數據庫鏈接地址不能為空");
}
Oracle.ManagedDataAccess.Client.OracleConnection conn = new Oracle.ManagedDataAccess.Client.OracleConnection(DBUrl);
conn.Open();
//获取数据库操作对象
Oracle.ManagedDataAccess.Client.OracleCommand cmd = conn.CreateCommand();
try
{
cmd.CommandText = storageName; //存儲名稱
cmd.CommandType = CommandType.StoredProcedure;
PropertyInfo[] properties = obj.GetType().GetProperties(BindingFlags.Instance | BindingFlags.Public); //獲取object中的字段名和值
for (int i = 0; i < properties.Length; i++)
{
if (i == (properties.Length - 1))
{ //設定輸出的類型和值
cmd.Parameters.Add(properties[i].Name, Oracle.ManagedDataAccess.Client.OracleDbType.Varchar2, short.MaxValue).Direction = ParameterDirection.Output;
cmd.Parameters[properties[i].Name].Value = DBNull.Value; //賦值
}
else
{ //設定輸入的類型和值
cmd.Parameters.Add(properties[i].Name, GetOracleDbType(properties[i], obj)).Direction = ParameterDirection.Input;
cmd.Parameters[properties[i].Name].Value = properties[i].GetValue(obj, null); //賦值
}
}
cmd.ExecuteNonQuery();
string message = cmd.Parameters[properties[properties.Length - 1].Name].Value.ToString(); //獲取返回的值
cmd.Dispose(); //釋放資源
conn.Dispose(); //釋放資源
conn.Close(); //關閉
return(message);
}
catch (Exception ex)
{
cmd.Dispose(); //釋放資源
conn.Dispose(); //釋放資源
conn.Close(); //關閉
throw ex;
}
}
///
///
/// </summary>
/// <param name="parmAppID"></param>
/// <returns></returns>
///
///
public DataTable GetMatchingReports(int?p_ApplicationID)
{
var con = ConfigurationManager.ConnectionStrings["OraReports"].ConnectionString;
Oracle.ManagedDataAccess.Client.OracleConnection connection = new Oracle.ManagedDataAccess.Client.OracleConnection(con);
try
{
OracleCommand command = new OracleCommand("ZZUAB_WAM.wfuab_Get_Application_Reports", connection);
command.CommandType = CommandType.StoredProcedure;
command.BindByName = true;
command.Parameters.Add("p_ApplicationID", p_ApplicationID).OracleDbType = OracleDbType.Varchar2;
command.Parameters.Add("p_recordSet ", OracleDbType.RefCursor).Direction = ParameterDirection.Output;
OracleDataAdapter adapter = new OracleDataAdapter(command);
DataSet result = new DataSet();
DataTable dt = new DataTable();
connection.Open();
adapter.Fill(result);
var fill = result.Tables[0];
return(fill);
}
catch (Exception e)
{
Console.WriteLine(e);
throw;
}
finally
{
connection.Dispose();
connection.Close();
}
}
/// <summary>
/// 執行多條SQL語句，實現數據庫事務。
/// </summary>
/// <param name="SQLStringList">多條SQL語句</param>
/// <param name="DBUrl">數據庫鏈接地址</param>
public int GetNonQueryByManySQL(ArrayList SQLStringList, string DBUrl)
{
if (DBUrl.ToString().Trim() == "" || DBUrl == null)
{
throw new Exception("數據庫鏈接地址不能為空");
}
using (Oracle.ManagedDataAccess.Client.OracleConnection conn = new Oracle.ManagedDataAccess.Client.OracleConnection(DBUrl))
{
conn.Open();
Oracle.ManagedDataAccess.Client.OracleCommand cmd = new Oracle.ManagedDataAccess.Client.OracleCommand();
cmd.Connection = conn;
Oracle.ManagedDataAccess.Client.OracleTransaction tx = conn.BeginTransaction();
cmd.Transaction = tx;
try
{
int num = 0;
for (int i = 0; i < SQLStringList.Count; i++)
{
string SQL = SQLStringList[i].ToString();//獲取SQL語句
if (SQL.Trim().Length > 1)
{
cmd.CommandText = SQL;
num = cmd.ExecuteNonQuery();
}
tx.Commit(); //提交事務
cmd.Dispose(); //釋放資源
conn.Dispose(); //釋放資源
conn.Close(); //關閉
}
return(num); //返回執行結果數量
}
catch (Oracle.ManagedDataAccess.Client.OracleException E)
{
tx.Rollback();//事務回滾
throw new Exception(E.Message);
}
}
}
protected override void ExecuteInternal(JobExecutionContext context)
{
string connStr = ConfigurationManager.AppSettings["mergeData"];
OracleConnection conn = new OracleConnection(connStr);
conn.Open();
OracleTransaction tran = conn.BeginTransaction(System.Data.IsolationLevel.ReadCommitted);
var startTime = DateTime.UtcNow;
var logEntity = new SCHEDULERLOG {
STARTTIME = startTime
};
var strInfo = new StringBuilder();
strInfo.AppendFormat("Source [Email:{0},Title:{1}]\n", ConfigurationManager.AppSettings["LongZhongSenderFilter"], ConfigurationManager.AppSettings["LongZhongTitleFilter"]);
strInfo.AppendFormat("Destination [Type: Oracle DB, Address: {0}]\n", connStr);
var lastSyncTime = Convert.ToDateTime("2016-11-16");
using (var cneEntities = new CnEEntities())
{
var date =
cneEntities.SCHEDULERLOGs.Where(x => x.STATUS == 0 && x.JOBTYPE == JobType).Select(
x => (DateTime?)x.STARTTIME).Max();
if (date != null)
{
//ToGMT8
lastSyncTime = date.Value.AddHours(8);
}
}
try
{
#region 执行数据同步程序
var attFetcherSever = ConfigurationManager.AppSettings["CnEServer"];
int attFetcherPort = int.Parse(ConfigurationManager.AppSettings["CnEPort"]);
bool attFetcherUsingSsl = Convert.ToBoolean(ConfigurationManager.AppSettings["CnEUsingSsl"]);
string attFetcherUserName = ConfigurationManager.AppSettings["CnEUserName"];
string attFetcherPassWord = ConfigurationManager.AppSettings["CnEPassWord"];
string attFetcherSavingPath = ConfigurationManager.AppSettings["LongZhongSavingPath"];
var attFetcher = new IMAP4AttFetcher(attFetcherSever,
attFetcherPort,
attFetcherUsingSsl,
attFetcherUserName,
attFetcherPassWord,
attFetcherSavingPath);
var attFetcherSenderFilter = new SenderFilter();
/*Filter example. If there are more filters , please write like below.*/
string[] senders = ConfigurationManager.AppSettings["LongZhongSenderFilter"].Split(';');
foreach (var sender in senders)
{
var senderRegex = new Regex(sender, RegexOptions.Compiled);
attFetcherSenderFilter.SetRule(senderRegex);
}
attFetcher.AppendFilter(attFetcherSenderFilter);
var attFetcherTitleFilter = new TitleFilter();
var titleRegex = new Regex(ConfigurationManager.AppSettings["LongZhongTitleFilter"], RegexOptions.Compiled);
attFetcherTitleFilter.SetRule(titleRegex);
attFetcher.AppendFilter(attFetcherTitleFilter);
attFetcher.Execute(lastSyncTime);
/*Get the saved attachment names.*/
var attachmentFileNames = attFetcher.GetAttachmentFileNames();
//List<string> attachmentFileNames = Directory.GetFiles(@"C:\DataFeedApp\Scheduler\CnE\LongZhong").ToList<string>();
foreach (var tempattachname in attachmentFileNames)
{
LongZhongExcelManager manager = new LongZhongExcelManager();
manager.GetCellsByFirstSheet(tempattachname, strInfo);
}
var fold = new DirectoryInfo(attFetcherSavingPath);
if (fold.Exists)
{
FileInfo[] files = fold.GetFiles();
foreach (FileInfo f in files)//删除目录下所有文件
{
f.Delete();
}
}
if (attachmentFileNames.Count > 0)
{
MergeData merge = new MergeData();
merge.ExecuteLongZhongExcel(conn, tran);
tran.Commit();
strInfo.Append("Execute Procedure : CREATEMAXENERGYYIELDTABLE, CREATEMAXCHEMISTRYOUTPUTTABLE \r\n");
strInfo.Append("Insert Into Table : GDT_CHEMISTRYOUTPUTMAX, GDT_ENERGYYIELDMAX \r\n");
}
else
{
strInfo.Append("No files found\r\n");
}
#endregion
var endTime = DateTime.UtcNow;
logEntity.ENDTIME = endTime;
logEntity.JobStatus = JobStatus.Success;
logEntity.RUNDETAIL = strInfo.ToString();
WriteLogEntity(logEntity);
//merge data from temp to persistence
}
catch (Exception exception)
{
tran.Rollback();
logEntity.ENDTIME = DateTime.UtcNow.AddDays(-1);
logEntity.JobStatus = JobStatus.Fail;
logEntity.RUNDETAIL = strInfo + "\n" + exception;
WriteLogEntity(logEntity);
}
finally
{
conn.Close();
conn.Dispose();
}
}
public void GetData()
{
try
{
string oradb = "";
if (Session["PERMA"].ToString().Contains("HPT"))
{
oradb = ConfigurationManager.ConnectionStrings["BDHPTQLD"].ConnectionString;
}
else if (Session["PERMA"].ToString().Contains("HLU"))
{
oradb = ConfigurationManager.ConnectionStrings["BDHLUQLD"].ConnectionString;
}
else
{
oradb = ConfigurationManager.ConnectionStrings["BDHSULQLD"].ConnectionString;
}
OracleConnection conn = new Oracle.ManagedDataAccess.Client.OracleConnection(oradb); // C#
conn.Open();
OracleCommand cmd = new OracleCommand();
cmd.Connection = conn;
cmd.CommandText = "select count(*) from MEDICO.V_DASH_DESLOC_ATP_V3";
cmd.CommandType = CommandType.Text;
OracleDataReader dr = cmd.ExecuteReader();
if (dr.HasRows)
{
while (dr.Read())
{
Globals.nDoentes = dr["COUNT(*)"].ToString();
}
}
else
{
Globals.nDoentes = "Sem Dados";
}
OracleCommand cmd2 = new OracleCommand();
cmd2.Connection = conn;
cmd2.CommandText = "select count(*) from MEDICO.V_DASH_DESLOC_ATP_V3 where COR_TRIAGEM IS NULL";
cmd2.CommandType = CommandType.Text;
OracleDataReader dr2 = cmd2.ExecuteReader();
if (dr2.HasRows)
{
while (dr2.Read())
{
Globals.semTriagem = dr2["COUNT(*)"].ToString();
}
}
else
{
Globals.semTriagem = "Sem Dados";
}
OracleCommand cmd3 = new OracleCommand();
cmd3.Connection = conn;
cmd3.CommandText = "select count(*) from MEDICO.V_DASH_DESLOC_ATP_V3 where DT_NOTA_MEDICA IS NULL";
cmd3.CommandType = CommandType.Text;
OracleDataReader dr3 = cmd3.ExecuteReader();
if (dr3.HasRows)
{
while (dr3.Read())
{
Globals.semNota = dr3["COUNT(*)"].ToString();
}
}
else
{
Globals.semNota = "Sem Dados";
}
conn.Dispose();
}
catch (Exception err)
{
string var = err.InnerException.ToString();
Globals.nDoentes = "Sem Dados";
Globals.semNota = "Sem Dados";
Globals.semTriagem = "Sem Dados";
}
}
public static void ExcuteMailSync(string jobType, DateTime syncStartTime, string attFetcherSavingPath, string senderFilter, string titleFilter, string userName, string password
, Action <SCHEDULERLOG> saveLog
, Action <List <string>, StringBuilder> syncCallback
, Action <OracleConnection, OracleTransaction> mergeCallback
)
{
string connStr = ConfigurationManager.AppSettings["mergeData"];
OracleConnection conn = new OracleConnection(connStr);
conn.Open();
OracleTransaction tran = conn.BeginTransaction(System.Data.IsolationLevel.ReadCommitted);
var startTime = DateTime.UtcNow;
var logEntity = new SCHEDULERLOG {
STARTTIME = startTime
};
var strInfo = new StringBuilder();
var lastSyncTime = syncStartTime;
using (var cneEntities = new CnEEntities())
{
var date =
cneEntities.SCHEDULERLOGs.Where(x => x.STATUS == 0 && x.JOBTYPE == jobType).Select(
x => (DateTime?)x.STARTTIME).Max();
if (date != null)
{
lastSyncTime = date.Value;
}
}
try
{
#region 执行数据同步程序
strInfo.AppendFormat("Source [Type: Email , Sender: {0} ,Title {1} ]\n",
senderFilter, titleFilter);
strInfo.AppendFormat("Destination [Type: {0} Address: {1}]\n", "Oracle",
connStr);
var attFetcherSever = ConfigurationManager.AppSettings["CnEServer"];
int attFetcherPort = int.Parse(ConfigurationManager.AppSettings["CnEPort"]);
bool attFetcherUsingSsl = Convert.ToBoolean(ConfigurationManager.AppSettings["CnEUsingSsl"]);
string attFetcherUserName = userName;
string attFetcherPassWord = password;
var attFetcher = new IMAP4AttFetcher(attFetcherSever,
attFetcherPort,
attFetcherUsingSsl,
attFetcherUserName,
attFetcherPassWord,
attFetcherSavingPath);
var attFetcherSenderFilter = new SenderFilter();
/*Filter example. If there are more filters , please write like below.*/
string[] senders = senderFilter.Split(';');
foreach (var sender in senders)
{
var senderRegex = new Regex(sender, RegexOptions.Compiled);
attFetcherSenderFilter.SetRule(senderRegex);
}
attFetcher.AppendFilter(attFetcherSenderFilter);
if (!string.IsNullOrEmpty(titleFilter))
{
var attFetcherTitleFilter = new TitleFilter();
var titleRegex = new Regex(titleFilter, RegexOptions.Compiled);
attFetcherTitleFilter.SetRule(titleRegex);
attFetcher.AppendFilter(attFetcherTitleFilter);
}
attFetcher.Execute(lastSyncTime);
/*Get the saved attachment names.*/
var attachmentFileNames = attFetcher.GetAttachmentFileNames();
//var attachmentFileNames=new List<string>();
//attachmentFileNames.Add(@"C:\DataFeedApp\Scheduler\CnE\路透数据20161109.xls");
StringBuilder sb = new StringBuilder();
//var attachmentFileNames = Directory.GetFiles(@"C:\xx\troil").ToList<string>();
syncCallback(attachmentFileNames, sb);
var fold = new DirectoryInfo(attFetcherSavingPath);
if (fold.Exists)
{
FileInfo[] files = fold.GetFiles();
foreach (FileInfo f in files)//删除目录下所有文件
{
f.Delete();
}
}
if (attachmentFileNames.Count == 0)
{
strInfo.Append("No find files .\r\n");
}
#endregion
var endTime = DateTime.UtcNow;
logEntity.ENDTIME = endTime;
logEntity.JobStatus = JobStatus.Success;
logEntity.RUNDETAIL = strInfo + sb.ToString();
saveLog(logEntity);
//merge data from temp to persistence
if (mergeCallback != null)
{
mergeCallback(conn, tran);
}
tran.Commit();
}
catch (Exception exception)
{
tran.Rollback();
logEntity.ENDTIME = DateTime.UtcNow.AddDays(-1);
logEntity.JobStatus = JobStatus.Fail;
logEntity.RUNDETAIL = strInfo + "\n" + exception;
saveLog(logEntity);
}
finally
{
conn.Close();
conn.Dispose();
}
}
