default2.aspx.cs

将sql数据库中所有的表读出

//using System;
//using System.Data;
//using System.Configuration;
using System.Collections;
//using System.Web;
//using System.Web.Security;
//using System.Web.UI;
//using System.Web.UI.WebControls;
//using System.Web.UI.WebControls.WebParts;
//using System.Web.UI.HtmlControls;using system.configuration;

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.Xml;
//using system.data.sqlclient;
public class Class2
{
    private string connecString;
    private SqlConnection connection;

    public Class2()
    {

        connecString = "server=localhost;database=master;uid=sa1;pwd=223512";
        connection = new SqlConnection(connecString);

    }


    public void WebMessageBox(string values)
    {

        HttpContext.Current.Response.Write("<script>alert('" + values + "');window.location.href='Default2.aspx'</script>");

        HttpContext.Current.Response.End();
    }



}
public partial class Default2 : System.Web.UI.Page
{
   
//static CacheItemRemovedReason reason;

    
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)//你可以去掉这个判断看下效果，对你有帮助的
        {
          
        }

    }
    private void Loaddata()
    {
        DataView source;
        string fileName = FileUpload1.FileName;
        string tableName = fileName.Substring(0, fileName.Length - 4);
        //if (Cache["tempdata"] == null)
        //{
        string path = @"app_data/" + tableName + ".xml";
            DataSet ds = new DataSet();
            string filepath = Server.MapPath(path );
            ds.ReadXml(filepath);
            source = new DataView(ds.Tables[0]);
           // Cachedependencydep = newcachedependency(filepath, datetime.now);
            //Cache.Insert("tempdata",source,dep,datetime.maxvalue,TimeSpan.Zero,cacheitempriority.default, new cacheitemremovedcallback(cachechanged) );
            GridView1.DataSource=source;
            GridView1.DataBind();
            //string path = @"app_data/" + tableName + ".xml";
            //DataSet ds = new DataSet();
           // string filepath = Server.MapPath(path);
            //ds.ReadXml(filepath);
            //string path1= @"C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\master.mdf";
            //string filepath1 = Server.MapPath(path1);
            DataTable tbl;
            tbl = new DataTable();
            tbl = ds.Tables[0];
            ExportTosql(tbl);
        //}
    }
    protected void Button1_Click(object sender, EventArgs e)
    {
        string fileName = FileUpload1.FileName;
        if (fileName.Length == 0)
            new Class2().WebMessageBox("请选择要导入的xml文件！");
        else
        Loaddata();
    }
    //protected void baocun()
    //{
    //    //string path = @"app_data/" + tableName + ".xml";
    //    //DataView source;
    //    //string fileName = FileUpload1.FileName;
    //    //if (fileName.Length == 0)
    //       // new Class2().WebMessageBox("请选择要导入的xml文件！");
       
    //    //string tableName = fileName.Substring(0, fileName.Length - 4);
    //    string path = @"app_data/" + tableName + ".xml";
    //    DataSet ds = new DataSet();
    //    string filepath = Server.MapPath(path);
    //    ds.ReadXml(filepath);
    //    //string path1= @"C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\master.mdf";
    //    //string filepath1 = Server.MapPath(path1);
    //    DataTable tbl;
    //    tbl = new DataTable();
    //    tbl = ds.Tables[0];
    //    ExportTosql(tbl);
    //}
    public void ExportTosql(DataTable dt)
    {
        string fileName = FileUpload1.FileName;
        string tableName = fileName.Substring(0, fileName.Length - 4);
        //string tname = dt.TableName.Trim();   //检测表名   
        //string   connstr="PROVIDER=Microsoft.Jet.OLEDB.4.0;Data   Sourth="+MapPath("./data/database.mdb")+";uid=sa1;pwd=223512;Jet   OLEDB:Max   Buffer   Size=256;";   
        //string tname = DateTime.Today.Date.ToString("XyyyyMMdd");
        string tname = tableName;
        //if (tname == "Table") tname = DateTime.Today.Date.ToString("XyyyyMMdd");
        //string cmdstr, connstr = "Provider=Microsoft.Jet.OLEDB.4.0;   Data Source=" + FilePath + "; uid=sa1; pwd=223512;  Jet   OLEDB:Max   Buffer   Size=256;Extended   Properties=Microsoft SQL Server";   
        string cmdstr;
      
        string connstr = "server=127.0.0.1;database=zb;uid=sa1;pwd=223512";
        //Provider=SQLOLEDB;Initial   Catalog=master;Data   Source=Huan_jinwu;Persist   Security   Info=False;User   ID=sa;password=123   
        //string cmdstr, connstr = "Provider=SQLOLEDB;server=127.0.0.1; Data Source=" + FilePath + ";uid=sa1;pwd=223512";
        SqlConnection  conn = new SqlConnection(connstr);
        conn.Open();
        //MessageBox.Show(ex.Message, "数据库连接成功");
        //response.write("connection   succeeded!");
        SqlCommand cmd = new SqlCommand("", conn);
        
        try   //如果有同名表则删除   
        {
            cmd.CommandText = "DROP   TABLE   " + tname;
            cmd.ExecuteNonQuery();
        }
        catch
        {
        }
        cmdstr = "CREATE   TABLE   " + tname + " (";
        foreach (DataColumn dc in dt.Columns)
            cmdstr += "[" + dc.ColumnName + "]"+" nvarchar(100),";

        cmdstr = cmdstr.Substring(0, cmdstr.Length - 1) + ")";   
        cmd.CommandText = cmdstr;
        cmd.ExecuteNonQuery();
        cmdstr = "";
        foreach (DataRow dr in dt.Rows)
        {
            cmdstr = "INSERT   INTO   " + tname + "   (";
            foreach (DataColumn dc in dt.Columns)
                cmdstr += "[" + dc.ColumnName + "],";
            cmdstr = cmdstr.Substring(0, cmdstr.Length - 1) + ")   VALUES   (";
            foreach (DataColumn dc in dt.Columns)
                cmdstr += "'" + dr[dc.ColumnName].ToString().Replace("'", "''") + "',";
            cmdstr = cmdstr.Substring(0, cmdstr.Length - 1) + ")";
            cmd.CommandText = cmdstr;
            cmd.ExecuteNonQuery();   

        }
       // response.write("connection   succeeded!");  
        cmd.Dispose();
        conn.Close();
    }
    protected void GridView1_SelectedIndexChanged(object sender, EventArgs e)
    {

    }

    protected void Button3_Click(object sender, EventArgs e)
    {
        string fileName = FileUpload1.FileName;
        if (fileName.Length == 0)
            new Class2().WebMessageBox("请选择要导入的xml文件！");
        else
            FileUpload1.SaveAs(Server.MapPath("App_Data/") + "web.xml");
        //tableName = fileName.Substring(0, fileName.Length - 4);
        string tableName = fileName.Substring(0, fileName.Length - 4);
    }
}