Passare a Sql Server Compact : come leggere dati da Mdb, Xls, Xml, Dbf, Csv, senza utilizzare Microsoft Jet Database Engine 4.0

(1)

Passare a Sql Server Compact : come leggere dati da Mdb, Xls, Xml, Dbf, Csv, senza utilizzare

Microsoft Jet Database Engine 4.0

Qualche anno fa ho sviluppato un' applicazione in VB6 per l' ottimizzazione dei tagli da eseguire su barre in legno o altro materiale, questa applicazione utilizzava file mdb per il salvataggio di tutti i dati necessari.

Nel 2012 ho ripreso questa applicazione potenziandola e riscrivendola completamente in VB. Net utilizzando esclusivamente le funzioni messe a disposizione del Framework, volendo rendere questa applicazione eseguibile sia in ambiente win32 che in ambiente win64, mi sono scontrato inevitabilmente con il fatto che il motore Jet esiste solo in versione a 32 bit.

A causa di questo, e nel timore che prima o poi la Microsoft decida di togliere il supporto ai 32 bit, ho deciso di migrare tutta la gestione dei dati in Sql Server Compact distribuito gratuitamente dalla Microsoft, utilizzando AccessDatabaseEngine solo per l' importazione di dati da file Mdb, Xls, Csv, Dbf, Xml, scontrandomi con qualche problema e dopo essermi documentato con quello che gira in rete e qualche prova, ho messo assieme i vari spezzoni di codice che permettono di collegarsi ai file: Sdf, Mdb, Xls, Csv, Dbf, Xml.

Spero con questo di aiutare chi come Me si troverà a migrare i propri database mdb verso Sdf, evitando loro qualche ora di lavoro nella ricerca e prova di esempi.

Linguaggio:

Visual Basic 2008 Express Installare:

Sql Server Compact 3.5 Sp2 32 o 64 Bit scaricabili dalla pagina download di Microsoft. (Viene installato automaticamente con Visual Studio 2010 Express)

AccessDatabaseEngine versione 14.0.4730.1010 o AccessDatabaseEngine_X64 14.0.4730.1010 scaricabili dalla pagina download di Microsoft.

Riferimenti necessari: System System.IO System.Data System.XML System.Data.SqlCe 3.5

Microsoft Office 14.0 Access Database Engine Object Library (Vedi immagine sotto)

Di seguito elenco le modifiche da apportare per utilizzare i file di Sql CE (Sdf)al posto dei file di Access (Mdb), e le modifiche per collegarsi ai file Csv, Dbf, Xls tramite i drive ODBC forniti da AccessDatabaseEngine.

(2)

Creazione stringa di connessione per file Mdb, Sdf : Dim strNameDb As String = nome del file di database Access Jet Database Engine 4.0:

Private Function BuildConnectionStringMDB(ByVal strNameDb As String) As String Dim strTempConn As String = ""

strTempConn &= "Provider=Microsoft.Jet.OLEDB.4.0;" strTempConn &= "Data Source=" & strNameDb & ";" Return strTempConn

End Function

AccessDatabaseEngine :

Private Function BuildConnectionStringMDB(ByVal strNameDb As String) As String Dim strTempConn As String = ""

strTempConn &= "Provider=Microsoft.ACE.OLEDB.12.0;" strTempConn &= "Data Source=" & strNameDb & ";" Return strTempConn

End Function

Sql Server Compact Engine:

Private Function BuildConnectionStringSDF(ByVal strNameDb As String) As String Dim strTempConn As String = ""

strTempConn &= "Data Source=" & strNameDb Return strTempConn

End Function

'*************************************************************************************************************************************** Creazione stringa di connessione per file Csv:

Access Jet Database Engine 4.0:

Private Function BuildConnectionStringCSV(ByVal strNameDb As String) As String Dim strTempConn As String = ""

strTempConn = "Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=" & strNameDb & ";Extended Properties= ""Text; HDR=No;FMT=Delimited""" Return strTempConn

End Function

AccessDatabaseEngine:

Private Function BuildConnectionStringCSV(ByVal strNameDb As String) As String Dim strTempConn As String = ""

strTempConn = "Driver={Microsoft Access Text Driver (*.txt, *.csv)};Extensions=asc,csv,tab,txt;Persist Security Info=False; Dbq=" & strNameDb Return strTempConn

EndFunction

'*************************************************************************************************************************************** Creazione stringa di connessione per file Dbf:

Private Function BuildConnectionStringDBF(ByVal strNameDb As String) As String Dim strTempConn As String = ""

strTempConn = "Provider=Microsoft.Jet.OLEDB.4.0;"

strTempConn &= "Data Source=" & strNameDb & ";Extended Properties=dBASE IV;User ID=Admin;Password=;" Return strTempConn

End Function

Private Function BuildConnectionStringDBF(ByVal strNameDb As String) As String Dim strTempConn As String = ""

strTempConn = "Driver={Microsoft dBASE Driver (*.dbf)};DriverID=277;SourceType=DBF; SourceDb=" & strNameDb & ";" Return strTempConn

EndFunction

'*************************************************************************************************************************************** Creazione stringa di connessione per file Xls:

Private Function BuildConnectionStringXLS(ByVal strNameDb As String) As String Dim strTempConn As String = ""

strTempConn = "Provider=Microsoft.Jet.OLEDB.4.0;"

strTempConn &= "Data Source=" & strNameDb & ";Extended Properties=""Excel 8.0;HDR=YES; MAXSCANROWS=0; IMEX=1;"""

(3)

Return strTempConn End Function

Private Function BuildConnectionStringXLS(ByVal strNameDb As String) As String Dim strTempConn As String = ""

strTempConn = "Provider=Microsoft.ACE.OLEDB.12.0;"

strTempConn &= "Data Source=" & strNameDb & ";Extended Properties=""Excel 8.0;HDR=YES; MAXSCANROWS=0; IMEX=1;""" Return strTempConn

EndFunction

'*************************************************************************************************************************************** '*************************************************************************************************************************************** '*** Dichiarazione variabili utilizzate ***

Dim strFileNameImp As String = Path.GetFileName(strNameDb) Dim strPathNameImp As String = Path.GetDirectoryName(strNameDb) Dim strDelimit As String = (Delimitatore campi , ; Tab ecc.)

Dim strConnImp As String Dim strSQLImp As String

Utilizzo di un DataReader con file Csv tramite Odbc: Access Jet Database Engine 4.0:

Dim OdbcCmdColName As New Odbc.OdbcCommand Dim OdbcConColName As New Odbc.OdbcConnection Dim OdbcReadColName As Odbc.OdbcDataReader

'*** Crea un file Schema.ini per bypassare le definizioni riguardo ai file Csv contenute nel registro di sistema *** Call CreateSchemaFile(strPathNameImp, strFileNameImp, strDelimit)

strConnImp = BuildConnectionStringCSV(strPathNameImp) strSQLImp = "SELECT * FROM [" & strFileNameImp & "]" OdbcConColName.ConnectionString = strConnImp OdbcConColName.Open()

OdbcCmdColName.Connection = OdbcConColName OdbcCmdColName.CommandText = strSQLImp

OdbcReadColName = OdbcCmdColName.ExecuteReader

With OdbcReadColName 'Leggo la prima riga che dovrebbe contenere le definizioni dei campi(colonne) .Read()

For Idx As Integer = 0 To (.FieldCount – 1)

'Aggiunge ad una ListBox i nomi delle colonne contenute nel file Csv ListBox1.Items.Add(.GetName(Idx).ToString) Next Idx .Close() End With OdbcConColName.Close() OdbcConColName.Dispose() OdbcCmdColName.Dispose() AccessDatabaseEngine:

'*** Crea un file Schema.ini per bypassare le definizioni riguardo ai file Csv contenute nel registro di sistema *** Call CreateSchemaFile(strPathNameImp, strFileNameImp, strDelimit)

strConnImp = BuildConnectionStringCSV(strPathNameImp) strSQLImp = "SELECT * FROM [" & strFileNameImp & "]" OdbcConColName.ConnectionString = strConnImp OdbcConColName.Open()

(4)

With OdbcReadColName 'Leggo la prima riga che dovrebbe contenere le definizioni dei campi(colonne) .Read()

For Idx As Integer = 0 To (.FieldCount – 1)

'Aggiunge ad una ListBox i nomi delle colonne contenute nel file Csv ListBox1.Items.Add(.GetName(Idx).ToString) Next Idx .Close() End With OdbcConColName.Close() OdbcConColName.Dispose() OdbcCmdColName.Dispose()

Private Sub CreateSchemaFile(ByVal Path As String, ByVal FileName As String, ByVal Delimiter As String) Try

Dim buffer As New List(Of String) Dim file As System.IO.StreamWriter buffer.Add("[" & FileName & "]")

buffer.Add("ColNameHeader=True")'Dichiara che nel file sono contenuti i nomi delle colonne buffer.Add("Format=Delimited(" & Delimiter & ")")

buffer.Add("MaxScanRows=0")'Dichiara quante righe leggere per trovare i nomi delle colonne 0 = tutte buffer.Add("CharacterSet=OEM")

'Verifica se esiste il file Schema.ini, se esiste lo elimina If System.IOFile.Exists(Path & "\Schema.ini") Then System.IO.File.Delete(Path & "\Schema.ini") End If

file = System.IO.File.CreateText(Path & "\Schema.ini") For Each item As String In buffer

file.WriteLine(item) Next file.Close() Catch ex As Exception MessageBox.Show(ex.Message, "CreateSchemaFile") End Try End Sub '*************************************************************************************************************************************** '*************************************************************************************************************************************** Utilizzo di un DataReader con file Dbf tramite OleDb:

Access Jet Database Engine 4.0: Dim ConColName As OleDbConnection Dim CmdColName As OleDbCommand Dim ReadColName As OleDbDataReader

ConColName = New OleDbConnection CmdColName = New OleDbCommand

strConnImp = BuildConnectionStringDBF(strPathNameImpExp) strSQLImp = "SELECT * FROM " & strFileNameImpExp

ConColName.ConnectionString = strConnImp ConColName.Open()

CmdColName.Connection = ConColName CmdColName.CommandText = strSQLImp ReadColName = CmdColName.ExecuteReader

With ReadColName 'Leggo la prima riga che dovrebbe contenere le definizioni dei campi(colonne) .Read()

For Idx As Integer = 0 To (.FieldCount - 1)

'Aggiunge ad una ListBox i nomi delle colonne contenute nel file Dbf ListBox1.Items.Add(.GetName(Idx).ToString)

Next Idx .Close() End With ConColName.Close()

(5)

AccessDatabaseEngine tramite Odbc:

'*** Creo una copia del file Dbf con un nome formato 8+3 caratteri richiesto dal drive Odbc per file Dbf *** Dim FileDbfImport As String = MakeDbfShortName(strPathNameImp, strFileNameImp)

strConnImp = BuildConnectionStringDBF(FileDbfImport)

strSQLImp = "SELECT * FROM " & Path.GetFileName(FileDbfImport) OdbcConColName.ConnectionString = strConnImp

OdbcConColName.Open()

OdbcReadColName = OdbcCmdColName.ExecuteReader

With OdbcReadColName '*** Leggo la prima riga che dovrebbe contenere le definizioni dei campi(colonne) .Read()

'Aggiunge ad una ListBox i nomi delle colonne contenute nel file Csv For Idx As Integer = 0 To (.FieldCount - 1)

ListBox1.Items.Add(.GetName(Idx).ToString) Next Idx .Close() End With OdbcConColName.Close() OdbcConColName.Dispose() OdbcCmdColName.Dispose()

*** Funzione per la creazione di una copia del file Dbf con nome formato 8.3 caratteri ***

Private Function MakeDbfShortName(ByVal FilePath As String, ByVal FileName As String) As String MakeDbfShortName = String.Empty

Try

Dim SourceFile As String = Path.Combine(FilePath, FileName)

Dim FileShortName As String = Path.Combine(FilePath, "TmpImpXB.dbf") My.Computer.FileSystem.CopyFile(SourceFile, FileShortName, True) MakeDbfShortName = FileShortName Catch ex As Exception MessageBox.Show(ex.Message, "MakeDbfShortName") End Try End Function '*************************************************************************************************************************************** '*************************************************************************************************************************************** Utilizzo di un DataTable con file Mdb tramite OleDb:

'****** Cerca tutte le tabelle contenute nel file mdb ****** Dim dtable As DataTable

Dim ConColName As OleDbConnection Dim CmdColName As OleDbCommand ConColName = New OleDbConnection CmdColName = New OleDbCommand

strConnImp = BuildConnectionStringMDB(strNameDb) ConColName.ConnectionString = strConnImp

ConColName.Open()

CmdColName.Connection = ConColName

dtable = ConColName.GetOleDbSchemaTable(OleDb.OleDbSchemaGuid.Tables, New Object() {Nothing, Nothing, Nothing, "TABLE"}) For Idx As Integer = 0 To dtable.Rows.Count – 1

'Aggiunge ad una ListBox i nomi delle tabelle contenute nel file Mdb ListBox1.Items.Add(dtable.Rows(Idx).Item(2).ToString)

'****** Cerca tutte le tabelle contenute nel file mdb ****** Dim dtable As DataTable

strConnImp = BuildConnectionStringMDB(strNameDb) ConColName.ConnectionString = strConnImp

ConColName.Open()

'Aggiunge ad una ListBox i nomi delle tabelle contenute nel file Mdb ListBox1.Items.Add(dtable.Rows(Idx).Item(2).ToString)

'*************************************************************************************************************************************** '*************************************************************************************************************************************** Utilizzo di un DataTable con file Xls tramite OleDb:

'****** Cerca tutte le tabelle contenute nel file Xls ****** Dim dtable As DataTable

strConnImp = BuildConnectionStringXLS(strNameDb) ConColName.ConnectionString = strConnImp

ConColName.Open()

'Aggiunge ad una ListBox i nomi delle tabelle contenute nel file Xls ListBox1.Items.Add(dtable.Rows(Idx).Item(2).ToString)

'****** Cerca tutte le tabelle contenute nel file Xls ****** Dim dtable As DataTable

strConnImp = BuildConnectionStringXLS(strNameDb) ConColName.ConnectionString = strConnImp

ConColName.Open()

'Aggiunge ad una ListBox i nomi delle tabelle contenute nel file Xls ListBox1.Items.Add(dtable.Rows(Idx).Item(2).ToString)

'*************************************************************************************************************************************** '***************************************************************************************************************************************

(7)

Utilizzo di un DataReader con file Sdf tramite SqlCeDataAdapter: '****** Cerca tutte le tabelle contenute nel file Sdf ******

strConnImp = BuildConnectionStringSdf(strNameDb)

Dim sqlCeConColName As New SqlCeConnection(strConnImp) sqlCeConColName.Open()

Dim SqlCeAdapter As SqlCeDataAdapter = New SqlCeDataAdapter("SELECT * FROM

INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'TABLE'", strConnImp) Dim SqlCeTable As New DataTable

SqlCeAdapter.Fill(SqlCeTable)

For Idx As Integer = 0 To SqlCeTable.Rows.Count – 1

'Aggiunge ad una ListBox i nomi delle tabelle contenute nel file Sdf ListBox1.Items.Add(SqlCeTable.Rows(Idx).Item(2).ToString) Next

'*************************************************************************************************************************************** '*************************************************************************************************************************************** Corrispondenze dei Tipi di dati :

Sdf Mdb Xls Csv Dbf

Int DBTYPE_I4 DBTYPE_R8 CHAR Numeric

NvarChar DBTYPE_WVARCHAR DBTYPE_WVARCHAR CHAR Char

Real DBTYPE_R8 DBTYPE_R8 CHAR Numeric