欢迎光临
我们一直在努力
当前位置：IDC资讯中心 > 网络编程 > 数据库教程 > 正文

使用程序导出建表语句，及以insert语句形式导出数据_数据库技巧

2007-08-24 分类：数据库教程 阅读(1229) 评论(0)
建站超值云服务器，限时71元/月

前一段时间在弄数据库设计，有时要把一些数据（数据类不大）导到其它类型的数据库里，发现无论是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

赞(0)
版权申明：本站文章部分自网络，如有侵权，请联系：west999com@outlook.com 特别注意：本站所有转载文章言论不代表本站观点！ 本站所提供的图片等素材，版权归原作者所有，如需使用，请与原作者联系。未经允许不得转载：IDC资讯中心 » 使用程序导出建表语句，及以insert语句形式导出数据_数据库技巧
分享到： 更多 (0)

相关推荐

热门标签

ldquo (1370)win (1324)活动 (1166)ios (1162)rdquo (818)微信 (747)iphone (728)小米 (722)windows (706)手机 (698)酷跑 (686)飞车 (540)天天 (492)qq (491)网站 (457)全民 (442)discuz (408)充值 (389)安装 (368)更新 (321)华为 (300)浏览器 (292)支付宝 (289)三星 (266)note (264)教程 (262)预览版 (261)小编 (258)越狱 (254)linux (248)

热门文章

分类目录