TableAdapter: Use a transaction with an TableAdapter

The easiest way to use an transaction with a TableAdapter is to use an TransactionScope.  First create a TransActionScope, update the database and finally commit the transaction.  Note not all database type work with System.Transactions. Add a reference to System.Transaction for this example. 

        Using tc As New TransactionScope
            Try
                EmployeeTableAdapter.Update(PubsDataSet.employee)
                'complete the transaction if there were no errors
                tc.Complete()
            Catch
                'something went wrong let the transaction roll back
            End Try
        End Using

Unable to Update Database added to Project

I see from time to time people complaining they can not update an database they added to their Visual Studio 2005 project. Sometimes your database is actually getting updated but Visual Studio is copying the old version of the database over the updated version. You should check and make sure the Database's Copy to Output property is not set to Copy Always. It should be set to Copy if newer.

Inner Join with 2 or more tables

If you are getting data from more than 2 tables you need to surround the inner joins in brackets. For this example I am using the just released SQL Compact Edition. You will find a sample NorthWind database in the sdk. Note to work with a SQL CE database you need to add a reference to the System.Data.Sqlce.dll you find in the directory you installed sql server ce.

Imports System.Data.SqlServerCe
Imports System.Text
 
Public Class Form1

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim conn As New SqlCeConnection("data source='NorthWind.sdf'; mode=Exclusive;")
        Dim sbCommand As New StringBuilder
        Dim dt As New DataTable
        sbCommand.Append("Select Orders.[Customer ID], Products.[Product Name], [Order Details].Quantity, [Order Details].[Unit Price] From ")
        sbCommand.Append("(Orders Inner Join [Order Details] on Orders.[Order ID] = [Order Details].[Order ID]) ")
        sbCommand.Append("Inner Join Products on [Order Details].[Product ID]= Products.[Product ID]")

        Dim da As New SqlCeDataAdapter(sbCommand.ToString, conn)
        da.Fill(dt)
        DataGridView1.DataSource = dt
        With DataGridView1.Columns("Unit Price").DefaultCellStyle
            .Format = "c"
            .Alignment = DataGridViewContentAlignment.MiddleRight
        End With
    End Sub
End Class

Find Duplicate Records in a Datatable

I was asked how to find duplicate records in a datatable, I used a dataview to sort my records. I used the sorted list to check the next record for a duplicate record. The duplicate will be deleted.

Imports System.Security.Cryptography

Public Class Form1
    Dim dt As New DataTable

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        dt.Columns.Add("Name")
        dt.Columns.Add("Number", GetType(Integer))

        For x As Integer = 0 To 200
            Select Case x Mod 3
                Case 0
                    dt.LoadDataRow(New Object() {"Ken", TrueRandom.Rand(100)}, True)
                Case 1
                    dt.LoadDataRow(New Object() {"Kelly", TrueRandom.Rand(100)}, True)
                Case 2
                    dt.LoadDataRow(New Object() {"Bill", TrueRandom.Rand(100)}, True)
            End Select
        Next

        DataGridView1.DataSource = dt
        FindDups(dt)
    End Sub

    Public Sub FindDups(ByVal dt As DataTable)
        Dim dv As New DataView(dt)
        dv.Sort = "Number, Name"
        For x As Integer = 0 To dv.Count - 2
            Dim drv As DataRowView = dv.Item(x)
            Dim drvNext As DataRowView = dv.Item(x + 1)
            If drv.Item("Name").ToString = drvNext.Item("Name").ToString AndAlso drv.Item("Number").ToString = drvNext.Item("Number").ToString Then
                drv.Delete()
            End If
        Next
    End Sub
End Class

Public Class TrueRandom
    Public Shared Function Rand(ByVal MaxNum As Integer) As Integer
        Dim rnd(20) As Byte
        Dim num As Long

        Dim generator As New RNGCryptoServiceProvider

        generator.GetBytes(rnd)
        For x As Integer = 0 To 20
            num += CInt(rnd(x))
        Next x
        Return CInt(num Mod MaxNum)
    End Function

End Class