SDE 1.0.2

(SDE) System.Data.Extensions

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

Db supported:

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

Samples

Query

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

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 queries

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 for multiple queries

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();
}

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
}

ExecuteNonQuery

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

Insert Many

using (var connection = new SqlConnection(ConnectionString))
{
    // rows affected = 2
    var 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" }
            });
}

ExecuteScalar

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

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;
}

With Async

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

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);                
}

Async

using (var connection = new SqlConnection(ConnectionString))
{
    var posts = await connection.QueryAsync<Post>("SELECT * FROM [Posts]");
    var post = await connection.QueryOneAsync<Post>("SELECT * FROM [Posts] WHERE PostId=@PostId", new { PostId = 1 });
}

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();

Or with ExecuteScalar

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

For unspecified object use Row

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

Output parameter with Stored procedure

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();

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

TableAttribute and ColumnAttribute (used by DefaultDataReaderMapper)

• Table Attribute allows to define the table name. Also the mapper tries to find a match to the type name (pluralized and singularized + ignore case)
• Column attribute allows to define the column name. Also the mapper tries to find a match to the property name.

Note: NpgsqlDbParameterBuilder (PostgreSql) uses the Column attribute to set NpgsqlDbType to Json and Xml. Avoid conversion errors with Npgsql library.

public class MyClass
{
    [Column(TypeName = "json")]
    public string MyJson { get; set; }

    [Column(TypeName = "xml")]
    public string MyXml { get; set; }
}

ColumnAnnotations

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

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

Alias

var results = connection.Query<Category>(@"Select Name as MyAlias from Categories", columnAnnotations: new ColumnAnnotations
{
    new ColumnAnnotation("MyAlias","Name","Categories",0),
}).ToList();

DbParameterBuilder

Allows to set up DbParameter(s) created for a param. A param is an anonymous object, an entity, etc.

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" }); 

2 types of DbParameterBuilders:

• By Provider
• By Type

Providers:

• "System.Data.SqlClient" or "Microsoft.Data.SqlClient": Sql Server
• "Microsoft.Data.Sqlite": Sqlite
• "MySql.Data.MySqlClient" or "MySqlConnector": MySql
• "Npgsql": PostgreSql
• DefaultDbParameterBuilder is used if no factory found for a provider.

Sample replace the DbParameterBuilder for the provider "Npgsql"

public class MyDbParameterBuilder : DbParameterBuilderBase
{
    protected override void SetUpDbParameterInternal(IDbDataParameter parameter, string parameterName, object value, PropertyInfo sourceProperty)
    {
        parameter.ParameterName = parameterName;
        parameter.Value = value; // caution: conversion required. For example: PostgreSql doesn't support uint
            
        // DbType, NpgsqlDbType, etc.
    }
}

Register

DbParameterBuilderRegistry.Default.Register("Npgsql", () => new MyDbParameterBuilder());

Create a DbParameterBuilder for a Type

Usefull for :

• Custom parameter configuration (example set the NpgsqlDbType to Json)
• Parameter nameless (OleDb)

Sample

public class SelectCustomerByCountryAndCityDbParameterBuilder : DbParameterBuilder<CustomerFilter>
{
    protected internal override void SetUpDbParameter(
        IDbDataParameter parameter, 
        string parameterName, 
        string originalParameterName, 
        int parameterIndex, 
        CustomerFilter customerFilter)
    {
        parameter.ParameterName = parameterName;

        // use parameter index
        if (parameterIndex == 0)
            parameter.Value = customerFilter.Country;
        else if (parameterIndex == 1)
            parameter.Value = customerFilter.City;
    }
}
public class CustomerFilter
{
    public string Country { get; set; }
    public string City { get; set; }
}

Register and use

DbParameterBuilderRegistry.Default.Register(typeof(CustomerFilter), () => new SelectCustomerByCountryAndCityDbParameterBuilder());

using (var connection = new OleDbConnection(ConnectionString))
{
    var customers = connection.Query<Customer>("select * from Customers where Country=? and City=?", new CustomerFilter { Country = "UK", City = "London" }).ToList();
}

DataReader Mapper

Uses the data reader to convert values to entity.

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

public class PostMapper : IDataReaderMapper<Post>
{
    public Post Map(IDataReader reader)
    {
        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;
    }
}

Register

DataReaderMapperRegistry.Default.Register<Post, PostMapper>();

Or directly

connection.Query<Post>("Select * from Posts", map: (reader) =>
{
    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;
});

Dependencies

Replace a dependency

Dependencies.Default.Replace<IPluralizer, MyPluralizer>();

• IPluralizer: HumanizerPluralizer by default. Allows to pluralize or singularize Entity Names to find table names.
• IValueToPropertyConverter: ValueToPropertyConverter by default. Allows to convert DataReader values to property types.
• IProviderResolver: ProviderResolver by default. Allows to resolve Providers.
• IQueryParameterFinder: QueryParameterFinder by default. Allows to find parameters in sql
• ISqlQuerySplitter: SqlQuerySplitter by default. Allows to split sql into queries (split on ";" and "GO") and clean queries.
• IInstanceFactory: Service used by DefaultDataReaderMapper to create instances ... ReflectionInstanceFactory by default.
• IPropertySetProvider: Service used by DefaultDataReaderMapper to set property value ... ReflectionPropertySetProvider by default.

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.