public CargarVereda ActualizarVereda(AgregarVereda vereda, int id)
{
conectar.Open();
SqlCommand cm = new SqlCommand("UPDATE COM.VEREDA SET VER_DESC = @DESCRIPCION, MUN_ID = @MUNICIPIO WHERE VER_ID = @VEREDA", conectar);
cm.Parameters.AddWithValue("DESCRIPCION", vereda.descripcion);
cm.Parameters.AddWithValue("MUNICIPIO", vereda.municipio);
cm.Parameters.AddWithValue("VEREDA", id);
cm.ExecuteNonQuery();
CargarVereda cargarVereda = null;
cm = new SqlCommand(" SELECT" +
" 'ver_id' = V.VER_ID," +
" 'ver_descripcion' = V.VER_DESC," +
" 'mun_id' = M.MUN_ID," +
" 'mun_descripcion' = M.MUN_DESC," +
" 'dep_id' = D.DEP_ID," +
" 'dep_descripcion' = D.DEP_DESC FROM COM.VEREDA V" +
" INNER JOIN COM.MUNICIPIO M ON M.MUN_ID = V.MUN_ID" +
" INNER JOIN COM.DEPARTAMENTO D ON D.DEP_ID = M.DEP_ID" +
" WHERE V.VER_ID = " + id, conectar);
DataSet ds = new DataSet();
SqlDataAdapter da = new SqlDataAdapter(cm);
da.Fill(ds, "vereda");
DataTable dt = ds.Tables[0];
foreach (DataRow dr in dt.Rows)
{
object departamento = new
{
id = Convert.ToInt32(dr["dep_id"]),
descripcion = dr["dep_descripcion"].ToString()
};
object municipio = new
{
id = Convert.ToInt32(dr["mun_id"]),
descripcion = dr["mun_descripcion"].ToString()
};
cargarVereda = new CargarVereda
{
id = Convert.ToInt32(dr["ver_id"]),
descripcion = dr["ver_descripcion"].ToString(),
departamento = departamento,
municipio = municipio
};
}
conectar.Close();
return(cargarVereda);
}
public CargarVereda AgregarVereda(AgregarVereda agregarVereda)
{
conectar.Open();
SqlCommand cm = new SqlCommand("INSERT INTO COM.VEREDA (VER_DESC, MUN_ID) VALUES ('" + agregarVereda.descripcion + "', '" + agregarVereda.municipio + "')", conectar);
cm.ExecuteNonQuery();
CargarVereda cargarVereda = null;
cm = new SqlCommand(" SELECT TOP 1" +
" 'ver_id' = V.VER_ID," +
" 'ver_descripcion' = V.VER_DESC," +
" 'mun_id' = M.MUN_ID," +
" 'mun_descripcion' = M.MUN_DESC," +
" 'dep_id' = D.DEP_ID," +
" 'dep_descripcion' = D.DEP_DESC FROM COM.VEREDA V" +
" INNER JOIN COM.MUNICIPIO M ON M.MUN_ID = V.MUN_ID" +
" INNER JOIN COM.DEPARTAMENTO D ON D.DEP_ID = M.DEP_ID " +
" ORDER BY V.VER_ID DESC", conectar);
DataSet ds = new DataSet();
SqlDataAdapter da = new SqlDataAdapter(cm);
da.Fill(ds, "vereda");
DataTable dt = ds.Tables[0];
foreach (DataRow dr in dt.Rows)
{
object departamento = new
{
id = Convert.ToInt32(dr["dep_id"]),
descripcion = dr["dep_descripcion"].ToString()
};
object municipio = new
{
id = Convert.ToInt32(dr["mun_id"]),
descripcion = dr["mun_descripcion"].ToString()
};
cargarVereda = new CargarVereda
{
id = Convert.ToInt32(dr["ver_id"]),
descripcion = dr["ver_descripcion"].ToString(),
departamento = departamento,
municipio = municipio
};
}
conectar.Close();
return(cargarVereda);
}
public List <CargarVereda> CargarVeredaByMunicipio(int id)
{
List <CargarVereda> veredas = new List <CargarVereda>();
conectar.Open();
SqlCommand cm = new SqlCommand(" SELECT" +
" 'ver_id' = V.VER_ID," +
" 'ver_descripcion' = V.VER_DESC," +
" 'mun_id' = M.MUN_ID," +
" 'mun_descripcion' = M.MUN_DESC," +
" 'dep_id' = D.DEP_ID," +
" 'dep_descripcion' = D.DEP_DESC FROM COM.VEREDA V" +
" INNER JOIN COM.MUNICIPIO M ON M.MUN_ID = V.MUN_ID" +
" INNER JOIN COM.DEPARTAMENTO D ON D.DEP_ID = M.DEP_ID" +
" WHERE M.MUN_ID = " + id, conectar);
DataSet ds = new DataSet();
SqlDataAdapter da = new SqlDataAdapter(cm);
da.Fill(ds, "vereda");
DataTable dt = ds.Tables[0];
foreach (DataRow dr in dt.Rows)
{
object departamento = new
{
id = Convert.ToInt32(dr["dep_id"]),
descripcion = dr["dep_descripcion"].ToString()
};
object municipio = new
{
id = Convert.ToInt32(dr["mun_id"]),
descripcion = dr["mun_descripcion"].ToString()
};
CargarVereda cargarVereda = new CargarVereda
{
id = Convert.ToInt32(dr["ver_id"]),
descripcion = dr["ver_descripcion"].ToString(),
departamento = departamento,
municipio = municipio
};
veredas.Add(cargarVereda);
}
conectar.Close();
return(veredas);
}
