/// <summary>
/// Add PublishedFileName(Text) table to the Artefacts table in database.mdb
/// </summary>
/// <returns>bool</returns>
public static bool ExistenceofColumnAccessDbSchema()
{
DIConnection diConnection;
bool retVal = false;
bool IsColumnExists = false;
string query = string.Empty;
using (diConnection = new DIConnection(DIServerType.MsAccess, string.Empty, string.Empty, Path.Combine(HttpContext.Current.Request.PhysicalApplicationPath, @"stock\Database.mdb"), string.Empty, string.Empty))
{
try
{
// Check if field exists to avoid exception
try
{
//// MOD LOG
//// Issue Id:BUG 7 Issue Date: 07-May-2014
//// Issue Fixed by:[email protected]
//// Description: Exception capturing was used to figure out database colmun existance. Fixed to check for column exist before running the query to create column PublishedFileName.
query = @" SELECT Top 1 * FROM Artefacts";
DataTable table = diConnection.ExecuteDataTable(query);
if (table.Columns.IndexOf("PublishedFileName") < 0)
{
query = @" ALTER TABLE Artefacts ADD COLUMN PublishedFileName Text(225)";
var result = diConnection.ExecuteScalarSqlQuery(query);
}
IsColumnExists = true;
}
catch
{
IsColumnExists = true;
}
retVal = true;
}
catch (Exception ex) //catch specific exception by refering to the DIConnectino source.
{
Global.CreateExceptionString(ex, null);
throw ex;
}
}
return retVal;
}
private int GetClassNId(DIConnection DBConnection, DIQueries DBQueries, out bool bNewClass, String classGUID, Boolean SectorGlobal, int iParentNId, String className)
{
int RetVal = 0;
string sQry = string.Empty;
Boolean NewClass = false;
try
{
//-- Getting Query from DAL
sQry = DBQueries.Calculates.GetICNIdByGId("'" + classGUID + "'");
//-- Executing Query
RetVal = Convert.ToInt32(DBConnection.ExecuteScalarSqlQuery(sQry));
if (RetVal == 0)
{
NewClass = true;
// -- Getting insert Query
sQry = DevInfo.Lib.DI_LibDAL.Queries.IndicatorClassification.Insert.InsertIC(DBQueries.DataPrefix, DBQueries.LanguageCode, className.Replace("'", "''"), classGUID, SectorGlobal, iParentNId, "", ICType.Sector);
//-- Using DAL for query execution and getting identity value
DBConnection.ExecuteNonQuery(sQry);
RetVal = Convert.ToInt32(DBConnection.ExecuteScalarSqlQuery("SELECT @@IDENTITY"));
}
}
catch (Exception ex)
{
RetVal = 0;
}
bNewClass = NewClass;
return RetVal;
}
/// <summary>
/// Get SectorNId
/// </summary>
/// <param name="DBConnection"></param>
/// <param name="DBQueries"></param>
/// <param name="bNewSector"></param>
/// <returns></returns>
private int GetSectorNId(DIConnection DBConnection, DIQueries DBQueries, out bool bNewSector, out Boolean bSectorGlobal)
{
int RetVal = 0;
string sQry = string.Empty;
Boolean SectorGlobal = false;
Boolean NewSector = false;
try
{
sQry = DBQueries.Calculates.GetICNIdByGId("'" + this.DESheetInformation.SectorGUID + "'");
//-- Executing Query
RetVal = Convert.ToInt32(DBConnection.ExecuteScalarSqlQuery(sQry));
if (RetVal == 0)
{
NewSector = true;
if (string.IsNullOrEmpty(this.DESheetInformation.SectorGlobal))
{
}
else
{
SectorGlobal = Convert.ToBoolean(this.DESheetInformation.SectorGlobal);
}
sQry = DevInfo.Lib.DI_LibDAL.Queries.IndicatorClassification.Insert.InsertIC(DBQueries.DataPrefix, DBQueries.LanguageCode, this.DESheetInformation.SectorName.Replace("'", "''"), this.DESheetInformation.IndicatorGUID,
SectorGlobal, -1, "", ICType.Sector);
//-- Using DAL for query execution and getting identity value
DBConnection.ExecuteNonQuery(sQry);
RetVal = Convert.ToInt32(DBConnection.ExecuteScalarSqlQuery("SELECT @@IDENTITY"));
}
}
catch (Exception ex)
{
RetVal = 0;
}
bSectorGlobal = SectorGlobal;
bNewSector = NewSector;
return RetVal;
}
/// <summary>
/// Get path for selected area starting from root area
/// </summary>
/// <param name="ElementNid">ID of selected Data</param>
/// <param name="DI_QueryBase">This contains Connection Details</param>
/// <returns>String containg the path of area node starting from root node</returns>
public static string GetAreaChain(int AreaNId, DIConnection DIConnection, DIQueries DIQueries)
{
string RetVal = string.Empty;
string sSql = string.Empty;
int AreaLevel = 1;
// Get Current Area Level
sSql = DIQueries.Area.GetArea(FilterFieldType.NId, AreaNId.ToString());
DataTable dtArea = DIConnection.ExecuteDataTable(sSql);
if (dtArea != null & dtArea.Rows.Count > 0)
{
AreaLevel = (int)dtArea.Rows[0][Area.AreaLevel];
}
//Get concatinated Area names
sSql = DIQueries.Area.GetAreaChain(AreaNId, AreaLevel, DIConnection.ConnectionStringParameters.ServerType);
RetVal = DIConnection.ExecuteScalarSqlQuery(sSql).ToString();
return RetVal;
}
/// <summary>
/// Insert Newly added records from DES to database
/// </summary>
/// <param name="sDestDBPath">Destination Database Path</param>
/// <param name="MsAccessPwd">Password</param>
/// <param name="sLng_Suffix">Lang suffix</param>
public void InsertNewRecords(String tempDESheetPath, string sDestDBPath, String MsAccessPwd)
{
DataView oDV = null;
DataRowView oDVRow;
string sDB_Prefix;
string sQry = "";
DIConnection DBConnection;
int i;
int j;
int iIndicatorNId = 0;
int iUnitNId = 0;
int[] iSubgroupNId = new int[this.DESheetInformation.Subgroup.Length];
int[] iIUSNId = new int[this.DESheetInformation.Subgroup.Length];
int iSectorNID = 0;
//int[] iClassNId = new int[Strings.Split(this.DESheetInformation.ClassGUID, "{}").Length];
int[] iClassNId = new int[this.DESheetInformation.ClassGUID.Split("{}".ToCharArray()).Length];
int iParentNId;
//Used for Class and Source
bool bNewIUS = false;
Boolean bNewSector = false;
bool bNewClass = false;
//*** True if any of I / U / S is new
bool SectorGlobal = false;
// -- Create New DiConnection with db as sDestDBPath
DBConnection = new DIConnection(DIServerType.MsAccess, "", "", sDestDBPath, "", MsAccessPwd);
//--- Using DAL for getting Database default Prefix
sDB_Prefix = DBConnection.DIDataSetDefault();
String sLng_Suffix = DBConnection.DILanguageCodeDefault(sDB_Prefix);
DIQueries DBQueries = new DIQueries(sDB_Prefix, DBConnection.DILanguageCodeDefault(sDB_Prefix));
// Get SectorNId
iSectorNID = GetSectorNId(DBConnection, DBQueries, out bNewSector, out bNewSector);
//*** If Selected Class has not been inserted by export routine then insert new Class
string[] sClassGuid;
string[] sClassName;
string[] sClassGlobal;
iParentNId = iSectorNID;
//sClassGuid = Strings.Split(this.DESheetInformation.ClassGUID,"{}");
sClassGuid = this.DESheetInformation.ClassGUID.Split("{}".ToCharArray());
sClassName = this.DESheetInformation.ClassName.Split("{}".ToCharArray());
sClassGlobal = this.DESheetInformation.ClassGlobal.Split("{}".ToCharArray());
//*** BugFix 29 Aug 2006 Do not try to insert class into database if indicator is directly associated with sector
if (this.DESheetInformation.ClassGUID != "" & this.DESheetInformation.ClassName != "")
{
for (i = 0; i <= sClassGuid.Length - 1; i++)
{
iClassNId[i] = GetClassNId(DBConnection, DBQueries, out bNewClass, sClassGuid[i].ToString(), SectorGlobal, iParentNId, sClassName[i].ToString());
iParentNId = Convert.ToInt32(iClassNId[i]);
}
}
//*** Indicator_NId
sQry = DBQueries.Indicators.GetIndicator(FilterFieldType.GId, "'" + this.DESheetInformation.IndicatorGUID + "'", FieldSelection.NId);
iIndicatorNId = Convert.ToInt32(DBConnection.ExecuteScalarSqlQuery(sQry));
if (iIndicatorNId == 0)
{
bNewIUS = true;
//-- Getting insert query using DAL
sQry = DI_LibDAL.Queries.Indicator.Insert.InsertIndicator(sDB_Prefix, sLng_Suffix, this.DESheetInformation.Indicator.Replace("'", "''"), this.DESheetInformation.IndicatorGUID, "", false);
//-- Using DAL for query execution and getting identity value
DBConnection.ExecuteNonQuery(sQry);
iIndicatorNId = Convert.ToInt32(DBConnection.ExecuteScalarSqlQuery("SELECT @@IDENTITY"));
}
//*** Unit_NId
//-- Using DAL for getting and executing query for getting unit NId
sQry = DBQueries.Calculates.GetUnitNIdByGId(this.DESheetInformation.UnitGUID);
iUnitNId = Convert.ToInt32(DBConnection.ExecuteScalarSqlQuery(sQry));
if (iUnitNId == 0)
{
bNewIUS = true;
//-- Getting insert query using DAL
sQry = DI_LibDAL.Queries.Unit.Insert.InsertUnit(sDB_Prefix, sLng_Suffix, this.DESheetInformation.Unit.Replace("'", "''"), this.DESheetInformation.UnitGUID, false);
//-- Using DAL for query execution and getting identity value
DBConnection.ExecuteNonQuery(sQry);
iUnitNId = Convert.ToInt32(DBConnection.ExecuteScalarSqlQuery("SELECT @@IDENTITY"));
}
//*** Subgroup_Val_NId
for (i = 0; i <= iSubgroupNId.Length - 1; i++)
{
sQry = DBQueries.Calculates.GetSubgroupValNIdByGId(this.DESheetInformation.SubgroupGUID[i]);
iSubgroupNId[i] = Convert.ToInt32(DBConnection.ExecuteScalarSqlQuery(sQry));
if (iSubgroupNId[i] == 0)
{
bNewIUS = true;
//-- Getting insert query using DAL
sQry = DBQueries.Calculates.InsertSubgroupVal(this.DESheetInformation.Subgroup[0].Replace("'", "''"), this.DESheetInformation.SubgroupGUID[0], false);
//-- Using DAL for query execution and getting identity value
DBConnection.ExecuteNonQuery(sQry);
iSubgroupNId[i] = Convert.ToInt32(DBConnection.ExecuteScalarSqlQuery("SELECT @@IDENTITY"));
}
}
//*** IUSNId
if (bNewIUS)
{
//*** Insert New IUS
for (i = 0; i <= iSubgroupNId.Length - 1; i++)
{
//-- Get query from DAL
sQry = DBQueries.Calculates.InsertNIdsInIUSTable(iIndicatorNId, iUnitNId, iSubgroupNId[i]);
//-- Using DAL for query execution and getting identity value
DBConnection.ExecuteNonQuery(sQry);
iIUSNId[i] = Convert.ToInt32(DBConnection.ExecuteScalarSqlQuery("SELECT @@IDENTITY"));
//*** Create Relationship with IndicatorClassification
string sICGuid;
sClassGuid = this.DESheetInformation.ClassGUID.Split("{}".ToCharArray());
sICGuid = "'" + this.DESheetInformation.SectorGUID + "'";
for (j = 0; j <= sClassGuid.Length - 1; j++)
{
sICGuid += ",'" + sClassGuid[j].ToString() + "'";
}
sQry = DBQueries.Calculates.GetICNIdByGId(sICGuid);
//-- Get Dataview using DAL
oDV = DBConnection.ExecuteDataTable(sQry).DefaultView;
foreach (DataRowView oDVRow1 in oDV)
{
//-- Using DAL for inserting IC IUS relation
sQry = DBQueries.Calculates.InsertIC_IUSRelation(Convert.ToInt32(oDVRow1["IC_NId"]), iIUSNId[i]);
DBConnection.ExecuteNonQuery(sQry);
}
}
}
else if (bNewSector || bNewClass)
{
for (i = 0; i <= iSubgroupNId.Length - 1; i++)
{
sQry = DBQueries.IUS.GetIUSByI_U_S(iIndicatorNId.ToString(), iUnitNId.ToString(), iSubgroupNId[i].ToString());
iIUSNId[i] = Convert.ToInt32(DBConnection.ExecuteScalarSqlQuery(sQry));
if (bNewSector)
{
//-- Using DAL for inserting IC IUS relation
sQry = DBQueries.Calculates.InsertIC_IUSRelation(iSectorNID, iIUSNId[i]);
DBConnection.ExecuteNonQuery(sQry);
}
if (bNewClass)
{
for (j = 0; j <= iClassNId.Length - 1; j++)
{
//-- Using DAL for inserting IC IUS relation
sQry = DBQueries.Calculates.InsertIC_IUSRelation(iClassNId[j], iIUSNId[i]);
try
{
DBConnection.ExecuteNonQuery(sQry);
}
catch (Exception ex)
{
}
}
}
}
}
else
{
int Index = 0;
foreach (int SubgroupNId in iSubgroupNId)
{
//-- Using DAL Query for getting IUSNId
sQry = DBQueries.Calculates.GetIUSNIdByIUS(iIndicatorNId, iUnitNId, SubgroupNId);
iIUSNId[Index] = Convert.ToInt32(DBConnection.ExecuteScalarSqlQuery(sQry));
Index += 1;
}
}
string sTimePeriod = string.Empty;
int iTimePeriod_NId = 0;
Hashtable oTimePeriod = new Hashtable();
string sAreaId = string.Empty;
int iArea_NId = 0;
Hashtable oAreaId = new Hashtable();
string sDataValue = string.Empty;
System.DateTime dtStartDate = System.DateTime.Now;
System.DateTime dtEndDate = System.DateTime.Now;
string sSubgroup = string.Empty;
string sSource = string.Empty;
int iSource_NId = 0;
Hashtable oSource = new Hashtable();
string sFootNote = "";
int iFootNote_NId = -1;
Hashtable oFootNote = new Hashtable();
string sData_Denominator = "";
IWorksheet DataSheet;
DIDatabase DIdatabase = null;
this.DIExcel = new DIExcel(tempDESheetPath);
// this.DIExcel = new DIExcel(this.TempLogFilePath);
DataSheet = DIExcel.GetWorksheet(0);
{
//For each record in excel sheet '*** Get TimePeriod_NId, Area_NId, Data Value, Subgroup, SourceNId
for (i = 10; i <= miCtrFillTo; i++)
{
try
{
if (DataSheet.Cells[i, 0].Value.ToString() == "" || DataSheet.Cells[i, 1].Value.ToString() == "" || DataSheet.Cells[i, 3].Value.ToString() == "" || DataSheet.Cells[i, 4].Value.ToString() == "" || DataSheet.Cells[i, 5].Value.ToString() == "")
{
}
//*** If TimePeriod, AreaID, DataValue,Subgroup, Source is blank leave this record
else
{
//*** TimePeriod
sTimePeriod = DataSheet.Cells[i, 0].Value.ToString();
iTimePeriod_NId = -1;
if (CheckDate(sTimePeriod))
{
//*** Check for Valid TimeFormat allowed in DE
if (oTimePeriod.ContainsKey(sTimePeriod))
{
iTimePeriod_NId = Convert.ToInt32(oTimePeriod[sTimePeriod]);
}
else
{
sQry = DBQueries.Calculates.GetTimeperiodNIdByTimePeriod(sTimePeriod);
iTimePeriod_NId = Convert.ToInt32(DBConnection.ExecuteScalarSqlQuery(sQry));
if (iTimePeriod_NId == 0)
{
// Using DAL for getting and executing Query for inserting timeperiod
sQry = DI_LibDAL.Queries.Timeperiod.Insert.InsertTimeperiod(sDB_Prefix, sTimePeriod);
DBConnection.ExecuteNonQuery(sQry);
iTimePeriod_NId = Convert.ToInt32(DBConnection.ExecuteScalarSqlQuery("SELECT @@IDENTITY"));
}
oTimePeriod.Add(sTimePeriod, iTimePeriod_NId);
}
SetDate(sTimePeriod, ref dtStartDate, ref dtEndDate);
}
else
{
iTimePeriod_NId = -1;
}
//*** Area
sAreaId = DataSheet.Cells[i, 1].Value.ToString();
iArea_NId = -1;
if (oAreaId.ContainsKey(sAreaId))
{
iArea_NId = Convert.ToInt32(oAreaId[sAreaId]);
}
else
{
//-- Using DAL for getting AreaNId Using AreaID
sQry = DBQueries.Calculates.GetAreaNIdByAreaID(sAreaId);
iArea_NId = Convert.ToInt32(DBConnection.ExecuteScalarSqlQuery(sQry));
if (iArea_NId == 0)
{
oAreaId.Add(sAreaId, -1);
}
//*** Don't add New areas to database
else
{
oAreaId.Add(sAreaId, iArea_NId);
}
}
//*** Data value
sDataValue = DataSheet.Cells[i, 3].Value.ToString();
//*** Subgroup
sSubgroup = DataSheet.Cells[i, 4].Value.ToString();
//*** Source
sSource = DI_LibBAL.Utility.DICommon.RemoveQuotes(DataSheet.Cells[i, 5].Value.ToString());
iSource_NId = -1;
if (oSource.ContainsKey(sSource))
{
iSource_NId = Convert.ToInt32(oSource[sSource]);
}
else
{
sQry = DBQueries.IndicatorClassification.GetIC(FilterFieldType.Name, " '" + sSource + "'", ICType.Source, FieldSelection.NId);
iSource_NId = Convert.ToInt32(DBConnection.ExecuteScalarSqlQuery(sQry));
if (iSource_NId == 0)
{
string[] sSourceArr;
sSourceArr = sSource.Split('_');
//Publisher_ISOCode_Year: Parent= Publisher; Child= Abbr_Year
if (sSourceArr.Length >= 2)
{
//*** Insert Parent
sQry = DBQueries.IndicatorClassification.GetIC(FilterFieldType.Name, " '" + sSourceArr[0] + "'", ICType.Source, FieldSelection.NId);
iSource_NId = Convert.ToInt32(DBConnection.ExecuteScalarSqlQuery(sQry));
if (iSource_NId == 0)
{
sQry = DI_LibDAL.Queries.IndicatorClassification.Insert.InsertIC(sDB_Prefix, sLng_Suffix, sSourceArr[0], Guid.NewGuid().ToString(), false, -1, "", ICType.Source);
DBConnection.ExecuteNonQuery(sQry);
iSource_NId = Convert.ToInt32(DBConnection.ExecuteScalarSqlQuery("SELECT @@IDENTITY"));
}
//*** Create relationship
for (j = 0; j <= iIUSNId.Length - 1; j++)
{
sQry = DBQueries.Calculates.InsertIC_IUSRelation(iSource_NId, iIUSNId[j]);
//'sQry = "INSERT INTO " & sDB_Prefix & "Indicator_Classifications_IUS" & " (IC_NId,IUSNId) VALUES (" & iSource_NId & "," & iIUSNId(j) & ")"
try
{
DBConnection.ExecuteNonQuery(sQry);
}
catch (Exception ex)
{
}
}
//*** Insert Source
// Using DAL for inserting Source
sQry = DI_LibDAL.Queries.IndicatorClassification.Insert.InsertIC(sDB_Prefix, sLng_Suffix, sSource.Replace("'", "''"), Guid.NewGuid().ToString(), false, iSource_NId, "", ICType.Source);
DBConnection.ExecuteNonQuery(sQry);
iSource_NId = Convert.ToInt32(DBConnection.ExecuteScalarSqlQuery("SELECT @@IDENTITY"));
//*** Create relationship
for (j = 0; j <= iIUSNId.Length - 1; j++)
{
sQry = DBQueries.Calculates.InsertIC_IUSRelation(iSource_NId, iIUSNId[j]);
try
{
DBConnection.ExecuteNonQuery(sQry);
}
//' oDestDB.ExecuteNonQuery(sQry)
catch (Exception ex)
{
}
}
}
else
{
//iSource_NId = -1
if (sSource.Trim() != "")
{
//*** Insert Parent as "Global"
sQry = DBQueries.IndicatorClassification.GetIC(FilterFieldType.Name, "Global", ICType.Source, FieldSelection.NId);
iSource_NId = Convert.ToInt32(DBConnection.ExecuteScalarSqlQuery(sQry));
if (iSource_NId == 0)
{
sQry = DI_LibDAL.Queries.IndicatorClassification.Insert.InsertIC(sDB_Prefix, sLng_Suffix, "Global", Guid.NewGuid().ToString(), false, -1, "", ICType.Source);
DBConnection.ExecuteNonQuery(sQry);
iSource_NId = Convert.ToInt32(DBConnection.ExecuteScalarSqlQuery("SELECT @@IDENTITY"));
}
//*** Create relationship
for (j = 0; j <= iIUSNId.Length - 1; j++)
{
sQry = DBQueries.Calculates.InsertIC_IUSRelation(iSource_NId, iIUSNId[j]);
try
{
DBConnection.ExecuteNonQuery(sQry);
}
catch (Exception ex)
{
}
}
//*** Insert Source
sQry = DI_LibDAL.Queries.IndicatorClassification.Insert.InsertIC(sDB_Prefix, sLng_Suffix, "Global_" + sSource.Replace("'", "''"), Guid.NewGuid().ToString(), false, iSource_NId, "", ICType.Source);
DBConnection.ExecuteNonQuery(sQry);
iSource_NId = Convert.ToInt32(DBConnection.ExecuteScalarSqlQuery("SELECT @@IDENTITY"));
//*** Create relationship
for (j = 0; j <= iIUSNId.Length - 1; j++)
{
sQry = DBQueries.Calculates.InsertIC_IUSRelation(iSource_NId, iIUSNId[j]);
try
{
DBConnection.ExecuteNonQuery(sQry);
}
catch (Exception ex)
{
}
}
}
}
}
else
{
//*** If New IUS was created then Create Relationship with Source
if (bNewIUS)
{
// using DAL query for getting parentNId
sQry = DBQueries.Calculates.GetICParentNIdByICNId(iSource_NId, "SR");
iParentNId = Convert.ToInt32(DBConnection.ExecuteScalarSqlQuery(sQry));
if (iParentNId != 0)
{
for (j = 0; j <= iIUSNId.Length - 1; j++)
{
sQry = DBQueries.Calculates.InsertIC_IUSRelation(iParentNId, iIUSNId[j]);
try
{
DBConnection.ExecuteNonQuery(sQry);
}
catch (Exception ex)
{
//*** database maintains unique composite key for IC_NId and IUSNId - this will prevent duplicate entry if any
}
}
}
for (j = 0; j <= iIUSNId.Length - 1; j++)
{
sQry = DBQueries.Calculates.InsertIC_IUSRelation(iSource_NId, iIUSNId[j]);
try
{
DBConnection.ExecuteNonQuery(sQry);
}
catch (Exception ex)
{
Console.Write(ex.Message);
}
}
}
}
oSource.Add(sSource, iSource_NId);
}
try
{
sFootNote = DataSheet.Cells[i, 6].Value.ToString();
}
catch (Exception ex)
{
sFootNote = "";
}
iFootNote_NId = -1;
if (sFootNote != "")
{
if (oFootNote.ContainsKey(sFootNote))
{
iFootNote_NId = Convert.ToInt32(oFootNote[sFootNote]);
}
else
{
sQry = DBQueries.Calculates.GetFootNoteNIdByFootNote(Utility.DICommon.EscapeWildcardChar(Utility.DICommon.RemoveQuotes(sFootNote)));
iFootNote_NId = Convert.ToInt32(DBConnection.ExecuteScalarSqlQuery(sQry));
if (iFootNote_NId == 0)
{
sQry = DI_LibDAL.Queries.Footnote.Insert.InsertFootnote(sDB_Prefix, sLng_Suffix, sFootNote, Guid.NewGuid().ToString());
DBConnection.ExecuteNonQuery(sQry);
iFootNote_NId = Convert.ToInt32(DBConnection.ExecuteScalarSqlQuery("SELECT @@IDENTITY"));
}
oFootNote.Add(sFootNote, iFootNote_NId);
}
}
try
{
if (Utility.DICommon.IsNumeric(DataSheet.Cells[i, 7].Value.ToString(), System.Threading.Thread.CurrentThread.CurrentCulture))
{
sData_Denominator = DataSheet.Cells[i, 7].Value.ToString() + ",";
}
else
{
sData_Denominator = "";
}
}
catch (Exception ex)
{
sData_Denominator = "";
}
if (iIUSNId[0] == -1 | iTimePeriod_NId == -1 | iArea_NId == -1 | iSource_NId == -1 | sDataValue == "")
{
}
else
{
if (this.ApplicationWizardType == WizardType.HundredMinus)
{
sQry = DBQueries.Calculates.InsertDataForCalculate(iIUSNId[Array.IndexOf(this.DESheetInformation.Subgroup, sSubgroup)], iTimePeriod_NId, iArea_NId, sDataValue, "#" + dtStartDate.ToString("MM/dd/yyyy", System.Globalization.DateTimeFormatInfo.InvariantInfo) + "#", "#" + dtEndDate.ToString("MM/dd/yyyy", System.Globalization.DateTimeFormatInfo.InvariantInfo) + "#", "", iFootNote_NId, iSource_NId);
DBConnection.ExecuteNonQuery(sQry);
}
else
{
sQry = DBQueries.Calculates.InsertDataForCalculate(iIUSNId[Array.IndexOf(this.DESheetInformation.Subgroup, sSubgroup)], iTimePeriod_NId, iArea_NId, sDataValue, "#" + dtStartDate.ToString("MM/dd/yyyy", System.Globalization.DateTimeFormatInfo.InvariantInfo) + "#", "#" + dtEndDate.ToString("MM/dd/yyyy", System.Globalization.DateTimeFormatInfo.InvariantInfo) + "#", sData_Denominator, iFootNote_NId, iSource_NId);
DBConnection.ExecuteNonQuery(sQry);
}
}
}
}
catch (Exception ex)
{
}
}
// Update Indicator Unit subgroupNId
DIdatabase = new DIDatabase(DBConnection, DBQueries);
DIdatabase.UpdateIndicatorUnitSubgroupNIDsInData();
DBConnection.Dispose();
}
//1.Time - 2.AreaId - 3.AreaName - 4.DataValue - 5.Subgroup - 6.Source - 7.Footnote - 8.Denominator - 12.SubgroupNId
//DataSheet = null;
if ((oDV != null))
{
oDV.Dispose();
oDV = null;
}
}
/// <summary>
/// Get the area level name
/// </summary>
/// <param name="dbConnection"></param>
/// <param name="tableNames"></param>
/// <param name="AreaNId"></param>
/// <returns></returns>
public static string GetAreaLevelName(DIConnection dbConnection, DITables tableNames, string AreaNId)
{
string RetVal = string.Empty;
StringBuilder SBQry = new StringBuilder();
string StrQry = string.Empty;
try
{
SBQry.Append("SELECT L." + Area_Level.AreaLevelName);
SBQry.Append(" FROM "+ QDSConstants.QDSTables.NewParentTable.TableName +" AS P ");
SBQry.Append(" INNER JOIN ("+ tableNames.AreaLevel +" AS L INNER JOIN "+ tableNames.Area +" AS A ON L."+ Area_Level.AreaLevel +" = A."+Area.AreaLevel + "+1) ");
SBQry.Append(" ON P." + QDSConstants.QDSTables.NewParentTable.Columns.AreaNId + " = A." + Area.AreaNId);
SBQry.Append(" where cstr(A." + Area.AreaNId + ") = " + AreaNId);
StrQry = SBQry.ToString();
RetVal = Convert.ToString(dbConnection.ExecuteScalarSqlQuery(StrQry));
}
catch (Exception)
{
throw;
}
return RetVal;
}
private bool IsAssistantTblExists(DIConnection dbConnection)
{
bool RetVal = false;
DIQueries DBQueries;
try
{
DBQueries = new DIQueries(dbConnection.DIDataSetDefault(), dbConnection.DILanguageCodeDefault(dbConnection.DIDataSetDefault()));
// Check availability of table
if (dbConnection.ExecuteScalarSqlQuery("SELECT count(*) FROM " + DBQueries.TablesName.Assistant + " WHERE 1=1") != null)
{
RetVal = true;
}
}
catch (Exception)
{
}
return RetVal;
}
private bool IsNotesTblExists(ref DIConnection oDT_DB, ref DIQueries oDA_Queries)
{
string sTblName = "";
bool bReturn = true;
string sTablePrefix = "";
//-- check tables exists in source database
try
{
sTablePrefix = oDT_DB.ExecuteScalarSqlQuery(this.NotesQuery.Import_Prefix()).ToString() + "_";
//-- Get all Languages
DataView oDV = oDT_DB.ExecuteDataTable(this.NotesQuery.GetDefaultLanguages( false, sTablePrefix)).DefaultView;
if (oDV.Count > 0)
{
//-- Check availability of table
sTblName = sTablePrefix + "Notes_" + oDV[0]["Language_Code"].ToString();
try
{
if (oDT_DB.ExecuteScalarSqlQuery("SELECT count(*) FROM " + sTblName + " WHERE 1=1") == null)
{
bReturn = false;
goto endoffunc_;
}
}
catch (Exception )
{
bReturn = false;
goto endoffunc_;
}
bReturn = true;
}
}
catch (Exception )
{
bReturn = false;
}
endoffunc_:
return bReturn;
}
/// <summary>
/// Returns the metadata Description of specified database along with IUS, Area, TimePeriod & Data Count.
/// </summary>
public static string GetDBMetaData(DIConnection dbConnection, DIQueries dbQueries)
{
string RetVal = string.Empty;
string MaxAreaLevel = string.Empty;
try
{
DataTable DTMeta = dbConnection.ExecuteDataTable(dbQueries.DBMetadata.GetDBMetadata());
//-- Get Maximum Area Level in Area Tables
String AreaLevel = dbConnection.ExecuteScalarSqlQuery(DIQueries.GetMaxValue(dbQueries.TablesName.Area, Area.AreaLevel, string.Empty)).ToString();
if (AreaLevel.Length > 0)
{
MaxAreaLevel = System.Int32.Parse(AreaLevel).ToString();
}
if (DTMeta.Rows.Count > 0)
{
RetVal = DTMeta.Rows[0][DBMetaData.Description].ToString();
// IUS count
if (DTMeta.Rows[0][DBMetaData.IUSCount].ToString() != null)
{
RetVal += "\n\n" + DILanguage.GetLanguageString("IUS") + " " + string.Format("{0:##,###}", DTMeta.Rows[0][DBMetaData.IUSCount]);
}
// Indicator
RetVal += " " + DILanguage.GetLanguageString("INDICATOR") + " " + DTMeta.Rows[0][DBMetaData.IndicatorCount].ToString();
// Area
if (DTMeta.Rows[0][DBMetaData.AreaCount].ToString() != null)
{
RetVal += " " + DILanguage.GetLanguageString("AREA") + " " + string.Format("{0:##,###}", DTMeta.Rows[0][DBMetaData.AreaCount]);
}
//-- AreaLevel
if (MaxAreaLevel.Length > 0)
{
RetVal += " " + DILanguage.GetLanguageString("AREA_LEVEL") + " " + MaxAreaLevel;
}
// Time
RetVal += " " + DILanguage.GetLanguageString("TIMEPERIOD") + " " + DTMeta.Rows[0][DBMetaData.TimeperiodCount].ToString();
// Source
RetVal += " " + DILanguage.GetLanguageString("SOURCE") + " " + DTMeta.Rows[0][DBMetaData.SourceCount].ToString();
// Data count
if (DTMeta.Rows[0][DBMetaData.DataCount].ToString() != null)
{
RetVal += " " + DILanguage.GetLanguageString("DATA") + " " + string.Format("{0:##,###}", DTMeta.Rows[0][DBMetaData.DataCount]);
}
//-- Get Last updated date from database table. (Column is "Pub_date")
if (DTMeta.Rows[0][DBMetaData.PublisherDate] != null)
{
if (string.IsNullOrEmpty(DTMeta.Rows[0][DBMetaData.PublisherDate].ToString()) == false)
{
RetVal += Environment.NewLine + DILanguage.GetLanguageString("LAST_UPDATED") + " " + ((DateTime)(DTMeta.Rows[0][DBMetaData.PublisherDate])).ToShortDateString();
}
}
}
else
{
// Get area count
string AreaCount = dbConnection.ExecuteScalarSqlQuery(DIQueries.GetTableRecordsCount(dbQueries.TablesName.Area, string.Empty)).ToString();
//// Get IUS count
String IUSCount = dbConnection.ExecuteScalarSqlQuery(DIQueries.GetTableRecordsCount(dbQueries.TablesName.IndicatorUnitSubgroup, string.Empty)).ToString();
if (IUSCount.Length > 0)
{
RetVal += DILanguage.GetLanguageString("IUS") + " " + string.Format("{0:##,###}", System.Int32.Parse(IUSCount));
}
// Get Indicator count
string IndicatorCount = dbConnection.ExecuteScalarSqlQuery(DIQueries.GetTableRecordsCount(dbQueries.TablesName.Indicator, string.Empty)).ToString();
RetVal += " " + DILanguage.GetLanguageString("INDICATOR") + " " + IndicatorCount;
// Area Count
if (AreaCount.Length > 0)
{
RetVal += " " + DILanguage.GetLanguageString("AREA") + " " + string.Format("{0:##,###}", System.Int32.Parse(AreaCount));
}
//-- AreaLevel
if (MaxAreaLevel.Length > 0)
{
RetVal += " " + DILanguage.GetLanguageString("AREA_LEVEL") + " " + MaxAreaLevel;
}
// Get timeperiod count
String TimeperiodCount = dbConnection.ExecuteScalarSqlQuery(DIQueries.GetTableRecordsCount(dbQueries.TablesName.TimePeriod, string.Empty)).ToString();
RetVal += " " + DILanguage.GetLanguageString("TIMEPERIOD") + " " + TimeperiodCount;
//// Get source count
String SourceCount = dbConnection.ExecuteScalarSqlQuery(DIQueries.GetTableRecordsCount(dbQueries.TablesName.IndicatorClassifications, IndicatorClassifications.ICType + " =" + DIQueries.ICTypeText[ICType.Source])).ToString();
RetVal += " " + DILanguage.GetLanguageString("SOURCE") + " " + SourceCount;
//// Get data count
String DataCount = dbConnection.ExecuteScalarSqlQuery(DIQueries.GetTableRecordsCount(dbQueries.TablesName.Data, string.Empty)).ToString();
if (DataCount.Length > 0)
{
RetVal += " " + DILanguage.GetLanguageString("DATA") + " " + string.Format("{0:##,###}", System.Int32.Parse(DataCount));
}
}
RetVal = RetVal.Trim();
}
catch
{
}
return RetVal;
}
private void CreateNotesTableForAllLngs(string sSrcDB, string sTRGTblPrefix, string sSrcDB_Prefix, ref DIConnection sourceDIConnection, ref DIQueries sourceDbQueries)
{
DataView dvLngs = null;
string sLngCode = "";
try
{
//-- create common Notes table
this._TargetDBConnection.ExecuteNonQuery(this.NotesQuery.IMP_Create_Notes_Data(sTRGTblPrefix));
this._TargetDBConnection.ExecuteNonQuery(this.NotesQuery.IMP_Create_Notes_profile(sTRGTblPrefix));
//-- Get all Languages from Imp_To.mdb(target Database)
dvLngs = this._TargetDBConnection.ExecuteDataTable(this.NotesQuery.GetDefaultLanguages(false, sTRGTblPrefix)).DefaultView;
foreach (DataRowView drvLng in dvLngs)
{
//-- check language exists in source database
sLngCode = drvLng[Language.LanguageCode].ToString();
this.CreateSrcConnection(ref sourceDIConnection, sSrcDB);
if ((int)sourceDIConnection.ExecuteScalarSqlQuery(this.NotesQuery.check_language_exists(sLngCode, "", false, sTRGTblPrefix)) > 0)
{
sLngCode = "_" + sLngCode;
this.DisposeSrcConnection(ref sourceDIConnection);
//-- create LngBsd Notes link tables
this.CreateNotesLinkTables(sSrcDB, sSrcDB_Prefix, sLngCode);
//-- Create LngBsd Notes tables : UT_Notes_en, UT_Notes_Classification_en
this._TargetDBConnection.ExecuteNonQuery(this.NotesQuery.IMP_Create_Notes(sTRGTblPrefix, sLngCode));
this._TargetDBConnection.ExecuteNonQuery(this.NotesQuery.IMP_Create_Notes_Classification(sTRGTblPrefix, sLngCode));
//-- Delete unmatched records from UT_Notes_en table
this._TargetDBConnection.ExecuteNonQuery(this.NotesQuery.IMP_DeleteUnMatchedFrm_Notes(sTRGTblPrefix, sLngCode));
//-- drop link tables
DropLngBasedNotesLinkTbl();
}
}
//-- drop _notes table
this._TargetDBConnection.ExecuteNonQuery("Drop table _Notes");
}
catch (Exception )
{
}
finally
{
if ((dvLngs != null))
dvLngs.Dispose();
dvLngs = null;
}
}
private void ImportNotes(string sourceDBFileNameWPath, ref DIConnection sourceDBconnection, ref DIQueries sourceDBQueries)
{
string TargetDBPrefix = "";
string sSrcDB_Prefix = "";
string SourceDBPrefix = sSrcDB_Prefix;
DataView DVLngs = null;
DataView DvSrc;
string LanguageCode = "";
string TableName = "";
bool IsMainTableCreated = false;
string sqlString = "";
bool IsNotesTblExistsInTarget = false;
bool IsUpdatedNotesData = false;
try
{
//sourceDBQueries.TablePrefix = sourceDBconnection.ExecuteScalarSqlQuery(this.NotesQuery.Import_Prefix()).ToString();
TargetDBPrefix = this._TargetDBConnection.DIDataSetDefault();
sSrcDB_Prefix = TargetDBPrefix;
SourceDBPrefix = sSrcDB_Prefix;
//-- Get all Languages from Imp_To.mdb(target Database)
DVLngs = this._TargetDBConnection.ExecuteDataTable(this.NotesQuery.GetDefaultLanguages(false, TargetDBPrefix)).DefaultView;
//-- check Notes table exists in target database or not
IsNotesTblExistsInTarget = this.IsNotesDataTblExists(TargetDBPrefix, ref this._TargetDBConnection);
if (IsNotesTblExistsInTarget)
{
//-- update Data_Nid in UT_Notes_Data table
sqlString = this.NotesQuery.IMP_Update_UTNotesData(TargetDBPrefix);
this._TargetDBConnection.ExecuteNonQuery(sqlString);
}
foreach (DataRowView drvLng in DVLngs)
{
//-- check language exists in source database
LanguageCode = drvLng[Language.LanguageCode].ToString();
if ((int)sourceDBconnection.ExecuteScalarSqlQuery(this.NotesQuery.check_language_exists(LanguageCode, "", false, SourceDBPrefix)) > 0)
{
try
{
//-- Step 1. create Temp tables for Notes_Data & Notes_profile :
//--------------------------------------------------------------------
if (IsMainTableCreated == false)
{
sourceDBconnection.Dispose();
//-- Notes_Data
this._TargetDBConnection.ExecuteNonQuery(this.NotesQuery.CreateTempNoteDataTable(sourceDBFileNameWPath, sSrcDB_Prefix + "Notes_Data", "lnk_Notes_Data"));
//-- Notes_profile
this._TargetDBConnection.ExecuteNonQuery(this.NotesQuery.CreateTempNotesProfileTable(sourceDBFileNameWPath, sSrcDB_Prefix + "Notes_profile", "lnk_Notes_profile"));
//-- create Temp_Notes_Profile table
this._TargetDBConnection.ExecuteNonQuery(this.NotesQuery.IMP_CreateTemp_Notes_Profile());
IsMainTableCreated = true;
sourceDBconnection = new DIConnection(DIServerType.MsAccess, string.Empty, string.Empty, sourceDBFileNameWPath, string.Empty, Constants.DBPassword);
}
//-- Step 2. create _Notes table : ( Select *, 0 as _mapped into [_Notes] FROM lnk_Notes_data)
try
{
this._TargetDBConnection.ExecuteNonQuery("Drop table _Notes");
}
catch(Exception )
{
}
sqlString = this.NotesQuery.IMP_Create_NotesTbl();
this._TargetDBConnection.ExecuteNonQuery(sqlString);
//-- Step 3. .UPDATE (_Notes AS N INNER JOIN _Data ON N.Data_NId = [_Data].Data_NId)
//-- INNER JOIN UT_Data ON ([_Data].Source_NId = UT_Data.Source_NId) AND
//-- ([_Data].FootNote_NId = UT_Data.FootNote_NId) AND ([_Data].Area_NId = UT_Data.Area_NId) AND
//-- ([_Data].TimePeriod_NId = UT_Data.TimePeriod_NId) AND ([_Data].IUSNId = UT_Data.IUSNId)
//-- SET N.Data_Nid=UT_Data.Data_Nid, Mapped=1 ;
sqlString = this.NotesQuery.IMP_Update_Notes(TargetDBPrefix);
this._TargetDBConnection.ExecuteNonQuery(sqlString);
//-- Step 4. Delete * from _Notes where Mapped <>1
sqlString = this.NotesQuery.IMP_DeleteUnMatchedNotes();
this._TargetDBConnection.ExecuteNonQuery(sqlString);
if (IsNotesTblExistsInTarget == false)
{
//-- Step 5. if Notes_Data doesn't exists in Target database then create all notes tables
this.CreateNotesTableForAllLngs(sourceDBFileNameWPath, TargetDBPrefix, sSrcDB_Prefix, ref sourceDBconnection, ref sourceDBQueries);
break; // TODO: might not be correct. Was : Exit For
}
else
{ //temp
//this.DisposeSrcConnection(ref sourceDBconnection);
LanguageCode = "_" + LanguageCode;
//-- create lngBsd link tables: lnk_Notes, lnk_Notes_Classification
CreateNotesLinkTables(sourceDBFileNameWPath, sSrcDB_Prefix, LanguageCode);
//-- create lngBsd temp tables :Temp_Notes, Temp_Notes_Classification
CreateTempNotesTables();
//-- set mapped to 0 in _Notes table where notes are already exists in UT_Notes_Data table
sqlString = this.NotesQuery.IMP_Update_AlreadyExistsNotes(TargetDBPrefix, LanguageCode);
this._TargetDBConnection.ExecuteNonQuery(sqlString);
//-- Step 4. Delete * from _Notes where Mapped <>1
sqlString = this.NotesQuery.IMP_DeleteUnMatchedNotes();
this._TargetDBConnection.ExecuteNonQuery(sqlString);
//-- delete unmatched records frm Temp_Notes table (which are not in _Notes)
this._TargetDBConnection.ExecuteNonQuery(this.NotesQuery.IMP_DeleteUnMatchedFrm_Temp_Notes());
//-- bug fixed on 16Jan,2007: allow to insert duplicate values
//-- check Notes with Profile_Email and Classification_Name already exists in target database
//-- and update temp_notes table
////this._TargetDBConnection.ExecuteNonQuery(this._TargetDBQueries.IMP_MatchNotes(sTRGTblPrefix, sLngCode))
//-- if exists then overwrite, otherwise create new record
//-- update UT_notes_en N set N.Notes= T. where mapped =1 in temp_notes t
this._TargetDBConnection.ExecuteNonQuery(this.NotesQuery.IMP_UpdatedMatchedNotes(TargetDBPrefix, LanguageCode));
//-- delete records from Temp_Notes table where mapped =1 (means already exists in target database)
this._TargetDBConnection.ExecuteNonQuery(this.NotesQuery.IMP_DeleteMatchedFrmTempNotes());
//-- update Notes_Data and Notes_Profile table
//-- Bulk Insert : UT_Notes_Profile_en
this._TargetDBConnection.ExecuteNonQuery(this.NotesQuery.IMP_InsertNotesProfile(TargetDBPrefix));
//-- Bulk Insert : UT_Notes_Classification_en
this._TargetDBConnection.ExecuteNonQuery(this.NotesQuery.IMP_InsertNotesClassification(TargetDBPrefix, LanguageCode));
//-- insert New_Classification_Nid in Temp_Notes_Classification and Temp_Notes
this._TargetDBConnection.ExecuteNonQuery(this.NotesQuery.IMP_UpdateNewNId_NotesClassification(TargetDBPrefix, LanguageCode));
this._TargetDBConnection.ExecuteNonQuery(this.NotesQuery.IMP_UpdateNewClassificationNid_TempNotes());
//-- insert New_Profile_Nid in Temp_Notes_Profile and Temp_Notes
this._TargetDBConnection.ExecuteNonQuery(this.NotesQuery.IMP_UpdateNewNId_NotesProfile(TargetDBPrefix));
this._TargetDBConnection.ExecuteNonQuery(this.NotesQuery.IMP_UpdateNewProfileNid_TempNotes());
//-- Bulk Insert : UT_Notes_en
this._TargetDBConnection.ExecuteNonQuery(this.NotesQuery.IMP_InsertInNotes(TargetDBPrefix, LanguageCode));
//-- insert new notes_nid in temp_notes table
this._TargetDBConnection.ExecuteNonQuery(this.NotesQuery.IMP_Update_NewNotesNid(TargetDBPrefix, LanguageCode, ""));
if (IsUpdatedNotesData == false)
{
//-- bug fixed on 5July,2007: Changed method name in DAQuery (IMP_InsertInNotesDataForDA)
//-- insert new records in UT_Notes_Data table
this._TargetDBConnection.ExecuteNonQuery(this.NotesQuery.IMP_InsertInNotesDataForDA(TargetDBPrefix));
IsUpdatedNotesData = true;
}
//-- delete lnk_Notes, lnk_Notes_Classification, Temp_Notes, Temp_Notes_Classification, Temp_Notes_Profile
DropTbls();
}
}
catch (Exception ex)
{
//MessageBoxControl.ShowMsg(ex);
}
}
}
}
catch (Exception ex)
{
}
finally
{
if ((DVLngs != null))
DVLngs.Dispose();
DVLngs = null;
if (sourceDBconnection != null)
{
this.DisposeSrcConnection(ref sourceDBconnection);
}
}
//-- drop common link table
try
{
this._TargetDBConnection.ExecuteNonQuery("Drop table lnk_Notes_Data");
this._TargetDBConnection.ExecuteNonQuery("Drop table lnk_Notes_profile");
this._TargetDBConnection.ExecuteNonQuery("Drop table Temp_Notes_profile");
this._TargetDBConnection.ExecuteNonQuery("Drop table _Notes");
}
catch (Exception )
{
}
}
/// <summary>
/// Returns true if ICON table exists otherwise false.
/// </summary>
/// <param name="iconTableName"></param>
/// <param name="dbConnection"></param>
/// <returns></returns>
public static bool IsIconsTblExists(string iconTableName, DIConnection dbConnection)
{
bool RetVal = false;
string sqlString = "Select count(*) from " + iconTableName + " where 1=2";
try
{
if ((dbConnection.ExecuteScalarSqlQuery(sqlString) != null))
{
RetVal = true;
}
}
catch (Exception ex)
{
RetVal = false;
}
return RetVal;
}
/// <summary>
/// Inserts Icon into ICON table
/// </summary>
/// <param name="dbConnection"></param>
/// <param name="dbQueries"></param>
/// <param name="buffer"></param>
/// <param name="iconType"></param>
/// <param name="width"></param>
/// <param name="height"></param>
/// <param name="elementType"></param>
/// <param name="elementNid"></param>
/// <returns></returns>
public static int InsertIcon(DIConnection dbConnection, DIQueries dbQueries, byte[] buffer, string iconType, int width, int height, string elementType, string elementNid)
{
int RetVal = 0;
//System.Data.OleDb.OleDbCommand cmd;
//OleDbParameter prmPic = new OleDbParameter();
DbCommand Command = dbConnection.GetCurrentDBProvider().CreateCommand();
DbParameter Parameter;
string SqlQuery = string.Empty;
try
{
SqlQuery = DevInfo.Lib.DI_LibDAL.Queries.Icon.Insert.InsertIcon(dbQueries.DataPrefix, iconType, width, height, elementType, elementNid);
//-- Change for Online Database
SqlQuery = SqlQuery.Replace("?", "@Element_Icon");
//SqlQuery = dbQueries.FetchAndUpdateIcons(1, clsIcons.TableName, iconType, width, height, elementType, elementNid);
// -- Get New NID generated
//cmd = new OleDbCommand(SqlQuery, (OleDbConnection)dbConnection.GetConnection);
//cmd.CommandType = CommandType.Text;
Command.Connection = dbConnection.GetConnection();
Command.CommandText = SqlQuery;
Command.CommandType = CommandType.Text;
Parameter = dbConnection.GetCurrentDBProvider().CreateParameter();
{
//prmPic.ParameterName = "@Element_Icon";
////the name used in the query for the parameter
//prmPic.OleDbType = OleDbType.Binary;
////set the database type
//prmPic.Value = buffer;
////assign the contents of the buffer to the value of the parameter
Parameter.ParameterName = "@Element_Icon";
//the name used in the query for the parameter
Parameter.DbType= DbType.Binary;
//set the database type
Parameter.Value = buffer;
//assign the contents of the buffer to the value of the parameter
}
//cmd.Parameters.Add(prmPic);
Command.Parameters.Add(Parameter);
//-- add the parameter to the command
//cmd.ExecuteNonQuery();
Command.ExecuteNonQuery();
//-- this saves the image to the database
RetVal =Convert.ToInt32( dbConnection.ExecuteScalarSqlQuery("SELECT @@IDENTITY"));
}
catch (Exception ex)
{
throw new ApplicationException(ex.Message);
}
finally
{
if ((Command != null))
{
Command.Dispose();
}
}
return RetVal;
}
/// <summary>
///
/// </summary>
/// <param name="dbConnection"></param>
/// <param name="languageCode"></param>
/// <param name="searchIndicators"></param>
/// <param name="isSearchForQS"></param>
/// <returns></returns>
public static DataTable GetSearchChildResults(DIConnection dbConnection, string languageCode, string searchIndicators, bool isSearchForQS)
{
DataTable RetVal = null;
StringBuilder SBQry = new StringBuilder();
string StrQry = string.Empty;
string SearchLanguage = languageCode;
int MaxAreaLength = -1;
int MaxDVSeriesLength = -1;
try
{
SBQry.Remove(0, SBQry.Length);
SBQry.Append("Insert into " + QDSConstants.QDSTables.ChildTable.TableName);
SBQry.Append(" (" + QDSConstants.QDSTables.ChildTable.Columns.NId + ", " + QDSConstants.QDSTables.ChildTable.Columns.SearchLanguage + ", " + QDSConstants.QDSTables.ChildTable.Columns.IndicatorNId + ", " + QDSConstants.QDSTables.ChildTable.Columns.UnitNId + ", " + QDSConstants.QDSTables.ChildTable.Columns.AreaNId + ", " + QDSConstants.QDSTables.ChildTable.Columns.IsAreaNumeric + ", " + QDSConstants.QDSTables.ChildTable.Columns.IndicatorName + ", " + QDSConstants.QDSTables.ChildTable.Columns.Unit + ", " + QDSConstants.QDSTables.ChildTable.Columns.Area + ", " + QDSConstants.QDSTables.ChildTable.Columns.DefaultSG + ", " + QDSConstants.QDSTables.ChildTable.Columns.MRDTP + ", " + QDSConstants.QDSTables.ChildTable.Columns.MRD + ", " + QDSConstants.QDSTables.ChildTable.Columns.AreaCount + ", " + QDSConstants.QDSTables.ChildTable.Columns.SGCount + ", " + QDSConstants.QDSTables.ChildTable.Columns.SourceCount + ", " + QDSConstants.QDSTables.ChildTable.Columns.TPCount + ", " + QDSConstants.QDSTables.ChildTable.Columns.DVCount + ", " + QDSConstants.QDSTables.ChildTable.Columns.AreaNIds + ", " + QDSConstants.QDSTables.ChildTable.Columns.SGNIds + ", " + QDSConstants.QDSTables.ChildTable.Columns.SourceNIds + ", " + QDSConstants.QDSTables.ChildTable.Columns.TPNIds + ", " + QDSConstants.QDSTables.ChildTable.Columns.DVSeries + ", " + QDSConstants.QDSTables.ChildTable.Columns.Dimensions + ", " + QDSConstants.QDSTables.ChildTable.Columns.BlockAreaParentNId + ", " + QDSConstants.QDSTables.ChildTable.Columns.IUSNId + ", " + QDSConstants.QDSTables.ChildTable.Columns.AreaParentNId + ")");
SBQry.Append(" SELECT DISTINCT " + QDSConstants.QDSTables.DISearchResult.Columns.NId + ", '" + SearchLanguage + "' As " + QDSConstants.QDSTables.DISearchResult.Columns.SearchLanguage + ", C." + QDSConstants.QDSTables.DISearchResult.Columns.IndicatorNId + ", " + QDSConstants.QDSTables.DISearchResult.Columns.UnitNId + ", C." + QDSConstants.QDSTables.DISearchResult.Columns.AreaNId + ", " + QDSConstants.QDSTables.DISearchResult.Columns.IsAreaNumeric + ", " + QDSConstants.QDSTables.DISearchResult.Columns.IndicatorName + " As [Indicator], " + QDSConstants.QDSTables.DISearchResult.Columns.Unit + ", " + QDSConstants.QDSTables.DISearchResult.Columns.Area + ", " + QDSConstants.QDSTables.DISearchResult.Columns.DefaultSG + ", " + QDSConstants.QDSTables.DISearchResult.Columns.MRDTP + ", " + QDSConstants.QDSTables.DISearchResult.Columns.MRD + ", " + QDSConstants.QDSTables.DISearchResult.Columns.AreaCount + ", " + QDSConstants.QDSTables.DISearchResult.Columns.SGCount + ", " + QDSConstants.QDSTables.DISearchResult.Columns.SourceCount + ", " + QDSConstants.QDSTables.DISearchResult.Columns.TPCount + ", " + QDSConstants.QDSTables.DISearchResult.Columns.DVCount + ", " + QDSConstants.QDSTables.DISearchResult.Columns.AreaNIds + ", " + QDSConstants.QDSTables.DISearchResult.Columns.SGNIds + ", " + QDSConstants.QDSTables.DISearchResult.Columns.SourceNIds + ", " + QDSConstants.QDSTables.DISearchResult.Columns.TPNIds + ", " + QDSConstants.QDSTables.DISearchResult.Columns.DVSeries + ", " + QDSConstants.QDSTables.DISearchResult.Columns.Dimensions + ", " + QDSConstants.QDSTables.DISearchResult.Columns.BlockAreaParentNId + ", " + QDSConstants.QDSTables.DISearchResult.Columns.IUSNId + ", " + QDSConstants.QDSTables.DISearchResult.Columns.AreaParentNId);
SBQry.Append(" FROM (" + QDSConstants.QDSTables.DISearchResult.TableName + "_" + languageCode + " C");
if (isSearchForQS)
{
SBQry.Append(" INNER JOIN " + QDSConstants.QDSTables.SearchAreas.TableName + " A ON C." + QDSConstants.QDSTables.DISearchResult.Columns.AreaNIds + " = cstr(A.AreaNId) ) ");
}
else
{
SBQry.Append(" INNER JOIN " + QDSConstants.QDSTables.SearchAreas.TableName + " A ON C." + QDSConstants.QDSTables.DISearchResult.Columns.AreaParentNId + " = A.AreaNId ) ");
}
SBQry.Append(" INNER JOIN " + QDSConstants.QDSTables.SearchIndicators.TableName + " I ON C." + QDSConstants.QDSTables.DISearchResult.Columns.IndicatorNId + " = I.IndicatorNId");
SBQry.Append(" WHERE '" + SearchLanguage + "' = C.SearchLanguage ");
SBQry.Append(" ORDER BY C." + QDSConstants.QDSTables.DISearchResult.Columns.IndicatorName + ", C." + QDSConstants.QDSTables.DISearchResult.Columns.Area + " ASC");
StrQry = SBQry.ToString();
dbConnection.ExecuteNonQuery(StrQry);
//-- Get max area length
StrQry = "SELECT Max(Len(" + QDSConstants.QDSTables.ChildTable.Columns.Area + ")) FROM " + QDSConstants.QDSTables.ChildTable.TableName;
Int32.TryParse(Convert.ToString(dbConnection.ExecuteScalarSqlQuery(StrQry)),out MaxAreaLength);
//-- Get max data value length
StrQry = "SELECT Max(Len(" + QDSConstants.QDSTables.ChildTable.Columns.DVSeries + ")) FROM " + QDSConstants.QDSTables.ChildTable.TableName;
Int32.TryParse(Convert.ToString(dbConnection.ExecuteScalarSqlQuery(StrQry)), out MaxDVSeriesLength);
//-- Get data with padded length
SBQry.Remove(0, SBQry.Length);
SBQry.Append("SELECT Distinct *, ");
SBQry.Append(QDSConstants.QDSTables.ChildTable.Columns.Area + " & Space(" + MaxAreaLength + " - Len(" + QDSConstants.QDSTables.ChildTable.Columns.Area + ")) as PaddedArea,");
SBQry.Append(" Space(" + MaxDVSeriesLength + " - Len(" + QDSConstants.QDSTables.ChildTable.Columns.DVSeries + ")) & " + QDSConstants.QDSTables.ChildTable.Columns.DVSeries + " as PaddedDVSeries");
SBQry.Append(" FROM " + QDSConstants.QDSTables.ChildTable.TableName);
StrQry = SBQry.ToString();
RetVal = dbConnection.ExecuteDataTable(StrQry);
}
catch (Exception)
{
throw;
}
return RetVal;
}
private Int32 GetPresNIdIfExistInDB(DIConnection dBConnection, string Pres_FileName, int GalleryFolderNid, string pres_Type)
{
string sSql = String.Empty;
Int32 RetVal = 0;
try
{
sSql = GetPresNIdQuery(Pres_FileName, GalleryFolderNid, pres_Type);
RetVal = Convert.ToInt32(dBConnection.ExecuteScalarSqlQuery(sSql));
}
catch (Exception ex)
{
RetVal = 0;
}
return RetVal;
}
/// <summary>
/// Create cache results for levels
/// </summary>
/// <param name="dbConnection"></param>
/// <param name="languageCode"></param>
private void CreateCacheResultsForLevel(DIConnection dbConnection, string languageCode, DITables tableNames)
{
StringBuilder SBQry = new StringBuilder();
string StrQry = string.Empty;
int AreaLevel = 4;
int ProgressCount = 33;
try
{
#region "-- Quick Search (level) --"
// increment progress bar value
this.RaiseProgressChangedEvent(ProgressCount++, string.Empty, string.Empty, false);
//-- 1 Add TempColumn column into Area table
StrQry = "Alter table " + tableNames.Area + " add column " + QDSConstants.QDSTables.Area.Columns.TempColumn + " varchar";
dbConnection.ExecuteNonQuery(StrQry);
//-- 2 Get area levels
StrQry = "Select count(*) from " + tableNames.AreaLevel;
AreaLevel = (int)dbConnection.ExecuteScalarSqlQuery(StrQry);
// increment progress bar value
this.RaiseProgressChangedEvent(ProgressCount++, string.Empty, string.Empty, false);
if (AreaLevel < 4)
{
AreaLevel = 4;
}
//-- 3 Update query and execute it
for (int i = 2; i <= AreaLevel; i++)
{
StrQry = GetUpdateAreaTempColumnQry(i, languageCode, tableNames);
dbConnection.ExecuteNonQuery(StrQry);
}
// increment progress bar value
this.RaiseProgressChangedEvent(ProgressCount++, string.Empty, string.Empty, false);
//-- 4. Update into table DI_Search_Results of AreaNIds column with TempComumn column from ut_Area_xx table
SBQry.Remove(0, SBQry.Length);
SBQry.Append("Update " + QDSConstants.QDSTables.DISearchResult.TableName + "_" + languageCode + " as R");
SBQry.Append(" Inner Join "+ tableNames.Area + " as A");
SBQry.Append(" On R." + QDSConstants.QDSTables.DISearchResult.Columns.AreaNId + " = A." + Area.AreaNId);
SBQry.Append(" And R."+ QDSConstants.QDSTables.DISearchResult.Columns.AreaParentNId + " = A." + Area.AreaParentNId);
SBQry.Append(" Set R." + QDSConstants.QDSTables.DISearchResult.Columns.AreaNIds + " = A.TempColumn");
StrQry = SBQry.ToString();
dbConnection.ExecuteNonQuery(StrQry);
// increment progress bar value
this.RaiseProgressChangedEvent(ProgressCount++, string.Empty, string.Empty, false);
//-- 5 Drop TempColumn column from Area table
StrQry = "Alter table " + tableNames.Area + " Drop Column " + QDSConstants.QDSTables.Area.Columns.TempColumn;
dbConnection.ExecuteNonQuery(StrQry);
#endregion
}
catch (Exception ex)
{
throw ex;
}
}
private string GetVersionDetails(string databaseFileName)
{
string RetVal = string.Empty;
DIConnection TempDBConnection = null;
try
{
if (File.Exists(databaseFileName))
{
TempDBConnection = new DIConnection(DevInfo.Lib.DI_LibDAL.Connection.DIServerType.MsAccess, string.Empty, string.Empty, databaseFileName, string.Empty, string.Empty);
RetVal = "<span style=\"color:gray;font-size:10px;\"> (v." + Convert.ToString(TempDBConnection.ExecuteScalarSqlQuery(DIQueries.GetDatabaseVersion())) + ") </span>";
}
}
catch (Exception)
{
}
finally
{
if (TempDBConnection != null)
{
TempDBConnection.Dispose();
}
}
return RetVal;
}
private string GetVersionDetails(DIConnection dbConnection)
{
string RetVal = string.Empty;
try
{
if (dbConnection != null)
{
RetVal = Convert.ToString(dbConnection.ExecuteScalarSqlQuery(DIQueries.GetDatabaseVersion()));
}
}
catch (Exception)
{
}
return RetVal;
}