/// <summary>
/// Constructor
/// </summary>
/// <param name="connection"></param>
public DbColumnInfoFactory(DIConnection connection)
{
DbConnection DbConnection;
DataTable SchemaTable;
DataTable SchemaColumns;
string[] ObjArrRestrict = new string[4];
string ColumnName = string.Empty;
long ColumnSize = 0;
try
{
if (connection.GetConnection().State == ConnectionState.Open)
{
DbConnection = connection.GetConnection();
ObjArrRestrict[0] = null;
ObjArrRestrict[1] = null;
ObjArrRestrict[2] = null;
ObjArrRestrict[3] = Table;
SchemaTable = DbConnection.GetSchema(TablesCollectionName);
foreach (DataRow row in SchemaTable.Rows)
{
ObjArrRestrict[2] = row[TableName].ToString();
ObjArrRestrict[3] = null;
SchemaColumns = DbConnection.GetSchema(ColumnsCollectionName, ObjArrRestrict);
foreach (DataRow schemaColumnsRow in SchemaColumns.Rows)
{
ColumnName = schemaColumnsRow[DbColumnInfoFactory.ColumnName].ToString();
if ((!Microsoft.VisualBasic.Information.IsDBNull(schemaColumnsRow[DbColumnInfoFactory.CharacterMaximumLength])) & (!(this._DbColumns.ContainsKey(ColumnName))))
{
ColumnSize = Convert.ToInt64(schemaColumnsRow[DbColumnInfoFactory.CharacterMaximumLength]);
this._DbColumns.Add(ColumnName, new DbColumnInfo(ColumnName, ColumnSize));
}
}
}
}
}
catch (Exception ex)
{
throw new ApplicationException(ex.ToString());
}
}
/// <summary>
/// This method is used for generating csv files
/// </summary>
/// <param name="requestParam"></param>
/// <returns></returns>
public string GenerateCsvFile()
{
string CsvFileNames = string.Empty;
string RetVal = string.Empty;
string ConStr = string.Empty;
int DBNId =Convert.ToInt32(Global.GetDefaultDbNId());
string FileBasePath = string.Empty;
string DestFilePath=string.Empty;
string CSVFileName = string.Empty;
//string DtatabaseConnectionname=string.Empty;
string CSVFileStartName = string.Empty;
FileBasePath = Path.Combine(HttpContext.Current.Request.PhysicalApplicationPath + "stock\\data\\CSV_DataFiles\\");
try
{
DeleteExistingFiles();
DIConnection = Global.GetDbConnection(DBNId);
List<string> RetAllLang = getAllDbLangCodes(DBNId);
string AdaptationName = string.Empty;
AdaptationName = Global.adaptation_name;
CSVFileStartName = AdaptationName + "_" + Global.AdaptationYear + "_";
foreach (string lang in RetAllLang)
{
CSVFileName = CSVFileStartName + lang + ".csv";
DestFilePath = FileBasePath + CSVFileName;
ConStr = DIConnection.GetConnection().ConnectionString.ToString() + "Password='******';";
SqlDataReader DataReader = ExecuteSqlQuery(ConStr, lang);
if (CreateCsvFile(DataReader, DestFilePath))
{
if (CreateZipFile(DestFilePath))
{
CsvFileNames = CsvFileNames + Path.GetFileName(DestFilePath).Replace(".csv", "").Replace(CSVFileStartName, "");
CsvFileNames = CsvFileNames + ",";
DeleteCsvFile(DestFilePath);
}
}
}
CsvFileNames = CsvFileNames.Remove(CsvFileNames.Length - 1);
AddCsv_EntryTo_DBXML(CsvFileNames, DBNId);
AddCsv_EntryTo_WebService(CsvFileNames);
RetVal = "true";
}
catch (Exception Ex)
{
Global.CreateExceptionString(Ex, null);
}
return RetVal;
}
/// <summary>
/// Delete Presentaion from Gallery Database
/// </summary>
/// <param name="presentationPath"></param>
/// <param name="dbPath"></param>
/// <remarks>To be deleted</remarks>
public void DeletePresentationFromGalleryDatabase_old(String presentationPath, string dbPath)
{
int PresNIdForDelete = 0;
string PresFileNameForDelete = string.Empty;
string PresGalleryPathForDelete = string.Empty;
string sSql = string.Empty;
string PDS_PresentationNIdTobeUpdated = string.Empty;
//Get Presentaion filename and Gallery path
PresGalleryPathForDelete = Path.GetDirectoryName(presentationPath);
PresFileNameForDelete = Path.GetFileName(presentationPath);
if (this.ValidateDB(dbPath))
{
// Find Presention in PresMaster and get it's NId
try
{
sSql = "SELECT " + PresentationMaster.Pres_NId + " FROM " + DBTable.UT_PresMst + " WHERE " +
PresentationMaster.Pres_FileName + "= '" + PresFileNameForDelete + "'";
// Get pres nid to be deleted
PresNIdForDelete = Convert.ToInt32(this.DBConnection.ExecuteScalarSqlQuery(sSql));
//If pres nid found then delete this pres records from tables
if (PresNIdForDelete != 0)
{
//DELETE FROM Pres keyword
sSql = "DELETE FROM " + DBTable.UT_PresKeyword + " WHERE " + PresentationMaster.Pres_NId + "=" + PresNIdForDelete;
this.DBConnection.ExecuteNonQuery(sSql);
//DELETE FROM Pres Mst
sSql = "DELETE FROM " + DBTable.UT_PresMst + " WHERE " + PresentationMaster.Pres_NId + "=" + PresNIdForDelete;
this.DBConnection.ExecuteNonQuery(sSql);
//Delete from presearch
sSql = "DELETE FROM UT_PreSearches WHERE PDS_Presentation_NIds=" + PresNIdForDelete;
this.DBConnection.ExecuteNonQuery(sSql);
string ConnString = this.DBConnection.GetConnection().ConnectionString;
DIServerType TempserverType = DBConnection.ConnectionStringParameters.ServerType;
this.DBConnection.Dispose();
DIConnection TempDBConn = new DIConnection(ConnString, TempserverType);
System.Data.Common.DbDataAdapter Adapter = TempDBConn.CreateDBDataAdapter();
System.Data.Common.DbCommand cmd = TempDBConn.GetCurrentDBProvider().CreateCommand();
cmd.CommandText = "Select * from " + DBTable.UT_PreSearches;
cmd.Connection = TempDBConn.GetConnection();
Adapter.SelectCommand = cmd;
System.Data.Common.DbCommandBuilder CmdBuilder = TempDBConn.GetCurrentDBProvider().CreateCommandBuilder();
CmdBuilder.DataAdapter = Adapter;
DataSet TargetFileDataset = new System.Data.DataSet();
Adapter.Fill(TargetFileDataset, DBTable.UT_PreSearches);
// Update
foreach (DataRow DRow in TargetFileDataset.Tables[0].Rows)
{
if (DRow[PreSearches.PDS_Presentation_NIds].ToString().Contains(PresNIdForDelete.ToString()))
{
if (DRow[PreSearches.PDS_Presentation_NIds].ToString().EndsWith(PresNIdForDelete.ToString()))
{
DRow[PreSearches.PDS_Presentation_NIds] = DRow[PreSearches.PDS_Presentation_NIds].ToString().Replace(PresNIdForDelete.ToString(), " ").Trim();
}
else
{
DRow[PreSearches.PDS_Presentation_NIds] = DRow[PreSearches.PDS_Presentation_NIds].ToString().Replace(PresNIdForDelete.ToString() + ",", "").Trim();
}
}
}
TargetFileDataset.AcceptChanges();
//update TempDataTable into target database
Adapter.Update(TargetFileDataset, DBTable.UT_PreSearches);
System.Threading.Thread.Sleep(1000);
TempDBConn.Dispose();
this.DBConnection = new DIConnection(ConnString, TempserverType);
}
}
catch (Exception ex)
{
}
}
}
/// <summary>
/// Check and create the IsMapServer column in Databases.mdb database
/// </summary>
private void CheckNCreateIsMapServerColumnInMDBDatabase()
{
DIConnection dIConnection = null;
try
{
dIConnection = new DIConnection(DIServerType.MsAccess, string.Empty, string.Empty, Server.MapPath("../../stock/Database.mdb"),
string.Empty, string.Empty);
try
{
DataTable table = dIConnection.GetConnection().GetSchema("Columns");
var v = from r in table.AsEnumerable() where (r["TABLE_NAME"].ToString() == "MappingInformation" && r["COLUMN_NAME"].ToString() == "mapserver_used") select r;
if (v.Count() == 0)
{
dIConnection.AddColumn("mappinginformation", "mapserver_used", "Text", "False");
}
}
catch
{
}
}
catch (Exception ex)
{
Global.WriteErrorsInLogFolder("error in Registering new database");
Global.CreateExceptionString(ex, null);
}
finally
{
if (dIConnection != null)
{
dIConnection.Dispose();
dIConnection = null;
}
}
}
private void ImportEBook(ref DIConnection Connection, ref DIQueries queries, string languageCode, DITables sourceTableNames, DITables targetTableNames)
{
string SqlString = string.Empty;
string TablePrefix = this._TargetDBConnection.DIDataSetDefault();
DataTable SourceTopicTable = null;
string TargetConnectionString = this._TargetDBConnection.GetConnection().ConnectionString;
string SourceConnectionString = Connection.GetConnection().ConnectionString;
string SourceDBName = Connection.ConnectionStringParameters.DbName;
string TargetDBName = this._TargetDBConnection.ConnectionStringParameters.DbName;
OleDbCommand InsertCommand;
OleDbDataAdapter Adapter;
OleDbCommandBuilder CmdBuilder;
DataSet EbookDataset;
DataRow Row;
try
{
this._TargetDBConnection.ExecuteNonQuery(AssistantQueries.DeleteFrmEBook(targetTableNames.AssistanteBook));
// get record from source database
SourceTopicTable = Connection.ExecuteDataTable(" Select * from " + sourceTableNames.AssistanteBook);
if (SourceTopicTable.Rows.Count > 0)
{
//dispose target and source connection
this._TargetDBConnection.Dispose();
Connection.Dispose();
InsertCommand = new OleDbCommand();
InsertCommand.Connection = new OleDbConnection(TargetConnectionString);
Adapter = new OleDbDataAdapter("Select * from " + sourceTableNames.AssistanteBook, TargetConnectionString);
CmdBuilder = new OleDbCommandBuilder(Adapter);
EbookDataset = new DataSet(sourceTableNames.AssistanteBook);
Adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
Adapter.Fill(EbookDataset, targetTableNames.AssistanteBook); //Fill data adapter
Row = EbookDataset.Tables[0].NewRow();
try
{
Row[Assistant_eBook.EBook] = SourceTopicTable.Rows[0][Assistant_eBook.EBook]; //ShpBuffer
EbookDataset.Tables[0].Rows.Add(Row);
Adapter.Update(EbookDataset, targetTableNames.AssistanteBook); // Save changes to the database
}
catch (Exception ex)
{
ExceptionHandler.ExceptionFacade.ThrowException(ex);
}
if (CmdBuilder != null)
{
CmdBuilder.Dispose();
CmdBuilder = null;
}
if (InsertCommand != null)
{
InsertCommand.Dispose();
InsertCommand = null;
}
if (Adapter != null)
{
Adapter.Dispose();
Adapter = null;
}
//reconnect the source and target database
this._TargetDBConnection = new DIConnection(new DIConnectionDetails(DIServerType.MsAccess,
string.Empty, string.Empty, TargetDBName, string.Empty, Common.Constants.DBPassword));
Connection = new DIConnection(SourceConnectionString, DIServerType.MsAccess);
}
}
catch (Exception ex)
{
ExceptionHandler.ExceptionFacade.ThrowException(ex);
}
finally
{
if (SourceTopicTable != null)
{
SourceTopicTable.Dispose();
}
}
}
/// <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;
}
private void ImportDataIntoTempDataTable(string fileNameWPath)
{
string TargetConnectionString = string.Empty;
string TargetConnectionServerName = string.Empty;
string TargetConnectionDBName = string.Empty;
string TargetConnectionUser = string.Empty;
string TargetConnectionPassword = string.Empty;
string TargetConnectionPort = string.Empty;
DIServerType TargetConnectionServerType;
System.Data.Common.DbDataAdapter Adapter = null;
System.Data.Common.DbCommandBuilder CmdBuilder = null;
DataSet TargetFileDataset = null;
DataSet SourceDataSet = null;
DIServerType ServerType;
System.Data.Common.DbCommand cmd;
DIConnection Connection;
try
{
SourceDataSet = this.GetDataSetFrmXmlFile(fileNameWPath);
if (SourceDataSet != null)
{
TargetConnectionString = this.DBConnection.GetConnection().ConnectionString;
TargetConnectionServerName = this.DBConnection.ConnectionStringParameters.ServerName;
TargetConnectionServerType = this.DBConnection.ConnectionStringParameters.ServerType;
TargetConnectionDBName = this.DBConnection.ConnectionStringParameters.DbName;
TargetConnectionUser = this.DBConnection.ConnectionStringParameters.UserName;
TargetConnectionPassword = this.DBConnection.ConnectionStringParameters.Password;
TargetConnectionPort = this.DBConnection.ConnectionStringParameters.PortNo;
ServerType = this.DBConnection.ConnectionStringParameters.ServerType;
//dispose connection
this.DBConnection.Dispose();
try
{
// Connection = new DIConnection(TargetConnectionString, ServerType);
Connection = new DIConnection(TargetConnectionServerType, TargetConnectionServerName, TargetConnectionPort, TargetConnectionDBName, TargetConnectionUser, TargetConnectionPassword);
Adapter = Connection.CreateDBDataAdapter();
cmd = Connection.GetCurrentDBProvider().CreateCommand();
cmd.CommandText = "Select * from " + DAImportCommon.Constants.TempDataTableName;
cmd.Connection = Connection.GetConnection();
Adapter.SelectCommand = cmd;
CmdBuilder = Connection.GetCurrentDBProvider().CreateCommandBuilder();
CmdBuilder.DataAdapter = Adapter;
TargetFileDataset = new System.Data.DataSet();
Adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
try
{
Adapter.Fill(TargetFileDataset, DAImportCommon.Constants.TempDataTableName);
}
catch (Exception ex)
{
throw new ApplicationException("ImportDataIntoTempDataTable - Adapter.Fill - " + TargetConnectionServerType + " - " + TargetConnectionServerName + " - " + TargetConnectionPort + " - " + TargetConnectionDBName + " - " + TargetConnectionUser + " - " + TargetConnectionPassword + ex.ToString());
}
TargetFileDataset.Tables[DAImport.Common.Constants.TempDataTableName].Columns[Data.DataNId].AutoIncrement = true;
if (TargetFileDataset.Tables[DAImport.Common.Constants.TempDataTableName].Rows.Count > 0)
{
TargetFileDataset.Tables[DAImport.Common.Constants.TempDataTableName].DefaultView.Sort = TargetFileDataset.Tables[DAImport.Common.Constants.TempDataTableName].Columns[Data.DataNId].ColumnName + " DESC";
TargetFileDataset.Tables[DAImport.Common.Constants.TempDataTableName].Columns[Data.DataNId].AutoIncrementSeed = Convert.ToInt32(TargetFileDataset.Tables[DAImport.Common.Constants.TempDataTableName].DefaultView[0][Data.DataNId]) + 1;
}
TargetFileDataset.Tables[DAImport.Common.Constants.TempDataTableName].PrimaryKey = new DataColumn[] { TargetFileDataset.Tables[DAImport.Common.Constants.TempDataTableName].Columns[Data.DataNId] };
TargetFileDataset.Tables[DAImport.Common.Constants.TempDataTableName].AcceptChanges();
//Get tempdatatable
this.FillTempDataTable(SourceDataSet, TargetFileDataset.Tables[DAImport.Common.Constants.TempDataTableName]);
//update TempDataTable into target database
Adapter.Update(TargetFileDataset, DAImport.Common.Constants.TempDataTableName);
System.Threading.Thread.Sleep(1000);
Connection.Dispose();
}
catch (Exception ex)
{
ExceptionFacade.ThrowException(ex);
}
finally
{
//dispose all used objects
if (Adapter != null)
{
Adapter.Dispose();
}
if (CmdBuilder != null)
{
CmdBuilder.Dispose();
}
if (TargetFileDataset != null)
{
TargetFileDataset.Dispose();
}
//reconnect to target database
//this.ConnectToDatabase();
//this.DBConnection = new DIConnection(TargetConnectionString, ServerType);
this.DBConnection = new DIConnection(TargetConnectionServerType, TargetConnectionServerName, TargetConnectionPort, TargetConnectionDBName, TargetConnectionUser, TargetConnectionPassword);
}
}
}
catch (Exception ex)
{
ExceptionFacade.ThrowException(ex);
}
finally
{
if (SourceDataSet != null)
{
SourceDataSet.Dispose();
}
}
}