Asp.net通过MySql.Data.dll操作mysql数据库(二）

//备份数据库
    private string sqldumptable(string tblname)
    {        
        StringBuilder sb = new StringBuilder();
        sb.Append("DROP TABLE IF EXISTS `" + tblname + "`;\n");
        sb.Append("CREATE TABLE " + tblname + " (\n");
        int firstfield=1;

        DataTable dtFields = RunTable("SHOW FIELDS FROM " + tblname + "");
        for (int i = 0, k = dtFields.Rows.Count; i < k; i++)
        {
            if (firstfield != 1)
                sb.Append(",\n");
            else
                firstfield = 0;
            sb.Append("`"+dtFields.Rows[i]["Field"].ToString() +"`  "+ dtFields.Rows[i]["Type"].ToString());
            if (dtFields.Rows[i]["Default"] != null && dtFields.Rows[i]["Default"].ToString() != string.Empty)
                sb.Append(" DEFAULT " + dtFields.Rows[i]["Default"]);
            if (dtFields.Rows[i]["Null"].ToString().ToUpper() != "YES")
                sb.Append(" NOT NULL ");
            if (dtFields.Rows[i]["Extra"].ToString() != "")
                sb.Append(dtFields.Rows[i]["Extra"].ToString());
        }
        dtFields.Dispose();

          
        DataTable dtKeys = RunTable("SHOW KEYS FROM " + tblname + "");
        bool haskey = false;
        string PRIMARY = string.Empty;
        for (int i = 0, k = dtKeys.Rows.Count; i < k; i++)
        {
            string kname = dtKeys.Rows[i]["Key_name"].ToString();
            if (kname.ToUpper() != "PRIMARY" && dtKeys.Rows[i]["Non_unique"].ToString().Trim() == "0")
            {
                kname = "UNIQUE|" + kname + "";
            }
            
            if (kname.ToUpper() == "PRIMARY")
            {
                if (haskey)
                {
                    PRIMARY = PRIMARY + ",";
                }
                else
                {
                    haskey = true;
                }
                PRIMARY = PRIMARY + dtKeys.Rows[i]["Column_name"];
            }
            else
            {
                sb.Append(",\n");
                if (kname.Length>6 && kname.Substring(0, 6).ToUpper() == "UNIQUE")
                {
                    kname = kname.Substring(7);
                }
                sb.Append(" KEY " + kname + " (" + dtKeys.Rows[i]["Column_name"] + ")");
            }
        }
        sb.Append(",\n PRIMARY KEY (" + PRIMARY + ") ");
        sb.Append("\n);\n\n");
        dtKeys.Dispose();

        DataTable dtRows = RunTable("SELECT * FROM " + tblname);

        for (int i = 0, k = dtRows.Rows.Count; i < k; i++)
        {
            sb.Append("INSERT INTO " + tblname + " VALUES(");
            int fieldcounter = -1;
            firstfield = 1;
            for (int m = 0, n = dtRows.Columns.Count; m < n; m++)
            {
                if (firstfield != 1)
                    sb.Append(", ");
                else
                    firstfield = 0;
                if (dtRows.Rows[i][m] == null)
                {
                    sb.Append("NULL");
                }
                else
                {
                    sb.Append("'" + dtRows.Rows[i][m].ToString().Trim().Replace("'", "''") + "'");
                }
            }
            sb.Append(");\n");
        }
        return sb.ToString();
    }

    private void SavetoFile(string info,string filepath)
    {
        FileStream stream = new FileStream(filepath, FileMode.Create, FileAccess.Write, FileShare.Delete | FileShare.ReadWrite);
        StreamWriter writer = new StreamWriter(stream);
        writer.WriteLine(info);
        writer.Close();
        stream.Close();
        stream.Dispose();
        writer.Dispose();
    }
    private void ExportDown(string info)
    {
        string filename = Request.ServerVariables["HTTP_HOST"] + "MySQL.sql";
        Response.ContentType = "application/unknown";
        Response.AddHeader("Content-Disposition", "attachment;filename=" + filename);
        Response.Write(info);
        Response.End();
    }

    protected void btnExport_ServerClick(object sender, EventArgs e)
    {
        string tables = string.Empty;
        StringBuilder infosb = new StringBuilder();
        if(Request.Form["tables"]!=null)
        {
            tables = Request.Form["tables"].ToString().Trim();
            string[] tableArr = tables.Split(',');
            for (int i = 0, k = tableArr.Length; i < k; i++)
            {
                if (tableArr[i].Trim() != string.Empty)
                {
                    infosb.Append(sqldumptable(tableArr[i].Trim()) + "\n\n\n\n\n\n");
                }
            }
            if (cbSaveFile.Checked)
            {
                SavetoFile(infosb.ToString(), txtSavePath.Value.Trim());
                Session["exportinfo"] = "<a href=\"" + txtSavePath.Value.Replace(Server.MapPath("now27347234.txt").Replace("now27347234.txt", ""), "") + "\" target=\"_blank\">" + txtSavePath.Value + "</a>";
                Response.Redirect(Request.ServerVariables["Script_Name"] + "?action=exportsucc", true);
            }
            else
            {
                ExportDown(infosb.ToString());
            }
        }
    }
</script>

<script language="javascript">

function SubmitKeyClick(button)
{   
   if (event.keyCode == 13)
   {       
      event.keyCode=9;
      event.returnValue = false;
      document.getElementById("btnLogin").click();
   }
}
function CheckAll(form) {
 for(var i=0;i<form.elements.length;i++) {
  var e = form.elements[i];
  if (e.name != 'chkall'&&e.name=="tables")
  e.checked = form.chkall.checked;
    }
}
</script>

</head>
<body>
    <form id="form1" runat="server">
        <div>
            <asp:Panel ID="PanelLogin" runat="server" Visible="false"  DefaultButton="btnLogin">
             <h2>
                    MYSQL Manager (DoNet) &raquo;</h2>
                <span style="font: 11px Verdana;">密码: </span>
                <input name="password" type="password" size="20" id="txtpassword" runat="server">&nbsp;
                <asp:Button ID="btnLogin" runat="server" Text="登录" OnClick="btnLogin_Click" />&nbsp;
            </asp:Panel>
             <asp:Panel ID="PanelSucc" runat="server" Visible="false">
            <asp:Panel ID="PanFrm" runat="server" Visible="false">
                <h2>
                    MYSQL Manager (DoNet) &raquo;</h2>
                <input id="action" type="hidden" name="action" value="sqladmin" runat="server" />
                <p>
                    主机IP:
                    <input class="input" name="dbhost" id="dbhost" value="localhost" type="text" size="20"
                        runat="server" />
                    :
                    <input class="input" name="dbport" id="dbport" value="3306" type="text" size="4"
                        runat="server" />
                    用户名:
                    <input class="input" name="dbuser" id="dbuser" value="root" type="text" size="15"
                        runat="server" />
                    密码:
                    <input class="input" name="dbpass" id="dbpass" type="text" size="15" runat="server" />
                    <span style="display:none">
                    数据库名:
                    <input class="input" name="dbname" id="dbname" type="text" size="15" runat="server" />
                    </span>
                    数据库编码:
                    <select class="input" id="charset" name="charset" runat="server" >
                        <option value="" selected>Default</option>
                        <option value="gbk">GBK</option>
                        <option value="big5">Big5</option>
                        <option value="utf8">UTF-8</option>
                        <option value="latin1">Latin1</option>
                    </select>
                    <input class="bt" name="connect" id="connect" value=" 连 接 " type="submit" size="100"
                        onserverclick="connect_ServerClick" runat="server" />
                </p>
                <p>