merge two datasets

merge two datasets

• seano288

  Hi!

  If both databases have same schema then dreate two datasets, one for each database. Merge remote dataset with local and accept changes. If at all there are changes your local database will be updated. Is that what you want.

  
  
  

• Carl Mailloux

  Sohail,

  As a beginner to Visual Basic, I haven't found any text resource for beginners that really holds your hand and explains step by step what's necessary to do even the basic things with database access, let alone something more complicated like this. I learned all of my coding skills from ColdFusion and when it comes to database access, its really such a peice of cake that its no challenge at all to learn what needs to be done. You just give the query code and the rest is done behind the scenes for you. (If someone knows of any must-read tutorials that are more plain English, please direct me to them. So far its not clicking for me.)

  Now I am trying to do something more complicated then just querying a database, so I am really grateful for your example. I feel there aren't alot of good examples to follow for things like this. The examples I've found are either in an old version of VB that doesn't apply, or aren't commented and explained in a way for a beginner to understand.

  Of all the code and forums I've been through, your example looks like its going to be the most helpful, if I can work out how to translate it from C# to Visual Basic .NET. I still am not sure what are the reasons for each line of code, but I want to say that I am really glad to have this to go by.

  So what I am trying to say is thanks for taking the time to post this!

  EC

  
  

• Ouster

  Sohail,
  Your code looks good, but there is an emoticon in one line that confuses me. In the AddDifferentRows sub, you have a foreach loop that theoretically should fill the datarow with data for each column...what is the actual syntax for that line
  My browser shows a lightbulb symbol between dv and [dc.ColumnName]...
  
  

• SagayaBabu

  hello sohail,

  thanks for your replies.

  but the links you referred to me are not so helpful. i am doing this task for the first time and therefore it is very difficult to understand it.

  Can you please do more for me.

  thanks in advance.

  
  
  

• KishoreNM

  Hi ECList!

  Thanks a lot for appreciating my post, I wish I had VB.Net with me I would have translated it for you even though I'm not very much familiar with it. Never the less I can give you some examples so you could translate it by yourself.

  C#: private void btnMerge_Click(object sender, System.EventArgs e)

  {

  }

  VB: private Sub btnMerge_Click(sender AS object , e AS System.EventArgs)

  End Sub

  C#: private DataSet GetChanges(DataSet remoteDS,DataSet localDS)

  {}

  VB: private Function GetChanges(remoteDS AS DataSet ,localDS AS DataSet) AS DataSet

  End Function

  C#: localDT.Columns[0].AutoIncrementSeed = 1;

  VB: localDT.Columns(0).AutoIncrementSeed = 1;

  Declaring variable in C#

  Type Variable Name;. e.g int x;

  Declaring variable in VB

  Dim Variable Name AS Type. e.g Dim x AS Interger

  C# Loops:

  for(int i = 0; i < 5; i++){}

  foreach(DataRow dr in Table1.Rows){}

  VB#:

  int i = 0 ;

  For i = 0 To 5

  Next i

  Dim dr AS [DataRow]

  For Each dr In Table1.Rows

  Next dr

  =======================================================================

  I hope this will help you in translating C# to VB and thanks again for appreciating my efforts.

  cheers.

  
  
  

• Knallform

  Hi!
  
  I've not been loging to forum it lately therefore I couldn't reply on time. Anyways since outer loop is for with "i" as counter variable then it must be "dv" with "i" in square brackets. e.g dv , it should show the bulb again. dv[ i ].
  
  cheers
  
  Sohail.
  
  
  

• Mohamed Amir

  check the link below

  http://forums.microsoft.com/MSDN/ShowPost.aspx PostID=220624&SiteID=1

  
  
  

• Tom Leeson

  hello,sohail.

  i have already done merging local and remote datasets but nothing happens.

  Please write to me the code if you think it can work. i might have done something wrong merging both datasets.

  please write to me soon.

  thanks to all

  
  
  

• rookie31

  This may not be the best but it works fine. I've three girds on form namely

  dgRemote,dgLocal,dgMerged and one button called btnMerge. Name these control and copy the following code and it merge remote data with local data. Read comments as well.

  private void btnMerge_Click(object sender, System.EventArgs e)

  {

  mergerDS = localDS.Copy();

  mergerDS.AcceptChanges();

  mergerDS.Merge(GetChanges(remoteDS,localDS));

  mergerDS.AcceptChanges();

  dgMerged.DataSource = mergerDS;

  dgMerged.DataMember = "Person";

  }

  DataSet localDS = new DataSet("Local DataSet");

  DataSet remoteDS = new DataSet("Remote DataSet");

  DataSet mergerDS = new DataSet("Merged DataSet");

  DataTable remoteDT = new DataTable("Person");

  DataTable localDT = new DataTable("Person");

  private void Form1_Load(object sender, System.EventArgs e)

  {

  remoteDT.Columns.Add("ID",typeof(int));

  remoteDT.Columns.Add("Name",typeof(string));

  remoteDT.Columns.Add("Job",typeof(string));

  remoteDT.Columns[0].AutoIncrement = true;

  remoteDT.Columns[0].AutoIncrementSeed = 1;

  remoteDT.Columns[0].AutoIncrementStep = 1;

  localDT.Columns.Add("ID",typeof(int));

  localDT.Columns.Add("Name",typeof(string));

  localDT.Columns.Add("Job",typeof(string));

  localDT.Columns[0].AutoIncrement = true;

  localDT.Columns[0].AutoIncrementSeed = 1;

  localDT.Columns[0].AutoIncrementStep = 1;

  remoteDS.Tables.Add(remoteDT);

  localDS.Tables.Add(localDT);

  for(int i=0;i<5;i++)

  {

  DataRow dr = remoteDT.NewRow();

  dr["Name"] = "Sohail " + i;

  dr["Job"] = "Programmer " + i;

  remoteDT.Rows.Add(dr);

  }

  //remoteDT.AcceptChanges();

  for(int i=0;i<2;i++)

  {

  DataRow dr = localDT.NewRow();

  dr["Name"] = "Sohail " + i;

  dr["Job"] = "Programmer " + i;

  localDT.Rows.Add(dr);

  }

  localDT.AcceptChanges();

  dgRemote.DataSource = remoteDS;

  dgRemote.DataMember = "Person";

  dgLocal.DataSource = localDS;

  dgLocal.DataMember = "Person";

  }

  private DataSet GetChanges(DataSet remoteDS,DataSet localDS)

  {

  //assuming that remote will have the latest data and both databases have same structure

  DataSet diffDS = new DataSet("Different");

  diffDS = remoteDS.Clone();

  int iSearchTableCount = 0;

  string[] sSearchCriteria = new string[localDS.Tables.Count];

  //Get primary key from all rows in all tables

  //I'm assuming first column to be primary key.

  foreach(DataTable dt in localDS.Tables)

  {

  foreach(DataRow dr in dt.Rows)

  {

  sSearchCriteria[iSearchTableCount] += dr[0] + ",";

  }

  //removing last comma

  sSearchCriteria[iSearchTableCount] = string.Format("{0} NOT IN ({1})",

  dt.Columns[0].ColumnName,sSearchCriteria[iSearchTableCount].Substring(0,sSearchCriteria[iSearchTableCount].Length -1));

  AddDifferentRows(diffDS,sSearchCriteria[iSearchTableCount],dt.TableName);

  iSearchTableCount++;

  }

  return diffDS;

  }

  private void AddDifferentRows(DataSet differentDS,string Criteria,string Table)

  {

  DataView dv = new DataView(remoteDS.Tables[Table]);

  DataRow dr = null;

  dv.RowFilter = Criteria;

  for(int i=0;i<dv.Count;i++)

  {

  dr = differentDS.Tables[Table].NewRow();

  foreach(DataColumn dc in differentDS.Tables[Table].Columns)

  {

  dr[dc.ColumnName] = dv[dc.ColumnName];

  }

  differentDS.Tables[Table].Rows.Add(dr);

  }

  }

  check the closing brackets

  cheers

  
  
  
• merge two datasets