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