using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Microsoft.VisualBasic;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Data.SqlClient;


namespace Database3
{
    public class StudentDataTier
    {
        static String connString = ConfigurationManager.ConnectionStrings["ConnString"].ConnectionString;
        static SqlConnection myConn = new SqlConnection(connString);
        static System.Data.SqlClient.SqlCommand cmdString = new System.Data.SqlClient.SqlCommand();
        
        public DataSet GetStudents(string studid, string lname, DateTime dob)
        {
            try
            {
                // open connection
                myConn.Open();
                //clear any parameters
                cmdString.Parameters.Clear();
                // command
                cmdString.Connection = myConn;
                cmdString.CommandType = CommandType.StoredProcedure;
                cmdString.CommandTimeout = 1500;
                cmdString.CommandText = "SearchStudent";
                // Define input parameter
                cmdString.Parameters.Add("@studentid", SqlDbType.VarChar, 6).Value = studid;
                cmdString.Parameters.Add("@lname", SqlDbType.VarChar, 25).Value = lname;
                cmdString.Parameters.Add("@dob", SqlDbType.Date).Value = dob;
                // adapter and dataset
                SqlDataAdapter aAdapter = new SqlDataAdapter();
                aAdapter.SelectCommand = cmdString;
                DataSet aDataSet = new DataSet();
        
                // fill adapter
                aAdapter.Fill(aDataSet);
        
                // return dataSet
                return aDataSet;
            }
            catch (Exception ex)
            {
                throw new ArgumentException(ex.Message);
            }
            finally
            {
                myConn.Close();
            }
        }


        public DataSet GetStudents(string StuID)
        {
            
            
            try
            {
                // open connection
                myConn.Open();
                //clear any parameters
                cmdString.Parameters.Clear();
                // command
                cmdString.Connection = myConn;
                cmdString.CommandType = CommandType.StoredProcedure;
                cmdString.CommandTimeout = 1500;
                cmdString.CommandText = "GetByStudentIDS";
                // Define input parameter
                cmdString.Parameters.Add("@studentid", SqlDbType.VarChar, 6).Value = StuID;
                // adapter and dataset
                SqlDataAdapter aAdapter = new SqlDataAdapter();
                aAdapter.SelectCommand = cmdString;
                DataSet aDataSet = new DataSet();
        
                // fill adapter
                aAdapter.Fill(aDataSet);
        
                // return dataSet
                return aDataSet;
            }
            catch (Exception ex)
            {
                throw new ArgumentException(ex.Message);
            }
            finally
            {
                myConn.Close();
            }
        }
        
        public void DeleteStudents(string StuID)
        {
            
            
            try
                        {
                            // open connection
                            myConn.Open();
                            //clear any parameters
                            cmdString.Parameters.Clear();
                            // command
                            cmdString.Connection = myConn;
                            cmdString.CommandType = CommandType.StoredProcedure;
                            cmdString.CommandTimeout = 1500;
                            cmdString.CommandText = "DELETESTUDENT";
                            // Define input parameter
                            cmdString.Parameters.Add("@studentid", SqlDbType.VarChar, 6).Value = StuID;
                            
                            object result = cmdString.ExecuteScalar();
                            
                        }
                        catch (Exception ex)
                        {
                            throw new ArgumentException(ex.Message);
                        }
                        finally
                        {
                            myConn.Close();
                        }
                    
        }
        
        public void UpdateStudent(string studid, string firstname, string lastname, DateTime dob, decimal acctbal, string Gender,decimal totalCreds, string state)
        {
            try
            {
                myConn.Open();

                cmdString.Parameters.Clear();

                cmdString.Connection = myConn;
                cmdString.CommandType = CommandType.StoredProcedure;
                cmdString.CommandTimeout = 1500;

                cmdString.CommandText = "UPDATESTUDENTBYID";

                cmdString.Parameters.Add("@student_id", SqlDbType.VarChar, 6).Value = studid;
                cmdString.Parameters.Add("@fname", SqlDbType.VarChar, 25).Value = firstname;
                cmdString.Parameters.Add("@lname", SqlDbType.VarChar, 25).Value = lastname;
                cmdString.Parameters.Add("@balance", SqlDbType.Decimal, 7).Value = acctbal;
                cmdString.Parameters.Add("@dob", SqlDbType.Date).Value = dob;
                cmdString.Parameters.Add("@gender", SqlDbType.Char, 6).Value = Gender;
                cmdString.Parameters.Add("@credits", SqlDbType.Decimal, 5).Value = totalCreds;
                cmdString.Parameters.Add("@state", SqlDbType.Char, 2).Value = state;

                cmdString.ExecuteScalar();

            }
            catch (Exception ex)
            {
                throw new ArgumentException(ex.Message);
            }
            finally
            {
                myConn.Close();
            }
        }
    }
    

}