Click on create , then will create application.
using SMS.DAL.Common;
using ObjectExtensions;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;
using Microsoft.Extensions.Configuration;
using SMS.DAL.AppConfig;
namespace SMS.DAL
{
public class EntityRowCount
{
public int Count { get; set; }
}
// Author: jms
/// <summary>
/// Implements the data access layer onject. This class uses generic types, and therefore
/// assumes/requires that the following components match EXACTLY (case and spelling):
/// - Entity property names (in your app)
/// - Stored proc parameter names (in the database)
/// - Table column names (in the database)
/// It is the job of the programmer to use transactions (ostensibly for setting data) in his
/// stored procs if that behavior is desired. For text queries (which AEF code should not use),
/// include all transaction related code (begin/end try, begin/end catch in your query.
/// </summary>
public partial class DALRepository
{
#region properties
private IConfiguration _config;
/// <summary>
/// Get/set flag indicating whether the List<T> ExecuteXXX<T>() methods should <br/>
/// throw an exception if the DataTable retrieved by the query does not match the model <br/>
/// being created (it compares the number of datatable columns with the number of assigned <br/>
/// values in the model). The default falue is false.
/// </summary>
public bool FailOnMismatch { get; set; }
/// <summary>
/// Get/set value indicating the SqlCommand object's timeout value (in seconds)
/// </summary>
public int TimeoutSecs { get; set; }
/// <summary>
/// Get/(protected) set the connection string.
/// </summary>
public string ConnectionString { get; protected set; }
/// <summary>
/// Get/set a flag indicating whether a return value parameter is added to the sql <br/>
/// parameter list if it's missing. This only applies to the SetData method <br/>
/// (insert/update/delete functionality). In order for this to work, you MUST return <br/>
/// @@ROWCOUNT from your stored proc.
/// </summary>
public bool AddReturnParamIfMissing { get; set; }
/// <summary>
/// Get/set the bulk insert batch size
/// </summary>
public int BulkInsertBatchSize { get; set; }
/// <summary>
/// Get/set the number of seconds before the bulk copy times out
/// </summary>
protected int BulkCopyTimeout { get; set; }
/// <summary>
/// Get/set options flag for SqlBulkCopy operations
/// </summary>
protected SqlBulkCopyOptions BulkCopyOptions { get; set; }
/// <summary>
/// Get/set the external transaction that can be set for/used by SqlBlukCopy.
/// </summary>
protected SqlTransaction ExternalTransaction { get; set; }
/// <summary>
/// Get/set a flag indicating whether the database can be accessed from <br/>
/// the GetData or SetData methods. Allows debugging of BLL without <br/>
/// actually reading from or writing to the database. Sefault value <br/>
/// is true.
/// </summary>
public bool CanReadWriteDB { get; set; }
#endregion properties
#region constructors
/// <summary>
/// Create instance of DALRepository, and set default values for properties.
/// </summary>
public DALRepository()
{
// this connection string *should* be base64 encoded
//this.ConnectionString = System.Configuration.ConfigurationSettings.AppSettings["DBConnectionString"];
//string connString = this._config.GetConnectionString("DBConnectionString");
AppConfiguration objAppConfiguration = new AppConfiguration();
this.ConnectionString = objAppConfiguration.ConnectionString;
this.Init();
}
#endregion constructors
#region protected data access methods
/// <summary>
/// Executes the named stored proc (using ExecuteReader) that gets data from the database. <br/>
/// </summary>
/// <typeparam name="T">The type of the list item</typeparam>
/// <param name="storedProc">The name of the stored procedure to execute</param>
/// <param name="parameters">The parameters to pass to the stored procedure</param>
/// <returns>A list of the specified object type (may be empty).</returns>
/// <remarks>Useage: List<MyObject> list = this.ExecuteStoredProc<MyObject>(...)</remarks>
/// <exception cref="ArgumentNullException">The storedProc parameter cannot be null/empty.</exception>
public virtual List<T> ExecuteStoredProc<T>(string storedProc, SqlParameter[] parameters)
{
if (string.IsNullOrEmpty(storedProc))
{
throw new ArgumentNullException("storedProc");
}
// get the data from the database
DataTable data = this.GetData(storedProc, parameters, CommandType.StoredProcedure);
List<T> collection = this.MakeEntityFromDataTable<T>(data);
return collection;
}
/// <summary>
/// Executes the specified stored proc (using ExecuteNonQuery) that stores data (specified <br/>
/// in the [parameters] parameter) in the database.
/// </summary>
/// <param name="storedProc">The stored proc to execute</param>
/// <param name="parameters">The parameters to pass to the stored procedure</param>
/// <returns>The number of records affected</returns>
/// <exception cref="ArgumentNullException">The [storedProc] parameter cannot be null/empty.</exception>
/// <exception cref="ArgumentNullException">The [parameters] array parameter cannot be null.</exception>
/// <exception cref="ArgumentNullException">The [parameters] array parameter cannot be empty.</exception>
public virtual int ExecuteStoredProc(string storedProc, SqlParameter[] parameters)
{
if (string.IsNullOrEmpty(storedProc))
{
throw new ArgumentNullException("storedProc");
}
if (parameters == null)
{
throw new ArgumentNullException("parameters");
}
if (parameters.Length == 0)
{
throw new InvalidOperationException("The [parameters] array must contain at least one item.");
}
// You must SET NOCOUNT OFF at the top of your stored proc in order to automatically
// return the @@ROWCOUNT value (created and set by sql server).
int result = this.SetData(storedProc, parameters, CommandType.StoredProcedure);
return result;
}
/// <summary>
/// Execute the specified stored proc to save the specified data item in the database.
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="data">The data item to save</param>
/// <param name="storedProc">The stored proc to execute</param>
/// <param name="bulkType">How to build the SqlParameter array</param>
/// <param name="precedence">Whether to ignore the discovered SqlParameter[] property</param>
/// <param name="paramArrayPropName">Name of desired SqlParameter[] property</param>
/// <returns>The number of records affected</returns>
public virtual int ExecuteStoredProc<T>(T data,
string storedProc,
BulkInsertType bulkType,
ParamPrecedence precedence = ParamPrecedence.None,
string paramArrayPropName = "SqlParameters")
{
int result = 0;
SqlParameter[] parameters = DALRepository.MakeSqlParameters(data, bulkType, precedence, paramArrayPropName);
result = this.ExecuteStoredProc(storedProc, parameters);
return result;
}
/// <summary>
/// Executes the specified stored proc for an entity collection, using a persistent <br/>
/// sql connection. This is intended to be used for data that needs to be merged <br/>
/// (update or insert) into a table rather than simply inserted.
/// </summary>
/// <typeparam name="T">The type of entity represented by the specified collection</typeparam>
/// <param name="data">The collection of entities</param>
/// <param name="storedProc">The name of the stored proc (must be a stored proc)</param>
/// <param name="bulkType">Indicates how properties should be retrieved from the entity items</param>
/// <param name="precedence">Whether to ignore the discovered SqlParameter[] property</param>
/// <param name="paramArrayPropName">Name of desired SqlParameter[] property</param>
/// <exception cref="ArgumentNullException">If the data parameter is null</exception>
/// <exception cref="ArgumentNullException">If the storedProc parameter is null/empty</exception>
/// <exception cref="InvalidOperationException">If the data collection is empty.</exception>
/// <returns>The number of records affected (inserted + updated).</returns>
/// <remarks>Usage: int result = ExecuteStoredProc(data.AsEnumerable(), "dbo.MyStoredProc", BulkInsertType.DBInsertAttribute);</remarks>
protected virtual int ExecuteStoredProc<T>(IEnumerable<T> data,
string storedProc,
BulkInsertType bulkType,
ParamPrecedence precedence = ParamPrecedence.None,
string paramArrayPropName = "SqlParameters")
{
if (string.IsNullOrEmpty(storedProc))
{
throw new ArgumentNullException("storedProc");
}
if (data == null)
{
throw new ArgumentNullException("data");
}
if (data.Count() == 0)
{
throw new InvalidOperationException("Data collection must contain at least onme item");
}
int result = this.DoBulkMerge(data, storedProc, bulkType, CommandType.StoredProcedure, precedence, paramArrayPropName);
return result;
}
/// <summary>
/// Executes the specified query (using ExecuteReader) that gets data from the database.
/// </summary>
/// <typeparam name="T">The type of the list item</typeparam>
/// <param name="query">The query text to execute</param>
/// <param name="parameters">The data to pass to the query text</param>
/// <returns>A list of the specified type.</returns>
/// <remarks>Useage: List<MyObject> list = this.ExecuteStoredProc<MyObject>(...)</remarks>
/// <exception cref="ArgumentNullException">If the query parameter is null/empty</exception>
protected virtual List<T> ExecuteQuery<T>(string query, params SqlParameter[] parameters)
{
// If you have questions regarding the use of parameters in query text, google
// "c# parameterized queries". In short, parameterized queries prevent sql
// injection. This code does not (and cannot) validate the use of parameters
// because some queries simply don't need them. Therefore, it's completely
// on you - the developer - to make sure you're doing it right.
if (string.IsNullOrEmpty(query))
{
throw new ArgumentNullException("query");
}
DataTable data = this.GetData(query, parameters, CommandType.Text);
List<T> collection = this.MakeEntityFromDataTable<T>(data);
return collection;
}
/// <summary>
/// Executes the specified query text (using ExecuteNonQuery) that stores data in the <br/>
/// database.
/// </summary>
/// <param name="query"></param>
/// <param name="parameters"></param>
/// <returns>The number of records affected (if you didn't use SET NOCOUNT ON in
/// your batch)</returns>
/// <exception cref="ArgumentNullException">If the query parameter is null/empty</exception>
protected virtual int ExecuteQuery(string query, params SqlParameter[] parameters)
{
// If you have questions regarding the use of parameters in query text, google
// "c# parameterized queries". In short, parameterized queries prevent sql
// injection. This code does not (and cannot) validate the use of parameters
// because some queries simply don't need them. Therefore, it's completely
// on you - the developer - to make sure you're doing it right.
if (string.IsNullOrEmpty(query))
{
throw new ArgumentNullException("query");
}
// Save the data to the database. If you use SET NOCOUNT ON in your query, the return
// value will always be -1, regardless of how many rows are actually affected.
int result = this.SetData(query, parameters, CommandType.Text);
return result;
}
/// <summary>
/// Execute the specified sql query to save the specified data item. This method creates the <br/>
/// parameters for you using the properties in the specified entity. While not required, you <br/>
/// can "grease the skids" by implementing a public property in your entities that returns a <br/>
/// SqlParameter[] array.
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="data">The data item to save</param>
/// <param name="query">The sql query text to execute</param>
/// <param name="bulkType">Indicates how to build the SqlParameter array</param>
/// <param name="precedence">Whether to ignore the discovered SqlParameter[] property</param>
/// <param name="paramArrayPropName">Name of desired SqlParameter[] property</param>
/// <returns>The number of records affected.</returns>
protected virtual int ExecuteQuery<T>(T data,
string query,
BulkInsertType bulkType,
ParamPrecedence precedence = ParamPrecedence.None,
string paramArrayPropName = "SqlParameters")
{
int result = 0;
SqlParameter[] parameters = DALRepository.MakeSqlParameters(data, bulkType);
result = this.ExecuteQuery(query, parameters);
return result;
}
/// <summary>
/// Performs an insert of an entity collection, using a persistent connection (reduces <br/>
/// processing time and memory consumption because we're not opening/closing a database <br/>
/// connection for every item). This method is intended to be used for data that needs <br/>
/// to be merged (update or insert) into a table rather than simply inserted.
/// </summary>
/// <typeparam name="T">The type of entity represented by the specified collection</typeparam>
/// <param name="data">The collection of entities</param>
/// <param name="storedProc">The name of the stored proc (must be a stored proc)</param>
/// <param name="bulkType">Indicates how properties should be retrieved from the entity items</param>
/// <param name="precedence">Whether to ignore the discovered SqlParameter[] property</param>
/// <param name="paramArrayPropName">Name of desired SqlParameter[] property</param>
/// <exception cref="ArgumentNullException">If the data parameter is null</exception>
/// <exception cref="ArgumentNullException">If the query parameter is null/empty</exception>
/// <exception cref="InvalidOperationException">If the data collection is empty.</exception>
/// <returns>The number of records affected.</returns>
protected virtual int ExecuteQuery<T>(IEnumerable<T> data,
string query,
BulkInsertType bulkType,
ParamPrecedence precedence = ParamPrecedence.None,
string paramArrayPropName = "SqlParameters")
{
if (string.IsNullOrEmpty(query))
{
throw new ArgumentNullException("query");
}
if (data == null)
{
throw new ArgumentNullException("data");
}
if (data.Count() == 0)
{
throw new InvalidOperationException("Data collection must contain at least onme item");
}
int result = this.DoBulkMerge(data, query, bulkType, CommandType.Text, precedence, paramArrayPropName);
return result;
}
/// <summary>
/// Performs a simply bulk insert into a table in the database. The schema MUST be part of <br/>
/// the table name. Using a bulk insert is NOT suitable if you need to merge data into an <br/>
/// existing table. Use the BulkMerge stored proc instead.
/// </summary>
/// <param name="dataTable">The datatable to bulk copy</param>
/// <returns>The number of records affected.</returns>
/// <exception cref="InvalidOperationException">If the table name hasn't been specified in the datatable</exception>
/// <exception cref="InvalidOperationException">If the schema hasn't been specified as part of the tablename</exception>
/// <exception cref="InvalidOperationException">If the dataTable is empty</exception>
protected virtual int ExecuteBulkInsert(DataTable dataTable)
{
if (string.IsNullOrEmpty(dataTable.TableName))
{
throw new InvalidOperationException("The table name MUST be specified in the datatable (including the schema).");
}
if (!dataTable.TableName.Contains('.') || dataTable.TableName.StartsWith("."))
{
throw new InvalidOperationException("The schema MUST be specified with the table name.");
}
if (dataTable.Rows.Count == 0)
{
throw new InvalidOperationException("The dataTable must contain at least one item");
}
int recsBefore = this.BulkInsertTargetCount(dataTable.TableName);
int recordsAffected = 0;
SqlConnection conn = null;
SqlBulkCopy bulk = null;
using (conn = new SqlConnection(this.ConnectionString.Base64Decode()))
{
conn.Open();
using (bulk = new SqlBulkCopy(conn, this.BulkCopyOptions, this.ExternalTransaction)
{
BatchSize = this.BulkInsertBatchSize
,
BulkCopyTimeout = this.BulkCopyTimeout
,
DestinationTableName = dataTable.TableName
})
{
Debug.WriteLine("DoBulkInsert - inserting {0} rows", dataTable.Rows.Count);
bulk.WriteToServer(dataTable);
}
}
int recsAfter = this.BulkInsertTargetCount(dataTable.TableName);
recordsAffected = recsAfter - recsBefore;
return recordsAffected;
}
/// <summary>
/// Performs a simple bulk insert into a table in the database. The schema MUST be part of <br/>
/// the table name.
/// </summary>
/// <typeparam name="T">The entity type being bulk inserted</typeparam>
/// <param name="data">The list of entities to be inserted</param>
/// <param name="tableName">The table name in which to insert the data</param>
/// <param name="bulkType">Indicates how properties should be retrieved from the entity items</param>
/// <param name="precedence">Whether to ignore the discovered SqlParameter[] property</param>
/// <param name="paramArrayPropName">Name of desired SqlParameter[] property</param>
/// <returns>Number of records affected.</returns>
/// <exception cref="ArgumentNullException">If the data parameter is null.</exception>
/// <exception cref="ArgumentNullException">If The tableName parameter cannot be null/empty.</exception>
/// <exception cref="InvalidOperationException">If the data collection is empty.</exception>
/// <exception cref="InvalidOperationException">If the table name doesn't include the schema.</exception>
protected virtual int ExecuteBulkInsert<T>(IEnumerable<T> data,
string tableName,
BulkInsertType bulkType,
ParamPrecedence precedence = ParamPrecedence.None,
string paramArrayPropName = "SqlParameters")
{
// sanity checks
if (data == null)
{
throw new ArgumentNullException("data");
}
if (data.Count() == 0)
{
throw new InvalidOperationException("The data collection must contain at least one item");
}
if (string.IsNullOrEmpty(tableName))
{
throw new ArgumentNullException("The tableName parameter cannot be null or empty.");
}
if (!tableName.Contains('.'))
{
throw new InvalidOperationException("The schema MUST be specified with the table name.");
}
int result = 0;
DataTable dataTable = null;
if (data.Count() > 0)
{
dataTable = this.MakeDataTable(data, tableName, bulkType, precedence, paramArrayPropName);
result = this.ExecuteBulkInsert(dataTable);
}
return result;
}
#endregion protected data access methods
#region Protected helper methods
/// <summary>
/// Set some reasonable defaults
/// </summary>
protected virtual void Init()
{
// this method is called from the constructor(s), and exists so that we can overload
// the constructor without duplicating code.
this.TimeoutSecs = 300;
this.FailOnMismatch = false;
this.AddReturnParamIfMissing = true;
this.ExternalTransaction = null;
this.BulkInsertBatchSize = 250;
this.BulkCopyTimeout = 600;
this.BulkCopyOptions = SqlBulkCopyOptions.Default;
this.CanReadWriteDB = true;
}
/// <summary>
/// Allows the programmer to test the database connection before trying to use it.
/// </summary>
/// <returns>If valid and empty string, otherwise, the message text from the ensuing <br/>exception.</returns>
protected virtual string TestConnection()
{
// assume success
string result = string.Empty;
SqlConnection conn = null;
try
{
using (conn = new SqlConnection(this.ConnectionString.Base64Decode()))
{
conn.Open();
conn.Close();
}
}
catch (Exception ex)
{
result = ex.Message;
}
return result;
}
/// <summary>
/// Adds a try/catch block, as well as a transaction (with optional name) to the specified <br/>
/// plain sql query text. This code does not checkt to see if transaction code is already <br/>
/// part of the query.
/// </summary>
/// <param name="query">The query to encase</param>
/// <param name="logQuery">The query that implements your logging mechanism.</param>
/// <param name="transactionName">The desired transaction name (optional)</param>
/// <returns></returns>
protected virtual string AddTryCatchTranToQuery(string query, string logQuery, string transactionName = "")
{
transactionName = transactionName.Trim();
logQuery = logQuery.Trim();
StringBuilder text = new StringBuilder();
text.AppendLine("BEGIN TRY");
text.AppendFormat(" BEGIN TRAN {0};", transactionName).AppendLine();
text.AppendLine(query).AppendLine();
text.AppendFormat(" COMMIT TRAN {0};", transactionName).AppendLine();
text.AppendLine("END TRY");
text.AppendLine("BEGIN CATCH");
text.AppendFormat(" IF @@TRANCOUNT > 0 ROLLBACK TRAN {0};", transactionName).AppendLine();
text.AppendLine(logQuery);
text.AppendLine("END CATCH");
return text.ToString();
}
/// <summary>
/// Normalizes the table name so there's no chance of sql injection. You can't be too careful.
/// </summary>
/// <param name="tableName">The table name (should have the schema included as well.</param>
/// <returns>The tablename with square brackets around it</returns>
protected virtual string NormalizeTableName(string tableName)
{
string[] parts = tableName.Split('.');
tableName = string.Empty;
foreach (string part in parts)
{
tableName = (string.IsNullOrEmpty(tableName))
? string.Format("[{0}]", part)
: string.Format(".[{0}]", part);
}
return tableName.Replace("[[", "[").Replace("]]", "]");
}
/// <summary>
/// Counts the number of records currently in the tarble targeted by the merge attempt.
/// </summary>
/// <param name="tableName">The table we're counting</param>
/// <returns>The number of records in the specified table</returns>
protected virtual int BulkInsertTargetCount(string tableName)
{
if (string.IsNullOrEmpty(tableName))
{
throw new ArgumentNullException("tableName");
}
if (!tableName.Contains('.') || tableName.StartsWith("."))
{
throw new InvalidOperationException("The [tableName] must include a schema. Example: 'dbo.tableName'");
}
int result = 0;
string query = string.Format("SELECT COUNT(1) FROM {0}", this.NormalizeTableName(tableName));
List<EntityRowCount> rowCount = this.ExecuteQuery<EntityRowCount>(query);
if (rowCount != null && rowCount.Count > 0)
{
result = rowCount[0].Count;
}
return result;
}
/// <summary>
/// Calls SqlCommand.ExecuteDataReader() to retrieve a dataset from the database.
/// </summary>
/// <param name="cmdText">The storedproc or query to execute</param>
/// <param name="parameters">The parameters to use in the storedproc/query</param>
/// <returns></returns>
/// <exception cref="ArgumentNullException">The cmdText parameter cannot be null/empty.</exception>
protected virtual DataTable GetData(string cmdText,
SqlParameter[] parameters = null,
CommandType cmdType = CommandType.StoredProcedure)
{
if (string.IsNullOrEmpty(cmdText))
{
throw new ArgumentNullException("cmdText");
}
//------------------------
// by defining these variables OUTSIDE the using statements, we can evaluate them in
// the debugger even when the using's go out of scope.
SqlConnection conn = null;
SqlCommand cmd = null;
SqlDataReader reader = null;
DataTable data = null;
// create the connection
using (conn = new SqlConnection(this.ConnectionString.Base64Decode()))
{
// open it
conn.Open();
// create the SqlCommand object
using (cmd = new SqlCommand(cmdText, conn) { CommandTimeout = this.TimeoutSecs, CommandType = cmdType })
{
// give the SqlCommand object the parameters required for the stored proc/query
if (parameters != null)
{
cmd.Parameters.AddRange(parameters);
}
//create the SqlDataReader
if (this.CanReadWriteDB)
{
using (reader = cmd.ExecuteReader())
{
// move the data to a DataTable
data = new DataTable();
data.Load(reader);
}
}
}
}
// return the DataTable object to the calling method
return data;
}
/// <summary>
/// Calls SqlCommand.ExecuteNonQuery to save data to the database.
/// </summary>
/// <param name="cmdText">The query text to execute</param>
/// <param name="parameters">The parameters to use</param>
/// <param name="cmdType">The sql command type</param>
/// <param name="useAdoTransaction">Flag indicating to wrap query with ado transaction</param>
/// <returns>The number of records affected</returns>
/// <exception cref="ArgumentNullException">The cmdText parameter cannot be null/empty.</exception>
protected virtual int SetData(string cmdText,
SqlParameter[] parameters,
CommandType cmdType = CommandType.StoredProcedure,
bool useAdoTransaction = false)
{
if (string.IsNullOrEmpty(cmdText))
{
throw new ArgumentNullException("cmdText");
}
int result = 0;
SqlConnection conn = null;
SqlCommand cmd = null;
SqlTransaction transaction = null;
using (conn = new SqlConnection(this.ConnectionString.Base64Decode()))
{
conn.Open();
if (useAdoTransaction && cmdType != CommandType.StoredProcedure)
{
transaction = conn.BeginTransaction();
}
using (cmd = new SqlCommand(cmdText, conn) { CommandTimeout = this.TimeoutSecs, CommandType = cmdType })
{
SqlParameter rowsAffected = null;
if (parameters != null)
{
cmd.Parameters.AddRange(parameters);
// if this is a stored proc and we want to add a return param
if (cmdType == CommandType.StoredProcedure && this.AddReturnParamIfMissing)
{
// see if we already have a return parameter
rowsAffected = parameters.FirstOrDefault(x => x.Direction == ParameterDirection.ReturnValue);
// if we don't, add one.
if (rowsAffected == null)
{
rowsAffected = cmd.Parameters.Add(new SqlParameter("@rowsAffected", SqlDbType.Int) { Direction = ParameterDirection.ReturnValue });
}
}
}
try
{
if (this.CanReadWriteDB)
{
result = cmd.ExecuteNonQuery();
}
}
catch (SqlException ex)
{
if (transaction != null && cmdType != CommandType.StoredProcedure)
{
transaction.Rollback();
}
throw (ex);
}
result = (rowsAffected != null) ? (int)rowsAffected.Value : result;
}
}
return result;
}
/// <summary>
/// Base BulkMerge
/// </summary>
/// <typeparam name="T">The entity type represented by the collection</typeparam>
/// <param name="data">The entity collection</param>
/// <param name="queryText">The query text to execute</param>
/// <param name="bulkType">How to extract propertiues from the entity</param>
/// <param name="cmdType">The sql command type</param>
/// <returns>Number of records affected.</returns>
protected virtual int DoBulkMerge<T>(IEnumerable<T> data,
string queryText,
BulkInsertType bulkType,
CommandType cmdType,
ParamPrecedence precedence = ParamPrecedence.None,
string paramArrayPropName = "SqlParameters",
bool useAdoTransaction = false)
{
if (string.IsNullOrEmpty(queryText))
{
throw new ArgumentNullException("queryText");
}
int result = 0;
SqlConnection conn = null;
SqlCommand cmd = null;
SqlTransaction transaction = null;
using (conn = new SqlConnection(this.ConnectionString.Base64Decode()))
{
conn.Open();
if (useAdoTransaction && cmdType != CommandType.StoredProcedure)
{
transaction = conn.BeginTransaction();
}
using (cmd = new SqlCommand(queryText, conn) { CommandTimeout = this.TimeoutSecs, CommandType = cmdType })
{
try
{
foreach (T item in data)
{
SqlParameter[] parameters = DALRepository.MakeSqlParameters(item, bulkType, precedence, paramArrayPropName);
if (parameters != null)
{
cmd.Parameters.AddRange(parameters);
if (this.CanReadWriteDB)
{
cmd.ExecuteNonQuery();
}
cmd.Parameters.Clear();
result++;
}
}
}
catch (Exception ex)
{
if (transaction != null)
{
transaction.Rollback();
}
throw (ex);
}
}
}
return result;
}
/// <summary>
/// Converts a value from its database value to something we can use (we need this because <br/>
/// we're using reflection to populate our entities)
/// </summary>
/// <typeparam name="T">The object type</typeparam>
/// <param name="obj">The object</param>
/// <param name="defaultValue">The default value to be used if object is null</param>
/// <returns>The object of the associated C# data type</returns>
protected static T ConvertFromDBValue<T>(object obj, T defaultValue)
{
T result = (obj == null || obj == DBNull.Value) ? default(T) : (T)obj;
return result;
}
/// <summary>
/// Creates the list of entities from the specified DataTable object. We do this because we <br/>
/// have two methods that both need to do the same thing.
/// </summary>
/// <typeparam name="T">The entity type represented by the collection</typeparam>
/// <param name="data">The entity collection</param>
/// <returns>The instantiated and populated list of entities.</returns>
/// <exception cref="ArgumentNullException">The data parameter cannot be null.</exception>
protected virtual List<T> MakeEntityFromDataTable<T>(DataTable data)
{
if (data == null)
{
throw new ArgumentNullException("data");
}
//----------------------------
Type objType = typeof(T);
List<T> collection = new List<T>();
// if we got back data
if (data != null && data.Rows.Count > 0)
{
// we're going to count how many properties in the model were assigned from the
// datatable.
int matched = 0;
foreach (DataRow row in data.Rows)
{
// create an instance of our object
T item = (T)Activator.CreateInstance(objType);
// get our object type's properties
PropertyInfo[] properties = objType.GetProperties();
// set the object's properties as they are found.
foreach (PropertyInfo property in properties)
{
if (data.Columns.Contains(property.Name))
{
Type pType = property.PropertyType;
var defaultValue = pType.GetDefaultValue();
var value = row[property.Name];
value = DALRepository.ConvertFromDBValue(value, defaultValue);
property.SetValue(item, value);
matched++;
}
}
if (matched != data.Columns.Count && this.FailOnMismatch)
{
throw new Exception("Data retrieved does not match specified model.");
}
collection.Add(item);
}
}
return collection;
}
/// <summary>
/// Creates a DataTable and populates it with the specified data collection.
/// </summary>
/// <typeparam name="T">The entity type represented by the collection</typeparam>
/// <param name="data">The collection of entties</param>
/// <param name="tableName">The name of the table to insert the data into</param>
/// <param name="bulkType">How to populate the columns from the entity</param>
/// <returns>The created/populated DataTable object.</returns>
public virtual DataTable MakeDataTable<T>(IEnumerable<T> data,
string tableName,
BulkInsertType bulkType,
ParamPrecedence precedence = ParamPrecedence.None,
string paramArrayPropName = "SqlParameters")
{
DataTable dataTable = null;
Debug.WriteLine(string.Format("MakeDataTable - {0} data items specified.", data.Count()));
using (dataTable = new DataTable() { TableName = tableName })
{
Type type = typeof(T);
// Get the properties to send to the database. If byDBInsertAttribute is true, only object
// properties that are decorated with the CanDBInsert attribute will be retrieved. If
// byDBInsertAttribute is false, only properties that have a Set method will be retrieved
PropertyInfo[] properties = DALRepository.GetEntityProperties(type, bulkType);
Debug.WriteLine(string.Format("MakeDataTable - {0} item properties per item.", properties.Length));
// create columns that match the retrieved properties
foreach (PropertyInfo property in properties)
{
//dataTable.Columns.Add(new DataColumn(property.Name, property.PropertyType));
dataTable.Columns.Add(new DataColumn(property.Name, Nullable.GetUnderlyingType(property.PropertyType) ?? property.PropertyType));
}
Debug.WriteLine(string.Format("MakeDataTable - {0} dataTable columns created.", dataTable.Columns.Count));
// add the rows to the datatable
foreach (T entity in data)
{
DataRow row = dataTable.NewRow();
foreach (PropertyInfo property in properties)
{
row[property.Name] = property.GetValue(entity) == null ? DBNull.Value : property.GetValue(entity);
}
dataTable.Rows.Add(row);
}
}
Debug.WriteLine(string.Format("MakeDataTable - {0} rows created.", dataTable.Rows.Count));
return dataTable;
}
#endregion Protected helper methods
}
}
