adventuresalesform.cs

来自「< SQL Server2005程序设计>」· CS 代码 · 共 205 行

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;

namespace AdventureSales
{
    public partial class AdventureSalesForm : Form
    {

        private int _CustomerId=0;

        public AdventureSalesForm()
        {
            InitializeComponent();
        }

        private void clearButton_Click(object sender, EventArgs e)
        {
            this.searchCityTextBox.Text = "";
            this.searchStateTextBox.Text = "";
            this.searchZipTextBox.Text = "";
            this.searchStoreNameTextBox.Text = "";
        }

        private void searchButton_Click(object sender, EventArgs e)
        {
            string searchSQL = GetSearchSQL();

            _CustomerId = 0;

            AdventureSalesWebService.SqlParameter[] p = null;
            DataSet ds = GetDataSet(Proxy.sqlbatch(searchSQL, ref p));
            if (!(ds == null))
            {
                storesListBox.DataSource = ds.Tables[0];
                storesListBox.DisplayMember = "Name";
                storesListBox.ValueMember = "CustomerID";
            }
            else
            {
                storesListBox.DataSource = null;
            }
        }

        private string GetSearchSQL()
        {
            string searchSQL =
            "SELECT  Store.CustomerID, Store.Name, Address.City, Address.PostalCode as Zip, " + 
            "        StateProvince.StateProvinceCode as State " +
            "   FROM Sales.Store " +
            "   INNER JOIN Sales.CustomerAddress " +
            "	    ON Store.CustomerID = CustomerAddress.CustomerID " +
            "   INNER JOIN Person.Address " +
            "	    ON CustomerAddress.AddressID = Address.AddressID " +
            "   INNER JOIN Person.StateProvince " +
            "	    ON Address.StateProvinceID = StateProvince.StateProvinceID " +
            "		AND Address.StateProvinceID = StateProvince.StateProvinceID ";

            string whereClause = "";
            if (!String.IsNullOrEmpty(this.searchStoreNameTextBox.Text))
                whereClause = AddToWhereClause(whereClause, "Store.Name Like '" 
                                                + searchStoreNameTextBox.Text + "%'");

            if (!String.IsNullOrEmpty(this.searchCityTextBox.Text))
                whereClause = AddToWhereClause(whereClause, "Address.City Like '" + this.searchCityTextBox.Text + "%'");

            if (!String.IsNullOrEmpty(this.searchStateTextBox.Text))
                whereClause = AddToWhereClause(whereClause, "StateProvince.StateProvinceCode = '" + this.searchStateTextBox.Text) + "'";

            if (!String.IsNullOrEmpty(this.searchZipTextBox.Text))
                whereClause = AddToWhereClause(whereClause, "Address.PostalCode Like '" + this.searchZipTextBox.Text + "%'");


            return searchSQL + whereClause;

        }

        private string AddToWhereClause(string whereClause, string newSection)
        {
            if (String.IsNullOrEmpty(whereClause))
                return " WHERE " + newSection;
            else
                return whereClause + " AND " + newSection;
        }

        private void storesListBox_DoubleClick(object sender, EventArgs e)
        {
            if ((storesListBox.SelectedIndex == -1) || (storesListBox.Items.Count==0))
                return;

            _CustomerId = (int)storesListBox.SelectedValue;

            LoadStoreInfo();
            LoadStoreContacts();
            LoadStoreRecentOrders();
            LoadStoreSalesYTD();

            this.CustomerTabControl.SelectedIndex = 1;
        }
         
        private void LoadStoreInfo()
        {
            System.Xml.XmlElement xmlData = GetXMLElement(Proxy.GetStoreInfo(this._CustomerId));
            if (!(xmlData == null))
            {
                this.nameTextBox.Text = xmlData.SelectSingleNode("//Name").InnerText;
                this.address1TextBox.Text = xmlData.SelectSingleNode("//AddressLine1").InnerText;
                this.cityStateZipTextBox.Text = xmlData.SelectSingleNode("//City").InnerText + ", " +
                                                xmlData.SelectSingleNode("//State").InnerText + ", " +
                                                xmlData.SelectSingleNode("//PostalCode").InnerText;
            }
            else
            {
                this.nameTextBox.Text = "";
                this.address1TextBox.Text = "";
                this.cityStateZipTextBox.Text = "";
            }
        }

        private void LoadStoreContacts()
        {
            DataSet ds = GetDataSet(Proxy.GetStoreContacts(this._CustomerId));
            if (!(ds == null))
                this.contactsDataGridView.DataSource = ds.Tables[0];
            else
                this.contactsDataGridView.DataSource = null;
        }

        private void LoadStoreRecentOrders()
        {
            DataSet ds = Proxy.GetStoreRecentOrders(this._CustomerId);
            if (!(ds == null))
                this.recentOrdersDataGridView.DataSource = ds.Tables[0];
            else
                this.recentOrdersDataGridView.DataSource = null;
        }

        private void LoadStoreSalesYTD()
        {

            System.Data.SqlTypes.SqlDecimal yTDSales = Proxy.GetStoreSalesYTD(this._CustomerId);
            if (!yTDSales.IsNull)
                this.salesYTDTextBox.Text = String.Format("{0:C}", (Decimal)yTDSales);
            else
                this.salesYTDTextBox.Text = String.Format("{0:C}", 0.0D) ;
        }

        private AdventureSalesWebService.AdventureWorks Proxy
        {
            get
            {
                if (_proxy == null)
                {
                    _proxy = new AdventureSalesWebService.AdventureWorks();
                    _proxy.Credentials = System.Net.CredentialCache.DefaultCredentials;
                }
                return _proxy;

            }
        }

        private AdventureSalesWebService.AdventureWorks _proxy;

        private DataSet GetDataSet(Object[] results)
        {
            DataSet data = null;
            for (int j = 0; j < results.Length; j++)
            {
                object result;
                result = results[j];

                if (result.ToString() == "System.Data.DataSet")
                {
                    data = (System.Data.DataSet)results[j];
                    break;
                }

            }
            return data;
        }

        private System.Xml.XmlElement GetXMLElement(Object[] results)
        {
            System.Xml.XmlElement data=null;
            for (int j = 0; j < results.Length; j++)
            {
                object e1;
                e1 = results[j];

                if (e1.ToString() == "System.Xml.XmlElement")
                {
                    data = (System.Xml.XmlElement)results[j];
                    break;
                }

            }
            return data;
        }

    }
}