Dapper skills and basic CRUD, dapper skills crud

Dapper skills and basic CRUD, dapper skills crud
Dapper skills and basic CRUD

1. add, delete, modify, and query a model.

     class ATest {            public int ID { get; set; }            public string Name { get; set; }            public string Code { get; set; }       }

 

2. Create a database

 

 1 CREATE TABLE [dbo].[ATest]( 2  3     [ID] [INT] IDENTITY(1,1) NOT NULL, 4  5     [Name] [VARCHAR](50) NULL, 6  7     [Code] [VARCHAR](50) NULL, 8  9  CONSTRAINT [PK_ATest] PRIMARY KEY CLUSTERED10 11 (12 13     [ID] ASC14 15 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]16 17 ) ON [PRIMARY]18 19  20 21 GO

 

3. add, delete, modify, and query data

The reference is messy and some tests have been conducted. By the way, MongoDB. Driver is also very useful and will be written next time.

 1 using System; 2 using System.Collections.Generic; 3 using System.Collections.ObjectModel; 4 using System.Collections; 5 using System.Linq; 6 using System.Text; 7 using System.Threading.Tasks; 8 using MongoDB.Bson; 9 using MongoDB.Driver;10 using Dapper;11 using System.Data;12 using System.Data.SqlClient;13 using AutoMapper.Mappers;14 using MongoDBTest.Model.TableExtend;15 using Newtonsoft.Json;16 using Newtonsoft;

 

1 static string conn = "data source = .; database = test; User ID = sa; Password = sa123456 "; 2 3 public static IDbConnection DB = new SqlConnection (conn ); 4 5 # region CRUD operation with model 6 7 /// <summary> 8 9 /// insert test 10 11 /// </summary> 12 13 /// <param name = "db"> </param> 14 15 // <param name = "test"> </param> 16 17 static void Insert (IDbConnection db, ATest test) 18 19 {20 21 string SQL = "insert into ATest (Name, Code) values (@ Name, @ Code)"; 22 23 db. execute (SQL, test ); 24 25} 26 27 /// <summary> 28 29 // insert 30 31 in batches /// </summary> 32 33 // <param name = "db"> </param> 34 35 // <param name = "test"> </param> 36 37 static void BluckInsert (IDbConnection db, list <ATest> list) 38 39 {40 41 string SQL = "insert into ATest (Name, Code) values (@ Name, @ Code)"; 42 43 db. execute (SQL, list ); 44 45} 46 47 // <summary> 48 49 // search for all objects named test 50 51 /// </summary> 52 53 // <param name = "db"> </param> 54 55 // <returns> </returns> 56 57 static IEnumerable <ATest> GetAlltest (IDbConnection db) 58 59 {60 61 string SQL = @ "SELECT ID, Name, Code 62 63 FROM dbo. ATest where Name = @ Name "; 64 65 return db. query <ATest> (SQL, new {Name = "test"}); 66 67} 68 69 static IEnumerable <ATest> GetAll (IDbConnection db) 70 71 {72 73 string SQL = @ "SELECT ID, Name, Code 74 75 FROM dbo. ATest "; 76 77 return db. query <ATest> (SQL ); 78 79} 80 81 // <summary> 82 83 // Delete 84 85 // </summary> 86 87 // <param name = "db"> </param> 88 89 static void DeleteAll (IDbConnection db) {90 91 string SQL = "TRUNCATE TABLE dbo. ATest "; 92 93 db. execute (SQL); 94 95} 96 97 static void Update (IDbConnection db, ATest test) {98 99 string SQL = "update ATest set Name = @ Name where Code = @ Code"; 100 101 db. execute (SQL, test); 102 103} 104 105 106 107 # endregion108 109 110 111 static void Main (string [] args) 112 113 {114 115 DeleteAll (DB ); 116 117 Console. writeLine ("------------ deletion ended ---------------"); 118 119 // test inserting a single object 120 121 ATest test = new ATest () {Name = "test ", code = "Test"}; 122 123 Insert (DB, test); 124 125 var json = JsonConvert. serializeObject (GetAll (DB); 126 127 Console. writeLine (json); 128 129 Console. writeLine ("------------ insert end ---------------"); 130 131 // batch insert 132 133 List <ATest> list = new List <ATest> () {134 135 136 137 new ATest () {Name = "test", Code = "bluckinsert"}, 138 139 new ATest () {Name = "test", Code = "bluckinsert"}, 140 141 new ATest () {Name = "test", Code = "bluckinsert"}, 142 143 new ATest () {Name = "test", Code = "bluckinsert"}, 144 145 new ATest () {Name = "test", Code = "bluckinsert"}, 146 147}; 148 149 BluckInsert (DB, list); 150 151 json = JsonConvert. serializeObject (GetAll (DB); 152 153 Console. writeLine (json); 154 155 Console. writeLine ("------------ batch insertion ends ---------------"); 156 157 // modify, change the name of all data in the list set above to Jay Chou, and directly use a model to pass values in, the running result is equivalent to paramer: new {Name = "Jay Chou", Code = "bluckinsert"} 158 159 var updatemodel = list. firstOrDefault (); 160 161 updatemodel. name = "Jay Chou"; 162 163 Update (DB, updatemodel); 164 165 json = JsonConvert. serializeObject (GetAll (DB); 166 167 Console. writeLine (json); 168 169 Console. writeLine ("------------ update ended ---------------"); 170 171 Console. readLine (); 172 173}

Note: You can use the model or object new {Name = "test", Code = "Test"} to input parameters. This is the focus of this test, in the past, I was told to query Gooole and use DynamicParameters. I believe that I have taken many detours. There are also a series of extensions that have been tried, such as Extendtions and Contrib. Although there are many help classes, the more troublesome it is. Especially write extension [key].

Running result:

Ii. Do not use Models

1. If the model is not used, the preceding model is written in the form of object paramer = new {key1 = value1, key2 = value2. Still flexible.

2. The Dymaic type of the Query. If this is not understood, it is quite useful to understand it as an anonymous class. Simply converting CRUD To json is irrelevant to the background. The complicated logic has not yet figured out how to use it.

Iii. Why should we perform this test?

Currently, the project architecture is relatively old, basically it is SQL writing, and the model layer is also a combination of SQL, which is totally different from the data schema. It is really troublesome to change hundreds of tables, the business is also complicated. In this test, we want to achieve full dynamics without establishing an entity model, and implement full dynamics in the background logic.

This is the idea and test result of the basic data access layer. The next article describes how to set up mvc dynamic project management.