Dapper 存储过程
EF 存储过程
● 存储过程 Procedure 是一组为了完成特定功能的 SQL 语句集合，经编译后存储在数据库中，一次编译后永久有效，用户通过指定存储过程的名称并给出参数来执行。
● 存储过程中可以包含逻辑控制语句和数据操纵语句，它可以接受参数、输出参数、返回单个或多个结果集以及返回值。
【存储过程-proc_user_select】动态添加 where 条件，分页，output【存储过程】
CREATE PROCEDURE [dbo].[proc_user_select]
--@department_code int,
@equipment_code int,
@start_time datetime,
@end_time datetime,
--@page int=1,
--@per_page int,
--@order nvarchar(200),
@total int output,
@page_count int output,
@msg nvarchar(60) output
AS
BEGIN
declare @sqlString1 nvarchar(666)
declare @sqlString2 nvarchar(666)
declare @sqlString3 nvarchar(666)
declare @sqlWhere nvarchar(666) = N''
declare @sql_total_string nvarchar(666) = N''
declare @user_name nvarchar(500) = 'a'
declare @page int = 2
declare @per_page int =10
declare @order nvarchar(666) = 'user_code'
declare @skip int
set @skip = (case when @page-1 <= 0 then 0 else @page-1 end) * @per_page
if (@order = '')
begin
set @order = 'user_code'
end
if (@user_name ! = '')
begin
set @sqlWhere = @sqlWhere + 'and user_name like ''%'+ @user_name +'%'' '
end
if (@start_time is not null and @end_time is not null)
begin
set @sqlWhere += 'and created_time between ''' + convert(nvarchar(10),@start_time,23) +''' and ''' + convert(nvarchar(10),dateadd(day,1,@end_time),120) + ''' '
end
set @sqlString1 = 'select * from sys_user where 1=1 '+ @sqlWhere +' ';
set @sqlString2 = N'select row_number() over (order by '+ @order +') as RowNumber,* from sys_user where 1=1 '+ @sqlWhere +' ';
set @sqlString3 = N'select top '+ convert(nvarchar(10),@per_page) +' * from (select row_number() over(order by '+ @order +' desc) AS RowNumber,* from sys_user where 1=1 '+ @sqlWhere +') record where RowNumber > '+ convert(nvarchar(10),@skip) +''
set @sql_total_string = N'select @total = count(*) from sys_user where 1=1 '+ @sqlWhere
print @sqlString1
print @sqlString2
print @sqlString3
exec sp_executesql @sql_total_string,N'@total int out',@total output
set @page_count = ceiling(@total*1.0/@per_page)
exec(@sqlString3)
END
GO【存储过程-proc_department_select】动态添加 where 条件，分页，排序，统计总条数，统计总页数，output【存储过程】
CREATE PROCEDURE [dbo].[proc_department_select]
@department_code int,
@department_name nvarchar(200),
@page int,
@per_page int,
@order nvarchar(200),
@total int output,
@page_count int output
AS
BEGIN
declare @sql_string nvarchar(2000)
declare @sql_total_string nvarchar(2000)
declare @sql_where nvarchar(2000) = N''
declare @skip int
set @skip = (case when @page-1 <= 0 then 0 else @page-1 end) * @per_page
if (@order = '')
begin
set @order = 'department_code'
end
if(@department_code > 0)
begin
set @sql_where += 'and department_code =' + @department_code
end
set @sql_total_string = N'select @total = count(*) from sys_department where 1=1 '+ @sql_where
set @sql_string = N'select top '+ convert(nvarchar(10),@per_page) +' * from (select row_number() over(order by '+ @order +' desc) AS RowNumber,* from sys_department where 1=1 '+ @sql_where +') record where RowNumber > '+ convert(nvarchar(10),@skip) +''
exec sp_executesql @sql_total_string,N'@total int out',@total output
set @page_count = ceiling(@total * 1.0 / @per_page)
exec(@sql_string)
END【存储过程-proc_statistics_workload】参数允许为空，动态添加 where 条件，分页，排序，统计总条数，统计总页数，offset/fetch next，output【存储过程】
CREATE PROCEDURE [dbo].[proc_statistics_workload]
@department_code nvarchar(20) = null,
@department_name nvarchar(200) = null,
@user_code nvarchar(20) = null,
@user_name nvarchar(20) = null,
@start_time datetime = null,
@end_time datetime = null,
@page int = 1,
@per_page int,
@total int output,
@page_count int output
AS
BEGIN
declare @sql_string nvarchar(2000)
declare @sql_total_string nvarchar(2000)
declare @sql_where nvarchar(500) = N''
declare @sql_where_user nvarchar(500) = N''
declare @skip int
set @skip = (case when @page-1 <= 0 then 0 else @page-1 end) * @per_page
if(@department_code<>'0')
begin
set @sql_where_user += 'and u.DEPARTMENTID ''%' + @department_code + '%'' '
end
if(@department_name <> '')
begin
set @sql_where_user += 'and u.DEPARTMENT_NAME like ''%' + @department_name + '%'' '
end
if (@user_code <> '')
begin
set @sql_where_user += 'and u.ACCOUNT like ''%' + @user_code + '%'' '
end
if (@user_name <> '')
begin
set @sql_where_user += 'and u.REALNAME like ''%' + @user_name + '%'' '
end
if(@start_time is not null and @end_time is not null)
begin
set @sql_where += 'and CREATE_DATE between ''' + convert(nvarchar(10),@start_time,23) +''' and ''' + convert(nvarchar(10),dateadd(day,1,@end_time),23) + ''' '
end
set @sql_string = N'
select t.total,t.DOCTOR_ID as doctor_id,u.REALNAME as real_name,u.DEPARTMENT_NAME as department_name
from (select count(*) total,DOCTOR_ID from ORG_PATIENT where 1=1 '+ @sql_where +' group by DOCTOR_ID) t
left join SYS_USER u on u.ACCOUNT = t.DOCTOR_ID
where 1=1 '+ @sql_where_user +'
order by CREATE_TIME desc
offset '+ convert(nvarchar(10),@skip) +' rows
fetch next '+ convert(nvarchar(10),@per_page) +' rows only'
set @sql_total_string = N'select @total = count(*) from ('+ @sql_string +') t1'
print @sql_total_string
print @sql_string
exec sp_executesql @sql_total_string,N'@total int out',@total output
set @page_count = ceiling(@total * 1.0 / @per_page)
exec(@sql_string)
END
GO【Dapper】动态添加 where 条件，分页，排序，统计总条数，统计总页数，output【Dapper】
public PageResult<List<DepartmentDto>> GetProcPageList(string name, PageDto page)
{
int pageCount = 0;
int total = 0;
PageResult<List<DepartmentDto>> result = null;
List<DepartmentDto> list = new List<DepartmentDto>();
#region 存储过程
DynamicParameters paraDept = new DynamicParameters();
paraDept.Add("@department_code", 0);
paraDept.Add("@department_name", "");
paraDept.Add("@page", page.page);
paraDept.Add("@per_page", page.per_page);
paraDept.Add("@order", "");
paraDept.Add("@total", 0, DbType.Int32, ParameterDirection.Output);
paraDept.Add("@page_count", 0, DbType.Int32, ParameterDirection.Output);
list = _conn.Query<DepartmentDto>(ConfigManager.Configuration["DbSql:Department:ProcSelectPage"], paraDept, commandType: CommandType.StoredProcedure).ToList();
pageCount = paraDept.Get<int>("@page_count");
total = paraDept.Get<int>("@total");
#endregion
result = new PageResult<List<DepartmentDto>>(list, total, pageCount);
return result;
}
动态添加 where 条件，分页，排序，统计总条数，统计总页数，output【appsettings.json】
"DbSql": {
"DbType": "SqlServer",
"SqlServerConnection": "Server=.;Database=dbTest;User ID=sa;Password=000000;",
"Department": {
"ProcSelectPage": "proc_department_select"
}
}无参数，output【存储过程】
CREATE PROCEDURE proc_User_select
@page_count int output,
@total int output
AS
BEGIN
declare @SqlString nvarchar(2000)
declare @sqlWhere nvarchar(500)
SELECT * from sys_user
END
GO
--执行存储过程SQL语句
execute proc_User_select
exec proc_User_select
GO有参数，output【存储过程】
CREATE PROCEDURE proc_NurseOnDuty_Select
@start datetime,
@end datetime,
@departmentCode int,
@page_count int output,
@total int output
AS
BEGIN
declare @SqlString nvarchar(2000)
declare @sqlWhere nvarchar(500)
SELECT * from sys_user
END
GO
--执行存储过程SQL语句
execute proc_NurseOnDuty_Select @start = N'2020-02-10', @end = N'2020-02-20', @departmentCode = 1
exec proc_NurseOnDuty_Select @start = N'2020-02-10', @end = N'2020-02-20', @departmentCode = 1有返回值，@@IDENTITY，SCOPE_IDENTITY()【insert - 存储过程】
CREATE PROCEDURE proc_VerifyCode_insert
@verify_code int output
AS
BEGIN
INSERT INTO sys_verify_code
(verify_type,code,phone,user_code,modify_code,modify_name,modify_time,[status])
VALUES
(1,'123456','123',1,1,'123','2020-02-02',1)
select @verify_code = @@IDENTITY;
--set @verify_code = @@IDENTITY;
--return SCOPE_IDENTITY(); --作用域不同
--return @@IDENTITY; --作用域不同
END
GO
execute proc_VerifyCode_insert
exec proc_VerifyCode_insert
GO删除 - 存储过程（输出参数，output）
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[proc_menu_delete]
@menu_code int,
@code int output,
@msg nvarchar(20) output
AS
BEGIN
declare @count int
set @count = (select count(*) from sys_menu where parent_code = @menu_code)
if(@count <= 0)
begin
delete from sys_menu where menu_code=@menu_code
set @code = 1
set @msg = '成功'
end
else
begin
set @code = 2
set @msg = '失败'
end
END删除 - 执行存储过程
DECLARE @return_value int,
@code int,
@msg nvarchar(20)
EXEC @return_value = proc_menu_delete @menu_code = 1, @code = @code OUTPUT, @msg = @msg OUTPUT
SELECT @code as N'@code', @msg as N'@msg'
SELECT 'Return Value' = @return_value删除 - dapper
public int DeleteMenu(int id)
{
var parameters = new DynamicParameters();
parameters.Add("@menu_code", id);
parameters.Add("@code", 0, DbType.Int32, ParameterDirection.Output);
parameters.Add("@msg", "", DbType.String, ParameterDirection.Output);
int result = _conn.Execute("proc_menu_delete", parameters, commandType: CommandType.StoredProcedure);
int code = parameters.Get<int>("@code");
string name = parameters.Get<string>("@msg");
return result;
}Dapper调用存储过程
private void Exec()
{
///执行 insert 存储过程
var insert = _conn.Execute("proc_NurseOnDuty_Select",
new
{
start = "2020-02-10",
end = "2020-02-11",
departmentCode = 1
}, commandType: CommandType.StoredProcedure);
///执行 insert 存储过程 ReturnValue
DynamicParameters para = new DynamicParameters();
para.Add("@start", "2020-02-10");
para.Add("@end", "2020-02-11");
para.Add("@id", 0, DbType.Int32, ParameterDirection.ReturnValue);
para.Add("@id", "", DbType.String, ParameterDirection.ReturnValue);
para.Add("@id", null, DbType.Int32, ParameterDirection.ReturnValue);
para.Add("@id", null, DbType.String, ParameterDirection.ReturnValue);
var insert = _conn.Execute("proc_NurseOnDuty_Select", para, commandType: CommandType.StoredProcedure);
///执行 查询 存储过程
var userList = _conn.Query<sys_role>("proc_role_select",
new
{
start = "2020-02-10",
end = "2020-02-11",
departmentCode = 1
}, commandType: CommandType.StoredProcedure).ToList();
var result = _conn.Query<object>("proc_NurseOnDuty_Select",
new
{
start = "2020-02-10",
end = "2020-02-11",
departmentCode = 1
}, commandType: CommandType.StoredProcedure).ToList();
}
EF 存储过程
1、FromSql：结果不能有关联关系数据。相当于不能 join ，也返回不了 join 的关联表的数据。
2、FromSqlInterpolated
3、ExecuteSqlRaw
4、ExecuteSqlInterpolated
5、ExecuteSqlCommand：执行插入、更新跟删除的存储过程不能直接映射到实体【弃用的】。
6、出参【ParameterDirection.Output】
7、SqlQuery
传参方式【Output出参】
public async Task<ArrayList> GetSevenDays()
{
#region 参数
List<DbParameter> param = new List<DbParameter>();
param.Add(new SqlParameter("@HosId", ""));
#endregion
#region 参数
SqlParameter[] parameters = new SqlParameter[1];
SqlParameter paramIn = new SqlParameter("currentDate", "");
#endregion
#region FromSqlRaw Sql语句
var model = Context.User.FromSqlRaw("select * from sys_user where @Id = 1").SingleOrDefault();
var data = Context.User.FromSqlRaw("select * from sys_user where code = 'ql'").AsNoTracking().FirstOrDefault();
#endregion
#region FromSqlRaw 存储过程 参数
var list = Context.User.FromSqlRaw("exec proc_age_select @Id = 1").ToList();
var a = Context.SequenceResult.FromSqlRaw("exec [dbo].[proc_user_select] @count=1,@code=QL").AsNoTracking().ToList();
var b = Context.SequenceResult.FromSqlRaw("exec [dbo].[proc_user_select] @count=1,@code=QL").AsEnumerable().FirstOrDefault();
var c = Context.User.FromSqlRaw("exec [dbo].[proc_select]").ToList();
var code = new SqlParameter("code", "QL");
var count1 = new SqlParameter("count", 1);
var e = Context.SequenceResult.FromSqlRaw("exec [dbo].[proc_sequence_select] @count,@code", count1, code).AsEnumerable().FirstOrDefault();
SqlParameter[] para = new SqlParameter[] {
new SqlParameter("count",1),
new SqlParameter("code","QL")
};
var f = Context.SequenceResult.FromSqlRaw("exec [dbo].[proc_sequence_select] @count,@code", para).AsEnumerable().FirstOrDefault();
#endregion
#region FromSqlInterpolated
var d = Context.User.FromSqlInterpolated("SELECT * FROM sys_user").ToList();
#endregion
#region ExecuteSqlInterpolated
Context.Database.ExecuteSqlInterpolated("select * from sys_user");
#endregion
#region ExecuteSqlRaw
Context.Database.ExecuteSqlRaw("select * from sys_user");
Context.Database.ExecuteSqlRaw("select * from sys_user where name=@name and id=@id", new[]
{
new SqlParameter("name", "tom1"),
new SqlParameter("id", 1),
});
Context.Database.ExecuteSqlRawAsync("exec SP_RRE_POST_HOSPITAL_RECORES");
#endregion
#region ExecuteSqlCommand
SqlParameter[] para = new SqlParameter[] {
new SqlParameter("@ID","")
};
Context.Database.ExecuteSqlCommand("delete sys_user where id=@ID", para);
Context.Database.ExecuteSqlCommand("sp_user_deleteByID @ID", para);
#endregion
#region ExecuteSqlCommand
SqlParameter pp_name = new SqlParameter("@Name", "");
SqlParameter pp_phone = new SqlParameter("@Phone", "");
Context.Database.ExecuteSqlCommand("exec [proc_AddSysUser01] @Name,@Phone", pp_name, pp_phone);
Context.Database.ExecuteSqlCommand($"update sys_user set name=@name where id=@id", new[]
{
new SqlParameter("name", "tom1"),
new SqlParameter("id", 1),
});
#endregion
#region ExecuteSqlCommand
var name = "C# 高级进阶";
var author = "-";
var cateid = 1;
Context.Database.ExecuteSqlCommand("proc_createbook @p0,@p1,@p2", name, author, cateid);
#endregion
#region 自定义方法执行存储过程
string userId = "123";
SqlParameter[] sqlParameters =
{
new SqlParameter("@UserId", System.Data.SqlDbType.VarChar),
new SqlParameter("@total", SqlDbType.Int,4),
new SqlParameter("@per_page", SqlDbType.Int)
};
if (string.IsNullOrEmpty(userId))
sqlParameters[0].Value = DBNull.Value;
else
sqlParameters[0].Value = userId;
sqlParameters[1].Direction = ParameterDirection.Output;
sqlParameters[2].Direction = ParameterDirection.Output;
int total = Convert.ToInt32(sqlParameters[1].Value);
int pageCount = Convert.ToInt32(sqlParameters[2].Value);
await DbHelperSQL.ExecSpAsync(Context, "proc_seven_days_select", sqlParameters);
#endregion
#region 自定义方法执行存储过程【出参】
List<DbParameter> param = new List<DbParameter>();
param.Add(new SqlParameter("@department_code", request.department_code));
param.Add(new SqlParameter("@department_name", request.department_name));
param.Add(new SqlParameter("@user_code", request.user_code));
param.Add(new SqlParameter("@user_name", request.user_name));
param.Add(new SqlParameter("@start_time", request.start_time));
param.Add(new SqlParameter("@end_time", request.end_time));
param.Add(new SqlParameter("@page", request.page));
param.Add(new SqlParameter("@per_page", request.limit));
SqlParameter paramTotal = new SqlParameter("@total", SqlDbType.Int);
paramTotal.Direction = ParameterDirection.Output;
SqlParameter paramPageCount = new SqlParameter("@page_count", SqlDbType.Int);
paramPageCount.Direction = ParameterDirection.Output;
param.Add(paramTotal);
param.Add(paramPageCount);
var resultExpression = DbHelperSQL.ExecSpAsync(Context,"proc_statistics_workload", param.ToArray());
//根据List<DbParameter> param集合数量，从0开始计数
int total = Convert.ToInt32(param[8].Value);
int pageCount = Convert.ToInt32(param[9].Value);
#endregion
#region
dbContext.Database.SqlQuery<CarExpendSettlementByIncomeOutPut>($"{sql}");
#endregion
return await DbHelperSQL.ExecSpAsync(Context, "proc_seven_days_select", null);
}
ExtendDbContext.cs
using Microsoft.Data.SqlClient;
using Microsoft.EntityFrameworkCore;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.Common;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Text;
namespace Nuclein.Repository
{
public static class ExtendDbContext
{
/// <summary>
/// 执行SQL返回受影响的行数
/// </summary>
public static int ExecSqlNoQuery<T>(this NucleinDbContext db, string sql, SqlParameter[] sqlParams = null) where T : new()
{
return ExecuteNoQuery<T>(db, sql, sqlParams);
}
/// <summary>
/// 执行存储过程返回IEnumerable数据集
/// </summary>
public static IEnumerable<T> ExecProcReader<T>(this NucleinDbContext db, string sql, SqlParameter[] sqlParams = null) where T : new()
{
return Execute<T>(db, sql, CommandType.StoredProcedure, sqlParams);
}
/// <summary>
/// 执行sql返回IEnumerable数据集
/// </summary>
public static IEnumerable<T> ExecSqlReader<T>(this NucleinDbContext db, string sql, SqlParameter[] sqlParams = null) where T : new()
{
return Execute<T>(db, sql, CommandType.Text, sqlParams);
}
private static int ExecuteNoQuery<T>(this NucleinDbContext db, string sql, SqlParameter[] sqlParams) where T : new()
{
DbConnection connection = db.Database.GetDbConnection();
DbCommand cmd = connection.CreateCommand();
int result = 0;
db.Database.OpenConnection();
cmd.CommandText = sql;
cmd.CommandType = CommandType.Text;
if (sqlParams != null)
{
cmd.Parameters.AddRange(sqlParams);
}
result = cmd.ExecuteNonQuery();
db.Database.CloseConnection();
return result;
}
private static IEnumerable<T> Execute<T>(this NucleinDbContext db, string sql, CommandType type, SqlParameter[] sqlParams) where T : new()
{
DbConnection connection = db.Database.GetDbConnection();
DbCommand cmd = connection.CreateCommand();
db.Database.OpenConnection();
cmd.CommandText = sql;
cmd.CommandType = type;
if (sqlParams != null)
{
cmd.Parameters.AddRange(sqlParams);
}
DataTable dt = new DataTable();
using (DbDataReader reader = cmd.ExecuteReader())
{
dt.Load(reader);
}
db.Database.CloseConnection();
return dt.ToCollection<T>();
}
public static async IAsyncEnumerable<string> ReadAllLines(string file)
{
using (var fs = File.OpenRead(file))
{
using (var sr = new StreamReader(fs))
{
while (true)
{
string line = await sr.ReadLineAsync();
if (line == null)
{
break;
}
yield return line;
}
}
}
}
}
}
public static class ExtendDataTable
{
public static DataTable ToDataTable<T>(this IEnumerable<T> data)
{
PropertyDescriptorCollection properties = TypeDescriptor.GetProperties(typeof(T));
var table = new DataTable();
foreach (PropertyDescriptor prop in properties)
table.Columns.Add(prop.Name, Nullable.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType);
foreach (T item in data)
{
DataRow row = table.NewRow();
foreach (PropertyDescriptor prop in properties)
row[prop.Name] = prop.GetValue(item) ?? DBNull.Value;
table.Rows.Add(row);
}
return table;
}
public static T ToEntity<T>(this DataTable dt) where T : new()
{
IEnumerable<T> entities = dt.ToCollection<T>();
return entities.FirstOrDefault();
}
public static IEnumerable<T> ToCollection<T>(this DataTable dt) where T : new()
{
if (dt == null || dt.Rows.Count == 0)
{
return Enumerable.Empty<T>();
}
IList<T> ts = new List<T>();
// 获得此模型的类型
Type type = typeof(T);
string tempName = string.Empty;
foreach (DataRow dr in dt.Rows)
{
T t = new T();
PropertyInfo[] propertys = t.GetType().GetProperties();
foreach (PropertyInfo pi in propertys)
{
tempName = pi.Name;
//检查DataTable是否包含此列（列名==对象的属性名）
if (dt.Columns.Contains(tempName))
{
// 判断此属性是否有Setter
if (!pi.CanWrite) continue;//该属性不可写，直接跳出
object value = dr[tempName];
if (value != DBNull.Value)
pi.SetValue(t, value, null);
}
}
ts.Add(t);
}
return ts;
}
}DbHelperSQL.cs
using Microsoft.Data.SqlClient;
using Microsoft.EntityFrameworkCore;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Text;
using System.Threading.Tasks;
namespace Nuclein.Repository
{
public static class DbHelperSQL
{
/// <summary>
/// 查询
/// </summary>
/// <returns></returns>
public async static Task<ArrayList> ExecSpAsync(this NucleinDbContext db, string sql, SqlParameter[] sqlParams)
{
var connection = db.Database.GetDbConnection();
using (var cmd = connection.CreateCommand())
{
await db.Database.OpenConnectionAsync();
cmd.CommandText = sql;
cmd.CommandType = System.Data.CommandType.StoredProcedure;
if(sqlParams!=null)
cmd.Parameters.AddRange(sqlParams);
var dr = await cmd.ExecuteReaderAsync();
var columnSchema = dr.GetColumnSchema();
var data = new ArrayList();
while (await dr.ReadAsync())
{
var item = new Dictionary<string, object>();
foreach (var kv in columnSchema)
{
if (kv.ColumnOrdinal.HasValue)
{
var itemVal = dr.GetValue(kv.ColumnOrdinal.Value);
item.Add(kv.ColumnName, itemVal.GetType() != typeof(DBNull) ? itemVal : "");
}
}
data.Add(item);
}
dr.Dispose();
return data;
}
}
/// <summary>
/// 增删改
/// </summary>
/// <returns></returns>
public async static Task<int> ExecuteNonQueryAsync(this NucleinDbContext db, string sql, SqlParameter[] sqlParams)
{
int numint;
var connection = db.Database.GetDbConnection();
using (var cmd = connection.CreateCommand())
{
await db.Database.OpenConnectionAsync();
cmd.CommandText = sql;
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.AddRange(sqlParams);
numint = await cmd.ExecuteNonQueryAsync();
}
return numint;
}
}
}
*
*
*
*
*