public List <Ubss> GetAll()
{
var listUbss = new List <Ubss>();
SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM dbo.Ubs", _context.Connection);
DataSet ds = new DataSet();
da.Fill(ds, "Ubs");
foreach (DataRow row in ds.Tables[0].Rows)
{
Decimal valor = Convert.ToDecimal(row["id"]);
var geocode = new GeoCode(
Convert.ToDecimal(row["VlrLatitude"]),
Convert.ToDecimal(row["VlrLongitude"]));
var score = new Scores(
Convert.ToInt32(row["DscEstrutFisicAmbiencia"]),
Convert.ToInt32(row["DscAdapDeficFisicIdosos"]),
Convert.ToInt32(row["DscEquipamentos"]),
Convert.ToInt32(row["DscMedicamentos"]));
var ubss = new Ubss(
Convert.ToInt32(row["id"]),
row["NomEstab"].ToString(),
row["DscEndereco"].ToString(),
row["DscCidade"].ToString(),
row["DscTelefone"].ToString(),
geocode, score);
listUbss.Add(ubss);
}
return(listUbss);
}
public void Insert(Ubss pObject)
{
using (SqlCommand cmd = new SqlCommand())
{
cmd.Connection = _context.Connection;
cmd.CommandType = CommandType.Text;
cmd.CommandText = "INSERT INTO dbo.Ubs (CodCnes, NomEstab, DscEndereco, DscBairro, DscCidade, CodMunic, DscTelefone, VlrLatitude, VlrLongitude," +
" DscEstrutFisicAmbiencia, DscAdapDeficFisicIdosos, DscEquipamentos, DscMedicamentos) " +
"VALUES (@codCnes, @nomEstab, @dscEndereco, @dscBairro, @dscCidade, @codMunic, @dscTelefone, @vlrLatitude, @vlrLongitude," +
" @dscEstrutFisicAmbiencia, @dscAdapDeficFisicIdosos, @dscEquipamentos, @dscMedicamentos)";
// add parameters and their values
cmd.Parameters.Add("@codCnes", SqlDbType.VarChar, 5000).Value = pObject.Id;
cmd.Parameters.Add("@nomEstab", SqlDbType.VarChar, 5000).Value = pObject.Name;
cmd.Parameters.Add("@dscEndereco", SqlDbType.VarChar, 5000).Value = pObject.Address;
cmd.Parameters.Add("@dscBairro", SqlDbType.VarChar, 5000).Value = string.Empty;
cmd.Parameters.Add("@dscCidade", SqlDbType.VarChar, 5000).Value = pObject.City;
cmd.Parameters.Add("@codMunic", SqlDbType.VarChar, 5000).Value = string.Empty;
cmd.Parameters.Add("@dscTelefone", SqlDbType.VarChar, 50).Value = pObject.Phone;
cmd.Parameters.Add("@vlrLatitude", SqlDbType.Decimal).Value = pObject.GeoLocation.Lat;
cmd.Parameters.Add("@vlrLongitude", SqlDbType.Decimal).Value = pObject.GeoLocation.Log;
cmd.Parameters.Add("@dscEstrutFisicAmbiencia", SqlDbType.Int).Value = pObject.Score.Size;
cmd.Parameters.Add("@dscAdapDeficFisicIdosos", SqlDbType.Int).Value = pObject.Score.AdaptationForSeniors;
cmd.Parameters.Add("@dscEquipamentos", SqlDbType.Int).Value = pObject.Score.MedicalEquipment;
cmd.Parameters.Add("@dscMedicamentos", SqlDbType.Int).Value = pObject.Score.Medicine;
cmd.ExecuteNonQuery();
}
}
public Ubss GetByCoordinate(decimal lat, decimal log)
{
int id = 0, DscEstrutFisicAmbiencia = 0, DscAdapDeficFisicIdosos = 0, DscEquipamentos = 0, DscMedicamentos = 0;
decimal VlrLatitude = 0, VlrLongitude = 0;
string NomEstab = null, DscEndereco = null, DscCidade = null, DscTelefone = null;
using (SqlCommand cmd = new SqlCommand())
{
cmd.Connection = _context.Connection;
cmd.CommandType = CommandType.Text;
cmd.CommandText = "SELECT * FROM dbo.Ubs WHERE VlrLatitude = @late AND VlrLongitude = @loga";
cmd.Parameters.AddWithValue("@late", lat);
cmd.Parameters.AddWithValue("@loga", log);
using (SqlDataReader dr = cmd.ExecuteReader())
{
if (dr.HasRows)
{
while (dr.Read())
{
id = Convert.ToInt32(dr["CodCnes"]);
DscEstrutFisicAmbiencia = Convert.ToInt32(dr["DscEstrutFisicAmbiencia"]);
DscAdapDeficFisicIdosos = Convert.ToInt32(dr["DscAdapDeficFisicIdosos"]);
DscEquipamentos = Convert.ToInt32(dr["DscEquipamentos"]);
DscMedicamentos = Convert.ToInt32(dr["DscMedicamentos"]);
VlrLatitude = Convert.ToDecimal(dr["VlrLatitude"]);
VlrLongitude = Convert.ToDecimal(dr["VlrLongitude"]);
NomEstab = dr["NomEstab"].ToString();
DscEndereco = dr["DscEndereco"].ToString();
DscCidade = dr["DscCidade"].ToString();
DscTelefone = dr["DscTelefone"].ToString();
}
}
}
}
var geocode = new GeoCode(VlrLatitude, VlrLongitude);
var score = new Scores(DscEstrutFisicAmbiencia, DscAdapDeficFisicIdosos, DscEquipamentos, DscMedicamentos);
var ubss = new Ubss(id, NomEstab, DscEndereco, DscCidade, DscTelefone, geocode, score);
return(ubss);
}
