protected bool saveUserLogin()
{
ClientScriptManager cs = Page.ClientScript;
string username = txtUser.Text;
Guid Id = Guid.Empty;
string pass = null;
string role = null;
string applicantId = null;
using (OleDbConnection conn = new OleDbConnection(ConfigurationManager.ConnectionStrings["Job_Portal"].ConnectionString))
using (OleDbCommand cmd = new OleDbCommand(@"Select sl.Id, sl.Password, sr.Role, ap.Id
From dbo.Security_Logins sl join dbo.Security_Logins_Roles slr
on sl.id = slr.login
join dbo.Security_roles sr on slr.Role = sr.Id
join dbo.Applicant_Profiles ap on sl.Id = ap.Login
Where sl.login = '******'"))
{
conn.Open();
cmd.Connection = conn;
OleDbDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
Id = reader.GetGuid(0);
pass = reader.GetString(1);
role = reader.GetString(2);
applicantId = reader.GetGuid(3).ToString();
}
conn.Close();
}
if (pass == null)
{
cs.RegisterClientScriptBlock(this.GetType(), "ButtonClickScript", "<script type=\"text/javascript\"> alert('User does not exist')</script>", false);
return(false);
}
if (txtPassword.Text != pass)
{
cs.RegisterClientScriptBlock(this.GetType(), "ButtonClickScript", "<script type=\"text/javascript\"> alert('User and Password are incorrec')</script>", false);
return(false);
}
Session.Add("Id", Id.ToString());
Session.Add("Username", username);
Session.Add("Role", role);
Session.Add("ApplicantId", applicantId);
return(true);
}
public void TestUsingSQLTextOnly()
{
//Only apply to MSSQL
if ((ConnectedDataProvider.GetDbType() != DataBaseServer.SQLServer))
{
return;
}
Exception exp = null;
OleDbDataReader rdr = null;
OleDbConnection con = null;
OleDbCommand cmdDelete = null;
try
{
BeginCase("Test using SQL text only.");
string rowId = "43973_" + TestCaseNumber.ToString();
string insertText = string.Format("INSERT INTO {0} (ID, {1}) VALUES ('{2}', '{{{3}}}')", GUID_TABLE_NAME, GUID_COLUMN_NAME, rowId, TEST_GUID_STRING);
string selectText = string.Format("SELECT {0} FROM {1} WHERE ID='{2}'", GUID_COLUMN_NAME, GUID_TABLE_NAME, rowId);
string deleteText = string.Format("DELETE FROM {0} WHERE ID='{1}'", GUID_TABLE_NAME, rowId);
con = new OleDbConnection(ConnectedDataProvider.ConnectionString);
OleDbCommand cmdInsert = new OleDbCommand(insertText, con);
OleDbCommand cmdSelect = new OleDbCommand(selectText, con);
cmdDelete = new OleDbCommand(deleteText, con);
con.Open();
cmdInsert.ExecuteNonQuery();
rdr = cmdSelect.ExecuteReader();
rdr.Read();
Guid guidValue = rdr.GetGuid(0);
Guid origGuid = new Guid(TEST_GUID_STRING);
Compare(guidValue, origGuid);
}
catch (Exception ex)
{
exp = ex;
}
finally
{
if ((rdr != null) && (!rdr.IsClosed))
{
rdr.Close();
}
if (cmdDelete != null)
{
cmdDelete.ExecuteNonQuery();
}
if ((con != null) && (con.State != ConnectionState.Closed))
{
con.Close();
}
EndCase(exp);
exp = null;
}
}
public Guid GetGuid(int i)
{
if (SDR != null)
{
return(SDR.GetGuid(i));
}
else
{
return(ODR.GetGuid(i));
}
}
private static Guid GetGuid(OleDbDataReader reader, string fieldName)
{
int ord = reader.GetOrdinal(fieldName);
if (ord < 0 || reader.IsDBNull(ord))
{
return(Guid.Empty);
}
else
{
return(reader.GetGuid(ord));
}
}
public void TestUsingParametersArray()
{
//Only apply to MSSQL
if ((ConnectedDataProvider.GetDbType() != DataBaseServer.SQLServer))
{
return;
}
Exception exp = null;
OleDbDataReader rdr = null;
OleDbConnection con = null;
DbTypeParametersCollection row = new DbTypeParametersCollection(GUID_TABLE_NAME);
string rowId = string.Empty;
try
{
BeginCase("Test using parameters array");
rowId = "43973_" + TestCaseNumber.ToString();
row.Add("UNIQUEIDENTIFIER", new Guid(TEST_GUID_STRING));
row.ExecuteInsert(rowId);
row.ExecuteSelectReader(rowId, out rdr, out con);
rdr.Read();
Guid guidValue = rdr.GetGuid(0);
Compare(guidValue, row[GUID_COLUMN_NAME].Value);
}
catch (Exception ex)
{
exp = ex;
}
finally
{
if ((rdr != null) && (!rdr.IsClosed))
{
rdr.Close();
}
if (rowId != String.Empty)
{
row.ExecuteDelete(rowId);
}
if ((con != null) && (con.State != ConnectionState.Closed))
{
con.Close();
}
EndCase(exp);
exp = null;
}
}
protected Guid getRoleId(string role)
{
Guid Id = Guid.Empty;
using (OleDbConnection conn = new OleDbConnection(ConfigurationManager.ConnectionStrings["Job_Portal"].ConnectionString))
using (OleDbCommand cmd = new OleDbCommand(@"Select Id From dbo.Security_roles
Where Role = '" + role + "'"))
{
conn.Open();
cmd.Connection = conn;
OleDbDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
Id = reader.GetGuid(0);
}
conn.Close();
}
return(Id);
}
private static TK?GetValue <TK>(OleDbDataReader reader, string fieldName) where TK : struct
{
Type tarType = typeof(TK);
int ordinal = reader.GetOrdinal(fieldName);
if (reader.IsDBNull(ordinal))
{
return(null);
}
if (tarType == typeof(Guid))
{
return((TK)Convert.ChangeType(reader.GetGuid(ordinal), tarType));
}
else if (tarType == typeof(int) || tarType == typeof(Int32))
{
return((TK)Convert.ChangeType(reader.GetInt32(ordinal), tarType));
}
else if (tarType == typeof(bool) || tarType == typeof(Boolean))
{
return((TK)Convert.ChangeType(reader.GetBoolean(ordinal), tarType));
}
else if (tarType == typeof(DateTime))
{
return((TK)Convert.ChangeType(reader.GetDateTime(ordinal), tarType));
}
else if (tarType == typeof(decimal))
{
return((TK)Convert.ChangeType(reader.GetDecimal(ordinal), tarType));
}
else if (tarType == typeof(UserRole))
{
return((TK)Convert.ChangeType((UserRole)reader.GetInt32(ordinal), tarType));
}
else
{
Debug.Assert(true, string.Format("Not implement GetValue for the type {0}", tarType.FullName));
return(default(TK));
}
}
protected void loadProfile()
{
Guid applicantId = Guid.Empty;
using (OleDbConnection conn = new OleDbConnection(ConfigurationManager.ConnectionStrings["Job_Portal"].ConnectionString))
using (OleDbCommand cmd = new OleDbCommand(@"Select Full_Name, Email_Address, Phone_Number, Current_Salary, Current_Rate, Currency, Country_Code,
State_Province_Code, Street_Address, City_Town, Zip_Postal_Code, ap.Id
From dbo.Security_Logins sl join dbo.Applicant_Profiles ap on sl.id = ap.Login
Where sl.Id = '" + Session["Id"].ToString() + "'"))
{
conn.Open();
cmd.Connection = conn;
OleDbDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
string[] fullname = reader.GetString(0).Split(' ');
txtFirstName.Text = fullname[0].ToString();
txtLastName.Text = fullname[1].ToString();
txtEmail.Text = reader.GetString(1);
txtPhone.Text = reader.GetString(2);
txtCurrentSalary.Text = reader.GetDecimal(3).ToString();
txtCurrentRate.Text = reader.GetDecimal(4).ToString();
ddlCurrency.SelectedValue = reader.GetString(5);
ddlCountryCode.SelectedValue = reader.GetString(6);
ddlStateProvince.SelectedValue = reader.GetString(7);
txtStreet.Text = reader.GetString(8);
txtCityTown.Text = reader.GetString(9);
txtZipPostalCode.Text = reader.GetString(10);
applicantId = reader.GetGuid(11);
}
conn.Close();
}
Session.Add("ApplicantId", applicantId.ToString());
}
/// <summary>
/// Retrives the class associated with this student. TODO: unasync this method
/// </summary>
/// <returns></returns>
public Class GetClass()
{
using (OleDbConnection conn = new OleDbConnection(AppConfiguration.connectionString))
using (OleDbCommand command = new OleDbCommand()
{
Connection = conn
})
{
conn.Open();
command.CommandText = "SELECT Classes.* FROM Students INNER JOIN Classes ON Students.CLASS_ID=Classes.ID WHERE Students.ID = @ID";
command.Parameters.AddWithValue("ID", ID);
using (OleDbDataReader reader = command.ExecuteReader())
{
if (reader.Read())
{
Class class1;
class1 = new Class(reader.GetString(1), reader.GetString(2), reader.GetGuid(0));
reader.Close();
conn.Close();
return(class1);
}
else
{
throw new Exception("Query result not correct");
}
}
}
}
