MichaelvdV@Atos

PI OLEDB provider hanging on Open (WCF/IIS 7)

Discussion created by MichaelvdV@Atos on Jun 24, 2010
Latest reply on Mar 18, 2011 by spilon

Please consider the following situation:

 

The current project I'm working on needs large amounts of future data. We use the PI OLEDB COM connector to store future timestamps into SQL server (and thus enabling future data in PI). Due to the lack of functionality and performance of the PI OLEDB COM connector, we  have build a custom 'data access' component. This data access component dispatches operations (inserts, deletes, updates, get's) to either SQL server or PI OLEDB calls (depending if the tag is a 'future data' tag, or a normal history tag).

 

My class definition is like so:

 

dataaccessdiagram.JPG

 

This is all implemented in a class library, which is instantiated and called by a WCF service hosted in IIS.

 

Here is the relevant code (from OLEDBAccessProvider) that executes queries (depending on the derived class, to SQL Server (SQLNCLI10) or PI OLEDB

private OleDbConnection GetConnection()
{
    Logger.Debug("Creating OLEDB Connection");
    var conn = new OleDbConnection(Config["connectionstring"]);
    Logger.Debug("OLEDB Connection Created");
    Logger.Debug("Opening Connection");
    conn.Open();
    Logger.Debug("Connection Opened");
    return conn;
}

#region Statement Execution
protected DataTable Execute(string command, params object[] parameters)
{
    using (OleDbConnection connection = GetConnection())
    using (OleDbCommand cmd = new OleDbCommand(string.Format(command, parameters), connection))
    using (OleDbDataAdapter adapter = new OleDbDataAdapter(cmd))
    using (DataTable table = new DataTable())
    {
        Logger.Debug("Executing command: {0}", cmd.CommandText);
        adapter.Fill(table);
        Logger.Debug("Command executed: {0} rows retrieved", table.Rows.Count);
        return table;
    }
}

protected int ExecuteNonQuery(string command, params object[] parameters)
{
    using (OleDbConnection connection = GetConnection())
    using (OleDbCommand cmd = new OleDbCommand(string.Format(command, parameters), connection))
    {
        //for (int i = 0; i < parameters.Length; i++)
        //    cmd.Parameters.Add(new OleDbParameter("param" + 1, parameters));
        var transaction = connection.BeginTransaction();
        cmd.Transaction = transaction;
        try
        {
            Logger.Debug("Executing command (nonquery): {0}", cmd.CommandText);
            int affected = cmd.ExecuteNonQuery();
            transaction.Commit();
            Logger.Debug("Executing command complete: {0} rows affected", affected);
            return affected;
        }
        catch (Exception ex)
        {
            transaction.Rollback();
            throw;
        }
    }
}

#endregion

As you can see, I'm creating a new connection, and disposing it (using the 'using' statement) after each execution. This is done to provide asynchronous access to this object (making it 'thread safe').

 

The OLEDB connection pool should create/keep alive connections (http://msdn.microsoft.com/en-us/library/bb399543.aspx)

 

After a certain amount of calls (few hundred/maybe few thousand), the PI OLEDB connection will not open anymore. It will block (indefinitly) execution on the following statement in GetConnection()

conn.Open();

When it does, I can still open another PI OLEDB connection using a different program (either a test console app, or the PI OLEDB tester). I think this could be somehow related to IIS.

 

I have no idea where to go from here, any suggestions would be greatly appreciated!

 

ps: I know my code is vulnerable to SQL injection at this time, please ignore this for the moment.

Outcomes