//-- Check orgTextual_Data_value exists or not. If column exists then move textual & numeric values into their respective column.
public static void SeparateTextualandNemericData(DIConnection dbConnection, DIQueries dbQueries)
{
string SqlQuery = string.Empty;
try
{
if (ISColumnExists(dbConnection, Constants.Data.Orginal_Data_valueColumn, dbQueries.TablesName.Data))
{
//--Update Textual value true if data value is not Numeric
SqlQuery = "UPDATE " + dbQueries.TablesName.Data + " SET " + Data.IsTextualData + " = 1 WHERE NOT ISNUMERIC(" + Data.DataValue + ")";
dbConnection.ExecuteNonQuery(SqlQuery);
//-- move orgData_Value values into Data_Value
SqlQuery = "UPDATE " + dbQueries.TablesName.Data + " Set "+ Constants.Data.Orginal_Data_valueColumn + "=" + Data.DataValue + " WHERE " + Data.IsTextualData + " = 0";
dbConnection.ExecuteNonQuery(SqlQuery);
//--move orgTextual_Data_value into Textual_Data_value if ISTextual is true
SqlQuery = "UPDATE " + dbQueries.TablesName.Data + " Set " + Constants.Data.Orginal_Textual_Data_valueColumn + " = " + Data.DataValue + " WHERE " + Data.IsTextualData + "<>0";
dbConnection.ExecuteNonQuery(SqlQuery);
//2. Remove Data_Value column and rename orgTextual_Data_Value and orgData_Value to Textual_Data_Value and Data_value"
DIDataValueHelper.RemoveDataValueAndRenameOrgColumns(dbConnection, dbQueries);
}
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// Rename Textual_Data_Value & Data_value TO orgTextual_Data_value & orgData_Value respectively
/// </summary>
/// <returns></returns>
private static bool RenameOriganalDataTableColumnInDatabase(DIConnection dbConnection, DIQueries dbQueries, string oldColumn, string newColumn, string newColumnDataType)
{
bool RetVal = true;
string SqlQuery = string.Empty;
try
{
//CatalogClass CatalogClassObj = new CatalogClass();
//-- cat.ActiveConnection = connString <-- Error here -- Cannot assign to cat.ActiveConnection directly.// Use code at below to assign connection string in C#.
//CatalogClassObj.let_ActiveConnection(dbConnection.ConnectionStringParameters.GetConnectionString());
//CatalogClassObj.Tables[dbQueries.TablesName.Data].Columns[oldColumn].Name = newColumn;
//CatalogClassObj = null;
//-- RENAME COLUMN
SqlQuery = "ALTER TABLE " + dbQueries.TablesName.Data + " ADD COLUMN " + newColumn + " " + newColumnDataType;
dbConnection.ExecuteNonQuery(SqlQuery);
SqlQuery = "UPDATE " + dbQueries.TablesName.Data + " SET " + newColumn + "=" + oldColumn;
dbConnection.ExecuteNonQuery(SqlQuery);
SqlQuery = "ALTER TABLE " + dbQueries.TablesName.Data + " DROP COLUMN " + oldColumn;
dbConnection.ExecuteNonQuery(SqlQuery);
}
catch (Exception ex)
{
throw ex;
}
return(RetVal);
}
/// <summary>
/// Rename Data_value and TextualValue column into OrgData_value and OrgTextualValue
/// and merge both column value into new column Data_Value
/// </summary>
/// <param name="dbConnection"></param>
/// <param name="dbQueries"></param>
public static void MergeTextualandNumericDataValueColumn(DIConnection dbConnection, DIQueries dbQueries)
{
try
{
if (!ISColumnExists(dbConnection, Constants.Data.Orginal_Data_valueColumn, dbQueries.TablesName.Data))
{
//1. Rename Textual_Data_Value & Data_value to orgTextual_Data_value & orgData_Value respectively
RenameOriganalDataTableColumnInDatabase(dbConnection, dbQueries, Data.TextualDataValue, Constants.Data.Orginal_Textual_Data_valueColumn, " Memo");
RenameOriganalDataTableColumnInDatabase(dbConnection, dbQueries, Data.DataValue, Constants.Data.Orginal_Data_valueColumn, " Double ");
System.Threading.Thread.Sleep(100);
//2. Create new column Data_Value of memo data type
string SqlQuery = "ALTER Table " + dbQueries.TablesName.Data + " Add Column " + Data.DataValue + " Memo NULL";
dbConnection.ExecuteNonQuery(SqlQuery);
System.Threading.Thread.Sleep(10);
//3. Merge all data values into Data_Value column
SqlQuery = "UPDATE " + dbQueries.TablesName.Data + " SET " + Data.DataValue + "=" + Constants.Data.Orginal_Data_valueColumn;
dbConnection.ExecuteNonQuery(SqlQuery);
SqlQuery = "UPDATE " + dbQueries.TablesName.Data + " SET " + Data.DataValue + "=" + Constants.Data.Orginal_Textual_Data_valueColumn + " WHERE " + Data.IsTextualData + "=" + true;
dbConnection.ExecuteNonQuery(SqlQuery);
}
}
catch (Exception ex)
{
throw ex;
}
}
private static void RemoveDataValueAndRenameOrgColumns(DIConnection dbConnection, DIQueries dbQueries)
{
string SqlQuery = string.Empty;
//2. Remove Data_Value column and rename orgTextual_Data_Value and orgData_Value to Textual_Data_Value and Data_value"
SqlQuery = "ALTER TABLE " + dbQueries.TablesName.Data + " DROP COLUMN " + Data.DataValue;
dbConnection.ExecuteNonQuery(SqlQuery);
DIDataValueHelper.RenameOriganalDataTableColumnInDatabase(dbConnection, dbQueries, Constants.Data.Orginal_Textual_Data_valueColumn, Data.TextualDataValue, " Memo");
DIDataValueHelper.RenameOriganalDataTableColumnInDatabase(dbConnection, dbQueries, Constants.Data.Orginal_Data_valueColumn, Data.DataValue, " Double ");
}
public static void ClearIcon(DIConnection dbConnection, string dataPrefix, IconElementType elementType)
{
try
{
dbConnection.ExecuteNonQuery(DevInfo.Lib.DI_LibDAL.Queries.Icon.Delete.ClearIcon(dataPrefix, DIIcons.Elements[elementType].ToString()));
}
catch (Exception ex)
{
throw new ApplicationException(ex.ToString());
}
}
/// <summary>
/// Creates Icon tables for all available language
/// </summary>
/// <param name="dbConnection"></param>
/// <param name="dbQueries"></param>
/// <param name="forOnlineDB"></param>
public static void CreateIconsTblsForAllLngs(DIConnection dbConnection, DIQueries dbQueries, bool forOnlineDB)
{
if (DIIcons.IsIconsTblExists(dbQueries.TablesName.Icons, dbConnection) == false)
{
try
{
//-- create Icon table
dbConnection.ExecuteNonQuery(DIIcons.CreateIconsTbl(dbQueries.TablesName.Icons, forOnlineDB));
}
catch (Exception ex)
{
throw new ApplicationException(ex.Message);
}
}
}
/// <summary>
/// Dictionary containing old IconNId as key and new IconNId as value.
/// This information shall be utilised to update metadata xml being inserted / updated
/// </summary>
/// <param name="NidInSourceDB"></param>
/// <param name="NidInTargetDB"></param>
/// <param name="elementType"></param>
/// <param name="sourceQurey"></param>
/// <param name="SourceDBConnection"></param>
/// <param name="targetQurey"></param>
/// <param name="TargetDBConnection"></param>
/// <returns>
/// </returns>
/// <remarks></remarks>
public static Dictionary <string, string> ImportElement(int NidInSourceDB, int NidInTargetDB, IconElementType elementType, DIQueries sourceQurey, DIConnection sourceDBConnection, DIQueries targetQurey, DIConnection targetDBConnection)
{
Dictionary <string, string> RetVal = new Dictionary <string, string>();
string ElementValue = DIIcons.Elements[elementType];
string SqlQuery = string.Empty;
DataTable IconsDatatable;
string OldIconNId = string.Empty;
string NewIconNId = string.Empty;
try {
if ((sourceDBConnection != null))
{
if (DIIcons.IsIconsTblExists(sourceQurey.TablesName.Icons, sourceDBConnection))
{
//-- In Target Database: delete records from UT_Icon table if Icon is already associated with given Element Type
SqlQuery = DevInfo.Lib.DI_LibDAL.Queries.Icon.Delete.DeleteIcon(targetQurey.DataPrefix, ElementValue, NidInTargetDB.ToString());
targetDBConnection.ExecuteNonQuery(SqlQuery);
//-- In Source Database: check Icon is associated with the given Element type in UT_ICon table
SqlQuery = sourceQurey.Icon.GetIcon(NidInSourceDB.ToString(), ElementValue);
IconsDatatable = sourceDBConnection.ExecuteDataTable(SqlQuery);
//-- If associated, then copy it it from Source database into target database
foreach (DataRow Row in IconsDatatable.Rows)
{
//-- Insert Icon and get new IconNId
NewIconNId = DIIcons.InsertIcon(targetDBConnection, targetQurey, (byte[])(Row["Element_Icon"]), Row["Icon_Type"].ToString(), Convert.ToInt32(Row["Icon_Dim_W"]), Convert.ToInt32(Row["Icon_Dim_H"]), ElementValue, Convert.ToString(NidInTargetDB)).ToString();
//-- Add Item to Dictionary with New IconNId as Key and Old IconNId as Value
RetVal.Add(IMG_PREFIX + Row["Icon_NId"].ToString() + ".", IMG_PREFIX + NewIconNId + ".");
}
}
}
}
catch (Exception ex) {
throw new ApplicationException(ex.Message);
}
return(RetVal);
}