Check for either ADO.NET / OLEDB Connection presence

Question

I am developing a custom SSIS component for use across my entire company. Right now the code (which is from here) accepts only ADO.NET connection type.

I would like to support OLEDB type as well and would like to change my code accordingly. The piece of code that checks for valid ADO.NET connectivity is:

SqlConnection connection = connections[_connectionName].AcquireConnection(null) as SqlConnection;

     if (connection == null)
          {
          componentEvents.FireError(0, METHOD_NAME, "The connection is not a valid ADO.NET connection", "", -1);
          return DTSExecResult.Failure;
          }

This would just check for a valid ADO.NET connection. How would I change this to check for OLEDB connection ALSO. So for example, if the connection type is OLEDB, it should be accepted, if its neither of those, it should fail.

I am not much of a C# person and hence I am looking for any help that I can get on this. Thanks for any help.

I found a working solution[1] for this. [1]: http://stackoverflow.com/questions/11265706/missing-library-to-reference-oledb-connection-types — rvphx, Jul 09 '12 at 17:15

Kevin Aenmey · Answer 1 · 2012-06-26T22:06:35.333

2

You can use the is keyword to determine if an object is an instance of a specified type (or a type that derives from the specified type). see MSDN

var connection = connections[_connectionName].AcquireConnection(null);

if (!(connection is SqlConnection || connection is OleDbConnection))
{
     componentEvents.FireError(0, METHOD_NAME, "The connection is not a valid ADO.NET connection", "", -1);
          return DTSExecResult.Failure;
}

If you want to determine if the connection is any DbConnection type (from which both SqlConnection and OleDbConnection derive), you could do the following:

DbConnection connection = connections[_connectionName].AcquireConnection(null) as DbConnection;

if (connection == null)
{
    componentEvents.FireError(0, METHOD_NAME, "The connection is not a valid ADO.NET connection", "", -1);
    return DTSExecResult.Failure;
}

edited Jun 26 '12 at 22:06

answered Jun 26 '12 at 21:58

Kevin Aenmey

13,259
5
46
45

I also have an insert statement in the code. Can you help me that as well? – rvphx Jun 26 '12 at 22:11
I'm not exactly sure what you mean. I think you should post that as a new question. That way you will be able to provide a better explanation and allow others to benefit from it too. You're welcome. :) – Kevin Aenmey Jun 26 '12 at 22:14
New question posted!! http://stackoverflow.com/questions/11216753/check-for-either-ado-net-oledb-connection-presence-and-then-insert-to-db – rvphx Jun 26 '12 at 22:21
Sorry, I had to roll back the answer. But neither of the way you suggested seem to work. It works only for ADO.NET, but not for OLEDB. – rvphx Jun 27 '12 at 18:40
I further found out from BOL "When you write managed code in a Script component, you cannot call the AcquireConnection method of connection managers that return unmanaged objects, such as the OLE DB connection manager and the Excel connection manager. However, you can read the ConnectionString property of these connection managers, and connect to the data source directly in your code by using the connection string of an OLEDB connection from the System.Data.OleDb namespace" – rvphx Jun 28 '12 at 16:52

Check for either ADO.NET / OLEDB Connection presence

1 Answers1

Linked