public static CategoryScheme GetCategoryInfo(int idCat)
{
DataWrapper dtw = new DataWrapper(DataWrapper.ECONNECTIONTYPE.SQL, DataAccess.SQLConnString_DB.ConnectionString);
if (dtw.TestConnection())
{
dtw.DBConnection.Open();
try
{
System.Data.IDbCommand cmd = dtw.DBConnection.CreateCommand();
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = "proc_GET_CATEGORY_INFO";
System.Data.IDbDataParameter prmIDCat = cmd.CreateParameter();
prmIDCat.DbType = System.Data.DbType.Int32;
prmIDCat.ParameterName = "@IDCat";
prmIDCat.Value = idCat;
cmd.Parameters.Add(prmIDCat);
CategoryScheme cs = new CategoryScheme();
IDataReader reader = cmd.ExecuteReader();
if (reader.Read())
{
cs.Id = reader["ID"].ToString();
cs.AgencyId = reader["Agency"].ToString();
cs.Version = reader["Version"].ToString();
cs.Categories.Add(new Category() { Code = reader["CatCode"].ToString() });
}
dtw.DBConnection.Close();
return cs;
}
catch (Exception ex)
{
dtw.DBConnection.Close();
throw ex;
}
} return null;
}
public static DataTable GetCatDataflows()
{
DataWrapper dtw = new DataWrapper(DataWrapper.ECONNECTIONTYPE.SQL, DataAccess.SQLConnString_DB.ConnectionString);
DataTable dt = new DataTable();
if (dtw.TestConnection())
{
dtw.DBConnection.Open();
try
{
System.Data.IDbCommand cmd = dtw.DBConnection.CreateCommand();
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = "proc_GET_CATDATAFLOWS";
IDataReader reader = cmd.ExecuteReader();
dt.Load(reader);
dtw.DBConnection.Close();
return dt;
}
catch (Exception ex)
{
dtw.DBConnection.Close();
throw ex;
}
} return null;
}
public static DataTable GetCategorySchemes()
{
DataWrapper dtw = new DataWrapper(DataWrapper.ECONNECTIONTYPE.SQL, DataAccess.SQLConnString_DB.ConnectionString);
DataTable dtRet = new DataTable();
if (dtw.TestConnection())
{
dtw.DBConnection.Open();
try
{
System.Data.IDbCommand cmd = dtw.DBConnection.CreateCommand();
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = "proc_OTF_GET_CATEGORY_SCHEMES";
dtRet.Load(cmd.ExecuteReader());
dtw.DBConnection.Close();
return dtRet;
}
catch (Exception ex)
{
dtw.DBConnection.Close();
throw ex;
}
}
return dtRet;
}
private static int Insert_DataFilts(int idset, string sql_h, string sql_d, string sql_w)
{
DataWrapper dtw = new DataWrapper(DataWrapper.ECONNECTIONTYPE.SQL, DataAccess.SQLConnString_DB.ConnectionString);
if (dtw.TestConnection())
{
dtw.DBConnection.Open();
try
{
////////////////////////
int id = -1;
System.Data.IDbCommand cmd = dtw.DBConnection.CreateCommand();
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = "dbo.proc_INSERT_DATA_FILTS";
System.Data.IDbDataParameter _param_idset = cmd.CreateParameter();
_param_idset.DbType = System.Data.DbType.Int32;
_param_idset.ParameterName = "IDSet";
_param_idset.Value = idset;
cmd.Parameters.Add(_param_idset);
System.Data.IDbDataParameter _param_sql_h = cmd.CreateParameter();
_param_sql_h.DbType = System.Data.DbType.String;
_param_sql_h.ParameterName = "Sql_part_h";
_param_sql_h.Value = sql_h;
cmd.Parameters.Add(_param_sql_h);
System.Data.IDbDataParameter _param_sql_d = cmd.CreateParameter();
_param_sql_d.DbType = System.Data.DbType.String;
_param_sql_d.ParameterName = "Sql_part_d";
_param_sql_d.Value = sql_d;
cmd.Parameters.Add(_param_sql_d);
System.Data.IDbDataParameter _param_sql_w = cmd.CreateParameter();
_param_sql_w.DbType = System.Data.DbType.String;
_param_sql_w.ParameterName = "Sql_part_w";
_param_sql_w.Value = sql_w;
cmd.Parameters.Add(_param_sql_w);
object result = cmd.ExecuteScalar();
if (result != null)
id = int.Parse(result.ToString());
////////////////////////
dtw.DBConnection.Close();
return id;
}
catch
{
dtw.DBConnection.Close();
return -1;
}
}
return -1;
}
public static int Delete_Dataflow(int dataflow_id)
{
DataWrapper dtw = new DataWrapper(DataWrapper.ECONNECTIONTYPE.SQL, DataSDMX.SQLConnString_DB.ConnectionString);
if (dtw.TestConnection())
{
try
{
dtw.DBConnection.Open();
////////////////////////
System.Data.IDbCommand cmd = dtw.DBConnection.CreateCommand();
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = "dbo.DELETE_DATAFLOW";
System.Data.IDbDataParameter _dataflow_id = cmd.CreateParameter();
_dataflow_id.DbType = System.Data.DbType.Int32;
_dataflow_id.ParameterName = "ART_ID";
_dataflow_id.Value = dataflow_id;
cmd.Parameters.Add(_dataflow_id);
cmd.ExecuteNonQuery();
////////////////////////
dtw.DBConnection.Close();
return 1;
}
catch// (Exception ex)
{
return 0;
}
}
return 0;
}
public static int Get_IDDataflow(SDMXIdentifier sdmxKey)
{
DataWrapper dtw = new DataWrapper(DataWrapper.ECONNECTIONTYPE.SQL, DataSDMX.SQLConnString_DB.ConnectionString);
int df_id = -1;
if (dtw.TestConnection())
{
try
{
dtw.DBConnection.Open();
////////////////////////
System.Data.IDbCommand cmd = dtw.DBConnection.CreateCommand();
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = "SELECT ART_ID " +
"FROM ARTEFACT A " +
"INNER JOIN DATAFLOW B ON A.ART_ID = B.DF_ID " +
"WHERE ID='" + sdmxKey.id +
"' AND AGENCY='" + sdmxKey.agencyid +
"' AND CAST(VERSION1 as char(1))+CASE ISNULL(VERSION2,-1) WHEN -1 THEN '' ELSE '.' END+CAST(VERSION2 as char(1))+CASE ISNULL(VERSION3,-1) WHEN -1 THEN '' ELSE '.' END+CASE ISNULL(VERSION3,-1) WHEN -1 THEN '' ELSE CAST(VERSION3 as char(1)) END ='" + sdmxKey.version + "'";
object result = cmd.ExecuteScalar();
if (result != null)
df_id = int.Parse(result.ToString());
////////////////////////
dtw.DBConnection.Close();
return df_id;
}
catch //(Exception ex)
{
return df_id;
}
}
return df_id;
}
public static DataTable GetCategoriesSchemes()
{
DataWrapper dtw = new DataWrapper(DataWrapper.ECONNECTIONTYPE.SQL, DataSDMX.SQLConnString_DB.ConnectionString);
if (dtw.TestConnection())
{
dtw.DBConnection.Open();
string sql = "SELECT " +
" 'CS_LOCAL_ID' = a.CAT_SCH_ID, " +
" 'CS_ID' = e.ID, " +
" 'CS_AGENCY' = e.AGENCY, " +
" 'CS_VERSION' = cast(e.VERSION1 as varchar(2)) +'.'+ cast(e.VERSION2 as varchar(2)), " +
" 'CS_LANG' = F.LANGUAGE, 'CS_TEXT' = F.TEXT, " +
" b.CAT_ID, b.PARENT_CAT_ID, 'CAT_CODE' = c.ID, " +
" 'CAT_LANG' = d.LANGUAGE, 'CAT_TEXT' = d.TEXT, " +
" TBORDER.TEXT CAT_ORD " +
"FROM CATEGORY_SCHEME A " +
" INNER JOIN CATEGORY B ON " +
" B.CAT_SCH_ID = A.CAT_SCH_ID " +
" INNER JOIN ITEM C ON " +
" C.ITEM_ID = B.CAT_ID " +
" INNER JOIN LOCALISED_STRING D ON " +
" D.ITEM_ID = C.ITEM_ID " +
" INNER JOIN ARTEFACT E ON " +
" E.ART_ID = A.CAT_SCH_ID " +
" INNER JOIN LOCALISED_STRING F ON " +
" F.ART_ID = E.ART_ID " +
" LEFT OUTER JOIN " +
" ( " +
" SELECT Z.ITEM_ID, Y.TEXT " +
" FROM ITEM_ANNOTATION Z " +
" INNER JOIN ANNOTATION X ON " +
" Z.ANN_ID = X.ANN_ID " +
" INNER JOIN ANNOTATION_TEXT Y ON " +
" Y.ANN_ID = X.ANN_ID " +
" WHERE X.TYPE IN('@ORDER@','CategoryScheme_node_order') " +
" AND Y.LANGUAGE = 'en' " +
" ) TBORDER ON " +
" TBORDER.ITEM_ID = C.ITEM_ID " +
"WHERE D.TYPE = 'Name' " +
" AND F.TYPE = 'Name' ";
try
{
System.Data.IDbCommand cmd = dtw.DBConnection.CreateCommand();
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = sql;
IDataReader reader = cmd.ExecuteReader();
DataTable dtRet = new DataTable();
dtRet.Load(reader);
dtw.DBConnection.Close();
return dtRet;
}
catch (Exception ex)
{
dtw.DBConnection.Close();
throw ex;
}
} return null;
}
public static int Delete_Dataflow(int dataflow_id)
{
DataWrapper dtw = new DataWrapper(DataWrapper.ECONNECTIONTYPE.SQL, DataSDMX.SQLConnString_DB.ConnectionString);
System.Data.IDbCommand cmd = null;
if (dtw.TestConnection())
{
try
{
dtw.DBConnection.Open();
string sqlDelteDf,sqlDeleteCateg;
sqlDeleteCateg = String.Format("DELETE ARTEFACT " +
"FROM ARTEFACT A " +
" INNER JOIN CATEGORISATION B ON " +
" A.ART_ID = B.CATN_ID " +
"WHERE B.ART_ID = {0} ", dataflow_id);
sqlDelteDf = String.Format("DELETE ARTEFACT " +
"WHERE ART_ID = {0} ", dataflow_id);
cmd = dtw.DBConnection.CreateCommand();
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = "BEGIN TRAN";
cmd.ExecuteNonQuery();
cmd.CommandText = sqlDeleteCateg;
cmd.ExecuteNonQuery();
cmd.CommandText = sqlDelteDf;
cmd.ExecuteNonQuery();
cmd.CommandText = "COMMIT TRAN";
cmd.ExecuteNonQuery();
dtw.DBConnection.Close();
return 1;
}
catch(Exception ex)
{
if (dtw.DBConnection.State == ConnectionState.Open)
{
cmd.CommandText = "ROLLBACK TRAN";
cmd.ExecuteNonQuery();
}
throw ex;
}
}
return 0;
}
public static void Set_DataflowProduction(int dataflow_id, bool active)
{
DataWrapper dtw = new DataWrapper(DataWrapper.ECONNECTIONTYPE.SQL, DataSDMX.SQLConnString_DB.ConnectionString);
if (dtw.TestConnection())
{
try
{
dtw.DBConnection.Open();
////////////////////////
System.Data.IDbCommand cmd = dtw.DBConnection.CreateCommand();
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = "UPDATE dbo.DATAFLOW SET PRODUCTION = " + (int)((active) ? DATAFLOW_PRODUCTION_STATUS.IN_PRODUCTION : DATAFLOW_PRODUCTION_STATUS.OUT_PRODUCTION) + " WHERE dbo.DATAFLOW.DF_ID=" + dataflow_id;
cmd.ExecuteNonQuery();
////////////////////////
dtw.DBConnection.Close();
}
catch (Exception ex)
{
Console.Write(ex.Message);
}
}
}
public static int Get_IDCatFromCategorisation(SDMXIdentifier sdmxKey)
{
DataWrapper dtw = new DataWrapper(DataWrapper.ECONNECTIONTYPE.SQL, DataSDMX.SQLConnString_DB.ConnectionString);
int cat_id = -1;
if (dtw.TestConnection())
{
try
{
dtw.DBConnection.Open();
string[] version = sdmxKey.version.Split('.');
System.Data.IDbCommand cmd = dtw.DBConnection.CreateCommand();
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = String.Format("SELECT B.CAT_ID " +
"FROM ARTEFACT A " +
" INNER JOIN CATEGORISATION B ON " +
" B.CATN_ID = A.ART_ID " +
"WHERE A.ID = '{0}' " +
" AND A.AGENCY = '{1}' " +
" AND VERSION1 = {2} " +
" AND VERSION2 = {3} ",
sdmxKey.id, sdmxKey.agencyid, version[0], version[1]);
object result = cmd.ExecuteScalar();
if (result != null)
cat_id = int.Parse(result.ToString());
dtw.DBConnection.Close();
return cat_id;
}
catch (Exception ex)
{
throw ex;
//return cat_id;
}
}
return cat_id;
}
public static Dictionary<string, string> GetCodesFromComponents(int idSet, string colName, string locale)
{
Dictionary<string, string> dictCodes = new Dictionary<string, string>();
DataWrapper dtw = new DataWrapper(DataWrapper.ECONNECTIONTYPE.SQL, DataAccess.SQLConnString_DB.ConnectionString);
if (dtw.TestConnection())
{
dtw.DBConnection.Open();
try
{
System.Data.IDbCommand cmd = dtw.DBConnection.CreateCommand();
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = "sp_GetCodesFromComponents";
System.Data.IDbDataParameter prmIDSet = cmd.CreateParameter();
prmIDSet.DbType = System.Data.DbType.Int32;
prmIDSet.ParameterName = "IDSET";
prmIDSet.Value = idSet;
cmd.Parameters.Add(prmIDSet);
System.Data.IDbDataParameter prmColName = cmd.CreateParameter();
prmColName.DbType = System.Data.DbType.String;
prmColName.ParameterName = "COLNAME";
prmColName.Value = colName;
cmd.Parameters.Add(prmColName);
System.Data.IDbDataParameter prmLocale = cmd.CreateParameter();
prmLocale.DbType = System.Data.DbType.String;
prmLocale.ParameterName = "LOCALE";
prmLocale.Value = locale;
cmd.Parameters.Add(prmLocale);
List<String> lCodes = new List<String>();
IDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
dictCodes.Add(reader["Code"].ToString(), reader["Codename"].ToString());
}
dtw.DBConnection.Close();
return dictCodes;
}
catch (Exception ex)
{
dtw.DBConnection.Close();
throw ex;
}
} return null;
}
public static List<SDMXIdentifier> Get_CategorisationIdentifierFromIdFlow(int idFlow)
{
DataWrapper dtw = new DataWrapper(DataWrapper.ECONNECTIONTYPE.SQL, DataSDMX.SQLConnString_DB.ConnectionString);
List<SDMXIdentifier> lIde = new List<SDMXIdentifier>();
if (dtw.TestConnection())
{
try
{
dtw.DBConnection.Open();
System.Data.IDbCommand cmd = dtw.DBConnection.CreateCommand();
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = "SELECT ID,AGENCY, " +
" 'VERSION' = CAST(VERSION1 AS varchar(2)) +'.'+ CAST(VERSION2 AS varchar(2)) " +
"FROM ARTEFACT A " +
" INNER JOIN CATEGORISATION B ON " +
" B.CATN_ID = A.ART_ID " +
"WHERE B.ART_ID="+ idFlow.ToString();
IDataReader reader = cmd.ExecuteReader();
while(reader.Read())
{
lIde.Add(new SDMXIdentifier() { id = reader["ID"].ToString(),
agencyid = reader["AGENCY"].ToString(),
version = reader["VERSION"].ToString()});
}
dtw.DBConnection.Close();
return lIde;
}
catch (Exception ex)
{
throw ex;
}
}
return lIde;
}
// DB SDMX Query
public static int Get_IDCategory(string urn)
{
DataWrapper dtw = new DataWrapper(DataWrapper.ECONNECTIONTYPE.SQL, DataSDMX.SQLConnString_DB.ConnectionString);
int idCat = -1;
if (dtw.TestConnection())
{
try
{
dtw.DBConnection.Open();
////////////////////////
int indexS = urn.LastIndexOf('.') + 1;
string strCategory = urn.Substring(indexS, urn.Length - indexS);
System.Data.IDbCommand cmd = dtw.DBConnection.CreateCommand();
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = "SELECT * FROM [dbo].[CATEGORY] INNER JOIN dbo.ITEM ON [dbo].[CATEGORY].CAT_ID=ITEM.ITEM_ID WHERE ID='" + strCategory + "'";
System.Data.IDataReader reader = cmd.ExecuteReader();
if (reader.Read())
{
var _idStr = reader.GetInt64(reader.GetOrdinal("CAT_ID"));
idCat = int.Parse(_idStr.ToString());
}
////////////////////////
dtw.DBConnection.Close();
return idCat;
}
catch// (Exception ex)
{
return idCat;
}
} return idCat;
}
public static List<int> GetListCubeViewIdFromIDCube(int idSet)
{
DataWrapper dtw = new DataWrapper(DataWrapper.ECONNECTIONTYPE.SQL, DataAccess.SQLConnString_DB.ConnectionString);
List<int> lID = new List<int>();
if (!dtw.TestConnection())
throw new Exception("Connection Error!");
dtw.DBConnection.Open();
try
{
System.Data.IDbCommand cmd = dtw.DBConnection.CreateCommand();
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = "SELECT IDSet " +
" FROM CubeViewRelation " +
" WHERE OriginalIDSet =" + idSet.ToString();
IDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
lID.Add((int)reader["IDSet"]);
}
dtw.DBConnection.Close();
return lID;
}
catch (Exception ex)
{
dtw.DBConnection.Close();
throw ex;
}
}
public static int GetOriginalIDSet(int idSet)
{
DataWrapper dtw = new DataWrapper(DataWrapper.ECONNECTIONTYPE.SQL, DataAccess.SQLConnString_DB.ConnectionString);
SDMXIdentifier identifier = null;
int originalIDSet = 0;
if (!dtw.TestConnection())
throw new Exception("Connection Error!");
dtw.DBConnection.Open();
try
{
System.Data.IDbCommand cmd = dtw.DBConnection.CreateCommand();
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = "SELECT OriginalIDSet " +
" FROM CubeViewRelation " +
" WHERE IDSet =" + idSet.ToString();
IDataReader reader = cmd.ExecuteReader();
if (reader.Read())
{
originalIDSet = (int)reader["OriginalIDSet"];
}
dtw.DBConnection.Close();
return originalIDSet;
}
catch (Exception ex)
{
dtw.DBConnection.Close();
throw ex;
}
}
//public static int GetCubeCategoryID(int idSet)
//{
// DataWrapper dtw = new DataWrapper(DataWrapper.ECONNECTIONTYPE.SQL, DataAccess.SQLConnString_DB.ConnectionString);
// if (!dtw.TestConnection())
// throw new Exception("Connection Error!");
// int idCat = 0;
// dtw.DBConnection.Open();
// try
// {
// System.Data.IDbCommand cmd = dtw.DBConnection.CreateCommand();
// cmd.CommandType = System.Data.CommandType.Text;
// cmd.CommandText = "SELECT IDCat " +
// " FROM CatSet " +
// " WHERE IDSet =" + idSet.ToString();
// IDataReader reader = cmd.ExecuteReader();
// if (reader.Read())
// {
// idCat = (int)reader["IDCat"];
// }
// dtw.DBConnection.Close();
// return idCat;
// }
// catch (Exception ex)
// {
// dtw.DBConnection.Close();
// throw ex;
// }
//}
public static int GetIDCatFromIDSet(int idSet)
{
DataWrapper dtw = new DataWrapper(DataWrapper.ECONNECTIONTYPE.SQL, DataAccess.SQLConnString_DB.ConnectionString);
int idCat = 0;
if (!dtw.TestConnection())
throw new Exception("Connection Error!");
dtw.DBConnection.Open();
try
{
System.Data.IDbCommand cmd = dtw.DBConnection.CreateCommand();
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = "SELECT IDCat " +
" FROM CatSet " +
" WHERE IDSet =" + idSet.ToString();
IDataReader reader = cmd.ExecuteReader();
if (reader.Read())
{
idCat = Int32.Parse(reader["IDCat"].ToString());
}
dtw.DBConnection.Close();
return idCat;
}
catch (Exception ex)
{
dtw.DBConnection.Close();
throw ex;
}
}
public static SDMXIdentifier GetIdentifierFromIDSet(int idSet)
{
DataWrapper dtw = new DataWrapper(DataWrapper.ECONNECTIONTYPE.SQL, DataAccess.SQLConnString_DB.ConnectionString);
SDMXIdentifier identifier = null;
if (!dtw.TestConnection())
throw new Exception("Connection Error!");
dtw.DBConnection.Open();
try
{
System.Data.IDbCommand cmd = dtw.DBConnection.CreateCommand();
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = "SELECT AgencyID , " +
" ID , " +
" Version " +
" FROM dbo.CatDataFlow " +
" WHERE IDSet =" + idSet.ToString();
IDataReader reader = cmd.ExecuteReader();
if (reader.Read())
{
identifier = new SDMXIdentifier() { id = reader["ID"].ToString(), agencyid = reader["AgencyID"].ToString(), version = reader["Version"].ToString() };
}
dtw.DBConnection.Close();
return identifier;
}
catch (Exception ex)
{
dtw.DBConnection.Close();
throw ex;
}
}
public static DataTable GetCubeViewPreview(int idSet)
{
//CategoryImportEngine
DataWrapper dtw = new DataWrapper(DataWrapper.ECONNECTIONTYPE.SQL, DataAccess.SQLConnString_DB.ConnectionString);
DataTable dt = new DataTable();
if (dtw.TestConnection())
{
dtw.DBConnection.Open();
try
{
System.Data.IDbCommand cmd = dtw.DBConnection.CreateCommand();
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = string.Format("SELECT * FROM Dataset_{0}_ViewAllData", idSet);
IDataReader reader = cmd.ExecuteReader();
dt.Load(reader);
dtw.DBConnection.Close();
return dt;
}
catch (Exception ex)
{
dtw.DBConnection.Close();
throw ex;
}
} return null;
}
public static List<Cube> GetCubeList(bool isCube, string localize)
{
DataWrapper dtw = new DataWrapper(DataWrapper.ECONNECTIONTYPE.SQL, DataAccess.SQLConnString_DB.ConnectionString);
if (dtw.TestConnection())
{
dtw.DBConnection.Open();
try
{
System.Data.IDbCommand cmd = dtw.DBConnection.CreateCommand();
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = "dbo.proc_GET_CUBE_LIST";
System.Data.IDbDataParameter prmIsCube = cmd.CreateParameter();
prmIsCube.DbType = System.Data.DbType.Boolean;
prmIsCube.ParameterName = "ISCUBE";
prmIsCube.Value = isCube;
cmd.Parameters.Add(prmIsCube);
System.Data.IDbDataParameter prmLocalize = cmd.CreateParameter();
prmLocalize.DbType = System.Data.DbType.String;
prmLocalize.ParameterName = "LOCALIZE";
prmLocalize.Value = localize;
cmd.Parameters.Add(prmLocalize);
List<Cube> lCube = new List<Cube>();
IDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
Cube cube = new Cube((int)reader["IDSet"],
reader["Code"].ToString(),
DateTime.Parse(reader["LastUpdated"].ToString()),
reader["UnitDimension"] == DBNull.Value ? null : reader["UnitDimension"].ToString(),
(int)reader["IDCat"],
(bool)reader["ISCube"]
);
lCube.Add(cube);
}
dtw.DBConnection.Close();
return lCube;
}
catch (Exception ex)
{
dtw.DBConnection.Close();
throw ex;
}
} return null;
}
public static DataTable GetCubeColumnsList(int idSet)
{
DataWrapper dtw = new DataWrapper(DataWrapper.ECONNECTIONTYPE.SQL, DataAccess.SQLConnString_DB.ConnectionString);
if (dtw.TestConnection())
{
dtw.DBConnection.Open();
try
{
System.Data.IDbCommand cmd = dtw.DBConnection.CreateCommand();
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = "dbo.proc_GET_CUBE_COLUMNS_LIST";
System.Data.IDbDataParameter prmIdSet = cmd.CreateParameter();
prmIdSet.DbType = System.Data.DbType.Int32;
prmIdSet.ParameterName = "IDSET";
prmIdSet.Value = idSet;
cmd.Parameters.Add(prmIdSet);
IDataReader reader = cmd.ExecuteReader();
DataTable dtRet = new DataTable();
dtRet.Load(reader);
dtw.DBConnection.Close();
return dtRet;
}
catch (Exception ex)
{
dtw.DBConnection.Close();
throw ex;
}
} return null;
}
public static int GetDataflowID(SDMXIdentifier sdmxKey)
{
DataWrapper dtw = new DataWrapper(DataWrapper.ECONNECTIONTYPE.SQL, DataSDMX.SQLConnString_DB.ConnectionString);
int dfID = 0;
if (dtw.TestConnection())
{
try
{
dtw.DBConnection.Open();
string sql = string.Format("SELECT a.ART_ID " +
"FROM dbo.ARTEFACT a " +
" INNER JOIN dbo.DATAFLOW b ON " +
" b.DF_ID = a.ART_ID " +
"WHERE A.ID = '{0}' " +
" AND A.VERSION1 = '{1}' " +
" AND A.VERSION2 = '{2}' " +
" AND AGENCY = '{3}' ",
sdmxKey.id,
sdmxKey.version.Substring(0, sdmxKey.version.IndexOf(".")),
sdmxKey.version.Substring(sdmxKey.version.IndexOf(".") + 1),
sdmxKey.agencyid);
System.Data.IDbCommand cmd = dtw.DBConnection.CreateCommand();
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = sql;
System.Data.IDataReader reader = cmd.ExecuteReader();
if (reader.Read())
{
dfID = Int32.Parse(reader["ART_ID"].ToString());
}
dtw.DBConnection.Close();
return dfID;
}
catch (Exception ex)
{
throw ex;
}
}
return 0;
}
/// <summary>
/// Insert_DataFacts
/// </summary>
/// <param name="idset">ID of Structure</param>
/// <param name="sid">SID from Filt table</param>
/// <param name="idtime">ID Time period</param>
/// <param name="value">Value in float can be NULL</param>
/// <param name="sql_h">string SQL part esp: tablename1,tablename2,tablename3</param>
/// <param name="sql_d">string SQL part esp: value1,value2,value3</param>
/// <returns>Return new ID of Fact | -1 if error </returns>
private static int Insert_DataFacts(int idset, int sid, int idtime, object value, string sql_h, string sql_d)
{
DataWrapper dtw = new DataWrapper(DataWrapper.ECONNECTIONTYPE.SQL, DataAccess.SQLConnString_DB.ConnectionString);
if (dtw.TestConnection())
{
dtw.DBConnection.Open();
try
{
////////////////////////
int id = -1;
System.Data.IDbCommand cmd = dtw.DBConnection.CreateCommand();
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = "dbo.proc_INSERT_DATA_FACTS";
System.Data.IDbDataParameter _param_idset = cmd.CreateParameter();
_param_idset.DbType = System.Data.DbType.Int32;
_param_idset.ParameterName = "IDSet";
_param_idset.Value = idset;
cmd.Parameters.Add(_param_idset);
System.Data.IDbDataParameter _param_sid = cmd.CreateParameter();
_param_sid.DbType = System.Data.DbType.Int32;
_param_sid.ParameterName = "SID";
_param_sid.Value = sid;
cmd.Parameters.Add(_param_sid);
System.Data.IDbDataParameter _param_time = cmd.CreateParameter();
_param_time.DbType = System.Data.DbType.Int32;
_param_time.ParameterName = "IDTime";
_param_time.Value = idtime;
cmd.Parameters.Add(_param_time);
if (value != null)
{
System.Data.IDbDataParameter _param_value = cmd.CreateParameter();
_param_value.DbType = System.Data.DbType.Double;
_param_value.ParameterName = "Value";
_param_value.Value = value;
cmd.Parameters.Add(_param_value);
}
System.Data.IDbDataParameter _param_sql_h = cmd.CreateParameter();
_param_sql_h.DbType = System.Data.DbType.String;
_param_sql_h.ParameterName = "Sql_part_h";
_param_sql_h.Value = sql_h;
cmd.Parameters.Add(_param_sql_h);
System.Data.IDbDataParameter _param_sql_d = cmd.CreateParameter();
_param_sql_d.DbType = System.Data.DbType.String;
_param_sql_d.ParameterName = "Sql_part_d";
_param_sql_d.Value = sql_d;
cmd.Parameters.Add(_param_sql_d);
id = (int)cmd.ExecuteScalar();
////////////////////////
dtw.DBConnection.Close();
return id;
}
catch
{
dtw.DBConnection.Close();
return -1;
}
}
return -1;
}
public static IDataStructureObject GetDSDFromDataflowID(int dfID)
{
DataWrapper dtw = new DataWrapper(DataWrapper.ECONNECTIONTYPE.SQL, DataSDMX.SQLConnString_DB.ConnectionString);
SDMXIdentifier sdmxKey;
if (dtw.TestConnection())
{
try
{
dtw.DBConnection.Open();
string sql = string.Format("SELECT b.ID,b.AGENCY, " +
"'Version'=CAST(b.VERSION1 AS VARCHAR(2)) + '.' + CAST(b.VERSION2 AS VARCHAR(2)) " +
"FROM DATAFLOW a " +
" INNER JOIN ARTEFACT b ON " +
" a.DSD_ID = b.ART_ID " +
"WHERE DF_ID = {0} ",
dfID.ToString());
System.Data.IDbCommand cmd = dtw.DBConnection.CreateCommand();
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = sql;
System.Data.IDataReader reader = cmd.ExecuteReader();
if (reader.Read())
{
sdmxKey = new SDMXIdentifier()
{
id = reader["ID"].ToString(),
agencyid = reader["AGENCY"].ToString(),
version = reader["Version"].ToString()
};
return GetDSD(sdmxKey, false);
}
dtw.DBConnection.Close();
}
catch (Exception ex)
{
throw ex;
}
}
return null;
}
public static int Get_IDDsd(int dataflow_id)
{
DataWrapper dtw = new DataWrapper(DataWrapper.ECONNECTIONTYPE.SQL, DataSDMX.SQLConnString_DB.ConnectionString);
int id_dsd = -1;
if (dtw.TestConnection())
{
try
{
dtw.DBConnection.Open();
////////////////////////
System.Data.IDbCommand cmd = dtw.DBConnection.CreateCommand();
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = "SELECT * FROM dbo.DATAFLOW WHERE DF_ID=" + dataflow_id;
System.Data.IDataReader reader = cmd.ExecuteReader();
if (reader.Read())
{
id_dsd = reader.GetInt32(reader.GetOrdinal("DSD_ID"));
}
////////////////////////
dtw.DBConnection.Close();
return id_dsd;
}
catch// (Exception ex)
{
return id_dsd;
}
} return id_dsd;
}
public static bool Get_DataflowProduction(int dataflow_id)
{
DataWrapper dtw = new DataWrapper(DataWrapper.ECONNECTIONTYPE.SQL, DataSDMX.SQLConnString_DB.ConnectionString);
if (dtw.TestConnection())
{
try
{
dtw.DBConnection.Open();
////////////////////////
System.Data.IDbCommand cmd = dtw.DBConnection.CreateCommand();
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = "SELECT dbo.DATAFLOW.PRODUCTION FROM dbo.DATAFLOW WHERE dbo.DATAFLOW.DF_ID=" + dataflow_id;
var result = cmd.ExecuteScalar();
////////////////////////
dtw.DBConnection.Close();
return (int.Parse(result.ToString()) == (int)DATAFLOW_PRODUCTION_STATUS.IN_PRODUCTION);
}
catch// (Exception ex)
{
return false;
}
}
return false;
}
public static int InsertCategorisation(SDMXIdentifier sdmxKey, int idFlow, int idCat, string uri)
{
DataWrapper dtw = new DataWrapper(DataWrapper.ECONNECTIONTYPE.SQL, DataSDMX.SQLConnString_DB.ConnectionString);
if (dtw.TestConnection())
{
try
{
dtw.DBConnection.Open();
System.Data.IDbCommand cmd_cat = dtw.DBConnection.CreateCommand();
cmd_cat.CommandType = System.Data.CommandType.StoredProcedure;
cmd_cat.CommandText = "dbo.INSERT_CATEGORISATION";
System.Data.IDbDataParameter _id_df = cmd_cat.CreateParameter();
_id_df.DbType = System.Data.DbType.String;
_id_df.ParameterName = "p_id";
_id_df.Value = sdmxKey.id;
cmd_cat.Parameters.Add(_id_df);
System.Data.IDbDataParameter _version_df = cmd_cat.CreateParameter();
_version_df.DbType = System.Data.DbType.String;
_version_df.ParameterName = "p_version";
_version_df.Value = sdmxKey.version;
cmd_cat.Parameters.Add(_version_df);
System.Data.IDbDataParameter _agency_df = cmd_cat.CreateParameter();
_agency_df.DbType = System.Data.DbType.String;
_agency_df.ParameterName = "p_agency";
_agency_df.Value = sdmxKey.agencyid;
cmd_cat.Parameters.Add(_agency_df);
System.Data.IDbDataParameter _uri_df = cmd_cat.CreateParameter();
_uri_df.DbType = System.Data.DbType.String;
_uri_df.ParameterName = "p_uri";
_uri_df.Value = uri;
cmd_cat.Parameters.Add(_uri_df);
System.Data.IDbDataParameter _art_id = cmd_cat.CreateParameter();
_art_id.DbType = System.Data.DbType.Int32;
_art_id.ParameterName = "p_art_id";
_art_id.Value = idFlow;
cmd_cat.Parameters.Add(_art_id);
System.Data.IDbDataParameter _cat_id = cmd_cat.CreateParameter();
_cat_id.DbType = System.Data.DbType.Int32;
_cat_id.ParameterName = "p_cat_id";
_cat_id.Value = idCat;
cmd_cat.Parameters.Add(_cat_id);
System.Data.IDbDataParameter _pk_cat = cmd_cat.CreateParameter();
_pk_cat.DbType = System.Data.DbType.Int32;
_pk_cat.ParameterName = "p_pk";
_pk_cat.Direction = System.Data.ParameterDirection.Output;
cmd_cat.Parameters.Add(_pk_cat);
cmd_cat.ExecuteNonQuery();
return (int)_pk_cat.Value;
}
catch (Exception ex)
{
throw ex;
}
}
return -1;
}
public static int Get_IDCatFromCategorisation(int idFlow)
{
DataWrapper dtw = new DataWrapper(DataWrapper.ECONNECTIONTYPE.SQL, DataSDMX.SQLConnString_DB.ConnectionString);
int cat_id = -1;
if (dtw.TestConnection())
{
try
{
dtw.DBConnection.Open();
System.Data.IDbCommand cmd = dtw.DBConnection.CreateCommand();
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = "SELECT CAT_ID " +
" FROM CATEGORISATION " +
" WHERE ART_ID =" + idFlow.ToString() ;
object result = cmd.ExecuteScalar();
if (result != null)
cat_id = int.Parse(result.ToString());
dtw.DBConnection.Close();
return cat_id;
}
catch (Exception ex)
{
throw ex;
//return cat_id;
}
}
return cat_id;
}
public static int Insert_Dataflow(SDMXIdentifier sdmxKey, int dsd_id, int idCat, List<TextTypeWrapper> names, List<TextTypeWrapper> descs, string uri)
{
DataWrapper dtw = new DataWrapper(DataWrapper.ECONNECTIONTYPE.SQL, DataSDMX.SQLConnString_DB.ConnectionString);
int idFlow = -1;
if (dtw.TestConnection())
{
try
{
dtw.DBConnection.Open();
////////////////////////
#region Insert dataflow
System.Data.IDbCommand cmd = dtw.DBConnection.CreateCommand();
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = "dbo.INSERT_DATAFLOW";
System.Data.IDbDataParameter _id = cmd.CreateParameter();
_id.DbType = System.Data.DbType.String;
_id.ParameterName = "p_id";
_id.Value = sdmxKey.id;
cmd.Parameters.Add(_id);
System.Data.IDbDataParameter _version = cmd.CreateParameter();
_version.DbType = System.Data.DbType.String;
_version.ParameterName = "p_version";
_version.Value = sdmxKey.version;
cmd.Parameters.Add(_version);
System.Data.IDbDataParameter _agency = cmd.CreateParameter();
_agency.DbType = System.Data.DbType.String;
_agency.ParameterName = "p_agency";
_agency.Value = sdmxKey.agencyid;
cmd.Parameters.Add(_agency);
System.Data.IDbDataParameter _uri = cmd.CreateParameter();
_uri.DbType = System.Data.DbType.String;
_uri.ParameterName = "p_uri";
_uri.Value = uri;
cmd.Parameters.Add(_uri);
System.Data.IDbDataParameter _dsd_id = cmd.CreateParameter();
_dsd_id.DbType = System.Data.DbType.Int32;
_dsd_id.ParameterName = "p_dsd_id";
_dsd_id.Value = dsd_id;
cmd.Parameters.Add(_dsd_id);
System.Data.IDbDataParameter _is_final = cmd.CreateParameter();
_is_final.DbType = System.Data.DbType.Int32;
_is_final.ParameterName = "p_is_final";
_is_final.Value = 0;
cmd.Parameters.Add(_is_final);
int pk = -1;
System.Data.IDbDataParameter _pk = cmd.CreateParameter();
_pk.DbType = System.Data.DbType.Int32;
_pk.ParameterName = "p_pk";
_pk.Value = pk;
_pk.Direction = System.Data.ParameterDirection.Output;
cmd.Parameters.Add(_pk);
object obj = cmd.ExecuteScalar();
#endregion
idFlow = int.Parse(_pk.Value.ToString());
#region Insert Localised string
System.Data.IDbCommand cmd_localised = dtw.DBConnection.CreateCommand();
cmd_localised.CommandType = System.Data.CommandType.StoredProcedure;
cmd_localised.CommandText = "dbo.INSERT_LOCALISED_STRING";
System.Data.IDbDataParameter _p_art_id = cmd_localised.CreateParameter();
_p_art_id.DbType = System.Data.DbType.Int32;
_p_art_id.ParameterName = "p_art_id";
_p_art_id.Value = idFlow;
cmd_localised.Parameters.Add(_p_art_id);
System.Data.IDbDataParameter _p_text = cmd_localised.CreateParameter();
_p_text.DbType = System.Data.DbType.String;
_p_text.ParameterName = "p_text";
cmd_localised.Parameters.Add(_p_text);
System.Data.IDbDataParameter _p_type = cmd_localised.CreateParameter();
_p_type.DbType = System.Data.DbType.String;
_p_type.ParameterName = "p_type";
cmd_localised.Parameters.Add(_p_type);
System.Data.IDbDataParameter _p_language = cmd_localised.CreateParameter();
_p_language.DbType = System.Data.DbType.String;
_p_language.ParameterName = "p_language";
cmd_localised.Parameters.Add(_p_language);
int pk_localised = -1;
System.Data.IDbDataParameter _pk_localised = cmd_localised.CreateParameter();
_pk_localised.DbType = System.Data.DbType.Int32;
_pk_localised.ParameterName = "p_pk";
_pk_localised.Value = pk_localised;
_pk_localised.Direction = System.Data.ParameterDirection.Output;
cmd_localised.Parameters.Add(_pk_localised);
foreach (TextTypeWrapper name in names)
{
_p_language.Value = name.Locale;
_p_type.Value = "Name";
_p_text.Value = name.Value;
cmd_localised.ExecuteNonQuery();
}
foreach (TextTypeWrapper desc in descs)
{
_p_language.Value = desc.Locale;
_p_type.Value = "Desc";
_p_text.Value = desc.Value;
cmd_localised.ExecuteNonQuery();
}
#endregion
dtw.DBConnection.Close();
//InsertCategorisation(sdmxKey, idFlow, idCat, uri);
return idFlow;
}
catch// (Exception ex)
{
return idFlow;
}
} return idFlow;
}
private static int Get_SIDToFilts(int idset, QueryPart query)
{
DataWrapper dtw = new DataWrapper(DataWrapper.ECONNECTIONTYPE.SQL, DataAccess.SQLConnString_DB.ConnectionString);
if (dtw.TestConnection())
{
dtw.DBConnection.Open();
try
{
int SID = -1;
////////////////////////
System.Data.IDbCommand cmd = dtw.DBConnection.CreateCommand();
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = "dbo.proc_GET_SID_VIEW_FILTS";
System.Data.IDbDataParameter _param_idset = cmd.CreateParameter();
_param_idset.DbType = System.Data.DbType.Int32;
_param_idset.ParameterName = "IDSet";
_param_idset.Value = idset;
cmd.Parameters.Add(_param_idset);
System.Data.IDbDataParameter _param_where = cmd.CreateParameter();
_param_where.DbType = System.Data.DbType.String;
_param_where.ParameterName = "Sql_Where";
_param_where.Value = query.WHERE_PART_CODE;
cmd.Parameters.Add(_param_where);
System.Data.IDbDataParameter _param_sid = cmd.CreateParameter();
_param_sid.DbType = System.Data.DbType.Int32;
_param_sid.ParameterName = "SID";
_param_sid.Direction = ParameterDirection.Output;
cmd.Parameters.Add(_param_sid);
cmd.ExecuteNonQuery();
SID = (_param_sid.Value != null) ? (int)_param_sid.Value : -1;
////////////////////////
dtw.DBConnection.Close();
return SID;
}
catch
{
dtw.DBConnection.Close();
return -1;
}
} return -1;
}
public static void AddCategoryToCube(int idSet, int idCat)
{
DataWrapper dtw = new DataWrapper(DataWrapper.ECONNECTIONTYPE.SQL, DataAccess.SQLConnString_DB.ConnectionString);
if (dtw.TestConnection())
{
dtw.DBConnection.Open();
try
{
System.Data.IDbCommand cmd = dtw.DBConnection.CreateCommand();
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = "proc_INSERT_CATEGORISATION";
System.Data.IDbDataParameter prmIDSet = cmd.CreateParameter();
prmIDSet.DbType = System.Data.DbType.Int32;
prmIDSet.ParameterName = "IDSet";
prmIDSet.Value = idSet;
cmd.Parameters.Add(prmIDSet);
System.Data.IDbDataParameter prmIDCat = cmd.CreateParameter();
prmIDCat.DbType = System.Data.DbType.Int32;
prmIDCat.ParameterName = "@IDCat";
prmIDCat.Value = idCat;
cmd.Parameters.Add(prmIDCat);
cmd.ExecuteNonQuery();
dtw.DBConnection.Close();
}
catch (Exception ex)
{
dtw.DBConnection.Close();
throw ex;
}
}
}