Ado.Net
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...
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]",...
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
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...
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.
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)},...