none
C# 2005 - DataRelation problem with 32+ columns - Comparing two different databases RRS feed

  • Question

  • Hello-

    I am migrating from a DBASE III datasource to a mySql Datasource, and during the transition I will be running simultaneous updates to both sources. I need a way to make sure that they both remain in sync with each other, and I'm trying to write an independent C# application that will compare all data in both sources when an update is made in a particular database/table.

    I found and have adapted the code below, and I've run into a couple of problems. If I select all the columns, I get the error that the DataRelation cannot handle more than 32 columns. When I tried to loop through and change the query each time to only select 32 columns at a time, I can't get the previous relation to clear and I end up with the same error.

    The code is shown below, with just the field names removed from the query. Any help is appreciated!

    John

      1. using System; 
      2. using System.Collections.Generic; 
      3. using System.ComponentModel; 
      4. using System.Data; 
      5. using System.Data.Odbc; 
      6. using System.Drawing; 
      7. using System.Text; 
      8. using System.Windows.Forms; 
      9.  
     10. namespace dbtesting 
     11. { 
     12.   public partial class Form1 : Form 
     13.   { 
     14.     public Form1() 
     15.     { 
     16.       InitializeComponent(); 
     17.     } 
     18.  
     19.     private void button1_Click(object sender, EventArgs e) 
     20.     { 
     21.       OdbcConnection dbfFileConnection; 
     22.       OdbcDataAdapter dbfFirstAdapter; 
     23.       OdbcDataAdapter dbfSecondAdapter; 
     24.  
     25.       DataTable dbfFirst = new DataTable(); 
     26.       DataTable dbfSecond = new DataTable(); 
     27.        
     28.       int dbfRecordCount = 0; 
     29.       string dbfSelectFirst = ""; 
     30.       string dbfSelectSecond = ""; 
     31.       textBox4.Text = DateTime.Now.ToString("hh:mm:ss tt"); 
     32.  
     33.       dbfFileConnection = new OdbcConnection(@"Driver={Microsoft dBASE Driver (*.dbf)};DriverID=277;Dbq=c:\dbtest"); 
     34.  
     35.       for (int m = 1; m < 5; m++) 
     36.       { 
     37.         DataTable dt = new DataTable();  
     38.          
     39.         if (m == 1) 
     40.         { 
     41.           dbfSelectFirst = "SELECT <32 fields> FROM testthre.dbf;"; 
     42.           dbfSelectSecond = "SELECT <same 32 fields> FROM testfour.dbf;"; 
     43.         } 
     44.         else if (m == 2) 
     45.         { 
     46.           dbfSelectFirst = "SELECT <next 32 fields> FROM testthre.dbf;"; 
     47.           dbfSelectSecond = "SELECT <same 32 fields> FROM testfour.dbf;"; 
     48.         } 
     49.         else if (m == 3) 
     50.         { 
     51.           dbfSelectFirst = "SELECT <next 32 fields> FROM testthre.dbf;"; 
     52.           dbfSelectSecond = "SELECT <same 32 fields> FROM testfour.dbf;"; 
     53.         } 
     54.         else if (m == 4) 
     55.         { 
     56.           dbfSelectFirst = "SELECT <last few fields> FROM testthre.dbf;"; 
     57.           dbfSelectSecond = "SELECT <same few fields> FROM testfour.dbf;"; 
     58.         } 
     59.  
     60.  
     61.         dbfFirstAdapter = new OdbcDataAdapter(dbfSelectFirst, dbfFileConnection); 
     62.         dbfRecordCount = dbfFirstAdapter.Fill(dbfFirst); 
     63.         textBox1.Text = dbfRecordCount.ToString(); 
     64.  
     65.         dbfSecondAdapter = new OdbcDataAdapter(dbfSelectSecond, dbfFileConnection); 
     66.         dbfRecordCount = dbfSecondAdapter.Fill(dbfSecond); 
     67.         textBox2.Text = dbfRecordCount.ToString(); 
     68.  
     69.         dt = getDifferentRecords(dbfFirst, dbfSecond); 
     70.  
     71.         if (dt.Rows.Count == 0) 
     72.           textBox6.Text = textBox6.Text + "Iteration # " + m.ToString() + " is Equal " + DateTime.Now.ToString("hh:mm:ss tt") + "\n"; 
     73.         else 
     74.           textBox6.Text = textBox6.Text + "Iteration # " + m.ToString() + " is NOT Equal " + DateTime.Now.ToString("hh:mm:ss tt") + "\n"; 
     75.         dt.Clear(); 
     76.         dbfFirst.Clear(); 
     77.         dbfSecond.Clear(); 
     78.         Application.DoEvents(); 
     79.       } 
     80.  
     81.       textBox5.Text = DateTime.Now.ToString("hh:mm:ss tt"); 
     82.  
     83.     } 
     84. 
     85.     #region Compare two DataTables and return a DataTable with DifferentRecords 
     86.     /// <summary>  
     87.     /// Compare two DataTables and return a DataTable with DifferentRecords  
     88.     /// </summary>  
     89.     /// <param name="FirstDataTable">FirstDataTable</param>  
     90.     /// <param name="SecondDataTable">SecondDataTable</param>  
     91.     /// <returns>DifferentRecords</returns>  
     92.     public DataTable getDifferentRecords(DataTable FirstDataTable, DataTable SecondDataTable) 
     93.     { 
     94.       //Create Empty Table  
     95.       
     96.       DataTable ResultDataTable = new DataTable("ResultDataTable"); 
     97.       ResultDataTable.Clear(); 
     98.       //use a Dataset to make use of a DataRelation object  
     99.       using (DataSet ds = new DataSet()) 
     100.       { 
     101.         //Add tables  
     102.         ds.Tables.AddRange(new DataTable[] { FirstDataTable.Copy(), SecondDataTable.Copy() }); 
     103.  
     104.         //Get Columns for DataRelation  
     105.         DataColumn[] firstColumns = new DataColumn[ds.Tables[0].Columns.Count]; 
     106.         for (int i = 0; i < firstColumns.Length; i++) 
     107.         { 
     108.           firstColumns[i] = ds.Tables[0].Columns[i]; 
     109.         } 
     110.  
     111.         DataColumn[] secondColumns = new DataColumn[ds.Tables[1].Columns.Count]; 
     112.         for (int i = 0; i < secondColumns.Length; i++) 
     113.         { 
     114.           secondColumns[i] = ds.Tables[1].Columns[i]; 
     115.         } 
     116.  
     117.         //Create DataRelation  
     118.         DataRelation r1 = new DataRelation(string.Empty, firstColumns, secondColumns, false); 
     119.         ds.Relations.Add(r1); 
     120.  
     121.         DataRelation r2 = new DataRelation(string.Empty, secondColumns, firstColumns, false); 
     122.         ds.Relations.Add(r2); 
     123.  
     124.         //Create columns for return table  
     125.         for (int i = 0; i < FirstDataTable.Columns.Count; i++) 
     126.         { 
     127.           ResultDataTable.Columns.Add(FirstDataTable.Columns[i].ColumnName, FirstDataTable.Columns[i].DataType); 
     128.         } 
     129.  
     130.         //If FirstDataTable Row not in SecondDataTable, Add to ResultDataTable.  
     131.         ResultDataTable.BeginLoadData(); 
     132.         foreach (DataRow parentrow in ds.Tables[0].Rows) 
     133.         { 
     134.           DataRow[] childrows = parentrow.GetChildRows(r1); 
     135.           if (childrows == null || childrows.Length == 0) 
     136.             ResultDataTable.LoadDataRow(parentrow.ItemArray, true); 
     137.         } 
     138.  
     139.         //If SecondDataTable Row not in FirstDataTable, Add to ResultDataTable.  
     140.         foreach (DataRow parentrow in ds.Tables[1].Rows) 
     141.         { 
     142.           DataRow[] childrows = parentrow.GetChildRows(r2); 
     143.           if (childrows == null || childrows.Length == 0) 
     144.             ResultDataTable.LoadDataRow(parentrow.ItemArray, true); 
     145.         } 
     146.         ResultDataTable.EndLoadData(); 
     147.         ds.Relations.Remove(r1); 
     148.         ds.Relations.Remove(r2); 
     149.         ds.Clear(); 
     150.         ds.Dispose(); 
     151.          
     152.       } 
     153.  
     154.       FirstDataTable.Clear(); 
     155.       SecondDataTable.Clear(); 
     156.        
     157.       return ResultDataTable; 
     158.     } 
     159. 
     160.     #endregion  
     161.  
     162.   } 
     163. } 
    

    • Moved by Mike Dos Zhang Sunday, November 28, 2010 12:28 PM (From:Visual C# Language)
    • Moved by Nina H Wednesday, December 29, 2010 8:50 PM Move to Data Platform Development forum per request. (From:SyncFx - Microsoft Sync Framework Developer Discussions)
    Tuesday, November 23, 2010 8:24 PM

All replies

  • I'm not that familiar with DBaseIII or MySql, but I am guessing that using ODBC is the limiting factor.  A thing I would try would be to export the data from each DB to text files and then use OLEDB to compare the textfiles.  C# can read textfiles fairly easily.  (assuming you have enough memory) you can read each textfile into a .Net in-memory datatable and use Linq to compare the contents.
    Tuesday, November 23, 2010 10:00 PM
  • Hi Fasterx3,

     

    Welcome to MSDN Forums!

     

    I think may be the following forum would be the suitable forum for your request.

    http://social.msdn.microsoft.com/Forums/en-US/syncdevdiscussions/threads

    So I moved this thread to this forum. Hope you can get what you want to know form this forum.

     

    Have a nice weekend!

    Mike

    *****************************************************

    [All-In-One Code Framework]

    Sample world! You will get more from this world!

    Welcome to the new world!

    Sunday, November 28, 2010 12:27 PM
  • Hi,

    I encounter the same need, that is comparing on more than 32 columns. Did you find a workaround please?

    Friday, December 24, 2010 4:32 PM
  • This question doesn't seem to be related to Sync Framework. Data programmability seems to be a better area.
    Monday, December 27, 2010 7:24 PM
  • Indeed, it doesn't seem to be related to Sync Framework, but has this thread been put in the Data programmability forum? If so, I'll be happy to reply over there if you give me the direct link to this thread over there. As the subject that I am concerned with has been raised here, I answered here... that's why

    Furthermore, I subscribed to get an email to any reply on the forum and I didn't get one on your answer, is there a problem?

    Tuesday, December 28, 2010 4:19 PM
  • I will find some help to try to move this thread. Thanks.
    Tuesday, December 28, 2010 7:09 PM
  • I have moved your thread to Data Platform Development forum so that you can get proper help.

    Thanks,
    Nina


    This posting is provided "AS IS" with no warranties, and confers no rights.
    Wednesday, December 29, 2010 8:51 PM
  • Is this ever going to get resolved? I would also like to know the solution or at least a workaround. If you have more than 100 columns can you search in blocks of 32 and then use each of those to build the result table? Would you have to specify a key column and include it in each block? why does dataRelation only work for 32 columns anyway......
    Friday, April 1, 2011 5:42 PM