검색결과 리스트
Dapper.NET에 해당되는 글 1건
- 2013.10.07 MySQL용 C# 라이브러리와 Dapper.NET (2)
MySQL 라이브러리 설치
NuGet으로 설치한다.
Dapper.NET 설치
Dapper.NET 기본 사용 방법
| public static SqlConnection GetOpenConnection() { var connection = new SqlConnection(connectionString); connection.Open(); return connection; }
public void TableName() { using (var connection = GetOpenConnection()) { // tests against "Automobiles" table (Table attribute) connection.Insert(new Car {Name = "Volvo"}); connection.Get<Car>(1).Name.IsEqualTo("Volvo"); connection.Update(new Car() {Id = 1, Name = "Saab"}).IsEqualTo(true); connection.Get<Car>(1).Name.IsEqualTo("Saab"); connection.Delete(new Car() {Id = 1}).IsEqualTo(true); connection.Get<Car>(1).IsNull(); } }
public class Car { public int Id { get; set; } public string Name { get; set; } } |
| IDbConnection connectionFactory() { string ConnString = "Server=172.20.60.221;Database=test;Uid=root;Pwd=패스워드";
IDbConnection db = new MySql.Data.MySqlClient.MySqlConnection(ConnString); return db; }
private void button1_Click(object sender, EventArgs e) { var db = connectionFactory(); db.Open();
var member = Dapper.SqlMapper.Query<USER>(db, "select ID, PW from Account where ID = 'M1_1'"); }
public class USER { public string ID { get; set; } public string PW { get; set; } } |
| struct Point { public int X { get; set; } public int Y { get; set; } public int Z { get; set; } }
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 } |
| class QueryResult { public DayOfWeek DayOfWeek { get; set; } // .NET의 프로퍼티 }
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
} |
Dapper.NET 사용 - 쿼리 갯수 얻기
| public class Product { public int ProductId { get; set; } public string Name { get; set; } public string ProductNumber { get; set; } }
using (var connection = connectionFactory()) { var result = connection.Query<int>("select count(*) from Product").Single(); Console.WriteLine(result + " products."); } |
Dapper.NET 사용 - 쿼리 인자 사용
| using (var connection = connectionFactory()) { var result = connection.Query<Product>( "select * from Product where ListPrice between @low and @high", new { low = 10.0, high = 100.0 });
Console.WriteLine("-- simple mapping:" + result.Count());
foreach (var p in result) { Console.WriteLine(string.Format("ID:{0},Name:{1}", p.ProductId, p.Name)); } } |
Dapper.NET 사용 - 매핑에 다이나믹 사용
| using (var connection = connectionFactory()) { var result = connection.Query( "select * from Product where ListPrice between @low and @high", new { low = 10.0, high = 100.0 });
Console.WriteLine("-- dynamic mapping:" + result.Count());
foreach (var p in result) { Console.WriteLine(string.Format("ID:{0},Name:{1},Price:{2}", p.ProductID, p.Name, p.ListPrice)); } } |
Dapper.NET 사용 - 하나의 쿼리에 두 개의 객체를 맵핑할 때
| 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; } }
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); } |
결과
1 2013/02/16 0001234567890 굉장한 책
Dapper.NET 사용 - insert
| var affected_rows = connection.Execute( @"insert MyTable(colA, colB) values (@a, @b)", new[] { // 파라메터를 배열로 하면 복수 행을 insert 할 수 있다. new { a=1, b=1 }, new { a=2, b=2 }, new { a=3, b=3 } } ); |
Dapper.NET 사용 - Execute a Command that returns no results
connection.Execute(@"
set nocount on
create table #t(i int)
set nocount off
insert #t
select @a a union
all select @b
set nocount on
drop table #t", new
{a=1, b=2 })
.IsEqualTo(2);
Dapper.NET
사용 - Multiple Resultsvar sql = @" select * from Customers where CustomerId = @id select * from Orders where CustomerId = @id select * from Returns where CustomerId = @id";
using (var multi = connection.QueryMultiple(sql, new {id=selectedId})) { var customer = multi.Read<Customer>().Single(); var orders = multi.Read<Order>().ToList(); var returns = multi.Read<Return>().ToList(); ... } |
Dapper.NET
사용 - Executepublic class Category { public int Id { get; set; } public string Name { get; set; } public DateTime ModifiedOn { get; set; } }
public class SubCategory : Category { public int CategoryId { get; set; } }
public int InsertSubCategory(SubCategory subCategory) { using (IDbConnection connection = OpenConnection()) { const string query = "INSERT INTO Production.ProductSubcategory(ProductCategoryID, [Name]) " + "VALUES (@CategoryId, @Name)"; int rowsAffectd = connection.Execute(query, subCategory); SetIdentity<int>(connection, id => subCategory.Id = id); return rowsAffectd; } } |
Dapper.NET
사용 - Updatepublic int UpdateSubCategory(SubCategory subCategory) { using (IDbConnection connection = OpenConnection()) { const string query = "UPDATE Production.ProductSubcategory " + "SET ProductCategoryID = @CategoryId, " + "[Name] = @Name, " + "ModifiedDate = @ModifiedOn " + "WHERE ProductSubcategoryID = @Id"; return connection.Execute(query, subCategory); } } |
Dapper.NET
사용 - Delete
| public int DeleteSubCategory(SubCategory subCategory) { using (IDbConnection connection = OpenConnection()) { const string query = "DELETE FROM Production.ProductSubcategory " + "WHERE ProductSubcategoryID = @Id"; return connection.Execute(query, subCategory); } } |
Dapper.NET 사용 - Stored Procedures
var user = cnn.Query<User>("spGetUser", new
{Id = 1}, commandType: CommandType.StoredProcedure).First();}}}
| var p = new DynamicParameters(); p.Add("@a", 11); p.Add("@b", dbType: DbType.Int32, direction: ParameterDirection.Output); p.Add("@c", dbType: DbType.Int32, direction: ParameterDirection.ReturnValue);
cnn.Execute("spMagicProc", p, commandType: commandType.StoredProcedure);
int b = p.Get<int>("@b"); int c = p.Get<int>("@c"); |
Dapper.NET
사용 - Stored Procedures 인자에 객체 사용
| public void TestProcWithOutAndReturnParameter() { connection.Execute( @"CREATE PROCEDURE #TestProcWithOutAndReturnParameter @ID int output, @Foo varchar(100), @Bar int AS SET @ID = @Bar + LEN(@Foo) RETURN 42"); var obj = new { ID = 0, Foo = "abc", Bar = 4 };
var args = new DynamicParameters(obj); args.Add("ID", 0, direction: ParameterDirection.Output); args.Add("result", 0, direction: ParameterDirection.ReturnValue); connection.Execute("#TestProcWithOutAndReturnParameter", args, commandType: CommandType.StoredProcedure); args.Get<int>("ID").IsEqualTo(7); args.Get<int>("result").IsEqualTo(42); } |
Dapper.NET 사용 - Transaction
| public int DeleteProduct(Product product) { using (IDbConnection connection = OpenConnection()) { const string deleteImageQuery = "DELETE FROM Production.ProductProductPhoto " + "WHERE ProductID = @ProductID"; const string deleteProductQuery = "DELETE FROM Production.Product " + "WHERE ProductID = @ProductID"; IDbTransaction transaction = connection.BeginTransaction(); int rowsAffected = connection.Execute(deleteImageQuery, new { ProductID = product.ProductID }, transaction); rowsAffected += connection.Execute(deleteProductQuery, new { ProductID = product.ProductID }, transaction); transaction.Commit(); return rowsAffected; } } |
Dapper.NET 사용 - Ansi Strings and varchar
Query<Thing>("select * from Thing where Name = @Name", new
{Name = new
DbString { Value = "abcde", IsFixedLength = true, Length = 10, IsAnsi = true
});
댓글
와 이거맘에드는데요. 좋은거 소개 감사합니다.
저도 이런게 마음에 들어서 다음에 PC 온라인 게임 개발할 때 DB서버는 닷넷으로 만들고 싶네요^^