//================================================================================//
//                              C:\LibraryApp-Master                              //
//================================================================================//
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace LibraryApp
{
    public partial class Users : Form
    {
        String TableName, PrimaryKey, OrderBy;
        long RecNo, NoRecords;
        SqlConnection Conn;
        DataTable dt;

        //================================================================================//
        //                        WindowsAuthenticationConnection                         //
        //================================================================================//
        //  Purpose: Use the Windows authentication connection string to open a           //
        //           connection, called Conn, to the specified database of the specified  //
        //           server.                                                              //
        //                                                                                //
        //  Written By : Dr. Thomas E. Hicks                   Environment : .NET 2005    //
        //        Date : 3/1/2008                                 Language : C#           //
        //================================================================================//
        public void WindowsAuthenticationConnection(String SQLServer, String Database)
        {
            String ConnectionString = "Data Source=" + SQLServer + ";Integrated Security=True;" +
                                      "Initial Catalog=" + Database;
            Conn = new SqlConnection(ConnectionString);
        }

        //================================================================================//
        //                           SQLAuthenticationConnection                          //
        //================================================================================//
        //  Purpose: Use the SQL authentication connection string to open a connection    //
        //           called Conn, to the specified database of the specified server for   //
        //           the specified user with the specified password.                      //
        //                                                                                //
        //  Written By : Dr. Thomas E. Hicks                   Environment : .NET 2005    //
        //        Date : 3/1/2008                                 Language : C#           //
        //================================================================================//
        public void SQLAuthenticationConnection(String SQLServer, String Database,
                                                String UserName, String Password)
        {
            String ConnectionString = "Data Source=" + SQLServer + ";Initial Catalog=" + Database + ";" +
                                      "Persist Security Info=True;User ID=" + UserName + "; Pwd=" + Password;
            Conn = new SqlConnection(ConnectionString);
        }

        //================================================================================//
        //                                  TableLength                                   //
        //================================================================================//
        //  Purpose: Explicitly the number of records in a database table.                //
        //                                                                                //
        //  Written By : Dr. Thomas E. Hicks                   Environment : .NET 2005    //
        //        Date : 3/1/2008                                 Language : C#           //
        //================================================================================//
        public long TableLength(String TableName)
        {
            String Query = "Select Count(*) As NoRecords From " + TableName;
            SqlDataAdapter da = new SqlDataAdapter(Query, Conn);
            DataTable dt = new DataTable();
            da.Fill(dt);
            return (Convert.ToInt32(dt.Rows[0]["NoRecords"]));
        }

        //================================================================================//
        //                                 FillDataTable                                  //
        //================================================================================//
        //  Purpose: Explicitly return a table whose that has been filled with the        //                                                        //
        //           query.                                                               //
        //                                                                                //
        //  Written By : Dr. Thomas E. Hicks                   Environment : .NET 2005    //
        //        Date : 3/1/2008                                 Language : C#           //
        //================================================================================//
        public DataTable FillDataTable(String Query)
        {
            SqlDataAdapter da = new SqlDataAdapter(Query, Conn);
            DataTable dt = new DataTable();
            da.Fill(dt);
            return (dt);
        }

        //================================================================================//
        //                              FillFormVariables                                 //
        //================================================================================//
        //  Purpose: Scatter all of the current datatable fields into the form variables. //
        //                                                                                //
        //  Written By : Dr. Thomas E. Hicks                   Environment : .NET 2005    //
        //        Date : 3/1/2008                                 Language : C#           //
        //================================================================================//
        public void FillFormVariables()
        {
            if (dt.Rows.Count != 0)
            {
                lbID.Text = dt.Rows[0]["ID"].ToString();
                txtFirst.Text = dt.Rows[0]["First"].ToString();
                txtMI.Text = dt.Rows[0]["MI"].ToString();
                txtLast.Text = dt.Rows[0]["Last"].ToString();
            }
            else
                MessageBox.Show("Unable To Fill Form Variables - Query Unsuccessful!");
        }

        //================================================================================//
        //                                CreateParams                                    //
        //================================================================================//
        //  Purpose: Block of code to disable the close box on a form and yet control     //
        //           miniize and maximize functionality.                                  //
        //                                                                                //
        //  Written By : Dr. Thomas E. Hicks                   Environment : .NET 2005    //
        //        Date : 3/1/2008                                 Language : C#           //
        //================================================================================//
        private const int CS_NOCLOSE = 0x200;
        protected override CreateParams CreateParams
        {
            get
            {
                CreateParams cp = base.CreateParams;
                cp.ClassStyle = cp.ClassStyle | CS_NOCLOSE;
                return cp;
            }
        }

        //================================================================================//
        //                              FillTableWithRecord                               //
        //================================================================================//
        //  Purpose: Fill the table with the specified record number.                     //
        //                                                                                //
        //  Written By : Dr. Thomas E. Hicks                   Environment : .NET 2005    //
        //        Date : 3/1/2008                                 Language : C#           //
        //================================================================================//
        public void FillTableWithRecord(long RecNo)
        {
            String Query = "  SELECT TOP 1 *  " +
                           "  FROM " + TableName +
                           "  WHERE [" + PrimaryKey + "] NOT IN " +
                           "  (SELECT TOP " + RecNo.ToString() + " [" + PrimaryKey + "] " +
                           "  FROM " + TableName +
                           "  ORDER BY " + OrderBy + ") " +
                           "  ORDER BY " + OrderBy;

            dt = FillDataTable(Query);
        }

        //================================================================================//
        //                                 LoadNextRecord                                 //
        //================================================================================//
        //  Purpose: Load the next logical record into the datatable.  If that record     //
        //           deleted, proceed to the Next until you find one that is has not      //
        //           been deleted.                                                        //
        //                                                                                //
        //  Limitation : Infinite loop is caused when all records are deleted.            //
        //                                                                                //
        //  Written By : Dr. Thomas E. Hicks                   Environment : .NET 2005    //
        //        Date : 3/1/2008                                 Language : C#           //
        //================================================================================//
        public void LoadNextRecord()
        {
            NoRecords = TableLength(TableName);
            if (RecNo >= NoRecords - 1)
                RecNo = 0;
            else
                RecNo++;
            FillTableWithRecord(RecNo);
            if ((dt.Rows[0]["Deleted"].ToString() == "T") || (dt.Rows[0]["Deleted"].ToString() == "t"))
                LoadNextRecord();
            FillFormVariables();
        }

        //================================================================================//
        //                              LoadPreviousRecord                                //
        //================================================================================//
        //  Purpose: Load the previous logical record into the datatable.  If that record //
        //           deleted, proceed to the Previous until you find one that is has not  //   
        //           been deleted.                                                        //
        //                                                                                //
        //  Limitation : Infinite loop is caused when all records are deleted.            //
        //                                                                                //
        //  Written By : Dr. Thomas E. Hicks                   Environment : .NET 2005    //
        //        Date : 3/1/2008                                 Language : C#           //
        //================================================================================//
        public void LoadPreviousRecord()
        {
            NoRecords = TableLength(TableName);
            if (RecNo <= 0)
                RecNo = NoRecords - 1;
            else
                RecNo--;
            FillTableWithRecord(RecNo);
            if ((dt.Rows[0]["Deleted"].ToString() == "T") || (dt.Rows[0]["Deleted"].ToString() == "t"))
                LoadPreviousRecord();
            FillFormVariables();
        }

        //================================================================================//
        //                               LoadFirstRecord                                  //
        //================================================================================//
        //  Purpose: Load the first logical record into the datatable.  If that record    //
        //           deleted, proceed to the Next until you find one that is has not      //
        //           been deleted.                                                        //
        //                                                                                //
        //  Limitation : Infinite loop is caused when all records are deleted.            //
        //                                                                                //
        //  Written By : Dr. Thomas E. Hicks                   Environment : .NET 2005    //
        //        Date : 3/1/2008                                 Language : C#           //
        //================================================================================//
        public void LoadFirstRecord()
        {
            NoRecords = TableLength(TableName);
            RecNo = 0;
            FillTableWithRecord(RecNo);
            if ((dt.Rows[0]["Deleted"].ToString() == "T") || (dt.Rows[0]["Deleted"].ToString() == "t"))
                LoadNextRecord();
            FillFormVariables();
        }

        //================================================================================//
        //                               LoadLastRecord                                   //
        //================================================================================//
        //  Purpose: Load the last logical record into the datatable.  If that record     //
        //           deleted, proceed to the Previous until you find one that is has not  //   
        //           been deleted.                                                        //
        //                                                                                //
        //  Limitation : Infinite loop is caused when all records are deleted.            //
        //                                                                                //
        //  Written By : Dr. Thomas E. Hicks                   Environment : .NET 2005    //
        //        Date : 3/1/2008                                 Language : C#           //
        //================================================================================//
        public void LoadLastRecord()
        {
            NoRecords = TableLength(TableName);
            RecNo = NoRecords - 1;
            FillTableWithRecord(RecNo);
            if ((dt.Rows[0]["Deleted"].ToString() == "T") || (dt.Rows[0]["Deleted"].ToString() == "t"))
                LoadPreviousRecord();
            FillFormVariables();
        }


        //================================================================================//
        //                                 LoadToolTips                                   //
        //================================================================================//
        //  Purpose: Create ToolTip help for each and every button on the Users form.     //
        //                                                                                //
        //  Written By : Dr. Thomas E. Hicks                   Environment : .NET 2005    //
        //        Date : 3/1/2008                                 Language : C#           //
        //================================================================================//
        public void LoadToolTips()
        {
            toolTip1.SetToolTip(btnNextRight, "Go To The LoadNextRecord User!");
            toolTip1.SetToolTip(btnPreviousRight, "Go To The LoadPreviousRecord User!");
            toolTip1.SetToolTip(btnTopRight, "Go To The First User!");
            toolTip1.SetToolTip(btnBottomRight, "Go To The Last User!");
            toolTip1.SetToolTip(btnNextLeft, "Go To The LoadNextRecord User!");
            toolTip1.SetToolTip(btnPreviousLeft, "Go To The LoadPreviousRecord User!");
            toolTip1.SetToolTip(btnTopLeft, "Go To The First User!");
            toolTip1.SetToolTip(btnBottomLeft, "Go To The Last User!");
            toolTip1.SetToolTip(btnClose, "Close This Form!");
        }

        public Users()
        {
            InitializeComponent();
            //WindowsAuthenticationConnection("NET1\\SQLEXPRESS", "LibraryApp");
            SQLAuthenticationConnection("NET1\\SQLEXPRESS", "LibraryApp", "student", "student");
            PrimaryKey = "ID";
            OrderBy = "FullName";
            TableName = "Users";
            RecNo = 0;
            NoRecords = TableLength(TableName);
            LoadFirstRecord();
            LoadToolTips();
        }

        private void Users_Load(object sender, EventArgs e)
        {
        }

        private void btnClose_Click(object sender, EventArgs e)
        {
            Application.Exit();
        }

        private void btnPreviousLeft_Click(object sender, EventArgs e)
        {
            LoadPreviousRecord();
        }

        private void btnPreviousRight_Click(object sender, EventArgs e)
        {
            LoadPreviousRecord();
        }

        private void btnNextRight_Click(object sender, EventArgs e)
        {
            LoadNextRecord();
        }

        private void btnNextLeft_Click(object sender, EventArgs e)
        {
            LoadNextRecord();
        }

        private void btnTopLeft_Click(object sender, EventArgs e)
        {
            LoadFirstRecord();
        }

        private void btnTopRight_Click(object sender, EventArgs e)
        {
            LoadFirstRecord();
        }

        private void btnBottomLeft_Click(object sender, EventArgs e)
        {
            LoadLastRecord();
        }

        private void btnBottomRight_Click(object sender, EventArgs e)
        {
            LoadLastRecord();
        }

        private void PersonalInfo_Click(object sender, EventArgs e)
        {

        }
    }
}