Synchronize Database operation

<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.OleDb" %>

<script language="VB" runat="server">

  Sub Page_Load(Sender As Object, E As EventArgs)

    Dim strConnection As String
    Dim strSQL        As String
    Dim objDataSet    As New DataSet()
    Dim objConnection As OleDbConnection
    Dim objAdapter    As OleDbDataAdapter

'    strConnection = "Provider=Microsoft.Jet.OLEDB.4.0; " & _
 '                   "Data Source=C:\Northwind.mdb"
     strConnection = "Provider=sqloledb;Data Source=whsql-v08.prod.mesa1.secureserver.net;Initial Catalog=DB_49907;User ID=java2suser;Password=password;"
  
    strSQL = "SELECT EmployeeID, FirstName, LastName FROM Employees;"

    objConnection = New OledbConnection(strConnection)
    objAdapter = New OledbDataAdapter(strSQL, objConnection)

    objAdapter.Fill(objDataSet, "Employees")

    dgNameList1.DataSource = objDataSet.Tables("Employees").DefaultView
    dgNameList1.DataBind()

    Dim objTable  As DataTable
    Dim objNewRow As DataRow
  
    objTable = objDataSet.Tables("Employees")
    objNewRow = objTable.NewRow()
    objNewRow("FirstName") = "Norman"
    objNewRow("LastName") = "Blake"
    objTable.Rows.Add(objNewRow)
    
    objNewRow = objTable.NewRow()
    objNewRow("FirstName") = "Kasey"
    objNewRow("LastName") = "Chambers"
    objTable.Rows.Add(objNewRow)


    dgNameList2.DataSource = objTable.DefaultView
    dgNameList2.DataBind()


    Dim objRow As DataRow
    
    objRow = objTable.Rows(3)
    objRow("FirstName") = "John"
    objRow("LastName") = "Hartford"

    ' bind the data grid to the new data
    dgNameList3.DataSource = objTable.DefaultView
    dgNameList3.DataBind()


    objTable.Rows(objTable.Rows.Count - 2).Delete()
    
    dgNameList4.DataSource = objTable.DefaultView
    dgNameList4.DataBind()


    Dim objBuilder    As OleDbCommandBuilder

    objBuilder = New OleDbCommandBuilder(objAdapter)
    objAdapter.UpdateCommand = objBuilder.GetUpdateCommand()
    objAdapter.InsertCommand = objBuilder.GetInsertCommand()
    objAdapter.DeleteCommand = objBuilder.GetDeleteCommand()

    objAdapter.Update(objDataSet, "Employees")
    
    strSQL = "SELECT EmployeeID, FirstName, LastName FROM Employees"
    objConnection.Open()
    Dim objCmd As New OleDbCommand(strSQL, objConnection)
    dgUpd.DataSource = objCmd.ExecuteReader(CommandBehavior.CloseConnection)
    dgUpd.DataBind()
  
  End Sub

</script>
<html>
 <body>
  <table width="100%">
   <tr>
    <td>Original Data</td>
    <td>Data with new Row</td>
    <td>Data with edited Row</td>
    <td>Data with deleted Row</td>
   </tr>
   <tr>
    <td valign="top"><asp:DataGrid id="dgNameList1" runat="server" /></td>
    <td valign="top"><asp:DataGrid id="dgNameList2" runat="server" /></td>
    <td valign="top"><asp:DataGrid id="dgNameList3" runat="server" /></td>
    <td valign="top"><asp:DataGrid id="dgNameList4" runat="server" /></td>
   </tr>
  </table>
  
  <hr />
  
  Data fetched from database after the update:<br/>
  
  <asp:DataGrid id="dgUpd" runat="server"/>
  
 </body>
</html>
Synchronize Database operation : Synchronize « ADO.net Database « ASP.Net

Related examples in the same category
