using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.OleDb;
using System.Data.Sql;
using System.Data.SqlClient;

public partial class Admin_BasicImportDB : System.Web.UI.Page
{
    string filename;
   LYS.UserData1 myud = new LYS.UserData1();
    //先要确定三个参数:表名: Session["ImportTable"] = "Student"; 两个关键字段    Session["KeyField1"] = "StudentID";       Session["KeyField2"] = "Stud_name";
    protected void Page_Load(object sender, EventArgs e)
    {
       Session["ImportTable"] = "UserS";
        Session["KeyField1"] = "用户名";
        Session["KeyField2"] = "密码";

        if (Session["ImportTable"] == null || Session["KeyField1"] == null || Session["KeyField2"] == null)
        {
            Response.Write("<script> alert('程序非法调用！！') </script>");
            Response.End();        
        }
      
        Panel2.Visible = false;
        Panel3.Visible = false;
        Panel4.Visible = false;
    }
    protected void input_Click(object sender, EventArgs e)
    {
        Panel1.Visible = false;
        Panel2.Visible = true;
        if (File1.PostedFile != null)
        {
            filename = File1.FileName;
            Session["file"] = filename;
            Session["conn"] = myud.ConnExcelString("file", Session["file"].ToString());
            File1.PostedFile.SaveAs(Server.MapPath("file/") + filename);
            //myud.GetSQLTableName(myud.ConnString(), DropDownList1); 获得获得SQL表名
            myud.GetExcelTable(Session["conn"].ToString(), DropDownList1);
        }
    }
    protected void Button1_Click(object sender, EventArgs e)
    {
        Panel1.Visible = false;
        Panel3.Visible = true;
        string Sql = "select ";
        for (int i = 0; i < CheckBoxList1.Items.Count; i++)
            if (CheckBoxList1.Items[i].Selected)
                Sql += CheckBoxList1.Items[i].Value + ",";
        Sql = Sql.Substring(0, Sql.Length - 1) + " from [" + DropDownList1.SelectedValue + "]";   //EXCEL SQL 语句
        //  Response.Write(Sql);
        Session["getsql"] = Sql;
        //Response.Write(Sql);
        Literal1.Text = myud.DispOleDbLineDataJTExcel(Sql, Session["conn"].ToString()).ToString();  //显示EXCEL数据
    }
    protected void Button2_Click(object sender, EventArgs e)
    {
        Panel4.Visible = true;
        string Sql = Session["getsql"].ToString();
        DataTable myTable = myud.getExcelds(Session["conn"].ToString(), Sql);
       // myud.ExcelToSQL(tablename, Session["conn"].ToString(), myTable);
        myud.ExcelToSQLKey(Session["ImportTable"].ToString(), Session["conn"].ToString(), myTable, Session["KeyField1"].ToString(), Session["KeyField2"].ToString(), Literal3); //不同的表需要更改两个要检查的关键字段名StudentID Stud_name
        System.IO.File.Delete(Server.MapPath("file/") + Session["file"].ToString());
        //  Response.Write("<script> alert('导入成功') </script>");
        Literal2.Text = myud.DispLineDataModi("Select Top 10 * from " + Session["ImportTable"].ToString() + " order by ID DESC", "").ToString();
    }
    
    
    protected void Button3_Click(object sender, EventArgs e)
    {
        Panel2.Visible = true;
        Button1.Visible = true;
        CheckBoxList1.Items.Clear();
        string tablename = DropDownList1.SelectedValue;
        string sql = "select * from [" + tablename + "]";
        DataTable myTable = myud.getExcelds(Session["conn"].ToString(), sql);
        for (int i = 0; i <= myTable.Columns.Count - 1; i++)
        {
            ListItem myit = new ListItem();
            myit.Text = myTable.Columns[i].Caption;
            myit.Value = myTable.Columns[i].Caption;
            CheckBoxList1.Items.Add(myit);
        }
    }
}