[Sample of Apr 21st] Compare and merge data from different database
 |
 |
|
 |
 |
|
Sample Downloads
C# version: https://code.msdn.microsoft.com/CompareAndMergeData-56979e7b
VB version: https://code.msdn.microsoft.com/CompareAndMergeData-327608ef
Today’s code sample demonstrates how to compare and merge data which from different datasources. In this sample, we store some data in xml file and SQL Server database respectively. We need to compare the data from different datasources and display the columns with one GridView Control. If the records ID are equal we need to set the status column as “ok”. Otherwise the status column should be set as “null”. When we display the columns from different datasources we may need to merge the datasets in order to bind to GridView Control.
You can find more code samples that demonstrate the most typical programming scenarios by using Microsoft All-In-One Code Framework Sample Browser or Sample Browser Visual Studio extension. They give you the flexibility to search samples, download samples on demand, manage the downloaded samples in a centralized place, and automatically be notified about sample updates. If it is the first time that you hear about Microsoft All-In-One Code Framework, please watch the introduction video on Microsoft Showcase, or read the introduction on our homepage https://1code.codeplex.com/.
Introduction
The project illustrates how to compare and merge data which from different datasources. In this sample, we store some data in xml file and SQL Server database respectively. We need to compare the data from different datasources and display the columns with one GridView Control. If the records ID are equal we need to set the status column as ��ok��. Otherwise the status column should be set as ��null��. When we display the columns from different datasources we may need to merge the datasets in order to bind to GridView Control.
Running the Sample
Please follow these demonstration steps below.
Step 1: Open the VBASPNETCompareAndMergeData.sln.
Step 2: Right click Default.aspx and choose "View in Browser".
Using the Code
Step 1. Create a VB ASP.NET Web Application in Visual Studio 2010 and name it as ��VBASPNETCompareAndMergeData��.
Step 2. Create a test database and the xml file.
The database name is ��books�� and the definition of the table ��bookdata�� as shown below:
ISBN(nvarchar(50)), BookName(nvarchar(50)), Price(nvarchar(50)), AuthorName(nvarchar(50)), Publisher(nvarchar(50))
<?xml version="1.0" standalone="yes"?>
<BookWorld>
<BookData>
<ISBN>F2LGFGCEUC</ISBN>
<BookName>Mysteries of World</BookName>
<Price>20$</Price>
<AuthorName>Alfred Newton</AuthorName>
<Publisher>McGraw Bills</Publisher>
</BookData>
<BookData>
<ISBN>R2MGFGASDF</ISBN>
<BookName>Bank of modern world</BookName>
<Price>20$</Price>
<AuthorName>Rajib Banerjee</AuthorName>
<Publisher>Kaplana Inc</Publisher>
</BookData>
<BookData>
<ISBN>H2LGFGBNFG</ISBN>
<BookName>Gulf and Gas</BookName>
<Price>40$</Price>
<AuthorName>Rahul Banerjee</AuthorName>
<Publisher>Oxford Newland</Publisher>
</BookData>
<BookData>
<ISBN>K2LGFGUIFR</ISBN>
<BookName>I am just tired. not retired</BookName>
<Price>35$</Price>
<AuthorName>Rajib Banerjee</AuthorName>
<Publisher>New Press</Publisher>
</BookData>
<BookData>
<ISBN>O2LGFGOPTR</ISBN>
<BookName>Bomb of Future</BookName>
<Price>39$</Price>
<AuthorName>James Kirk</AuthorName>
<Publisher>Alter Nation</Publisher>
</BookData>
<BookData>
<ISBN>A2LGFGWERT</ISBN>
<BookName>Climb the Tomb</BookName>
<Price>45$</Price>
<AuthorName>Abardeen</AuthorName>
<Publisher>New Castle</Publisher>
</BookData>
<BookData>
<ISBN>K2LGFGNJYI</ISBN>
<BookName>If you can Dare</BookName>
<Price>40$</Price>
<AuthorName>Sydmond Rock</AuthorName>
<Publisher>Daily Needs</Publisher>
</BookData>
</BookWorld>
Step 3. Add a GridView Control to the Default page.
<asp:GridView ID="gdvData" runat="server" >
</asp:GridView>
Step 4. Get data from database and then fill it in dataset. The code as shown below:
' Sql DataSource
Dim connection As New SqlConnection(connString)
Dim queryString As String = "select * from bookData"
Dim adapter As New SqlDataAdapter()
adapter.SelectCommand = New SqlCommand(queryString, connection)
' Open connection
connection.Open()
' Fill Sql data in DataSet.
Dim sqlData As New DataSet()
adapter.FillSchema(sqlData, SchemaType.Source, "BookData")
adapter.Fill(sqlData, "BookData")
' Close connection
connection.Close()
Step 5. Get data from xml file and then fill it in dataset .
' Fill xml data in DataSet.
Dim xmlData As New DataSet()
xmlData.ReadXml(filePath)
xmlData.AcceptChanges()
Step 6. Declare DataTable, and add the desired status column. The code as shown below:
' A column is used to store compare state.
xmlData.Tables(0).Columns.Add("CompareState")
' Declare a table to hold Xml data.
Dim dtXml As DataTable = xmlData.Tables(0)
' Declare a table to hold Sql data.
Dim dtSql As DataTable = sqlData.Tables(0)
' The table is used to store Merged data.
Dim dtXmlTemp As DataTable = dtXml.Clone()
' Copy dtXml data to dtXmlTemp.
For Each row As DataRow In dtXml.Rows
dtXmlTemp.ImportRow(row)
Next
Step 7. Compare and merge operations. The code as shown below:
' Loop all rows of the sql table and xml table.
' If the ISBN in the dtXml is equal to the ISBN in dtSql we can merge the two records and
' set CompareState as "OK", Otherwise we can add the null value to the records in dtXml.
For Each dr1 As DataRow In dtXml.Rows
For Each dr2 As DataRow In dtSql.Rows
If dr1(0).ToString().Equals(dr2(0).ToString()) Then
' Compare the ISBN
' Get the index of current row, then update the CompareState in the copy of dtXml(dtXmlTemp).
Dim intIndex As Integer = dtXml.Rows.IndexOf(dr1)
' Set CompareState value
dtXmlTemp.Rows(intIndex)(5) = "OK"
Else
' Add record to dtXmlTemp and add a "null" flag.
Dim drNew As DataRow = dtXmlTemp.NewRow()
drNew("ISBN") = dr2("ISBN")
drNew("CompareState") = "null"
' Add a new row if the table does not has duplicate rows.
If IsNotExist(drNew, dtXmlTemp) Then
dtXmlTemp.Rows.Add(drNew)
End If
End If
Next
Next
Step 8. Bind data to GridView. The code as shown below:
'Bind datatable to GridView
gdvData.DataSource = dtXmlTemp
gdvData.DataBind()