前一段时间在弄数据库设计,有时要把一些数据(数据类不大)导到其它类型的数据库里,发现无论是Sql Server、Oracle、MySql,在从库中导出标准的建表和插入数据(即Insert语句)语句时,多少有些不如人意,于是结合自己的要求,自己动手写一个导出的程序。
程序在ListBox(lboxAll)中列出指定的数据库中所有的用户表名,选中要导出的表名将会被添加到另一ListBox(lboxSelected)中,程序将导出lboxSelected中所有表的建表语句以及以Insert语句形式导出的数据:
1)Sql Server:
获得用户表信息
select name tname from sysobjects where xtype='U'
获取表的字段信息
select sysobjects.name table_name,syscolumns.name column_name,systypes.name
data_type,syscolumns.isnullable,syscolumns.length,syscolumns.prec,syscolumns.scale
from sysobjects
join syscolumns
on sysobjects.id=syscolumns.id
join systypes
on syscolumns.xtype=systypes.xtype
where sysobjects.name='tablename'
相关程序(省略部分代码):
private
void
ShowAllTable()
![](https://i-blog.csdnimg.cn/blog_migrate/34031c708bfe702fe82d01ff5c6593aa.gif)
{
……
this.cmd.CommandText = "select name tname from sysobjects where xtype='U'";
try
![](https://i-blog.csdnimg.cn/blog_migrate/3112b7b6526db5bc83e275260ae60525.gif)
{
this.conn.Open();
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds, "alltable");
this.Invoke((MethodInvoker)delegate
![](https://i-blog.csdnimg.cn/blog_migrate/3112b7b6526db5bc83e275260ae60525.gif)
{
this.lboxAll.DataSource = ds.Tables["alltable"].DefaultView;
this.lboxAll.DisplayMember = "tname";
});
}
catch (Exception x)
![](https://i-blog.csdnimg.cn/blog_migrate/3112b7b6526db5bc83e275260ae60525.gif)
{
MessageBox.Show(x.Message, "错误", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
finally
![](https://i-blog.csdnimg.cn/blog_migrate/3112b7b6526db5bc83e275260ae60525.gif)
{
if (conn.State == ConnectionState.Open)
![](https://i-blog.csdnimg.cn/blog_migrate/3112b7b6526db5bc83e275260ae60525.gif)
{
conn.Close();
}
}
}
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
private
void
ExpSQL()
![](https://i-blog.csdnimg.cn/blog_migrate/34031c708bfe702fe82d01ff5c6593aa.gif)
{
this.sw = new StreamWriter(this.path,true,Encoding.Default);
try
![](https://i-blog.csdnimg.cn/blog_migrate/3112b7b6526db5bc83e275260ae60525.gif)
{
this.conn.Open();
for (int i = 0; i < this.lboxSelected.Items.Count; i++)
![](https://i-blog.csdnimg.cn/blog_migrate/3112b7b6526db5bc83e275260ae60525.gif)
{
this.cmd.CommandText ="select sysobjects.name table_name,syscolumns.name
column_name,systypes.name data_type, syscolumns.isnullable,
syscolumns.length,syscolumns.prec,syscolumns.scale";
this.cmd.CommandText +=Environment.NewLine +"from sysobjects";
this.cmd.CommandText +=Environment.NewLine +"join syscolumns";
this.cmd.CommandText +=Environment.NewLine +"on sysobjects.id=syscolumns.id";
this.cmd.CommandText +=Environment.NewLine +"join systypes";
this.cmd.CommandText +=Environment.NewLine +"on syscolumns.xtype=systypes.xtype";
this.cmd.CommandText +=Environment.NewLine +"where sysobjects.name='" +
this.lboxSelected.Items[i].ToString() + "'";
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds, "colinfo");
DataTable dt = ds.Tables["colinfo"];
this.GenerateCreateStatement(dt);
this.cmd.CommandText = "select * from " + this.lboxSelected.Items[i].ToString();
![](https://i-blog.csdnimg.cn/blog_migrate/587e34b10dcf5efbc0859b53470a2db3.gif)
da = new SqlDataAdapter(cmd);
da.Fill(ds, "datainfo");
DataTable dt2 = ds.Tables["datainfo"];
this.GenerateInsertStatement(dt, dt2);
![](https://i-blog.csdnimg.cn/blog_migrate/587e34b10dcf5efbc0859b53470a2db3.gif)
Thread.Sleep(10);
}
this.sw.Flush();
this.sw.Close();
this.sw.Dispose();
MessageBox.Show("完成", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (Exception x)
![](https://i-blog.csdnimg.cn/blog_migrate/3112b7b6526db5bc83e275260ae60525.gif)
{
MessageBox.Show(x.Message, "错误", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
finally
![](https://i-blog.csdnimg.cn/blog_migrate/3112b7b6526db5bc83e275260ae60525.gif)
{
if (conn.State == ConnectionState.Open)
![](https://i-blog.csdnimg.cn/blog_migrate/3112b7b6526db5bc83e275260ae60525.gif)
{
conn.Close();
}
}
}
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
private
void
GenerateCreateStatement(DataTable dt)
![](https://i-blog.csdnimg.cn/blog_migrate/34031c708bfe702fe82d01ff5c6593aa.gif)
{
string sql = "CREATE TABLE " + dt.Rows[0]["TABLE_NAME"].ToString() + "(" +
Environment.NewLine;
![](https://i-blog.csdnimg.cn/blog_migrate/587e34b10dcf5efbc0859b53470a2db3.gif)
for (int i = 0; i < dt.Rows.Count; i++)
![](https://i-blog.csdnimg.cn/blog_migrate/3112b7b6526db5bc83e275260ae60525.gif)
{
sql += dt.Rows[i]["COLUMN_NAME"].ToString() + " " + dt.Rows[i]["DATA_TYPE"].ToString();
if (dt.Rows[i]["DATA_TYPE"].ToString() == "varchar" ||
dt.Rows[i]["DATA_TYPE"].ToString() == "char" ||
dt.Rows[i]["DATA_TYPE"].ToString() == "nvarchar" ||
dt.Rows[i]["DATA_TYPE"].ToString() == "nchar")
![](https://i-blog.csdnimg.cn/blog_migrate/3112b7b6526db5bc83e275260ae60525.gif)
{
sql += "(" + dt.Rows[i]["LENGTH"].ToString() + ")";
}
if (dt.Rows[i]["DATA_TYPE"].ToString() == "decimal" ||
dt.Rows[i]["DATA_TYPE"].ToString() == "numeric")
![](https://i-blog.csdnimg.cn/blog_migrate/3112b7b6526db5bc83e275260ae60525.gif)
{
sql += "(" + dt.Rows[i]["PREC"].ToString();
sql += "," + dt.Rows[i]["SCALE"].ToString();
sql += ")";
}
if (dt.Rows[i]["ISNULLABLE"].ToString() == "0")
![](https://i-blog.csdnimg.cn/blog_migrate/3112b7b6526db5bc83e275260ae60525.gif)
{
sql += " NOT NULL";
}
sql += "," + Environment.NewLine;
}
sql = sql.Substring(0, sql.LastIndexOf(',')) + ");" + Environment.NewLine;
this.sw.Write(sql);
this.sw.Write(Environment.NewLine);
}
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
private
void
GenerateInsertStatement(DataTable dtColInfo, DataTable dtDataInfo)
![](https://i-blog.csdnimg.cn/blog_migrate/34031c708bfe702fe82d01ff5c6593aa.gif)
{
string sql = "INSERT INTO " + dtColInfo.Rows[0]["TABLE_NAME"].ToString() + "(";
![](https://i-blog.csdnimg.cn/blog_migrate/587e34b10dcf5efbc0859b53470a2db3.gif)
string values = "";
string colName = "";
string dataType = "";
for (int i = 0; i < dtDataInfo.Rows.Count; i++)
![](https://i-blog.csdnimg.cn/blog_migrate/3112b7b6526db5bc83e275260ae60525.gif)
{
values = "VALUES(";
for (int j = 0; j < dtColInfo.Rows.Count; j++)
![](https://i-blog.csdnimg.cn/blog_migrate/3112b7b6526db5bc83e275260ae60525.gif)
{
colName = dtColInfo.Rows[j]["COLUMN_NAME"].ToString();
dataType = dtColInfo.Rows[j]["DATA_TYPE"].ToString();
if (i == 0)
![](https://i-blog.csdnimg.cn/blog_migrate/3112b7b6526db5bc83e275260ae60525.gif)
{
sql += colName + ",";
}
if (dtDataInfo.Rows[i][colName].ToString() == "")
![](https://i-blog.csdnimg.cn/blog_migrate/3112b7b6526db5bc83e275260ae60525.gif)
{
values += "NULL,";
}
else
![](https://i-blog.csdnimg.cn/blog_migrate/3112b7b6526db5bc83e275260ae60525.gif)
{
if (dataType == "varchar" || dataType == "char" || dataType == "datetime" ||
dataType == "nvarchar" || dataType == "nchar")//小写
![](https://i-blog.csdnimg.cn/blog_migrate/3112b7b6526db5bc83e275260ae60525.gif)
{
values += "'" +
dtDataInfo.Rows[i][colName].ToString().Trim().Replace("/'","/'/'") + "',";
}
else//
![](https://i-blog.csdnimg.cn/blog_migrate/3112b7b6526db5bc83e275260ae60525.gif)
{
values += dtDataInfo.Rows[i][colName].ToString() + ",";
}
}
}
this.sw.Write(sql.Substring(0, sql.LastIndexOf(',')) + ")" + Environment.NewLine +
values.Substring(0, values.LastIndexOf(',')) + ");"
+ Environment.NewLine);
this.sw.Write(Environment.NewLine);
}
}
2)Oracle:
获得用户表信息
select tname from tab
获取表的字段信息
select * from ALL_TAB_COLUMNS A WHERE A.TABLE_NAME='tablename'
相关程序(省略部分代码):
private
void
ShowAllTable()
![](https://i-blog.csdnimg.cn/blog_migrate/34031c708bfe702fe82d01ff5c6593aa.gif)
{
……
this.cmd.CommandText = "select tname from tab";
try
![](https://i-blog.csdnimg.cn/blog_migrate/3112b7b6526db5bc83e275260ae60525.gif)
{
this.conn.Open();
OracleDataAdapter da = new OracleDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds, "alltable");
this.Invoke((MethodInvoker)delegate
![](https://i-blog.csdnimg.cn/blog_migrate/3112b7b6526db5bc83e275260ae60525.gif)
{
this.lboxAll.DataSource = ds.Tables["alltable"].DefaultView;
this.lboxAll.DisplayMember = "tname";
});
}
catch (Exception x)
![](https://i-blog.csdnimg.cn/blog_migrate/3112b7b6526db5bc83e275260ae60525.gif)
{
MessageBox.Show(x.Message, "错误", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
finally
![](https://i-blog.csdnimg.cn/blog_migrate/3112b7b6526db5bc83e275260ae60525.gif)
{
if (conn.State == ConnectionState.Open)
![](https://i-blog.csdnimg.cn/blog_migrate/3112b7b6526db5bc83e275260ae60525.gif)
{
conn.Close();
}
}
}
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
private
void
ExpSQL()
![](https://i-blog.csdnimg.cn/blog_migrate/34031c708bfe702fe82d01ff5c6593aa.gif)
{
this.sw = new StreamWriter(this.path,true,Encoding.Default);
try
![](https://i-blog.csdnimg.cn/blog_migrate/3112b7b6526db5bc83e275260ae60525.gif)
{
this.conn.Open();
for (int i = 0; i < this.lboxSelected.Items.Count; i++)
![](https://i-blog.csdnimg.cn/blog_migrate/3112b7b6526db5bc83e275260ae60525.gif)
{
this.cmd.CommandText = "select * from ALL_TAB_COLUMNS A WHERE A.TABLE_NAME='" +
this.lboxSelected.Items[i].ToString()+"'";
OracleDataAdapter da = new OracleDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds, "colinfo");
DataTable dt = ds.Tables["colinfo"];
this.GenerateCreateStatement(dt);
![](https://i-blog.csdnimg.cn/blog_migrate/587e34b10dcf5efbc0859b53470a2db3.gif)
this.cmd.CommandText = "select * from " + this.lboxSelected.Items[i].ToString();
da = new OracleDataAdapter(cmd);
da.Fill(ds, "datainfo");
DataTable dt2 = ds.Tables["datainfo"];
this.GenerateInsertStatement(dt, dt2);
![](https://i-blog.csdnimg.cn/blog_migrate/587e34b10dcf5efbc0859b53470a2db3.gif)
Thread.Sleep(10);
}
this.sw.Flush();
this.sw.Close();
this.sw.Dispose();
MessageBox.Show("完成", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (Exception x)
![](https://i-blog.csdnimg.cn/blog_migrate/3112b7b6526db5bc83e275260ae60525.gif)
{
MessageBox.Show(x.Message, "错误", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
finally
![](https://i-blog.csdnimg.cn/blog_migrate/3112b7b6526db5bc83e275260ae60525.gif)
{
if (conn.State == ConnectionState.Open)
![](https://i-blog.csdnimg.cn/blog_migrate/3112b7b6526db5bc83e275260ae60525.gif)
{
conn.Close();
}
}
}
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
private
void
GenerateCreateStatement(DataTable dt)
![](https://i-blog.csdnimg.cn/blog_migrate/34031c708bfe702fe82d01ff5c6593aa.gif)
{
string sql = "CREATE TABLE " + dt.Rows[0]["TABLE_NAME"].ToString() + "(" +
Environment.NewLine;
for (int i = 0; i < dt.Rows.Count; i++)
![](https://i-blog.csdnimg.cn/blog_migrate/3112b7b6526db5bc83e275260ae60525.gif)
{
sql += dt.Rows[i]["COLUMN_NAME"].ToString() + " " + dt.Rows[i]["DATA_TYPE"].ToString();
if (dt.Rows[i]["DATA_TYPE"].ToString() == "VARCHAR2" ||
dt.Rows[i]["DATA_TYPE"].ToString() == "CHAR")
![](https://i-blog.csdnimg.cn/blog_migrate/3112b7b6526db5bc83e275260ae60525.gif)
{
sql += "(" + dt.Rows[i]["DATA_LENGTH"].ToString() + ")";
}
if (dt.Rows[i]["DATA_TYPE"].ToString() == "NUMBER" && dt.Rows[i]["DATA_PRECISION"] !=
System.DBNull.Value)
![](https://i-blog.csdnimg.cn/blog_migrate/3112b7b6526db5bc83e275260ae60525.gif)
{
sql += "(" + dt.Rows[i]["DATA_PRECISION"].ToString();
if (dt.Rows[i]["DATA_SCALE"].ToString() != "0")
![](https://i-blog.csdnimg.cn/blog_migrate/3112b7b6526db5bc83e275260ae60525.gif)
{
sql += "," + dt.Rows[i]["DATA_SCALE"].ToString();
}
sql += ")";
}
if (dt.Rows[i]["NULLABLE"].ToString() == "N")
![](https://i-blog.csdnimg.cn/blog_migrate/3112b7b6526db5bc83e275260ae60525.gif)
{
sql += " NOT NULL";
}
sql += "," + Environment.NewLine;
}
sql = sql.Substring(0, sql.LastIndexOf(',')) + ");" + Environment.NewLine;
this.sw.Write(sql);
this.sw.Write(Environment.NewLine);
}
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
private
void
GenerateInsertStatement(DataTable dtColInfo, DataTable dtDataInfo)
![](https://i-blog.csdnimg.cn/blog_migrate/34031c708bfe702fe82d01ff5c6593aa.gif)
{
string sql = "INSERT INTO " + dtColInfo.Rows[0]["TABLE_NAME"].ToString() + "(";
string values = "";
string colName = "";
string dataType = "";
for (int i = 0; i < dtDataInfo.Rows.Count; i++)
![](https://i-blog.csdnimg.cn/blog_migrate/3112b7b6526db5bc83e275260ae60525.gif)
{
values = "VALUES(";
for (int j = 0; j < dtColInfo.Rows.Count; j++)
![](https://i-blog.csdnimg.cn/blog_migrate/3112b7b6526db5bc83e275260ae60525.gif)
{
colName = dtColInfo.Rows[j]["COLUMN_NAME"].ToString();
dataType = dtColInfo.Rows[j]["DATA_TYPE"].ToString();
if (i == 0)
![](https://i-blog.csdnimg.cn/blog_migrate/3112b7b6526db5bc83e275260ae60525.gif)
{
sql += colName + ",";
}
if (dtDataInfo.Rows[i][colName].ToString() == "")
![](https://i-blog.csdnimg.cn/blog_migrate/3112b7b6526db5bc83e275260ae60525.gif)
{
values += "NULL,";
}
else
![](https://i-blog.csdnimg.cn/blog_migrate/3112b7b6526db5bc83e275260ae60525.gif)
{
if (dataType == "VARCHAR2" || dataType == "CHAR")
![](https://i-blog.csdnimg.cn/blog_migrate/3112b7b6526db5bc83e275260ae60525.gif)
{
values += "'" +
dtDataInfo.Rows[i][colName].ToString().Trim().Replace("/'", "/'/'") + "',";
}
else if (dataType == "DATE")
![](https://i-blog.csdnimg.cn/blog_migrate/3112b7b6526db5bc83e275260ae60525.gif)
{
values += "TO_DATE('" + dtDataInfo.Rows[i][colName].ToString() +
"','yyyy-mm-dd hh24:mi:ss'),";
}
else//
![](https://i-blog.csdnimg.cn/blog_migrate/3112b7b6526db5bc83e275260ae60525.gif)
{
values += dtDataInfo.Rows[i][colName].ToString() + ",";
}
}
}
this.sw.Write(sql.Substring(0, sql.LastIndexOf(',')) + ")" + Environment.NewLine +
values.Substring(0, values.LastIndexOf(',')) + ");" + Environment.NewLine);
this.sw.Write(Environment.NewLine);
}
}
3)MySql:
获得用户表信息
show tables;
MySql中可以不必自己去拼建表语句,使用以下Sql获得建表语句
show create table tablename;
获取表的字段信息
show columns from tablename;
相关程序(省略部分代码):
private
void
ShowAllTable()
![](https://i-blog.csdnimg.cn/blog_migrate/34031c708bfe702fe82d01ff5c6593aa.gif)
{
……
this.cmd.CommandText = "show tables;";
try
![](https://i-blog.csdnimg.cn/blog_migrate/3112b7b6526db5bc83e275260ae60525.gif)
{
this.conn.Open();
MySqlDataAdapter da = new MySqlDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds, "alltable");
this.Invoke((MethodInvoker)delegate
![](https://i-blog.csdnimg.cn/blog_migrate/3112b7b6526db5bc83e275260ae60525.gif)
{
this.lboxAll.DataSource = ds.Tables["alltable"].DefaultView;
this.lboxAll.DisplayMember = "Tables_in_"+this.tbDatabase.Text.Trim();
});
}
catch (Exception x)
![](https://i-blog.csdnimg.cn/blog_migrate/3112b7b6526db5bc83e275260ae60525.gif)
{
MessageBox.Show(x.Message, "错误", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
finally
![](https://i-blog.csdnimg.cn/blog_migrate/3112b7b6526db5bc83e275260ae60525.gif)
{
if (conn.State == ConnectionState.Open)
![](https://i-blog.csdnimg.cn/blog_migrate/3112b7b6526db5bc83e275260ae60525.gif)
{
conn.Close();
}
}
}
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
private
void
ExpSQL()
![](https://i-blog.csdnimg.cn/blog_migrate/34031c708bfe702fe82d01ff5c6593aa.gif)
{
this.sw = new StreamWriter(this.path, true, Encoding.Default);
try
![](https://i-blog.csdnimg.cn/blog_migrate/3112b7b6526db5bc83e275260ae60525.gif)
{
this.conn.Open();
for (int i = 0; i < this.lboxSelected.Items.Count; i++)
![](https://i-blog.csdnimg.cn/blog_migrate/3112b7b6526db5bc83e275260ae60525.gif)
{
this.cmd.CommandText = "show columns from " +
this.lboxSelected.Items[i].ToString();
MySqlDataAdapter da = new MySqlDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds, "colinfo");
DataTable dt = ds.Tables["colinfo"];
![](https://i-blog.csdnimg.cn/blog_migrate/587e34b10dcf5efbc0859b53470a2db3.gif)
this.cmd.CommandText = "select * from " + this.lboxSelected.Items[i].ToString();
da = new MySqlDataAdapter(cmd);
da.Fill(ds, "datainfo");
DataTable dt2 = ds.Tables["datainfo"];
![](https://i-blog.csdnimg.cn/blog_migrate/587e34b10dcf5efbc0859b53470a2db3.gif)
this.GenerateCreateStatement(this.lboxSelected.Items[i].ToString());//
this.GenerateInsertStatement(dt, dt2, this.lboxSelected.Items[i].ToString());
![](https://i-blog.csdnimg.cn/blog_migrate/587e34b10dcf5efbc0859b53470a2db3.gif)
Thread.Sleep(10);
}
this.sw.Flush();
this.sw.Close();
this.sw.Dispose();
MessageBox.Show("完成", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (Exception x)
![](https://i-blog.csdnimg.cn/blog_migrate/3112b7b6526db5bc83e275260ae60525.gif)
{
MessageBox.Show(x.Message, "错误", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
finally
![](https://i-blog.csdnimg.cn/blog_migrate/3112b7b6526db5bc83e275260ae60525.gif)
{
if (conn.State == ConnectionState.Open)
![](https://i-blog.csdnimg.cn/blog_migrate/3112b7b6526db5bc83e275260ae60525.gif)
{
conn.Close();
}
}
}
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
private
void
GenerateCreateStatement(
string
tname)
![](https://i-blog.csdnimg.cn/blog_migrate/34031c708bfe702fe82d01ff5c6593aa.gif)
{
string sql = "";
try
![](https://i-blog.csdnimg.cn/blog_migrate/3112b7b6526db5bc83e275260ae60525.gif)
{
if (conn.State == ConnectionState.Closed)
![](https://i-blog.csdnimg.cn/blog_migrate/3112b7b6526db5bc83e275260ae60525.gif)
{
conn.Open();
}
this.cmd.CommandText ="show create table " + tname;
MySqlDataReader dr = this.cmd.ExecuteReader();
if (dr.Read())
![](https://i-blog.csdnimg.cn/blog_migrate/3112b7b6526db5bc83e275260ae60525.gif)
{
sql = dr["Create Table"].ToString();
}
}
catch (Exception x)
![](https://i-blog.csdnimg.cn/blog_migrate/3112b7b6526db5bc83e275260ae60525.gif)
{
MessageBox.Show(x.Message, "错误", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
finally
![](https://i-blog.csdnimg.cn/blog_migrate/3112b7b6526db5bc83e275260ae60525.gif)
{
if (conn.State == ConnectionState.Open)
![](https://i-blog.csdnimg.cn/blog_migrate/3112b7b6526db5bc83e275260ae60525.gif)
{
conn.Close();
}
}
![](https://i-blog.csdnimg.cn/blog_migrate/587e34b10dcf5efbc0859b53470a2db3.gif)
sql = sql.Replace("`", "") + ";" + Environment.NewLine;
this.sw.Write(sql);
this.sw.Write(Environment.NewLine);
}
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
private
void
GenerateInsertStatement(DataTable dtColInfo, DataTable dtDataInfo,
string
tname)
![](https://i-blog.csdnimg.cn/blog_migrate/34031c708bfe702fe82d01ff5c6593aa.gif)
{
string sql = "INSERT INTO " + tname + "(";
![](https://i-blog.csdnimg.cn/blog_migrate/587e34b10dcf5efbc0859b53470a2db3.gif)
string values = "";
string colName = "";
string dataType = "";
for (int i = 0; i < dtDataInfo.Rows.Count; i++)
![](https://i-blog.csdnimg.cn/blog_migrate/3112b7b6526db5bc83e275260ae60525.gif)
{
values = "VALUES(";
for (int j = 0; j < dtColInfo.Rows.Count; j++)
![](https://i-blog.csdnimg.cn/blog_migrate/3112b7b6526db5bc83e275260ae60525.gif)
{
colName = dtColInfo.Rows[j]["Field"].ToString();
dataType = dtColInfo.Rows[j]["Type"].ToString();
if (i == 0)
![](https://i-blog.csdnimg.cn/blog_migrate/3112b7b6526db5bc83e275260ae60525.gif)
{
sql += colName + ",";
}
if (dtDataInfo.Rows[i][colName].ToString() == "")
![](https://i-blog.csdnimg.cn/blog_migrate/3112b7b6526db5bc83e275260ae60525.gif)
{
values += "NULL,";
}
else
![](https://i-blog.csdnimg.cn/blog_migrate/3112b7b6526db5bc83e275260ae60525.gif)
{
if (dataType.StartsWith("varchar") || dataType.StartsWith("char") ||
dataType == "datetime" || dataType == "date")
![](https://i-blog.csdnimg.cn/blog_migrate/3112b7b6526db5bc83e275260ae60525.gif)
{
values += "'" +
dtDataInfo.Rows[i][colName].ToString().Trim().Replace("/'", "/'/'") + "',";
}
else//
![](https://i-blog.csdnimg.cn/blog_migrate/3112b7b6526db5bc83e275260ae60525.gif)
{
values += dtDataInfo.Rows[i][colName].ToString() + ",";
}
}
}
this.sw.Write(sql.Substring(0, sql.LastIndexOf(',')) + ")" + Environment.NewLine +
values.Substring(0, values.LastIndexOf(',')) + ");" + Environment.NewLine);
this.sw.Write(Environment.NewLine);
}
}
程序里有好些方面是没有处理的,包括:表名、字段名为数据库保留字;表名、字段名含有特殊字符(如空格);一些数据类型没有处理,如Sql Server中的Float型等;主、外键的声明;对大对象类型没有处理等等等等,只是原理上大抵是如此。
对于MySql导出Insert语句,如果不是要移到其它类型的数据库上,用MySqlDump命令是OK,不用上面这么麻烦。
顺便抱怨一下各数据库的图型界面,Sql Server是OK的,企业管理器和查询分析器用起来很好。
对于MySql的图形界面,用的多的好像是“mysql front”,一个字,烂!只要数据库中表较多(几十张)或者表中数据较多(几千行)时,就死机(机器配置很好的前提下)。后来上mysql官网上下了“mysql query browser”,一样有问题,首先,每次只能执行一条语句,其次,只要语句中有汉字,马上死,两个字,烂啊!
对于Oracle,不知道大家都在用什么界面,这里用的是“oraedit”,倒是不死,只是在编写存储过程或执行存储过程时,不时有莫名其妙的问题。