VersaTul.Data.Sql
2.0.16
Prefix Reserved
See the version list below for details.
dotnet add package VersaTul.Data.Sql --version 2.0.16
NuGet\Install-Package VersaTul.Data.Sql -Version 2.0.16
<PackageReference Include="VersaTul.Data.Sql" Version="2.0.16" />
paket add VersaTul.Data.Sql --version 2.0.16
#r "nuget: VersaTul.Data.Sql, 2.0.16"
// Install VersaTul.Data.Sql as a Cake Addin #addin nuget:?package=VersaTul.Data.Sql&version=2.0.16 // Install VersaTul.Data.Sql as a Cake Tool #tool nuget:?package=VersaTul.Data.Sql&version=2.0.16
VersaTul Data Sql
A library for quickly creating database access objects for any SQL databases
VersaTul Data Sql is a project that simplifies the process of accessing data from various SQL databases, such as MSSQL, SQLite, MySQL, PostgreSQL, and Oracle. It is built on top of the System.Data.Common namespace and provides helper methods for executing commands, mapping results, and managing parameters.
Installation
You can install VersaTul Data Sql from NuGet using the following command:
PM> NuGet\Install-Package VersaTul.Data.Sql -Version latest
Usage
To use VersaTul Data Sql, you need to implement the following interfaces:
IDataSource
: Represents a composite of the data role interfaces that provides read and write capabilities.IProviderFactory
: Represents a set of methods for creating instances of a provider�s implementation of the data source classes.IDataConfiguration
: Represents a set of methods or properties for getting configuration values from setting store.
You can also use the default implementations provided by the project, such as:
SqlDbDataSource
: Represents a default implementation of theIDataSource
interface.BaseDataService
: Provides a starting point for custom data services used in projects. Provides all the basic or general database functionality.Parameter
: Represents a parameter to a command and optionally its mapping to DataSet columns.ProviderFactory
: Represents a set of methods for creating instances of a provider�s implementation of the data source classes.DataConfiguration
: Provides a set of methods or properties for getting configuration values from setting store.
To execute commands and process results, you can use the methods from the BaseDataService
class, such as:
ExecuteReader()
: Executes a given command and returns aDbDataReader
object.ExecuteNonQuery()
: Executes a given command and returns the affected number of rows count.ProcessReader()
: Iterates the given data reader and provides access to the data at each row via the helper methods.
Example
Here is a simple example of using VersaTul Data Sql with Oracle as the database:
using System.Data;
using VersaTul.Configuration.Defaults.Sql;
using VersaTul.Data.Sql;
using VersaTul.Data.Sql.Configurations;
using VersaTul.Data.Sql.Contracts;
using VersaTul.Extensions;
using VersaTul.Utilities;
using VersaTul.Utilities.Contracts;
namespace SqlDatabaseConnection
{
public class Program
{
static void Main(string[] args)
{
// Supported database engines.
//MSSQL ---> System.Data.SqlClient.SqlClientFactory
//SQLite ---> System.Data.SQLite.SQLiteFactory
//MySql ---> MySql.Data.MySqlClient.MySqlClientFactory
//PostgreSql ---> Npgsql.NpgsqlFactory
//Oracle ---> Oracle.ManagedDataAccess.Client.OracleClientFactory
//Register factory
DbProviderFactories.RegisterFactory("Oracle.ManagedDataAccess.Client.OracleClientFactory", OracleClientFactory.Instance);
// Setup configuration for Oracle Database querying
var configSettings = new Builder().AddOrReplace(new[]
{
//Tested with nuget package Oracle.ManagedDataAccess.Core Version 3.21.90
new KeyValuePair<string, object>("OracleSqlDb", new ConnectionInfo("User Id=SYS;Password=Secretdatabasepassword;Data Source=database-address.local.com/ORCLCDB;DBA Privilege=SYSDBA;", "Oracle.ManagedDataAccess.Client.OracleClientFactory")),
new KeyValuePair<string, object>("SqlDbConnectionName", "OracleSqlDb")
}).BuildConfig();
var dataConfiguration = new DataConfiguration(configSettings);
// Setup needed class instance
var providerFactory = new ProviderFactory();
var commandFactory = new CommandFactory(dataConfiguration, providerFactory);
var sqlDbDataSource = new SqlDbDataSource(commandFactory);
var commonUtility = new CommonUtility();
// Create our DAL or DataService class
var dataService = new ProductDataService(sqlDbDataSource, commonUtility, commonUtility);
// Get all products
var products = dataService.Get();
// get a known product
var product = dataService.Get(100);
// Add a new product
var newProduct = dataService.Add(new Product
{
CategoryId = 1,
Description = "Some product description",
ListPrice = 100.99m,
Name = "A cool Product Name",
StandardCost = 50.99m
});
}
}
// Data Model
public class Product
{
public int Id { get; set; }
public string? Name { get; set; }
public string? Description { get; set; }
public decimal StandardCost { get; set; }
public decimal ListPrice { get; set; }
public int CategoryId { get; set; }
}
// DAL Or Data Service layer
public interface IProductService
{
Product Add(Product product);
Product? Get(int productId);
IEnumerable<Product> Get();
}
// By inheriting from BaseDataService all project specific data service will have the common functionality they need to access the dataSource.
public class ProductDataService : BaseDataService, IProductService
{
public ProductDataService(IDataSource dataSource, INullFiltering filtering, IUtility utility) : base(dataSource, filtering, utility)
{
}
// using stored command example
public IEnumerable<Product> Get()
{
var products = new List<Product>();
// using the ProcessReader method to read the return DbDataReader from ExecuteReader.
// technique commonly used to populate data models from returned data.
ProcessReader(ExecuteReader(new StoredCommand("GetAllProducts")), (position) =>
{
// position parameter: useful for multiple result sets, this value represents which reader is currently being read from in the result set.
// this information can then be used to populate different models in the lambda helper method.
products.Add(new Product
{
CategoryId = Get((Product prod) => prod.CategoryId),
Description = Get((Product prod) => prod.Description),
Id = Get((Product prod) => prod.Id),
ListPrice = Get((Product prod) => prod.ListPrice),
Name = Get((Product prod) => prod.Name),
StandardCost = Get((Product prod) => prod.StandardCost)
});
});
return products;
}
// using command text example
public Product? Get(int productId)
{
Product? product = null;
var commandText = @"select product_id as Id, product_name as Name, description as Description, standard_cost as StandardCost, list_price as ListPrice, category_id as CategoryId from products where product_id = :productId";
var parameterCollection = new ParameterCollection();
parameterCollection.Add(new Parameter("productId", productId, DbType.Int32, 0, ParameterDirection.Input));
// using the ProcessReader method to read the return DbDataReader from ExecuteReader.
// technique commonly used to populate data models from returned data.
ProcessReader(ExecuteReader(new DataCommand(commandText, DataCommandType.Query), parameterCollection), (position) =>
{
product = new Product
{
CategoryId = Get((Product prod) => prod.CategoryId),
Description = Get((Product prod) => prod.Description),
Id = Get((Product prod) => prod.Id),
ListPrice = Get((Product prod) => prod.ListPrice),
Name = Get((Product prod) => prod.Name),
StandardCost = Get((Product prod) => prod.StandardCost)
};
});
return product;
}
// using stored procedure to insert data.
public Product Add(Product product)
{
var parameterCollection = new ParameterCollection();
parameterCollection.Add(new Parameter("description", product.Description, DbType.String, 500, ParameterDirection.Input));
parameterCollection.Add(new Parameter("standard_cost", product.StandardCost, DbType.Decimal, 0, ParameterDirection.Input));
parameterCollection.Add(new Parameter("product_name", product.Name, DbType.String, 500, ParameterDirection.Input));
parameterCollection.Add(new Parameter("list_price", product.ListPrice, DbType.Decimal, 0, ParameterDirection.Input));
parameterCollection.Add(new Parameter("category_id", product.CategoryId, DbType.Int32, 0, ParameterDirection.Input));
parameterCollection.Add(new Parameter("product_id", product.Id, DbType.Int32, 0, ParameterDirection.Output));
ExecuteNonQuery(new StoredCommand("InsertProduct"), parameterCollection);
product.Id = parameterCollection["product_id"].Value.To<int>();
return product;
}
}
}
For more details and examples, please refer to the official documentation..
License
This project is licensed under the MIT License - see the LICENSE file for details.
Product | Versions Compatible and additional computed target framework versions. |
---|---|
.NET | net8.0 is compatible. net8.0-android was computed. net8.0-browser was computed. net8.0-ios was computed. net8.0-maccatalyst was computed. net8.0-macos was computed. net8.0-tvos was computed. net8.0-windows was computed. net9.0 is compatible. |
-
net8.0
- VersaTul.Configurations (>= 1.0.16)
- VersaTul.Data.Contracts (>= 1.0.13)
- VersaTul.Utilities (>= 1.0.15)
-
net9.0
- VersaTul.Configurations (>= 1.0.16)
- VersaTul.Data.Contracts (>= 1.0.13)
- VersaTul.Utilities (>= 1.0.15)
NuGet packages (1)
Showing the top 1 NuGet packages that depend on VersaTul.Data.Sql:
Package | Downloads |
---|---|
VersaTul.Data.MsSql
The VersaTul Data MsSql project provides the ability to quickly create database access objects, usable on Microsoft SQL Server databases. This project is built on top of a combination of System.Data.Common and System.Data.SqlClient namespaces. These are used to provide the functionality to quickly call stored procedures or plain text sql queries, and map the result into data objects using the provided helper methods. The project also provides MsSql Bulk Copy functionality, which can be use to bulk insert data into a MsSQL Server databases. |
GitHub repositories
This package is not used by any popular GitHub repositories.
Version | Downloads | Last updated |
---|---|---|
2.0.19 | 39 | 12/20/2024 |
2.0.18 | 41 | 12/19/2024 |
2.0.17 | 86 | 12/16/2024 |
2.0.16 | 86 | 12/10/2024 |
2.0.15 | 138 | 12/6/2024 |
2.0.14 | 116 | 11/12/2024 |
2.0.13 | 149 | 8/31/2024 |
2.0.11 | 176 | 4/5/2024 |
2.0.10 | 137 | 4/4/2024 |
2.0.9 | 133 | 4/4/2024 |
2.0.8 | 163 | 3/1/2024 |
2.0.7 | 157 | 2/1/2024 |
2.0.6 | 180 | 1/15/2024 |
2.0.5 | 172 | 1/11/2024 |
2.0.4 | 210 | 11/13/2023 |
2.0.3 | 190 | 11/8/2023 |
2.0.2 | 154 | 11/8/2023 |
2.0.1 | 147 | 11/8/2023 |
1.0.15 | 153 | 11/2/2023 |
1.0.14 | 260 | 7/22/2023 |