dapper.dib

#!csharp

#r "nuget:Microsoft.Extensions.Configuration"
#r "nuget:Microsoft.Extensions.Configuration.Json"
#r "nuget:Dapper"
#r "nuget:Dapper.Contrib"
#r "nuget:System.Data.SqlClient"
#r "System.IO"

#!csharp

using Dapper.Contrib.Extensions;

public class Address
{
    public int Id { get; set; }
    public int ContactId { get; set; }
    public string AddressType { get; set; }
    public string StreetAddress { get; set; }
    public string City { get; set; }
    public int StateId { get; set; }
    public string PostalCode { get; set; }

    internal bool IsNew => (this.Id == default(int));
    public bool IsDeleted { get; set; }
}

public class Contact
{
    public int Id { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public string Email { get; set; }
    public string Company { get; set; }
    public string Title { get; set; }

    [Computed]
    public bool IsNew => this.Id == default(int);

    [Write(false)]
    public List<Address> Addresses { get; } = new List<Address>();
}

#!csharp

using System.Data;
using System.Data.SqlClient;
using Dapper;

public interface IContactRepository
{
    Contact Find(int id);
    List<Contact> GetAll();
    Contact Add(Contact contact);
    Contact Update(Contact contact);
    void Remove(int id);
    Contact GetFullContact(int id);
    void Save(Contact contact);
    List<Contact> CustomQuery(string query);
}

public partial class ContactRepository : IContactRepository
{
    private IDbConnection db;

    public ContactRepository(string connStr)
    {
        db = new SqlConnection(connStr);
    }

    public Contact Add(Contact contact)
    {
        var sql = "INSERT INTO Contacts (FirstName, LastName, Email, Company, Title) VALUES(@FirstName, @LastName, @Email, @Company, @Title); SELECT CAST(SCOPE_IDENTITY() AS int);";
        var id = this.db.Query<int>(sql, contact).Single();
        contact.Id = id;
        return contact;
    }

    public Contact Find(int id)
    {
        // Second parameter is responsible for dynamic query parameters, it is an object containing properties that we want to use as query params
        return this.db.QueryFirst<Contact>("SELECT * FROM Contacts WHERE Id = @id", new { id });

        // we could just use db methods instead :)
        // return this.db.Get<Contact>(id);
    }

    public List<Contact> GetAll()
    {
        return this.db.Query<Contact>("SELECT * FROM Contacts").ToList();

        // we could just use db methods instead :)
        // return this.db.GetAll<Contact>().ToList();
    }

    public List<Contact> CustomQuery(string query)
    {
        return this.db.Query<Contact>(query).ToList();
    }

    public Contact GetFullContact(int id)
    {
        // QueryMultiple means we expect multiple result sets, e.g. we execute two SELECTs
        var sql = "SELECT * FROM Contacts WHERE Id = @Id; SELECT * FROM Addresses WHERE ContactId = @Id";

        using (var multipleResults = this.db.QueryMultiple(sql, new { Id = id }))
        {
            var contact = multipleResults.ReadSingleOrDefault<Contact>();
            var addresses = multipleResults.Read<Address>().ToList();

            if (contact != null && addresses != null)
            {
                contact.Addresses.AddRange(addresses);
            }
            return contact;
        }
    }

    public void Remove(int id)
    {
        this.db.Query<Contact>("DELETE FROM Contacts WHERE Id = @Id", new [] { id });
        // this.db.Delete<Contact>(new Contact() { Id = id });
    }

    public void Save(Contact contact)
    {
        throw new NotImplementedException();
    }

    public Contact Update(Contact contact)
    {
        // For running parametrized SQL we use Execute method
        // We define the @Parameters with coresponding values
        // And pass an object that contians those properties
        // this.db.Execute("UDPATE Contacts SET FirstName = @FirstName, LastName = @LastName, Email = @Email, Company = @Company, Title = @Title WHERE Id = @Id", contact);
        
        // we could just use db methods instead :)
        // Dapper.Contrib provides those methods
        this.db.Update<Contact>(contact);
        return contact;
    }

    public void AdvancedOperation()
    {
        // Transaction handling
        // We just need to begin transaction
        this.db.BeginTransaction();
        this.CustomQuery("SELECT 1 FROM Contacts");
        this.CustomQuery("SELECT 2 FROM Contacts");
        this.CustomQuery("SELECT 3 FROM Contacts");
    }
}

#!csharp

using System.IO;
IDbConnection db = new SqlConnection("Server=tcp:logicdb.database.windows.net,1433;Initial Catalog=logicdb;Persist Security Info=False;User ID=user2;Password=SuperSecret!;MultipleActiveResultSets=True;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;");

private IContactRepository CreateRepository()
{
    return new ContactRepository("Server=tcp:logicdb.database.windows.net,1433;Initial Catalog=logicdb;Persist Security Info=False;User ID=user2;Password=SuperSecret!;MultipleActiveResultSets=True;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;");
}

#!markdown

-- Adding new user to database
-- STEP 1
-- IN MASTER DB
CREATE LOGIN user2
WITH PASSWORD = 'SuperSecret!' ;
GO
------------------------------------
--  STEP 2 in your chosen DB, create a database User
--  NB Select your Database tree, then choose New Query
--  (Ensure your query is in your target DB )
------------------------------------
CREATE USER user2
FOR LOGIN user2
WITH DEFAULT_SCHEMA = dbo;
-- add user to roles in db
ALTER ROLE db_datareader ADD MEMBER user2;
ALTER ROLE db_datawriter ADD MEMBER user2;
GO

#!csharp

    var repository = CreateRepository();

    var results = repository.GetAll();

    Console.WriteLine($"{results.Count} contacts found");

#!csharp

// Let's say we have a model with properties that have different names than they do in database, we can just use aliases in our query
// public class Contact
// {
//     public int ID { get; set; }
//     public string First { get; set; }
//     public string Last { get; set; }
//     public string Mail { get; set; }
//     public string CompanyName { get; set; }
//     public string Header { get; set; }

//     [Computed]
//     public bool IsNew => this.ID == default(int);

//     [Write(false)]
//     public List<Address> Addresses { get; } = new List<Address>();
// }

// here we apply aliases
// var updatedQuery = repository.CustomQuery("SELECT Id ID, FirstName First, LastName Last, Email Mail, Company CompanyName, Title Header FROM Contacts");

// foreach (var contact in updatedQuery)
// {
//     Console.WriteLine($"[{contact.ID}, {contact.First}, {contact.Last}, {contact.Mail}, {contact.CompanyName}, {contact.Header}]");
// }

#!csharp

// Adding new item
// Just another query with @NamedParameters
var contact = new Contact()
{
    FirstName = "a",
    LastName = "b"

};

var createdContact = repository.Add(contact);

Console.Write($"{contact == createdContact}, ID: {createdContact.Id}");

#!csharp

// Getting existing item
var item = repository.Find(8);
Console.Write(item.FirstName);

#!csharp

// Updating
Console.WriteLine(item.FirstName);
item.FirstName = "John";
var updatedItem = repository.Update(item);
Console.Write(updatedItem.FirstName);

#!csharp

// Dapper.Contrib provides us with already implemented CRUD operations so we don't have to do it manually
// If it fits our needs we can use it
/*
    Interface:
        T Get<T>(id);
        IEnumerable<T> GetAll<T>();
        int Insert<T>(T obj);
        int Insert<T>(Enumerable<T> list);
        bool Update<T>(T obj);
        bool Update<T>(Enumerable<T> list);
        bool Delete<T>(T obj);
        bool Delete<T>(Enumerable<T> list);
        bool DeleteAll<T>();
*/

// Dapper.Contrib tries to put all the fields that are in our model
// if they dont exist in database we might get errors
// Here property attributes come in:

// [Computed]
// Fields that don't actually exist in database but are needed in model

// [Write(false)]
// Not computed, but we don't want to attempt to insert that property into database, because it doesn't exist there

#!csharp

// Handling relationships
// One contact can have many addresses, 1:n
var contactWithAddresses = repository.GetFullContact(1);
foreach (var address in contactWithAddresses.Addresses)
{
    Console.WriteLine(address.StreetAddress);
}

#!csharp

// Handling stored procedures
public Contact Find(int id)
{
    // Alternative to using anonymous types is using DynamicParameters class
    // it lets us explicitly provide details about each of the parameters
    var parameters = new DynamicParameters();
    parameters.Add("@Id", value: contact.Id, dbType: DbType.Int32, direction: ParameterDirection.InputOutput);
    // we can also get info about them
    var id1 = parameters.Get<int>("@Id");
    return db.QueryFirst<Contact>("GetContact", parameters, commandType: CommandType.StoredProcedure);
}

#!csharp

// List Support for In Operator
public List<Contact> GetContactsById(params int[] ids)
{
    return db.Query<Contact>("SELECT * FROM Contacts WHERE Id In @Ids", new { Ids = ids }).ToList();
}

#!csharp

// Dynamic capabilities
public dynamic Find(int id)
{
    // If we won't use generic methods then the returned type will be dynamic
    // it will still be the same object, just not mapped
    // so if we want to avoid mapping entity then we can just use dynamics!
    return db.QueryFirst("SELECT * FROM Contacts WHERE Id = @id", new { id });
}

#!csharp

var contacts = new List<Contact>()
{
    new Contact() { FirstName = "a", LastName = "a" },
    new Contact() { FirstName = "b", LastName = "b" },
    new Contact() { FirstName = "c", LastName = "c" },
    new Contact() { FirstName = "d", LastName = "d" }
};

// Bulk Insert
public void BulkInsert(List<Contact> contacts)
{
    // we can just use single add, because dapper will execute it multiple times for collection
    repository.Add(contacts);
}

#!csharp

// Literal replacements
public List<Address> GetAddressesByState(int stateId)
{
    // Literal replacement is declared as follows: {=param}
    // They can be used for grouping results, e.g. getting all addresses that are in state X
    // They are mostly used for boolean and numeric types
    return db.Query<Address>("SELECT * FROM Addresses WHERE StateId = {=stateId}", new { StateId = stateId }).ToList();
}

#!csharp

// Multi-mapping
// Destined for m:n relationships
// This is how we can handle JOINs
// eager-loading with single query

public List<Contact> GetAllContactsWithAddresses()
{
    var sql = "SELECT * FROM Contacts AS c INNER JOIN Addresses AS a ON a.ContactId = c.Id";

    var contactDict = new Dictionary<int, Contact>();

    // familiar to linq, <parent, child, return value>
    var contacts = db.Query<Contact, Address, Contact>(sql, (contact, address) =>
    {
        if (!contactDict.TryGetValue(contact.Id, out var currentContact))
        {
            currentContact = contact;
            contactDict.Add(currentContact.Id, currentContact);
        }

        // if entry has more than one address we map it to addresses array that is not officially in the database table
        // This way we have info about all addresses if there are multiple
        contact.Addresses.Add(address);
        return contact;
    });
    return contacts.Distinct().ToList();
}

#!markdown

Extra functionalities:
- async
- support for different databases (only way of connecting will change, methods will stay the same)