VSTO & .NET & Excel

November 8, 2011

Excel & Valentina Database

Filed under: .NET & Excel, COM Add-ins, Excel, SQLite, Tools, Valentina DB, Valentina Office Server, VSTO & Excel — Dennis M Wallentin @ 1:42 am

Introducing the Valentina Database

I thought I would introduce the Valentina Database (Valentina DB) with this article and discuss how we can connect to it by using ODBC, both in VBA and in VB.NET.

For the last 11-12 months I have been using Valentina DB as an embedded database in all clients’ solutions where it requires some data storage. Some of them run 24/7 and up to this date no errors have been reported related to the database in use. It has mainly been used in managed COM add-ins (Shared add-ins and VSTO add-ins) and by using the .NET driver & APIs for the database.

Using the ODBC technology is still a common way. First of all, it’s a fast way to establish a connection to a data source. It’s also a reliable technology as it has been around for the last 20 years or so.

Actually, we can say exactly the same about the Valentina DB although it has been around “only” about 10 years. In addition, it’s a really fast and compact database with a small footprint. Therefore I find it to be a great companion when I need an embedded database solution. Valentina DB can also be connected to the Valentina Office Server on both x86 and x64 platforms.

Another advantage is, compared with other databases, that it targets a great number of operating system and development platforms. So per se it’s a true cross-platform database. I will later discuss in a coming article more details about developing .NET solutions by using the Valentina DB.

On several development platforms we have access to Valentina APIs library. It’s a quite powerful library. For instance, we can use APIs instead of using SQL the traditionally way, i.e. it’s like LINQ.

Valentina DB is also a maturity software as it has been around since the late 90’s. The general price level should not scare away anyone; expect those that looking for free options.

The company behind Valentina DB, Paradigma Software, offers a good support and maintenance a public mailing list for Q&A. The only thing that I regret is that the Valentina DB Wiki documentation is not yet complete. This is rather typical for small companies; they lack the man hours to keep up the pace with both their product(s) and the documentation to them.

What Paradigma Software says themselves about Valentina DB is:

  • Performs Queries x10 to x500 Faster than Other Databases
  • Convert from Old Relational Databases and Reduce Sizes by 1/3
  • Smart SQL Means Shorter, Easier to Read SQL Queries
  • Modern Post Relational Architecture
  • Connects with All Major Environments on Windows, Linux, Mac OS X
  • Royalty Free Embedded Server Deployment

In addition to the Valentina DB product Paradigma Software also offers related tools such as the Valentina Pro Studio, which supports 18+ Valentina DB platforms. The Studio enables us to work with databases and also to connect to Valentina Office Servers. We can also use their excellent Valentina Reports to create highly customized reports both for Valentina DBs and also for SQLite DBs. Once installed Valentina Reports will be part of the Studio.

Valentina DB Homepage

Valentina DB Wiki

Connecting to a Valentina DB through VBA using ODBC

I have intentionally left out all comments of the code as it should be rather self-explanatory. What we need to do before using the licensed ODBC driver is to register it on the computer which requires us to create a user DSN or a File DSN.

In the first case I use a DSN file as the source for the connection string. It contains the following information:

[ODBC]
DRIVER=Valentina ODBC Driver
Database=C:\Users\Dennis Wallentin\Documents\Valentina DBs\FirstDB.vdb
IsDatabaseLocal=yes

In the second case I create the connection string in code. In both cases I use the Microsoft ADO library in order to work with the database and with its data.

'Make sure You set a reference to
'Microsoft ActiveX Data Objects Library x.x

Option Explicit

Sub Retrieve_Data_ValentinaDB_ADO_FILEDSN()

Dim adoConnection As ADODB.Connection
Dim adoRecordset As ADODB.Recordset

Dim wsTarget As Worksheet

Dim stSQL As String

Dim lnCountFields As Long

Set adoConnection = New ADODB.Connection
Set adoRecordset = New ADODB.Recordset

Set wsTarget = ThisWorkbook.Worksheets("Data")

stSQL = "SELECT * FROM Person"

adoConnection.Open ConnectionString:= _
"FileDSN=C:\Users\Dennis Wallentin\Documents\Valentina.dsn"

adoRecordset.Open Source:=stSQL, ActiveConnection:= _
"FileDSN=C:\Users\Dennis Wallentin\Documents\Valentina.dsn"

lnCountFields = adoRecordset.Fields.Count

Application.ScreenUpdating = False

For lnCountFields = 0 To lnCountFields - 1
wsTarget.Cells(1, lnCountFields + 1).Value = _
adoRecordset.Fields(lnCountFields).Name
Next

wsTarget.Cells(2, 1).CopyFromRecordset adoRecordset

adoRecordset.Close
adoConnection.Close

Set adoRecordset = Nothing
Set adoConnection = Nothing

End Sub

 

Sub Retrieve_Data_ValentinaDB_ADO_ODBC_Driver()

Dim adoConnection As ADODB.Connection
Dim adoRecordset As ADODB.Recordset

Dim wsTarget As Worksheet

Dim stSQL As String
Dim stConnection As String
Dim stDataBase As String

Dim lnCountFields As Long

Set adoConnection = New ADODB.Connection
Set adoRecordset = New ADODB.Recordset

Set wsTarget = ThisWorkbook.Worksheets("Data")

stDataBase = "C:\Users\Dennis Wallentin\Documents\Valentina DBs\FirstDB.vdb"
stConnection = "Driver={Valentina ODBC Driver};IsDatabaseLocal=yes;Database=" & _
stDataBase
stSQL = "SELECT * FROM Person"

adoConnection.Open ConnectionString:=stConnection

adoRecordset.Open Source:=stSQL, ActiveConnection:=stConnection

lnCountFields = adoRecordset.Fields.Count

Application.ScreenUpdating = False

For lnCountFields = 0 To lnCountFields - 1
wsTarget.Cells(1, lnCountFields + 1).Value = _
adoRecordset.Fields(lnCountFields).Name
Next

wsTarget.Cells(2, 1).CopyFromRecordset adoRecordset

adoRecordset.Close
adoConnection.Close

Set adoRecordset = Nothing
Set adoConnection = Nothing

End Sub

Connecting to a Valentina DB through VB.NET using ODBC

The ODBC driver can also be used in VB.NET but it exist a specific version of Valentina that explicit target the .NET platform. In general I recommend using it.

Although the following case shows how we can populate a worksheet with data based on the ODBC datareader I recommend to use classic ADO instead, like above, as we can dump all the data once via the method CopyFromRecordset.

Imports System
Imports System.Data
Imports System.Data.Odbc
Imports Excel = Microsoft.Office.Interop.Excel

Public Class Form1

Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) _
Handles Button1.Click

'Snabbast är att använda classic ADO.

Dim odbcCon As OdbcConnection = Nothing
Dim odbcCom As OdbcCommand = Nothing
Dim odbcDataR As OdbcDataReader = Nothing

Dim stConnection As String = String.Empty
Dim stDatabase As String = "C:\Users\Dennis Wallentin\Documents\Valentina DBs\FirstDB.vdb;"
Dim stSQL As String = "SELECT * FROM Person"

stConnection = "Driver={Valentina ODBC Driver};IsDatabaseLocal=yes;Database=" + stDatabase

Dim stSoureFile As String = "C:\Users\Dennis Wallentin\Documents\Data\SalesData.xlsm"

Try
odbcCon = New OdbcConnection(stConnection)
odbcCom = New OdbcCommand(stSQL, odbcCon)
odbcCom.CommandType = CommandType.Text
odbcCon.Open()
odbcDataR = odbcCom.ExecuteReader(CommandBehavior.CloseConnection)

'In order to dimension the array for field names the number of columns need to be retrieved.
Dim intColumnsCounter As Int32 = odbcDataR.FieldCount - 1

Dim FieldsArr(0, intColumnsCounter) As String

Dim intRowsCounter As Int32 = 0

'The DataReader is a forward-only and read-only recordset. Therefore we cannot
'know in advance the number of records it will return. The only possible solution
'is to estimate the max number of records and use it in the solution.
Dim intMaxRecords As Int32 = 10000

Dim DataArr(intMaxRecords, intColumnsCounter) As Object

'Populate the array of field names by using the DataReader's method 'GetName'.
For intNameCounter As Int32 = 0 To intColumnsCounter
FieldsArr(0, intNameCounter) = odbcDataR.GetName(intNameCounter)
Next

'Populate the array of records by reading all the records in the DataReader.
While odbcDataR.Read
For intRecordCounter As Int32 = 0 To intColumnsCounter
DataArr(intRowsCounter, intRecordCounter) = odbcDataR.Item(intRecordCounter)
Next
intRowsCounter = intRowsCounter + 1
End While

'Close both the connection and the DataReader.
odbcCon.Close()
odbcDataR.Close()

Dim xlApp As Excel.Application = New Excel.Application
Dim wbBook As Excel.Workbook = xlApp.Workbooks.Open(stSoureFile)
Dim wsSheet As Excel.Worksheet = TryCast(wbBook.Worksheets("Data"), Excel.Worksheet)

Dim xlCalc As Excel.XlCalculation = xlApp.Calculation
xlApp.Calculation = Excel.XlCalculation.xlCalculationManual

With wsSheet
.Range(.Cells(1, 1), .Cells(1, intColumnsCounter + 1)).Value = FieldsArr
.Range(.Cells(2, 1), .Cells(intRowsCounter + 2, intColumnsCounter + 1)).Value = DataArr
.UsedRange.Columns.AutoFit()
End With

With xlApp
.Visible = True
.UserControl = True
'Restore the calculation mode.
.Calculation = xlCalc
End With

Catch ex As Exception

MessageBox.Show(ex.ToString())

End Try

'Prepare variables for the Garbage Collector.
'Don't forget to do a proper release of alla COM objects.

odbcCom.Dispose()
odbcCon.Dispose()

odbcDataR = Nothing
odbcCom = Nothing
odbcCon = Nothing

End Sub

End Class

Valentina DB offers so much that I sometimes feels that only the sky is the limit. I learn every day something new about the database and its features and the more I learn the more impressed I am.

Enjoy!

Kind regards,
Dennis

Advertisements

4 Comments »

  1. >>>>Performs Queries x10 to x500 Faster than Other Databases

    This really caught my attention 😀

    Nice One Dennis 🙂

    Couple of questions.

    1) Could this be a deterrent if I am developing for a client? The client might not have the relevant drivers.
    2) Have you tested the code for vb6? Yes, some of my clients are still stuck with vb6 and refuse to budge from it LOL…
    3) Can the drivers be redistributed as a part of my setup if I use it in VBA/VB6/VB.Net/VSTO ?

    Sid

    Comment by Siddharth Rout — November 8, 2011 @ 9:42 am

  2. Sid,
    Let’s see if I can clear some mud or not. Actually, I will ask someone from Paradigma to review my answers and correct them if I have made the wrong interpretation of the licenses’ rules.

    The ODBC driver is special, because it’s a standalone driver and therefore it must be bought for each computer an ODBC-solution is intended to run on. Since this is the only option for VBA it means that the client must buy licenses. Paradigma offer a Valentina COM for VB6 but it should work to use the ODBC driver with VB6.

    If the customer is not prepared to pay for the ODBC driver then it may be deterrent but as with all things it should be viewed of what Valentina can contribute with.

    On the .NET platform it exist a special Valentina development package for it (including a “driver”). Therefore it should not trigger any additional expensives for Your clients when You ship any.NET solutions, including VSTO solutions.

    Actually, I still use VB6 for a new client. It was rather surprising but they have made huge investment in VB6 code and are not prepared to port it to .NET. One major factor is the performance.

    BTW, today Paradigma announced that the Valentina Server 5 connections will be free for certain tasks and for certain groups. If You have SMBs as clients (which I guess You have) then this offer should be very interesting. The version of the Server that will be free includes 5 connections (at the same time) to the server. Not only that, the Studio, Report and ADKs will also be free under the same conditions. I recommend You to check it out.

    You and other can check it out at the following URL:
    http://www.valentina-db.com/en/company/news/1-latest-news/257-paradigma-software-releases-free-business-ready-database-and-reports-server

    Kind regards,
    Dennis

    Comment by Dennis Wallentin — November 8, 2011 @ 4:32 pm

  3. I will go through the links Dennis 🙂

    Cheers

    Sid

    Comment by Siddharth Rout — November 8, 2011 @ 4:56 pm


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: