public IEnumerable <CommunityContactData> GetCommunityContact()
{
List <CommunityContactData> communityContacts = new List <CommunityContactData>();
string sqlSelect = "exec sel_community_contact";
using (SqlDataReader reader = Utilities.ExecQuery(sqlSelect))
{
while (reader.Read())
{
CommunityContactData newCommunityContact = new CommunityContactData
{
ID = (int)reader["ID"],
CommunityID = (int)reader["communityID"],
CommunityName = reader["community_name"].ToString(),
ContactID = (int)reader["contactID"],
ContactName = reader["first_name"].ToString() + " " + reader["last_name"].ToString(),
CreatedDate = (DateTime)reader["created_date"],
UpdatedDate = (DateTime)reader["updated_date"]
};
communityContacts.Add(newCommunityContact);
}
}
return(communityContacts);
}
public ActionResult <CommunityContactData> GetCommunityContact(int id)
{
CommunityContactData data = GetCommunityContact().FirstOrDefault((p) => p.ID == id);
if (data == null)
{
return(NotFound());
}
return(Ok(data));
}
public ActionResult <CommunityContactData> PutCommunityContact(int id, [FromBody] CommunityContactData data)
{
//Get connection string - to be replaced with different credentials later
string fcnConnectionString = Utilities.GetDBConnectionString();
string sqlUpdate = "exec FCN..upd_community_contact @ID, @communityID, @contactID";
using (SqlConnection fcnDBConnection = new SqlConnection(fcnConnectionString))
{
fcnDBConnection.Open();
using (SqlCommand cmdUpdate = new SqlCommand(sqlUpdate, fcnDBConnection))
{
cmdUpdate.Parameters.AddWithValue("@ID", id);
cmdUpdate.Parameters.AddWithValue("@communityID", data.CommunityID);
cmdUpdate.Parameters.AddWithValue("@contactID", data.ContactID);
cmdUpdate.ExecuteNonQuery();
}
}
data = GetCommunityContact().FirstOrDefault((p) => p.ID == id);
return(Ok(data));
}
public ActionResult <CommunityContactData> PostCommunityContact([FromBody] CommunityContactData data)
{
int id = 0;
//Get connection string - to be replaced with different credentials later
string fcnConnectionString = Utilities.GetDBConnectionString();
string sqlInsert = "exec FCN..ins_community_contact @communityID, @contactID";
using (SqlConnection fcnDBConnection = new SqlConnection(fcnConnectionString))
{
fcnDBConnection.Open();
using (SqlCommand cmdInsert = new SqlCommand(sqlInsert, fcnDBConnection))
{
cmdInsert.Parameters.AddWithValue("@communityID", data.CommunityID);
cmdInsert.Parameters.AddWithValue("@contactID", data.ContactID);
using (SqlDataReader reader = cmdInsert.ExecuteReader())
{
while (reader.Read())
{
if (reader["ID"] != DBNull.Value)
{
id = (int)reader["ID"];
}
if (id == 0)
{
return(NotFound());
}
}
}
}
}
data = GetCommunityContact().FirstOrDefault((p) => p.ID == id);
return(CreatedAtAction("GetCommunityContact", new { id }, data));
}
public IEnumerable <CommunityData> GetCommunity()
{
List <CommunityData> communities = new List <CommunityData>();
string sqlSelect = "exec sel_community";
using (SqlDataReader reader = Utilities.ExecQuery(sqlSelect))
{
while (reader.Read())
{
CommunityData newCommunity = new CommunityData
{
ID = (int)reader["ID"],
CommunityName = reader["community_name"].ToString(),
Affiliation = reader["affiliation"].ToString(),
Address1 = reader["address1"].ToString(),
Address2 = reader["address2"].ToString(),
City = reader["city"].ToString(),
State = reader["state"].ToString(),
ZipCode = reader["zip_code"].ToString(),
Phone = reader["phone"].ToString(),
AlternatePhone = reader["alt_phone"].ToString(),
Email = reader["email"].ToString(),
Website = reader["website"].ToString(),
Active = reader["active"].ToString() == "1" ? true : false,
CreatedDate = (DateTime)reader["created_date"],
UpdatedDate = (DateTime)reader["updated_date"]
};
string fcnConnectionString = Utilities.GetDBConnectionString();
string sqlSelectContacts = "exec sel_community_contact_by_community @communityID";
using (SqlConnection fcnDBConnection = new SqlConnection(fcnConnectionString))
{
fcnDBConnection.Open();
using (SqlCommand cmdContacts = new SqlCommand(sqlSelectContacts, fcnDBConnection))
{
cmdContacts.Parameters.AddWithValue("@communityID", newCommunity.ID);
using (SqlDataReader readerContacts = cmdContacts.ExecuteReader())
{
while (readerContacts.Read())
{
CommunityContactData newContact = new CommunityContactData
{
ID = (int)readerContacts["ID"],
CommunityID = (int)readerContacts["communityID"],
CommunityName = readerContacts["community_name"].ToString(),
ContactID = (int)readerContacts["contactID"],
ContactName = readerContacts["first_name"].ToString() + " " + readerContacts["last_name"].ToString(),
CreatedDate = (DateTime)reader["created_date"],
UpdatedDate = (DateTime)reader["updated_date"]
};
if (newCommunity.Contacts == null)
{
newCommunity.Contacts = new List <CommunityContactData>();
}
newCommunity.Contacts.Add(newContact);
}
}
}
}
communities.Add(newCommunity);
}
}
return(communities);
}