// with parameters
public DataSet GetDataSet(string sql, SQL_PARAMETER[] parameters)
{
using (DbCommand command = DBProviderFactory.CreateCommand())
{
command.Connection = this.DBConnection;
command.CommandType = CommandType.StoredProcedure;
command.CommandText = sql;
command.CommandTimeout = 3000;
if (parameters != null)
{
foreach (SQL_PARAMETER parameter in parameters)
{
DbParameter dbParameter = DBProviderFactory.CreateParameter();
dbParameter.DbType = parameter.Type;
dbParameter.ParameterName = parameter.Name;
dbParameter.Value = parameter.Value;
command.Parameters.Add(dbParameter);
}
}
using (DbDataAdapter adapter = DBProviderFactory.CreateDataAdapter())
{
adapter.SelectCommand = command;
DataSet ds = new DataSet();
adapter.Fill(ds);
return(ds);
}
}
}
/// <summary>
/// Consume Source
/// </summary>
/// <returns></returns>
internal override SourceAdapterResponse ConsumeSource()
{
DBSourceAdapterResponse response = null;
try
{
if (base.ValidateAdapter(IntegrationChannelType.Source))
{
using (AbstractDBProvider sourceDBProvider =
DBProviderFactory.CreateDBProvider(
this.AdapterMetadata,
this.AdapterMetadata.ApplicationDatabaseQuery.ApplicationDatabas,
base.AppRuntime))
{
response = new DBSourceAdapterResponse(this.AdapterMetadata, this.AdapterMetadata.ApplicationDatabaseQuery);
response.Results = sourceDBProvider.RunSelectQuery(this.AdapterMetadata.ApplicationDatabaseQuery);
}
}
}
catch (Exception ex)
{
LogManager.LogException(ex);
if (response != null)
{
response.Status = OperationStatus.Failed;
}
}
return(response);
}
public object GetScalar(string sql, SQL_PARAMETER[] parameters)
{
using (DbCommand command = DBProviderFactory.CreateCommand())
{
command.Connection = this.DBConnection;
command.CommandText = sql;
command.CommandType = CommandType.StoredProcedure;
if (parameters != null)
{
foreach (SQL_PARAMETER parameter in parameters)
{
DbParameter dbParameter = DBProviderFactory.CreateParameter();
dbParameter.DbType = parameter.Type;
dbParameter.ParameterName = parameter.Name;
dbParameter.Value = parameter.Value;
command.Parameters.Add(dbParameter);
}
}
if (this.DBTransaction != null)
{
command.Transaction = this.DBTransaction;
}
return(command.ExecuteScalar());
}
}
/// <summary>
/// Executes Insert statements in the database. Optionally returns new identifier.
/// </summary>
/// <param name="sql">Sql statement.</param>
/// <param name="parameters">Sql parameter.</param>
/// <param name="getId">Value indicating whether newly generated identity is returned.</param>
/// <returns>Newly generated identity value (auto number value).</returns>
public int Insert(string sql, SQL_PARAMETER[] parameters, bool getId)
{
using (DbCommand command = DBProviderFactory.CreateCommand())
{
command.Connection = this.DBConnection;
if (this.DBTransaction != null)
{
command.Transaction = this.DBTransaction;
}
command.CommandText = sql;
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Clear();
foreach (SQL_PARAMETER parameter in parameters)
{
DbParameter dbParameter = DBProviderFactory.CreateParameter();
dbParameter.DbType = parameter.Type;
dbParameter.ParameterName = parameter.Name;
dbParameter.Value = parameter.Value;
command.Parameters.Add(dbParameter);
}
command.ExecuteNonQuery();
int id = -1;
// Check if new identity is needed.
if (getId)
{
// Execute RpmServerDb specific auto number or identity retrieval code
// SELECT SCOPE_IDENTITY() -- for SQL Server
// SELECT @@IDENTITY -- for MS Access/Odbc
string identitySelect;
//switch (Consts.DataProvider)
//{
// Odbc
//case "System.Data.Odbc":
identitySelect = "SELECT @@IDENTITY";
//break;
// Sql Server
//case "System.Data.SqlClient":
// identitySelect = "SELECT SCOPE_IDENTITY()";
// break;
//default:
// identitySelect = "SELECT @@IDENTITY";
// break;
// }
command.CommandText = identitySelect;
//id = int.Parse(command.ExecuteScalar().ToString());
}
return(id);
}
}
public int Update(string sql)
{
using (DbCommand command = DBProviderFactory.CreateCommand())
{
command.Connection = this.DBConnection;
command.CommandText = sql;
if (this.DBTransaction != null)
{
command.Transaction = this.DBTransaction;
}
int result = command.ExecuteNonQuery();
return(result);
}
}
//DBMethods With Transaction
#region DBMethods With Transaction
public int Insert(string sql, bool getId)
{
using (DbCommand cmd = DBProviderFactory.CreateCommand())
{
cmd.Connection = this.DBConnection;
cmd.CommandText = sql;
if (this.DBTransaction != null)
{
cmd.Transaction = this.DBTransaction;
}
cmd.ExecuteNonQuery();
int id = -1;
// Check if new identity is needed.
if (getId)
{
// Execute RpmServerDb specific auto number or identity retrieval code
// SELECT SCOPE_IDENTITY() -- for SQL Server
// SELECT @@IDENTITY -- for MS Access/Odbc
string identitySelect;
//switch (Consts.DataProvider)
//{
// // Odbc
// case "System.Data.Odbc":
// identitySelect = "SELECT @@IDENTITY";
// break;
// // Sql Server
// case "System.Data.SqlClient":
identitySelect = "SELECT SCOPE_IDENTITY()";
// break;
// default:
// identitySelect = "SELECT @@IDENTITY";
// break;
//}
cmd.CommandText = identitySelect;
id = int.Parse(cmd.ExecuteScalar().ToString());
}
return(id);
}
}
public int Update(string sql, SQL_PARAMETER[] parameters)
{
using (DbCommand command = DBProviderFactory.CreateCommand())
{
command.Connection = this.DBConnection;
if (this.DBTransaction != null)
{
command.Transaction = this.DBTransaction;
}
command.CommandText = sql;
command.Parameters.Clear();
foreach (SQL_PARAMETER parameter in parameters)
{
DbParameter dbParameter = DBProviderFactory.CreateParameter();
dbParameter.DbType = parameter.Type;
dbParameter.ParameterName = parameter.Name;
dbParameter.Value = parameter.Value;
command.Parameters.Add(dbParameter);
}
int result = command.ExecuteNonQuery();
return(result);
}
}
/// <summary>
/// Publish To Destination
/// </summary>
/// <param name="pPushToDestinationRequest"></param>
/// <returns>Destination Adapter Response</returns>
internal override DestinationAdapterResponse PublishToDestination(PushToDestinationRequest pPushToDestinationRequest)
{
DBDestinationAdapterResponse response = null;
try
{
if (base.ValidateAdapter(IntegrationChannelType.Destination) && pPushToDestinationRequest != null)
{
using (AbstractDBProvider destinationDBProvider = DBProviderFactory.CreateDBProvider(
this.AdapterMetadata,
this.AdapterMetadata.ApplicationDatabaseQuery.ApplicationDatabas,
base.AppRuntime))
{
response = new DBDestinationAdapterResponse(this.AdapterMetadata, this.AdapterMetadata.ApplicationDatabaseQuery);
foreach (AdapterCacheResult cacheResult in pPushToDestinationRequest.AdapterCacheResults)
{
try
{
cacheResult.DBRecord.RecordTransactionStatus = destinationDBProvider.RunInsertQuery(
this.AdapterMetadata.ApplicationDatabaseQuery,
this.AdapterMetadata.IntegrationAdapterFields,
cacheResult.DBRecord);
if (cacheResult.DBRecord.RecordTransactionStatus == RecordTransactionStatus.Succeeded ||
cacheResult.DBRecord.RecordTransactionStatus == RecordTransactionStatus.Duplicated)
{
cacheResult.DBRecordCache.CacheStatus = RecordCacheStatus.Succeeded.GetValue <Byte>();
}
else
{
if (cacheResult.DBRecord.RecordTransactionStatus == RecordTransactionStatus.Failed ||
cacheResult.DBRecord.RecordTransactionStatus == RecordTransactionStatus.None)
{
cacheResult.DBRecordCache.CacheStatus = RecordCacheStatus.Failed.GetValue <Byte>();
}
}
base.AppRuntime.DataService.ApplyEntityChanges(cacheResult.DBRecordCache);
response.Results.Add(cacheResult.DBRecord);
}
catch (Exception ex)
{
string extraMessage = string.Format("Record Keys: {0} - Parameter: {1}",
cacheResult.DBRecord != null ? cacheResult.DBRecord.DbRecordKey : "NO KEYS",
cacheResult.DBRecord != null ? cacheResult.DBRecord.ExceptionExtraMessage : string.Empty);
LogManager.LogException(ex, extraMessage);
}
}
response.Status = response.Results.Where(c =>
c.RecordTransactionStatus == RecordTransactionStatus.None ||
c.RecordTransactionStatus == RecordTransactionStatus.Failed).Count() > 0 ?
OperationStatus.Failed : OperationStatus.Succeeded;
//Save the updated query cache
if (pPushToDestinationRequest.AdapterCacheResults.Count > 0)
{
base.AppRuntime.DataService.SaveChanges();
}
}
}
}
catch (Exception ex)
{
LogManager.LogException(ex);
if (response != null)
{
response.Status = OperationStatus.Failed;
}
}
finally
{
}
return(response);
}
