brief introduction
This article is a learning instance of. net mvc + Dapper. There is a link to the source code of the instance.
Why write this article
Because I don't like EF's bloated and Linq's awkward, I used my own lightweight orm in previous projects. In thinking about and drawing on other ORM frameworks, Dapper found. Dapper's introduction to Du Niang is a lot. I won't introduce it here.
Don't talk much, read essays
- Create MVC project
- Get Dapper through Nuget
Search and install Dapper, test Dapper first, then SimpleCRUD
Dapper right.net The Framework version is limited, so please note that the project I created is 4.5.1.
- Code
- data base
/***Table * **/
CREATE TABLE Users
(
Id INT PRIMARY KEY IDENTITY(1,1),
UserName NVARCHAR(20),
UserPWD NVARCHAR(32),
NickName NVARCHAR(20),
UserStatus int,
LastLoginOn DATETIME
)
GO
/************************
Normal stored procedure
*************************/
CREATE PROC proc_GetUsers
AS
BEGIN
SELECT * FROM Users;
END
GO
/**************************
Stored procedure with parameters
***************************/
CREATE PROC proc_GetUsers_OutParam
(
@Id INT,
@UserName NVARCHAR(20) output
)
AS
BEGIN
SELECT @UserName=UserName FROM Users WHERE Id=@Id;
END
-
Physical layer
Create Users class in Model layer
public class Users
{
/// <summary>
///Primary key auto increment
/// </summary>
public int? Id { get; set; }
/// <summary>
//Username
/// </summary>
public string UserName { get; set; }
/// <summary>
//Password
/// </summary>
public string UserPWD { get; set; }
/// <summary>
/// name
/// </summary>
public string NickName { get; set; }
/// <summary>
//Status
/// </summary>
public int? UserStatus { get; set; }
/// <summary>
///Last landing time
/// </summary>
public DateTime? LastLoginOn { get; set; }
}
- Service layer
/// <summary>
///User services
///Dapper framework services
/// </summary>
public class Users_Biz
{
const string connstr = "Data Source=.;Initial Catalog=DBTest;User Id=sa;Password=123456;";
//private readonly string connstr =
// "Data Source=.;Initial Catalog=DBTest;User Id=sa;Password=123456;";
/// <summary>
/// add
/// </summary>
///< param name = "model" > user object < / param >
public static int Add(Users model)
{
int count = 0;
using (IDbConnection conn=new SqlConnection(connstr))
{
string sql = "insert into Users(UserName,UserPWD,NickName,UserStatus,LastLoginOn) values(@UserName,@UserPWD,@NickName,@UserStatus,@LastLoginOn)";
count =conn.Execute(sql, new { UserName = model.UserName, UserPWD = model.UserPWD, NickName = model.NickName, model.UserStatus, model.LastLoginOn });
}
return count;
}
/// <summary>
///Get list
/// </summary>
/// <returns></returns>
public static List<Users> GetList()
{
List<Users> list = null;
using (IDbConnection conn = new SqlConnection(connstr))
{
string sql = "select * from Users";
list = conn.Query<Users>(sql).ToList();
}
return list;
}
/// <summary>
///Find by Id
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public static Users GetModel(int id)
{
Users model = null;
using (IDbConnection conn = new SqlConnection(connstr))
{
string sql = "select * from Users where Id=@Id";
model = conn.QueryFirstOrDefault<Users>(sql, new { Id = id });
}
return model;
}
/// <summary>
///Find the last
/// </summary>
/// <returns></returns>
public static Users GetLastModel()
{
Users model = null;
using (IDbConnection conn = new SqlConnection(connstr))
{
string sql = "select * from Users order by Id desc";
model = conn.QueryFirstOrDefault<Users>(sql);
}
return model;
}
/// <summary>
///Total number of queries
/// </summary>
/// <returns></returns>
public static int GetCount()
{
int count = 0;
using (IDbConnection conn = new SqlConnection(connstr))
{
string sql = "select count(1) from Users";
count = (int)conn.ExecuteScalar(sql);
}
return count;
}
/// <summary>
/// query List
///Call normal stored procedure
/// </summary>
/// <returns></returns>
public static List<Users> GetList_Proc()
{
List<Users> list = null;
using (IDbConnection conn = new SqlConnection(connstr))
{
list = conn.Query<Users>("proc_GetUsers", null, null, true,null, CommandType.StoredProcedure).ToList();
}
return list;
}
/// <summary>
///Query UserName by Id
///Stored procedure with return parameters
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public static string GetUserName_Proc(int id)
{
int count = 0;
string userName = string.Empty ;
using (IDbConnection conn = new SqlConnection(connstr))
{
//Set parameter set
DynamicParameters dp = new DynamicParameters();
dp.Add("@Id", id);
dp.Add("@UserName", userName, DbType.String, ParameterDirection.Output);
//Carry out the travel process
count = conn.Execute("proc_GetUsers_OutParam", dp, null, null, CommandType.StoredProcedure);
//Get parameters
userName = dp.Get<string>("@UserName");
}
return userName;
}
}
- Controller layer
public ActionResult Index()
{
//1 NEW
Users model = new Users()
{
UserName = "Seven",
UserPWD = "13333",
NickName = "Severn",
UserStatus = 1,
LastLoginOn = DateTime.Now
};
int addRes = Users_Biz.Add(model);
//2 query list
List<Users> list= Users_Biz.GetList();
//3 query by Id
model = Users_Biz.GetModel(1);
//4 NEW
model.UserName = "Eleven";
model.UserPWD = "88886666";
model.NickName = "Yi Lai Wen";
addRes = Users_Biz.Add(model);
//5 query the last
model = Users_Biz.GetLastModel();
//6 total number of searches
int count = Users_Biz.GetCount();
//7 call normal stored procedure
list = Users_Biz.GetList_Proc();
//8 calling stored procedures with return parameters
string userName = Users_Biz.GetUserName_Proc(1);
string json= JsonConvert.SerializeObject(new {
//Add 1=addRes,
//Query list = list,
//Query = model by Id,
//Total number of lookups = count,
//Calling stored procedure with return parameter = userName
});
return Content(json);
}
So far, the basic operation of Dapper has been completed, which seems to be relatively simple.
But we will find that one of them needs to write SQL statements, which is very uncomfortable.
If you want to automatically splice SQL through reflection, you can complete entity operations without writing SQL.
In Nuget, * * * Dapper.SimpleCRUD * * is such a class library.
Let's write the service class again:
public class Users_BizEx
{
const string connstr = "Data Source=.;Initial Catalog=DBTest;User Id=sa;Password=123456;";
//private readonly string connstr =
// "Data Source=.;Initial Catalog=DBTest;User Id=sa;Password=123456;";
/// <summary>
/// add
/// </summary>
///< param name = "model" > user object < / param >
public static int Add(Users model)
{
int? count = 0;
using (IDbConnection conn = new SqlConnection(connstr))
{
count = conn.Insert<Users>(model);
}
return count.Value;
}
/// <summary>
///Get list
/// </summary>
/// <returns></returns>
public static List<Users> GetList()
{
List<Users> list = null;
using (IDbConnection conn = new SqlConnection(connstr))
{
list = conn.GetList<Users>().ToList();
}
return list;
}
/// <summary>
///Find by Id
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public static Users GetModel(int id)
{
Users model = null;
using (IDbConnection conn = new SqlConnection(connstr))
{
model = conn.Get<Users>(id);
}
return model;
}
/// <summary>
///Find last (paged query)
/// </summary>
/// <returns></returns>
public static Users GetLastModel()
{
Users model = null;
using (IDbConnection conn = new SqlConnection(connstr))
{
model = conn.GetListPaged<Users>(1, 1, string.Empty, "Id desc").ToList().FirstOrDefault();
}
return model;
}
/// <summary>
///Total number of queries
/// </summary>
/// <returns></returns>
public static int GetCount()
{
int count = 0;
using (IDbConnection conn = new SqlConnection(connstr))
{
count = conn.RecordCount<Users>(string.Empty);
}
return count;
}
/// <summary>
/// query List
///Call normal stored procedure
/// </summary>
/// <returns></returns>
public static List<Users> GetList_Proc()
{
List<Users> list = null;
using (IDbConnection conn = new SqlConnection(connstr))
{
list = conn.Query<Users>("proc_GetUsers", null, null, true, null, CommandType.StoredProcedure).ToList();
}
return list;
}
/// <summary>
///Query UserName by Id
///Stored procedure with return parameters
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public static string GetUserName_Proc(int id)
{
int count = 0;
string userName = string.Empty;
using (IDbConnection conn = new SqlConnection(connstr))
{
//Set parameter set
DynamicParameters dp = new DynamicParameters();
dp.Add("@Id", id);
dp.Add("@UserName", userName, DbType.String, ParameterDirection.Output);
//Carry out the travel process
count = conn.Execute("proc_GetUsers_OutParam", dp, null, null, CommandType.StoredProcedure);
//Get parameters
userName = dp.Get<string>("@UserName");
}
return userName;
}
}
summary
Dapper is a lightweight ORM. If you are interested in the source code of this class library, you can go to git.
Project source code
Baidu SkyDrive
Links: https://pan.baidu.com/s/1KIqZHjLYtU1oiIUgWysyjw
Extraction code: 8tjj