The problem of [connection lost contact] after C # code connecting Oracle database for a period of time

Recently, we are using C # code to connect Oracle database, which is divided into two parts, client and server of WCF. There is no problem in the startup and running of the program, and there is no problem in the running after it is deployed to the server. But when you visit again the next day, you will throw the exception shown below. What’s going on?

 

Oracle.DataAccess.Client.OracleException ORA-03135: connection lost contact
	Process ID: 22574
	Session ID: 799 Serial number: 43225    Oracle.DataAccess.Client.OracleException.HandleErrorHelper(Int32 errCode, OracleConnection conn, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src, String procedure, Boolean bCheck)
	   at Oracle.DataAccess.Client.OracleCommand.ExecuteReader(Boolean requery, Boolean fillRequest, CommandBehavior behavior)
	   at Oracle.DataAccess.Client.OracleDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
	   at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable)
	

The relevant code used to connect Oracle database is also very typical, without any complexity.

 

 

public DataSet ExcuteDataSetForOralce(string sql, CommandType type, List<OracleParameter> paras)
        {
            DataSet ds = new DataSet();
            OracleCommand cmd = null;
            try
            {
                using (OracleConnection conn = new OracleConnection(ConnectionString))
                {
                    using (cmd = new OracleCommand(sql, conn))
                    {
                        if (paras != null)
                        {
                            foreach (OracleParameter p in paras)
                            {
                                cmd.Parameters.Add(p);
                            }
                        }
                        cmd.CommandType = type;
                        OracleDataAdapter oda = new OracleDataAdapter();
                        oda.SelectCommand = cmd;
                        conn.Open();
                        oda.Fill(ds, "tempTable");
                        conn.Close();
                    }
                }
            }
            catch (Exception ex)
            {
                log.Error("error", ex, LogType.Error);
            }
            return ds;
        }

 

 

 

Through multi-party search, it is found that this is not a code problem. Moreover, the problem of the connection between the provider and the database. In fact, when we create a new connection, we get the connection from the database connection pool. These connections are placed in the. Net provider. Conn.Close () does not close the connection, but returns it to the connection pool.

 

Now the problem is Oracle provider, not code. Your program hasn’t interacted with the database for a long time, and the database server turned off the open state of the connection, but the provider didn’t handle it in time, and still gave it to the C # code, so this exception was thrown.

 

The solution is to directly connect to the database without using the connection pool. Although this problem will not occur, there will be performance loss.

 

stay app.config In the configuration file of, in the string datasource = ((description…), add do not use connection pool.

 

 

 

Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=xxx)(PORT=1521)))(CONNECT_DATA =(SERVICE_NAME=xxx)(SERVER = DEDICATED)));User ID=xxx;password=xxxxx;Pooling=false;

 

 

 

http://www.csdn.net/topics/3920225524

 


Read More: