using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Diagnostics;


namespace SQL_ConnectionTest
{
    public partial class Form1 : Form
    {

        public Form1()
        {
            InitializeComponent();
        }

        private void btnRefresh_Click(object sender, EventArgs e)
        {
            //hide previous results
            lblConnectionResults.Visible = false;
            lblConnectionString.Visible = false;

            grdData.DataSource = null;
            grdData.Refresh();


            //set the connection string
            //string connString = @"Server =" + txtConnection.Text + ";Integrated Security=True;";
            //string connString = @"Server =" + txtConnection.Text + "; User Id=" + txtUserName.Text + "; Password=" + txtPassword.Text + ";Encrypt=false;";
            //string connString = @"Server =" + txtConnection.Text + "; User Id=" + txtUserName.Text + "; Password=" + txtPassword.Text + ";Initial Catalog=master;Connection Timeout=60;Encrypt=false;";
            string connString = @"Server=" + txtConnection.Text + ";User Id=" + txtUserName.Text + ";Password=" + txtPassword.Text + ";Encrypt=false;";
            lblConnectionString.Text = connString;
            

            Stopwatch ElapsedTime = new Stopwatch();
            ElapsedTime.Start();

            try
            {
                //sql connection object
                using (SqlConnection conn = new SqlConnection(connString))
                {

                    //retrieve the SQL Server instance version
                    string query = @"SELECT [RID]
      ,[company]
      ,[pid]
      ,[first_name]
      ,[last_name]
      ,[middle_initial]
      ,[lastmask]
      ,[lastfit]
      ,[notes]
      ,[exf1]
      ,[exf2]
      ,[exf3]
      ,[exf4]
      ,[exf5]
      ,[exf6]
      ,[exf7]
      ,[exf8]
      ,[exf9]
      ,[exf10]
      ,[DateAdded]
      ,[ppe]
      ,[isactive]
      ,[UUID]
      ,[LastWrite]
  FROM [logic1].[dbo].[Personnel];";

                    //define the SqlCommand object
                    SqlCommand cmd = new SqlCommand(query, conn);


                    //Set the SqlDataAdapter object
                    SqlDataAdapter dAdapter = new SqlDataAdapter(cmd);

                    //define dataset
                    DataSet ds = new DataSet();

                    //fill dataset with query results
                    dAdapter.Fill(ds);

                    //set DataGridView control to read-only
                    grdData.ReadOnly = true;

                    //set the DataGridView control's data source/data table
                    grdData.DataSource = ds.Tables[0];


                    //close connection
                    // conn.Close();
                    ElapsedTime.Stop();

                    Single TimeToRead = Convert.ToSingle(ElapsedTime.ElapsedMilliseconds) / 1000;
                    lblConnectionResults.Text = "Read " + grdData.Rows.Count + " rows in " + TimeToRead.ToString("0.000") + " seconds";
                    lblConnectionResults.Visible = true;
                    lblConnectionString.Visible = true ;

                }
            }
            catch (Exception ex)
            {
                //display error message
                MessageBox.Show("Exception: " + ex.Message);
            }

        }

        private void btnClear_Click(object sender, EventArgs e)
        {
            grdData.DataSource = null;
            grdData.Refresh();
            lblConnectionResults.Visible = false;
            lblConnectionString.Visible = false;
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            this.Text = this.Text + System.Reflection.Assembly.GetExecutingAssembly().GetName().Version.ToString(); 

        }

      }
}