1 using DapperTest.Models;
2 using System.Collections.Generic;
3 using System.Web.Http;
4 using Dapper;
5 using System.Data;
6 using System.Data.SqlClient;
7 using System.Linq;
8 using System.Configuration;
9
10 namespace DapperTest.Controllers
11 {
12 public class HomeController : ApiController
13 {
14 #region 查询
15
16 /// <summary>
17 /// 查询所有数据
18 /// </summary>
19 /// <returns></returns>
20 [HttpGet]
21 public IHttpActionResult GetStudentList()
22 {
23 string conStr = ConfigurationManager.AppSettings["SqlConnStr"];
24 string sql = @"SELECT * FROM STUDENT";
25 using (IDbConnection conn = new SqlConnection(conStr))
26 {
27 var result = conn.Query<StudentInfo>(sql).ToList();
28 return Ok(ReturnJsonResult.GetJsonResult(RequestResult.请求成功, result));
29 }
30 }
31
32 /// <summary>
33 /// 查询指定ID单条数据(带参数)
34 /// </summary>
35 /// <returns></returns>
36 [HttpGet]
37 public IHttpActionResult GetStudentInfo(string ID)
38 {
39 string conStr = ConfigurationManager.AppSettings["SqlConnStr"];
40 string sql = @"SELECT * FROM STUDENT WHERE STUID in @STUID";
41 using (IDbConnection conn = new SqlConnection(conStr))
42 {
43 var result = conn.Query<StudentInfo>(sql, new { STUID = ID });
44 return Ok(ReturnJsonResult.GetJsonResult(RequestResult.请求成功, result));
45 }
46 }
47
48 /// <summary>
49 /// IN查询
50 /// </summary>
51 /// <returns></returns>
52 [HttpGet]
53 public IHttpActionResult GetStudentInfos(string IDStr)
54 {
55 string conStr = ConfigurationManager.AppSettings["SqlConnStr"];
56 string sql = @"SELECT * FROM STUDENT WHERE STUID in @STUIDStr";
57 var IDArr = IDStr.Split(',');
58 using (IDbConnection conn = new SqlConnection(conStr))
59 {
60 var result = conn.Query<StudentInfo>(sql, new { STUIDStr = IDArr });
61 return Ok(ReturnJsonResult.GetJsonResult(RequestResult.请求成功, result));
62 }
63 }
64
65 /// <summary>
66 /// 两表联合查询
67 /// </summary>
68 /// <returns></returns>
69 [HttpGet]
70 public IHttpActionResult GetStudentAndClass()
71 {
72 string conStr = ConfigurationManager.AppSettings["SqlConnStr"];
73 string sql = @"SELECT * FROM STUDENT A JOIN CLASS B ON A.FK_CLASSID = B.ID";
74 using (IDbConnection conn = new SqlConnection(conStr))
75 {
76 var result = conn.Query(sql);
77 return Ok(ReturnJsonResult.GetJsonResult(RequestResult.请求成功, result));
78 }
79 }
80
81 #endregion
82
83 #region 新增
84
85
86 /// <summary>
87 /// 插入单条数据(带参数)
88 /// </summary>
89 /// <returns></returns>
90 [HttpPost]
91 public IHttpActionResult AddStudent()
92 {
93 string conStr = ConfigurationManager.AppSettings["SqlConnStr"];
94 string sql = @"INSERT INTO STUDENT (NAME,AGE,FK_CLASSID) VALUES (@NAME,@AGE,@CLASSID)";
95
96 StudentInfo student = new StudentInfo
97 {
98 Name = "恩格斯",
99 Age = 55,
100 FK_ClassID = 1
101 };
102
103 using (IDbConnection conn = new SqlConnection(conStr))
104 {
105 var result = conn.Execute(sql, new { NAME = student.Name, AGE = student.Age, CLASSID = student.FK_ClassID });
106 return Ok(ReturnJsonResult.GetJsonResult(RequestResult.请求成功, result));
107 }
108 }
109
110
111 /// <summary>
112 /// 插入单条数据(直接插入整个实体)
113 /// </summary>
114 /// <returns></returns>
115 [HttpPost]
116 public IHttpActionResult AddStudentInfo()
117 {
118 string conStr = ConfigurationManager.AppSettings["SqlConnStr"];
119 string sql = @"INSERT INTO STUDENT (NAME,AGE,FK_CLASSID) VALUES (@NAME,@AGE,@FK_CLASSID)";
120
121 StudentInfo student = new StudentInfo
122 {
123 Name = "马克思",
124 Age = 55,
125 FK_ClassID = 1
126 };
127
128 using (IDbConnection conn = new SqlConnection(conStr))
129 {
130 var result = conn.Execute(sql, student);
131 return Ok(ReturnJsonResult.GetJsonResult(RequestResult.请求成功, result));
132 }
133 }
134
135 /// <summary>
136 /// 插入多条数据(实体)
137 /// </summary>
138 /// <returns></returns>
139 [HttpPost]
140 public IHttpActionResult AddStudentList()
141 {
142 string conStr = ConfigurationManager.AppSettings["SqlConnStr"];
143 string sql = @"INSERT INTO STUDENT (NAME,AGE,FK_CLASSID) VALUES (@NAME,@AGE,@FK_CLASSID)";
144
145 List<StudentInfo> list = new List<StudentInfo>();
146 for (int i = 0; i < 3; i++)
147 {
148 StudentInfo student = new StudentInfo
149 {
150 Name = "强森" + i.ToString(),
151 Age = 55,
152 FK_ClassID = 1
153 };
154 list.Add(student);
155 }
156
157 using (IDbConnection conn = new SqlConnection(conStr))
158 {
159 var result = conn.Execute(sql, list);
160 return Ok(ReturnJsonResult.GetJsonResult(RequestResult.请求成功, result));
161 }
162 }
163
164 /// <summary>
165 /// 插入数据后返回自增主键
166 /// </summary>
167 /// <returns></returns>
168 [HttpPost]
169 public IHttpActionResult AddReturnID()
170 {
171 string conStr = ConfigurationManager.AppSettings["SqlConnStr"];
172 string sql = @"INSERT INTO STUDENT (NAME,AGE,FK_CLASSID) VALUES (@NAME,@AGE,@CLASSID)";
173
174 StudentInfo student = new StudentInfo
175 {
176 Name = "恩格斯",
177 Age = 55,
178 FK_ClassID = 1
179 };
180
181 using (IDbConnection conn = new SqlConnection(conStr))
182 {
183 sql += "SELECT SCOPE_IDENTITY()";
184 var result = conn.Execute(sql, new { NAME = student.Name, AGE = student.Age, CLASSID = student.FK_ClassID });
185 var id = conn.QueryFirstOrDefault<int>(sql, new { NAME = student.Name, AGE = student.Age, CLASSID = student.FK_ClassID });
186 return Ok(ReturnJsonResult.GetJsonResult(RequestResult.请求成功, id));
187 }
188 }
189 #endregion
190
191 #region 更新
192 /// <summary>
193 /// 使用实体更新
194 /// </summary>
195 /// <returns></returns>
196 [HttpPost]
197 public IHttpActionResult UpdateStudetInfo()
198 {
199 string conStr = ConfigurationManager.AppSettings["SqlConnStr"];
200 string sql = @"UPDATE STUDENT SET NAME=@NAME,AGE=@AGE,FK_CLASSID=@FK_CLASSID WHERE STUID = @StuID";
201 StudentInfo student = new StudentInfo
202 {
203 StuID = 1,
204 Name = "老夫子",
205 Age = 59,
206 FK_ClassID = 2
207 };
208 using (IDbConnection conn = new SqlConnection(conStr))
209 {
210 var result = conn.Execute(sql, student);
211 return Ok(ReturnJsonResult.GetJsonResult(RequestResult.请求成功, result));
212 }
213 }
214
215 /// <summary>
216 /// 参数更新
217 /// </summary>
218 /// <returns></returns>
219 [HttpPost]
220 public IHttpActionResult UpdateStudet(int ID)
221 {
222 string conStr = ConfigurationManager.AppSettings["SqlConnStr"];
223 string sql = @"UPDATE STUDENT SET NAME=@NAME,AGE=@AGE,FK_CLASSID=@FK_CLASSID WHERE STUID = @StuID";
224 using (IDbConnection conn = new SqlConnection(conStr))
225 {
226 var result = conn.Execute(sql, new {NAME = "尼古拉斯赵四",AGE = 1,StuID = ID});
227 return Ok(ReturnJsonResult.GetJsonResult(RequestResult.请求成功, result));
228 }
229 }
230 #endregion
231
232 #region 删除
233 public IHttpActionResult Delete(int ID)
234 {
235 string conStr = ConfigurationManager.AppSettings["SqlConnStr"];
236 string sql = @"DELETE STUDENT WHERE STUID = @StuID";
237 using (IDbConnection conn = new SqlConnection(conStr))
238 {
239 var result = conn.Execute(sql, new { StuID = ID });
240 return Ok(ReturnJsonResult.GetJsonResult(RequestResult.请求成功, result));
241 }
242 }
243 #endregion
244
245 #region 事务
246 [HttpPost]
247 public IHttpActionResult AddStudentT()
248 {
249 string conStr = ConfigurationManager.AppSettings["SqlConnStr"];
250 string sql = @"INSERT INTO STUDENT (NAME,AGE,FK_CLASSID) VALUES (@NAME,@AGE,@CLASSID)";
251
252 StudentInfo student = new StudentInfo
253 {
254 Name = "恩格斯",
255 Age = 55,
256 FK_ClassID = 1
257 };
258
259 StudentInfo student2 = new StudentInfo
260 {
261 Name = "恩格斯2",
262 Age = 55,
263 FK_ClassID = 1
264 };
265
266 try
267 {
268 using (IDbConnection conn = new SqlConnection(conStr))
269 {
270 IDbTransaction transaction = conn.BeginTransaction();
271 var result = conn.Execute(sql, student);
272 var result1 = conn.Execute(sql, student2);
273 transaction.Commit();
274 return Ok(ReturnJsonResult.GetJsonResult(RequestResult.请求成功, result));
275 }
276 }
277 catch (System.Exception)
278 {
279 throw;
280 }
281
282 }
283 #endregion
284 }
285 }
浙公网安备 33010602011771号