/// <summary>
/// Constructor set connection, transaction and generates model
/// data map from model type input
/// </summary>
/// <param name="conn">
/// Connection object
/// </param>
/// <param name="modelType">
/// GlobalTranz.Framework.Common.Models type
/// </param>
/// <param name="transaction">
/// SQL transaction object.
/// </param>
public SqlDataAccessor(IDbConnection conn, Type modelType, IDbTransaction transaction)
{
this.connection = conn;
this.modelType = modelType;
this.transaction = transaction;
this.modelDataMap = ModelDataMap.GetDataMap(modelType);
this.commandBuilder = SqlFrameworkCommandBuilder.GetSqlStringBuilder(this.modelDataMap, modelType);
}
/// <summary>
/// Create select command for multiple result set
/// </summary>
/// <param name="mapping">
/// Model data map for attribute mapping
/// </param>
/// <param name="fieldNames">
/// Name of the field names for where clause
/// </param>
/// <param name="fieldValues">
/// values of the field for where clause
/// </param>
/// <returns>
/// Command object with all parameters
/// </returns>
private IDbCommand CreateSelectCommandForMultipleResults(
ModelDataMap mapping,
string[] fieldNames,
object[] fieldValues)
{
SqlCommand cmd = new SqlCommand();
StringBuilder sb = new StringBuilder();
sb.Append("select ");
sb = this.GenerateColumnList(mapping, sb, true);
sb.Append(" from ");
sb.Append(mapping.ViewName);
sb.Append(" ");
if (fieldNames != null)
{
sb.Append(" where ");
for (int i = 0; i < fieldNames.Length; i++)
{
ModelColumnMap columnMap = (ModelColumnMap)mapping.Columns[fieldNames[i]];
sb.Append(columnMap.ColumnName);
sb.Append(" = @");
sb.Append(fieldNames[i]);
if (fieldNames.Length > 1 && i != fieldNames.Length - 1)
{
sb.Append(" AND ");
}
SqlParameter p = new SqlParameter();
if (!columnMap.IsDatabaseTypeNull)
{
p.DbType = columnMap.DatabaseType;
}
// p.SourceColumn = columnMap.FieldName;
if (columnMap.Field != null)
{
p.ParameterName = "@" + columnMap.FieldName;
}
else if (columnMap.Property != null)
{
p.ParameterName = "@" + columnMap.PropertyName;
}
object val = fieldValues[i];
p.Value = val;
cmd.Parameters.Add(p);
}
}
cmd.CommandText = sb.ToString();
return(cmd);
}
/// <summary>
/// Create select sql statement
/// </summary>
/// <param name="modelDataMap">
/// Model data map object
/// </param>
/// <param name="modelType">
/// Type of model
/// </param>
/// <returns>
/// Command builder object with sql query.
/// </returns>
public static SqlFrameworkCommandBuilder GetSqlStringBuilder(
ModelDataMap modelDataMap,
Type modelType)
{
SqlFrameworkCommandBuilder b = (SqlFrameworkCommandBuilder)sqlMap[modelType];
if (b == null)
{
b = new SqlFrameworkCommandBuilder(modelDataMap, modelType);
sqlMap[modelType] = b;
}
return(b);
}
/// <summary>
/// Generate column list for select query
/// </summary>
/// <param name="mapping">
/// Model column mapping details
/// </param>
/// <param name="sb">
/// String builder object for the query
/// </param>
/// <param name="selectStatement">
/// Select statement
/// </param>
/// <returns>
/// string builder object with column list
/// </returns>
private StringBuilder GenerateColumnList(ModelDataMap mapping, StringBuilder sb, bool selectStatement)
{
bool changed = false;
foreach (ModelColumnMap col in mapping.Columns.Values)
{
if (selectStatement || (!col.IsViewOnly))
{
// add the column if this is a select statement or if the column
// is not a read only column.
sb.Append(col.ColumnName);
sb.Append(", ");
changed = true;
}
}
if (changed)
{
// remove the comma and space from the end of the string
sb.Remove(sb.Length - 2, 2);
}
return(sb);
}
/// <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>
/// Constructor of DataReaderConverter class. It accepts object of ModelDataMap class.
/// </summary>
/// <param name="mapping">
/// Object of ModelDataMap class.
/// </param>
public DataReaderConverter(ModelDataMap mapping)
{
this.mapping = mapping;
}
/// <summary>
/// Constructor of DataReaderConverter class. It accepts model type.
/// </summary>
/// <param name="modelType">
/// The type of model to be associated with the new instance of DataReaderConverter
/// </param>
public DataReaderConverter(Type modelType)
{
this.mapping = ModelDataMap.GetDataMap(modelType);
}
/// <summary>
/// Constructor, create new instance of SqlFrameworkCommandBuilder
/// </summary>
/// <param name="modelDataMap">
/// Model parameter map
/// </param>
/// <param name="modelType">
/// Type of imodel object
/// </param>
private SqlFrameworkCommandBuilder(ModelDataMap modelDataMap, Type modelType)
{
this.map = modelDataMap;
this.type = modelType;
}
