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.SqlClient;
using System.Configuration;

namespace Website
{
	public partial class Default : System.Web.UI.Page
	{
		SqlConnection conNorthwind	= new SqlConnection();
		SqlCommand comKlanten		= new SqlCommand();
		SqlCommand comKlantIDs		= new SqlCommand();
		SqlCommand comOrders		= new SqlCommand();
		SqlDataAdapter daKlanten	= new SqlDataAdapter();
		
		DataSet dsKlanten			= new DataSet();
		DataTable dtKlanten			= new DataTable("klanten");
		DataTable dtOrders			= new DataTable("orders");
		SqlDataReader drKlantIDs;

		string klantID;



		protected void Page_Load(object sender, EventArgs e)
		{
			conNorthwind.ConnectionString = ConfigurationManager.ConnectionStrings["NorthwindConnectionString"].ConnectionString;
			comKlanten.Connection	= conNorthwind;
			comKlantIDs.Connection	= conNorthwind;
			comOrders.Connection	= conNorthwind;

			comKlanten.CommandText	= "SELECT * FROM customers";
			comKlantIDs.CommandText	= "SELECT customerid FROM customers";

			daKlanten.SelectCommand = comKlanten;
			dsKlanten.Tables.Add(dtKlanten);
			dsKlanten.Tables.Add(dtOrders);

			daKlanten.Fill(dsKlanten.Tables["klanten"]);
			gvKlanten.DataSource = dsKlanten.Tables["klanten"];
			gvKlanten.DataBind();

			conNorthwind.Open();
			drKlantIDs = comKlantIDs.ExecuteReader();

			//ddlKlantIDs.DataSource		= drKlantIDs;
			//ddlKlantIDs.DataTextField		= "CustomerID";
			//dllKlantIDs.DataValueField	= "CustomerID";

			while(drKlantIDs.Read())
			{
				ddlKlantIDs.Items.Add(drKlantIDs.GetValue(0).ToString());
			}

			ddlKlantIDs.DataBind();
			conNorthwind.Close();
		}


		protected void btnZoeken_Click(object sender, EventArgs e)
		{
			klantID = ddlKlantIDs.Text;

			comOrders.CommandText = @"SELECT
										Products.ProductID,
										Products.ProductName,
										[Order Details].Quantity,
										CAST([Order Details].UnitPrice as DECIMAL(5,2) as UnitPrice,
										CAST([Order Details].Quantity*[Order Details].UnitPrice as DECIMAL(10, 2)) as [Row Total]
									FROM	Customers.Orders, [Order Details], Products
									WHERE	Customers.CustomerID = Orders.CustomerID
									AND		Orders.OrderID = [Order Details].OrderID
									AND		Products.ProductID = [Order Details].ProductID
									AND		Customers.CustomerID = '" + klantID + "'";

			daKlanten.SelectCommand = comOrders;
			daKlanten.Fill(dsKlanten.Tables["orders"]);
			gvKlanten.DataSource = dsKlanten.Tables["orders"];
			gvKlanten.DataBind();
		}
	}
}