public Dictionary <string, MainTableVariableHierarchyRow> GetMainTableVariableHierarchyRows_KeyIsGroupingID(string aMainTable, string aVariable, bool emptyRowSetIsOK)
{
Dictionary <string, MainTableVariableHierarchyRow> myOut = new Dictionary <string, MainTableVariableHierarchyRow>();
SqlDbConfig dbconf = DB;
string sqlString = GetMainTableVariableHierarchy_SQLString_NoWhere();
//
// WHERE MTP.MainTable = <"MainTable as parameter reference for your db vendor">
// AND MTP.Variable = <"Variable as parameter reference for your db vendor">
//
sqlString += " WHERE " + DB.MainTableVariableHierarchy.MainTableCol.Is(mSqlCommand.GetParameterRef("aMainTable")) +
" AND " + DB.MainTableVariableHierarchy.VariableCol.Is(mSqlCommand.GetParameterRef("aVariable"));
// creating the parameters
System.Data.Common.DbParameter[] parameters = new System.Data.Common.DbParameter[2];
parameters[0] = mSqlCommand.GetStringParameter("aMainTable", aMainTable);
parameters[1] = mSqlCommand.GetStringParameter("aVariable", aVariable);
DataSet ds = mSqlCommand.ExecuteSelect(sqlString, parameters);
DataRowCollection myRows = ds.Tables[0].Rows;
if (myRows.Count < 1 && !emptyRowSetIsOK)
{
throw new PCAxis.Sql.Exceptions.DbException(35, " MainTable = " + aMainTable + " Variable = " + aVariable);
}
foreach (DataRow sqlRow in myRows)
{
MainTableVariableHierarchyRow outRow = new MainTableVariableHierarchyRow(sqlRow, DB);
myOut.Add(outRow.Grouping, outRow);
}
return(myOut);
}
public Dictionary <string, ValueGroupRow> GetValueGroupRowskeyValueCode(string aGrouping, string aGroupCode, bool emptyRowSetIsOK)
{
Dictionary <string, ValueGroupRow> myOut = new Dictionary <string, ValueGroupRow>();
SqlDbConfig dbconf = DB;
string sqlString = GetValueGroup_SQLString_NoWhere();
//
// WHERE VPL.Grouping = '<aGrouping>'
// AND VPL.GroupCode = '<aGroupCode>'
//
sqlString += " WHERE " + DB.ValueGroup.GroupingCol.Is(aGrouping) +
" AND " + DB.ValueGroup.GroupCodeCol.Is(aGroupCode);
DataSet ds = mSqlCommand.ExecuteSelect(sqlString);
DataRowCollection myRows = ds.Tables[0].Rows;
if (myRows.Count < 1 && !emptyRowSetIsOK)
{
throw new PCAxis.Sql.Exceptions.DbException(35, " Grouping = " + aGrouping + " GroupCode = " + aGroupCode);
}
foreach (DataRow sqlRow in myRows)
{
ValueGroupRow outRow = new ValueGroupRow(sqlRow, DB, mLanguageCodes);
myOut.Add(outRow.ValueCode, outRow);
}
return(myOut);
}
public static string GetColumnName(this SqlDbConfig sqlDbConfig, string table, string column)
{
var col = sqlDbConfig.getFieldValue(table).getFieldValue(column + "Col");
string myOut = (col.getMethodValue("PureColumnName")).ToString();
return(myOut);
}
public Dictionary <string, MainTableVariableHierarchyRow> GetMainTableVariableHierarchyRows_KeyIsGroupingID(string aMainTable, string aVariable, bool emptyRowSetIsOK)
{
Dictionary <string, MainTableVariableHierarchyRow> myOut = new Dictionary <string, MainTableVariableHierarchyRow>();
SqlDbConfig dbconf = DB;
string sqlString = GetMainTableVariableHierarchy_SQLString_NoWhere();
//
// WHERE MTP.MainTable = '<aMainTable>'
// AND MTP.Variable = '<aVariable>'
//
sqlString += " WHERE " + DB.MainTableVariableHierarchy.MainTableCol.Is(aMainTable) +
" AND " + DB.MainTableVariableHierarchy.VariableCol.Is(aVariable);
DataSet ds = mSqlCommand.ExecuteSelect(sqlString);
DataRowCollection myRows = ds.Tables[0].Rows;
if (myRows.Count < 1 && !emptyRowSetIsOK)
{
throw new PCAxis.Sql.Exceptions.DbException(35, " MainTable = " + aMainTable + " Variable = " + aVariable);
}
foreach (DataRow sqlRow in myRows)
{
MainTableVariableHierarchyRow outRow = new MainTableVariableHierarchyRow(sqlRow, DB);
myOut.Add(outRow.Grouping, outRow);
}
return(myOut);
}
public Dictionary <string, List <FootnoteContValueRow> > GetFootnoteContValueRows(string aMainTable, bool emptyRowSetIsOK)
{
Dictionary <string, List <FootnoteContValueRow> > myOut = new Dictionary <string, List <FootnoteContValueRow> >();
SqlDbConfig dbconf = DB;
string sqlString = GetFootnoteContValue_SQLString_NoWhere();
//
// WHERE FCA.MainTable = <"MainTable as parameter reference for your db vendor">
//
sqlString += " WHERE " + DB.FootnoteContValue.MainTableCol.Is(mSqlCommand.GetParameterRef("aMainTable"));
// creating the parameters
System.Data.Common.DbParameter[] parameters = new System.Data.Common.DbParameter[1];
parameters[0] = mSqlCommand.GetStringParameter("aMainTable", aMainTable);
DataSet ds = mSqlCommand.ExecuteSelect(sqlString, parameters);
DataRowCollection myRows = ds.Tables[0].Rows;
if (myRows.Count < 1 && !emptyRowSetIsOK)
{
throw new PCAxis.Sql.Exceptions.DbException(35, " MainTable = " + aMainTable);
}
foreach (DataRow sqlRow in myRows)
{
FootnoteContValueRow outRow = new FootnoteContValueRow(sqlRow, DB);
if (!myOut.ContainsKey(outRow.FootnoteNo))
{
myOut[outRow.FootnoteNo] = new List <FootnoteContValueRow>();
}
myOut[outRow.FootnoteNo].Add(outRow);
}
return(myOut);
}
public Dictionary <string, ContentsRow> GetContentsRows(string aMainTable, bool emptyRowSetIsOK)
{
Dictionary <string, ContentsRow> myOut = new Dictionary <string, ContentsRow>();
SqlDbConfig dbconf = DB;
string sqlString = GetContents_SQLString_NoWhere();
//
// WHERE CNT.MainTable = <"MainTable as parameter reference for your db vendor">
//
sqlString += " WHERE " + DB.Contents.MainTableCol.Is(mSqlCommand.GetParameterRef("aMainTable"));
// creating the parameters
System.Data.Common.DbParameter[] parameters = new System.Data.Common.DbParameter[1];
parameters[0] = mSqlCommand.GetStringParameter("aMainTable", aMainTable);
DataSet ds = mSqlCommand.ExecuteSelect(sqlString, parameters);
DataRowCollection myRows = ds.Tables[0].Rows;
if (myRows.Count < 1 && !emptyRowSetIsOK)
{
throw new PCAxis.Sql.Exceptions.DbException(35, " MainTable = " + aMainTable);
}
foreach (DataRow sqlRow in myRows)
{
ContentsRow outRow = new ContentsRow(sqlRow, DB, mLanguageCodes);
myOut.Add(outRow.Contents, outRow);
}
return(myOut);
}
public Dictionary <string, FootnoteGroupingRow> GetFootnoteGroupingRows(StringCollection aGrouping, bool emptyRowSetIsOK)
{
Dictionary <string, FootnoteGroupingRow> myOut = new Dictionary <string, FootnoteGroupingRow>();
SqlDbConfig dbconf = DB;
string sqlString = GetFootnoteGrouping_SQLString_NoWhere();
//
// WHERE FCO.Grouping = '<aGrouping>'
//
sqlString += " WHERE " + DB.FootnoteGrouping.GroupingCol.In(aGrouping);
DataSet ds = mSqlCommand.ExecuteSelect(sqlString);
DataRowCollection myRows = ds.Tables[0].Rows;
if (myRows.Count < 1 && !emptyRowSetIsOK)
{
throw new PCAxis.Sql.Exceptions.DbException(35, " query, see log. ");
}
foreach (DataRow sqlRow in myRows)
{
FootnoteGroupingRow outRow = new FootnoteGroupingRow(sqlRow, DB);
myOut.Add(outRow.FootnoteNo, outRow);
}
return(myOut);
}
public Dictionary <string, GroupingLevelRow> GetGroupingLevelRows_KeyIsLevel(string aGrouping, bool emptyRowSetIsOK)
{
Dictionary <string, GroupingLevelRow> myOut = new Dictionary <string, GroupingLevelRow>();
SqlDbConfig dbconf = DB;
string sqlString = GetGroupingLevel_SQLString_NoWhere();
//
// WHERE GRP.Grouping = <"Grouping as parameter reference for your db vendor">
//
sqlString += " WHERE " + DB.GroupingLevel.GroupingCol.Is(mSqlCommand.GetParameterRef("aGrouping"));
// creating the parameters
System.Data.Common.DbParameter[] parameters = new System.Data.Common.DbParameter[1];
parameters[0] = mSqlCommand.GetStringParameter("aGrouping", aGrouping);
DataSet ds = mSqlCommand.ExecuteSelect(sqlString, parameters);
DataRowCollection myRows = ds.Tables[0].Rows;
if (myRows.Count < 1 && !emptyRowSetIsOK)
{
throw new PCAxis.Sql.Exceptions.DbException(35, " Grouping = " + aGrouping);
}
foreach (DataRow sqlRow in myRows)
{
GroupingLevelRow outRow = new GroupingLevelRow(sqlRow, DB, mLanguageCodes);
myOut.Add(outRow.LevelNo, outRow);
}
return(myOut);
}
public Dictionary <string, FootnoteGroupingRow> GetFootnoteGroupingRows(StringCollection aGrouping, bool emptyRowSetIsOK)
{
Dictionary <string, FootnoteGroupingRow> myOut = new Dictionary <string, FootnoteGroupingRow>();
SqlDbConfig dbconf = DB;
string sqlString = GetFootnoteGrouping_SQLString_NoWhere();
//
// WHERE FCO.Grouping = <"Grouping as parameter reference for your db vendor">
//
sqlString += " WHERE " + DB.FootnoteGrouping.GroupingCol.In(mSqlCommand.GetParameterRef("aGrouping"), aGrouping.Count);
// creating the parameters
System.Data.Common.DbParameter[] parameters = new System.Data.Common.DbParameter[aGrouping.Count];
for (int counter = 1; counter <= aGrouping.Count; counter++)
{
parameters[counter - 1] = mSqlCommand.GetStringParameter("aGrouping" + counter, aGrouping[counter - 1]);
}
DataSet ds = mSqlCommand.ExecuteSelect(sqlString, parameters);
DataRowCollection myRows = ds.Tables[0].Rows;
if (myRows.Count < 1 && !emptyRowSetIsOK)
{
throw new PCAxis.Sql.Exceptions.DbException(35, " query, see log. ");
}
foreach (DataRow sqlRow in myRows)
{
FootnoteGroupingRow outRow = new FootnoteGroupingRow(sqlRow, DB);
myOut.Add(outRow.FootnoteNo, outRow);
}
return(myOut);
}
public string GetValueGroupMaxValueLevel(string aGrouping, bool emptyRowSetIsOK)
{
string myOut;
SqlDbConfig dbconf = DB;
string sqlString = "SELECT ";
sqlString +=
"MAX(" +
DB.ValueGroup.GroupLevelCol.Id() + ") ";
sqlString += " FROM " + DB.ValueGroup.GetNameAndAlias();
sqlString += " WHERE " + DB.ValueGroup.GroupingCol.Is(mSqlCommand.GetParameterRef("aGrouping"));
// creating the parameters
System.Data.Common.DbParameter[] parameters = new System.Data.Common.DbParameter[1];
parameters[0] = mSqlCommand.GetStringParameter("aGrouping", aGrouping);
DataSet ds = mSqlCommand.ExecuteSelect(sqlString, parameters);
DataRowCollection myRows = ds.Tables[0].Rows;
if (myRows.Count < 1 && !emptyRowSetIsOK)
{
throw new PCAxis.Sql.Exceptions.DbException(35, " Grouping = " + aGrouping);
}
myOut = myRows[0][0].ToString();
return(myOut);
}
public Dictionary <string, ValueRow> GetValueRows(string aValuePool, bool emptyRowSetIsOK)
{
Dictionary <string, ValueRow> myOut = new Dictionary <string, ValueRow>();
SqlDbConfig dbconf = DB;
string sqlString = GetValue_SQLString_NoWhere();
//
// WHERE VAL.ValuePool = <"ValuePool as parameter reference for your db vendor">
//
sqlString += " WHERE " + DB.Value.ValuePoolCol.Is(mSqlCommand.GetParameterRef("aValuePool")) +
" AND " + DB.Value.MetaIdCol.IsNotNULL();
// creating the parameters
System.Data.Common.DbParameter[] parameters = new System.Data.Common.DbParameter[1];
parameters[0] = mSqlCommand.GetStringParameter("aValuePool", aValuePool);
DataSet ds = mSqlCommand.ExecuteSelect(sqlString, parameters);
DataRowCollection myRows = ds.Tables[0].Rows;
if (myRows.Count < 1 && !emptyRowSetIsOK)
{
throw new PCAxis.Sql.Exceptions.DbException(35, " ValuePool = " + aValuePool);
}
foreach (DataRow sqlRow in myRows)
{
ValueRow outRow = new ValueRow(sqlRow, DB, mLanguageCodes);
myOut.Add(outRow.ValueCode, outRow);
}
return(myOut);
}
public Dictionary <string, GroupingLevelRow> GetGroupingLevelRows_KeyIsLevel(string aGrouping, bool emptyRowSetIsOK)
{
Dictionary <string, GroupingLevelRow> myOut = new Dictionary <string, GroupingLevelRow>();
SqlDbConfig dbconf = DB;
string sqlString = GetGroupingLevel_SQLString_NoWhere();
//
// WHERE GRP.Grouping = '<aGrouping>'
//
sqlString += " WHERE " + DB.GroupingLevel.GroupingCol.Is(aGrouping);
DataSet ds = mSqlCommand.ExecuteSelect(sqlString);
DataRowCollection myRows = ds.Tables[0].Rows;
if (myRows.Count < 1 && !emptyRowSetIsOK)
{
throw new PCAxis.Sql.Exceptions.DbException(35, " Grouping = " + aGrouping);
}
foreach (DataRow sqlRow in myRows)
{
GroupingLevelRow outRow = new GroupingLevelRow(sqlRow, DB, mLanguageCodes);
myOut.Add(outRow.Level, outRow);
}
return(myOut);
}
public Dictionary <string, ValueSetGroupingRow> GetValueSetGroupingRowskeyGrouping(string aValueSet, bool emptyRowSetIsOK)
{
Dictionary <string, ValueSetGroupingRow> myOut = new Dictionary <string, ValueSetGroupingRow>();
SqlDbConfig dbconf = DB;
string sqlString = GetValueSetGrouping_SQLString_NoWhere();
//
// WHERE VBL.ValueSet = '<aValueSet>'
//
sqlString += " WHERE " + DB.ValueSetGrouping.ValueSetCol.Is(aValueSet);
DataSet ds = mSqlCommand.ExecuteSelect(sqlString);
DataRowCollection myRows = ds.Tables[0].Rows;
if (myRows.Count < 1 && !emptyRowSetIsOK)
{
throw new PCAxis.Sql.Exceptions.DbException(35, " ValueSet = " + aValueSet);
}
foreach (DataRow sqlRow in myRows)
{
ValueSetGroupingRow outRow = new ValueSetGroupingRow(sqlRow, DB);
myOut.Add(outRow.Grouping, outRow);
}
return(myOut);
}
public Dictionary <string, MainTablePersonRow> GetMainTablePersonRows(string aMainTable, string aRolePerson, bool emptyRowSetIsOK)
{
Dictionary <string, MainTablePersonRow> myOut = new Dictionary <string, MainTablePersonRow>();
SqlDbConfig dbconf = DB;
string sqlString = GetMainTablePerson_SQLString_NoWhere();
//
// WHERE MTP.MainTable = '<aMainTable>'
// AND MTP.RolePerson = '<aRolePerson>'
//
sqlString += " WHERE " + DB.MainTablePerson.MainTableCol.Is(aMainTable) +
" AND " + DB.MainTablePerson.RolePersonCol.Is(aRolePerson);
DataSet ds = mSqlCommand.ExecuteSelect(sqlString);
DataRowCollection myRows = ds.Tables[0].Rows;
if (myRows.Count < 1 && !emptyRowSetIsOK)
{
throw new PCAxis.Sql.Exceptions.DbException(35, " MainTable = " + aMainTable + " RolePerson = " + aRolePerson);
}
foreach (DataRow sqlRow in myRows)
{
MainTablePersonRow outRow = new MainTablePersonRow(sqlRow, DB);
myOut.Add(outRow.PersonCode, outRow);
}
return(myOut);
}
public Dictionary <string, ValueSetGroupingRow> GetValueSetGroupingRowskeyGrouping(string aValueSet, bool emptyRowSetIsOK)
{
Dictionary <string, ValueSetGroupingRow> myOut = new Dictionary <string, ValueSetGroupingRow>();
SqlDbConfig dbconf = DB;
string sqlString = GetValueSetGrouping_SQLString_NoWhere();
//
// WHERE VBL.ValueSet = <"ValueSet as parameter reference for your db vendor">
//
sqlString += " WHERE " + DB.ValueSetGrouping.ValueSetCol.Is(mSqlCommand.GetParameterRef("aValueSet"));
// creating the parameters
System.Data.Common.DbParameter[] parameters = new System.Data.Common.DbParameter[1];
parameters[0] = mSqlCommand.GetStringParameter("aValueSet", aValueSet);
DataSet ds = mSqlCommand.ExecuteSelect(sqlString, parameters);
DataRowCollection myRows = ds.Tables[0].Rows;
if (myRows.Count < 1 && !emptyRowSetIsOK)
{
throw new PCAxis.Sql.Exceptions.DbException(35, " ValueSet = " + aValueSet);
}
foreach (DataRow sqlRow in myRows)
{
ValueSetGroupingRow outRow = new ValueSetGroupingRow(sqlRow, DB);
myOut.Add(outRow.Grouping, outRow);
}
return(myOut);
}
public Dictionary <string, SubTableRow> GetSubTableRows(string aMainTable, bool emptyRowSetIsOK)
{
Dictionary <string, SubTableRow> myOut = new Dictionary <string, SubTableRow>();
SqlDbConfig dbconf = DB;
string sqlString = GetSubTable_SQLString_NoWhere();
//
// WHERE STB.MainTable = '<aMainTable>'
//
sqlString += " WHERE " + DB.SubTable.MainTableCol.Is(aMainTable);
DataSet ds = mSqlCommand.ExecuteSelect(sqlString);
DataRowCollection myRows = ds.Tables[0].Rows;
if (myRows.Count < 1 && !emptyRowSetIsOK)
{
throw new PCAxis.Sql.Exceptions.DbException(35, " MainTable = " + aMainTable);
}
foreach (DataRow sqlRow in myRows)
{
SubTableRow outRow = new SubTableRow(sqlRow, DB, mLanguageCodes);
myOut.Add(outRow.SubTable, outRow);
}
return(myOut);
}
public Dictionary <string, ValueGroupRow> GetValueGroupRowskeyValueCode(string aGrouping, string aGroupCode, bool emptyRowSetIsOK)
{
Dictionary <string, ValueGroupRow> myOut = new Dictionary <string, ValueGroupRow>();
SqlDbConfig dbconf = DB;
string sqlString = GetValueGroup_SQLString_NoWhere();
//
// WHERE VPL.Grouping = <"Grouping as parameter reference for your db vendor">
// AND VPL.GroupCode = <"GroupCode as parameter reference for your db vendor">
//
sqlString += " WHERE " + DB.ValueGroup.GroupingCol.Is(mSqlCommand.GetParameterRef("aGrouping")) +
" AND " + DB.ValueGroup.GroupCodeCol.Is(mSqlCommand.GetParameterRef("aGroupCode"));
// creating the parameters
System.Data.Common.DbParameter[] parameters = new System.Data.Common.DbParameter[2];
parameters[0] = mSqlCommand.GetStringParameter("aGrouping", aGrouping);
parameters[1] = mSqlCommand.GetStringParameter("aGroupCode", aGroupCode);
DataSet ds = mSqlCommand.ExecuteSelect(sqlString, parameters);
DataRowCollection myRows = ds.Tables[0].Rows;
if (myRows.Count < 1 && !emptyRowSetIsOK)
{
throw new PCAxis.Sql.Exceptions.DbException(35, " Grouping = " + aGrouping + " GroupCode = " + aGroupCode);
}
foreach (DataRow sqlRow in myRows)
{
ValueGroupRow outRow = new ValueGroupRow(sqlRow, DB, mLanguageCodes);
myOut.Add(outRow.ValueCode, outRow);
}
return(myOut);
}
protected PXSqlMeta(SqlDbConfig config, InfoForDbConnection selectedDbInfo, Instancemodus aModus, bool constructedFromPxs)
{
log.Info("PXSqlMeta this.mCNMMVersion = " + config.MetaModel + " Instancemodus aModus(=" + aModus.ToString() + "constructedFromPxs=" + constructedFromPxs.ToString());
this.mCNMMVersion = config.MetaModel;
this.mSelectedDbInfo = selectedDbInfo;
this.mInstanceModus = aModus;
this.constructedFromPxs = constructedFromPxs;
}
public static string GetColumnId(this SqlDbConfig sqlDbConfig, string table, string column, string languageCode)
{
var col = sqlDbConfig.getFieldValue(table).getFieldValue(column + "Col");
return
((
col is Lang2Col
?
col.getMethodValue("Id", languageCode)
:
col.getMethodValue("Id")
).ToString());
}
public static string GetTableNameAndAlias(this SqlDbConfig sqlDbConfig, string table, string languageCode)
{
var tab = sqlDbConfig.getFieldValue(table);
return
((
tab is Lang2Tab
?
tab.getMethodValue("GetNameAndAlias", languageCode)
:
tab.getMethodValue("GetNameAndAlias")
).ToString());
}
public List <ValueGroupRow> GetValueGroupRowsSorted(string aGrouping, string aLevel, bool emptyRowSetIsOK, string aSortOrderLanguage)
{
string sortOrderLanguage = aSortOrderLanguage;
if (!this.mLanguageCodes.Contains(aSortOrderLanguage))
{
log.Error("requsted sortOrder language not in extaction, using first in list of languages");
sortOrderLanguage = this.mLanguageCodes[0];
}
bool sortOnPrimaryLanguage = !this.mDbConfig.isSecondaryLanguage(sortOrderLanguage);
List <ValueGroupRow> myOut = new List <ValueGroupRow>();
SqlDbConfig dbconf = DB;
string sqlString = GetValueGroup_SQLString_NoWhere();
//
// WHERE VPL.Grouping = '<aGrouping>'
// AND VPL.GroupCode = '<aGroupCode>'
//
sqlString += " WHERE " + DB.ValueGroup.GroupingCol.Is(aGrouping);
if (aLevel != null)
{
sqlString += " AND " + DB.ValueGroup.GroupLevelCol.Is(aLevel);
}
if (sortOnPrimaryLanguage)
{
sqlString += " ORDER BY " + DB.ValueGroup.SortCodeCol.Id() + ", ";
}
else
{
sqlString += " ORDER BY "
+ DB.ValueGroupLang2.SortCodeCol.Id(sortOrderLanguage) + ", ";
}
sqlString += DB.ValueGroup.GroupCodeCol.Id() + ", " +
DB.ValueGroup.ValueCodeCol.Id();
DataSet ds = mSqlCommand.ExecuteSelect(sqlString);
DataRowCollection myRows = ds.Tables[0].Rows;
if (myRows.Count < 1 && !emptyRowSetIsOK)
{
throw new PCAxis.Sql.Exceptions.DbException(35, " Grouping = " + aGrouping);
}
foreach (DataRow sqlRow in myRows)
{
ValueGroupRow outRow = new ValueGroupRow(sqlRow, DB, mLanguageCodes);
myOut.Add(outRow);
}
return(myOut);
}
/// <summary>
/// gets a SqlDbConfig instance (with any user/passeword given) the first time it is called.
/// </summary>
private void setDbConfig()
{
if (!hasSetDbId)
{
dbConf = SqlDbConfigsStatic.DefaultDatabase;
}
else
{
log.Debug("Using explisit dbId= \"" + _dbId + "\"");
if (!SqlDbConfigsStatic.DataBases.ContainsKey(_dbId))
{
throw new ApplicationException("Can't find databaseId =\"" + _dbId + "\"");
}
dbConf = SqlDbConfigsStatic.DataBases[_dbId];
}
}
public string GetValueGroupMaxValueLevel(string aGrouping, bool emptyRowSetIsOK)
{
string myOut;
SqlDbConfig dbconf = DB;
string sqlString = "SELECT ";
sqlString +=
"MAX(" +
DB.ValueGroup.GroupLevelCol.Id() + ") ";
sqlString += " FROM " + DB.ValueGroup.GetNameAndAlias();
sqlString += " WHERE " + DB.ValueGroup.GroupingCol.Is(aGrouping);
DataSet ds = mSqlCommand.ExecuteSelect(sqlString);
DataRowCollection myRows = ds.Tables[0].Rows;
if (myRows.Count < 1 && !emptyRowSetIsOK)
{
throw new PCAxis.Sql.Exceptions.DbException(35, " Grouping = " + aGrouping);
}
myOut = myRows[0][0].ToString();
return(myOut);
}
public int GetNumberOfDistinctAttributeColumns(string aMainTable)
{
SqlDbConfig dbconf = DB;
string sqlString = "select count(distinct " + DB.Attribute.AttributeColumnCol.PureColumnName() + ") FROM " + DB.Attribute.GetNameAndAlias() +
" WHERE " + DB.Attribute.MainTableCol.Is();
// creating the parameters
System.Data.Common.DbParameter[] parameters = new System.Data.Common.DbParameter[1];
parameters[0] = DB.Attribute.MainTableCol.GetStringParameter(aMainTable);
DataSet ds = mSqlCommand.ExecuteSelect(sqlString, parameters);
DataRowCollection myRows = ds.Tables[0].Rows;
if (myRows.Count != 1)
{
throw new PCAxis.Sql.Exceptions.DbException(35, " MainTable = " + aMainTable);
}
return(int.Parse(myRows[0][0].ToString()));
}
public PXSqlMeta_24(PxsQuery mPxsObject, string preferredLang, SqlDbConfig config, InfoForDbConnection selectedDbInfo, PCAxis.Sql.Parser.Instancemodus aModus, bool useTempTables)
: base(config, selectedDbInfo, aModus, true)
{
log.Debug("PXSqlMeta(PxsQuery mPxsObject, SqlDbConfig config, Instancemodus aModus");
//disse er trukket hit for å kunne kjøre med String hovedtabellId.
this.mMainTableId = mPxsObject.Query.TableSource;
//TODO; denne burde kunne fjernes
this.mPxsSubTableId = mPxsObject.Query.SubTable;
this.mConfig = (SqlDbConfig_24)config;
mMetaQuery = new MetaQuery(this.mConfig, this.SelectedDbInfo, useTempTables);
this.mPxsFile = this.rearrangePxsQuery(mPxsObject);
SetLanguageCodesFromPxs(preferredLang);
BuildMeta(); //
}
public static PXSqlData GetPXSqlData(PXSqlMeta mPXSqlMeta, SqlDbConfig mSqlDbConfig)
{
if (mPXSqlMeta.CNMMVersion.Equals("2.1"))
{
return(new PCAxis.Sql.Parser_21.PXSqlData_21((PCAxis.Sql.Parser_21.PXSqlMeta_21)mPXSqlMeta));
}
else if (mPXSqlMeta.CNMMVersion.Equals("2.2"))
{
return(new PCAxis.Sql.Parser_22.PXSqlData_22((PCAxis.Sql.Parser_22.PXSqlMeta_22)mPXSqlMeta));
}
else if (mPXSqlMeta.CNMMVersion.Equals("2.3"))
{
return(new PCAxis.Sql.Parser_23.PXSqlData_23((PCAxis.Sql.Parser_23.PXSqlMeta_23)mPXSqlMeta));
}
else if (mPXSqlMeta.CNMMVersion.Equals("2.4"))
{
return(new PCAxis.Sql.Parser_24.PXSqlData_24((PCAxis.Sql.Parser_24.PXSqlMeta_24)mPXSqlMeta));
}
else
{
log.Debug("creating PXSqlData_21, but mPXSqlMeta.CNMMVersion is " + mPXSqlMeta.CNMMVersion);
return(new PCAxis.Sql.Parser_21.PXSqlData_21((PCAxis.Sql.Parser_21.PXSqlMeta_21)mPXSqlMeta));
}
}
/// <summary>
/// Create instance of appropriate DatamodelMenu
/// </summary>
/// <param name="language">Language code</param>
/// <param name="sqlDbConfig">Instance of SqlDbConfig for configuring the menu</param>
/// <param name="menuInitialization">Further menuinitialization as eg. Lambda expression</param>
/// <returns></returns>
public static DatamodelMenu Create(string language, SqlDbConfig sqlDbConfig, PCAxis.Menu.Implementations.DatamodelMenu.DatamodelMenuInitialization menuInitialization)
{
//TODO; SqlDbConfig_XX //Norway, JF
return(Create(language, sqlDbConfig, menuInitialization, null));
}
/// <summary>
/// Create instance of appropriate DatamodelMenu
/// </summary>
/// <param name="language">Language code</param>
/// <param name="sqlDbConfig">Instance of SqlDbConfig for configuring the menu</param>
/// <param name="menuInitialization">Further menuinitialization as eg. Lambda expression</param>
/// <param name="alterSql">Changes to SQL as eg. Lambda expression</param>
/// <returns></returns>
public static DatamodelMenu Create(string language, SqlDbConfig sqlDbConfig, DatamodelMenu.DatamodelMenuInitialization menuInitialization, Func <string, string> alterSql)
{
SqlDbConfig dbConf;
//TODO; SqlDbConfig_XX //Norway, JF
if (sqlDbConfig != null)
{
dbConf = sqlDbConfig;
}
else if (ConfigurationManager.AppSettings["dbconfigfile"] != null)
{
dbConf = SqlDbConfigsStatic.DefaultDatabase;
}
else
{
throw new ConfigDatamodelMenuExceptions.NoDbConfigurationException("A dbconfiguration must be supplied to ConfigDatamodelMenu.");
}
if (!dbConf.GetAllLanguages().Contains(language))
{
throw new ConfigDatamodelMenuExceptions.LanguageNotInConfigXmlException("Cant' find a language with the code \"" + language + "\" in the Language section of the SqlDbConfig-XML.");
}
DatamodelMenu.DatamodelMenuInitialization newSettings =
m =>
{
m.OverrideSQL =
convertSQL(
dbConf,
m.FormatSql.Replace(
"{1}",
dbConf.isSecondaryLanguage(language) ? "Lang2" : ""
),
language
);
if (dbConf.MetaModel == "2.2")
{
m.Parameters["menu"].Size = 80; //Norway, JF
m.Parameters["selection"].Size = 80;
}
if (alterSql != null)
{
m.OverrideSQL = alterSql(m.OverrideSQL);
}
if (menuInitialization != null)
{
menuInitialization(m);
}
};
var dbInfo = dbConf.GetInfoForDbConnection("", "");
string connectionString = dbInfo.ConnectionString;
if (OverrideDatamodelMenuType != null)
{
try
{
return
(OverrideDatamodelMenuType.GetConstructor(
new Type[] { typeof(string), typeof(string), typeof(DatamodelMenu.DatamodelMenuInitialization) }
).Invoke(
new object[] { connectionString, language, newSettings }
) as DatamodelMenu);
}
catch (Exception e)
{
throw
new ConfigDatamodelMenuExceptions.TypeForExtractingDataNotValidException(
"Type supplied for overriding extraction of data doesn't have the requested constructor (string, string, DatamodelMenuInitialization)"
);
}
}
string provider = dbInfo.DataProvider;
switch (provider)
{
case "Oracle":
return(new OracleDatamodelMenu(connectionString, language, newSettings));
case "Sql":
return(new MsSqlDatamodelMenu(connectionString, language, newSettings));
default:
var menuPlugins = ((Dictionary <string, string>)ConfigurationManager.GetSection("PCAxis.Menu"));
if (menuPlugins == null || !menuPlugins.ContainsKey(provider))
{
throw new NotImplementedException("Code for retrieving data from a " + provider + " database is not (yet) implemented in ConfigDatamodelMenu. No plugin for " + provider + " found.");
}
try
{
return((DatamodelMenu)Activator.CreateInstance(Type.GetType(menuPlugins[provider]), new object[] { connectionString, language, newSettings }));
}
catch
{
throw new NotImplementedException("Failed using plugin for retrieving data from a " + provider + " database.");
}
}
}
/// <summary>
///
/// </summary>
/// <param name="mainTableId"></param>
/// <param name="preferredLang">The code ("no","en",...) of the language the client wants as main language in paxiom. May be null or empty, indicating the client dont care, in which case a "random" language is choosen.</param>
/// <param name="getAllLangs"></param>
/// <param name="config"></param>
/// <param name="selectedDbInfo"></param>
/// <param name="aModus"></param>
public PXSqlMeta_24(string mainTableId, string preferredLang, bool getAllLangs, SqlDbConfig config, InfoForDbConnection selectedDbInfo, PCAxis.Sql.Parser.Instancemodus aModus, bool useTempTables)
: base(config, selectedDbInfo, aModus, false)
{
log.Info("PXSqlMeta(string mainTableId(=" + mainTableId + "), StringCollection desiredLanguages, SqlDbConfig config, Instancemodus aModus(=" + aModus.ToString() + "))");
this.mMainTableId = mainTableId;
this.mConfig = (SqlDbConfig_24)config;
mMetaQuery = new MetaQuery(this.mConfig, this.SelectedDbInfo, useTempTables);
SetLanguageCodesNoPxs(preferredLang, getAllLangs);
BuildMeta();
}
/// <summary>
/// Returns rows for the given grouping ids sorted by sortorder, prestext from given language.
/// </summary>
/// <param name="aGroupings">if this is empty, an empty list is returned </param>
/// <param name="emptyRowSetIsOK"></param>
/// <param name="aSortOrderLanguage"></param>
/// <returns></returns>
public List <GroupingRow> GetGroupingRowsSorted(StringCollection aGroupings, bool emptyRowSetIsOK, string aSortOrderLanguage)
{
string sortOrderLanguage = aSortOrderLanguage;
if (!this.mLanguageCodes.Contains(aSortOrderLanguage))
{
log.Error("requsted sortOrder language not in extaction, using first in list of languages");
sortOrderLanguage = this.mLanguageCodes[0];
}
bool sortOnPrimaryLanguage = !this.mDbConfig.isSecondaryLanguage(sortOrderLanguage);
List <GroupingRow> myOut = new List <GroupingRow>();
if (aGroupings.Count == 0)
{
return(myOut);
}
SqlDbConfig dbconf = DB;
string sqlString = GetGrouping_SQLString_NoWhere();
sqlString += " WHERE " + DB.Grouping.GroupingCol.In(mSqlCommand.GetParameterRef("aGroupings"), aGroupings.Count);
// creating the parameters
System.Data.Common.DbParameter[] parameters = new System.Data.Common.DbParameter[aGroupings.Count];
for (int counter = 1; counter <= aGroupings.Count; counter++)
{
parameters[counter - 1] = mSqlCommand.GetStringParameter("aGroupings" + counter, aGroupings[counter - 1]);
}
if (sortOnPrimaryLanguage)
{
sqlString += " ORDER BY " + DB.Grouping.SortCodeCol.Id() + ", " + DB.Grouping.PresTextCol.Id() + ", ";
}
else
{
sqlString += " ORDER BY " + DB.GroupingLang2.SortCodeCol.Id(sortOrderLanguage) + ", " +
DB.GroupingLang2.PresTextCol.Id(sortOrderLanguage) + ", ";
}
sqlString += DB.Grouping.GroupingCol.Id();
DataSet ds = mSqlCommand.ExecuteSelect(sqlString, parameters);
DataRowCollection myRows = ds.Tables[0].Rows;
if (myRows.Count < 1 && !emptyRowSetIsOK)
{
throw new PCAxis.Sql.Exceptions.DbException(35, " Grouping = " + aGroupings.ToString());
}
foreach (DataRow sqlRow in myRows)
{
GroupingRow outRow = new GroupingRow(sqlRow, DB, mLanguageCodes);
myOut.Add(outRow);
}
return(myOut);
}
