Dapper.net using a simple example

Overview

Dapper is the. NET next micro ORM, which is lightweight and semi-automatic, unlike the Entity Framework or nhibnate. This means that the entity classes are written by themselves. It does not have a complex configuration file, a single file can be.

has now replaced the original SqlHelper advantages:

• Object mapping can be done automatically using Dapper!
• Lightweight, single file.
• Supports multiple databases.
• The dapper principle is to get and produce objects quickly by emit the sequence queue that reflects IDataReader.
• Since the operation of the dapper Orm is actually an extension of the IDbConnection class, all methods are extension methods of that class. So instantiate a IDbConnection object before using it. So before calling, be sure to add a namespace: using Dapper;

Examples of Use

database table Structure

----Book table, Main Table
CREATE TABLE [dbo]. [Book] ([BookId] [int] IDENTITY (1,1) not NULL, [Name] [nvarchar] ( -) not NULL, CONSTRAINT [Pk_book] PRIMARY KEY CLUSTERED ([BookId] ASC) with (Pad_index= off, Statistics_norecompute = off, Ignore_dup_key = off, Allow_row_locks = on, Allow_page_locks =On ) On [PRIMARY]) on [primary]go----Bookreview similar to book evaluation table CREATE TABLE [dbo]. [Bookreview] ([Id] [int] IDENTITY (1,1) not NULL, [BookId] [int] NULL, [Content] [nvarchar] ( -) NULL, CONSTRAINT [Pk_bookreview] PRIMARY KEY CLUSTERED ([Id] ASC) with (Pad_index= off, Statistics_norecompute = off, Ignore_dup_key = off, Allow_row_locks = on, Allow_page_locks =On ) On [PRIMARY]) on [Primary]goalter TABLE [dbo].  [Bookreview] With CHECK ADD CONSTRAINT [Fk_bookid_bookprebookid] FOREIGN KEY ([BookID]) REFERENCES [dbo]. [Book] ([BookId]) Goalter TABLE [dbo]. [Bookreview] CHECK CONSTRAINT [Fk_bookid_bookprebookid]go

Entity structure

 Public classBook { PublicBook () {Reviews=NewList<bookreview>(); }         Public intBookId {Get;Set; }  Public stringName {Get;Set; }  Public VirtualList<bookreview> Reviews {Get;Set; }  Public Override stringToString () {return string. Format ("[{0}]------' {1} '", BookId, Name); }    }//Book review     Public classBookreview { Public intId {Get;Set; }  Public intBookId {Get;Set; }  Public Virtual stringContent {Get;Set; }  Public VirtualBook Assoicationwithbook {Get;Set; }  Public Override stringToString () {return string. Format ("{0})--[{1}]\t\ "{3}\"", Id, BookId, Content); }    }

• Execute Execute Database Script

         Public Static voidOpeartdata () {stringSqlconnct = system.configuration.configurationmanager.connectionstrings["Myconnect"].            ConnectionString; using(IDbConnection comm =NewSqlConnection (sqlconnct)) {Comm.                Open (); //Book Book = new book () {name= "c#9 exposition", id=1};                stringquery ="INSERT into book (Name) VALUES (@name)"; //Add, you can execute SQL script//Comm.                Execute (query, book); //Comm. Execute (query, new {name = "Java Learn"});Query="UPDATE book SET [email protected] WHERE BookId [email protected]"; //Comm.                Execute (query, book); //Comm. Execute (query, new {name= "C985426", bookid=1});Query="DELETE from book WHERE BookId = @id"; //var idsult=comm. Execute (query, new {name = "C985426", BookId = 1});Query="SELECT * from book where [email protected]"; //var ruslt = Comm. Query<book> (query).                ToList (); //var ruslt = Comm. Query<book> (query, new {BookId = 2}). FirstOrDefault ();            }        }

• Query execution Database script queries

  • One to more queries

   Public StaticBook Opeartmodata () {stringSqlconnct = system.configuration.configurationmanager.connectionstrings["Myconnect"].            ConnectionString; using(IDbConnection conn =NewSqlConnection (sqlconnct)) {Conn.                Open (); stringquery ="SELECT * from book B left joins Bookreview BR on Br. BookId = b.id WHERE b.id = @id"; Book Lookup=NULL; //Query<tfirst, Tsecond, treturn>                varb = Conn. Query<book, Bookreview, book>(Query, (book, Bookreview)=                 {                     //scan the first record to determine non-null and non-repetition                     if(Lookup = =NULL|| Lookup. BookId! =Book . BOOKID) Lookup=Book ; //books corresponding to the book review is not empty, added to the current book review list, and finally the repetition of the book removed.                      if(Bookreview! =NULL) lookup.                     Reviews.add (Bookreview); returnLookup; }, New{id =2}, SplitOn:"ID"). Distinct ().                Singleordefault (); returnb; }        }

  • One to one query

    Public StaticBookreview opeartdata1t1 () {stringSqlconnct = system.configuration.configurationmanager.connectionstrings["Myconnect"].            ConnectionString; using(IDbConnection conn =NewSqlConnection (sqlconnct))                {Bookreview BR; stringquery ="SELECT Br. Id,b.bookid,content,name from Bookreview br inner joins book B on Br. BookId = b.bookid WHERE br.id = @id"; using(conn) {BR= Conn. Query<bookreview, book, bookreview>(query, (bookreview, book)={Bookreview.assoicationwithbook=Book ; returnBookreview; }, New{id =4}, SplitOn:"BookId", CommandType:CommandType.StoredProcedure).                    FirstOrDefault (); returnBR; }            }        }

  • Execution transactions

    Public Static voidbooktransaction () {stringSqlconnct = system.configuration.configurationmanager.connectionstrings["Myconnect"].            ConnectionString; using(IDbConnection conn =NewSqlConnection (sqlconnct)) {Conn.                Open (); //Start a transactionIDbTransaction transaction =Conn.                BeginTransaction (); Try                {                    stringquery ="DELETE from book WHERE id = @id"; stringQuery2 ="DELETE from Bookreview WHERE BookId = @BookId"; Conn. Execute (Query2,New{BookId =2}, Transaction,NULL,NULL); Conn. Execute (Query,New{id =2}, Transaction,NULL,NULL); //Commit a transactionTransaction.commit (); }                Catch(Exception ex) {//exception occurred, transaction rollbacktransaction.                    Rollback (); Throw NewException (ex.                Message); }            }        }

Dapper.net using a simple example