title: Check for either ADO.NET / OLEDB Connection presence and then insert to DB
body: <p>I originally posted the <a href="https://stackoverflow.com/questions/11216494/check-for-either-ado-net-oledb-connection-presence">question</a> 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. </p>
  
  <p>For example, when the connection type is ADO.NET we use the "Transaction" in the connection type.</p>
  
  <pre><code> SqlConnection connection = (SqlConnection)connections[_connectionName].AcquireConnection(transaction);
  </code></pre>
  
  <p>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.</p>
  
  <p><strong>EDIT</strong>
  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.</p>
  
  <pre><code>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();
      }
  }
  </code></pre>
  
  <p>}</p>