using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;

namespace web
{
	/// <summary>
	/// WebForm1 的摘要說明。
	/// </summary>
	public class WebForm1 : System.Web.UI.Page
	{
		protected System.Web.UI.WebControls.Label Label1;
		protected System.Web.UI.WebControls.TextBox txt_studentID;
		protected System.Web.UI.WebControls.Label Label2;
		protected System.Web.UI.WebControls.TextBox txt_studentName;
		protected System.Web.UI.WebControls.Label Label3;
		protected System.Web.UI.WebControls.Label Label4;
		protected System.Web.UI.WebControls.TextBox txt_studentAge;
		protected System.Web.UI.WebControls.DropDownList ddl_sex;
		protected System.Web.UI.WebControls.Panel pnl_Add;
		protected System.Web.UI.WebControls.Panel pnl_Sel;
		protected System.Web.UI.WebControls.Button btn_ok;
		protected System.Web.UI.WebControls.DataGrid dg_addStu;
	
		private void Page_Load(object sender, System.EventArgs e)
		{
			// 在此處放置用戶代碼以初始化頁面
			if (!IsPostBack)
			{
				pnl_Sel.Visible = false;
			}
		}

		#region Web 窗體設(shè)計器生成的代碼
		override protected void OnInit(EventArgs e)
		{
			//
			// CODEGEN: 該調(diào)用是 ASP.NET Web 窗體設(shè)計器所必需的。
			//
			InitializeComponent();
			base.OnInit(e);
		}
		
		/// <summary>
		/// 設(shè)計器支持所需的方法 - 不要使用代碼編輯器修改
		/// 此方法的內(nèi)容。
		/// </summary>
		private void InitializeComponent()
		{    
			this.btn_ok.Click += new System.EventHandler(this.btn_ok_Click);
			this.dg_addStu.CancelCommand += new System.Web.UI.WebControls.DataGridCommandEventHandler(this.dg_addStu_CancelCommand);
			this.dg_addStu.EditCommand += new System.Web.UI.WebControls.DataGridCommandEventHandler(this.dg_addStu_EditCommand);
			this.dg_addStu.UpdateCommand += new System.Web.UI.WebControls.DataGridCommandEventHandler(this.dg_addStu_UpdateCommand);
			this.dg_addStu.DeleteCommand += new System.Web.UI.WebControls.DataGridCommandEventHandler(this.dg_addStu_DeleteCommand);
			this.Load += new System.EventHandler(this.Page_Load);

		}
		#endregion

		private void btn_ok_Click(object sender, System.EventArgs e)
		{
			string id = txt_studentID.Text;
			string name = txt_studentName.Text;
			string sex = ddl_sex.SelectedValue;
			int age = Convert.ToInt32(txt_studentAge.Text);

			if (insertStu(id, name, sex, age))
			{
				pnl_Add.Visible = false;
				pnl_Sel.Visible = true;

				SqlConnection conn = new SqlConnection("User ID=sa;pwd=5111863;Initial Catalog=data;server=.");
				conn.Open();

				SqlDataAdapter sda = new SqlDataAdapter();
				sda.SelectCommand = new SqlCommand("select * from student", conn);
				DataSet ds = new DataSet();
				sda.Fill(ds, "addStudent");
				
				this.dg_addStu.DataKeyField = "sid";
				
				dg_addStu.DataSource = ds.Tables["addStudent"];
				dg_addStu.DataBind();
			}
		}

		private bool insertStu(string stuID, string stuName, string stuSex, int stuAge)
		{
			try
			{
				SqlConnection conn = new SqlConnection("User ID=sa;pwd=5111863;Initial Catalog=data;server=.");
				conn.Open();

				SqlCommand cmd = new SqlCommand("insert into student values(@sid, @sname, @ssex, @sage, @sgrade)", conn);

				SqlParameter para = new SqlParameter("@sid", SqlDbType.Char, 10);
				para.Value = stuID;
				cmd.Parameters.Add(para);

				para = new SqlParameter("@sname", SqlDbType.Char, 20);
				para.Value = stuName;
				cmd.Parameters.Add(para);

				para = new SqlParameter("@ssex", SqlDbType.Char, 2);
				para.Value = stuSex;
				cmd.Parameters.Add(para);

				para = new SqlParameter("@sage", SqlDbType.TinyInt, 1);
				para.Value = stuAge;
				cmd.Parameters.Add(para);

				para = new SqlParameter("@sgrade", SqlDbType.Int, 4);
				para.Value = 0;
				cmd.Parameters.Add(para);

				cmd.ExecuteNonQuery();
				return true;
			}
			catch (Exception e)
			{
				return false;
			}
		}

		private void dg_addStu_DeleteCommand(object source, System.Web.UI.WebControls.DataGridCommandEventArgs e)
		{
			string id = this.dg_addStu.DataKeys[e.Item.ItemIndex].ToString(); //獲取當(dāng)前行的主鍵
			
			SqlConnection conn = new SqlConnection("User ID=sa;pwd=5111863;Initial Catalog=data;server=.");
			conn.Open();

			SqlCommand cmd = new SqlCommand("delete from student where sid = '"+id+"'", conn);
			
			cmd.ExecuteNonQuery();
			this.BindToData();
		}

		private void BindToData()
		{
			SqlConnection conn = new SqlConnection("User ID=sa;pwd=5111863;Initial Catalog=data;server=.");
			conn.Open();

			SqlDataAdapter sda = new SqlDataAdapter();
			sda.SelectCommand = new SqlCommand("select * from student", conn);
			DataSet ds = new DataSet();
			sda.Fill(ds, "newStudent");

			dg_addStu.DataSource = ds.Tables["newStudent"];
			dg_addStu.DataBind();
		}

		private void dg_addStu_EditCommand(object source, System.Web.UI.WebControls.DataGridCommandEventArgs e)
		{
			this.dg_addStu.EditItemIndex = e.Item.ItemIndex;
			this.BindToData();
		}

		private void dg_addStu_UpdateCommand(object source, System.Web.UI.WebControls.DataGridCommandEventArgs e)
		{
			string id = this.dg_addStu.DataKeys[e.Item.ItemIndex].ToString(); //獲取當(dāng)前行的主鍵
			int grade = Convert.ToInt32(((TextBox)(e.Item.Cells[4].Controls[0])).Text);
			
			SqlConnection conn = new SqlConnection("User ID=sa;pwd=5111863;Initial Catalog=data;server=.");
			conn.Open();

			SqlCommand cmd = new SqlCommand("update student set sgrade = '"+grade+"' where sid = '"+id+"'", conn);
			cmd.ExecuteNonQuery();
			
			this.BindToData();
		}

		private void dg_addStu_CancelCommand(object source, System.Web.UI.WebControls.DataGridCommandEventArgs e)
		{
			this.dg_addStu.EditItemIndex = -1;
			this.BindToData();
		}

	
	}
}