using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data;
using System.Data.SqlClient;

namespace Akadia.DataSetToListView
{
    // Load Data from Table "Titles" into a ListView using a DataSet.
    // Perform Sorting on Column Headers
    public class DataSetListView : System.Windows.Forms.Form
    {
        private System.Windows.Forms.ListView listView1;
        private System.ComponentModel.Container components = null;
        private DataSet _DataSet;
        private SqlConnection _Conn;
        private SqlDataAdapter _DataAdapterTitles;

        // The ListView Sorter
        private ListViewItemComparer _lvwItemComparer;

        // Constructor
        public DataSetListView()
        {
            // Initialize GUI Components
            InitializeComponent();

            // Initialize ListView
            InitializeListView();

            // Load Data From the Database into the DataSet
            GetData();

            // Load Data from the DataSet into the ListView
            LoadList();
        }

        // Initialize ListView
        private void InitializeListView()
        {
            // Set the view to show details.
            listView1.View = View.Details;

            // Allow the user to edit item text.
            listView1.LabelEdit = true;

            // Allow the user to rearrange columns.
            listView1.AllowColumnReorder = true;

            // Display check boxes.
            // listView1.CheckBoxes = true;

            // Select the item and subitems when selection is made.
            listView1.FullRowSelect = true;

            // Display grid lines.
            listView1.GridLines = true;

            // Sort the items in the list in ascending order.
            listView1.Sorting = SortOrder.Ascending;

            // Attach Subitems to the ListView
            listView1.Columns.Add("Title", 300, HorizontalAlignment.Left);
            listView1.Columns.Add("ID", 70, HorizontalAlignment.Left);
            listView1.Columns.Add("Price", 70, HorizontalAlignment.Left);
            listView1.Columns.Add("Publi-Date", 100, HorizontalAlignment.Left);

            // The ListViewItemSorter property allows you to specify the
            // object that performs the sorting of items in the ListView.
            // You can use the ListViewItemSorter property in combination
            // with the Sort method to perform custom sorting.
            _lvwItemComparer = new ListViewItemComparer();
            this.listView1.ListViewItemSorter = _lvwItemComparer;
        }

        // Load Data From the Database into the DataSet
        private void GetData()
        {
            string strConn = "data source=xeon;uid=sa;password=manager;database=pubs";
            try
            {
                _Conn = new SqlConnection(strConn);

                // Fill DataSet
                string strSQL = "SELECT title, "
                    + "title_id, "
                    + "CAST(ROUND(price, 2) as VARCHAR) as price, "
                    + "CONVERT(char(10), pubdate, 104) as pubdate "
                    + "FROM Titles "
                    + "ORDER BY title";

                _DataSet = new DataSet();
                _DataAdapterTitles = new SqlDataAdapter(strSQL, _Conn);
                _DataAdapterTitles.Fill(_DataSet, "Titles");

                // Create an explicit UPDATE command for the Titles Table
                _DataAdapterTitles.UpdateCommand = _Conn.CreateCommand();
                _DataAdapterTitles.UpdateCommand.CommandText =
                    "UPDATE Titles SET "
                    + "title = @u1, "
                    + "price = @u2, "
                    + "pubdate = @u3 "
                    + "WHERE (title_id = @u4)";

                // Add the parameters for UPDATE
                SqlParameter u1 = new SqlParameter("@u1",SqlDbType.VarChar,80,"title");
                u1.SourceVersion = DataRowVersion.Current;
                _DataAdapterTitles.UpdateCommand.Parameters.Add(u1);

                SqlParameter u2 = new SqlParameter("@u2",SqlDbType.Money,2,"price");
                u2.SourceVersion = DataRowVersion.Current;
                _DataAdapterTitles.UpdateCommand.Parameters.Add(u2);

                SqlParameter u3 = new SqlParameter("@u3",SqlDbType.SmallDateTime,0,"pubdate");
                u3.SourceVersion = DataRowVersion.Current;
                _DataAdapterTitles.UpdateCommand.Parameters.Add(u3);

                SqlParameter u4 = new SqlParameter("@u4",SqlDbType.Char,6,"title_id");
                u4.SourceVersion = DataRowVersion.Original;
                _DataAdapterTitles.UpdateCommand.Parameters.Add(u4);

                // Create an explicit INSERT command for the Titles Table
                _DataAdapterTitles.InsertCommand = _Conn.CreateCommand();
                _DataAdapterTitles.InsertCommand.CommandText =
                    "INSERT INTO titles (title, title_id, price, pubdate) " +
                    "VALUES(@i1, @i2, @i3, @i4)";

                // Add the parameters for INSERT
                SqlParameter i1 = new SqlParameter("@i1",SqlDbType.VarChar,80,"title");
                i1.SourceVersion = DataRowVersion.Current;
                _DataAdapterTitles.InsertCommand.Parameters.Add(i1);

                SqlParameter i2 = new SqlParameter("@i2",SqlDbType.Char,6,"title_id");
                i2.SourceVersion = DataRowVersion.Current;
                _DataAdapterTitles.InsertCommand.Parameters.Add(i2);

                SqlParameter i3 = new SqlParameter("@i3",SqlDbType.Money,2,"price");
                i3.SourceVersion = DataRowVersion.Current;
                _DataAdapterTitles.InsertCommand.Parameters.Add(i3);

                SqlParameter i4 = new SqlParameter("@i4",SqlDbType.SmallDateTime,0,"pubdate");
                i4.SourceVersion = DataRowVersion.Current;
                _DataAdapterTitles.InsertCommand.Parameters.Add(i4);

                // Create an explicit DELETE command for the Titles Table
                _DataAdapterTitles.DeleteCommand = _Conn.CreateCommand();
                _DataAdapterTitles.DeleteCommand.CommandText =
                    "DELETE FROM titles " +
                    "WHERE title_id = @d1";

                // Bind parameters to appropriate columns for DELETE command
                SqlParameter d1 = new SqlParameter("@d1",SqlDbType.Char,6,"title_id");
                d1.SourceVersion = DataRowVersion.Original;
                _DataAdapterTitles.DeleteCommand.Parameters.Add(d1);
            }
            catch (Exception ex)
            {
                string msg = ex.Message.ToString();

                MessageBox.Show(msg, "Unable to retrieve data.",
                    MessageBoxButtons.OK, MessageBoxIcon.Error);

                this.Close();
                return;
            }
        }
        // Load Data from the DataSet into the ListView
        private void LoadList()
        {
            // Get the table from the data set
            DataTable dtable = _DataSet.Tables["Titles"];

            // Clear the ListView control
            listView1.Items.Clear();

            // Display items in the ListView control
            for (int i = 0; i < dtable.Rows.Count; i++)
            {
                DataRow drow = dtable.Rows[i];

                // Only row that have not been deleted
                if (drow.RowState != DataRowState.Deleted)
                {
                    // Define the list items
                    ListViewItem lvi = new ListViewItem(drow["title"].ToString());
                    lvi.SubItems.Add (drow["title_id"].ToString());
                    lvi.SubItems.Add (drow["price"].ToString());
                    lvi.SubItems.Add (drow["pubdate"].ToString());

                    // Add the list items to the ListView
                    listView1.Items.Add(lvi);
                }
            }
        }

        // Perform Sorting on Column Headers
        private void listView1_ColumnClick(
            object sender,
            System.Windows.Forms.ColumnClickEventArgs e)
        {

            // Determine if clicked column is already the column that is being sorted.
            if (e.Column == _lvwItemComparer.SortColumn)
            {
                // Reverse the current sort direction for this column.
                if (_lvwItemComparer.Order == SortOrder.Ascending)
                {
                    _lvwItemComparer.Order = SortOrder.Descending;
                }
                else
                {
                    _lvwItemComparer.Order = SortOrder.Ascending;
                }
            }
            else
            {
                // Set the column number that is to be sorted; default to ascending.
                _lvwItemComparer.SortColumn = e.Column;
                _lvwItemComparer.Order = SortOrder.Ascending;
            }

            // Perform the sort with these new sort options.
            this.listView1.Sort();
        }

        // This class is an implementation of the 'IComparer' interface.
        public class ListViewItemComparer : IComparer
        {
            // Specifies the column to be sorted
            private int ColumnToSort;

            // Specifies the order in which to sort (i.e. 'Ascending').
            private SortOrder OrderOfSort;

            // Case insensitive comparer object
            private CaseInsensitiveComparer ObjectCompare;

            // Class constructor, initializes various elements
            public ListViewItemComparer()
            {
                // Initialize the column to '0'
                ColumnToSort = 0;

                // Initialize the sort order to 'none'
                OrderOfSort = SortOrder.None;

                // Initialize the CaseInsensitiveComparer object
                ObjectCompare = new CaseInsensitiveComparer();
            }

            // This method is inherited from the IComparer interface.
            //
            // x: First object to be compared
            // y: Second object to be compared
            //
            // The result of the comparison. "0" if equal,
            // negative if 'x' is less than 'y' and
            // positive if 'x' is greater than 'y'
            public int Compare(object x, object y)
            {
                int compareResult;
                ListViewItem listviewX, listviewY;

                // Cast the objects to be compared to ListViewItem objects
                listviewX = (ListViewItem)x;
                listviewY = (ListViewItem)y;

                // Determine the type being compared
                try
                {
                    compareResult = CompareDateTime(listviewX, listviewY);
                }
                catch
                {
                    try
                    {
                        compareResult = CompareDecimal(listviewX, listviewY);
                    }
                    catch
                    {
                        compareResult = CompareString(listviewX, listviewY);
                    }
                }

                // Simple String Compare
                // compareResult = String.Compare (
                //  listviewX.SubItems[ColumnToSort].Text,
                //  listviewY.SubItems[ColumnToSort].Text
                // );

                // Calculate correct return value based on object comparison
                if (OrderOfSort == SortOrder.Ascending)
                {
                    // Ascending sort is selected, return normal result of compare operation
                    return compareResult;
                }
                else if (OrderOfSort == SortOrder.Descending)
                {
                    // Descending sort is selected, return negative result of compare operation
                    return (-compareResult);
                }
                else
                {
                    // Return '0' to indicate they are equal
                    return 0;
                }
            }

            public int CompareDateTime(ListViewItem listviewX, ListViewItem listviewY)
            {
                // Parse the two objects passed as a parameter as a DateTime.
                System.DateTime firstDate =
                    DateTime.Parse(listviewX.SubItems[ColumnToSort].Text);
                System.DateTime secondDate =
                    DateTime.Parse(listviewY.SubItems[ColumnToSort].Text);

                // Compare the two dates.
                int compareResult = DateTime.Compare(firstDate, secondDate);
                return compareResult;
            }

            public int CompareDecimal(ListViewItem listviewX, ListViewItem listviewY)
            {
                // Parse the two objects passed as a parameter as a DateTime.
                System.Decimal firstValue =
                    Decimal.Parse(listviewX.SubItems[ColumnToSort].Text);
                System.Decimal secondValue =
                    Decimal.Parse(listviewY.SubItems[ColumnToSort].Text);

                // Compare the two dates.
                int compareResult = Decimal.Compare(firstValue, secondValue);
                return compareResult;
            }


            public int CompareString(ListViewItem listviewX, ListViewItem listviewY)
            {
                // Case Insensitive Compare
                int compareResult = ObjectCompare.Compare (
                    listviewX.SubItems[ColumnToSort].Text,
                    listviewY.SubItems[ColumnToSort].Text
                );
                return compareResult;
            }

            // Gets or sets the number of the column to which to
            // apply the sorting operation (Defaults to '0').
            public int SortColumn
            {
                set
                {
                    ColumnToSort = value;
                }
                get
                {
                    return ColumnToSort;
                }
            }

            // Gets or sets the order of sorting to apply
            // (for example, 'Ascending' or 'Descending').
            public SortOrder Order
            {
                set
                {
                    OrderOfSort = value;
                }
                get
                {
                    return OrderOfSort;
                }
            }
        }

        // Clean up any resources being used.
        protected override void Dispose( bool disposing )
        {
            if( disposing )
            {
                if (components != null)
                {
                    components.Dispose();
                }
            }
            base.Dispose( disposing );
        }

        #region Windows Form Designer generated code
        /// <summary>
        /// Required method for Designer support - do not modify
        /// the contents of this method with the code editor.
        /// </summary>
        private void InitializeComponent()
        {
            this.listView1 = new System.Windows.Forms.ListView();
            this.SuspendLayout();
            //
            // listView1
            //
            this.listView1.Location = new System.Drawing.Point(15, 13);
            this.listView1.Name = "listView1";
            this.listView1.Size = new System.Drawing.Size(544, 327);
            this.listView1.TabIndex = 0;
            this.listView1.ColumnClick += new System.Windows.Forms.ColumnClickEventHandler(this.listView1_ColumnClick);
            //
            // DataSetListView
            //
            this.AutoScaleBaseSize = new System.Drawing.Size(5, 13);
            this.ClientSize = new System.Drawing.Size(574, 352);
            this.Controls.Add(this.listView1);
            this.Name = "DataSetListView";
            this.Text = "Load DB-Table into a ListView using a DataSet, perform Sorting on Column Headers";
            this.ResumeLayout(false);

        }
        #endregion

        // The main entry point for the application.
        [STAThread]
        static void Main()
        {
            Application.Run(new DataSetListView());
        }
    }
}