使用程序導出建表語句，及以Insert語句形式導出數據

2024-07-21 02:31:56

字體：大中小

來源：轉載

供稿：網友

前一段時間在弄數據庫設計，有時要把一些數據（數據類不大）導到其它類型的數據庫里，發現無論是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);

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

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”，一樣有問題，首先，每次只能執行一條語句，其次，只要語句中有漢字，馬上死，兩個字，爛?。?/P>

對于Oracle，不知道大家都在用什么界面，這里用的是“oraedit”，倒是不死，只是在編寫存儲過程或執行存儲過程時，不時有莫名其妙的問題。

http://www.cnblogs.com/KissKnife/archive/2006/12/10/587997.html

上一篇：提數據高分頁效率

下一篇：分頁查詢的一個幫助類