前一段时间在弄数据库设计，有时要把一些数据（数据类不大）导到其它类型的数据库里，发现无论是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(){ …… this.cmd.CommandText = 'select name tname from sysobjects where xtype='U''; try { this.conn.Open(); SqlDataAdapter da = new SqlDataAdapter(cmd); DataSet ds = new DataSet(); da.Fill(ds, 'alltable'); this.Invoke((MethodInvoker)delegate { this.lboxAll.DataSource = ds.Tables['alltable'].DefaultView; this.lboxAll.DisplayMember = 'tname'; }); } catch (Exception x) { MessageBox.Show(x.Message, '错误', MessageBoxButtons.OK, MessageBoxIcon.Information); } finally { if (conn.State == ConnectionState.Open) { conn.Close(); } }}
private void ExpSQL(){ this.sw = new StreamWriter(this.path,true,Encoding.Default); try { this.conn.Open(); for (int i = 0; i < this.lboxSelected.Items.Count; i++) { 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();
da = new SqlDataAdapter(cmd); da.Fill(ds, 'datainfo'); DataTable dt2 = ds.Tables['datainfo']; this.GenerateInsertStatement(dt, dt2);
Thread.Sleep(10); } this.sw.Flush(); this.sw.Close(); this.sw.Dispose(); MessageBox.Show('完成', '提示', MessageBoxButtons.OK, MessageBoxIcon.Information); } catch (Exception x) { MessageBox.Show(x.Message, '错误', MessageBoxButtons.OK, MessageBoxIcon.Information); } finally { if (conn.State == ConnectionState.Open) { conn.Close(); } }}
private void GenerateCreateStatement(DataTable dt){string sql =; 'CREATE TABLE ' + dt.Rows[0]['TABLE_NAME'].ToString() + '(' + Environment.NewLine;
for (int i = 0; i < dt.Rows.Count; i++) { 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') { sql += '(' + dt.Rows[i]['LENGTH'].ToString() + ')'; } if (dt.Rows[i]['DATA_TYPE'].ToString() == 'decimal' || dt.Rows[i]['DATA_TYPE'].ToString() == 'numeric') { sql += '(' + dt.Rows[i]['PREC'].ToString(); sql += ',' + dt.Rows[i]['SCALE'].ToString(); sql += ')'; } if (dt.Rows[i]['ISNULLABLE'].ToString() == '0') { sql += ' NOT NULL'; } sql += ',' + Environment.NewLine; } sql = sql.Substring(0, sql.LastIndexOf(',')) + ');' + Environment.NewLine; this.sw.Write(sql); this.sw.Write(Environment.NewLine);}
private void GenerateInsertStatement(DataTable dtColInfo, DataTable dtDataInfo){ 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++) { values = 'VALUES('; for (int j = 0; j < dtColInfo.Rows.Count; j++) { colName = dtColInfo.Rows[j]['COLUMN_NAME'].ToString(); dataType = dtColInfo.Rows[j]['DATA_TYPE'].ToString(); if (i == 0) { sql += colName + ','; } if (dtDataInfo.Rows[i][colName].ToString() == '') { values += 'NULL,'; } else { if (dataType == 'varchar' || dataType == 'char' || dataType == 'datetime' || dataType == 'nvarchar' || dataType == 'nchar')//小写 { values += ''' + dtDataInfo.Rows[i][colName].ToString().Trim().Replace(''','''') + '','; };;;;; else// { 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(){ …… this.cmd.CommandText = 'select tname from tab'; try { this.conn.Open(); OracleDataAdapter da = new OracleDataAdapter(cmd); DataSet ds = new DataSet(); da.Fill(ds, 'alltable'); this.Invoke((MethodInvoker)delegate { this.lboxAll.DataSource = ds.Tables['alltable'].DefaultView; this.lboxAll.DisplayMember = 'tname'; }); } catch (Exception x) { MessageBox.Show(x.Message, '错误', MessageBoxButtons.OK, MessageBoxIcon.Information); } finally { if (conn.State == ConnectionState.Open) { conn.Close(); } }}
private void ExpSQL(){ this.sw = new StreamWriter(this.path,true,Encoding.Default); try { this.conn.Open(); for (int i = 0; i < this.lboxSelected.Items.Count; i++) { 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);
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);
Thread.Sleep(10); } this.sw.Flush(); this.sw.Close(); this.sw.Dispose(); MessageBox.Show('完成', '提示', MessageBoxButtons.OK, MessageBoxIcon.Information); } catch (Exception x) { MessageBox.Show(x.Message, '错误', MessageBoxButtons.OK, MessageBoxIcon.Information); } finally { if (conn.State == ConnectionState.Open) { conn.Close(); } }}
private void GenerateCreateStatement(DataTable dt){string sql = 'CREATE TABLE ' + dt.Rows[0]['TABLE_NAME'].ToString() + '(' + Environment.NewLine; for (int i = 0; i < dt.Rows.Count; i++) { 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') { sql += '(' + dt.Rows[i]['DATA_LENGTH'].ToString() + ')'; } if (dt.Rows[i]['DATA_TYPE'].ToString() == 'NUMBER' && dt.Rows[i]['DATA_PRECISION'] != System.DBNull.Value) { sql += '(' + dt.Rows[i]['DATA_PRECISION'].ToString(); if (dt.Rows[i]['DATA_SCALE'].ToString() != '0') { sql += ',' + dt.Rows[i]['DATA_SCALE'].ToString(); } sql += ')'; } if (dt.Rows[i]['NULLABLE'].ToString() == 'N') { sql += ' NOT NULL'; } sql += ',' + Environment.NewLine; } sql = sql.Substring(0, sql.LastIndexOf(',')) + ');' + Environment.NewLine; this.sw.Write(sql); this.sw.Write(Environment.NewLine);}
private void GenerateInsertStatement(DataTable dtColInfo, DataTable dtDataInfo){ 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++) { values = 'VALUES('; for (int j = 0; j < dtColInfo.Rows.Count; j++) { colName = dtColInfo.Rows[j]['COLUMN_NAME'].ToString(); dataType = dtColInfo.Rows[j]['DATA_TYPE'].ToString(); if (i == 0) { sql += colName + ','; } if (dtDataInfo.Rows[i][colName].ToString() == '') { values += 'NULL,'; } else { if (dataType == 'VARCHAR2' || dataType == 'CHAR') { values += ''' + dtDataInfo.Rows[i][colName].ToString().Trim().Replace(''', '''') + '','; } else if (dataType == 'DATE') { values += 'TO_DATE('' + dtDataInfo.Rows[i][colName].ToString() + '','yyyy-mm-dd hh24:mi:ss'),'; } else// { 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(){ …… this.cmd.CommandText = 'show tables;'; try { this.conn.Open(); MySqlDataAdapter da = new MySqlDataAdapter(cmd); DataSet ds = new DataSet(); da.Fill(ds, 'alltable'); this.Invoke((MethodInvoker)delegate { this.lboxAll.DataSource = ds.Tables['alltable'].DefaultView; this.lboxAll.DisplayMember = 'Tables_in_'+this.tbDatabase.Text.Trim(); }); } catch (Exception x) { MessageBox.Show(x.Message, '错误', MessageBoxButtons.OK, MessageBoxIcon.Information); } finally { if (conn.State == ConnectionState.Open) { conn.Close(); } }}
private void ExpSQL(){ this.sw = new StreamWriter(this.path, true, Encoding.Default); try { this.conn.Open(); for (int i = 0; i < this.lboxSelected.Items.Count; i++) { 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'];
this.cmd.CommandText = 'select * from ' + this.lboxSelected.Items[i].ToString(); da = new MySqlDataAdapter(cmd); da.Fill(ds, 'datainfo'); DataTable dt2 = ds.Tables['datainfo'];
this.GenerateCreateStatement(this.lboxSelected.Items[i].ToString());// this.GenerateInsertStatement(dt, dt2, this.lboxSelected.Items[i].ToString());
Thread.Sleep(10); } this.sw.Flush(); this.sw.Close(); this.sw.Dispose(); MessageBox.Show('完成', '提示', MessageBoxButtons.OK, MessageBoxIcon.Information); } catch (Exception x) { MessageBox.Show(x.Message, '错误', MessageBoxButtons.OK, MessageBoxIcon.Information); } finally { if (conn.State == ConnectionState.Open) { conn.Close(); } }}
private void GenerateCreateStatement(string tname){ string sql = ''; try { if (conn.State == ConnectionState.Closed) { conn.Open(); } this.cmd.CommandText ='show create table ' + tname; MySqlDataReader dr = this.cmd.ExecuteReader(); if (dr.Read()) { sql = dr['Create Table'].ToString(); } } catch (Exception x) { MessageBox.Show(x.Message, '错误', MessageBoxButtons.OK, MessageBoxIcon.Information); } finally { if (conn.State == ConnectionState.Open) { conn.Close(); } }
sql = sql.Replace('`', '') + ';' + Environment.NewLine; this.sw.Write(sql); this.sw.Write(Environment.NewLine);}
private void GenerateInsertStatement(DataTable dtColInfo, DataTable dtDataInfo,string tname){ string sql =; 'INSERT INTO ' + tname + '(';
string values = ''; string colName = ''; string dataType = ''; for (int i = 0; i < dtDataInfo.Rows.Count; i++) { values = 'VALUES('; for (int j = 0; j < dtColInfo.Rows.Count; j++) { colName = dtColInfo.Rows[j]['Field'].ToString(); dataType = dtColInfo.Rows[j]['Type'].ToString(); if (i == 0) { sql += colName + ','; } if (dtDataInfo.Rows[i][colName].ToString() == '') { values += 'NULL,'; } else { if (dataType.StartsWith('varchar') || dataType.StartsWith('char') || dataType == 'datetime' || dataType == 'date') { values += ''' + dtDataInfo.Rows[i][colName].ToString().Trim().Replace(''', '''') + '','; } else// { 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”，倒是不死，只是在编写存储过程或执行存储过程时，不时有莫名其妙的问题。
http://www.cnblogs.com/KissKnife/archive/2006/12/10/587997.html
