public static bool CreateAccount(Agents inputAgentInfo) { bool success = true; // block code style string InsertStatement = "DECLARE @newUserName varchar(255) " + "DECLARE @newPassword varchar(255) " + "DECLARE @GetAgentId int " + "SELECT @newPassword = (AgtFirstName + AgtLastName), @newUserName = AgtEmail, @GetAgentId = AgentId " + "FROM AGENTS WHERE " + "AgtFirstName = @inputFirstName " + "AND AgtLastName = @inputLastname " + "AND AgtEmail = @inputEmail " + "INSERT INTO AccLoginInfos(accUserName, accPassWord, AgentId) VALUES(@newUserName, @newPassword, @GetAgentId)"; SqlConnection con = GetConnection(); SqlCommand cmd = new SqlCommand(InsertStatement, con); cmd.Parameters.AddWithValue("@inputFirstName", inputAgentInfo.AgtFirstName); cmd.Parameters.AddWithValue("@inputLastname", inputAgentInfo.AgtLastName); cmd.Parameters.AddWithValue("@inputEmail", inputAgentInfo.AgtEmail); try { // use cmd anywhere in this block // any exception will be thrown to the place where this method was called con.Open(); GetExistenceLoginInfosTable(); int rowsUpdated = cmd.ExecuteNonQuery(); if (rowsUpdated == 0) { success = false; } } catch (Exception e) { success = false; } finally { con.Close(); } return(success); }
public static bool FindAgentExistingAcc(string accUserName, string accPassWord, out Agents SessionStoreAgentAcc) { bool success = true; SessionStoreAgentAcc = new Agents(); try { // block code style string selectQuery = "DECLARE @GetAgentId int " + "SELECT @GetAgentId = AgentId " + "FROM AccLoginInfos ali " + "WHERE ali.accUserName = @inputUserName " + "AND ali.accPassWord = @inputPassword " + "SELECT * from Agents a WHERE a.AgentId = @GetAgentId"; using (SqlConnection con = GetConnection()) { //use "con" anywhere in this block using (SqlCommand cmd = new SqlCommand(selectQuery, con)) { //any exception will be thrown to the place where this method was called con.Open(); // open connection GetExistenceLoginInfosTable(); cmd.Parameters.AddWithValue("@inputUserName", accUserName); cmd.Parameters.AddWithValue("@inputPassword", accPassWord); SqlDataReader dr = cmd.ExecuteReader(CommandBehavior .CloseConnection); // closes connection when done reading SessionStoreAgentAcc = new Agents(); if (!dr.Read()) { success = false; } else { // while there is data in data reader /* * [AgentId] * ,[AgtFirstName] * ,[AgtMiddleInitial] * ,[AgtLastName] * ,[AgtBusPhone] * ,[AgtEmail] * ,[AgtPosition] * ,[AgencyId] */ SessionStoreAgentAcc.AgentId = Convert.ToInt32(dr["AgentId"]); SessionStoreAgentAcc.AgtFirstName = dr["AgtFirstName"] as string; SessionStoreAgentAcc.AgtMiddleInitial = dr["AgtMiddleInitial"] as string; SessionStoreAgentAcc.AgtLastName = dr["AgtLastName"] as string; SessionStoreAgentAcc.AgtBusPhone = dr["AgtBusPhone"] as string; SessionStoreAgentAcc.AgtEmail = dr["AgtEmail"] as string; SessionStoreAgentAcc.AgtPosition = dr["AgtPosition"] as string; SessionStoreAgentAcc.AgencyId = Convert.ToInt32(dr["AgencyId"]); } } } } catch (Exception e) { throw e; success = false; } return(success); }