public object ExecuteScalar(
string query,
ParameterSetup parameters = null,
CommandType commandType = CommandType.StoredProcedure)
{
object result = null;
DbConnection connection = null;
try
{
if (Settings.ConnectionConfiguration != null)
{
connection = Settings.ConnectionConfiguration();
}
else
{
connection = CreateConnection(Settings.ConnectionString);
}
if (connection.State == ConnectionState.Closed)
{
connection.Open();
}
using (DbCommand command = connection.CreateCommand())
{
command.CommandText = query;
command.CommandType = commandType;
if (parameters != null)
{
parameters(command.Parameters);
}
try
{
result = command.ExecuteScalar();
}
catch (SqlException)
{
throw;
}
catch (Exception ex)
{
throw new QueryMappingException(ex);
}
}
}
finally
{
if (connection != null)
{
connection.Dispose();
}
}
return(result);
}
public int ExecuteNonQuery(
string query,
ParameterSetup parameters = null,
CommandType commandType = CommandType.StoredProcedure)
{
DbConnection connection = null;
try
{
if (Settings.ConnectionConfiguration != null)
{
connection = Settings.ConnectionConfiguration();
}
else
{
connection = CreateConnection(Settings.ConnectionString);
}
if (connection.State == ConnectionState.Closed)
{
connection.Open();
}
using (DbCommand command = connection.CreateCommand())
{
command.CommandText = query;
parameters?.Invoke(command.Parameters);
try
{
command.CommandType = commandType;
return(command.ExecuteNonQuery());
}
catch (SqlException)
{
throw;
}
catch (Exception ex)
{
throw new QueryMappingException(ex);
}
}
}
finally
{
if (connection != null)
{
connection.Dispose();
}
}
}
/// <summary>
/// Execute a Sql Query that returns 3 result sets. Using multiple resultsets in the same query
/// can improve your performance since there is no need to reopen a connection or to open
/// another data reader.
/// </summary>
/// <typeparam name="TResult1">The type for the first result, this method also supports anonymous types.</typeparam>
/// <typeparam name="TResult2">The type for the second result, this method also supports anonymous types.</typeparam>
/// <param name="query">The query to run against the database.</param>
/// <param name="mapping1">A lambda expression with the mapping for the first result.</param>
/// <param name="mapping2">A lambda expression with the mapping for the second result.</param>
/// <param name="mapping3">A lambda expression with the mapping for the third result.</param>
/// <param name="parameters">A delegate to setup the parameters for the query.
/// ie.
/// ...
/// parameters : _ => { _.AddWithValue("@MyParam", paramValue); }
/// ...
/// </param>
/// <param name="commandType">Sets whether the query is run as a Stored Procedure or a Query, this parameter defaults to Stored Procedure - Yes, we did that on purpose.</param>
/// <returns>
/// A result set with three properties for each result.
/// </returns>
public ResultSet <TResult1, TResult2, TResult3> ExecuteObjectArray <TResult1, TResult2, TResult3>(
string query,
Func <DbDataReader, TResult1> mapping1,
Func <DbDataReader, TResult2> mapping2,
Func <DbDataReader, TResult3> mapping3,
ParameterSetup parameters = null,
CommandType commandType = CommandType.StoredProcedure)
{
if (query == null)
{
throw new ArgumentNullException("query");
}
if (mapping1 == null)
{
throw new ArgumentNullException("mapping1");
}
if (mapping2 == null)
{
throw new ArgumentNullException("mapping2");
}
if (mapping3 == null)
{
throw new ArgumentNullException("mapping3");
}
var resultSet = ExecuteObjectArray(
query: query,
mapping1: mapping1,
mapping2: mapping2,
mapping3: mapping3,
mapping4: m_empty,
parameters: parameters,
commandType: commandType
);
return(new ResultSet <TResult1, TResult2, TResult3>()
{
Result1 = resultSet.Result1,
Result2 = resultSet.Result2,
Result3 = resultSet.Result3
});
}
private void SetID(Parameter parameter, ParameterSetup parameterSetup)
{
EditorGUILayout.LabelField("ID: " + parameter.Id, GUILayout.Width(45));
}
public IEnumerable <T> ExecuteObjectArray <T>(
string query,
Func <DbDataReader, T> mapping,
ParameterSetup parameters = null,
CommandType commandType = CommandType.StoredProcedure)
{
if (string.IsNullOrEmpty(query))
{
throw new ArgumentNullException("query");
}
if (mapping == null)
{
throw new ArgumentNullException("mapping");
}
List <T> results = new List <T>();
DbConnection connection = null;
try
{
if (Settings.ConnectionConfiguration != null)
{
connection = Settings.ConnectionConfiguration();
}
else
{
connection = CreateConnection(Settings.ConnectionString);
}
if (connection.State == ConnectionState.Closed)
{
connection.Open();
}
using (DbCommand command =
Settings.CommandConfiguration == null ?
connection.CreateCommand()
:
Settings.CommandConfiguration(connection))
{
command.CommandText = query;
command.CommandType = commandType;
if (parameters != null)
{
parameters(command.Parameters);
}
try
{
using (DbDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
var result = mapping(reader);
results.Add(result);
}
}
}
catch (SqlException)
{
throw;
}
catch (Exception ex)
{
throw new QueryMappingException(ex);
}
}
}
finally
{
if (connection != null)
{
connection.Dispose();
}
}
return(results);
}
public ResultSet <TResult1, TResult2, TResult3, TResult4> ExecuteObjectArray <TResult1, TResult2, TResult3, TResult4>(
string query,
Func <DbDataReader, TResult1> mapping1,
Func <DbDataReader, TResult2> mapping2,
Func <DbDataReader, TResult3> mapping3,
Func <DbDataReader, TResult4> mapping4,
ParameterSetup parameters = null,
CommandType commandType = CommandType.StoredProcedure)
{
if (query == null)
{
throw new ArgumentNullException("query");
}
if (mapping1 == null)
{
throw new ArgumentNullException("mapping1");
}
if (mapping2 == null)
{
throw new ArgumentNullException("mapping2");
}
if (mapping3 == null)
{
throw new ArgumentNullException("mapping3");
}
if (mapping4 == null)
{
throw new ArgumentNullException("mapping3");
}
List <TResult1> results1 = null;
List <TResult2> results2 = null;
List <TResult3> results3 = null;
List <TResult4> results4 = null;
DbConnection connection = null;
try
{
if (Settings.ConnectionConfiguration != null)
{
connection = Settings.ConnectionConfiguration();
}
else
{
connection = CreateConnection(Settings.ConnectionString);
}
if (connection.State == ConnectionState.Closed)
{
connection.Open();
}
using (DbCommand command = connection.CreateCommand())
{
command.CommandText = query;
command.CommandType = commandType;
if (parameters != null)
{
parameters(command.Parameters);
}
try
{
using (DbDataReader reader = command.ExecuteReader())
{
results1 = new List <TResult1>();
while (reader.Read())
{
var result1 = mapping1(reader);
results1.Add(result1);
}
if (reader.NextResult())
{
if (mapping2 == null)
{
throw new InvalidOperationException("The query seems to have a second result set but no mappings2 was specified.");
}
results2 = new List <TResult2>();
while (reader.Read())
{
var result2 = mapping2(reader);
results2.Add(result2);
}
}
if (reader.NextResult())
{
if (mapping3 == null)
{
throw new InvalidOperationException("The query seems to have a third result set but no mappings3 was specified.");
}
results3 = new List <TResult3>();
while (reader.Read())
{
var result3 = mapping3(reader);
results3.Add(result3);
}
}
if (reader.NextResult())
{
if (mapping2 == null)
{
throw new InvalidOperationException("The query seems to have a fourth result set but no mappings4 was specified.");
}
results4 = new List <TResult4>();
while (reader.Read())
{
var result4 = mapping4(reader);
results4.Add(result4);
}
}
}
}
catch (SqlException)
{
throw;
}
catch (Exception ex)
{
throw new QueryMappingException(ex);
}
}
}
finally
{
if (connection != null)
{
connection.Dispose();
}
}
return(new ResultSet <TResult1, TResult2, TResult3, TResult4>()
{
Result1 = results1,
Result2 = results2,
Result3 = results3,
Result4 = results4
});
}
