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