Własna kontrolka SSIS, część 8 – po co mi było to OLEDB?

Od samego początku było jasne: pobieramy dane z serwera SFTP/FTPS/FTP i zapisujemy wszystkie metadane w bazie SQL Server (a potem może pomyślimy czy umożliwimy to też w innej). Do obsługi komunikacji z bazą SQL Server wybrałem arbitralnie OLEDB Connection Manager. Bo w pracy zawodowej używam najczęściej i mam opanowaną pracę z nim.

Tyle, że nie.

Znaczy się – wiem jak go używać i w ogóle. I używam. Z powodzeniem. Ale dotąd jakoś niespecjalnie używałem z poziomu programowania. Bo gdybym używał, to prawdopodobnie bym się nie zdecydował. Z dokumentacji wiedziałbym, że:

Many connection managers return unmanaged COM objects (System.__ComObject) and these objects cannot easily be used from managed code. The list of these connection managers includes the frequently used OLE DB connection manager.

Po prostu bajka. To co, zmieniamy typ managera połączeń np. na ADO.NET i korzystamy z przestrzeni nazw System.Data.SqlClient.SqlConnection, czy kombinujemy jak koń pod górę wiedząc, że jesteśmy być może skazani z góry na niepowodzenie?

No jasne, ja też wybieram to drugie.

Zgodnie z informacją z innego linku w MSDN:

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, in the managed code of a Script task. 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 with an OledbConnection from the System.Data.OleDb namespace.

Czyli w kontrolce ScriptTask dla OLEDB nie wywołamy AcquireConnection, tylko musimy kombinować z odczytywaniem parametrów połączenia i wywoływać dedykowany kod? No nie do końca – jeśli stworzymy sobie zadanie ScriptTask i wywołamy w nim AcquireConnection() na połączeniu OLEDB, to dostaniemy po prostu System.__ComObject. Dotyczy to nie tylko ScriptTask, ale też naszego własnego kodu C#. Nie będzie za łatwo korzystać z obiektu COM – jak wywołać na nim metodę np. ExecuteReader()?

Matt Mason pokazał, że można wykorzystać manager połączeń OLEDB i uzyskać obiekt typu OleDbConnection. W tym celu trzeba użyć własności InnerObject managera połączeń i rzutować ją na IDTSConnectionManagerDatabaseParameters100. Potem jeszcze tylko użyć metodę GetConnectionForSchema() i rzutować wynik na OleDbConnection.

using Microsoft.SqlServer.Dts.Runtime.Wrapper;
// (...)
ConnectionManager cm = Dts.Connections["oledb"];
IDTSConnectionManagerDatabaseParameters100 cmParams = cm.InnerObject as IDTSConnectionManagerDatabaseParameters100;
OleDbConnection conn = cmParams.GetConnectionForSchema() as OleDbConnection;

Proste i intuicyjne. Tak jakby. Żeby skorzystać z IDTSConnectionManagerDatabaseParameters100 trzeba dodatkowo podłączyć assembly Microsoft.SqlServer.DTSRuntimeWrap – znajduje się w GAC, ale nie GAC_MSIL, tylko GAC_32 i GAC_64 – w moim przypadku w C:\Windows\Microsoft.NET\assembly\GAC_32\Microsoft.SqlServer.DTSRuntimeWrap.

Ale po co komplikować sobie życie. Skoro piszą, że można odczytać i wykorzystać parametry połączenia z managera połączeń? Na przykład tak:

//cm - connection manager
System.Data.OleDb.OleDbConnection c = new System.Data.OleDb.OleDbConnection(cm.ConnectionString);
c.Open();

Przy takim podejściu otrzymamy błąd logowania, bo w cm.ConnectionString nie ma zapisanego hasła – musielibyśmy dodatkowo ustawiać hasło w kodzie co nie jest najlepszym pomysłem. Podejście Matta wydaje się ciekawsze, ale też ma małą wadę – co jeśli będziemy chcieli użyć innego managera połączeń, np. ADO.NET?

Kolejna podpowiedź, którą się podeprzemy pochodzi od Johna Welcha – to recepta dokładnie na nasze rozważania. Wykorzystujemy kod Matta Masona, dodajemy warunek – jeśli połączenie jest tylu OLEDB, to rzutuj na IDTSConnectionManagerDatabaseParameters100, a jeśli nie, to wywołaj po prostu AcquireConnection(). Przy takim opisie pojawia się pytanie – ale to będą różne typy połączeń, to mamy rozgałęziać kod? Na szczęście nie – wynikowe połączenia rzutujemy na ogólny DbConnection dostępny w przestrzeni nazw System.Data.Common:

using System.Data.Common;
using Wrap = Microsoft.SqlServer.Dts.Runtime.Wrapper;
//(...)
DbConnection conn = null;
if (cm.CreationName == "OLEDB")
{
Wrap.IDTSConnectionManagerDatabaseParameters100 cmParams =
cm.InnerObject as Wrap.IDTSConnectionManagerDatabaseParameters100;
conn = cmParams.GetConnectionForSchema() as DbConnection;
}
else
{
conn = cm.AcquireConnection(null) as DbConnection;
}

if (conn.State == ConnectionState.Closed)
{
conn.Open();
}

Prawie jesteśmy w domu. Teraz pozostaje tylko napisanie kodu, który wykorzysta DbConnection. Tu z kolei z pomocą przychodzi Stack Overflow.


// dwie pierwsze linie mamy załatwione z poziomu managera połączeń
//Database database = DatabaseFactory.CreateDatabase("connection string");
//using (var conn = database.CreateConnection())
using (var cmd = conn.CreateCommand())
{
conn.Open();
cmd.CommandText = "SELECT id FROM foo";
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
// TODO: work with the results here
}
}
}

I to działa! Podsumowując – jeśli używamy połączenia typu OLEDB do SQL Server, to:

  • Wykorzystujemy rzutowanie na IDTSConnectionManagerDatabaseParameters100
  • Połączenia do bazy uogólniamy do DbConnection
  • Dalej bazujemy na DbCommand i DbDataReader

Dzięki temu mamy bazę do komunikacji z SQL Server. Wykorzystamy to do obsługi przyrostowego pobierania plików z serwera.

Advertisements

Jedna uwaga do wpisu “Własna kontrolka SSIS, część 8 – po co mi było to OLEDB?

  1. Pingback: Własna kontrolka SSIS – część 10 – DBCommand i parametry | Takie tam

Skomentuj

Wprowadź swoje dane lub kliknij jedną z tych ikon, aby się zalogować:

Logo WordPress.com

Komentujesz korzystając z konta WordPress.com. Log Out / Zmień )

Zdjęcie z Twittera

Komentujesz korzystając z konta Twitter. Log Out / Zmień )

Facebook photo

Komentujesz korzystając z konta Facebook. Log Out / Zmień )

Google+ photo

Komentujesz korzystając z konta Google+. Log Out / Zmień )

Connecting to %s