employeeorders.aspx.cs

基于sqlserver2k,reporting service的扩展(auth,render,deliver==)应用（mssql,RS,AS,BI）

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

namespace AWC.Reporter.Web
{
	/// <summary>
	/// Summary description for EmployeeOrders.
	/// </summary>
	public class EmployeeOrders : System.Web.UI.Page
	{
		protected System.Web.UI.WebControls.Panel Panel1;
		protected System.Web.UI.WebControls.Label lblSalesPerson;
		protected System.Web.UI.WebControls.DataGrid grdOrders;
		protected System.Web.UI.WebControls.DropDownList drpSalesPerson;
	
		private void Page_Load(object sender, System.EventArgs e)
		{
			if (!this.IsPostBack) 
			{
				LoadEmployees();
				LoadOrders();
			}
		}

		/// <summary>
		/// Load all salespersons in the dropdown
		/// </summary>
		private void LoadEmployees()
		{
			SqlConnection connection = new SqlConnection();
			SqlDataReader reader = null;
			try 
			{
				string sql = "SELECT EmployeeID, LastName + N' ' + FirstName AS Employee FROM  Employee INNER JOIN SalesPerson ON EmployeeID = SalesPersonID ORDER BY LastName, FirstName";
				connection.ConnectionString = ConfigurationSettings.AppSettings[Constants.CONFIG_CONNECT_STRING];
				SqlCommand sqlCmd = new SqlCommand(sql, connection);
				sqlCmd.Connection.Open();
				reader = sqlCmd.ExecuteReader();
				drpSalesPerson.DataSource = reader;
				drpSalesPerson.DataBind();
			}
			catch (System.Exception ex)
			{
				reader.Close();
				connection.Close();
				throw ex;
			}
		}

		/// <summary>
		/// Load the orders submitted by the selected salesperson.
		/// </summary>
		private void LoadOrders()
		{
			SqlConnection connection = new SqlConnection();
			SqlDataReader reader = null;
			try
			{
				connection.ConnectionString = ConfigurationSettings.AppSettings[Constants.CONFIG_CONNECT_STRING];
				string sql = "SELECT OrderDate, SalesOrderNumber, Store.Name AS Store, SubTotal FROM SalesOrderHeader INNER JOIN Store ON SalesOrderHeader.CustomerID = Store.CustomerID WHERE SalesPersonID = @SalesPersonID AND OrderDate BETWEEN '9/1/2003' AND '12/1/2003' ORDER BY OrderDate, SalesOrderNumber";
				SqlCommand sqlCmd = new SqlCommand(sql, connection);
				sqlCmd.Parameters.Add("@SalesPersonID", SqlDbType.Int, 0).Value = Int32.Parse(drpSalesPerson.SelectedValue);
				sqlCmd.Connection.Open();
				reader = sqlCmd.ExecuteReader();
				grdOrders.DataSource = reader;
				grdOrders.DataBind();
			}
			catch (System.Exception ex)
			{
				reader.Close();
				connection.Close();
				throw ex;
			}
		}

		#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.drpSalesPerson.SelectedIndexChanged += new System.EventHandler(this.drpSalesPerson_SelectedIndexChanged);
			this.Load += new System.EventHandler(this.Page_Load);

		}
		#endregion

		private void drpSalesPerson_SelectedIndexChanged(object sender, System.EventArgs e)
		{
			LoadOrders();
		}



		
		protected override void OnError(EventArgs ex)
		{
			base.OnError (ex);
		}
	}
}