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