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