public static void InsertAgent(AgentLocation myAgent)
{
string sql = "INSERT INTO Locations "
+ "(Id, FirstName, LastName, Agency, Certifications, Address, Suite, City, State, Zip, Licenses, Phone) "
+ "VALUES (@Id, @FirstName, @LastName, @Agency, @Certifications, @Address, @Suite, @City, @State, @Zip, @Licenses, @Phone)";
using (SqlConnection con = new SqlConnection(GetConnectionString()))
{
using (SqlCommand cmd = new SqlCommand(sql, con))
{
cmd.Parameters.AddWithValue("Id", myAgent.Id);
cmd.Parameters.AddWithValue("FirstName", myAgent.FirstName);
cmd.Parameters.AddWithValue("LastName", myAgent.LastName);
cmd.Parameters.AddWithValue("Agency", myAgent.Agency);
cmd.Parameters.AddWithValue("Certifications", myAgent.Certifications);
cmd.Parameters.AddWithValue("Address", myAgent.Address);
cmd.Parameters.AddWithValue("Suite", myAgent.Suite);
cmd.Parameters.AddWithValue("City", myAgent.City);
cmd.Parameters.AddWithValue("State", myAgent.State);
cmd.Parameters.AddWithValue("Zip", myAgent.Zip);
cmd.Parameters.AddWithValue("Licenses", myAgent.Licenses);
cmd.Parameters.AddWithValue("Phone", myAgent.Phone);
con.Open();
cmd.ExecuteNonQuery();
}
}
}
public static List <AgentLocation> GetAllAgents()
{
List <AgentLocation> agentList = new List <AgentLocation>();
string cs = ConfigurationManager.ConnectionStrings["AgentAdmin"].ConnectionString;
using (SqlConnection con = new SqlConnection(cs))
{
SqlCommand cmd = new SqlCommand("SELECT * FROM Locations", con);
con.Open();
SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
AgentLocation myAgent = new AgentLocation();
myAgent.Id = Convert.ToInt32(rdr["Id"]);
myAgent.FirstName = rdr["FirstName"].ToString();
myAgent.LastName = rdr["LastName"].ToString();
myAgent.Agency = rdr["Agency"].ToString();
myAgent.Certifications = rdr["Certifications"].ToString();
myAgent.Address = rdr["Address"].ToString();
myAgent.Suite = rdr["Suite"].ToString();
myAgent.City = rdr["City"].ToString();
myAgent.State = rdr["State"].ToString();
myAgent.Zip = rdr["Zip"].ToString();
myAgent.Licenses = rdr["Licenses"].ToString();
myAgent.Phone = rdr["Phone"].ToString();
agentList.Add(myAgent);
}
}
return(agentList);
}
public static int DeleteAgent(AgentLocation myAgent)
{
int deleteCount = 0;
string sql = "DELETE FROM Locations "
+ "WHERE Id = @Id "
+ "AND FirstName = @FirstName "
+ "AND LastName = @LastName "
+ "AND Agency = @Agency "
+ "AND Certifications = @Certifications "
+ "AND Address = @Address "
+ "AND Suite = @Suite "
+ "AND City = @City "
+ "AND State = @State "
+ "AND Zip = @Zip "
+ "AND Licenses = @Licenses "
+ "AND Phone = @Phone";
using (SqlConnection con = new SqlConnection(GetConnectionString()))
{
using (SqlCommand cmd = new SqlCommand(sql, con))
{
cmd.Parameters.AddWithValue("Id", myAgent.Id);
cmd.Parameters.AddWithValue("FirstName", myAgent.FirstName);
cmd.Parameters.AddWithValue("LastName", myAgent.LastName);
cmd.Parameters.AddWithValue("Agency", myAgent.Agency);
cmd.Parameters.AddWithValue("Certifications", myAgent.Certifications);
cmd.Parameters.AddWithValue("Address", myAgent.Address);
cmd.Parameters.AddWithValue("Suite", myAgent.Suite);
cmd.Parameters.AddWithValue("City", myAgent.City);
cmd.Parameters.AddWithValue("State", myAgent.State);
cmd.Parameters.AddWithValue("Zip", myAgent.Zip);
cmd.Parameters.AddWithValue("Licenses", myAgent.Licenses);
cmd.Parameters.AddWithValue("Phone", myAgent.Phone);
con.Open();
deleteCount = cmd.ExecuteNonQuery();
}
}
return(deleteCount);
}
public static int UpdateAgent(AgentLocation original_Agent, AgentLocation myAgent)
{
int updateCount = 0;
string sql = "UPDATE Locations "
+ "SET Id = @Id, "
+ "FirstName = @FirstName, "
+ "LastName = @LastName, "
+ "Agency = @Agency, "
+ "Certifications = @Certifications, "
+ "Address = @Address, "
+ "Suite = @Suite, "
+ "City = @City, "
+ "State = @State, "
+ "Zip = @Zip, "
+ "Licenses = @Licenses, "
+ "Phone = @Phone "
+ "WHERE Id = @original_Id "
+ "AND FirstName = @original_FirstName "
+ "AND LastName = @original_LastName "
+ "AND Agency = @original_Agency "
+ "AND Certifications = @original_Certifications "
+ "AND Address = @original_Address "
+ "AND Suite = @original_Suite "
+ "AND City = @original_City "
+ "AND State = @original_State "
+ "AND Zip = @original_Zip "
+ "AND Licenses = @original_Licenses "
+ "AND Phone = @original_Phone";
using (SqlConnection con = new SqlConnection(GetConnectionString()))
{
using (SqlCommand cmd = new SqlCommand(sql, con))
{
cmd.Parameters.AddWithValue("FirstName", myAgent.FirstName);
cmd.Parameters.AddWithValue("LastName", myAgent.LastName);
cmd.Parameters.AddWithValue("Agency", myAgent.Agency);
cmd.Parameters.AddWithValue("Certifications", myAgent.Certifications);
cmd.Parameters.AddWithValue("Address", myAgent.Address);
cmd.Parameters.AddWithValue("Suite", myAgent.Suite);
cmd.Parameters.AddWithValue("City", myAgent.City);
cmd.Parameters.AddWithValue("State", myAgent.State);
cmd.Parameters.AddWithValue("Zip", myAgent.Zip);
cmd.Parameters.AddWithValue("Licenses", myAgent.Licenses);
cmd.Parameters.AddWithValue("Phone", myAgent.Phone);
cmd.Parameters.AddWithValue("original_Id", original_Agent.Id);
cmd.Parameters.AddWithValue("original_FirstName", original_Agent.FirstName);
cmd.Parameters.AddWithValue("original_LastName", original_Agent.LastName);
cmd.Parameters.AddWithValue("original_Agency", original_Agent.Agency);
cmd.Parameters.AddWithValue("original_Certifications", original_Agent.Certifications);
cmd.Parameters.AddWithValue("original_Address", original_Agent.Address);
cmd.Parameters.AddWithValue("original_Suite", original_Agent.Suite);
cmd.Parameters.AddWithValue("original_City", original_Agent.City);
cmd.Parameters.AddWithValue("original_State", original_Agent.State);
cmd.Parameters.AddWithValue("original_Zip", original_Agent.Zip);
cmd.Parameters.AddWithValue("original_Licenses", original_Agent.Licenses);
cmd.Parameters.AddWithValue("original_Phone", original_Agent.Phone);
con.Open();
updateCount = cmd.ExecuteNonQuery();
}
}
return(updateCount);
}