327 lines
12 KiB
C#
327 lines
12 KiB
C#
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();
|
|
}
|
|
}
|
|
|
|
}
|
|
} |