In this post we will create a local cache of the Northwind database.  To start with lets create a new visual basic windows forms project in Visual Studio 2008.  From the project menu select add a new item and select a new local database cache and name it northwind.

 

 

In the server connect select a connection to the northwind database.

 

Press the add button and select the product table.  Press OK to close the dialog.  Go ahead and create a table adapter for the product table.  The drag the products table on to the form from the data source window.  Add a button to the binding navigator and set its text to Sync and change the display style to Text.

 

In the button you added to the toolbar add this code

 

Private Sub ToolStripButton1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ToolStripButton1.Click

       ' Update the database

        Me.ProductsBindingSource.EndEdit()         Me.TableAdapterManager.UpdateAll(Me.NorthwindDataSet)

        ' Call SyncAgent.Synchronize() to initiate the synchronization process.

        ' Synchronization only updates the local database, not your project’s data source.

        Dim syncAgent As ProductsSyncAgent = New ProductsSyncAgent()         Dim syncStats As Microsoft.Synchronization.Data.SyncStatistics = syncAgent.Synchronize()

       ' Reload the data source from the local database

        Me.ProductsTableAdapter.Fill(Me.NorthwindDataSet.Products)

End Sub

Run the app and Open up the Sql Server Management Studio Express.  Make some changes in the Northwind database's Product table and Press the sync button.  

 

Notice the changes you made to the Products table show up in the datagridview.  The changes are also saved in the local sqlce database.

Change the DataType of a Column

Sometimes when you fill a DataTable the .Net framework does not get the data type right.  Unfortunately once you fill a data table you can not change the data type.  You can use the data adapters FillScheme method to setup the data table this will allow you to be to change the data type.  Then you can fill the datatable with the data of the right type. .

VB Example
Dim conn As New SqlClient.SqlConnection("Server = .\sqlexpress; Database = NorthWind; " & _

"Integrated Security = sspi;")

Dim dt As New DataTable

Dim da As New SqlClient.SqlDataAdapter("Select * from [Order Details]", conn)

da.FillSchema(dt, SchemaType.Mapped)

dt.Columns("OrderID").DataType = GetType(Integer)

da.Fill(dt)

C# example

            SqlConnection  conn = new SqlConnection(
                 @"Server = .\sqlexpress; Database = NorthWind;Integrated Security  = sspi;");
            DataTable dt = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter("Select * from [Order Details]", conn);
            da.FillSchema(dt, SchemaType.Mapped);
            dt.Columns["OrderID"].DataType = typeof(int);
            da.Fill(dt);

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