CNSA-216-Personal/CH78/StudentDataTier.cs

using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Web.UI.WebControls;
using System;
using System.IO;
using System.Security.Cryptography;
using System.Text;
using System.Web;

namespace CH78
{
    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();

        protected void grdStudnet_RowDataBind(object sender, GridViewRowEventArgs e)
        {
            
            if (e.Row.RowType == DataControlRowType.Header) // header add click event to select checkboxes
            {
                ((CheckBox)e.Row.FindControl("cbSelectAll")).Attributes.Add("onclick", "javascript:SelectAll('" + ((CheckBox)e.Row.FindControl("cbSelectAll")).ClientID + "')");
            }

            
        }
        
        public static string EncryptQueryString(string inputText, string key, string salt)
{
    byte[] plainText = Encoding.UTF8.GetBytes(inputText);

    using (RijndaelManaged rijndaelCipher = new RijndaelManaged())
    {
        PasswordDeriveBytes secretKey = new PasswordDeriveBytes(Encoding.ASCII.GetBytes(key), Encoding.ASCII.GetBytes(salt));
        using (ICryptoTransform encryptor = rijndaelCipher.CreateEncryptor(secretKey.GetBytes(32), secretKey.GetBytes(16)))
        {
            using (MemoryStream memoryStream = new MemoryStream())
            {
                using (CryptoStream cryptoStream = new CryptoStream(memoryStream, encryptor, CryptoStreamMode.Write))
                {
                    cryptoStream.Write(plainText, 0, plainText.Length);
                    cryptoStream.FlushFinalBlock();
                    string base64 = Convert.ToBase64String(memoryStream.ToArray());

                    // Generate a string that won't get screwed up when passed as a query string.
                    string urlEncoded = HttpUtility.UrlEncode(base64);
                    return urlEncoded;
                }
            }
        }
    }
}

public static string DecryptQueryString(string inputText, string key, string salt)
        {
            byte[] encryptedData = Convert.FromBase64String(inputText);
            PasswordDeriveBytes secretKey = new PasswordDeriveBytes(Encoding.ASCII.GetBytes(key), Encoding.ASCII.GetBytes(salt));

            using (RijndaelManaged rijndaelCipher = new RijndaelManaged())
            {
                using (ICryptoTransform decryptor = rijndaelCipher.CreateDecryptor(secretKey.GetBytes(32), secretKey.GetBytes(16)))
                {
                    using (MemoryStream memoryStream = new MemoryStream(encryptedData))
                    {
                        using (CryptoStream cryptoStream = new CryptoStream(memoryStream, decryptor, CryptoStreamMode.Read))
                        {
                            byte[] plainText = new byte[encryptedData.Length];
                            cryptoStream.Read(plainText, 0, plainText.Length);
                            string utf8 = Encoding.UTF8.GetString(plainText);
                            return utf8;
                        }
                    }
                }
            }
        }
        
        public DataSet GetStudents()
        {
            
            
            try
            {
                // open connection
                myConn.Open();
                //clear any parameters
                cmdString.Parameters.Clear();
                // command
                cmdString.Connection = myConn;
                cmdString.CommandType = CommandType.StoredProcedure;
                cmdString.CommandTimeout = 1500;
                cmdString.CommandText = "GetAllStudents";
                // 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 static DataSet GetStates()
        {
            
            
            try
            {
                // open connection
                myConn.Open();
                //clear any parameters
                cmdString.Parameters.Clear();
                // command
                cmdString.Connection = myConn;
                cmdString.CommandType = CommandType.StoredProcedure;
                cmdString.CommandTimeout = 1500;
                cmdString.CommandText = "GetAllStates";
                // 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 static DataSet GetGenders()
        {
            
            
            try
            {
                // open connection
                myConn.Open();
                //clear any parameters
                cmdString.Parameters.Clear();
                // command
                cmdString.Connection = myConn;
                cmdString.CommandType = CommandType.StoredProcedure;
                cmdString.CommandTimeout = 1500;
                cmdString.CommandText = "GetAllGenders";
                // 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 static 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";

                cmdString.Parameters.Add("@studentid", SqlDbType.VarChar, 6).Value = StuID;

                cmdString.ExecuteNonQuery();

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


        public static void UpdateStudents(
            int update,
            string student_id,
            string fname,
            string lname,
            string midinit,
            DateTime dob,
            string gender,
            Decimal acct_bal,
            string major,
            // string home_phone,
            string cell_phone,
            // string work_phone,
            string address_one,
            string city,
            string stu_state,
            string zip,
            Decimal totalcredits)
        {
            try
            {
                string home_phone = "0";
                string work_phone = "0";
                
                // open connection
                myConn.Open();
                //clear any parameters
                cmdString.Parameters.Clear();
                // command
                cmdString.Connection = myConn;
                cmdString.CommandType = CommandType.StoredProcedure;
                cmdString.CommandTimeout = 1500;
                cmdString.CommandText = "UpdateStudent";

                cmdString.Parameters.Add("@update", SqlDbType.Int).Value = update;
                cmdString.Parameters.Add("@student_id", SqlDbType.VarChar, 6).Value = student_id;
                cmdString.Parameters.Add("@fname", SqlDbType.VarChar, 25).Value = fname;
                cmdString.Parameters.Add("@lname", SqlDbType.VarChar, 25).Value = lname;
                cmdString.Parameters.Add("@midinit", SqlDbType.Char).Value = midinit;
                cmdString.Parameters.Add("@dob", SqlDbType.Date).Value = dob;
                cmdString.Parameters.Add("@gender", SqlDbType.Char, 6).Value = gender;
                cmdString.Parameters.Add("@acct_bal", SqlDbType.Decimal, 7).Value = acct_bal;
                cmdString.Parameters.Add("@major", SqlDbType.VarChar, 60).Value = major;
                cmdString.Parameters.Add("@home_phone", SqlDbType.VarChar, 15).Value = home_phone;
                cmdString.Parameters.Add("@cell_phone", SqlDbType.VarChar, 15).Value = cell_phone;
                cmdString.Parameters.Add("@work_phone", SqlDbType.VarChar, 15).Value = work_phone;
                cmdString.Parameters.Add("@address_one", SqlDbType.VarChar, 60).Value = address_one;
                cmdString.Parameters.Add("@city", SqlDbType.VarChar, 60).Value = city;
                cmdString.Parameters.Add("@stu_state", SqlDbType.Char, 2).Value = stu_state;
                cmdString.Parameters.Add("@zip", SqlDbType.VarChar, 5).Value = zip;
                cmdString.Parameters.Add("@totalcredits", SqlDbType.Decimal, 5).Value = totalcredits;

                cmdString.ExecuteNonQuery();

            }
            catch (Exception ex)
            {
                throw new ArgumentException(ex.Message);
            }
            finally
            {
                myConn.Close();
            }
        }
            
        public static DataSet SearchStudents(string fname, string lname, 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 = "SEARCHSTUDENT2";

                cmdString.Parameters.Add("@studentid", SqlDbType.VarChar, 6).Value = stuID;
                cmdString.Parameters.Add("@fname", SqlDbType.VarChar, 25).Value = fname;
                cmdString.Parameters.Add("@lname", SqlDbType.VarChar, 25).Value = lname;
                
                // 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();
            }
        }

    }
}