/// <param name="tableName">Name of the table.</param>
/// <param name="fieldList">The list of fields returned by the query (ignored for stored procedure execution)</param>
/// <param name="fieldNames">The list of fields by which to filter</param>
/// <param name="filterParameters">Parameters used for filtering. The parameters need to match the list of filter fields (name and types)</param>
/// <param name="selectMethod">Process method for the select method</param>
/// <returns>DataSet with the returned data</returns>
/// <example>
/// var fieldNames = new string[] { "FirstName", "LastName", "IsActive" };
/// var parameters = new object[] { "Chris", "Pronger", true };
/// using (var data = await service.QueryAsync("Customers", "*", fieldNames, parameters);
/// {
/// // Do something with the data
/// }
/// </example>
public virtual async Task<DataSet> QueryAsync(string tableName, string fieldList, IList<string> fieldNames, IList<object> filterParameters, DataRowProcessMethod selectMethod = DataRowProcessMethod.Default)
{
using (var command = BuildQueryCommand(tableName, fieldList, fieldNames, filterParameters, selectMethod))
return await ExecuteQueryAsync(command);
}
public async Task<DataSet> GetListAsync(string tableName, string fieldList = "*", string sortOrder = "", DataRowProcessMethod selectMethod = DataRowProcessMethod.Default)
{
using (var selectCommand = BuildAllRecordsQueryCommand(tableName, fieldList, sortOrder, selectMethod))
return await ExecuteQueryAsync(selectCommand, tableName);
}
/// <summary>
/// Returns a single record or multiple records (with a specified list of fields) by primary key.
/// </summary>
/// <remarks>
/// The name of the method is a little misleading. It can actually return more than one query *if* the key
/// field used for the query is non-unique (in other words: not a primary key field).
/// </remarks>
/// <param name="tableName">Name of the table to query.</param>
/// <param name="fieldList">List of fields to return.</param>
/// <param name="primaryKeyFieldName">Name of the primary key field.</param>
/// <param name="primaryKeyValue">Primary key (value)</param>
/// <param name="selectMethod">Select method (such as stored procedure or select commands)</param>
/// <returns>DataSet with the returned data</returns>
/// <example>
/// using (var data = await service.GetSingleRecordAsync("Customers", "*", "CustomerKey", key))
/// {
/// // Do something with the data
/// }
/// </example>
public virtual async Task<DataSet> GetSingleRecordAsync(string tableName, string fieldList = "*", string primaryKeyFieldName = "Id", object primaryKeyValue = null, DataRowProcessMethod selectMethod = DataRowProcessMethod.Default)
{
using (var command = BuildSingleRecordQueryCommand(tableName, fieldList, primaryKeyFieldName, primaryKeyValue, selectMethod))
return await ExecuteQueryAsync(command, tableName);
}
/// <summary>
/// Builds a command that returns a set of records based on the provided field names and filter parameters.
/// </summary>
/// <param name="tableName">Name of the table.</param>
/// <param name="fieldList">The list of fields returned by the query (ignored for stored procedure execution)</param>
/// <param name="fieldNames">The list of fields by which to filter</param>
/// <param name="filterParameters">
/// Parameters used for filtering. The parameters need to match the list of filter fields
/// (name and types)
/// </param>
/// <param name="selectMethod">Process method for the select method</param>
/// <returns>IDbCommand object representing the query</returns>
/// <example>
/// string[] fieldNames = new string[] { "FirstName", "LastName", "IsActive" };
/// object[] parameters = new object[] { "Chris", "Pronger", true };
/// IDbCommand command = service.BuildQueryCommand("Customers", "*", fieldNames, parameters,
/// DataRowProcessMethod.Default);
/// DataSet data = service.ExecuteQuery(command);
/// </example>
public abstract IDbCommand BuildQueryCommand(string tableName, string fieldList, IList<string> fieldNames = null, IList<object> filterParameters = null, DataRowProcessMethod selectMethod = DataRowProcessMethod.Default);
/// <summary>
/// Queries all records from the specified table
/// </summary>
/// <param name="tableName">Name of the table to query from</param>
/// <param name="fieldList">Fields to query (or * for all fields)</param>
/// <param name="orderBy">Order (or empty string if no special order is desired)</param>
/// <param name="selectMethod">Select method (such as stored procedure or select commands)</param>
/// <returns>DataSet with the requested data</returns>
public virtual async Task<DataSet> GetAllRecordsAsync(string tableName, string fieldList = "*", string orderBy = "", DataRowProcessMethod selectMethod = DataRowProcessMethod.Default)
{
using (var command = BuildAllRecordsQueryCommand(tableName, fieldList, orderBy, selectMethod))
return await ExecuteQueryAsync(command, tableName);
}
/// <summary>
/// Returns a single record (with a specified list of fields) by primary key.
/// </summary>
/// <param name="tableName">Name of the table to query.</param>
/// <param name="fieldList">List of fields to return.</param>
/// <param name="primaryKeyFieldName">Name of the primary key field.</param>
/// <param name="primaryKeyValue">Primary key (value)</param>
/// <param name="selectMethod">Select method (such as stored procedure or select commands)</param>
/// <returns>IDbCommand object</returns>
/// <remarks>
/// The name of the method is a little misleading. It can actually return more than one query *if* the key
/// field used for the query is non-unique (in other words: not a primary key field).
/// </remarks>
/// <example>
/// IDbCommand command = service.BuildSingleRecordQueryCommand("Customers", "*", "CustomerKey", key,
/// DataRowProcessMethod.Default);
/// DataSet data = service.ExecuteQuery(command);
/// </example>
public abstract IDbCommand BuildSingleRecordQueryCommand(string tableName, string fieldList = "*", string primaryKeyFieldName = "Id", object primaryKeyValue = null, DataRowProcessMethod selectMethod = DataRowProcessMethod.Default);
/// <summary> /// Builds a command object that queries an empty record containing all fields of the specified table. /// </summary> /// <param name="tableName">Table Name</param> /// <param name="fieldList">List of fields to be included in the query</param> /// <param name="selectMethod">Select method (such as stored procedure or select commands)</param> /// <returns>IDbCommand object</returns> public abstract IDbCommand BuildEmptyRecordQueryCommand(string tableName, string fieldList = "*", DataRowProcessMethod selectMethod = DataRowProcessMethod.Default);
/// <summary> /// Builds a command object that queries all records (with specified fields) from the specified table. /// Defining an order is possible as well. /// </summary> /// <param name="tableName">Name of the table to query from</param> /// <param name="fieldList">Fields to query (or * for all fields)</param> /// <param name="orderBy">Order (or empty string if no special order is desired)</param> /// <param name="selectMethod">Select method (such as stored procedure or select commands)</param> /// <returns>IDbCommand object</returns> public abstract IDbCommand BuildAllRecordsQueryCommand(string tableName, string fieldList = "*", string orderBy = "", DataRowProcessMethod selectMethod = DataRowProcessMethod.Default);
/// <summary> /// Creates an update command object for the row passed along. /// </summary> /// <param name="changedRow">Changed ADO.NET data row</param> /// <param name="primaryKeyType">Primary key type</param> /// <param name="primaryKeyFieldName">Name of the primary key field</param> /// <param name="updateMode">Optimistic or pessimistic update mode.</param> /// <param name="updateMethod">Method used to update the database (commands, stored procedures,...)</param> /// <param name="tableName">Name of the table.</param> /// <param name="fieldNames">Names of the fields to be included in the update (all others will be ignored)</param> /// <param name="fieldMaps"> /// List of key value pairs that can be used to map field names. For instance, if a field in the /// table is called MyId but in the database it is called ID, then one can add a key 'MyId' with a value of 'ID' /// </param> /// <returns>Update command that can sub-sequentially be executed against the database using the same data service.</returns> public abstract IDbCommand BuildUpdateCommand(DataRow changedRow, KeyType primaryKeyType, string primaryKeyFieldName, string tableName, DataRowUpdateMode updateMode = DataRowUpdateMode.ChangedFieldsOnly, DataRowProcessMethod updateMethod = DataRowProcessMethod.Default, IList<string> fieldNames = null, IDictionary<string, string> fieldMaps = null);
/// <summary> /// Creates a delete command object for the defined table and primary key. /// </summary> /// <param name="tableName">Name of the table the record is to be deleted from .</param> /// <param name="primaryKeyFieldName">Primary key field name within the table</param> /// <param name="primaryKeyValue">Primary key value for the record that is to be deleted</param> /// <param name="updateMethod">Method used to update the database (commands, stored procedures,...)</param> /// <returns>IDbCommand object that can sub-sequentially be executed against a database</returns> public abstract IDbCommand BuildDeleteCommand(string tableName, string primaryKeyFieldName, object primaryKeyValue, DataRowProcessMethod updateMethod = DataRowProcessMethod.Default);
/// <summary>
/// Returns a single record or multiple records (with a specified list of fields) by primary key.
/// </summary>
/// <remarks>
/// The name of the method is a little misleading. It can actually return more than one query *if* the key
/// field used for the query is non-unique (in other words: not a primary key field).
/// </remarks>
/// <param name="tableName">Name of the table to query.</param>
/// <param name="fieldList">List of fields to return.</param>
/// <param name="primaryKeyFieldName">Name of the primary key field.</param>
/// <param name="primaryKeyValue">Primary key (value)</param>
/// <param name="selectMethod">Select method (such as stored procedure or select commands)</param>
/// <returns>DataSet with the returned data</returns>
/// <example>
/// using (var data = Data.GetSingleRecord("Customers", "*", "CustomerKey", key))
/// {
/// // Do something with the data
/// }
/// </example>
public static DataSet GetSingleRecord(string tableName, string fieldList = "*", string primaryKeyFieldName = "Id", object primaryKeyValue = null, DataRowProcessMethod selectMethod = DataRowProcessMethod.Default) => DataService.GetSingleRecord(tableName, fieldList, primaryKeyFieldName, primaryKeyValue, selectMethod);
/// <summary> /// Queries all records from the specified table async /// </summary> /// <param name="tableName">Name of the table to query from</param> /// <param name="fieldList">Fields to query (or * for all fields)</param> /// <param name="orderBy">Order (or empty string if no special order is desired)</param> /// <param name="selectMethod">Select method (such as stored procedure or select commands)</param> /// <returns>DataSet with the requested data</returns> public static async Task <DataSet> GetAllRecordsAsync(string tableName, string fieldList = "*", string orderBy = "", DataRowProcessMethod selectMethod = DataRowProcessMethod.Default) => await DataService.GetAllRecordsAsync(tableName, fieldList, orderBy, selectMethod);
/// <summary> /// Queries all records from the specified table /// </summary> /// <param name="tableName">Name of the table to query from</param> /// <param name="fieldList">Fields to query (or * for all fields)</param> /// <param name="orderBy">Order (or empty string if no special order is desired)</param> /// <param name="selectMethod">Select method (such as stored procedure or select commands)</param> /// <returns>DataSet with the requested data</returns> public static DataSet GetAllRecords(string tableName, string fieldList = "*", string orderBy = "", DataRowProcessMethod selectMethod = DataRowProcessMethod.Default) => DataService?.GetAllRecords(tableName, fieldList, orderBy, selectMethod);
/// <param name="tableName">Name of the table.</param>
/// <param name="fieldList">The list of fields returned by the query (ignored for stored procedure execution)</param>
/// <param name="fieldNames">The list of fields by which to filter</param>
/// <param name="filterParameters">Parameters used for filtering. The parameters need to match the list of filter fields (name and types)</param>
/// <param name="selectMethod">Process method for the select method</param>
/// <returns>DataSet with the returned data</returns>
/// <example>
/// var fieldNames = new string[] { "FirstName", "LastName", "IsActive" };
/// var parameters = new object[] { "Chris", "Pronger", true };
/// using (var data = await Data.QueryAsync("Customers", "*", fieldNames, parameters);
/// {
/// // Do something with the data
/// }
/// </example>
public static async Task <DataSet> QueryAsync(string tableName, string fieldList, IList <string> fieldNames, IList <object> filterParameters, DataRowProcessMethod selectMethod = DataRowProcessMethod.Default) => await DataService.QueryAsync(tableName, fieldList, fieldNames, filterParameters, selectMethod);
/// <summary>Runs the specified query</summary>
/// <param name="tableName">Name of the table.</param>
/// <param name="fieldList">The list of fields returned by the query (ignored for stored procedure execution)</param>
/// <param name="fieldNames">The list of fields by which to filter</param>
/// <param name="filterParameters">Parameters used for filtering. The parameters need to match the list of filter fields (name and types)</param>
/// <param name="selectMethod">Process method for the select method</param>
/// <returns>DataSet with the returned data</returns>
/// <example>
/// var fieldNames = new string[] { "FirstName", "LastName", "IsActive" };
/// var parameters = new object[] { "Chris", "Pronger", true };
/// using (var data = Data.Query("Customers", "*", fieldNames, parameters);
/// {
/// // Do something with the data
/// }
/// </example>
public static DataSet Query(string tableName, string fieldList, IList <string> fieldNames, IList <object> filterParameters, DataRowProcessMethod selectMethod = DataRowProcessMethod.Default) => DataService.Query(tableName, fieldList, fieldNames, filterParameters, selectMethod);
