Search

Categories

 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 

Send mail to the author(s) E-mail

# Wednesday, 27 April 2016

Inspired by:
http://farm-fresh-code.blogspot.co.uk/2014/02/lightweight-integration-testing-with.html

which was itself inspired by a Jimmy Bogard article
https://lostechies.com/jimmybogard/2012/10/18/isolating-database-data-in-integration-tests/

If you get the MSDTC error
http://toddmeinershagen.blogspot.co.uk/2014/05/data-integration-tests-and-transactions_25.html

Here is the start of an example just using rollback transactions

[TestClass] public class TransactionTests { [TestMethod] public void Insert_User_and_test_transaction_rolls_back() { var user = new User { CreatedDate = DateTime.Now, DisplayName = "Todd Smith4", Email = "todd4@asdf.com", Active = true }; var sql = "INSERT INTO dbo.Users (CreatedDate, DisplayName, Email, Active) VALUES (@CreatedDate, @DisplayName, @Email, @Active)"; db.Execute(sql, user); var retrievedUser = db.Query<User>("select * from users where Email = @email", new { email = user.Email }).FirstOrDefault(); Assert.AreEqual(user.DisplayName, retrievedUser.DisplayName); Assert.AreEqual(user.Active, retrievedUser.Active); Assert.AreEqual(user.CreatedDate.ToString(), retrievedUser.CreatedDate.ToString()); }

asdf

private TransactionScope scope; private DbConnection db; private DapperUserRepositoryTestContext c; private bool rollBackTransaction = true; // before each test [TestInitialize] public void SetUp() { // direct access to the db db = new SqlConnection( ConfigurationManager.ConnectionStrings["DapperMangoTestingConnectionString"].ConnectionString); if (rollBackTransaction) scope = new TransactionScope(); // going through the repo c = new DapperUserRepositoryTestContext(); } // after each test [TestCleanup] public void TearDown() { if (rollBackTransaction) scope.Dispose(); db.Dispose(); }
| | # 
# Sunday, 05 April 2015
( Dapper | Miniprofiler | MVC5 )

Got working

using (profiler.Step("Doing complex stuff")) { using (profiler.Step("Step A")) { Thread.Sleep(5); } using (profiler.Step("Step B")) { Thread.Sleep(25); } }

Simple example of timing

using (mp.CustomTiming("http", url)) { var json = CallAPI(stopWatchResult, url); return json; }

This is good

image

Interesting – Azure DNS broken again, or just slow network?

image
A good use case for Async!

image
Well – actually I could increase the limit size from 20 to 50 to make it faster.

image
Yes as expected it makes it faster to do all gets in parallel.

image

image
Showing the difference

| | # 
( Dapper )

http://stackoverflow.com/questions/10030285/dapper-rainbow-vs-dapper-contrib

Dapper.Contrib

  • CRUD
  • Change tracking

Dapper.Rainbow

  • CRUD
  • Jan 12th 2012
  • 5k downloads

Dapper Extensions

  • CRUD
  • August 5th 2014
  • 37k downloads

Dapper.SimpleCRUD

  • Feb 27th, 2015
  • 5k

https://github.com/ericdc1/Dapper.SimpleCRUD/
https://github.com/ericdc1/Dapper.SimpleCRUD/wiki/T4-Template – generate POCOS from DB

Read

public Contact Get(int id){ return db.Get<Contact>(id); }

instead of:

public Contact Find(int id) { // Anonymous types for parameters // if not found, will return a null return db.Query<Contact>("SELECT * FROM Contacts WHERE Id = @Id", new { id }).SingleOrDefault(); }

 

had to mark up the class as:

// used for SimpleCRUD [Table("Contacts")] 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; } //Additional properties not in database [Editable(false)] public bool IsNew { get { return this.Id == default(int); } } }

Read Many

public IEnumerable<Contact> GetList(){ return db.GetList<Contact>(); }

instead of:

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

Insert

Getting a strange error, so looking at the tests in source: https://github.com/ericdc1/Dapper.SimpleCRUD

image
Nice tests – uses reflection to find the tests

public Contact Insert(Contact contact) { using (var db = GetOpenConnection()){ var id = db.Insert(contact); contact.Id = (int)id; return contact; } }

so this works.. instead of:

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

Makes life a bit easier.

Update and Delete

public Contact Update(Contact contact){ using (var db = GetOpenConnection()) { db.Update(contact); return contact; } } public void Delete(Contact contact) { using (var db = GetOpenConnection()) { db.Delete(contact); } }

instead of:

public Contact UpdateOld(Contact contact) { using (var db = GetOpenConnection()){ var sql = "UPDATE Contacts " + "SET FirstName = @FirstName, " + " LastName = @LastName, " + " Email = @Email, " + " Company = @Company, " + " Title = @Title " + "WHERE Id = @Id"; db.Execute(sql, contact); return contact; } } public void DeleteOld(int id) { using (var db = GetOpenConnection()){ db.Execute("DELETE FROM Contacts WHERE Id = @Id", new{id}); } }

So quite a bit simpler.  Next step is to see how it goes with more complexity, and to get some figures for perf and see the actual queries in the UI/Log files.

http://stackoverflow.com/questions/14318571/how-to-read-an-sql-query-generated-by-dapper

| | # 
# Friday, 03 April 2015
( Dapper )

What is a Micro ORM?

  • Lightweight (no designer or config files)
  • Fast
  • Simple (code focus, and some cases closer to the metal ie more SQL)
  • Good at mapping from POCO to DB parameters, and back

http://www.pluralsight.com/courses/exercise-files/dotnet-micro-orms-introduction

Dapper vs EF

static void Main(string[] args) { Console.Title = "Micro ORM Demo"; var connectionString = ConfigurationManager.ConnectionStrings["contactsDB"].ConnectionString; // EF //var context = new ContactsContext(connectionString); //var contacts = context.Contacts.ToList(); //var contacts = context.Contacts.Where(c => c.Id == 1); // Dapper - implemented using extension methods IDbConnection db = new SqlConnection(connectionString); //var contacts = db.Query<Contact>("SELECT * FROM Contacts"); var contacts = db.Query<Contact>("SELECT * FROM Contacts WHERE Id = @Id", new {id=1}); foreach (var item in contacts) { Console.WriteLine(item.ToString()); } Console.WriteLine("Done."); Console.ReadLine(); }

A very simple way to query the db using EF and Dapper.
class ContactsContext : DbContext { public ContactsContext(string connectionString) : base(connectionString) { } public DbSet<Contact> Contacts { get; set; } }

EF’s context
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; } public override string ToString() { return string.Format("{0} - {1} {2} ({3}) - {4} ({5})", this.Id, this.FirstName, this.LastName, this.Title, this.Company, this.Email); } }

Defining a contact
<connectionStrings> <add name="contactsDB" connectionString="server=.\;database=ContactsDemoDB;Trusted_Connection=Yes;" providerName="System.Data.SqlClient" /> </connectionStrings>

db connection string

Setting up the DB

image

In a DB project, can set a post deploy script to seed data (in the properties of the file)

image
Using SSMS Database Diagram

Ordered Tests

image
A way of running the tests in order.  Maybe should wrap in a transaction, or run db scripts so if in the middle, it will get db state back to the start.  Or maybe not use them

Features of Dapper

  • Speed! (almost as fast as raw ADO.NET DataReader)
  • Mapping from db columns to properties of CLR objects
  • Sprocs
  • Rich community of Contrib projects..
private IDbConnection db = new SqlConnection(ConfigurationManager.ConnectionStrings["ContactsDB"].ConnectionString); public Contact Find(int id) { // Use Anonymous types typically // SingleOrDefault so if its not found it will return a null return db.Query<Contact>("SELECT * FROM Contacts WHERE Id = @Id", new {Id = id}).SingleOrDefault(); } public List<Contact> GetAll() { return db.Query<Contact>("SELECT * FROM Contacts").ToList(); } 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 = db.Query<int>(sql, contact).Single(); contact.Id = id; return contact; } public Contact Update(Contact contact) { var sql = "UPDATE Contacts " + "SET FirstName = @FirstName, " + " LastName = @LastName, " + " Email = @Email, " + " Company = @Company, " + " Title = @Title " + "WHERE Id = @Id"; db.Execute(sql, contact); return contact; } public void Remove(int id) { db.Execute("DELETE FROM Contacts WHERE ID = @Id", new {id}); }

Add method – notice am using the object Contact, which matched up to the db columns, so don’t have to specify the parameters. Annoying that have to write boilerplate SQL ie for the INSERT

Addresses Child Table

1 contact can have multiple addresses

public Contact GetFullContact(int id){ var sql = "SELECT * FROM Contacts WHERE Id = @Id; " + "SELECT * From Addresses WHERE ContactID = @Id"; using (var multipleResults = db.QueryMultiple(sql, new {id})){ var contact = multipleResults.Read<Contact>().SingleOrDefault(); var addresses = multipleResults.Read<Address>().ToList(); if (contact != null && addresses != null){ contact.Addresses.AddRange(addresses); } return contact; } }

Creating a contact with addresses hydrated using 1 round trip to the db.

Instead of saving each entity – we’ll save the parent entity which is hydrated.

public void Save(Contact contact) { using (var txScope = new TransactionScope()){ if (contact.IsNew){ Add(contact); } else{ Update(contact); } foreach (var addr in contact.Addresses.Where(a => !a.IsDeleted)){ addr.ContactId = contact.Id; if (addr.IsNew){ Add(addr); } else{ Update(addr); } } // if delete an address off an existing contact foreach (var addr in contact.Addresses.Where(a => a.IsDeleted)){ db.Execute("DELETE FROM Addresses WHERE Id = @Id", new {addr.Id}); } txScope.Complete(); } }

Quite nice as easy to see what is going on, and wrapped in a transaction.

Durandal UI

He’s done a nice simple SPA UI    ,                                                       

Bulk Insert

ie a list<contacts> with a single INSERT.. behind the scenes though.. multipletrips to the db.. just nice sugar

List Support

ie SQL - IN

| | # 
# Tuesday, 20 May 2014