SDE 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.