/// <summary> /// Clears the connection pool. /// </summary> protected override void ClearPool() { if (Connection != null) { OracleConnection.ClearPool((OracleConnection)Connection); } }
public static string TestConnectionDataBase(string oracleDBConnection) { string result = String.Empty; OracleConnection conn = new OracleConnection(oracleDBConnection); try { conn.Open(); OracleCommand dbcmd = conn.CreateCommand(); return(result); } catch (ArgumentException ex) { Trace.TraceWrite("Test DB failed: " + ex.ToString() + "\n\n"); result = "Ошибка соединения с БД"; return(result); } catch (Exception ex) { Trace.TraceWrite("Test DB failed: " + ex.ToString() + "\n\n"); result = "Указан неверный адрес БД"; return(result); } finally { OracleConnection.ClearPool(conn); conn.Dispose(); conn.Close(); conn = null; } }
private void btnLogIn_Click(object sender, EventArgs e) { InnodisLogin.uname = textBoxUsername.Text.ToString(); InnodisLogin.printerIP = PrinterInfoComboBox.SelectedValue.ToString(); try { //myConn.ConnectionString = "User Id=" + textBoxUsername.Text + ";Password="******";Server=innebnidm02.innodisgroup.com;Port = 1541;Sid = prd1;"; myConn.ConnectionString = "User Id=xxean;Password=ean123;Server=innebnidm02.innodisgroup.com;Port = 1541;Sid = prd1;"; myConn.Open(); if (myConn.State == System.Data.ConnectionState.Open) { //if (textBoxUsername.Text.ToString() == "" || textBoxPassword.Text.ToString() == "") //{ // MessageBox.Show("Please enter a username or password"); //} //else //{ validateLogin(); //} textBoxUsername.Text = ""; textBoxPassword.Text = ""; textBoxUsername.Focus(); } myConn.Close(); OracleConnection.ClearPool(myConn); } catch (OracleException ex) { MessageBox.Show(ex.Message); } }
static void Main() { Console.WriteLine("Running ClearPool sample..."); // Set the connection string string strConn = "User Id=scott;Password=tiger;Data Source=oracle;" + "Min pool size=5;"; OracleConnection conn = new OracleConnection(strConn); // Open the connection conn.Open(); // Clears the connection pool associated with connection 'conn' OracleConnection.ClearPool(conn); // This connection will be placed back into the pool conn.Close(); // Open the connection again to create additional connections in the pool conn.Open(); // Create a new connection object OracleConnection connNew = new OracleConnection(strConn); // Clears the pool associated with Connection 'connNew' // Since the same connection string is set for both the connections, // connNew and conn, they will be part of the same connection pool. // We need not do an Open() on the connection object before calling // ClearPool OracleConnection.ClearPool(connNew); // cleanup conn.Close(); Console.WriteLine("Done!"); }
/// <summary> /// 针对性处理数据库异常 /// </summary> /// <param name="oraEx"></param> /// <param name="conn"></param> public static void HandleOraException(OracleException oraEx, OracleConnection conn) { if (oraEx == null) { return; } switch (oraEx.Number) { case 3113: //ORA-03113: 通信通道的文件结尾 //可能发生在重启数据库 OracleConnection.ClearAllPools(); throw new Exception("发生ORA-03113错误,已清空数据库连接池。", oraEx); case 28: //ORA-00028: 会话己被终止(session kill) - 发生在会话被Kill之后 case 1012: //ORA-01012: 没有登录(not logon) - 发生在ORA-00028后再访问数据库 case 2396: //ORA-02396: 超出最大空闲时间(exceeded maximum idle time) case 12535: //ORA-12535: TNS操作超时(TNS:operation timed out) if (conn != null) { OracleConnection.ClearPool(conn); throw new Exception("发生ORA-" + oraEx.Number.ToString() + "错误,已从数据库连接池清理掉当前连接。", oraEx); } else { OracleConnection.ClearAllPools(); throw new Exception("发生ORA-" + oraEx.Number.ToString() + "错误,已清空数据库连接池。", oraEx); } default: break; } //ORA-01089: 正在执行立即关闭 - 发生在数据库正在关闭 }
public static bool TestConnect(string user, string pwd, string serviceName, out string msg) { //Data Source=lan_189;User ID=user_ahdy_shi;Password=user_ahdy_shi string conn = string.Format("Data Source={0};User ID={1};Password={2}", serviceName.Trim(), user, pwd); try { using (OracleConnection con = new OracleConnection(conn)) { OracleConnection.ClearPool(con); System.Threading.Thread.Sleep(100); con.Open(); con.Close(); con.Dispose(); } msg = "连接" + serviceName.ToUpper() + "成功"; return(true); } catch (Exception ex) { msg = ex.Message; return(false); } }
public void Close() { if (_connection != null) { _connection.Dispose(); _connection.Close(); OracleConnection.ClearPool(_connection); } }
public static List <JuridicalData> GetJuridicalDataDB(string oracleDBConnection) { List <JuridicalData> JD = new List <JuridicalData>(); OracleConnection conn = new OracleConnection(oracleDBConnection); try { conn.Open(); OracleCommand dbcmd = conn.CreateCommand(); dbcmd.CommandText = "select replace((case when inn is not null then inn ||'-'|| (case when lc.title is not null then lc.title else rp.title end) " + "else (case when lc.title is not null then lc.title else rp.title end) end), '\"') as jurInnAndTitle, " + "lc.inn, (case when lc.title is not null then lc.title else rp.title end) as jurTitle, lc.legacy_cli_id as jurID, " + "lc.email from legacy_clients lc join retail_points rp on lc.legacy_cli_id = rp.legacy_cli_id " + "where lower(lc.agent_agreement) not like '%(раст)%' " + "group by lc.inn, (case when lc.title is not null then lc.title else rp.title end), lc.legacy_cli_id, lc.email order by 3"; OracleDataReader reader = dbcmd.ExecuteReader(); while (reader.HasRows) { while (reader.Read()) { JD.Add(new JuridicalData() { jurInnAndTitle = reader.IsDBNull(0) ? String.Empty : reader.GetString(0), inn = reader.IsDBNull(1) ? String.Empty : reader.GetString(1), jurTitle = reader.IsDBNull(2) ? String.Empty : reader.GetString(2), jurID = reader.IsDBNull(3) ? 0 : reader.GetInt32(3), email = reader.IsDBNull(4) ? String.Empty : reader.GetString(4) }); } reader.NextResult(); } reader.Close(); reader = null; return(JD); } catch (Exception ex) { Console.WriteLine(DateTime.Now + ":\n" + ex.ToString() + "\n\n"); return(JD); } finally { OracleConnection.ClearPool(conn); conn.Dispose(); conn.Close(); conn = null; } }
private static void OpenConnection(string connection) { if (con == null) { con = new OracleConnection(connection); } else { con.Close(); OracleConnection.ClearPool(con); con.ConnectionString = connection; } con.Open(); }
public FailoverSpawnConnectionInfo Connect() { FailoverSpawnConnectionInfo connectionInfo; using (OracleConnection connection = new OracleConnection(_connectionString)) { // create variables var dbName = new object(); var sysdate = new object(); // Create new stopwatch. Stopwatch stopwatch = new Stopwatch(); stopwatch.Start(); connection.Open(); Console.WriteLine("Milliseconds elapsed open connection\t\t: {0}", stopwatch.ElapsedMilliseconds); // first run dbName = ExecuteQuery(connection, @"select lower(sys_context('userenv','db_name')) as db_name from dual"); sysdate = ExecuteQuery(connection, @"select sysdate from dual"); Console.WriteLine("Milliseconds elapsed first run staments\t\t: {0}", stopwatch.ElapsedMilliseconds); // second run dbName = ExecuteQuery(connection, @"select lower(sys_context('userenv','db_name')) as db_name from dual"); sysdate = ExecuteQuery(connection, @"select sysdate from dual"); Console.WriteLine("Milliseconds elapsed second run staments\t: {0}", stopwatch.ElapsedMilliseconds); connection.Dispose(); connection.Close(); OracleConnection.ClearPool(connection); OracleConnection.ClearAllPools(); Console.WriteLine("Milliseconds elapsed close connection\t\t: {0}", stopwatch.ElapsedMilliseconds); stopwatch.Stop(); Console.WriteLine("Connection state\t\t\t\t: " + connection.State); connectionInfo = new FailoverSpawnConnectionInfo() { ConnectionDateTime = sysdate.ToString(), DatabaseName = dbName.ToString(), ConnectionState = connection.State }; } return(connectionInfo); }
public bool Disconnect() { try { if (this.objCommand != null) { this.objCommand.Dispose(); } this.objConnection.Close(); OracleConnection.ClearPool(this.objConnection); return(true); } catch { return(false); } }
public static DBResult DBSQL(string oracleDBConnection, string sql, bool needResult) { Trace(sql); DBResult DBResult = new DBResult(); DBResult.result = String.Empty; DBResult.state = Variables.SUCCESS; OracleConnection conn = new OracleConnection(oracleDBConnection); try { conn.Open(); OracleCommand dbcmd = conn.CreateCommand(); dbcmd.CommandText = sql; OracleDataReader reader = dbcmd.ExecuteReader(); if (needResult) { while (reader.Read()) { DBResult.result = (string)reader["data"].ToString(); Trace("Result: " + DBResult.result); } } reader.Close(); reader = null; return(DBResult); } catch (Exception ex) { Trace(ex.ToString()); DBResult.state = Variables.ERROR; return(DBResult); } finally { OracleConnection.ClearPool(conn); conn.Dispose(); conn.Close(); conn = null; } }
public static List <BonusClub> GetBonusClubDataDB(string oracleDBConnection) { List <BonusClub> BC = new List <BonusClub>(); OracleConnection conn = new OracleConnection(oracleDBConnection); try { conn.Open(); OracleCommand dbcmd = conn.CreateCommand(); dbcmd.CommandText = "select club_id, title from bonus_clubs order by 2"; OracleDataReader reader = dbcmd.ExecuteReader(); while (reader.HasRows) { while (reader.Read()) { BC.Add(new BonusClub() { clubId = reader.IsDBNull(0) ? 0 : reader.GetInt32(0), title = reader.IsDBNull(1) ? String.Empty : reader.GetString(1) }); } reader.NextResult(); } reader.Close(); reader = null; return(BC); } catch (Exception ex) { Console.WriteLine(DateTime.Now + ":\n" + ex.ToString() + "\n\n"); return(BC); } finally { OracleConnection.ClearPool(conn); conn.Dispose(); conn.Close(); conn = null; } }
partial void OnOracleReconnecting(ref bool processed) { if (processed) { return; } OracleConnection conn = _Connection as OracleConnection; if (conn == null) { return; } OracleConnection.ClearPool(conn); processed = true; }
protected override void disconnection() { try { if (base.Connection.State == ConnectionState.Open) { base.Connection.Close(); } } catch (DbException ex) { throw ex; } finally { OracleConnection.ClearPool(base.Connection); } }
public void TestDbConnection() { var connectionString = $"Data Source = {Environment.GetEnvironmentVariable("dbinstance", EnvironmentVariableTarget.Machine)};" + $"User Id = {Environment.GetEnvironmentVariable("dbusername", EnvironmentVariableTarget.Machine)}; " + $"Password = {Environment.GetEnvironmentVariable("dbpassword", EnvironmentVariableTarget.Machine)};" + $"Pooling = False;"; using (OracleConnection connection = new OracleConnection(connectionString)) { Assert.AreNotEqual(connection.State, ConnectionState.Open); connection.Open(); Assert.AreEqual(connection.State, ConnectionState.Open); connection.Dispose(); connection.Close(); OracleConnection.ClearPool(connection); OracleConnection.ClearAllPools(); } }
/// <summary> /// 关闭一个MYSQL连接 /// </summary> /// <param name="oracleConnection"></param> /// <returns></returns> public static bool CloseOracleConnection(OracleConnection oracleConnection) { if (null == oracleConnection) { return(false); } try { if (oracleConnection.State != ConnectionState.Closed) { OracleConnection.ClearPool(oracleConnection); oracleConnection.Close(); } return(true); } catch (Exception e) { throw e; } }
public int ExecuteBulk(string SqlStatment) { OracleConnection Cn = new OracleConnection(ConnectionString); int result = -1; if (Cn.State != ConnectionState.Open) { Cn.Open(); } OracleCommand command = Cn.CreateCommand(); OracleTransaction transaction; // Start a local transaction transaction = Cn.BeginTransaction(IsolationLevel.ReadCommitted); // Assign transaction object for a pending local transaction command.Transaction = transaction; try { foreach (string sql in SqlStatment.Split(';')) { command.CommandText = sql; result = command.ExecuteNonQuery(); } transaction.Commit(); } catch (Exception e) { transaction.Rollback(); throw e; } finally { Cn.Close(); Cn.Dispose(); OracleConnection.ClearPool(Cn); } return(result); }
public override async Task DropAsync() { using (var connection = new OracleConnection(ConnectionString)) { OracleConnection.ClearPool(connection); } // TODO: better way to force delete database? await Task.Delay(250); using (var connection = new OracleConnection(DbaConnectionString)) { await connection.OpenAsync(); var varUsername = $"\"{_connectionString.UserId}\""; await connection.ExecuteAsync($"drop user {varUsername} CASCADE"); await connection.CloseAsync(); } }
public static List <string> GetMobileUrls(string oracleDBConnection, string receiver) { OracleConnection conn = new OracleConnection(oracleDBConnection); string sql = "select mobile_push_url from mobile_logins ml " + "join mobile_push_names mpn on ml.mobile_name = mpn.mobile_name " + "where login_name = '" + receiver + "' and ml.is_delete = 0 "; List <string> GetMobileUrls = new List <string>(); try { conn.Open(); OracleCommand dbcmd = conn.CreateCommand(); dbcmd.CommandText = sql; dbcmd.CommandTimeout = 60; OracleDataReader reader = dbcmd.ExecuteReader(); while (reader.Read()) { GetMobileUrls.Add(reader.IsDBNull(0) ? String.Empty : reader.GetString(0)); } reader.Close(); reader = null; return(GetMobileUrls); } catch (Exception) { return(GetMobileUrls); } finally { OracleConnection.ClearPool(conn); conn.Dispose(); conn.Close(); conn = null; } }
public static string DataBaseSQL(string oracleDBConnection, string sql, bool needResult) { string result = String.Empty; OracleConnection conn = new OracleConnection(oracleDBConnection); try { conn.Open(); OracleCommand dbcmd = conn.CreateCommand(); dbcmd.CommandText = sql; dbcmd.CommandTimeout = 60; OracleDataReader reader = dbcmd.ExecuteReader(); if (needResult) { while (reader.Read()) { result = (string)reader["data"].ToString(); } } reader.Close(); reader = null; return(result); } catch (Exception) { return(result); } finally { OracleConnection.ClearPool(conn); conn.Dispose(); conn.Close(); conn = null; } }
private void ReadData(string connString, string cmdString) { List <Class> data = new List <Class>(); using (OracleConnection conn = new OracleConnection() { ConnectionString = connString }) using (OracleCommand objCmd = new OracleCommand() { Connection = conn, CommandText = cmdString }) { try { conn.Open(); } catch (OracleException) { OracleConnection.ClearPool(conn); conn.Open(); } using (OracleDataReader dataReader = objCmd.ExecuteReader()) { while (dataReader.Read()) { data.Add(new Class() { FirstProperty = dataReader.GetString(0), SecondProperty = dataReader.GetString(1) }); } } conn.Close(); } //some long operation using data }
private List <string> getTableNames() { List <string> ret = new List <string>(); try { using (OracleConnection conn = new OracleConnection(connstringInput.Text)) { conn.Open(); ret = conn.Query <string>("select distinct(table_name) from dba_tab_cols where owner = :o order by table_name", new { o = schemaInput.Text }).ToList(); conn.Close(); OracleConnection.ClearPool(conn); } } catch (Exception ex) { MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error); } return(ret); }
public bool TestConnection(out string msg) { msg = ""; try { using (OracleConnection connection = new OracleConnection(_ConnectionString)) { OracleConnection.ClearPool(connection); connection.Open(); msg = "连接成功"; WriteLog("测试连接成功"); return(true); } } catch (Exception ex) { msg = ex.Message; return(false); } }
/* * private DbDataAdapter CreateAdapter() * { * switch (_dbType.ToUpper()) * { * case "ORACLE": * { * return new OracleDataAdapter(); * } * case "SQLSERVER": * { * return new SqlDataAdapter(); * } * case "MYSQL": * { * return new MySqlDataAdapter(); * } * default: * return new OracleDataAdapter(); * } * } */ private void ClearPool(IDbConnection conn) { switch (_dbType.ToUpper()) { case "ORACLE": { OracleConnection.ClearPool(conn as OracleConnection); break; } case "SQLSERVER": { SqlConnection.ClearPool(conn as SqlConnection); break; } case "MYSQL": { MySqlConnection.ClearPool(conn as MySqlConnection); break; } } }
public static string DataBaseSQL(string oracleDbConnection, string sql) { string data = String.Empty; OracleConnection conn = new OracleConnection(oracleDbConnection); try { conn.Open(); OracleCommand dbcmd = conn.CreateCommand(); dbcmd.CommandText = sql; OracleDataReader reader = dbcmd.ExecuteReader(); while (reader.Read()) { data = (string)reader["data"].ToString(); } reader.Close(); reader = null; return(data); } catch (Exception ex) { LogFile(Variables.fileError, oracleDbConnection.Remove(oracleDbConnection.IndexOf(";")) + "\n\n" + ex.ToString(), false); return(data); } finally { OracleConnection.ClearPool(conn); conn.Dispose(); conn.Close(); conn = null; } }
private void button1_Click(object sender, EventArgs e) { //try to get schemas and table names try { using (OracleConnection conn = new OracleConnection(connstringInput.Text)) { conn.Open(); var res = conn.Query <string>("select distinct(owner) from dba_tab_cols order by owner"); schemaInput.Items.Clear(); schemaInput.Items.AddRange(res.ToArray()); schemaInput.Enabled = true; conn.Close(); OracleConnection.ClearPool(conn); } } catch (Exception ex) { MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error); } }
private void DropUser() { OracleConnection.ClearPool((OracleConnection)Connection); DropUser(Name); }
/// <summary> /// Task for performing queries in Oracle databases. See documentation at https://github.com/CommunityHiQ/Frends.Community.Oracle.Query /// </summary> /// <param name="database"></param> /// <param name="queryProperties"></param> /// <param name="options"></param> /// <returns>Object { bool Success, string Message, string Result }</returns> public static Output Query([PropertyTab] ConnectionProperties database, [PropertyTab] QueryProperties queryProperties, [PropertyTab] Options options) { try { using (var connection = new OracleConnection(database.ConnectionString)) { try { connection.Open(); using (var command = new OracleCommand(queryProperties.Query, connection)) { command.CommandTimeout = database.TimeoutSeconds; command.BindByName = true; // is this xmlCommand specific? // check for command parameters and set them if (queryProperties.Parameters != null) { command.Parameters.AddRange(queryProperties.Parameters.Select(p => CreateOracleParameter(p)).ToArray()); } // declare Result object string queryResult; // set commandType according to ReturnType switch (queryProperties.ReturnType) { case QueryReturnType.Xml: command.XmlCommandType = OracleXmlCommandType.Query; command.XmlQueryProperties.MaxRows = queryProperties.MaxmimumRows; command.XmlQueryProperties.RootTag = queryProperties.RootElementName; command.XmlQueryProperties.RowTag = queryProperties.RowElementName; var xmlReader = command.ExecuteXmlReader(); var xmlDocument = new XmlDocument { PreserveWhitespace = true }; xmlDocument.Load(xmlReader); // assign query result XML or empty XML if query returned no results queryResult = xmlDocument.HasChildNodes ? xmlDocument.OuterXml : $"<{queryProperties.RootElementName}></{queryProperties.RootElementName}>"; break; case QueryReturnType.Json: command.CommandType = CommandType.Text; var reader = command.ExecuteReader(); queryResult = reader.ToJson(queryProperties.CultureInfo); break; default: throw new ArgumentException("Task 'Return Type' was invalid! Check task properties."); } return(new Output { Success = true, Result = queryResult }); } } catch (Exception ex) { throw ex; } finally { // Close connection connection.Dispose(); connection.Close(); OracleConnection.ClearPool(connection); } } } catch (Exception ex) { if (options.ThrowErrorOnFailure) { throw ex; } return(new Output { Success = false, Message = ex.Message }); } }
/// <summary> /// Create multiple queries for batch operations like insert. /// Queries are executed with Dapper ExecuteAsync. /// See documentation at https://github.com/CommunityHiQ/Frends.Community.Oracle /// </summary> /// <param name="input">Input parameters</param> /// <param name="options"></param> /// <param name="cancellationToken"></param> /// <returns>Object { bool Success, string Message, JArray Results}</returns> public static async Task <MultiBatchOperationOutput> MultiBatchOperationOracle( [PropertyTab] InputMultiBatchOperation input, [PropertyTab] BatchOptions options, CancellationToken cancellationToken) { try { using (var c = new OracleConnection(input.ConnectionString)) { try { await c.OpenAsync(cancellationToken); if (options.IsolationLevel == Oracle_IsolationLevel.None) { // Declare Result object. int queryResult; var queryResults = new JArray(); foreach (var query in input.BatchQueries) { using (var command = new OracleCommand(query.BatchInputQuery, c)) { command.CommandTimeout = options.TimeoutSeconds; // Is this xmlCommand specific? command.BindByName = true; var obj = JsonConvert.DeserializeObject <ExpandoObject[]>(query.InputJson, new ExpandoObjectConverter()); queryResult = await c.ExecuteAsync( query.BatchInputQuery, param : obj, commandTimeout : options.TimeoutSeconds, commandType : CommandType.Text) .ConfigureAwait(false); var result = new { QueryIndex = Array.IndexOf(input.BatchQueries, query), RowCount = queryResult }; queryResults.Add(JObject.FromObject(result)); } cancellationToken.ThrowIfCancellationRequested(); } return(new MultiBatchOperationOutput { Success = true, Results = queryResults }); } else { var txn = c.BeginTransaction(options.IsolationLevel.GetTransactionIsolationLevel()); // Declare queryResult (rowcount). int queryResult; var queryResults = new JArray(); try { foreach (var query in input.BatchQueries) { var obj = JsonConvert.DeserializeObject <ExpandoObject[]>(query.InputJson, new ExpandoObjectConverter()); queryResult = await c.ExecuteAsync( query.BatchInputQuery, param : obj, commandTimeout : options.TimeoutSeconds, commandType : CommandType.Text, transaction : txn) .ConfigureAwait(false); var result = new { QueryIndex = Array.IndexOf(input.BatchQueries, query), RowCount = queryResult }; queryResults.Add(JObject.FromObject(result)); cancellationToken.ThrowIfCancellationRequested(); } txn.Commit(); txn.Dispose(); return(new MultiBatchOperationOutput { Success = true, Results = queryResults }); } catch (Exception) { txn.Rollback(); txn.Dispose(); throw; } } } finally { // Close connection. c.Dispose(); c.Close(); OracleConnection.ClearPool(c); } } } catch (Exception ex) { if (options.ThrowErrorOnFailure) { throw; } return(new MultiBatchOperationOutput { Success = false, Message = ex.Message }); } }