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

namespace UsingServerControls
{
	/// <summary>
	/// Summary description for DataListFeatures.
	/// </summary>
	public class DataListFeatures : System.Web.UI.Page
	{
		protected System.Web.UI.WebControls.DataList BookList;
	
		private void Page_Load(object sender, System.EventArgs e)
		{
			// Put user code to initialize the page here
			if (!Page.IsPostBack)
				{
				BindDataList();
				}
		}

		#region Web Form Designer generated code
		override protected void OnInit(EventArgs e)
		{
			//
			// CODEGEN: This call is required by the ASP.NET Web Form Designer.
			//
			InitializeComponent();
			base.OnInit(e);
		}
		
		/// <summary>
		/// Required method for Designer support - do not modify
		/// the contents of this method with the code editor.
		/// </summary>
		private void InitializeComponent()
		{    
			this.BookList.ItemCommand += new System.Web.UI.WebControls.DataListCommandEventHandler(this.DoItemSelect);
			this.BookList.CancelCommand += new System.Web.UI.WebControls.DataListCommandEventHandler(this.DoItemCancel);
			this.BookList.EditCommand += new System.Web.UI.WebControls.DataListCommandEventHandler(this.DoItemEdit);
			this.BookList.UpdateCommand += new System.Web.UI.WebControls.DataListCommandEventHandler(this.DoItemUpdate);
			this.BookList.DeleteCommand += new System.Web.UI.WebControls.DataListCommandEventHandler(this.DoItemDelete);
			this.Load += new System.EventHandler(this.Page_Load);

		}
		#endregion

		private void BindDataList()
			{
			SqlConnection myConnection = new SqlConnection("server=localhost;Trusted_Connection=true;database=NorthWind");
			string selectionString = "Select Title, Price,TitleID from Books";
			SqlCommand myCommand = new SqlCommand(selectionString, myConnection);
			myConnection.Open();
			SqlDataReader reader = myCommand.ExecuteReader(CommandBehavior.CloseConnection);

			this.BookList.DataSource = reader;
			this.BookList.DataMember = "Books";
			this.BookList.DataKeyField = "TitleID";
			this.BookList.DataBind();

			reader.Close();
			}

		private void DoItemSelect(object sender, DataListCommandEventArgs e)
			{
			if ("Select" == e.CommandName)
				{
				this.BookList.SelectedIndex = e.Item.ItemIndex;
				BindDataList();
				}
			}

		private void DoItemEdit(object sender, DataListCommandEventArgs e)
			{
			this.BookList.SelectedIndex = -1;
			this.BookList.EditItemIndex = e.Item.ItemIndex;
			BindDataList();
			}

		private void DoItemUpdate(object sender, DataListCommandEventArgs e)
			{
			TextBox titleBox = e.Item.FindControl("txtTitle") as TextBox;
			TextBox priceBox = e.Item.FindControl("txtPrice") as TextBox;
			HtmlInputHidden idLabel = e.Item.FindControl("txtID") as HtmlInputHidden;
			string updateString = "Update Books Set Title='" + titleBox.Text + "'," + "Price='" + priceBox.Text + "' " + "where TitleID='" + idLabel.Value +"'";
			ExecuteSqlStatement(updateString);

			// pull the item out of the edit state
			this.BookList.EditItemIndex = -1;
			BindDataList();
			}

		private void DoItemDelete(object sender, DataListCommandEventArgs e)
			{
			HtmlInputHidden idLabel = e.Item.FindControl("txtID") as HtmlInputHidden;
			string deleteString = "Delete from Books where TitleID='" + idLabel.Value +"'";
			ExecuteSqlStatement(deleteString);

			this.BookList.EditItemIndex = -1;
			BindDataList();
			}

		private void DoItemCancel(object sender, DataListCommandEventArgs e)
			{
			this.BookList.EditItemIndex = -1;
			BindDataList();
			}

		private void ExecuteSqlStatement(string sqlString)
			{
			SqlConnection myConnection = new SqlConnection("server=localhost;Trusted_Connection=true;database=NorthWind");
			string selectionString = sqlString;
			SqlCommand myCommand = new SqlCommand(selectionString, myConnection);
			myConnection.Open();
			myCommand.ExecuteNonQuery();
			myConnection.Close();
			}
	}
}
