Kull.Data 6.1.3

Kull.Data Database Access

This Library extends the .Net Standard DBA Classes like System.Data.Common.DbConnection or System.Data.Common.DbDataReader with some extension methods that makes them easiert to use. You can use this a very simple ORM Mapper as well, without the need of a full-bown Entity Framework. A good alternative is Dapper.

Install it using Nuget, Package "Kull.Data":

Some Examples

You always need to be using Kull.Data to use the extension methods.

Call a Stored Procedure

using (var con = Kull.Data.DatabaseUtils.GetConnectionFromConfig("SomeConfigConnstr"))
{
    return con.CreateSPCommand("spGetSomeData")
        .AddCommandParameter("NameOfParameter", 1)
        .AddCommandParameter("NameofOtherPArameter", 2)
        .AsCollectionOf<SomeClassName>() // As of v6, a C# 9 record can be used here
}

Call a Stored Procedure, but use default DataReader

System.Data.Common.DbCommand cmd = con.CreateSPCommand("spGetSomeData")
                    .AddCommandParameter("NameOfParameter", 1)
                    .AddCommandParameter("NameofOtherPArameter", 2);

using(var rdr = cmd.ExecuteReader())
{
    rdr.Read();
    return rdr.GetNInt16(2); // Use Kull.Data Extension method that handles null-values for you (No System.DBNull Checking anymore...)
}

Abstraction of Config

You can simple call Kull.Data.DatabaseUtils.GetConnectionFromConfig("NAMEOFCONNECTIONSTRING") and it will return your DbConnection.

It works in the following order:

• Look in System.Configuration.ConfigurationManager.ConnectionStrings (.Net Fx only)
• Look in System.Configuration.ConfigurationManager.AppSettings (.Net Fx only)
• Look in appsettings.json (.Net Core only)
• Look in Environment Variables

When looking into Environment Variables, the ones of Azure functions are the base. We support the following prefixes:

• No prefix, meaning just NAMEOFCONNECTIONSTRING in the example above
• SQLCONNSTR_
• SQLAZURECONNSTR_
• MYSQLCONNSTR_
• PostgreSQLCONNSTR_
• CUSTOMCONNSTR_

Other Feature: WrapperDataReader and ObjectDataReader

For SQL Bulk Copy or other things it can be useful to pass C# Data as a DataReader. You can achieve that by using Kull.Data.DataReader.ObjectDataReader. If you need to add some columns to a datareader, you can use Kull.Data.DataReader.WrappedDataReader.

Powershell Module

At first, I do not recommend Powershell for such stuff at all because of the following issue: https://github.com/PowerShell/PowerShellGet/issues/381 Prefer doing such stuff in C# directly. If you really want to, you can still use this Powershell module.

You can use the Kull.Data.Powershell Module to use some simple cmdlets that allow for querying stored procedures(queries with parameters easily.

Import-Module SqlServer # To load System.Data.SqlClient driver in Powershell 6.2+
Import-Module Kull.Data.Powershell 
$allAssemblies = [appdomain]::currentdomain.GetAssemblies() | ForEach-Object { [IO.Path]::GetFileName($_.Location) } # we try to be ready when SqlServer Module uses Microsoft.Data.SqlClient
if($allAssemblies.Contains("System.Data.SqlClient.dll") -and -not $allAssemblies.Contains("Microsoft.Data.SqlClient.dll")){
    $provider = "System.Data.SqlClient"
} else {
    $provider = "Microsoft.Data.SqlClient"
}
if($firstLoad -and $provider -eq "System.Data.SqlClient"){
    [System.Data.Common.DbProviderFactories]::RegisterFactory($provider, [System.Data.SqlClient.SqlClientFactory]::Instance)
}
else {
    [System.Data.Common.DbProviderFactories]::RegisterFactory($provider, [Microsoft.Data.SqlClient.SqlClientFactory]::Instance)
}
$sqlcon = Connect-Database $connectionstring $provider
Send-DbCommand $sqlcon $commandText $parameters ([System.Data.CommandType]::StoredProcedure)
# Or, use Receive-DbData to get result list

Run Tests in Docker Container

1. To create the image, execute the following command in the directory of the docker file

   docker build . -t sqlserver_sqltoolsservice --no-cache 
   

2. To execute the docker container, use the following command

   docker run -d -p 1433:1433 --name sqlserver sqlserver_sqltoolsservice 
   

3. Now you have a working SQL server for development and testing purpose. To run the test, connect to the container and navigate to the Kull.Data.Test and execute dotnet test