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