How do you dimension these variables?

[jbellCNS]

I am re-arranging some sql data, and am having issues building my query.

The source table layout is as follows:

id
filename
src
value

In my program, I know the destination table/col from the src column. There will be many rows in the src table, even for a single update query, and it will build the where clause for a number of the values based on the source.

The thing is, there will be multiple column/values for each query that is executed. Right now I have created a class called sqlTable to contain the information for the queries. Each table object has a list of queries, and I want a list of column/value pairs for each query. I have been trying to use the where clause as the identifier for each query.

I have been doing a lot of PHP in recent years, and I know exactly how I would code this in PHP. I am trying to build a variable that looks like this:

while ($row = mssql_fetch_assoc) {
    $arrQuery['strWhere']['col'][]= $row['col']
    $arrQuery['strWhere']['val'][]= $row['val']
}

However I have been having problems with getting the syntax correct for VB Express 2010 (if this is the wrong forum please tell me where I need to move this thread to)

I have gone through a number of attempted solutions using lists, dictionaries, and collections over the course of the past week. How can I get that kind of functionality using VB.net syntax? Any input would be most welcome.

 

[ousoonerjoe]

Have you looked at the DataSet and DataTable objects? If I'm understanding your question correctly, you can load the incoming data into a DataTable and retrieve the specific row-column value you are looking for. In fact, since you are pulling the data out of SQL, you load it directly into a DataSet (which contains a collection of DataTables; Which contains the, forgive the old terminology, a recordset from a single SELECT statement.).

Look into the SqlClient Object and how it connects to a database and how you can easily move data in and out.

--------------------------------------------------
“Crash programs fail because they are based on the theory that, with nine women pregnant, you can get a baby a month.” --Wernher von Braun
--------------------------------------------------

 

[jbellCNS]

ousoonerjoe:

Thank you for your feedback. I've looked into the DataSet and DataTable objects like you were talking about, and while it has been somewhat helpful, it hasn't directly addressed the issue I am dealing with.

I see it as a three step process.

Step 1: Select existing SQL from a source table Status: SUCCESS. This works.

Step 2: Loop through the results, and assign the values to their correct location based on the saved source column. This is where I am having issues.

I have a large logic block that looks at [sourcetable].src, and evaluates that column to figure out where the data and all the data from subsequent rows need to go, until it reaches another .src value that changes the destination. I need a place to store the related parts of the query until I know that I have all the pieces of the query and I am ready to execute it.

My thoughts were to store it in a multidimensional variable, so that the column/value pairs would be related to their appropriate table name and where clause. Design-wise, this makes sense to me, but I am having issues with the syntax.

Right now I have created a simple class called clsSqlTable. This is the stripped down version of that class:

Public Class sqlTable
    Dim tName As String
    Dim dQueries As New Dictionary(Of String, query2)
    Dim strWhereCurrent As String

    Function sqlTable(ByVal tName)
        Me.tName = tName
        Return Me
    End Function

    Function addQuery(ByVal dColVals(,) As String, ByVal strWhere As String)
        Dim cnt As Integer = (dColVals.Length / 2) - 1
        Dim objQuery1 As New query1()
        For i = 0 To cnt Step 1

            objQuery1.add(dColVals(i, 0), dColVals(i, 1))
            'colval.val = dColVals(i, 1)

            If dQueries.ContainsKey(strWhere) Then
                'dQueries(strWhere).add(colval.col, colval.val)
            Else
                'dQueries.Add(strWhere, colval)
            End If

            If dQueries.ContainsKey(strWhere) Then ' TryGetValue(strWhere, colval) Then

                'dQueries.Item(strWhere).Add(colval(0),colval(1))

            Else
                Debug.Print("else")
                'dQueries.Add(strWhere, colval)
            End If

        Next

        Return Nothing
    End Function

    Public Function executeQueries()

        Return Nothing
    End Function

    Private Function colval(ByVal p1 As Integer) As Object
        Throw New NotImplementedException
    End Function

End Class

Public Class query1
    Public Property where As String = ""
    Public Property colval As New List(Of query2)

    Public Function add(ByVal col, ByVal val)
        Dim objColVal As New query2
        objColVal.col = col
        objColVal.val = val
        Me.colval.Add(objColVal)
        Return Nothing
    End Function
End Class

Public Class query2
    Public Property col As String
    Public Property val As String
End Class

I've re-worked this section a number of times trying to get the data set to look like:

root
    strWhere
        col="[table.column]"
        val="value to insert"

so that I can loop through it and assemble the final insert query the way it needs to be.

Step 3: Insert data back into the database in the destination tables. While this is technically functional, it will not actually be working correctly until I finish crafting the query correctly in step 2.

 

[ousoonerjoe]

Here is a crude example of connecting to the database, executing a SELECT, then reading from the records returned to do an INSERT directly from the SELECT records.

	Public Sub ReadInsert()
		Dim Cnn As SqlClient.SqlConnection = Nothing
		Dim Cmd As SqlClient.SqlCommand = Nothing
		Dim Rst As SqlClient.SqlDataAdapter = Nothing
		Dim dSet As DataSet = Nothing
		Dim TmpDt As DataTable = Nothing
		Dim x As Integer = 0
		Try
			Cnn = New SqlClient.SqlConnection(ConnectionString)
			If Cmd Is Nothing Then Cmd = New SqlClient.SqlCommand
			Cmd.Connection = Cnn
			Cmd.CommandType = CommandType.StoredProcedure
			Cmd.CommandText = "cst_Select_AssemblyLine"
			Cmd.Parameters.AddWithValue("@PopRequest", 1)
			Cmd.Parameters.AddWithValue("@Division", Division)
			If Cnn.State <> ConnectionState.Open Then Cnn.Open()
			Rst = New SqlClient.SqlDataAdapter(Cmd)
			dSet = New DataSet
			Rst.Fill(dSet)
			TmpDt = dSet.Tables(0)
			Cmd.CommandText = "INSERT_Procedure"
			For x = 0 To (TmpDt.Rows.Count - 1) Step 1
				Cmd.Parameters.Clear()
				If TmpDt.Rows(x).Item("Field1") = "Something" Then
					Cmd.Parameters.AddWithValue("@Arg1", TmpDt.Rows(x).Item("Field1"))
					Cmd.Parameters.AddWithValue("@Arg2", TmpDt.Rows(x).Item("Field2"))
					Cmd.ExecuteNonQuery()
					'(or you can fill another Rst if you need to check for a return record)
				End If
			Next x
		Catch Ex As Exception
			Ex.Source = System.Reflection.MethodBase.GetCurrentMethod.Name
			'Error Notice to user, etc.
		Finally
			dSet = Nothing
			Rst = Nothing
			Cmd = Nothing
			If Cnn.State <> ConnectionState.Closed Then Cnn.Close()
			Cnn = Nothing
			TmpDt = Nothing
		End Try
	End Sub

There is of course the Array and ArrayList objects that you can use. Or you can always create an array of Structures.

--------------------------------------------------
“Crash programs fail because they are based on the theory that, with nine women pregnant, you can get a baby a month.” --Wernher von Braun
--------------------------------------------------