?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;

public partial class _Default : System.Web.UI.Page
{
    static int m;
    static int n;
    protected void Page_Load(object sender, EventArgs e)
    {

    }
    protected void Button1_Click(object sender, EventArgs e)
    {
        Label2.Text = null;
        Label1.Text = null;
        TextBox3.Text = "列名        數據類型" + "\r";
        try
        {
            sqlconntection connection = new sqlconntection();
            connection.con = @"Data Source=WWW-59CE1982991;Initial Catalog=Northwind;Integrated Security=True";
            connection.com = TextBox1.Text;
            GridView1.DataSource = connection.ds(connection.com, connection.com);
            GridView1.DataBind();
            //        char[] chars1 ={ ' ' };
            //string select2= TextBox1.Text;
            //string[] selectarry2 = select2.Split(chars1);
            for (int i = 0; i < connection.ds(connection.con, connection.com).Tables[0].Columns.Count; i++)
            {
                TextBox3.Text += connection.ds(connection.con, connection.com).Tables[0].Columns[i].ColumnName + "      " + connection.ds(connection.con, connection.com).Tables[0].Columns[i].DataType + "\r";
            }
        }
        catch (Exception E)
        {
            Label1.Text = @"查詢語句有誤，請重新輸入";
        }
    }


    protected void Button2_Click(object sender, EventArgs e)
    {
        Label2.Text = "";
        string[] strings ={ " ", "  ", "   ", "    ", "     ", "(", ")", "     ", "," };
        string select1 = TextBox1.Text;
        string[] selectarry1 = select1.Split(strings, StringSplitOptions.RemoveEmptyEntries);
        //sqlconntection connection = new sqlconntection();
        //string a1 = @"Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=petshop;Data Source=88EADCE47451494\SQLEXPRESS";
        //string a2 = TextBox1.Text;
        //SqlConnection conne = new SqlConnection(a1);
        //SqlCommand command = new SqlCommand(a2, conne);
        //SqlDataAdapter mydataadapter = new SqlDataAdapter(command);
        //SqlCommandBuilder mybuilder = new SqlCommandBuilder(mydataadapter);
        //DataSet ds = new DataSet();
        //mydataadapter.Fill(ds);

        sqlconntection connection = new sqlconntection();
        connection.con = @"Data Source=WWW-59CE1982991;Initial Catalog=Northwind;Integrated Security=True";
        connection.com = TextBox1.Text;
        GridView1.DataSource = connection.ds(connection.com, connection.com);
        GridView1.DataBind();

        //connection.ds(connection.con,connection.com).Tables[0].TableName=selectarry1[2];
        //DataSet dt=connection.ds(connection.con,connection.com);
        //GridView1.DataBind();

        string select = TextBox2.Text;
        string[] selectarry = select.Split(strings, StringSplitOptions.RemoveEmptyEntries);
        //foreach (string s in selectarry)
        //{
        //    TextBox3.Text += s + "\r";
        //}

        if (selectarry[0] != "insert" || selectarry[1] != "into" || selectarry[3] != "values")
        {
            Label2.Text = "請輸入正確的插入語句語句！例如：insert into TableName values()";
        }
        if (selectarry[2] != selectarry1[3])
        {
            Label2.Text = "請輸入正確的表名";
        }
        if (selectarry.Length != connection.ds(connection.con, connection.com).Tables[0].Columns.Count + 4)
        {
            Label2.Text = "請輸入正確的插入數據！";

        }
        bool loop = true;
        for (int i = 0; i < connection.ds(connection.con, connection.com).Tables[0].Rows.Count; i++)
        {
            if (connection.ds(connection.con, connection.com).Tables[0].Rows[i][0].ToString() == selectarry[4])
            {
                loop = false;
                Label2.Text = "主鍵違反完整性約束規則";
            }

        }
        if (selectarry[2] == selectarry1[3] && selectarry[0] == "insert" && selectarry[1] == "into" && selectarry[3] == "values" && selectarry.Length == connection.ds(connection.con, connection.com).Tables[0].Columns.Count + 4 && loop == true)
        {
            try
            { //sqlconntection connection = new sqlconntection();
                string a1 = @"Data Source=WWW-59CE1982991;Initial Catalog=Northwind;Integrated Security=True";
                string a2 = TextBox1.Text;
                SqlConnection conne = new SqlConnection(a1);
                conne.Open();
                SqlCommand command = new SqlCommand(a2, conne);
                SqlDataAdapter mydataadapter = new SqlDataAdapter(command);
                SqlCommandBuilder mybuilder = new SqlCommandBuilder(mydataadapter);
                DataSet ds = new DataSet();
                mydataadapter.Fill(ds);
                DataRow myrow = ds.Tables[0].NewRow();
                for (int i = 0; i < selectarry.Length - 4; i++)
                {
                    myrow[i] = selectarry[i + 4];
                }
                ds.Tables[0].Rows.Add(myrow);
                mydataadapter.Update(ds);
                GridView2.DataSource = ds;
                GridView2.DataBind();
                Response.Write("<script>alert('添加成功')</script>");
            }
            catch (Exception e2)
            {
                Label2.Text = "請參照數據類型錄入正確的數據！";

            }

        }

        // if(
        //bool loop1 = false;
        // if (selectarry[1] == "*"||selectarry[selectarry.Length-1]==connection.ds(connection.con,connection.com).Tables[0].TableName)
        // {GridView2.DataSource = connection.ds(connection.con,connection.com).Tables[0];
        //     GridView2.DataBind();

        // }

        // for (int i = 0; i < dt.Tables[0].Columns.Count; i++)
        // {
        //     string[] lieming = new string[dt.Tables[0].Columns.Count];
        //     lieming[i] = dt.Tables[0].Columns[i].ColumnName;
        //     if (selectarry[1] == lieming[i])
        //         loop1 = true;

        // }
        // if (loop1 ==false)
        // {
        //     Label2.Text = @"請輸入正確的列名！"; 
        // }
        // if (loop1 == true || selectarry[selectarry.Length - 1] == connection.ds(connection.con, connection.com).Tables[0].TableName || selectarry1[3] == "from")
        // {
        //     DataColumn co1 = new DataColumn();
        //     DataSet ds1 = new DataSet();
        //     DataTable dt1 = new DataTable();
        //     dt1.Columns.Add(selectarry[1]);
        //     ds1.Tables.Add(dt1);
        //     GridView2.DataSource = ds1;
        //     GridView2.DataBind();
        // }
    }
    protected void Button3_Click(object sender, EventArgs e)
    {
        Label2.Text = "";
        string[] strings ={ " ", "  ", "   ", "    ", "     ", "(", ")", "     ", ",", "=","= "," =" };
        string select1 = TextBox1.Text;
        string[] selectarry1 = select1.Split(strings, StringSplitOptions.RemoveEmptyEntries);
        string select2 = TextBox4.Text;
        string[] selectarry2 = select2.Split(strings, StringSplitOptions.RemoveEmptyEntries);
        //foreach (string s in selectarry2)
        //{
        //    TextBox3.Text += s + '\r';
        //}
        sqlconntection connection = new sqlconntection();
        connection.con = @"Data Source=WWW-59CE1982991;Initial Catalog=Northwind;Integrated Security=True";
        connection.com = TextBox1.Text;
        GridView1.DataSource = connection.ds(connection.com, connection.com);
        GridView1.DataBind();

        if (selectarry2[0] != "update" || selectarry2[2] != "set" || selectarry2[5] != "where")
        {
            Label3.Text = "請錄入正確的update語句！";
        }
        if (selectarry2[1] != selectarry1[3])
        {
            Label3.Text = "請輸入正確的表名";
        }
        bool loop1 = false;
        bool loop3 = false;
        if (selectarry2[6] ==connection.ds(connection.con,connection.com).Tables[0].Columns[0].ColumnName)
        {
            loop3 = true;
        }
        else Label3.Text = "請輸入正確的列名";
        for (int i = 0; i < connection.ds(connection.con, connection.com).Tables[0].Columns.Count; i++)
        {
            if (connection.ds(connection.con, connection.com).Tables[0].Columns[i].ColumnName != selectarry2[3] )
            {
                loop1 = true;
               
            }
            
        }
        bool loop2 = true ;
        for (int i = 0; i < connection.ds(connection.con, connection.com).Tables[0].Rows.Count; i++)
        {
            if (connection.ds(connection.con, connection.com).Tables[0].Rows[i][0].ToString() == selectarry2[7])
            {
                loop2 = true;
            }
            //else
            //{
            //    Label3.Text = "請輸入正確的查詢條件！";
            //}

        }
        if (selectarry2[0] == "update" && selectarry2[2] == "set" && selectarry2[5] == "where" && selectarry2[1] == selectarry1[3]&& loop1 == true && loop2 == true && loop3 == true)
        {
            try
            { //sqlconntection connection = new sqlconntection();
                string a11 = @"Data Source=WWW-59CE1982991;Initial Catalog=Northwind;Integrated Security=True";
                string a21 = TextBox1.Text;
                SqlConnection conne1 = new SqlConnection(a11);
                conne1.Open();
                SqlCommand command1 = new SqlCommand(a21, conne1);
                SqlDataAdapter mydataadapter1 = new SqlDataAdapter(command1);
                
                DataSet ds1 = new DataSet();
                mydataadapter1.Fill(ds1);
                SqlCommandBuilder mybuilder1 = new SqlCommandBuilder(mydataadapter1);
                for (int i = 0; i < connection.ds(connection.con, connection.com).Tables[0].Rows.Count; i++)
                { if (selectarry2[7] == connection.ds(connection.con, connection.com).Tables[0].Rows[i][0].ToString())m = i; }
                for (int j = 0; j < connection.ds(connection.con, connection.com).Tables[0].Columns.Count; j++)
                {
                    if (selectarry2[3] == connection.ds(connection.con, connection.com).Tables[0].Columns[j].ColumnName)
                        n = j;
                }
                ds1.Tables[0].Rows[m][n] = selectarry2[4];
                mydataadapter1.Update(ds1);
                GridView3.DataSource = ds1;
                GridView3.DataBind();
                Response.Write("<script>alert('修改成功')</script>");
            }
            catch (Exception e2)
            {
                Label2.Text = e2.Message ;

            }




        }
    }
}