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 }); }