检查无论是ADO.NET / OLEDB连接presence，然后插入到数据库 [英] Check for either ADO.NET / OLEDB Connection presence and then insert to DB
问题描述
我最初发布的<一href="http://stackoverflow.com/questions/11216494/check-for-either-ado-net-oledb-connection-$p$psence">question检查或者ADO.NET/OLEDB连接类型$ P $ psence。话虽这么解决了，我想知道，如何改变code，当涉及到插入到数据库。
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.
例如，当连接类型是ADO.NET我们使用交易，在连接类型。
For example, when the connection type is ADO.NET we use the "Transaction" in the connection type.
SqlConnection connection = (SqlConnection)connections[_connectionName].AcquireConnection(transaction);
现在，如果我有OLEDB连接（而不是ADO.NET），我想处理这个code这种情况。什么我需要做的。很抱歉，如果我不健全的技术不够，但我不是一个C＃的人。再次感谢你的帮助。
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.
修改 我粘贴了整个code在这里，因为我似乎无法使用OLEDB类型的连接。我只能用ADO.NET ......我知道它的简单的东西，但我不知道它是什么。
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();
}
}
}
推荐答案
在 System.Data.Common 你会发现，具体的数据库类型（SqlConnection的，OleDbConnection的，SqlDataAdapter的等）都来源于基类。
In System.Data.Common you will find the base classes that the concrete database types (SqlConnection, OleDbConnection, SqlDataAdapter etc.) are all derived from.
如果您使用的这些，IE浏览器。 的DbConnection ，也不要紧，你正在使用的具体实现时，code将是相同的。
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;
然后，您可以调用 DbConnection.BeginTransaction（）而不是 SqlConnection.BeginTransaction（）并没有关系无所谓是否连接的OleDbConnection或SqlConnection的。
Then, you can call DbConnection.BeginTransaction() instead of SqlConnection.BeginTransaction() and it doesn't matter whether the connection is OleDbConnection or SqlConnection.
这会一直工作，因为所有你需要调用自DbConnection被继承的方法。
This will work as long as all the methods you need to call are inherited from DbConnection.
你的code，其余可以使用基本类型一样，所以 DbTransaction ， DbDataAdapter的， DbDataReader 等。
The rest of your code could use the base types as well, so DbTransaction, DbDataAdapter, DbDataReader etc.
由于您的 AquireConnection（）方法不返回一个具体的连接类型，你能够利用依赖注入的优势，并写code未implementation-具体。
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();
不要忘记，你将需要的Connection.close（）的地方。 （或者ReleaseConnection如果您正在使用的ConnectionManager）
Don't forget that you will need connection.Close() somewhere. (Or ReleaseConnection if you are using ConnectionManager)
这篇关于检查无论是ADO.NET / OLEDB连接presence，然后插入到数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
