SDE 1.1.3

dotnet add package SDE --version 1.1.3
NuGet\Install-Package SDE -Version 1.1.3
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="SDE" Version="1.1.3" />
For projects that support PackageReference, copy this XML node into the project file to reference the package.
paket add SDE --version 1.1.3
#r "nuget: SDE, 1.1.3"
#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 SDE as a Cake Addin
#addin nuget:?package=SDE&version=1.1.3

// Install SDE as a Cake Tool
#tool nuget:?package=SDE&version=1.1.3

SDE (System.Data.Extensions)

Provides extensions methods to code faster.

Methods:

  • CreateCommand
  • CreateStoredProcCommand
  • CreateParameter
  • AddInParameter
  • AddOutParameter
  • AddParameter
  • ExecuteNonQuery
  • ExecuteScalar
  • Query
  • QueryOne
  • QueryMultiple
  • CleanSql: remove "Go" delimiter from queries. Avoid fail on execution
  • Open: Shortcut for fluent code

Async

  • ExecuteNonQueryAsync
  • ExecuteScalarAsync
  • QueryAsync
  • QueryOneAsync
  • QueryMultipleAsync
  • OpenAsync

DataReader methods

  • GetNames
  • GetValueOrDefault
  • GetValueAs
  • GetValueOrDefaultAs
  • GetValueTo
  • GetValueOrDefaultTo

Db supported:

  • Sql Server
  • Sqlite
  • MySql
  • PostgreSql
  • And more

Query (and QueryAsync)

using (var connection = new SqlConnection(ConnectionString))
{
    var products = connection.Query<Product>("SELECT * FROM [Products]").ToList();
}

Async

using (var connection = new SqlConnection(ConnectionString))
{
    var products = await connection.QueryAsync<Post>("SELECT * FROM [Products]");
}

List of values (works also with TypeMappers)

var productNames = connection.Query<string>("Select [ProductName] from [Products]").ToList();

Single result

using (var connection = new SqlConnection(ConnectionString))
{
    var products = connection.Query<Product>("SELECT top 1 * FROM [Products]").Single();
}

Custom Mapper

public class Post
{
    public int PostId { get; set; }
    public string Title { get; set; }
    // etc.
}

public class PostMapper : IDataReaderMapper<Post>
{
    public Post Map(IDataReader reader, ColumnMappings columnMappings)
    {
        var post = new Post();
        for (int i = 0; i < reader.FieldCount; i++)
        {
            var name = reader.GetName(i);
            if (name == "PostId")
                post.PostId = reader.GetInt32(i);
            else if (name == "Title")
                post.Title = reader.IsDBNull(i) ? null : reader.GetString(i);
        }
        return post;
    }
}

and use

var mapper = new PostMapper();
var posts = connection.Query<Post>("Select * from Posts", map: mapper.Map).ToList();

Or directly

var posts = connection.Query<Post>("Select * from Posts", map: (reader, columnMappings) =>
{
    var post = new Post();
    for (int i = 0; i < reader.FieldCount; i++)
    {
        var name = reader.GetName(i);
        if (name == "PostId")
            post.PostId = reader.GetInt32(i);
        else if (name == "Title")
            post.Title = reader.IsDBNull(i) ? null : reader.GetString(i);
    }
    return post;
});

Query Many

using (var connection = new SqlConnection(ConnectionString))
{
    var products = connection.Query<Product>("SELECT * FROM [Products] WHERE [CategoryID]=@CategoryID",
        new[]
        {
            new { CategoryID = 1 },
            new { CategoryID = 2 }
        }).ToList();
}

Multiple result sets

using (var connection = new SqlConnection(ConnectionString))
{
    var products = connection.Query<Product>("SELECT * FROM [Products] WHERE [ProductID]=@ProductID;SELECT * FROM [Products] WHERE [ProductID]=@ProductID2",
        new dynamic[]
        {
            new { ProductID = 1 },
            new { ProductID2 = 2 }
        }).ToList();
}

Tip: single parameter

var products = connection.Query<Product>("SELECT * FROM [Products] WHERE [ProductID]=@ProductID;SELECT * FROM [Products] WHERE [ProductID]=@ProductID2",
    new { ProductID = 1, ProductID2 = 2 }).ToList();

QueryMultiple (and QueryMultipleAsync)

using (var connection = new SqlConnection(ConnectionString))
{
    var results = connection.QueryMultiple("SELECT * FROM [Categories];SELECT * FROM [Products]");

    var categories = results.Read<Category>().ToList(); // or ReadOne
    var products = results.Read<Product>().ToList();
}

Relations

  • Foreign Key
using (var connection = new SqlConnection(ConnectionString))
{
    var products = connection.Query<Product>("SELECT * FROM [Products]").ToList();
    foreach (var product in products)
    {
        var category = connection.QueryOne<Category>("SELECT * FROM [Categories] WHERE [CategoryId]=@CategoryId", new { CategoryId = product.CategoryId });
        product.Category = category;
    }
}

Or only 1 query

var connection = new SqlConnection(ConnectionString);
var products = connection.Query<Product, Category, Product>(@"select * from Products p inner join Categories c on p.CategoryId = c.CategoryId", (product, category) =>
{
    product.Category = category; // navigation property
    return product;
}).ToList();
  • Many relation
var connection = new SqlConnection(ConnectionString);
var rows = connection.Query<Category, Product, Category>(@"select * from Categories c inner join Products p on p.CategoryId = c.CategoryId", (category, product) =>
{
    category.Products.Add(product); // navigation property

    return category;
}).ToList();

// group by CategoryId
var categories = new List<Category>();
foreach (var row in rows)
{
    // each row has one product
    var category = categories.FirstOrDefault(x => x.CategoryId == row.CategoryId);
    if(category != null)
    {
        // append row's product to category
        category.Products.AddRange(row.Products);
    }
    else
        categories.Add(row);                
}

And more ...

Get the new id with Query

var sql = "INSERT INTO [Posts]([Title],[Content]) VALUES (@Title,@Content);SELECT CAST(SCOPE_IDENTITY() as int)";
var id = connection.Query<int>(sql, new Post { Title = "First Article", Content = "First Content" }).Single();

For unspecified object use Row

var rows = connection.Query<Row>("select * from Posts; select * from Categories").ToList();

QueryOne

QueryOne with parameter

using (var connection = new SqlConnection(ConnectionString))
{
    var product = connection.QueryOne<Product>(@"SELECT * FROM [Products] WHERE [ProductID]=@ProductID", new { ProductID = 1 }); // anonymous object
}

Async

using (var connection = new SqlConnection(ConnectionString))
{
    var product = await connection.QueryOneAsync<Product>(@"SELECT * FROM [Products] WHERE [ProductID]=@ProductID", new { ProductID = 1 }); 
}

Single value (works also with TypeMappers)

var userName = connection.QueryOne<string>("Select top 1 UserName from Users");

Param

Anonymous object

var product = connection.QueryOne<Product>(@"SELECT * FROM [Products] WHERE [ProductID]=@ProductID", new { ProductID = 1 });

Entity

connection.ExecuteNonQuery("INSERT INTO [Posts]([Title],[Content]) VALUES (@Title,@Content)", new Post { Title = "First Article", Content = "First Content" }); 

DbParameters collection (allows to configure parameters)

// sample with PostgreSql 
connection.ExecuteNonQuery("INSERT INTO \"MyTable\"(\"MyJson\")VALUES(@MyJson)", new DbParameters
{
    new NpgsqlParameter ("MyJson", NpgsqlDbType.Json){ Value = "{\"sample\":\"value\"}" }
});

TypeHandler

TypeHandler is used to convert a param to a db parameter for custom types (Geometry for example) or types supported/not supported by the SGBD (DateTimeOffset, uint, etc.).

Example sbyte with PostgreSql

public class NpgsqlSByteTypeHandler : TypeHandler<sbyte>
{
    // param => db parameter value
    public override void SetValue(IDbDataParameter parameter, sbyte value)
    {
        parameter.Value = (int)value;
    }
}

Register

SDECore.DbCommandBuilder.AddOrUpdateTypeHandler<sbyte>(new NpgsqlSByteTypeHandler());

TypeMapper

TypeMapper is used to convert DataReader value to property value. The default datareader mapper converts a lot of values, so the TypeMapper is not always required.

Sample with JArray (Json.Net)

var connection = new SqlConnection(Constants.ConnectionStringTypes);
connection.ExecuteNonQuery("CREATE TABLE dbo.[Colors]([Id] INT NOT NULL, [ColorName] NVARCHAR(50) NOT NULL)");

// write 
SDECore.DbCommandBuilder.AddOrUpdateTypeHandler<JArray>(new JArrayTypeHandler());
var colors = new JArray() { "Blue", "Red", "Green" };
connection.ExecuteNonQuery("INSERT INTO dbo.[Colors]([Id],[ColorName]) select @Id,[value] from openjson(@Colors)", new { Id = 1, Colors = colors });

// read
SDECore.DefaultMapper.AddOrUpdateTypeMapper<JArray>(new JArrayTypeMapper());
var results = connection.Query<JArray>("SELECT JSON_QUERY(REPLACE(REPLACE((SELECT [ColorName] FROM dbo.[Colors] FOR JSON PATH), '{\"ColorName\":', ''), '}', ''))").ToList();

TypeHandler and TypeMapper

public class JArrayTypeHandler : TypeHandler<JArray>
{
    public override void SetValue(IDbDataParameter parameter, JArray value)
    {
        parameter.Value = value.ToString();
    }
}

public class JArrayTypeMapper : TypeMappper<JArray>
{
    public override JArray Map(IDataReader reader, int? index)
    {
        var fieldValue = reader.GetString(index.Value);
        return JArray.Parse(fieldValue);
    }
}

ColumnMappings

Avoids conflicts for tables with same column names, allows to resolve columns with aliases and target properties.

var results = connection.Query<Row>(@"Select ProductId, t1.Name, CategoryId, t2.Name from Products t1, Categories t2", columnMappings: new ColumnMappings
{
    new ColumnMapping("Name","Products",1),
    new ColumnMapping("Name","Categories",3)
}).ToList();

Alias

var results = connection.Query<Category>(@"Select Name as MyAlias from Categories", columnMappings: new ColumnMappings
{
    new ColumnMapping("MyAlias","Name", nameof(Category),0)
}).ToList();

Mapping

var results = connection.Query<Category>(@"Select Name from Categories", columnMappings: new ColumnMappings
{
    new ColumnMapping("Name","CategoryName", nameof(Category),0)
}).ToList();

Or with Generic version (less performant)

var results = connection.Query<Category>(@"Select Name from Categories", columnMappings: new ColumnMappings
{
    new ColumnMapping<Category>("Name", x => x.CategoryName, 0)
}).ToList();
public class Category
{
    public string CategoryName { get; set; }
}

Or with Column Attribute (and ColumnMappings not used)

public class Category
{
    [Column("Name")]
    public string CategoryName { get; set; }
}

Positional parameters "?"

SDE replaces paremeters like ?Title? by ?

Sample

using (var connection = new OleDbConnection(ConnectionString))
{
    connection.ExecuteNonQuery("INSERT INTO [Posts]([Title],[Content]) VALUES (?Title?,?Content?)", new Post { Title = "First Article", Content = "First Content" }); 
}
// sql sent: INSERT INTO [Posts]([Title],[Content]) VALUES (?,?)

It's possible to tell to SDE explicitly that we use Positional Parameters (Auto detected for System.Data.OleDb)

SDECore.DbCommandBuilder.IsUsingPositionalParameters = true;

Default Mapper

It's possible to replace the default DataReader Mapper. Sample with AutoMapper.Data

public class AutoMapperDefaultMapper : DefaultMapper
{
    private readonly Mapper _mapper;

    public AutoMapperDefaultMapper()
    {
        var configuration = new MapperConfiguration(cfg =>
        {
            cfg.AddDataReaderMapping(true);
            cfg.CreateMap<IDataRecord, Customer>();
        });
        _mapper = new Mapper(configuration);
    }

    public override T Map<T>(IDataReader reader, ColumnMappings columnMappings)
    {
        return _mapper.Map<T>(reader);
    }
}

And change the default mapper

SDECore.DefaultMapper = new AutoMapperDefaultMapper();

ExecuteScalar (and ExecuteScalarAsync)

using (var connection = new SqlConnection(ConnectionString))
{
    var count = (int)connection.ExecuteScalar("SELECT COUNT(*) FROM [Posts]"); 
}

Async

using (var connection = new SqlConnection(ConnectionString))
{
    var count = (int)await connection.ExecuteScalarAsync("SELECT COUNT(*) FROM [Posts]"); 
}

Get the new id

var sql = "INSERT INTO [Posts]([Title],[Content]) output inserted.PostId VALUES (@Title,@Content);SELECT CAST(SCOPE_IDENTITY() as int)";
int id = (int)connection.ExecuteScalar(sql, new Post { Title = "First Article", Content = "First Content" });

ExecuteNonQuery (and ExecuteNonQueryAsync)

using (var connection = new SqlConnection(ConnectionString))
{
    int rowsAffected = connection.ExecuteNonQuery("INSERT INTO [Posts]([Title],[Content]) VALUES (@Title,@Content)", new Post { Title = "My Article", Content = "My Content" }); 
}

Async

using (var connection = new SqlConnection(ConnectionString))
{
    int rowsAffected = await connection.ExecuteNonQueryAsync("INSERT INTO [Posts]([Title],[Content]) VALUES (@Title,@Content)", new Post { Title = "My Article", Content = "My Content" }); 
}

Insert Many

using (var connection = new SqlConnection(ConnectionString))
{
    // rows affected = 2
    int rowsAffected = connection.ExecuteNonQuery(@"INSERT INTO [Posts]([Title],[Content]) VALUES (@Title,@Content)",
            new[]
            {
                new { Title = "Article A", Content = "Content A" },
                new { Title = "Article B", Content = "Content B" }
            });
}

Transactions

With TransactionScope

try
{
    using (var scope = new TransactionScope())
    {
        using (var connection = new SqlConnection(ConnectionString))
        {
            connection.ExecuteNonQuery("CREATE TABLE [Posts]([PostId] INT NOT NULL PRIMARY KEY IDENTITY(1,1),[Title] NVARCHAR(MAX),Content NTEXT)");
            connection.ExecuteNonQuery("INSERT INTO [Posts]([Title],[Content]) VALUES (@Title,@Content)", new Post { Title = "First Article", Content = "First Content" }); // entity

            scope.Complete();
        }
    }
}
catch (Exception ex)
{
    throw;
}

Async

using (var scope = new TransactionScope(TransactionScopeAsyncFlowOption.Enabled))
// ect.

Stored Procedures

Output parameter

var connection = new SqlConnection(ConnectionString);

connection.ExecuteNonQuery(@"
    CREATE PROC usp_AddUser
    (
        @UserName nvarchar(150),
        @UserId int OUTPUT
    )
    AS
    BEGIN
        INSERT INTO Users(UserName) VALUES(@UserName);
        SET @UserId = SCOPE_IDENTITY();
    END
");

var command = connection.CreateStoredProcCommand("usp_AddUser")
    .AddInParameter("UserName", "Brad")
    .AddOutParameter("UserId", dbType: DbType.Int32);

command.Open().ExecuteNonQuery();

int userId = ((IDbDataParameter)command.Parameters["UserId"]).Value;

Or with DbParameters collection

var parameters = new DbParameters
{
    new SqlParameter("@UserName", "Brad"),
    new SqlParameter
    {
        ParameterName = "@UserId",
        DbType = DbType.Int32,
        Direction = ParameterDirection.Output
    }
};

connection.ExecuteNonQuery("usp_AddUser", parameters, commandType: CommandType.StoredProcedure);

int userId = parameters.Get<int>("UserId"); 

Interception

SDEProxy. Intercept IDbConnection, IDbCommand, etc.

public class Sample
{
    private const string ConnectionString = "Server=(localdb)\\MSSQLLocalDB;Database=Northwind;Trusted_Connection=True;MultipleActiveResultSets=true;";

    public void Run()
    {
        using (var connection = new SqlConnection(ConnectionString))
        {
            var command = connection.CreateCommand();
            command.CommandText = "SELECT COUNT(*) FROM [Customers] WHERE [Country]=@Country";
            command.Parameters.Add(new SqlParameter("Country", "France"));

            var proxy = SDEProxy<IDbCommand>.CreateProxy(command, BeforeInvoke, AfterInvoke, OnFailed);

            connection.Open();

            int result = (int)command.ExecuteScalar();
            Console.WriteLine("result: " + result);

            int proxyResult = (int)proxy.ExecuteScalar();
            Console.WriteLine("proxy result: " + proxyResult);
        }
    }

    private void BeforeInvoke(MethodInfo method, object[] parameters, IDbCommand command)
    {
        // update command parameter value
        int index = command.Parameters.IndexOf("Country");
        if (index != -1)
            ((IDataParameter)command.Parameters[index]).Value = "UK";
    }

    private void AfterInvoke(MethodInfo method, object[] parameters, IDbCommand command, object result) { }
    private void OnFailed(MethodInfo method, object[] parameters, IDbCommand command, Exception ex) { }
}

Or use a library like PostSharp, Fody, NIntercept, Castle, etc.

Or use visitor or proxy pattern to create a custom connections, commands, etc.

Repository Pattern

Sample

public interface IEmployeeRepository
{
    Employee Find(int id);
    List<Employee> GetAll();
    Employee Add(Employee employee);
    Employee Update(Employee employee);

    void Remove(int id);
    List<Employee> GetEmployeeWithCompany();
}

// we can create a Generic Repository base class, create a repository for Stored Procedures commands, create a repository with "extras" functions, etc.
public class EmployeeRepository : IEmployeeRepository
{
    private readonly DbConnection _db;

    public EmployeeRepository(IConfiguration configuration)
    {
        // we can use a single connection for all operations or a connection for each operation
        _db = new SqlConnection(configuration.GetConnectionString("DefaultConnection"));
    }

    public List<Employee> GetAll()
    {
        var sql = "SELECT * FROM Employees";
        return _db.Query<Employee>(sql).ToList();
    }

    public List<Employee> GetEmployeeWithCompany()
    {
        // relation
        var sql = "SELECT E.*,C.* FROM Employees AS E INNER JOIN Companies AS C ON E.CompanyId = C.CompanyId ";
        var employee = _db.Query<Employee, Company, Employee>(sql, (e, c) =>
        {
            e.Company = c;
            return e;
        });

        return employee.ToList();
    }

    public Employee Find(int id)
    {
        var sql = "SELECT * FROM Employees WHERE EmployeeId = @Id";
        return _db.Query<Employee>(sql, new { @Id = id }).Single(); // param ignore case and @ ignored
    }

    public Employee Add(Employee employee)
    {
        var sql = "INSERT INTO Employees (Name, Title, Email, Phone, CompanyId) VALUES(@Name, @Title, @Email, @Phone, @CompanyId);SELECT CAST(SCOPE_IDENTITY() as int); ";
        var id = _db.Query<int>(sql, employee).Single(); // get the new id
        employee.EmployeeId = id;
        return employee;
    }

    public Employee Update(Employee employee)
    {
        var sql = "UPDATE Employees SET Name = @Name, Title = @Title, Email = @Email, Phone = @Phone, CompanyId = @CompanyId WHERE EmployeeId = @EmployeeId";
        _db.ExecuteNonQuery(sql, employee);
        return employee;
    }

    public void Remove(int id)
    {
        var sql = "DELETE FROM Employees WHERE EmployeeId = @Id";
        _db.ExecuteNonQuery(sql, new { id });
    }
}

Migrations

CodeFirstDbGenerator: supports Sql Server, Sqlite, MySql, etc. Avoids installing Entity Framework.

Product Compatible and additional computed target framework versions.
.NET net5.0 is compatible.  net5.0-windows was computed.  net6.0 is compatible.  net6.0-android was computed.  net6.0-ios was computed.  net6.0-maccatalyst was computed.  net6.0-macos was computed.  net6.0-tvos was computed.  net6.0-windows was computed.  net7.0 was computed.  net7.0-android was computed.  net7.0-ios was computed.  net7.0-maccatalyst was computed.  net7.0-macos was computed.  net7.0-tvos was computed.  net7.0-windows was computed.  net8.0 was computed.  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. 
.NET Core netcoreapp2.0 was computed.  netcoreapp2.1 was computed.  netcoreapp2.2 was computed.  netcoreapp3.0 was computed.  netcoreapp3.1 was computed. 
.NET Standard netstandard2.0 is compatible.  netstandard2.1 was computed. 
.NET Framework net461 was computed.  net462 was computed.  net463 was computed.  net47 was computed.  net471 was computed.  net472 was computed.  net48 was computed.  net481 was computed. 
MonoAndroid monoandroid was computed. 
MonoMac monomac was computed. 
MonoTouch monotouch was computed. 
Tizen tizen40 was computed.  tizen60 was computed. 
Xamarin.iOS xamarinios was computed. 
Xamarin.Mac xamarinmac was computed. 
Xamarin.TVOS xamarintvos was computed. 
Xamarin.WatchOS xamarinwatchos was computed. 
Compatible target framework(s)
Included target framework(s) (in package)
Learn more about Target Frameworks and .NET Standard.

NuGet packages

This package is not used by any NuGet packages.

GitHub repositories

This package is not used by any popular GitHub repositories.

Version Downloads Last updated
1.1.3 450 3/14/2022
1.1.0 405 3/13/2022
1.0.2 416 2/27/2022
0.20.0 436 2/25/2022
0.19.0 424 2/24/2022
0.18.2 429 2/24/2022
0.17.2 416 2/22/2022
0.17.1 411 2/22/2022
0.15.0 396 2/19/2022