Saving an array to a SQL Server table with VB and ADO

Persisting an array of data in a SQL Server DB is usually considered quite difficult. The common solution is to use a master detail table, but this leads to a waste of space and overall decreases performance. Here we’ll see how to save an array with VB and ADO.Create the destination table with the following T-SQL code:

CREATE TABLE tblArrays (    ID numeric(18, 0) IDENTITY (1, 1) NOT NULL ,    Name char (32) NULL ,    Data image NULL ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]GO

The field named “Data” is an Image field that can store Variant or Object data. The VB class presented in this article stores an array into a PropertyBag variable, and then persists this object to the DB table. For the data read, it performs the opposite operation: the data stream is saved into a Variant variable, which represents the PropertyBag with the array. Here’s the code of the class:

' Class name: CDBVariant' Database variant class by Giuseppe Dimauro in 2000Dim mCn As ADODB.Connection' set the connection to be used by the classPublic Property Set DataSource(newVal As ADODB.Connection)    Set mCn = newValEnd Property' read the Variant from the DB' tblName = source table name' Name = variable name to be readPublic Property Get DBVariant(tblName As String, Name As String) As Variant    Dim strFilter As String, pb As New PropertyBag, vtTemp As Variant    Dim rs As New ADODB.Recordset    strFilter = "Name = '" & Name & "'"    rs.Open "SELECT * FROM " & tblName & " WHERE " & strFilter, mCn, _        adOpenStatic, adLockPessimistic        If Not rs.EOF And Not rs.BOF Then        vtTemp = rs!Data        pb.Contents = vtTemp        If pb.ReadProperty("Size", 0) = 0 Then            DBVariant = pb.ReadProperty("Value", vbNull)        Else            Dim vtTemp2() As Variant            ReDim vtTemp2(pb.ReadProperty("Size", 0)) As Variant            For i = 0 To UBound(vtTemp2)                vtTemp2(i) = pb.ReadProperty(CStr(i))            Next            DBVariant = vtTemp2        End If    End If        Set rs = NothingEnd Property' write the Variant to the DB' tblName = destination table name' Name = variable namePublic Property Let DBVariant(tblName As String, Name As String, _    newValue As Variant)    Dim strFilter As String, pb As New PropertyBag    Dim rs As New ADODB.Recordset    Dim bRemove As Boolean    strFilter = "Name = '" & Name & "'"    rs.Open "SELECT * FROM " & tblName & " WHERE " & strFilter, mCn, _        adOpenStatic, adLockPessimistic            If VarType(newValue) = vbObject Then        bRemove = newValue Is Nothing    Else        bRemove = False    End If        If Not bRemove Then        If rs.EOF And rs.BOF Then            rs.AddNew            rs!Name = Name        End If        If (VarType(newValue) And vbArray) = 0 Then            ' valore non array            pb.WriteProperty "Size", 0            pb.WriteProperty "Value", newValue        Else            ' preparazione di uno stream per array            pb.WriteProperty "Size", UBound(newValue)            For i = 0 To UBound(newValue)                pb.WriteProperty CStr(i), newValue(i)            Next        End If        rs!Data = CVar(pb.Contents)        rs.Update    Else        If Not rs.EOF And Not rs.BOF Then            rs.Delete        End If    End If    Set rs = NothingEnd Property

This code shows how to use the class:

Dim t As New cDBVariantDim cn As New ADODB.ConnectionDim vArray(100) As IntegerDim vArray2 As Variant' open a connectioncn.Open "file name=c:udlmaster.udl"For i = 1 To UBound(vArray)    vArray(i) = iNext' assign the connection to the classSet t.DataSource = cn' write the arrayt.DBVariant("tblArrays", "vArray") = vArray' read the arrayvArray2 = t.DBVariant("tblArrays", "vArray")' dump resultsDebug.Print UBound(vArray2)For i = 1 To UBound(vArray2)    Debug.Print vArray2(i)Next

Note that the .UDL file is a data link file, but of course you can use any connection string.

########################################################
This tip has been originally published on Microsoft Italia’s web site. It has been translated and re-published on VB2TheMax with the permission of Microsoft Italia. You can find more tips like this one (in Italian) at http://www.microsoft.com/italy/sql/articoli
########################################################

Share the Post:
Share on facebook
Share on twitter
Share on linkedin

Overview

The Latest

Top 5 B2B SaaS Marketing Agencies for 2023

In recent years, the software-as-a-service (SaaS) sector has experienced exponential growth as more and more companies choose cloud-based solutions. Any SaaS company hoping to stay ahead of the curve in this quickly changing industry needs to invest in effective marketing. So selecting the best marketing agency can mean the difference

technology leadership

Why the World Needs More Technology Leadership

As a fact, technology has touched every single aspect of our lives. And there are some technology giants in today’s world which have been frequently opined to have a strong influence on recent overall technological influence. Moreover, those tech giants have popular technology leaders leading the companies toward achieving greatness.

iOS app development

The Future of iOS App Development: Trends to Watch

When it launched in 2008, the Apple App Store only had 500 apps available. By the first quarter of 2022, the store had about 2.18 million iOS-exclusive apps. Average monthly app releases for the platform reached 34,000 in the first half of 2022, indicating rapid growth in iOS app development.