===============查询===============
1 查询所有数据
2 查询指定ID单条数据(带参数)
3 IN查询
4 两表联合查询
===============新增===============
1 插入单条数据(带参数)
2 插入单条数据(直接插入整个实体)
3 插入多条数据(实体)
4 插入数据后返回自增主键
===============更新===============
1 使用实体更新
2 参数更新
===============删除===============
1 删除
===============事务===============
1 事务
using DapperTest.Models;
using System.Collections.Generic;
using System.Web.Http;
using Dapper;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Configuration;
namespace DapperTest.Controllers
{
public class HomeController : ApiController
{
#region 查询
/// <summary>
/// 查询所有数据
/// </summary>
/// <returns></returns>
[HttpGet]
public IHttpActionResult GetStudentList()
{
string conStr = ConfigurationManager.AppSettings["SqlConnStr"];
string sql = @"SELECT * FROM STUDENT";
using (IDbConnection conn = new SqlConnection(conStr))
{
var result = conn.Query<StudentInfo>(sql).ToList();
return Ok(ReturnJsonResult.GetJsonResult(RequestResult.请求成功, result));
}
}
/// <summary>
/// 查询指定ID单条数据(带参数)
/// </summary>
/// <returns></returns>
[HttpGet]
public IHttpActionResult GetStudentInfo(string ID)
{
string conStr = ConfigurationManager.AppSettings["SqlConnStr"];
string sql = @"SELECT * FROM STUDENT WHERE STUID in @STUID";
using (IDbConnection conn = new SqlConnection(conStr))
{
var result = conn.Query<StudentInfo>(sql, new { STUID = ID });
return Ok(ReturnJsonResult.GetJsonResult(RequestResult.请求成功, result));
}
}
/// <summary>
/// IN查询
/// </summary>
/// <returns></returns>
[HttpGet]
public IHttpActionResult GetStudentInfos(string IDStr)
{
string conStr = ConfigurationManager.AppSettings["SqlConnStr"];
string sql = @"SELECT * FROM STUDENT WHERE STUID in @STUIDStr";
var IDArr = IDStr.Split(',');
using (IDbConnection conn = new SqlConnection(conStr))
{
var result = conn.Query<StudentInfo>(sql, new { STUIDStr = IDArr });
return Ok(ReturnJsonResult.GetJsonResult(RequestResult.请求成功, result));
}
}
/// <summary>
/// 两表联合查询
/// </summary>
/// <returns></returns>
[HttpGet]
public IHttpActionResult GetStudentAndClass()
{
string conStr = ConfigurationManager.AppSettings["SqlConnStr"];
string sql = @"SELECT * FROM STUDENT A JOIN CLASS B ON A.FK_CLASSID = B.ID";
using (IDbConnection conn = new SqlConnection(conStr))
{
var result = conn.Query(sql);
return Ok(ReturnJsonResult.GetJsonResult(RequestResult.请求成功, result));
}
}
#endregion
#region 新增
/// <summary>
/// 插入单条数据(带参数)
/// </summary>
/// <returns></returns>
[HttpPost]
public IHttpActionResult AddStudent()
{
string conStr = ConfigurationManager.AppSettings["SqlConnStr"];
string sql = @"INSERT INTO STUDENT (NAME,AGE,FK_CLASSID) VALUES (@NAME,@AGE,@CLASSID)";
StudentInfo student = new StudentInfo
{
Name = "恩格斯",
Age = 55,
FK_ClassID = 1
};
using (IDbConnection conn = new SqlConnection(conStr))
{
var result = conn.Execute(sql, new { NAME = student.Name, AGE = student.Age, CLASSID = student.FK_ClassID });
return Ok(ReturnJsonResult.GetJsonResult(RequestResult.请求成功, result));
}
}
/// <summary>
/// 插入单条数据(直接插入整个实体)
/// </summary>
/// <returns></returns>
[HttpPost]
public IHttpActionResult AddStudentInfo()
{
string conStr = ConfigurationManager.AppSettings["SqlConnStr"];
string sql = @"INSERT INTO STUDENT (NAME,AGE,FK_CLASSID) VALUES (@NAME,@AGE,@FK_CLASSID)";
StudentInfo student = new StudentInfo
{
Name = "马克思",
Age = 55,
FK_ClassID = 1
};
using (IDbConnection conn = new SqlConnection(conStr))
{
var result = conn.Execute(sql, student);
return Ok(ReturnJsonResult.GetJsonResult(RequestResult.请求成功, result));
}
}
/// <summary>
/// 插入多条数据(实体)
/// </summary>
/// <returns></returns>
[HttpPost]
public IHttpActionResult AddStudentList()
{
string conStr = ConfigurationManager.AppSettings["SqlConnStr"];
string sql = @"INSERT INTO STUDENT (NAME,AGE,FK_CLASSID) VALUES (@NAME,@AGE,@FK_CLASSID)";
List<StudentInfo> list = new List<StudentInfo>();
for (int i = 0; i < 3; i++)
{
StudentInfo student = new StudentInfo
{
Name = "强森" + i.ToString(),
Age = 55,
FK_ClassID = 1
};
list.Add(student);
}
using (IDbConnection conn = new SqlConnection(conStr))
{
var result = conn.Execute(sql, list);
return Ok(ReturnJsonResult.GetJsonResult(RequestResult.请求成功, result));
}
}
/// <summary>
/// 插入数据后返回自增主键
/// </summary>
/// <returns></returns>
[HttpPost]
public IHttpActionResult AddReturnID()
{
string conStr = ConfigurationManager.AppSettings["SqlConnStr"];
string sql = @"INSERT INTO STUDENT (NAME,AGE,FK_CLASSID) VALUES (@NAME,@AGE,@CLASSID)";
StudentInfo student = new StudentInfo
{
Name = "恩格斯",
Age = 55,
FK_ClassID = 1
};
using (IDbConnection conn = new SqlConnection(conStr))
{
sql += "SELECT SCOPE_IDENTITY()";
var result = conn.Execute(sql, new { NAME = student.Name, AGE = student.Age, CLASSID = student.FK_ClassID });
var id = conn.QueryFirstOrDefault<int>(sql, new { NAME = student.Name, AGE = student.Age, CLASSID = student.FK_ClassID });
return Ok(ReturnJsonResult.GetJsonResult(RequestResult.请求成功, id));
}
}
#endregion
#region 更新
/// <summary>
/// 使用实体更新
/// </summary>
/// <returns></returns>
[HttpPost]
public IHttpActionResult UpdateStudetInfo()
{
string conStr = ConfigurationManager.AppSettings["SqlConnStr"];
string sql = @"UPDATE STUDENT SET NAME=@NAME,AGE=@AGE,FK_CLASSID=@FK_CLASSID WHERE STUID = @StuID";
StudentInfo student = new StudentInfo
{
StuID = 1,
Name = "老夫子",
Age = 59,
FK_ClassID = 2
};
using (IDbConnection conn = new SqlConnection(conStr))
{
var result = conn.Execute(sql, student);
return Ok(ReturnJsonResult.GetJsonResult(RequestResult.请求成功, result));
}
}
/// <summary>
/// 参数更新
/// </summary>
/// <returns></returns>
[HttpPost]
public IHttpActionResult UpdateStudet(int ID)
{
string conStr = ConfigurationManager.AppSettings["SqlConnStr"];
string sql = @"UPDATE STUDENT SET NAME=@NAME,AGE=@AGE,FK_CLASSID=@FK_CLASSID WHERE STUID = @StuID";
using (IDbConnection conn = new SqlConnection(conStr))
{
var result = conn.Execute(sql, new { NAME = "尼古拉斯赵四", AGE = 1, StuID = ID });
return Ok(ReturnJsonResult.GetJsonResult(RequestResult.请求成功, result));
}
}
#endregion
#region 删除
public IHttpActionResult Delete(int ID)
{
string conStr = ConfigurationManager.AppSettings["SqlConnStr"];
string sql = @"DELETE STUDENT WHERE STUID = @StuID";
using (IDbConnection conn = new SqlConnection(conStr))
{
var result = conn.Execute(sql, new { StuID = ID });
return Ok(ReturnJsonResult.GetJsonResult(RequestResult.请求成功, result));
}
}
#endregion
#region 事务
[HttpPost]
public IHttpActionResult AddStudentT()
{
string conStr = ConfigurationManager.AppSettings["SqlConnStr"];
string sql = @"INSERT INTO STUDENT (NAME,AGE,FK_CLASSID) VALUES (@NAME,@AGE,@CLASSID)";
StudentInfo student = new StudentInfo
{
Name = "恩格斯",
Age = 55,
FK_ClassID = 1
};
StudentInfo student2 = new StudentInfo
{
Name = "恩格斯2",
Age = 55,
FK_ClassID = 1
};
try
{
using (IDbConnection conn = new SqlConnection(conStr))
{
IDbTransaction transaction = conn.BeginTransaction();
var result = conn.Execute(sql, student);
var result1 = conn.Execute(sql, student2);
transaction.Commit();
return Ok(ReturnJsonResult.GetJsonResult(RequestResult.请求成功, result));
}
}
catch (System.Exception)
{
throw;
}
}
#endregion
}
}