////////////////////////////////////////////////////////////////////////////////////////////////////
/// <summary>
/// An IDbConnection extension method that gets dynamic result set with paging.
/// </summary>
///
/// <remarks> Msacli, 22.04.2019. </remarks>
///
/// <param name="connection"> The connection to act on. </param>
/// <param name="sql"> The SQL. </param>
/// <param name="commandType"> Type of the command. </param>
/// <param name="transaction"> (Optional) The transaction. </param>
/// <param name="inputParameters"> (Optional) Options for controlling the input. </param>
/// <param name="outputParameters"> (Optional) Options for controlling the output. </param>
/// <param name="pageNumber"> (Optional) The page number. </param>
/// <param name="pageItemCount"> (Optional) Number of page ıtems. </param>
///
/// <returns> The dynamic result set with paging. </returns>
////////////////////////////////////////////////////////////////////////////////////////////////////
public static List <ExpandoObject> GetDynamicResultSetWithPaging(this IDbConnection connection,
string sql, CommandType commandType,
IDbTransaction transaction = null,
Dictionary <string, object> inputParameters = null,
Dictionary <string, object> outputParameters = null,
uint pageNumber = 1, uint pageItemCount = 10)
{
List <ExpandoObject> list = new List <ExpandoObject>();
using (IDbCommand command = connection.CreateCommand())
{
command.CommandText = sql;
command.CommandType = commandType;
if (transaction != null)
{
command.Transaction = transaction;
}
DxDbCommandHelper.SetCommandParameters(command, inputParameters, outputParameters);
using (var reader = command.ExecuteReader())
{
try
{
if (outputParameters != null && outputParameters.Count > 0)
{
outputParameters = (Dictionary <string, object>)DxDbCommandHelper.GetOutParametersOfCommand(command);
}
list = reader.GetDynamicResultSetWithPaging(
pageNumber: pageNumber, pageItemCount: pageItemCount, closeAtFinal: false);
}
catch (Exception e)
{
throw;
}
finally
{
if (reader != null && !reader.IsClosed)
{
reader.Close();
}
}
}
}
return(list);
}
////////////////////////////////////////////////////////////////////////////////////////////////////
/// <summary> An IDbConnection extension method that executes the reader operation. </summary>
///
/// <remarks> Msacli, 22.04.2019. </remarks>
///
/// <param name="connection"> The connection to act on. </param>
/// <param name="sql"> The SQL. </param>
/// <param name="commandType"> (Optional) Type of the command. </param>
/// <param name="transaction"> (Optional) The transaction. </param>
/// <param name="inputParameters"> (Optional) Options for controlling the input. </param>
/// <param name="outputParameters"> (Optional) Options for controlling the output. </param>
///
/// <returns> An IDataReader. </returns>
////////////////////////////////////////////////////////////////////////////////////////////////////
public static IDataReader ExecuteReader(this IDbConnection connection,
string sql, CommandType commandType = CommandType.Text,
IDbTransaction transaction = null,
Dictionary <string, object> inputParameters = null,
Dictionary <string, object> outputParameters = null, CommandBehavior?commandBehavior = null)
{
IDataReader dataReader = null;
try
{
using (IDbCommand command = connection.CreateCommand())
{
command.CommandText = sql;
command.CommandType = commandType;
if (transaction != null)
{
command.Transaction = transaction;
}
DxDbCommandHelper.SetCommandParameters(command, inputParameters, outputParameters);
if (!commandBehavior.HasValue)
{
dataReader = command.ExecuteReader();
}
else
{
dataReader = command.ExecuteReader(commandBehavior.Value);
}
if (outputParameters != null && outputParameters.Count > 0)
{
outputParameters = (Dictionary <string, object>)DxDbCommandHelper.GetOutParametersOfCommand(command);
}
}
}
catch (Exception e)
{
throw;
}
return(dataReader);
}
////////////////////////////////////////////////////////////////////////////////////////////////////
/// <summary> An IDbConnection extension method that gets multi dynamic result set. </summary>
///
/// <remarks> Msacli, 22.04.2019. </remarks>
///
/// <param name="connection"> The connection to act on. </param>
/// <param name="sql"> The SQL. </param>
/// <param name="commandType"> (Optional) Type of the command. </param>
/// <param name="transaction"> (Optional) The transaction. </param>
/// <param name="inputParameters"> (Optional) Options for controlling the input. </param>
/// <param name="outputParameters"> (Optional) Options for controlling the output. </param>
///
/// <returns> The multi dynamic result set. </returns>
////////////////////////////////////////////////////////////////////////////////////////////////////
public static List <List <ExpandoObject> > GetMultiDynamicResultSet(this IDbConnection connection,
string sql, CommandType commandType = CommandType.Text,
IDbTransaction transaction = null,
Dictionary <string, object> inputParameters = null,
Dictionary <string, object> outputParameters = null)
{
var list = new List <List <ExpandoObject> >();
using (IDbCommand command = connection.CreateCommand())
{
command.CommandText = sql;
command.CommandType = commandType;
if (transaction != null)
{
command.Transaction = transaction;
}
DxDbCommandHelper.SetCommandParameters(command, inputParameters, outputParameters);
using (var reader = command.ExecuteReader())
{
try
{
if (outputParameters != null && outputParameters.Count > 0)
{
outputParameters = (Dictionary <string, object>)DxDbCommandHelper.GetOutParametersOfCommand(command);
}
list = reader.GetMultiDynamicResultSet(closeAtFinal: true);
}
catch (Exception e)
{
throw;
}
finally
{
reader.CloseIfNot();
}
}
}
return(list);
}
////////////////////////////////////////////////////////////////////////////////////////////////////
/// <summary> An IDbConnection extension method that executes. </summary>
///
/// <remarks> Msacli, 22.04.2019. </remarks>
///
/// <param name="connection"> The connection to act on. </param>
/// <param name="sql"> The SQL. </param>
/// <param name="commandType"> (Optional) Type of the command. </param>
/// <param name="transaction"> (Optional) The transaction. </param>
/// <param name="inputParameters"> (Optional) Options for controlling the input. </param>
/// <param name="outputParameters"> (Optional) Options for controlling the output. </param>
///
/// <returns> An int. </returns>
////////////////////////////////////////////////////////////////////////////////////////////////////
public static int Execute(this IDbConnection connection,
string sql,
CommandType commandType = CommandType.Text,
IDbTransaction transaction = null,
Dictionary <string, object> inputParameters = null,
Dictionary <string, object> outputParameters = null)
{
int res = 0;
try
{
using (IDbCommand command = connection.CreateCommand())
{
command.CommandText = sql;
command.CommandType = commandType;
if (transaction != null)
{
command.Transaction = transaction;
}
DxDbCommandHelper.SetCommandParameters(command, inputParameters, outputParameters);
res = command.ExecuteNonQuery();
if (outputParameters != null && outputParameters.Count > 0)
{
outputParameters = (Dictionary <string, object>)DxDbCommandHelper.GetOutParametersOfCommand(command);
}
}
}
catch (Exception e)
{
res = -1;
throw;
}
return(res);
}
////////////////////////////////////////////////////////////////////////////////////////////////////
/// <summary> An IDbConnection extension method that gets result set of sql query. </summary>
///
/// <remarks> Msacli, 22.04.2019. </remarks>
///
/// <param name="connection"> The connection to act on. </param>
/// <param name="sql"> The SQL. </param>
/// <param name="commandType"> (Optional) Type of the command. </param>
/// <param name="transaction"> (Optional) The transaction. </param>
/// <param name="inputParameters"> (Optional) Options for controlling the input. </param>
/// <param name="outputParameters"> (Optional) Options for controlling the output. </param>
///
/// <returns> The DataSet object instance. </returns>
////////////////////////////////////////////////////////////////////////////////////////////////////
public static DataSet GetResultSet(this IDbConnection connection,
string sql, CommandType commandType = CommandType.Text,
IDbTransaction transaction = null,
Dictionary <string, object> inputParameters = null,
Dictionary <string, object> outputParameters = null)
{
DataSet dataset = null;
try
{
Type adapterType = null;
var adapterTypes =
connection.GetType().Assembly.GetExportedTypes().Where(
typ => typ.IsClass && typ.GetInterfaces().Contains(typeof(IDbDataAdapter)) &&
typ.IsAbstract == false && typeof(IDbDataAdapter).IsAssignableFrom(typ));
if (adapterTypes.Count() > 1)
{
var connTypeName = connection.GetType().Name;
if (connTypeName.EndsWith("Connection"))
{
connTypeName = connTypeName.Substring(0, connTypeName.Length - 10).ToLower();
}
adapterType = adapterTypes.Where(typ => typ.Name.ToLower().StartsWith(connTypeName)).First();
}
else
{
adapterType = adapterTypes.First();
}
if (adapterType != null)
{
var dataAdapter = Activator.CreateInstance(adapterType) as IDbDataAdapter;
using (IDbCommand command = connection.CreateCommand())
{
command.CommandText = sql;
command.CommandType = commandType;
if (transaction != null)
{
command.Transaction = transaction;
}
DxDbCommandHelper.SetCommandParameters(command, inputParameters, outputParameters);
dataAdapter.SelectCommand = command;
dataset = new DataSet();
var result = dataAdapter.Fill(dataset);
if (outputParameters != null && outputParameters.Count > 0)
{
outputParameters = (Dictionary <string, object>)DxDbCommandHelper.GetOutParametersOfCommand(command);
}
}
}
}
catch (Exception e)
{
throw;
}
return(dataset);
}