Comments by RevathySanthanam (Top 30 by date)

//Continuation to above comment-part2


{
if (!reader.Read()) continue;
for (int n = 0; n < reader.FieldCount; n++)
{
cmd2.Parameters.AddWithValue(String.Format("val{0}", ++i),
reader.IsDBNull(n) ? DBNull.Value : reader.GetValue(n));
if (n == 2)
time = DateTime.Parse(reader.GetValue(n).ToString());

}
}
// Insert row
cmd2.ExecuteNonQuery();
}


}
catch (MySqlException ex)
{
MessageBox.Show("Error: " + ex.ToString());

}


}


}

}

//Continuation to above comment


// Load target list's ids and timestamps
string stmt1 = "SELECT seriennummer, DATE_FORMAT(fdate, '%Y-%M-%D %H:%i:%s') FROM " + tbl1;
MySqlCommand cmd1 = new MySqlCommand(stmt1, conn1);
List<idtsentry> A = new List<idtsentry>();
using (MySqlDataReader reader = cmd1.ExecuteReader())
{

while (reader.Read())
{ // I assume the fields are set to NOT NULL
A.Add(new IdTsEntry()
{
fid = reader.IsDBNull(0) ? -1L : reader.GetInt64(0),
fdate = reader.IsDBNull(1) ? "" : reader.GetString(1)
});
}
}
// Load source list's ids and timestamps
string stmt2 = "SELECT fid, DATE_FORMAT(fdate, '%Y-%M-%D %H:%i:%s') FROM " + tbl1;
MySqlCommand cmd2 = new MySqlCommand(stmt2, conn2);

List<idtsentry> B = new List<idtsentry>();
using (MySqlDataReader reader = cmd2.ExecuteReader())
{
while (reader.Read())
{ // I assume the fields are set to NOT NULL
B.Add(new IdTsEntry()
{
fid = reader.GetInt64(0),
fdate = reader.GetString(1)
});
}

}



// Filter lists
List<int64> List1 = new List<int64>();
List<int64> List2 = new List<int64>();
foreach (IdTsEntry b in B)
{
var a = A.FirstOrDefault(e => e.fid.Equals(b.fid));
if (a == null)
List2.Add(b.fid); // b.id not in A -> new row
else if (!a.fdate.Equals(b.fdate))
List1.Add(b.fid); // b.id in A but other timestamp -> altered row
}


foreach (Int64 id in List1)
{
// Read all entry values into parameters

DateTime time = DateTime.Now;



cmd1.CommandText = "SELECT variante,charge,fdate FROM " + tbl1 + " WHERE fid = " + id.ToString();

cmd2.Parameters.Clear();
int i = 0;
using (MySqlDataReader reader = cmd1.ExecuteReader())
{
if (!reader.Read()) continue;
for (int n = 0; n < reader.FieldCount; n++)
{
cmd2.Parameters.AddWithValue(String.Format("val{0}", ++i),
reader.IsDBNull(n) ? DBNull.Value : reader.GetValue(n));
if (n == 2)
time = DateTime.Parse(reader.GetValue(n).ToString());


}

}
// Update row


cmd2.CommandText = "UPDATE " + tbl1 + " SET variante = @val1,charge = @val2, fdate = '" + time.ToString("yyyy-MM-dd HH:mm:ss") + "' WHERE seriennummer = " + id.ToString();
cmd2.ExecuteNonQuery();
}


// Insert new rows

cmd2.CommandText = "INSERT INTO " + tbl1 + " (seriennummer,variante,charge,fdate) " +
"VALUES (@val1, @val2, @val3, @val4)";
foreach (Int64 id in List2)
{
// Read all values into parameters

DateTime time = DateTime.Now;

cmd1.CommandText = "SELECT fid,variante,charge,fdate FROM " + tbl1 + " WHERE fid = " + id.ToString();

cmd2.Parameters.Clear();
int i = 0;
using (MySqlDataReader reader = cmd1.ExecuteReader())

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using MySql.Data.MySqlClient;




namespace DBsync3
{
public partial class Form1 : Form
{
private MySqlConnection conn1 = null;
private MySqlConnection conn2 = null;
string cs1, cs2;

class IdTsEntry
{
public Int64 fid { get; set; }
public String fdate { get; set; }
}

public Form1()
{
InitializeComponent();
Init();
}

void create_backup()
{

string constring1 = "SERVER=localhost;" + "DATABASE=;" + "UID=root;" + "PASSWORD=;" + "Convert Zero Datetime=True;" +
"Allow Zero Datetime=True";
string file1 = "C:\\Users\\Santhanam\\Documents\\Visual Studio 2010\\Projects\\DBsync3\\backup.sql";
using (MySqlConnection conn = new MySqlConnection(constring1))
{
using (MySqlCommand cmd = new MySqlCommand())
{
using (MySqlBackup mb = new MySqlBackup(cmd))
{
// if (file1.Length == 0)
{
cmd.Connection = conn;
conn.Open();
//cmd.CommandText = "SET SESSION max_allowed_packet=1024*1024*1024;";
mb.ExportInfo.MaxSqlLength = 1024 * 1024; // 1MB
mb.ExportInfo.AddCreateDatabase = true;
mb.ExportInfo.ExportTableStructure = true;
mb.ExportInfo.ExportRows = true;
mb.ExportToFile(file1);
}
}
}
}
}

void load_from_backup()
{

string constring2 = "SERVER=localhost;" + "DATABASE=;" + "UID=root;" + "PASSWORD=;" + "Convert Zero Datetime=True;" +
"Allow Zero Datetime=True";

string file2 = "C:\\Users\\Santhanam\\Documents\\Visual Studio 2010\\Projects\\DBsync3\\backup.sql";
using (MySqlConnection conn = new MySqlConnection(constring2))
{
using (MySqlCommand cmd = new MySqlCommand())
{
using (MySqlBackup mb = new MySqlBackup(cmd))
{
cmd.Connection = conn;
conn.Open();
// cmd.CommandText = "SET SESSION max_allowed_packet=1024*1024*1024;";
mb.ImportInfo.TargetDatabase = "_create_from_dump_dummy";
mb.ImportInfo.DatabaseDefaultCharSet = "utf8";
mb.ImportFromFile(file2);
}
}
}
}

void Init()
{


try




{
create_backup();
load_from_backup();



cs1 = "SERVER=localhost;" + "DATABASE=;" + "UID=root;" + "PASSWORD=;" + "Convert Zero Datetime=True;" +
"Allow Zero Datetime=True"; //existing database
cs2 = "SERVER=localhost;" + "DATABASE=;" + "UID=root;" + "PASSWORD=;" + "Convert Zero Datetime=True;" +
"Allow Zero Datetime=True"; //dumped database

string servername1 = server1.ToString();
string uname1 = uid1.ToString();
string pass1 = pwd1.ToString();
string dbName1 = dbname1.ToString();

string tbl1 = table1.ToString();
string tbl2 = table2.ToString();



conn1 = new MySqlConnection(cs1);
conn1.Open();
conn2 = new MySqlConnection(cs2);
conn2.

Hallo @Bjørn,

I am trying to extend this solution towards two databases and now I'm stuck.!

I have a db1 and I'm creating a sqldump of that, and using that dump I create another database db2.
Then later, the db1 gets updated or modified and I take the dump of it again and using that second dump of db1, I should make the same changes as above (inserting/updating the recent rows) to the already existing db2 (which was created before in the first step from first dump).
All the operations are done locally on machine2 that has db2 from the sqldump of db1 from machine1 in a usb drive.! db1 and db2 have the same schema.

I was thinking of creating a dummy database from the new dump (from the 2nd updated db1) and using it to compare and update accordingly into db2. but is there a way to directly compare the dumpsql's tables and rows or what is a better way to do it.!?

The code till now that I've tried is posted as comment below.
Could you please help me out with some ideas??

Option 2 might be a good choice for us because we have many products and many systems for each product.
Thank you for all the enlightening options.

right now, it is copied by taking a sqldump from system1 through usb disk n copied into system2. is it possible to do it any other better way?

Thank you, I figured out the issue. The val3 (that takes fdate) was not being written as dateTime nor string as the value in database.
So i had to modify by adding some extra codes to convert to DateTime and then give it into specific format.
then it works.!!
thanks alot for your help :)

Hallo after a long time, I was in vacation and couldn't check the code. So, i tried changing the select statements for insert and update to what you had suggested; but it doesn't seem to work either. still am getting 0s inserted/updated. :(
1st place: cmd1.CommandText = "SELECT variante,charge,fdate FROM " + tbl2 + " WHERE fid = " + id.ToString();
2nd place: cmd1.CommandText = "SELECT fid,variante,charge,fdate FROM " + tbl2 + " WHERE fid = " + id.ToString();

any ideas, plz?? @Bjørn

so, looks like I'm not done yet..! :( the dates are inserted/updated as zeros into db..! guess the string->Datetime conversion isn't working as I planned..! any other ways to insert into db as a 'Datetime' value?

@Bjørn, ur a life saver..! i figured out the issue with val4. I was not including id column in my select from table2. it works now. both insert and update..!!
man, you rock..!! :) i was breaking my head over this for the past week now and finally it's all gud, thanks to you..!

Phew!! after countless googling, 'Forums-Posting'; thank you B...!! :D
Now i can sleep peacefully during the weekend..! :P
A very nice weekend to you too..!! :D

hey, that handling Null values worked. But the excution stops with a Fatal Error during the Insertion of new rows, saying: &val4 needs to be defined. here:

cmd2.CommandText = "INSERT INTO " + tbl1 + " (seriennummer,variante,charge,fdate) " +
"VALUES (@val1, @val2, @val3, @val4)";

could you help me with figuring out what this could mean? i even checked the spacing issue but it seems fine to me (i even tested if that insert would work in my db too--seems to be working..!).
sorry fr troubling with these many issues, but any ideas plz? :(

yes, that is totally true. i had datatype issues before, also causing the ID issue from before. later all are fixed but this sudden weird thing started to happen. Im using Int64 for id and String for timestamp. no idea what is going wrong, i cant figure out from debugging either... please tell me you got some ideas.. :(

can i not have values that zeros too, as part of Non-Null? :-o if i change the zero value to some random value, seems to take it. but stops when there are zeros again.
but it did not give me an issue for the values that are zeros before when i executed the code (before and after the ID assignment issue), why now? :( suddenly!! could you maybe explain this bizarre situation..! ?

wow, i did not notice the spacing issue :) Thank you.
So i fixed the errors i made and the ID assignment issue is gone.!! but i have a new problem now.!
it keeps throwing me Nulldata Exception in the first while( here:

// I assume the fields are set to NOT NULL
A.Add(new IdTsEntry()
...)

but there are no Null values in my db, i even set the columns to NotNull property. Not sure why.. :( any help plzz??

Yeah, i gathered that something else is the issue over there. but i have no clue as to how the id could be something that is not present in any of the tables. So how do i check from where that value is assigned? :( been stuck with it for long time!
and yes, that was a very nice explanation for 'String.format', so much better than Google. I understood that now. Thank you.
oh and yeah, DBDataReader is simply MySqlDataReader in MySql..
So, do you have any idea about the 'Id assignment issue' from that Select Statement..!! (the code is posted below and the exception is a MySql Syntax Error mentioned above) :( please!!

//Continuation

// Update altered rows
string ct1 = "SELECT variante,charge,DATE_FORMAT(fdate, '%Y-%M-%D %H:%i:%s') FROM" + tbl2 +"WHERE fid = {0}";
string ct2 = "UPDATE "+tbl1 +"SET variante = @val1,charge = @val2, fdate = @val7 WHERE seriennummer = {0}";
foreach (int id in List1)
{
// Read all entry values into parameters
cmd1.CommandText = String.Format(ct1, id);
cmd2.Parameters.Clear();
int i = 0;
using (MySqlDataReader reader = cmd1.ExecuteReader())
{
if (!reader.Read()) continue;
for (int n = 0; n < reader.FieldCount; n++)
{
cmd2.Parameters.AddWithValue(String.Format("val{0}", ++i),
reader.IsDBNull(n) ? DBNull.Value : reader.GetValue(n));
}
}
// Update row
cmd2.CommandText = String.Format(ct2, id);
cmd2.ExecuteNonQuery();
}


// Insert new rows
ct1 = "SELECT fid, variante,charge,DATE_FORMAT(fdate, '%Y-%M-%D %H:%i:%s') FROM" + tbl2 + "WHERE fid = {0}";
ct2 = "INSERT INTO " + tbl1 + "(seriennummer, variante,charge,fdate) " +
"VALUES (@val1, @val2, @val3, @val4)";
cmd2.CommandText = ct2;
foreach (int id in List2)
{
// Read all values into parameters
cmd1.CommandText = String.Format(ct1, id);
cmd2.Parameters.Clear();
int i = 0;
using (MySqlDataReader reader = cmd1.ExecuteReader())
{
if (!reader.Read()) continue;
for (int n = 0; n < reader.FieldCount; n++)
{
cmd2.Parameters.AddWithValue(String.Format("val{0}", ++i),
reader.IsDBNull(n) ? DBNull.Value : reader.GetValue(n));
}
}
// Insert row
cmd2.ExecuteNonQuery();
}





}




catch (MySqlException ex)
{
MessageBox.Show("Error: " + ex.ToString());

}

}
public Form1()
{
InitializeComponent();

Init();


}

}

}

Wow, That's really an amazing solution. Thanks alot Bjørn.!! So far I have been working with both databases in same server only.
and I have been trying to make the code you gave work and so far so good until now.. I am unable to understand what this line will do: <pre>
"// Read all entry values into parameters
cmd1.CommandText = String.Format(ct1, id);" </pre>
because I have an error thrown when I try to execute the Command that's following that line..!
The Syntax error points to this line (at id = {0} exactly):

// Update altered rows
string ct1 = "SELECT col1, col2, ... col_last FROM table2 WHERE id = {0}";

I'm not sure what the issue is because the value for id from {0} is something that is not present in any of the tables. So I don't know how and from where that value is assigned.
I will post the code I re-framed according to my project from yours, for better understanding. Could you please help me out?

great, it is a nice piece of information. and it works. Thank you. But i was wondering, is it possible to do the Insert dynamically, as in not plugging in all the column names manually in Insert Statement because I have a huge number of columns in my table. it really would be helpful..! googling for a solution wasn't of much help.! Also, any idea on how I can build the above code in C#.? I'm supposed to provide a C# solution to my problem.!!

Thank you for the interesting solution, Ben.
but I don't want to merge based on just the Primary key because when there are many duplicates in table1, I need only the Most Recently Updated Row (based on the date column corresponding to the Primary Key) inserted or updated into Table2. So how can I achieve that here?

i have to update the row in table2 if it already exists, else insert new row. i check for recent record based on the date column, time_date.
both the tables have same schema.

thank you Suvendu but I am supposed to insert only the recent records for each id into table2. so how can i make use of a flag? and i check for recent record based on the date column.
and yes, i was using the way i mentioned before about finding the recent record id and inserting it into table2. but i do not know how can i do it for each and every id in table1. i have to insert all the recent records for all ids in table1 into table2...!! any ideas???

yes, it is MySQL.!

but i am unable to do an insert statement with update in MySQL. how can i do that? i tried dis to get the most recently updated id,
SELECT distinct id
FROM `table1` order by `date` desc limit 1;
now how can i do an insert on table2 (has same schema as table1) with this id and the respective other columns?
table2 is the copy of unique data from table1.
any inputs?

Thank you Aarti.
yes, i have ID and Timestamp columns. and yeah, that is how i planned to do that part too.
but the thing is, how do i check for new updates in Db1 and perform the respective changes to Db2 in C#?
any ideas?