当前位置:
首页 > temp > 简明python教程 >
-
C# ORM学习笔记：Dapper基本用法(3)
/// <summary>
/// 通过SQL查询数据(查询所有数据)
/// </summary>
/// <returns></returns>
public IEnumerable<Student> GetStudentList1()
{
string strSql = "SELECT * FROM Student";
using (var conn = GetCon())
{
conn.Open();
return conn.Query<Student>(strSql);
}
}
a2）通过SQL查询数据（带参数）
/// <summary>
/// 通过SQL查询数据(带参数)
/// </summary>
/// <param name="studentID"></param>
/// <returns></returns>
public Student GetStudentList1A(int studentID)
{
string strSql = "SELECT * FROM Student WHERE StudentID=@StudentID";
using (var conn = GetCon())
{
conn.Open();
return conn.Query<Student>(strSql, new { StudentID = studentID }).FirstOrDefault();
}
}
a3）通过SQL查询数据（IN）
/// <summary>
/// 通过SQL查询数据(IN)
/// </summary>
/// <param name="studentID"></param>
/// <returns></returns>
public IEnumerable<Student> GetStudentList1B(string studentID)
{
string strSql = "SELECT * FROM Student WHERE StudentID IN @StudentID";
var idArr = studentID.Split(',');
using (var conn = GetCon())
{
conn.Open();
return conn.Query<Student>(strSql, new { StudentID = idArr });
}
}
b1）通过实体查询数据（查询所有数据）
/// <summary>
/// 通过实体询数据(查询所有数据)
/// </summary>
/// <returns></returns>
public IEnumerable<Student> GetStudentList2()
{
using (var conn = GetCon())
{
conn.Open();
return conn.GetList<Student>();
}
}
b2）通过实体查询数据（指定ID）
/// <summary>
/// 通过实体询数据(指定ID)
/// </summary>
/// <param name="studentID"></param>
/// <returns></returns>
public Student GetStudentList2A(int studentID)
{
using (var conn = GetCon())
{
conn.Open();
return conn.Get<Student>(studentID);
}
}
b3）通过实体查询数据（带参数）
/// <summary>
/// 通过实体询数据(带参数)
/// </summary>
/// <param name="studentID"></param>
/// <returns></returns>
public Student GetStudentList2B(int studentID)
{
using (var conn = GetCon())
{
conn.Open();
return conn.GetList<Student>(new { StudentID = studentID }).FirstOrDefault();
}
}
c1）多表查询(QueryMultiple)，主要操作：通过QueryMultiple方法，返回查询中每条SQL语句的数据集合。
/// <summary>
/// 多表查询(QueryMultiple)
/// </summary>
/// <returns></returns>
public string GetMultiEntityA()
{
string strSql = "SELECT * FROM Student AS A;SELECT * FROM Teacher AS A";
StringBuilder sbStudent = new StringBuilder();
StringBuilder sbTeacher = new StringBuilder();
using (var conn = GetCon())
{
conn.Open();
var grid = conn.QueryMultiple(strSql);
var students = grid.Read<Student>();
var teachers = grid.Read<Teacher>();
foreach (var item in students)
{
sbStudent.Append($"StudentID={item.StudentID} Name={item.Name} Age={item.Age} Gender={item.Gender}\n");
}
foreach (var item in teachers)
{
sbTeacher.Append($"TeacherID={item.TeacherID} Name={item.Name}\n");
}
return sbStudent.ToString() + sbTeacher.ToString();
}
}
c2）多表查询(Query)，主要操作：通过SQL进行多表关联查询，返回查询结果的数据集合。
/// <summary>
/// 多表查询(Query)
/// </summary>
/// <returns></returns>
public string GetMultiEntityB()
{
string strSql = "SELECT A.Name CourseName,B.Name TeacherName FROM Course A INNER JOIN Teacher B ON A.TeacherID=B.TeacherID";
StringBuilder sbResult = new StringBuilder();
using (var conn = GetCon())
{
conn.Open();
var query = conn.Query(strSql);
query.AsList().ForEach(q =>
{
sbResult.Append($"CourseName={q.CourseName} TeacherName={q.TeacherName}\n");
});
return sbResult.ToString();
}
}
3.4、Update
a1）通过SQL更新数据(带参数)，返回结果是影响行数。
/// <summary>
/// 通过SQL更新数据(带参数)，返回结果是影响行数。
/// </summary>
/// <returns></returns>
public int? UpdateWithSqlA()
{
using (var conn = GetCon())
{
conn.Open();
string strSql = "UPDATE Student SET Name=@Name,Age=@Age,Gender=@Gender WHERE StudentID=@StudentID";
return conn.Execute(strSql, new { Name = "World3", Age = 19, Gender = "female", StudentID = 17 });
}
}
a2）通过SQL插入单条数据(带实体)，返回结果是影响行数。
/// <summary>
/// 通过SQL更新数据(带实体)，返回结果是影响行数。
/// </summary>
/// <returns></returns>
public int? UpdateWithSqlB()
{
using (var conn = GetCon())
{
conn.Open();
string strSql = "UPDATE Student SET Name=@Name,Age=@Age,Gender=@Gender WHERE StudentID=@StudentID";
Student student = new Student
{
StudentID = 17,
Name = "World3",
Age = 18,
Gender = "male"
};
return conn.Execute(strSql, student);
}
}
b）通过实体更新数据，返回结果是影响行数。
/// <summary>
/// 通过实体更新数据，返回结果是影响行数。
/// </summary>
/// <returns></returns>
public int? UpdateWithEntity()
{
using (var conn = GetCon())
{
conn.Open();
var entity = new Student { StudentID = 17, Name = "World4", Age = 18, Gender = "male" };
return conn.Update(entity);
}
}
3.5、Delete
a）通过SQL删除数据(带参数)，返回结果是影响行数。
/// <summary>
/// 通过SQL删除数据(带参数)，返回结果是影响行数。
/// </summary>
/// <returns></returns>
public int? DeleteWithSql()
{
using (var conn = GetCon())
{
conn.Open();
string strSql = "DELETE FROM Student WHERE StudentID=@StudentID";
return conn.Execute(strSql, new { StudentID = 16 });
}
}
b）通过实体删除数据，返回结果是影响行数。
/// <summary>
/// 通过实体删除数据，返回结果是影响行数。
/// </summary>
/// <returns></returns>
public int? DeleteWithEntity()
{
using (var conn = GetCon())
{
conn.Open();
var entity = new Student { StudentID = 17 };
return conn.Delete(entity);
}
}
四、Procedure
4.1、带输出参数的存储过程
CREATE PROCEDURE [dbo].[GetStudentAge] @StudentID INT, @Name NVARCHAR(50) OUTPUT AS BEGIN DECLARE @Age SMALLINT SELECT @Name=Name,@Age=Age FROM Student WHERE StudentID=@StudentID SELECT @Age END
/// <summary>
/// 带输出参数的存储过程
/// </summary>
/// <param name="studentID"></param>
/// <returns></returns>
public Tuple<string, int> GetStudentAge(int studentID)
{
int age = 0;
var para = new DynamicParameters();
para.Add("StudentID", 1);
para.Add("Name", string.Empty, DbType.String, ParameterDirection.Output);
using (var conn = GetCon())
{
conn.Open();
age = conn.Query<int>("GetStudentAge", para, commandType: CommandType.StoredProcedure).FirstOrDefault();
}
return Tuple.Create(para.Get<string>("Name"), age);
}
五、Transaction
5.1、在IDbConnection下事务，主要操作：在执行Insert方法时传入Transaction；在正常情况下Commit事务；在异常时回滚事务。
/// <summary>
/// 在IDbConnection下事务
/// </summary>
/// <returns></returns>
public bool InsertWithTran()
{
using (var conn = GetCon())
{
conn.Open();
int studentID = 0, teacherID = 0, result = 0;
var student = new Student { Name = "Sandy", Age = 18, Gender = "female" };
var teacher = new Teacher { Name = "Luci" };
var tran = conn.BeginTransaction();
try
{
studentID = conn.Insert(student, tran).Value;
result++;
teacherID = conn.Insert(teacher, tran).Value;
result++;
tran.Commit();
}
catch
{
result = 0;
tran.Rollback();
}
return result > 0;
}
}
5.2、在存储过程下事务，主要操作：在存储过程中进行事务；通过DynamicParameters传递参数给存储过程；通过Query调用存储过程。
CREATE PROCEDURE [dbo].[InsertData] --Student @StudentName NVARCHAR(50), @Age SMALLINT, @Gender NVARCHAR(10), --Teacher @TeacherName NVARCHAR(50) AS BEGIN --变量定义 DECLARE @Result BIT=1 --结果标识 --事务开始 BEGIN TRANSACTION --数据插入 INSERT INTO Student (Name,Age,Gender) VALUES (@StudentName,@Age,@Gender) INSERT INTO Teacher (Name) VALUES (@TeacherName) --事务执行 IF @@ERROR=0 BEGIN COMMIT TRANSACTION END ELSE BEGIN SET @Result=0 ROLLBACK TRANSACTION END --结果返回 SELECT @Result END
/// <summary>
/// 在存储过程下事务
/// </summary>
/// <returns></returns>
public bool InsertWithProcTran()
{
var para = new DynamicParameters();
para.Add("StudentName", "Hanmeimei");
para.Add("Age", 18);
para.Add("Gender", "female");
para.Add("TeacherName", "Angel");
using (var conn = GetCon())
{
conn.Open();
return conn.Query<bool>("InsertData", para, commandType: CommandType.StoredProcedure).FirstOrDefault();
}
}
