private static void CreateMonth(int year, int month, NpgsqlConnection conn)
{
NpgsqlCommand cmd;
// Remove last year old months
string sqlString = "INSERT INTO dim_meses (codigo_mes, yyyy, nombre_mes, indice_mes) " +
"VALUES(@codigo, @year, @mes, @indice)";
DimMeses dim = new DimMeses();
dim.YYYY = year;
dim.IndiceMes = (Loader.Meses)month;
cmd = new NpgsqlCommand(sqlString, conn);
cmd.Parameters.Add("@codigo", NpgsqlTypes.NpgsqlDbType.Varchar, 4);
cmd.Parameters.Add("@year", NpgsqlTypes.NpgsqlDbType.Varchar, 4);
cmd.Parameters.Add("@mes", NpgsqlTypes.NpgsqlDbType.Varchar, 40);
cmd.Parameters.Add("@indice", NpgsqlTypes.NpgsqlDbType.Integer);
cmd.Parameters["@codigo"].Value = dim.CodigoMes;
cmd.Parameters["@year"].Value = dim.YYYY;
cmd.Parameters["@mes"].Value = dim.Mes;
cmd.Parameters["@indice"].Value = dim.IndiceMes;
cmd.ExecuteNonQuery();
}
public static List <DimMeses> GetMeses(NpgsqlConnection conn)
{
bool didOpenConnection = false;
NpgsqlDataReader dr;
NpgsqlCommand cmd;
List <DimMeses> result = new List <DimMeses>();
string sqlString = "SELECT id_mes, yyyy, indice_mes " +
"FROM dim_meses;";
if (conn == null || !(conn.State == ConnectionState.Open))
{
string connectionString = ConfigurationManager.ConnectionStrings[Config.Common.JASPER].ConnectionString;
conn = new NpgsqlConnection(connectionString);
conn.Open();
didOpenConnection = true;
}
cmd = new NpgsqlCommand(sqlString, conn);
dr = cmd.ExecuteReader();
while (dr.Read())
{
DimMeses dm = new DimMeses();
dm.IdMes = int.Parse(dr["id_mes"].ToString());
dm.YYYY = int.Parse(dr["yyyy"].ToString());
dm.IndiceMes = (Meses)int.Parse(dr["indice_mes"].ToString());
result.Add(dm);
}
dr.Close();
if (didOpenConnection)
{
conn.Close();
}
return(result);
}