ASP.Net/ADO.net Database/Synchronize
Synchronize Database operation
<source lang="csharp">
<%@ 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=nfexuser;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>
Original Data | Data with new Row | Data with edited Row | Data with deleted Row |
<asp:DataGrid id="dgNameList1" runat="server" /> | <asp:DataGrid id="dgNameList2" runat="server" /> | <asp:DataGrid id="dgNameList3" runat="server" /> | <asp:DataGrid id="dgNameList4" runat="server" /> |
Data fetched from database after the update:
<asp:DataGrid id="dgUpd" runat="server"/> </body>
</html>
</source>