Month: November 2009

Posted on

Manually Populating Datarows

I like using datarows and datatables as multi-dimensioal array structures. Their most common use seems to be iterating through datatables, but you can also populate them as you would an array but with strongly typed values. This is a demonstration of populating a datarow with a combination of datareader, datarow and direct assignment. I have encapsulated it in a function to avoid using public variables.

Function GetOrderData(ByVal rAccount As DataRow, ByVal rOrder As DataRow) As DataRow

Dim strErrorMessage As String
Dim intExitCode As Integer
Dim StartDate As Date
Dim EndDate As Date

Try

Dim strConnectionString As String = “Server=DBSERVER;Database=DB;User ID=Program;Password=Password;Trusted_Connection=False”
Dim myConnection As SqlConnection = New SqlConnection(strConnectionString)
myConnection.Open()

Dim myCommand As SqlCommand = New SqlCommand(“usp_sel_Query”, myConnection)
Dim mySqlReader As SqlDataReader

‘Get original account info
myCommand.CommandType = CommandType.StoredProcedure
myCommand.Parameters.Add(“@ID”, rAccount.Item(“ID”))

mySqlReader = myCommand.ExecuteReader
Do

While mySqlReader.Read()

If IsDBNull(mySqlReader(“Collected”)) Then rOrder.Item(“Collected”) = 0 Else rOrder.Item(“Collected”) = mySqlReader(“Collected”)
If IsDBNull(mySqlReader(“EndDate”)) Then EndDate = Nothing Else EndDate = mySqlReader(“EndDate”)
rOrder.Item(“CustomerID”) = rAccount.Item(“CustomerID”)
rOrder.Item(“OrderDate”) = rAccount.Item(“OrderDate”)
rOrder.Item(“AccountDate”) = rAccount.Item(“AcctAssign”)
rOrder.Item(“Orders”) = 1 + 5
rOrder.Item(“Package”) = rAccount.Item(“Package”)

End While

Loop While mySqlReader.NextResult()

mySqlReader.Close()
myConnection.Close()

GetOrderData = rOrder

Catch ex As Exception
strErrorMessage = ex.Message
intExitCode = Err.Number()

Finally

If intExitCode <> 0 Then

MsgBox(“Error ” & intExitCode & ” occurred for CustomerID ” & rAccount.Item(“CustomerID”) & “. ” & strErrorMessage, MsgBoxStyle.OKOnly + MsgBoxStyle.Critical)

End If

End Try

End Function

Posted on

Create Custom DataTable

Datatables are very useful containers for data and have a very familiar format for database programmers. The most common method for creating them is using a DataAdapter. When using a DataAdapter, you are obliged to use the data schema of the query used to fill the datatable. Here is a method in VB.NET for creating a datatable with exactly the columns and attributes called for in your program.

Private Function CreateOrdersTable() As DataTable
Dim strErrorMessage As String
Dim intExitCode As Integer
Try
Dim OrdersTable As DataTable
OrdersTable = New DataTable(“Orders”)

Dim Cost As DataColumn = New DataColumn
Cost.ColumnName = “Cost”
Cost.DataType = System.Type.GetType(“System.Decimal”)
OrdersTable.Columns.Add(Cost)

Dim Package As DataColumn = New DataColumn
Package.ColumnName = “Package”
Package.DataType = System.Type.GetType(“System.String”)
OrdersTable.Columns.Add(Package)

Dim CustomerID As DataColumn = New DataColumn
CustomerID.ColumnName = “CustomerID”
CustomerID.DataType = System.Type.GetType(“System.Int32”)
OrdersTable.Columns.Add(CustomerID)

Dim AccountDate As DataColumn = New DataColumn
AccountDate.ColumnName = “AccountDate”
AccountDate.DataType = System.Type.GetType(“System.DateTime”)
OrdersTable.Columns.Add(AccountDate)

CreateOrdersTable = OrdersTable

Catch ex As Exception
strErrorMessage = ex.Message
intExitCode = Err.Number()
Finally
If intExitCode <> 0 Then
MsgBox(“Error ” & intExitCode & ” occurred. ” & strErrorMessage, MsgBoxStyle.OKOnly + MsgBoxStyle.Critical)
End If
End Try

End Function