public List <Modelo.Bairro> SelectBairros(string rname)
{
Modelo.Bairro aBairro;
List <Modelo.Bairro> aListBairro = new List <Modelo.Bairro>();
SqlConnection conn = new SqlConnection(connectionString);
conn.Open();
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = "SELECT r.id, b.nome FROM Rotas_Bairro rb INNER JOIN rota r ON rb.rotas_id = r.id INNER JOIN Bairro b ON rb.bairro_id = b.id WHERE r.nome = @rname";
cmd.Parameters.AddWithValue("@rname", rname);
SqlDataReader dr = cmd.ExecuteReader();
if (dr.HasRows)
{
while (dr.Read())
{
aBairro = new Modelo.Bairro(
dr["Nome"].ToString()
);
aBairro.Id = int.Parse(dr["id"].ToString());
aListBairro.Add(aBairro);
}
}
dr.Close();
conn.Close();
return(aListBairro);
}
public List <Modelo.Bairro> SearchSelect(string value, string option)
{
if (value == "" || value == null)
{
value = "%";
}
Modelo.Bairro aBairro;
List <Modelo.Bairro> aListBairro = new List <Modelo.Bairro>();
SqlConnection conn = new SqlConnection(connectionString);
conn.Open();
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = "Select * from Bairro WHERE Nome LIKE'%" + value + "%'";
SqlDataReader dr = cmd.ExecuteReader();
if (dr.HasRows)
{
while (dr.Read())
{
aBairro = new Modelo.Bairro(
dr["Nome"].ToString()
);
aBairro.Id = int.Parse(dr["id"].ToString());
aListBairro.Add(aBairro);
}
}
dr.Close();
conn.Close();
return(aListBairro);
}
public List <Modelo.Bairro> SelectAll()
{
Modelo.Bairro aBairro;
List <Modelo.Bairro> aListBairro = new List <Modelo.Bairro>();
SqlConnection conn = new SqlConnection(connectionString);
conn.Open();
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = "Select * from Bairro";
SqlDataReader dr = cmd.ExecuteReader();
if (dr.HasRows)
{
while (dr.Read())
{
aBairro = new Modelo.Bairro(
dr["Nome"].ToString()
);
aBairro.Id = int.Parse(dr["id"].ToString());
aListBairro.Add(aBairro);
}
}
dr.Close();
conn.Close();
return(aListBairro);
}
public Modelo.Bairro Select(int Id)
{
Modelo.Bairro aBairro;
Modelo.Bairro aListBairro = new Modelo.Bairro();
SqlConnection conn = new SqlConnection(connectionString);
conn.Open();
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = "Select * from Bairro Where Id = @Id";
cmd.Parameters.AddWithValue("@Id", Id);
SqlDataReader dr = cmd.ExecuteReader();
if (dr.HasRows)
{
while (dr.Read())
{
aBairro = new Modelo.Bairro(
dr["Nome"].ToString()
);
aBairro.Id = int.Parse(dr["id"].ToString());
aListBairro = aBairro;
}
}
dr.Close();
conn.Close();
return(aListBairro);
}
public void Insert(Modelo.Bairro obj)
{
SqlConnection conn = new SqlConnection(connectionString);
conn.Open();
SqlCommand com = conn.CreateCommand();
SqlCommand cmd = new SqlCommand("INSERT INTO Bairro (Nome) VALUES (@Nome)", conn);
cmd.Parameters.AddWithValue("@Nome", obj.Nome);
cmd.ExecuteNonQuery();
}
public void Update(Modelo.Bairro obj)
{
SqlConnection conn = new SqlConnection(connectionString);
conn.Open();
SqlCommand com = conn.CreateCommand();
SqlCommand cmd = new SqlCommand("UPDATE Bairro SET Nome = @Nome WHERE Id = @Id", conn);
cmd.Parameters.AddWithValue("@Id", obj.Id);
cmd.Parameters.AddWithValue("@Nome", obj.Nome);
cmd.ExecuteNonQuery();
}
public void Delete(Modelo.Bairro obj)
{
SqlConnection conn = new SqlConnection(connectionString);
conn.Open();
SqlCommand com = conn.CreateCommand();
SqlCommand cmd = new SqlCommand("DELETE FROM Rotas_Bairro where bairro_id = @id", conn);
cmd.Parameters.AddWithValue("@id", obj.Id);
cmd.ExecuteNonQuery();
cmd = new SqlCommand("DELETE FROM Bairro WHERE Id = @Id", conn);
cmd.Parameters.AddWithValue("@Id", obj.Id);
cmd.ExecuteNonQuery();
}
