HIC.FAnsiSql 3.2.7

dotnet add package HIC.FAnsiSql --version 3.2.7                
NuGet\Install-Package HIC.FAnsiSql -Version 3.2.7                
This command is intended to be used within the Package Manager Console in Visual Studio, as it uses the NuGet module's version of Install-Package.
<PackageReference Include="HIC.FAnsiSql" Version="3.2.7" />                
For projects that support PackageReference, copy this XML node into the project file to reference the package.
paket add HIC.FAnsiSql --version 3.2.7                
#r "nuget: HIC.FAnsiSql, 3.2.7"                
#r directive can be used in F# Interactive and Polyglot Notebooks. Copy this into the interactive tool or source code of the script to reference the package.
// Install HIC.FAnsiSql as a Cake Addin
#addin nuget:?package=HIC.FAnsiSql&version=3.2.7

// Install HIC.FAnsiSql as a Cake Tool
#tool nuget:?package=HIC.FAnsiSql&version=3.2.7                

FAnsiSql

Build and test CodeQL NuGet Badge

<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 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. 
Compatible target framework(s)
Included target framework(s) (in package)
Learn more about Target Frameworks and .NET Standard.

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 3,789 10/17/2024
3.2.6 5,944 7/16/2024
3.2.5 2,424 6/7/2024
3.2.4 141 6/5/2024
3.2.3 1,567 5/22/2024
3.2.2 2,493 3/13/2024
3.2.1 681 3/11/2024
3.2.0 339 3/5/2024
3.1.1 9,939 9/6/2023
3.1.0 2,578 5/30/2023
3.0.1 16,647 10/28/2022
3.0.0 11,151 8/29/2022
2.0.5 2,297 8/23/2022
2.0.4 21,879 4/21/2022
2.0.3 11,573 2/22/2022
2.0.2 3,955 2/3/2022
2.0.1 24,118 7/27/2021
1.0.7 4,097 5/18/2021
1.0.6 15,314 9/16/2020
1.0.5 9,845 8/13/2020
1.0.4 1,342 8/10/2020
1.0.3 1,291 8/6/2020
1.0.2 4,404 7/7/2020
1.0.1 1,356 7/7/2020
0.11.1 14,531 2/27/2020
0.11.0 1,366 2/27/2020
0.10.13 7,614 11/25/2019
0.10.12 7,459 11/19/2019
0.10.11 1,406 11/18/2019
0.10.10 3,191 11/7/2019
0.10.9 1,470 11/4/2019
0.10.8 1,304 11/4/2019
0.10.7 3,185 9/30/2019
0.10.6 2,659 9/16/2019
0.10.5 1,384 9/16/2019
0.10.4 5,335 9/11/2019
0.10.3 1,426 9/10/2019
0.10.2 1,373 9/5/2019
0.10.1 1,374 9/5/2019
0.10.0 1,551 8/30/2019
0.9.8 1,409 8/26/2019
0.9.7 1,345 8/20/2019
0.9.6 1,432 8/9/2019
0.9.5 1,415 8/8/2019
0.9.4 3,051 7/29/2019
0.9.3 1,459 7/19/2019
0.9.2 3,353 7/4/2019
0.9.1.10 3,444 5/28/2019
0.9.1.9 1,458 5/28/2019
0.9.1.8 2,192 5/22/2019
0.9.1.7 1,458 5/21/2019
0.9.1.6 1,439 5/20/2019
0.9.1.5 1,421 5/17/2019
0.9.1.4 1,479 5/16/2019
0.9.1.3 2,630 5/14/2019
0.9.1.1 5,299 5/7/2019
0.9.0.10 1,431 4/12/2019
0.9.0.9 1,389 4/10/2019
0.9.0.8 1,455 4/10/2019
0.9.0.7 1,372 4/10/2019
0.9.0.6 1,437 4/10/2019
0.9.0.5 1,595 1/18/2019
0.9.0.4 1,503 1/18/2019
0.9.0.3 1,353 1/11/2019
0.9.0.2 1,310 1/11/2019
0.9.0.1 13,883 1/10/2019