//////////////////////////////////////////////////////////////////////////////////////////////////// /// <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="parameters"> A variable-length parameters list containing parameters. </param> /// /// <returns> An IDataReader. </returns> //////////////////////////////////////////////////////////////////////////////////////////////////// public static IDataReader ExecuteReader(this IDbConnection connection, string sql, CommandType commandType = CommandType.Text, IDbTransaction transaction = null, params object[] parameters) { IDataReader reader = null; try { using (IDbCommand command = connection.CreateCommand()) { command.CommandText = sql; command.CommandType = commandType; if (transaction != null) { command.Transaction = transaction; } DxDbCommandHelper.SetCommandParameters(command, parameters); reader = command.ExecuteReader(); } } catch (Exception e) { throw; } return(reader); }
//////////////////////////////////////////////////////////////////////////////////////////////////// /// <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="parameters"> A variable-length parameters list containing parameters. </param> /// /// <returns> An int. </returns> //////////////////////////////////////////////////////////////////////////////////////////////////// public static int Execute(this IDbConnection connection, string sql, CommandType commandType = CommandType.Text, IDbTransaction transaction = null, params object[] parameters) { int res = 0; try { using (var command = connection.CreateCommand()) { command.CommandText = sql; command.CommandType = commandType; if (transaction != null) { command.Transaction = transaction; } DxDbCommandHelper.SetCommandParameters(command, parameters); res = command.ExecuteNonQuery(); } } catch (Exception e) { res = -1; throw; } return(res); }
//////////////////////////////////////////////////////////////////////////////////////////////////// /// <summary> An IDbConnection extension method that gets 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="parameters"> A variable-length parameters list containing parameters. </param> /// /// <returns> The result set. </returns> //////////////////////////////////////////////////////////////////////////////////////////////////// public static DataSet GetResultSet(this IDbConnection connection, string sql, CommandType commandType = CommandType.Text, IDbTransaction transaction = null, params object[] parameters) { 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; 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, parameters); dataAdapter.SelectCommand = command; dataset = new DataSet(); var result = dataAdapter.Fill(dataset); } } } catch (Exception e) { throw; } return(dataset); }
//////////////////////////////////////////////////////////////////////////////////////////////////// /// <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 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="parameters"> A variable-length parameters list containing parameters. </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, params object[] parameters) { 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, parameters); using (var reader = command.ExecuteReader()) { try { list = reader.GetMultiDynamicResultSet(closeAtFinal: true); } catch (Exception e) { throw; } finally { if (reader != null && !reader.IsClosed) { reader.Close(); } } } } 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); }