Login | Register   
LinkedIn
Google+
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


Tip of the Day
Language: VB6,SS7,SS2K
Expertise: Advanced
Jul 17, 2002

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 2000

Dim mCn As ADODB.Connection

' set the connection to be used by the class
Public Property Set DataSource(newVal As ADODB.Connection)
    Set mCn = newVal
End Property

' read the Variant from the DB
' tblName = source table name
' Name = variable name to be read
Public 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 = Nothing

End Property

' write the Variant to the DB
' tblName = destination table name
' Name = variable name
Public 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 = Nothing

End Property
This code shows how to use the class:

Dim t As New cDBVariant
Dim cn As New ADODB.Connection

Dim vArray(100) As Integer
Dim vArray2 As Variant

' open a connection
cn.Open "file name=c:\udl\master.udl"

For i = 1 To UBound(vArray)
    vArray(i) = i
Next

' assign the connection to the class
Set t.DataSource = cn

' write the array
t.DBVariant("tblArrays", "vArray") = vArray

' read the array
vArray2 = t.DBVariant("tblArrays", "vArray")

' dump results
Debug.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
########################################################

Francesco Balena
 
Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap