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"); } } } }