.Net HowTo - Using Disconnected Data from a Database
In order to use data while disconnected from a database, it needs to be loaded in one form or other from the database and kept in memory or stored into a different object. The following code illustrates how to do this by using a datagrid on a windows form. This takes the previous connection how-to a step further, because we are closing the connection after the info is loaded, then allowing for changes to the data in the dataset, and afterwards we can save the newly changed data after opening a new connection again. This example is using a windows form with a datagrid (dgData), a button to load the data (btnLoad) and a button to save the data (btnSave):
Dim objDS As New DataSet
Private Sub btnLoad_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLoad.Click Dim objConn As New SqlConnection Dim objDA As SqlDataAdapter
Try objConn.ConnectionString = "server=(local);database=northwind;integrated security=SSPI" objConn.Open() objDA = New SqlDataAdapter("SELECT * FROM customers ORDER BY companyname", objConn) objDA.Fill(objDS, "customers") objConn.Close() dgData.DataSource = objDS.Tables("customers") Catch objError As Exception MessageBox.Show(objError.Message) End Try End Sub
Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click Dim objConn As New SqlConnection Dim objDA As SqlDataAdapter Dim objBuilder As SqlCommandBuilder
Try objConn.ConnectionString = "server=(local);database=northwind;integrated security=SSPI" objConn.Open() 'the following is being used as a template to create the update, delete, and instert commands objDA = New SqlDataAdapter("SELECT * FROM customers ORDER BY companyname", objConn) objDA.Fill(objDS, "customers") objBuilder = New SqlCommandBuilder(objDA) objDA.DeleteCommand = objBuilder.GetDeleteCommand objDA.InsertCommand = objBuilder.GetInsertCommand objDA.UpdateCommand = objBuilder.GetUpdateCommand
objDA.Update(objDS, "customers") objConn.Close() Catch objError As Exception MessageBox.Show(objError.Message) End Try End Sub
This scenario comes in handy when developing applications that remote users may use. However, the inherent complexity comes into play when the user connects again and attempts to update records that have been changed since they were originally downloaded by this user.