HIC.FAnsiSql
3.2.6
See the version list below for details.
dotnet add package HIC.FAnsiSql --version 3.2.6
NuGet\Install-Package HIC.FAnsiSql -Version 3.2.6
<PackageReference Include="HIC.FAnsiSql" Version="3.2.6" />
paket add HIC.FAnsiSql --version 3.2.6
#r "nuget: HIC.FAnsiSql, 3.2.6"
// Install HIC.FAnsiSql as a Cake Addin #addin nuget:?package=HIC.FAnsiSql&version=3.2.6 // Install HIC.FAnsiSql as a Cake Tool #tool nuget:?package=HIC.FAnsiSql&version=3.2.6
FAnsiSql
<p align="right"> <a href="https://www.publicdomainpictures.net/en/view-image.php?image=184699&picture=a-laugh-every-day-126"> <img src="FansiHammerSmall.png" align="right"/> </a> </p>
Ever had difficulty getting a DataTable into a database? Maybe the dates are going in as strings or some clever dude put spaces in the middle of column names? FAnsiSql has you covered:
//Some data we want to load
var dt = new DataTable();
dt.Columns.Add("Name");
dt.Columns.Add("Date of Birth");
dt.Rows.Add("Frank \"The Boss\" Spagetti","1920-01-01");
dt.Rows.Add("Pete Mudarillo","22-May-1910");
//Load the DBMS implementation(s) you need
ImplementationManager.Load<MicrosoftSQLImplementation>();
//Get management object for the database
var server = new DiscoveredServer(
@"server=localhost\sqlexpress;Trusted_Connection=True;",
DatabaseType.MicrosoftSQLServer);
var database = server.ExpectDatabase("test");
var table = database.ExpectTable("MyTable");
//Throw out whatever was there before
if(table.Exists())
table.Drop();
//Create the table
database.CreateTable("MyTable",dt);
//Database types are compatible with all the data
Assert.AreEqual("datetime2",
table.DiscoverColumn("Date of Birth").DataType.SQLType);
Assert.AreEqual("varchar(25)",
table.DiscoverColumn("Name").DataType.SQLType);
//And the (string) data is now properly typed and sat in our DBMS
Assert.AreEqual(2,table.GetRowCount());
Assert.AreEqual(new DateTime(1920,1,1),
table.GetDataTable().Rows[0][1]);
Assert.AreEqual(new DateTime(1910,5,22),
table.GetDataTable().Rows[1][1]);
FAnsi Sql! it's like a budget version of SMO (that works cross platform - Sql Server, MySql, Postgres and Oracle). It supports:
- Table Creation
- Assigning types to untyped (string) data
- Bulk Insert
- DDL operations (Create database, drop database etc)
- Discovery (Does table exist?, what columns are in table? etc)
- Query writting assistance (e.g. TOP X)
It is not an ORM, it deals only in value type data (Strings, System.DataTable
, Value Types, SQL etc).
Install
FAnsi Sql is a nuget package. You can install it using either using the package manager:
PM> Install-Package HIC.FansiSql
Or .NET CLI Console:
> dotnet add package HIC.FansiSql
Feature Completeness
Most features are implemented across all 4 DBMS, you can find a breakdown of progress here:
Implementations are defined in separate assemblies (e.g. FAnsi.Implementations.MicrosoftSQL.dll) to allow for future expansion. Each implementation uses it's own backing library (e.g. ODP.net for Oracle). Implementations are loaded using Managed Extensibility Framework.
Why is it useful?
FAnsiSql is a database management/ETL library that allows you to perform common SQL operations without having to know which Database Management System (DBMS) you are targetting (e.g. Sql Server, My Sql, Oracle).
Consider writing an SQL create table command:
CREATE TABLE table_name (
column1 datatype,
column2 datatype,
column3 datatype,
....
);
If we wanted to target Microsoft Sql Server we might write something like:
CREATE TABLE [FAnsiTests].[dbo].[MyTable]
(
Name varchar(10) NULL,
DateOfBirth datetime2 NULL,
);
The same code on MySql would be:
CREATE TABLE `FAnsiTests`.`MyTable`
(
`Name` varchar(5) NULL ,
`DateOfBirth` datetime NULL
);
We have to change the table qualifier, we don't specify schema (dbo) and even the data types are different. The more advanced the feature, the more disparate the varied the implementations are (e.g. TOP X, UPDATE from JOIN etc).
The goal of FAnsiSql is to abstract away cross DBMS differences and streamline common tasks while still allowing you to harness the power of executing raw SQL commands.
Example
Imagine we have a System.DataTable
in memory and we want to store it in a database with an appropriate schema.
//Create some test data
DataTable dt = new DataTable();
dt.Columns.Add("Name");
dt.Columns.Add("DateOfBirth");
dt.Rows.Add("Frank","2001-01-01");
dt.Rows.Add("Dave", "2001-01-01");
//Load implementation assemblies that are relevant to your application (do this once on startup)
ImplementationManager.Load(
typeof(FAnsi.Implementations.MicrosoftSQL.MicrosoftSQLImplementation).Assembly,
typeof(FAnsi.Implementations.MySql.MySqlImplementation).Assembly);
//Create a server object
var server = new DiscoveredServer(@"server=localhost\sqlexpress;Trusted_Connection=True;", DatabaseType.MicrosoftSQLServer);
//Find the database
var database = server.ExpectDatabase("FAnsiTests");
//Or create it
if(!database.Exists())
database.Create();
//Create a table that can store the data in dt
var table = database.CreateTable("MyTable", dt);
//Table has 2 rows in it
Console.WriteLine("Table {0} has {1} rows" ,table.GetFullyQualifiedName(), table.GetRowCount());
Console.WriteLine("Column Name is of type {0}", table.DiscoverColumn("Name").DataType.SQLType);
Console.WriteLine("Column DateOfBirth is of type {0}", table.DiscoverColumn("DateOfBirth").DataType.SQLType);
//Drop the table afterwards
table.Drop();
This will output the following:
Table [FAnsiTests]..[MyTable] has 2 rows
Column Name is of type varchar(5)
Column DateOfBirth is of type datetime2
We can get the same code to execute against a MySql server by changing only the connection string line:
var server = new DiscoveredServer(@"Server=localhost;Uid=root;Pwd=***;SSLMode=None", DatabaseType.MySql);
In this case we get the following output:
Table `FAnsiTests`.`MyTable` has 2 rows
Column Name is of type varchar(5)
Column DateOfBirth is of type datetime
We can still execute raw ANSI Sql against the table
using (DbConnection con = server.GetConnection())
{
con.Open();
using(DbCommand cmd = server.GetCommand("Select * from " + table.GetFullyQualifiedName(), con))
using(DbDataReader r = cmd.ExecuteReader())
while (r.Read())
Console.WriteLine(string.Join(",", r["Name"],r["DateOfBirth"]));
}
Building
Building requires MSBuild 15 or later (or Visual Studio 2017 or later). You will also need to install the DotNetCore 2.2 SDK.
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. |
-
net8.0
- HIC.TypeGuesser (>= 1.2.6)
- Microsoft.Data.SqlClient (>= 5.2.1)
- MySqlConnector (>= 2.3.7)
- Npgsql (>= 8.0.3)
- Oracle.ManagedDataAccess.Core (>= 23.5.0)
- System.Linq.Async (>= 6.0.1)
NuGet packages (3)
Showing the top 3 NuGet packages that depend on HIC.FAnsiSql:
Package | Downloads |
---|---|
HIC.RDMP.Plugin
Core package for plugin development |
|
HIC.DicomTypeTranslation
Extension library for FoDicom supporting flexible relational database schemas for storing large Dicom imaging datasets. Also supports persisting Dicom tag data to MongoDb |
|
IsIdentifiable
Library for spotting identifiable data in flat files, dicom files and relational databases (Sql Server, MySql, Oracle). |
GitHub repositories
This package is not used by any popular GitHub repositories.
Version | Downloads | Last updated |
---|---|---|
3.2.7 | 1,100 | 10/17/2024 |
3.2.6 | 5,824 | 7/16/2024 |
3.2.5 | 2,361 | 6/7/2024 |
3.2.4 | 133 | 6/5/2024 |
3.2.3 | 1,559 | 5/22/2024 |
3.2.2 | 2,487 | 3/13/2024 |
3.2.1 | 673 | 3/11/2024 |
3.2.0 | 333 | 3/5/2024 |
3.1.1 | 9,931 | 9/6/2023 |
3.1.0 | 2,570 | 5/30/2023 |
3.0.1 | 16,637 | 10/28/2022 |
3.0.0 | 11,125 | 8/29/2022 |
2.0.5 | 2,292 | 8/23/2022 |
2.0.4 | 21,874 | 4/21/2022 |
2.0.3 | 11,568 | 2/22/2022 |
2.0.2 | 3,950 | 2/3/2022 |
2.0.1 | 24,113 | 7/27/2021 |
1.0.7 | 4,090 | 5/18/2021 |
1.0.6 | 15,305 | 9/16/2020 |
1.0.5 | 9,838 | 8/13/2020 |
1.0.4 | 1,333 | 8/10/2020 |
1.0.3 | 1,284 | 8/6/2020 |
1.0.2 | 4,395 | 7/7/2020 |
1.0.1 | 1,347 | 7/7/2020 |
0.11.1 | 14,524 | 2/27/2020 |
0.11.0 | 1,357 | 2/27/2020 |
0.10.13 | 7,607 | 11/25/2019 |
0.10.12 | 7,452 | 11/19/2019 |
0.10.11 | 1,399 | 11/18/2019 |
0.10.10 | 3,184 | 11/7/2019 |
0.10.9 | 1,463 | 11/4/2019 |
0.10.8 | 1,297 | 11/4/2019 |
0.10.7 | 3,178 | 9/30/2019 |
0.10.6 | 2,650 | 9/16/2019 |
0.10.5 | 1,375 | 9/16/2019 |
0.10.4 | 5,328 | 9/11/2019 |
0.10.3 | 1,419 | 9/10/2019 |
0.10.2 | 1,366 | 9/5/2019 |
0.10.1 | 1,367 | 9/5/2019 |
0.10.0 | 1,544 | 8/30/2019 |
0.9.8 | 1,402 | 8/26/2019 |
0.9.7 | 1,337 | 8/20/2019 |
0.9.6 | 1,425 | 8/9/2019 |
0.9.5 | 1,408 | 8/8/2019 |
0.9.4 | 3,043 | 7/29/2019 |
0.9.3 | 1,450 | 7/19/2019 |
0.9.2 | 3,347 | 7/4/2019 |
0.9.1.10 | 3,437 | 5/28/2019 |
0.9.1.9 | 1,451 | 5/28/2019 |
0.9.1.8 | 2,185 | 5/22/2019 |
0.9.1.7 | 1,451 | 5/21/2019 |
0.9.1.6 | 1,431 | 5/20/2019 |
0.9.1.5 | 1,412 | 5/17/2019 |
0.9.1.4 | 1,472 | 5/16/2019 |
0.9.1.3 | 2,623 | 5/14/2019 |
0.9.1.1 | 5,292 | 5/7/2019 |
0.9.0.10 | 1,424 | 4/12/2019 |
0.9.0.9 | 1,380 | 4/10/2019 |
0.9.0.8 | 1,448 | 4/10/2019 |
0.9.0.7 | 1,365 | 4/10/2019 |
0.9.0.6 | 1,430 | 4/10/2019 |
0.9.0.5 | 1,588 | 1/18/2019 |
0.9.0.4 | 1,496 | 1/18/2019 |
0.9.0.3 | 1,344 | 1/11/2019 |
0.9.0.2 | 1,303 | 1/11/2019 |
0.9.0.1 | 13,876 | 1/10/2019 |