Notes: This guide is the initial methods and design used to build the DLL version of 1.1.
The basic ideas
- Part 1 : Construct the database CREATE statements
- Part 2 : Construct the Tables CREATE statements
- Part 3 : Construct the INSERT statement for all data.
- Part 4 : Write All SQL statements into textfile.
- Part 5 : Restore.
Part 1 : Construct the database CREATE statement
For example, we have a database called "people".
We use this statement to display the CREATE of this database:
SHOW CREATE DATABASE `people`;
In C#,
string sql = "SHOW CREATE DATABASE `people`;";
MySqlConnection conn = new MySqlConnection("server=localhost;user=root;password=qwerty;database=people;");
DataTable dt = new DataTable();
MySqlDataAdapter da = new MySqlDataAdapter(sql, conn);
da.Fill(dt);
string output = dt.Rows[0][0] + "";
MessageBox.Show(output);
Below is the result of the above commands. This will be the CREATE
sql statement for creating a database exactly the same of the current database.
CREATE DATABASE `people` /*!40100 DEFAULT CHARACTER SET utf8 */
However, CREATE DATABASE will cause error if there is already existed a database called `people`. We need to replace
CREATE DATABASE with CREATE DATABASE IF NOT EXISTS.
string sql = "SHOW CREATE TABLE `people`;";
MySqlConnection conn = new MySqlConnection("server=localhost;user=root;password=qwerty;database=people;");
DataTable dt = new DataTable();
MySqlDataAdapter da = new MySqlDataAdapter(sql, conn);
da.Fill(dt);
string output = (dt.Rows[0][0] + "").Replace("CREATE DATABASE", "CREATE DATABASE IF NOT EXISTS ") + ";");;
MessageBox.Show(output);
This is the result:
CREATE DATABASE IF NOT EXISTS `people` /*!40100 DEFAULT CHARACTER SET utf8 */;
For some situation, you might need to add a DROP DATABASE sql statement before a
CREATE. Add USE to state that all command will take effect within database of `people`.
DROP DATABASE IF EXISTS `people`;
CREATE DATABASE IF NOT EXISTS `people` /*!40100 DEFAULT CHARACTER SET utf8 */;
USE `people`;
Now, we have collected 3 SQL commands.
Part 2 : Construct the Tables CREATE Commands
First, collects all tables' name in database `people`.
string sql = "SHOW TABLES;";
MySqlConnection conn = new MySqlConnection("server=localhost;user=root;password=qwerty;database=people;");
DataTable dt = new DataTable();
MySqlDataAdapter da = new MySqlDataAdapter(sql, conn);
da.Fill(dt);
foreach (DataRow dr in dt.Rows)
{
MessageBox.Show(dr[0] + "");
}
Now, DataTable dt contains a list of current available table in database.
Collecting CREATE statement of tables:
foreach (DataRow dr in dt.Rows)
{
string sql = "SHOW CREATE TABLE `" + dr[0] + "`;";
MySqlDataAdapter da = new MySqlDataAdapter(sql, conn);
DataTable dtNames = new DataTable();
da.Fill(dtNames);
MessageBox.Show(dtNames.Rows[0][1] + "");
}
Result (This is just an example, the result will vary on different structure of table):
CREATE TABLE `people`.`personal` (
`id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
`name` VARCHAR(45) COMMENT 'Member\'s Name',
`datejoin` DATETIME,
`status` SMALLINT UNSIGNED DEFAULT 1,
PRIMARY KEY (`id`)
) ENGINE = InnoDB;
Please note that, this CREATE statement is splited into several rows. If we browse the statement in runtime debug mode, we'll find that the
CREATE statement is actually in one line, but there is a special escape character "\n" appear in between. "\n" means "New Line" (split the current point into new line). It'll look like this:
CREATE TABLE `people`.`personal` (\n `id` INTEGER UNSIGNED...........
This "\n" need to be replaced to avoid the whole statement to be splited. Besides,
CREATE TABLE sql statemnt need to be replaced as well with
CREATE TABLE IF NOT EXISTS.
string sql = "SHOW TABLES;";
MySqlConnection conn = new MySqlConnection("server=localhost;user=root;password=qwerty;database=people;");
DataTable dt = new DataTable();
MySqlDataAdapter da = new MySqlDataAdapter(sql, conn);
da.Fill(dt);
foreach (DataRow dr in dt.Rows)
{
sql = "SHOW CREATE TABLE `" + dr[0] + "`;";
MySqlDataAdapter da = new MySqlDataAdapter(sql, conn);
DataTable dtNames = new DataTable();
da.Fill(dtNames);
string output = dtNames.Rows[0][1] + "";
output = output.Replace("\n", string.Empty);
output = output.Replace("CREATE TABLE", "CREATE TABLE IF NOT EXISTS") + ";");
MessageBox.Show(output);
}
Result:
CREATE TABLE IF NOT EXISTS `people`.`personal` ( `id` INTEGER UNSIGNED...........
In some situation, you might need to add DROP TABLE before
CREATE TABLE.
DROP TABLE IF EXISTS `people`.`personal`;
CREATE TABLE IF NOT EXISTS `people`.`personal` ( `id` INTEGER........
Till here, we have collected the CREATE statement for creating Tables.
Part 3 : Construct the INSERT statement for all data.
In C#:
string sql = "SELECT * FROM `personal`;"
string mycon = "server=localhost;user=root;password=qwerty;database=people;";
MySqlConnection conn = new MySqlConnection(mycon);
MySqlDataAdapter da = new MySqlDataAdapter(sql, conn);
DataTable dtRows = new DataTable();
da.Fill(dtRows);
All data is loaded from database into C# DataTable of dtRows.
Collecting INSERT statement of each row:
for (int i = 0; i < dtRows.Rows.Count; i++)
{
StringBuilder sb = new StringBuilder();
sb.AppendFormat("INSERT INTO `people` VALUE(");
for (int j = 0; j < dtRows.Columns.Count; j++)
{
if (dtRows.Rows[i][j] == DBNull.Value) // NULL value
sb.AppendFormat("NULL");
else
sb.AppendFormat("'" + dtRows.Rows[i][j] + "'");
if (j + 1 != dtRows.Columns.Count)
sb.AppendFormat(",");
}
sb.AppendFormat(");");
MessageBox.Show(sb.ToString());
}
Result:
INSERT INTO `people` VALUE('1','James','19/09/2011 Monday 8:00:12 AM',NULL);
INSERT INTO `people` VALUE('2',NULL,'19/09/2011 Monday 11:00:40 AM','1');
INSERT INTO `people` VALUE('3','Thomas',NULL,'1');
Next, we need to do some modification on the DateTime Value Type of Data. C# default datetime format is affected by
System Regional and Language Settings and it is not compatible with MySQL datetime format. Thus we need to convert it into something acceptable by MySQL.
for (int i = 0; i < dtRows.Rows.Count; i++)
{
StringBuilder sb = new StringBuilder();
sb.AppendFormat("INSERT INTO `people` VALUE(");
for (int j = 0; j < dtRows.Columns.Count; j++)
{
string datatype = dtRows.Columns[j].DataType.ToString();
string text = "";
DateTime dtime = DateTime.Now;
if (datatype == "System.DateTime")
{
if (DateTime.TryParse(dtRows.Rows[i][j] + "", out dtime))
{
text = "'" + Convert.ToDateTime(dtRows.Rows[i][j]).ToString("yyyy-MM-dd HH:mm:ss") + "'";
}
else
text = "null";
}
else if (dtRows.Rows[i][j] + "" == "")
text = "null";
else
text = "'" + dtRows.Rows[i][j] + "'";
sb.AppendFormat(text);
if (j + 1 != dtRows.Columns.Count)
sb.AppendFormat(",");
}
sb.AppendFormat(");");
MessageBox.Show(sb.ToString());
}
Result:
INSERT INTO `people` VALUE(1,James,'2011-09-19 08:00:12',1);
INSERT INTO `people` VALUE(2,Cathrine,'2011-09-19 11:00:40',1);
INSERT INTO `people` VALUE(3,Thomas,'2011-09-20 12:23:10',1);
Next, we need to solve the special escape character that appear between MySQL and C#.
Example:
| Process |
Backup |
Backup |
Backup |
Restore |
Restore |
| Place |
MySQL |
C# |
Textfile |
C# |
MySQL |
| string |
\a |
\\a |
\\\\a |
\\a |
\a |
| Description |
Original string store in MySQL database |
Another "\" is automatically added by C# when the string passes into C# |
We manually add another "\\" into the string and write it into textfile. |
C# convert "\\\\" into "\\" after reading from textfile. |
MySQL will finally convert "\\" into "\" |
| string |
'a |
'a |
\\'a |
\'a |
'a |
| Description |
Original string store in MySQL database |
Nothing is changed when passing the string from MySQL to C# |
We manually add another "\\" into the string and write it into textfile. |
C# convert "\\" into "\" after reading from textfile. |
MySQL replace "\'" with "'" |
So, there are 2 special character we need to take care in order to pass information correctly during database restore process.
Simply replace "\\" with "\\\\" and
' with \\'.
for (int i = 0; i < dtRows.Rows.Count; i++)
{
StringBuilder sb = new StringBuilder();
sb.AppendFormat("INSERT INTO `people` VALUE(");
for (int j = 0; j < dtRows.Columns.Count; j++)
{
string datatype = dtRows.Columns[j].DataType.ToString();
string text = "";
DateTime dtime = DateTime.Now;
if (datatype == "System.DateTime")
{
if (DateTime.TryParse(dtRows.Rows[i][j] + "", out dtime))
{
text = "'" + Convert.ToDateTime(dtRows.Rows[i][j]).ToString("yyyy-MM-dd HH:mm:ss") + "'";
}
else
text = "null";
}
else if (dtRows.Rows[i][j] + "" == "")
text = "null";
else
{
text = dtRows.Rows[i][j] + "";
// Escape special character for MySQL commands
text = text.Replace("\\", "\\\\");
text = text.Replace("'", "\\'");
text = "'" + text + "'";
}
sb.AppendFormat(text);
if (j + 1 != dtRows.Columns.Count)
sb.AppendFormat(",");
}
sb.AppendFormat(");");
MessageBox.Show(sb.ToString());
}
Part 4: Write All SQL statements into textfile.
Lastly, we write all the SQL statements that collected into textfile.
We use a list to store all SQL statements.
List<string> SqlList = new List<string>();
SqlList.Add(sql);
All the sql statements has to be added into the list in the correct sequence.
For example, you won't CREATE a table, and then you DROP
it right?
We immediately add the sql statement by the time it is created.
After we added all the sql statements, its time to write it into textfile.
At the top of your project, add:
C#,
string[] sa = null;
sa = new string[SqlList.Count];
for (int i = 0; i < SqlList.Count; i++)
{
sa[i] = SQLs[i];
}
File.WriteAllLines("C:\\backup.sql", sa, Encoding.UTF8);
Done.
Part 5 : Restore
Open the file, read it and execute all SQLs one by one.
string myCon = "server=localhost;user=root;password=qwerty;database=people;port=3306";
MySqlConnection conn = new MySqlConnection(myCon);
string[] sqls = File.ReadAllLines("C:\\backup.sql", Encoding.UTF8);
MySqlCommand cmd = new MySqlCommand();
cmd.Connection = conn;
conn.Open();
foreach (string s in sqls)
{
cmd.CommandText = s;
cmd.ExecuteNonQuery();
}
conn.Close();
Done.