AY的Dapper研究学习-继续深入-C#开发-aaronyang技术分享
原文:AY的Dapper研究学习-继续深入-C#开发-aaronyang技术分享
====================www.ayjs.net 杨洋 wpfui.com ayui ay aaronyang=======请不要转载谢谢了。=========
不想定义属性,直接索引方式用,Dynamic方式
var _1 = con.Query("SELECT * FROM USERS").ToArray();
Console.WriteLine(_1[0].UserName + " EMAIL: " + _1[0].Email);
带参数的存储过程
create proc _ay
@address varchar(200) output,
@id int
as
Select @address=[Address] from [Users] WHERE USERID=@id
GO
数据库执行上面sql。
然后数据库中执行
DECLARE @address varchar(200)
exec _ay @address output,@id=1
print '--------------------------------------------------'
print @address
番外,带 参数的存储过程,其他TSQL存储过程参考
if object_id('up_user') is not null
drop proc up_user
go
create proc up_user
as
set nocount on
delcare @age int
begin
select @age=uage from user
return @age
end
set nocount off
go
--执行该存储过程
declare @age int
exec @age=up_user
select @age
代码:
var p = new DynamicParameters();
p.Add("@id", dbType: DbType.Int32, direction: ParameterDirection.Input,value:1);
p.Add("@address", dbType: DbType.String, direction: ParameterDirection.Output,size:200);
con.Execute("_ay", p, commandType: CommandType.StoredProcedure);
string b = p.Get<string>("@address");
Console.WriteLine(b);
这一块,一直报什么size错误,原来我存储过程设置output变量时候,设置了长度,所以要指定。
这里有4种类型呢。。returnValue我知道,inputoutput我就不清楚了。
关于参数这块呢,用法很多哎。。我们以前demo的 参数,都是匿名对象,或者类对象,现在可以像下面那样,有多个
var p2 = new DynamicParameters();
p2.Add("id", 1, System.Data.DbType.String);
var _q = con.QueryFirstOrDefault<Users>("select * from Users Where UserId=@id", p2);
Console.WriteLine(_q.UserName);
var p3 = new DynamicParameters();
p3.Add("cnt", System.Data.DbType.Int32,direction: ParameterDirection.Output);
var _q = con.Query<Users>("select @cnt=count(*) from Users Where UserName like '%ay%'", p3);
int cnt = p3.Get<int>("@cnt");
Console.WriteLine(cnt);
以上东西,都是根据Dapper的源码下,Test项目,自己看,改出来的。
当然有的存储过程,返回的类型,种类很多,比如有的还是 数组,集合,地理信息 等,具体可以看dapper的源码下的Test学习。
还有个比较常用的sql,返回数量
var _q = con.Query<int>("select count(*) from Users Where UserName like '%ay%'").First();
Console.WriteLine(_q);
当列使用了AS,要不然泛型的话,加个你as的列名的属性,要不然就用dynamic方式
var _q = con.QueryFirstOrDefault("select UserName as UN from Users Where UserId=@id", new { id=1});
Console.WriteLine(_q.UN);
参数创建,还可以使用
1
dynamic expando = new ExpandoObject();
expando.A = 1;
expando.B = "two";
var p = new DynamicParameters();
p.AddDynamicParams(expando);
2
var p = new DynamicParameters();
var list = new int[] { 1, 2, 3 };
var args = new Dictionary<string, object> { ["ids"] = list };
p.AddDynamicParams(args);
3
var p = new DynamicParameters();
var list = new int[] { 1, 2, 3 };
p.AddDynamicParams(new { list });
关于事物 这块
虽然,这里 第二行输出,貌似是改成功的
但是由于事务没提交,所以数据库不变的。我们回滚下,再查一下,是ay了。
现在提交事务,已经更改了。
发现事务执行完,sql的管理工具,可以继续执行了,不然sql执行,貌似被事务阻塞了。
修改下代码, Commit后的查询,不需要在加上 transaction:tran参数饿了。
感觉还有分页哦。。
打开SQLSERVER,执行 存储过程脚本
Create PROCEDURE [dbo].[ProcGetPageData]
( @TableName VARCHAR(1000), --表名,多表是请使用 tA a inner join tB b On a.AID = b.AID
@PrimaryKey NVARCHAR(100), --主键,可以带表头 a.AID
@Fields NVARCHAR(2000) = '*',--读取字段
@Condition NVARCHAR(3000) = '',--Where条件
@CurrentPage INT = 1, --开始页码
@PageSize INT = 10, --页大小
@Sort NVARCHAR(200) = '', --排序字段
@RecordCount INT = 0 OUT
)
AS
DECLARE @strWhere VARCHAR(2000)
DECLARE @strsql NVARCHAR(3900)
IF @Condition IS NOT NULL AND len(ltrim(rtrim(@Condition)))>0
BEGIN
SET @strWhere = ' WHERE ' + @Condition + ' '
END
ELSE
BEGIN
SET @strWhere = ''
END
IF (charindex(ltrim(rtrim(@PrimaryKey)),@Sort)=0)
BEGIN
IF(@Sort='')
SET @Sort = @PrimaryKey + ' DESC '
ELSE
SET @Sort = @Sort+ ' , '+@PrimaryKey + ' DESC '
END
SET @strsql = 'SELECT @RecordCount = Count(1) FROM ' + @TableName + @strWhere
EXECUTE sp_executesql @strsql ,N'@RecordCount INT output',@RecordCount OUTPUT
IF @CurrentPage = 1 --第一页提高性能
BEGIN
SET @strsql = 'SELECT TOP ' + str(@PageSize) +' '+@Fields
+ ' FROM ' + @TableName + ' ' + @strWhere + ' ORDER BY '+ @Sort
END
ELSE
BEGIN
/* Execute dynamic query */
DECLARE @START_ID NVARCHAR(50)
DECLARE @END_ID NVARCHAR(50)
SET @START_ID = CONVERT(NVARCHAR(50),(@CurrentPage - 1) * @PageSize + 1)
SET @END_ID = CONVERT(NVARCHAR(50),@CurrentPage * @PageSize)
SET @strsql = ' SELECT *
FROM (SELECT ROW_NUMBER() OVER(ORDER BY '+@Sort+') AS rownum,
'+@Fields+ '
FROM '+@TableName + @strWhere +') AS XX
WHERE rownum BETWEEN '+@START_ID+' AND ' +@END_ID +' ORDER BY XX.rownum ASC'
END
EXEC(@strsql)
RETURN
然后新建类
public class PageInputParam
{
private string _TableName;
//表名,多表是请使用 tA a inner join tB b On a.AID = b.AID
public string TableName
{
get { return _TableName; }
set { _TableName = value; }
}
private string _Fileds = "*";
public string Fields
{
get { return _Fileds; }
set { _Fileds = value; }
}
private string _PrimaryKey = "ID";
public string PrimaryKey
{
get { return _PrimaryKey; }
set { _PrimaryKey = value; }
}
private int _PageSize = 10;
public int PageSize
{
get { return _PageSize; }
set { _PageSize = value; }
}
private int _CurrentPage = 1;
public int CurrentPage
{
get { return _CurrentPage; }
set { _CurrentPage = value; }
}
private string _Sort = string.Empty;
public string Sort
{
get { return _Sort; }
set { _Sort = value; }
}
private string _Condition = string.Empty;
public string Condition
{
get { return _Condition; }
set { _Condition = value; }
}
private int _RecordCount;
public int RecordCount
{
get { return _RecordCount; }
set { _RecordCount = value; }
}
}
public class AyPagingDto<T>
{
private int _Total;
public AyPagingDto()
{
this._Data = new List<T>();
}
public int Total
{
get { return _Total; }
set { _Total = value; }
}
private IList<T> _Data;
public IList<T> Data
{
get { return _Data; }
set { _Data = value; }
}
public int PageIndex { get; set; }
public int PageSize { get; set; }
}
public static class AyDapperCommon
{
public static AyPagingDto<T> GetPageData<T>(this System.Data.SqlClient.SqlConnection conn, PageInputParam inputParam, object param = null)
{
var p = new DynamicParameters();
string proName = "ProcGetPageData";
p.Add("TableName", inputParam.TableName);
p.Add("PrimaryKey", inputParam.PrimaryKey);
p.Add("Fields", inputParam.Fields);
p.Add("Condition", inputParam.Condition);
p.Add("CurrentPage", inputParam.CurrentPage);
p.Add("PageSize", inputParam.PageSize);
p.Add("Sort", inputParam.Sort);
p.Add("RecordCount", dbType: DbType.Int32, direction: ParameterDirection.Output);
conn.Open();
var pageData = new AyPagingDto<T>();
pageData.Data = conn.Query<T>(proName, p, commandType: CommandType.StoredProcedure).ToList();
conn.Close();
pageData.Total = p.Get<int>("RecordCount");
pageData.PageSize = Convert.ToInt32(Math.Ceiling(pageData.Total * 1.0 / inputParam.PageSize));
pageData.PageIndex = inputParam.CurrentPage > pageData.PageSize ? pageData.PageSize : inputParam.CurrentPage;
return pageData;
}
}
调用方法,AY的代码如下:
PageInputParam pip = new PageInputParam();
pip.Condition = "1=1";
pip.Condition += string.Format(" and UserName like '%{0}%'", "AY");
pip.CurrentPage = 1;
pip.Fields = "*";
pip.Sort = "UserId asc";
pip.PageSize = 20;
pip.TableName = "Users";
pip.PrimaryKey = "UserID";
var userList = con.GetPageData<Users>(pip);
foreach (var item in userList.Data)
{
Console.WriteLine(item.UserID + ":" + item.UserName);
}
第一页,20条数据如下:
====================www.ayjs.net 杨洋 wpfui.com ayui ay aaronyang=======请不要转载谢谢了。=========
先写到这里了。不管了。
88 各位
推荐您阅读更多有关于“C#,”的文章
最新文章
- Kafka vs RocketMQ—— Topic数量对单机性能的影响-转自阿里中间件
- TypeScript Declaration Merging(声明合并)
- MyBatis笔记
- Apple个人(Individual)开发者账号升级公司(Company)开发者账号
- 引用计数gc机制使用不当导致内存泄漏
- jQuer基础
- (转)css换行样式:word-wrap同word-break的区别
- view ondraw
- msmtp+mutt发送邮件报警
- Mac下安装BeautifulSoup
- Linux学习笔记(二)——文件/目录/VIM
- ERP不规范,同事两行泪
- CentOS 7 实现zabbix agent 自动添加,并链接到指定的模版
- #1 Python灵活技巧
- ServiceFramework作为Java Web框架都有哪些不错的设计
- [简记] github 上的 GraphQL v4 API
- 【代码笔记】Web-HTML-布局
- MFS+Keepalived双机高可用热备方案操作记录
- FreeMarker boolean Issue
- 自学Linux Shell11.1-shell概述