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]GOThe 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 PropertyThis 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)NextNote 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
########################################################
