dapper dot net を試す

gistfile1.cs

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Dapper;

namespace ConsoleApp {
	class Category {
		public int Id { get; set; }
		public string Name { get; set; }
	}
	class Book {
		public int Id { get; set; }
		public string Title { get; set; }
		public Category Category { get; set; }
	}

	class Program {
		private static SqlConnection Connect() {
			var builder = new SqlConnectionStringBuilder {
				DataSource = ".",
				InitialCatalog = "tempdb",
				IntegratedSecurity = true,
			};

			var connection = new SqlConnection(builder.ConnectionString);
			connection.Open();
			return connection;
		}

		static void Main(string[] args) {
			using(var connection = Connect()) {
				// データ準備
				connection.Execute(@"
create table #Category(Id int, Name nvarchar(50));
create table #Book(Id int, Title nvarchar(50), CategoryId int);

insert into #Category(Id, Name)
values(1, 'プログラミング'), (2, 'データベース');
insert into #Book(Id, Title, CategoryId)
values(1, 'C#の本', 1), (2, 'JavaScriptの本', 1), (3, 'SQLの本', 2);");

				// クエリ実行とマッピング
				var books = connection.Query<Book, Category, Book>(@"
select *
from #Book
	inner join #Category
		on #Book.CategoryId = #Category.Id;",
					// マッピング用デリゲート
					(book, category) => {
						book.Category = category;
						return book;
					});
				foreach(var book in books) {
					Console.WriteLine("{0} {1} {2}", book.Id, book.Title, book.Category.Name);
				}
			}
		}
	}
}

gistfile2.cs

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Dapper;

namespace ConsoleApp {
	class Book {
		public string Isbn { get; set; }
		public string Title { get; set; }
	}

	class Order {
		public int Id { get; set; }
		public DateTime Date { get; set; }
		public Book Book { get; set; }
	}

	class Program {
		private static SqlConnection Connect() {
			var builder = new SqlConnectionStringBuilder {
				DataSource = ".",
				InitialCatalog = "tempdb",
				IntegratedSecurity = true,
			};

			var connection = new SqlConnection(builder.ConnectionString);
			connection.Open();
			return connection;
		}

		static void Main(string[] args) {
			using(var connection = Connect()) {
				var orders = connection.Query<Order, Book, Order>(@"
select
	1 as Id,
	'2013/02/16' as Date,
	'0001234567890' as Isbn,
	'すごい本' as Title;",
					(order, book) => {
						order.Book = book;
						return order;
					},
					// "Isbn"以降をBookにマッピング
					splitOn: "Isbn");

				foreach(var order in orders) {
					Console.WriteLine("{0} {1:d} {2} {3}",
						order.Id, order.Date, order.Book.Isbn, order.Book.Title);
				}
			}
		}
	}
}

gistfile3.cs

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Dapper;

namespace ConsoleApp {
  struct Point {
		public int X { get; set; }
		public int Y { get; set; }
		public int Z { get; set; }
	}

	class Program {
		private static SqlConnection Connect() {
			var builder = new SqlConnectionStringBuilder {
				DataSource = ".",
				InitialCatalog = "tempdb",
				IntegratedSecurity = true,
			};

			var connection = new SqlConnection(builder.ConnectionString);
			connection.Open();
			return connection;
		}

		static void Main(string[] args) {
			using(var connection = Connect()) {
				// strongly typed objects
				var result1 = connection
					.Query<Point>("select @x as X, @y as Y, @z as Z", new { x = 1, y = 2, z = 3 })
					.First();
				Console.WriteLine("X:{0} Y:{1} Z:{2}", result1.X, result1.Y, result1.Z);	// X:1 Y:2 Z:3

				// dynamic objects
				var result2 = connection
					.Query("select @x as X, @y as Y, @z as Z", new { x = 1, y = 2, z = 3 })
					.First();
				Console.WriteLine("X:{0} Y:{1} Z:{2}", result2.X, result2.Y, result2.Z);	// X:1 Y:2 Z:3
			}
		}
	}
}

gistfile4.cs

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Dapper;

namespace ConsoleApp {
  class Program {
		private static SqlConnection Connect() {
			var builder = new SqlConnectionStringBuilder {
				DataSource = ".",
				InitialCatalog = "tempdb",
				IntegratedSecurity = true,
			};

			var connection = new SqlConnection(builder.ConnectionString);
			connection.Open();
			return connection;
		}

		class QueryResult {
			public DayOfWeek DayOfWeek { get; set; }
		}

		static void Main(string[] args) {
			using(var connection = Connect()) {
				// intをDayOfWeekプロパティにマッピング
				var result1 = connection
					.Query<QueryResult>("select 1 as DayOfWeek;")
					.First();
				Console.WriteLine(result1.DayOfWeek);	// Monday

				// 文字列をDayOfWeekプロパティにマッピング
				var result2 = connection
					.Query<QueryResult>("select 'Monday' as DayOfWeek;")
					.First();
				Console.WriteLine(result2.DayOfWeek);	// Monday

				// intをDayOfWeekとして取得
				var result3 = connection
					.Query<DayOfWeek>("select 1;")
					.First();
				Console.WriteLine(result3);	// Monday

				// 文字列をDayOfWeekとして取得
				// 例外がスロー
				/*
				var result4 = connection
					.Query<DayOfWeek>( "select 'Monday';" )
					.First();
				Console.WriteLine( result4 );
				*/
			}
		}
	}
}

gistfile5.cs

using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Dapper;

namespace ConsoleApp {
	class Author {
		public int Id { get; set; }
		public string Name { get; set; }
	}

	class Book {
		public string Isbn { get; set; }
		public string Title { get; set; }
		public Author Author { get; set; }
	}

	class Order {
		public int Id { get; set; }
		public DateTime Date { get; set; }
		public Book Book { get; set; }
	}

	class Program {
		private static SqlConnection OpenConnection() {
			var connection = new SqlConnection("Data Source=.;Initial Catalog=tempdb;Integrated Security=True");
			connection.Open();
			return connection;
		}

		static void Main(string[] args) {
			using (var connection = OpenConnection()) {
				var orders = connection.Query<Order, Book, Author, Order>(@"
select
	1 as Id,
	'2014/02/05' as Date,
	N'0001234567890' as Isbn,
	N'あれな本' as Title,
	2 as Id,
	N'あれな人' as Name;",
					(order, book, author) => {
						book.Author = author;
						order.Book = book;
						return order;
					},
					// bookはIsbnから、authorはIdから
					splitOn: "Isbn,Id");

				foreach (var order in orders) {
					Console.WriteLine("{0} {1:d} {2} {3} {4} {5}",
						order.Id, order.Date, order.Book.Isbn, order.Book.Title, order.Book.Author.Id, order.Book.Author.Name);
				}
			}
		}
	}
}