x
首页 后端[mysql资料] 导出建表语句,Insert语句形式导出数据

导出建表语句,Insert语句形式导出数据

发表于 2012-04-12 - 浏览:12722 评论:0 收藏 0

使用程序导出建表语句,及以Insert语句形式导出数据

前一段时间在弄数据库设计,有时要把一些数据(数据类不大)导到其它类型的数据库里,发现无论是Sql ServerOracleMySql,在从库中导出标准的建表和插入数据(即Insert语句)语句时,多少有些不如人意,于是结合自己的要求,自己动手写一个导出的程序。

 

程序在ListBoxlboxAll)中列出指定的数据库中所有的用户表名,选中要导出的表名将会被添加到另一ListBoxlboxSelected)中,程序将导出lboxSelected中所有表的建表语句以及以Insert语句形式导出的数据:

 

1Sql 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);
    }

}


 

2Oracle

获得用户表信息

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);
    }

}


 


 

3MySql

获得用户表信息

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 ServerOK的,企业管理器和查询分析器用起来很好。

 

对于MySql的图形界面,用的多的好像是"mysql front",一个字,烂!只要数据库中表较多(几十张)或者表中数据较多(几千行)时,就死机(机器配置很好的前提下)。后来上mysql官网上下了"mysql query browser",一样有问题,首先,每次只能执行一条语句,其次,只要语句中有汉字,马上死,两个字,烂啊!

 

对于Oracle,不知道大家都在用什么界面,这里用的是"oraedit",倒是不死,只是在编写存储过程或执行存储过程时,不时有莫名其妙的问题。

文章评论。。。