Check for either ADO.NET / OLEDB Connection presen

Question

I originally posted the question to check for presence of either ADO.NET/OLEDB connection types. That being resolved, I'd like to know, how to change the code when it comes to inserting to the DB.

For example, when the connection type is ADO.NET we use the "Transaction" in the connection type.

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

Now if I have the OLEDB connection (instead of ADO.NET), I'd like to handle that situation in this code. What do I need to do. Sorry if I dont sound technical enough, but I am not a C# person. Thanks again for your kind help.

EDIT I am pasting the whole code here because I cant seem to use OLEDB type connections. I can only use ADO.NET...I know its something simple, but I dont know what it is.

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using Microsoft.SqlServer.Dts.Runtime;
using System.Data.OleDb;
using System.Data.Common;

namespace AOC.SqlServer.Dts.Tasks

{

[DtsTask(
    DisplayName = "Custom Logging Task",
    Description = "Writes logging info into a table")]
public class CustomLoggingTask : Task
{

    private string _packageName;
    private string _taskName;
    private string _errorCode;
    private string _errorDescription;
    private string _machineName;
    private double _packageDuration;

    private string _connectionName;
    private string _eventType;
    private string _executionid;
    private DateTime _handlerdatetime;
    public string ConnectionName
    {
        set
        {
            _connectionName = value;
        }
        get
        {
            return _connectionName;
        }
    }


    public string Event
    {
        set
        {
            _eventType = value;
        }
        get
        {
            return _eventType;
        }
    }


    public override DTSExecResult Validate(Connections connections, VariableDispenser variableDispenser, IDTSComponentEvents componentEvents, IDTSLogging log)
    {
        const string METHOD_NAME = "CustomLoggingTask-Validate";

        try
        {

            if (string.IsNullOrEmpty(_eventType))
            {
                componentEvents.FireError(0, METHOD_NAME, "The event property must be specified", "", -1);
                return DTSExecResult.Failure;
            }


            if (string.IsNullOrEmpty(_connectionName))
            {
                componentEvents.FireError(0, METHOD_NAME, "No connection has been specified", "", -1);
                return DTSExecResult.Failure;
            }

            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;
            }

            if (!variableDispenser.Contains("System::SourceID"))
            {
                componentEvents.FireError(0, METHOD_NAME, "No System::SourceID variable available. This task can only be used in an Event Handler", "", -1);
                return DTSExecResult.Failure;
            }

            return DTSExecResult.Success;
        }
        catch (Exception exc)
        {
            componentEvents.FireError(0, METHOD_NAME, "Validation Failed: " + exc.ToString(), "", -1);
            return DTSExecResult.Failure;
        }
    }


    public override DTSExecResult Execute(Connections connections, VariableDispenser variableDispenser, IDTSComponentEvents componentEvents, IDTSLogging log, object transaction)
    {
        try
        {
            string commandText =
@"INSERT INTO SSISLog (EventType, PackageName, TaskName, EventCode, EventDescription, PackageDuration, Host, ExecutionID, EventHandlerDateTime)
VALUES (@EventType, @PackageName, @TaskName, @EventCode, @EventDescription, @PackageDuration, @Host, @Executionid, @handlerdatetime)";

            ReadVariables(variableDispenser);
            DbConnection connection = connections[_connectionName].AcquireConnection(transaction) as DbConnection;
            //SqlConnection connection = (SqlConnection)connections[_connectionName].AcquireConnection(transaction);
            DbCommand command = null;
            //using (SqlCommand command = new SqlCommand())
            if (connection is SqlConnection)
                command = new SqlCommand();
            else if (connection is OleDbConnection)
                command = new OleDbCommand();

            {
                command.CommandText = commandText;
                command.CommandType = CommandType.Text;
                command.Connection = connection;

                command.Parameters.Add(new SqlParameter("@EventType", _eventType));
                command.Parameters.Add(new SqlParameter("@PackageName", _packageName));
                command.Parameters.Add(new SqlParameter("@TaskName", _taskName));
                command.Parameters.Add(new SqlParameter("@EventCode", _errorCode ?? string.Empty));
                command.Parameters.Add(new SqlParameter("@EventDescription", _errorDescription ?? string.Empty));
                command.Parameters.Add(new SqlParameter("@PackageDuration", _packageDuration));
                command.Parameters.Add(new SqlParameter("@Host", _machineName));
                command.Parameters.Add(new SqlParameter("@ExecutionID", _executionid));
                command.Parameters.Add(new SqlParameter("@handlerdatetime", _handlerdatetime));
                command.ExecuteNonQuery();
            }

            return DTSExecResult.Success;
        }
        catch (Exception exc)
        {
            componentEvents.FireError(0, "CustomLoggingTask-Execute", "Task Errored: " + exc.ToString(), "", -1);
            return DTSExecResult.Failure;
        }
    }


    private void ReadVariables(VariableDispenser variableDispenser)
    {
        variableDispenser.LockForRead("System::StartTime");
        variableDispenser.LockForRead("System::PackageName");
        variableDispenser.LockForRead("System::SourceName");
        variableDispenser.LockForRead("System::MachineName");
        variableDispenser.LockForRead("System::ExecutionInstanceGUID");
        variableDispenser.LockForRead("System::EventHandlerStartTime");
        bool includesError = variableDispenser.Contains("System::ErrorCode");
        if (includesError)
        {
            variableDispenser.LockForRead("System::ErrorCode");
            variableDispenser.LockForRead("System::ErrorDescription");
        }

        Variables vars = null;
        variableDispenser.GetVariables(ref vars);

        DateTime startTime = (DateTime)vars["System::StartTime"].Value;
        _packageDuration = DateTime.Now.Subtract(startTime).TotalSeconds;
        _packageName = vars["System::PackageName"].Value.ToString();
        _taskName = vars["System::SourceName"].Value.ToString();
        _machineName = vars["System::MachineName"].Value.ToString();
        _executionid = vars["System::ExecutionInstanceGUID"].Value.ToString();
        _handlerdatetime = (DateTime)vars["System::EventHandlerStartTime"].Value;
        if (includesError)
        {
            _errorCode = vars["System::ErrorCode"].Value.ToString();
            _errorDescription = vars["System::ErrorDescription"].Value.ToString();
        }

        // release the variable locks.
        vars.Unlock();

        // reset the dispenser
        variableDispenser.Reset();
    }
}

}

Answer 1

In System.Data.Common you will find the base classes that the concrete database types (SqlConnection, OleDbConnection, SqlDataAdapter etc.) are all derived from.

If you use those, ie. DbConnection, it doesn't matter which of the concrete implementations you are working with, the code will be the same.

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

Then, you can call DbConnection.BeginTransaction() instead of SqlConnection.BeginTransaction() and it doesn't matter whether the connection is OleDbConnection or SqlConnection.

This will work as long as all the methods you need to call are inherited from DbConnection.

The rest of your code could use the base types as well, so DbTransaction, DbDataAdapter, DbDataReader etc.

Because your AquireConnection() method does not return a concrete connection type, you are able to take advantage of Dependency Injection and write code that is not implementation-specific.

---

Specifically, for an insert you'll have something like this:

DbCommand command = null;
if (connection is SqlConnection)
   command = new SqlCommand();
else if (connection is OleDbConnection)
   command = new OleDbCommand();
command.CommandText = "INSERT STATEMENT HERE";
command.Connection = connection;
command.ExecuteNonQuery();

Don't forget that you will need connection.Close() somewhere. (Or ReleaseConnection if you are using ConnectionManager)

Answer 2

Since the connections[_connectionName].AcquireConnection(transaction); part of your code has nothing to do with a SqlConnection (you are just casting to SqlConnection afterwards), you should just be able to declare connection with var and then do anything with connection you need to.

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

Once you have your connection object, you can cast it to whatever connection type you need to and perform the same operation as if you had declared your connection variable as that type. E.g.

if(connection is DbConnection)
{
    // ((DbConnection)connection).SomethingToDoWithDbConnection
}

if(connection is SqlConnection)
{
    // ((SqlConnection)connection).SomethingToDoWithSqlConnection
}

if(connection is OleDbConnection)
{
    // ((OleDbConnection)connection).SomethingToDoWithOleDbConnection
}