?using System;
using System.Linq;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlServerCe;
using System.IO;
using System.Reflection;
using Demo4___LinqToResultSet.NorthwindDataSetTableAdapters;

namespace Demo4___LinqToResultSet
{
	public partial class FrmMain : Form
	{
		SqlCeConnection northwindConnection = null;

		const string _northwindDatabaseFile = "Northwind.sdf";
		string NorthwindDatabaseFullPath
		{
			get { return Path.Combine(ApplicationFolder, _northwindDatabaseFile); }
		}

		string ApplicationFolder
		{
			get { return Path.GetDirectoryName(Assembly.GetExecutingAssembly().GetName().CodeBase); }
		}

		public FrmMain()
		{
			InitializeComponent();
		}

		private void FrmMain_Load(object sender, EventArgs e)
		{
			northwindConnection = new SqlCeConnection("Data Source = " + NorthwindDatabaseFullPath);
			northwindConnection.Open();
		}

		private void FrmMain_Closing(object sender, CancelEventArgs e)
		{
			if (northwindConnection != null)
				northwindConnection.Dispose();
		}

		#region DataSet Helpers

		NorthwindDataSet northwindDataSet;

		NorthwindDataSet CreateAndPopulateNorthwindDataSet(SqlCeConnection connection)
		{
			NorthwindDataSet dataSet = new NorthwindDataSet();

			// Customers Table
			DisposeItem(delegate
			{
				CustomersTableAdapter tableAdapter = new CustomersTableAdapter();
				tableAdapter.Connection = connection;
				tableAdapter.Fill(dataSet.Customers);
				return tableAdapter;
			});

			// Suppliers Table
			DisposeItem(delegate
			{
				SuppliersTableAdapter tableAdapter = new SuppliersTableAdapter();
				tableAdapter.Connection = connection;
				tableAdapter.Fill(dataSet.Suppliers);
				return tableAdapter;
			});

			// Employees Table
			DisposeItem(delegate
			{
				EmployeesTableAdapter tableAdapter = new EmployeesTableAdapter();
				tableAdapter.Connection = connection;
				tableAdapter.Fill(dataSet.Employees);
				return tableAdapter;
			});

			// Shippers Table
			DisposeItem(delegate
			{
				ShippersTableAdapter tableAdapter = new ShippersTableAdapter();
				tableAdapter.Connection = connection;
				tableAdapter.Fill(dataSet.Shippers);
				return tableAdapter;
			});

			// Categories Table
			DisposeItem(delegate
			{
				CategoriesTableAdapter tableAdapter = new CategoriesTableAdapter();
				tableAdapter.Connection = connection;
				tableAdapter.Fill(dataSet.Categories);
				return tableAdapter;
			});

			// Products Table
			DisposeItem(delegate
			{
				ProductsTableAdapter tableAdapter = new ProductsTableAdapter();
				tableAdapter.Connection = connection;
				tableAdapter.Fill(dataSet.Products);
				return tableAdapter;
			});

			// Orders Table
			DisposeItem(delegate
			{
				OrdersTableAdapter tableAdapter = new OrdersTableAdapter();
				tableAdapter.Connection = connection;
				tableAdapter.Fill(dataSet.Orders);
				return tableAdapter;
			});

			// Order Details Table
			DisposeItem(delegate
			{
			    Order_DetailsTableAdapter tableAdapter = new Order_DetailsTableAdapter();
			    tableAdapter.Connection = connection;
			    tableAdapter.Fill(dataSet.Order_Details);
			    return tableAdapter;
			});

			return dataSet;
		}

		delegate IDisposable GetDisposableItem();
		void DisposeItem(GetDisposableItem func)
		{
			IDisposable item = null;
			try
			{
				item = func();
			}
			finally
			{
				if (item != null)
					item.Dispose();
			}
		}

		#endregion

		#region ResultSet Helpers

		SqlCeResultSet customersResultSet;
		SqlCeResultSet employeesResultSet;
		SqlCeResultSet shippersResultSet;
		SqlCeResultSet suppliersResultSet;
		SqlCeResultSet orderDetailsResultSet;
		SqlCeResultSet ordersResultSet;
		SqlCeResultSet categoriesResultSet;
		SqlCeResultSet productsResultSet;

		void OpenResultSets()
		{
			customersResultSet = OpenResultSet("Customers");
			employeesResultSet = OpenResultSet("Employees");
			shippersResultSet = OpenResultSet("Shippers");
			suppliersResultSet = OpenResultSet("Suppliers");
			orderDetailsResultSet = OpenResultSet("Order Details");
			ordersResultSet = OpenResultSet("Orders");
			categoriesResultSet = OpenResultSet("Categories");
			productsResultSet = OpenResultSet("Products");
		}

		SqlCeResultSet OpenResultSet(string tableName)
		{
			using (SqlCeCommand command = new SqlCeCommand(tableName, northwindConnection))
			{
				command.CommandType = CommandType.TableDirect;
				return command.ExecuteResultSet(ResultSetOptions.Scrollable | ResultSetOptions.Sensitive);
			}
		}

		#endregion

		private void mniPopulateDataSet_Click(object sender, EventArgs e)
		{
			int startMilliseconds = Environment.TickCount;
			Cursor.Current = Cursors.WaitCursor;

			northwindDataSet = CreateAndPopulateNorthwindDataSet(northwindConnection);

			Cursor.Current = Cursors.Default;
			int endMilliseconds = Environment.TickCount;

			MessageBox.Show((endMilliseconds - startMilliseconds).ToString(), "DataSet");
		}

		private void mniSelectFromDataSet_Click(object sender, EventArgs e)
		{
			using (FilterDialog filterDialog = new FilterDialog())
			{
				if (filterDialog.ShowDialog() == DialogResult.OK)
				{
					var selectedRows = from order in northwindDataSet.Orders
									   where order.Ship_Country == filterDialog.SelectedCountry
									   select order;

					dataGrid1.DataSource = selectedRows.AsDataView();
				}
			}
		}

		private void mniPrepareResultSets_Click(object sender, EventArgs e)
		{
			int startMilliseconds = Environment.TickCount;
			Cursor.Current = Cursors.WaitCursor;

			OpenResultSets();

			Cursor.Current = Cursors.Default;
			int endMilliseconds = Environment.TickCount;

			MessageBox.Show((endMilliseconds - startMilliseconds).ToString(), "ResultSet");
		}

		private void mniSelectFromResultSet_Click(object sender, EventArgs e)
		{
			using (FilterDialog filterDialog = new FilterDialog())
			{
				if (filterDialog.ShowDialog() == DialogResult.OK)
				{
					var selectedRows = from SqlCeUpdatableRecord order in ordersResultSet
									   where order["Ship Country"].ToString() == filterDialog.SelectedCountry
									   select new
									   {
									       OrderId = order["Order Id"],
									       ShipName = order["Ship Name"],
									       ShipCountry = order["Ship Country"]
									   };

					dataGrid1.DataSource = selectedRows.ToList();
				}
			}
		}
	}
}