/// <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(); } } }