/// <summary>
/// Retrieve a collection of entities based on the input criterias using stored procedure
/// </summary>
/// <typeparam name="T">
/// Any type which implements Framework.Common.Models.IModel interface.
/// </typeparam>
/// <param name="model">
/// A model object which contains filter conditions.
/// </param>
/// <returns>
/// A collection of entities.
/// </returns>
public List <T> GetModelsProcedure <T>(T model) where T : IModel, new()
{
IFrameworkDataAccessFactory factory = DbInstance.CurrentInstance;
// retrieves connection string of specified server type
string connectionString = ServerManager.Instance.GetConnectionString(model.DatabaseServerType);
IDbConnection connection = factory.CreateConnection(connectionString);
List <T> dataModels = null;
try
{
connection.Open();
// create common data accessor for doing database operations.
IDataAccessor dataAccessor = factory.CreateAccessor(connection, typeof(T));
dataModels = dataAccessor.SelectMultipleFromProcedure <T>(model);
}
finally
{
// Close the Connection if Opened
if (connection.State == ConnectionState.Open)
{
connection.Close();
}
}
return(dataModels);
}
/// <summary>
/// Delete the entry from persistent storage using stored procedure.
/// It is a generic method, it can be used to delete any of the
/// objects which implements Framework.Common.Models.IModel interface.
/// </summary>
/// <typeparam name="T">
/// Any type which implements Framework.Common.Models.IModel interface.
/// </typeparam>
/// <param name="primaryKey">
/// Primary key of the entity which is used to identify the entity.
/// </param>
/// <returns>
/// A boolean value indicating delete operation is succesful or not.
/// </returns>
public bool DeleteProcedure <T>(int primaryKey) where T : IModel, new()
{
IFrameworkDataAccessFactory factory = DbInstance.CurrentInstance;
// retrieves connection string of specified server type
string connectionString = ServerManager.Instance.GetConnectionString(new T().DatabaseServerType);
IDbConnection connection = factory.CreateConnection(connectionString);
bool isSuccess = false;
try
{
connection.Open();
// create common data accessor for doing database operations.
IDataAccessor dataAccessor = factory.CreateAccessor(connection, typeof(T));
isSuccess = dataAccessor.DeleteProcedure(primaryKey);
}
finally
{
// Close the Connection if Opened
if (connection.State == ConnectionState.Open)
{
connection.Close();
}
}
return(isSuccess);
}
/// <summary>
/// Retrieve an entity from the persistant storage using the primary key.
/// </summary>
/// <typeparam name="T">
/// Any type which implements Framework.Common.Models.IModel interface.
/// </typeparam>
/// <param name="model">
/// A model object which contains filter conditions
/// </param>
/// <returns>
/// A Framework.Common.Models object which contains requested entity.
/// </returns>
public T GetModelProcedure <T>(T model) where T : IModel, new()
{
IFrameworkDataAccessFactory factory = DbInstance.CurrentInstance;
// retrieves connection string of specified server type
string connectionString = ServerManager.Instance.GetConnectionString(model.DatabaseServerType);
IDbConnection connection = factory.CreateConnection(connectionString);
try
{
connection.Open();
// create common data accessor for doing database operations.
IDataAccessor dataAccessor = factory.CreateAccessor(connection, typeof(T));
// call select method for retrieving requested entity
// from database and fill to the dto model.
model = (T)dataAccessor.SelectProcedure(model);
}
finally
{
// Close the Connection if Opened
if (connection.State == ConnectionState.Open)
{
connection.Close();
}
}
return(model);
}
public DataSet GetDataSet(string storedProcName, List <DbParameter> inputParameters, ServerTypes serverType)
{
// checks for stored procedure name
// if not specified then throw exception
if (string.IsNullOrEmpty(storedProcName))
{
throw new MissingMemberException("Stored procedure name not specified.");
}
// create instance of data access factory
IFrameworkDataAccessFactory factory = DbInstance.CurrentInstance;
// create connection object for database access.
string connectionString = ServerManager.Instance.GetConnectionString(serverType);
IDbConnection connection = factory.CreateConnection(connectionString);
IDbTransaction transaction = null;
DataSet result = new DataSet();
try
{
connection.Open();
transaction = connection.BeginTransaction();
// create custom accessor instance
ICustomDataAccessor customAccessor = factory.CreateCustomAccessor <ICustomDataAccessor>(transaction);
result = customAccessor.GetDataSet(storedProcName, inputParameters);
transaction.Commit();
transaction = null;
}
catch (SqlException sx)
{
if (sx.Number == DatabaseErrorConstants.DATA_MISMATCH_ERROR)
{
// Throw custom exception
throw new DataMismatchException(sx.Message, sx);
}
else
{
throw sx;
}
}
finally
{
// if transaction exits then rollback transaction
if (transaction != null)
{
transaction.Rollback();
}
// close connection if it is open
if (connection.State == ConnectionState.Open)
{
connection.Close();
}
}
return(result);
}
/// <summary>
/// Save the data in a persistant storage.
/// If the given object is new one then create a new record else update existing record.
/// It is a generaic method, it can be used to save any of the objects which implements
/// Framework.Common.Models.IModel interface.
/// </summary>
/// <typeparam name="T">
/// Any type which implements Framework.Common.Models.IModel interface.
/// </typeparam>
/// <param name="dtoModel">
/// A Framework.Common.Models object that contins data to be saved.
/// </param>
/// <returns>
/// A boolean value which indicating save operation is succesful or not.
/// </returns>
public bool Save <T>(T dtoModel) where T : IModel
{
IFrameworkDataAccessFactory factory = DbInstance.CurrentInstance;
// retrieves connection string of specified server type
string connectionString = ServerManager.Instance.GetConnectionString(dtoModel.DatabaseServerType);
IDbConnection connection = factory.CreateConnection(connectionString);
bool isSuccess = false;
try
{
connection.Open();
// create common data accessor for doing database operations.
IDataAccessor dataAccessor = factory.CreateAccessor(connection, typeof(T));
// If the given object is new one then create a new record in database
// else update existing record.
if (dtoModel.IsNew)
{
// Create a new record in database, Insert method of data accessor class
// will generate sql statements on the fly from custom attributes of dto model.
dtoModel = (T)dataAccessor.Insert(dtoModel);
if (!dtoModel.IsNew)
{
isSuccess = true;
}
}
else
{
// Update existing record in database, Update method of data accessor class
// will generate sql statements on the fly from custom attributes of dto model.
dtoModel = (T)dataAccessor.Update(dtoModel);
isSuccess = true;
}
}
finally
{
// Close the Connection if Opened
if (connection.State == ConnectionState.Open)
{
connection.Close();
}
}
return(isSuccess);
}
/// <summary>
/// Generic data retrieval method for datasets, It fill result to typed dataset
/// </summary>
/// <typeparam name="T">
/// Typed dataset that should implement IDataSet interface.
/// </typeparam>
/// <param name="typedDataSet">
/// Typed dataset instance, stored procedure name property
/// of this instance should have valid data.
/// </param>
/// <returns>
/// A typed dataset instance, filled with query execution result.
/// </returns>
public T GetDataSet <T>(T typedDataSet) where T : DataSet, IDataSet
{
// checks for stored procedure name in given dataset,
// if not specified then throw exception
if (string.IsNullOrEmpty(typedDataSet.StoredProcedureName))
{
throw new MissingMemberException("Stored procedure name not specified in given dataset instance");
}
// create instance of data access factory
IFrameworkDataAccessFactory factory = DbInstance.CurrentInstance;
// create connection object for database access.
string connectionString = ServerManager.Instance.GetConnectionString(typedDataSet.DatabaseServerType);
IDbConnection connection = factory.CreateConnection(connectionString);
try
{
connection.Open();
// create custom accessor instance
ICustomDataAccessor customAccessor = factory.CreateCustomAccessor <ICustomDataAccessor>(connection);
// call generic get method.
typedDataSet = customAccessor.GetDataSet <T>(typedDataSet);
}
finally
{
// close connection if it is open
if (connection.State == ConnectionState.Open)
{
connection.Close();
}
}
return(typedDataSet);
}
/// <summary>
/// Bulk insert collection of record to the database using sql server table valued parameter.
/// </summary>
/// <typeparam name="T">
/// Any type which implements Framework.Common.Models.IModel interface.
/// </typeparam>
/// <param name="modelCollection">
/// Implements imodel for bulk insert.
/// </param>
/// <param name="dtProduct">
/// A collection objects which implements imodel for bulk insert.
/// </param>
/// <returns></returns>
public bool InsertTableProcedure <T>(List <T> modelCollection, DataTable dtProduct, T modeldt) where T : IModel, new()
{
IFrameworkDataAccessFactory factory = DbInstance.CurrentInstance;
// retrieves connection string of specified server type
string connectionString = ServerManager.Instance.GetConnectionString(modeldt.DatabaseServerType);
IDbConnection connection = factory.CreateConnection(connectionString);
IDbTransaction transaction = null;
bool isSuccess = false;
// get your connection string
// connect to SQL
using (SqlConnection sqlconnection =
new SqlConnection(connectionString))
{
try
{
connection.Open();
// create common data accessor for doing database operations.
IDataAccessor dataAccessor = factory.CreateAccessor(connection, typeof(T), transaction);
//transaction = connection.BeginTransaction();
/// Hash table for model mapping
ModelDataMap modelDataMap = dataAccessor.InsertTableValuedProcedure <T>(modelCollection);
// make sure to enable triggers
// more on triggers in next post
SqlBulkCopy bulkCopy =
new SqlBulkCopy
(
sqlconnection,
SqlBulkCopyOptions.TableLock |
SqlBulkCopyOptions.FireTriggers |
SqlBulkCopyOptions.UseInternalTransaction,
null
);
// set the destination table name
bulkCopy.DestinationTableName = modelDataMap.TableName;
sqlconnection.Open();
// write the data in the "dataTable"
bulkCopy.WriteToServer(dtProduct);
connection.Close();
isSuccess = true;
//if (isSuccess)
//{
// transaction.Commit();
//}
//else
//{
// transaction.Rollback();
//}
//transaction = null;
}
finally
{
// if transaction exits then rollback transaction
//if (transaction != null)
//{
// transaction.Rollback();
//}
// Close the Connection if Opened
if (connection.State == ConnectionState.Open)
{
connection.Close();
}
}
}
return(isSuccess);
}
/// <summary>
/// Save the data in a persistant storage using stored procedure.
/// If the given object is new one then create a new record else update existing record.
/// It is a generic method, it can be used to save any of the objects which implements
/// Framework.Common.Models.IModel interface.
/// </summary>
/// <typeparam name="T">
/// Any type which implements Framework.Common.Models.IModel interface.
/// </typeparam>
/// <param name="dtoModel">
/// A Framework.Common.Models object that contins data to be saved.
/// </param>
/// <returns>
/// A boolean value which indicating save operation is succesful or not.
/// </returns>
public bool SaveProcedure <T>(T dtoModel) where T : IModel
{
IFrameworkDataAccessFactory factory = DbInstance.CurrentInstance;
// retrieves connection string of specified server type
string connectionString = ServerManager.Instance.GetConnectionString(dtoModel.DatabaseServerType);
IDbConnection connection = factory.CreateConnection(connectionString);
bool isSuccess = false;
try
{
connection.Open();
// create common data accessor for doing database operations.
IDataAccessor dataAccessor = factory.CreateAccessor(connection, typeof(T));
// If the given object is new one then create a new record in database
// else update existing record.
if (dtoModel.IsNew)
{
// Create a new record in database, Insert method of data accessor class
// will generate sql command object and parameters on the fly from custom attributes of dto model.
dtoModel = (T)dataAccessor.InsertProcedure(dtoModel);
if (dtoModel != null && (!dtoModel.IsNew))
{
isSuccess = true;
}
}
else
{
// Update existing record in database, Update method of data accessor class
// will generate sql command object and parameters on the fly from custom attributes of dto model.
dtoModel = (T)dataAccessor.UpdateProcedure(dtoModel);
isSuccess = true;
}
}
catch (SqlException sx)
{
if (sx.Number == DatabaseErrorConstants.TIMESTAMP_MISMATCH_ERROR)
{
// Throw custom exception
throw new TimeStampMismatchException(sx.Message, sx);
}
else if (sx.Number == DatabaseErrorConstants.HAZMAT_PACKET_NOT_RECEIVED_ERROR)
{
// Throw custom exception
throw new CarrierPacketNotAvailableException(sx.Message, sx);
}
else
{
throw sx;
}
}
finally
{
// Close the Connection if Opened
if (connection.State == ConnectionState.Open)
{
connection.Close();
}
}
return(isSuccess);
}
/// <summary>
/// Save data in the data set in persistent storage, it use table valued
/// data type functionality SQL Server 2008.
/// </summary>
/// <typeparam name="T">
/// Typed dataset that should implement IDataSet interface.
/// </typeparam>
/// <param name="typedDataSet">
/// Typed dataset instance, stored procedure name property
/// of this instance should have valid data.
/// </param>
/// <returns>
/// Returns number of rows affected
/// </returns>
public T InsertSelectSQLTableType <T>(T typedDataSet, T outputTypedDataSet) where T : DataSet, IDataSet, ISQLTableType
{
// checks for stored procedure name in given dataset,
// if not specified then throw exception
if (string.IsNullOrEmpty(typedDataSet.StoredProcedureName))
{
throw new MissingMemberException("Stored procedure name not specified in given dataset instance");
}
// create instance of data access factory
IFrameworkDataAccessFactory factory = DbInstance.CurrentInstance;
// create connection object for database access.
string connectionString = ServerManager.Instance.GetConnectionString(typedDataSet.DatabaseServerType);
IDbConnection connection = factory.CreateConnection(connectionString);
IDbTransaction transaction = null;
try
{
connection.Open();
transaction = connection.BeginTransaction();
// create custom accessor instance
ICustomDataAccessor customAccessor = factory.CreateCustomAccessor <ICustomDataAccessor>(transaction);
// call generic get method.
outputTypedDataSet = customAccessor.InsertSelectSQLTableType <T>(typedDataSet, outputTypedDataSet);
transaction.Commit();
transaction = null;
}
catch (SqlException sx)
{
if (sx.Number == DatabaseErrorConstants.TIMESTAMP_MISMATCH_ERROR)
{
// Throw custom exception
throw new TimeStampMismatchException(sx.Message, sx);
}
else if (sx.Number == DatabaseErrorConstants.HAZMAT_PACKET_NOT_RECEIVED_ERROR)
{
// Throw custom exception
throw new CarrierPacketNotAvailableException(sx.Message, sx);
}
else if (sx.Number == DatabaseErrorConstants.CARRIER_CODE_ALREADY_EXIST_ERROR)
{
// Throw custom exception
throw new CarrierCodeAlreadyExistException(sx.Message, sx);
}
else if (sx.Number == DatabaseErrorConstants.CARRIER_INSURANCE_EXPIRED_ERROR)
{
// Throw custom exception
throw new CarrierInsuranceExpiredException(sx.Message, sx);
}
else
{
throw sx;
}
}
finally
{
// if transaction exits then rollback transaction
if (transaction != null)
{
transaction.Rollback();
}
// close connection if it is open
if (connection.State == ConnectionState.Open)
{
connection.Close();
}
}
return(outputTypedDataSet);
}
