文章目录

前言
一、DeveloperSharp的使用
1.安装包
2.初始化数据库连接
3.查询数据
4.增/删/改
5.输出参数
6.存储过程
二、DeveloperSharp的参数说明
1.SqlExecute<T>
2.SqlExecute<T>
3.SqlExecute
4.SqlExecute
5.SpExecute<T>
6.SpExecute<T>
7.SpExecute
8.SpExecute

前言

DeveloperSharp是一个研发中大型项目必备的系统平台。也是一个低代码平台。

它主要包括了如下一些功能：

基于Sql语句、存储过程、事务、分页的数据库操作。并几乎支持市面上所有种类的数据库。
图片操作。裁剪、缩放、加水印。
http请求调用（Post与Get）
高效分页
Web服务/WebApi的负载均衡
数据库的负载均衡，以及读写分离
CORS跨域访问
UUID全球通用唯一识别码
MQ消息队列（请另行使用DeveloperSharp.RabbitMQ包）
Redis缓存（请另行使用DeveloperSharp.Redis包）
“异种数据库”的负载均衡
其他相关功能

一、DeveloperSharp的使用

1.安装包

DeveloperSharp

2.初始化数据库连接

 
using DeveloperSharp.Framework.QueryEngine;
 
 
DatabaseInfo DIF;
 
DIF.DatabaseType = DatabaseType.SQLServer; //设置数据库类型
DIF.ConnectionString = "Server=localhost;Database=PSIDBase;Uid=sa;Pwd=1";
IDataAccess IDA = DataAccessFactory.Create(DIF);

通过对DatabaseType属性的设定，提供了对所有种类数据库的支持（包括：MySql、Oracle、PostgreSQL、SqlServer、Sqlite、Firebird、达梦、以及人大金仓KingbaseES、神舟通用, 南大通用, 翰高, Access、等）

3.查询数据

1、普通查询

 
using DeveloperSharp.Framework.QueryEngine;
using System.Data;
 
DatabaseInfo DIF;
 
DIF.DatabaseType = DatabaseType.SQLServer; //设置数据库类型
DIF.ConnectionString = "Server=localhost;Database=PSIDBase;Uid=sa;Pwd=1";
IDataAccess IDA = DataAccessFactory.Create(DIF);
 
 
//查询多数据
var Students1 = IDA.SqlExecute<UserInfos>("select * from UserInfos");
 
//查询多数据（带参数）
var Students2 = IDA.SqlExecute<UserInfos>("select * from UserInfos where UserId>@IdMin and UserName like @LikeName", new { IdMin = 5, LikeName = "%lyc%" });
//另一种写法1
var IdMin = IDA.CreateParameterInput("IdMin", DbType.Int32, 4);
var LikeName = IDA.CreateParameterInput("LikeName", DbType.String, 50, "%lyc%");
var Students3 = IDA.SqlExecute<UserInfos>("select * from UserInfos where UserId>@IdMin and UserName like @LikeName", LikeName, IdMin);
//另一种写法2
var Students4 = IDA.SqlExecute<UserInfos>("select * from UserInfos").Where(t => t.UserId > 2 && t.UserName.Contains("lyc"));
 
//选出单数据
var OneStudent = Students2.FirstOrDefault();
 
 
public class UserInfos
{
    public int UserId { get; set; }
    public string UserName { get; set; }
    public int Age { get; set; }
    public string UserPwd { get; set; }
    public int UserState { get; set; }
}

2、分页查询

 
//--------------------------------------------分页------------------------------------------------
var Page1 = Students1.PagePartition(3, 1);
 
//一气呵成的写法
var Page3 = IDA.SqlExecute<UserInfos>("select * from UserInfos").PagePartition(3, 2);

4.增/删/改

 
try
{
    //开启事务
    IDA.TransactionBegin();
 
    //修改数据（多语句）
    int affectedRows1 = IDA.SqlExecute("insert into UserInfos(UserName,Age)values('ww','96');update UserInfos set Age=100 where Id=1006");
 
    //修改数据（带参数）
    int affectedRows2 = IDA.SqlExecute("insert into UserInfos(UserName,Age)values(@N,@A)", new { N = "愚公", A = 200 });
    //另一种写法
    var NewAge = IDA.CreateParameterInput("NewAge", DbType.Int32, 200);
    var NewName = IDA.CreateParameterInput("NewName", DbType.String, 50, "愚公");
    int affectedRows3 = IDA.SqlExecute("insert into UserInfos(UserName,Age)values(@NewName,@NewAge)", NewName, NewAge);
 
    //完成事务
    IDA.TransactionCommit();
}
catch
{
    //回滚事务
    IDA.TransactionRollBack();
}

5.输出参数

 
 var op1 = IDA.CreateParameterOutput("TotalCount", DbType.Int32);//此项为输出参数
 var op2 = IDA.CreateParameterOutput("MyName", DbType.String, 50);//此项为输出参数
 
 //以下sql语句混杂了多个“输入”与“输出”参数，注意看
 IDA.SqlExecute("insert into Friend(Birth,Name,height)values(@B,@N,@h);" +
     "select @TotalCount=count(*) from Friend;" +
     "select @MyName=Name from Friend where Id=@Id",
     new { N = "愚公", B = "1999-02-28 12:03:45", h = 11.023, Id = 2 },
     op1, op2);
 
 int tc = Convert.ToInt32(op1.Value);
 string mn = op2.Value.ToString();

6.存储过程

1、创建存储过程

 
CREATE PROCEDURE Test5
    @B as datetime,
    @N as nvarchar(50),
    @h as float,
    @TotalCount as int output,
    @MyName as nvarchar(50) output,
    @Id as int
AS
BEGIN
    insert into Friend(Birth,Name,height)values(@B,@N,@h);
    select @TotalCount=count(*) from Friend;
    select @MyName=Name from Friend where Id=@Id;
    return @TotalCount+100;
END

2、调用存储过程

 
var op1 = IDA.CreateParameterOutput("TotalCount", DbType.Int32);//输出参数
var op2 = IDA.CreateParameterOutput("MyName", DbType.String, 50);//输出参数
var op3 = IDA.CreateParameterReturn();//返回参数
 
IDA.SpExecute("Test5", new { N = "愚公", B = "1999-02-28 12:03:45", h = 11.023, Id = 2 }, op1, op2, op3);
 
int tc = Convert.ToInt32(op1.Value);
string mn = op2.Value.ToString();
int ret = Convert.ToInt32(op3.Value);

二、DeveloperSharp的参数说明

1.SqlExecute

声明：IEnumerable SqlExecute(string cmdText, params IDataParameter[] Params) where T : class, new()
用途：执行Sql语句(Select类)
参数：（1）string cmdText – Sql语句（2）params IDataParameter[] Params – 参数组返回：IEnumerable – 多数据结果集

2.SqlExecute

声明：IEnumerable SqlExecute(string cmdText, object InputParams,params IDataParameter[] Params) where T : class, new()
用途：执行Sql语句(Select类)
参数：（1）string cmdText – Sql语句（2）object InputParams – 输入参数对象（3）params IDataParameter[] Params – 参数组返回：IEnumerable – 多数据结果集

3.SqlExecute

声明：int SqlExecute(string cmdText, params IDataParameter[] Params)
用途：执行Sql语句(Insert/Update/Delete类)
参数：（1）string cmdText – Sql语句（2）params IDataParameter[] Params – 参数组返回：int – 受影响的行数

4.SqlExecute

声明：int SqlExecute(string cmdText, object InputParams, params IDataParameter[] Params)
用途：执行Sql语句(Insert/Update/Delete类)
参数：（1）string cmdText – Sql语句（2）object InputParams – 输入参数对象（3）params IDataParameter[] Params – 参数组返回：int – 受影响的行数

5.SpExecute

声明：IEnumerable SpExecute(string cmdText, params IDataParameter[] Params) where T : class, new()
用途：执行Sp存储过程(Select类)
参数：（1）string cmdText – Sp存储过程名（2）params IDataParameter[] Params – 参数组返回：IEnumerable – 多数据结果集

6.SpExecute

声明：IEnumerable SpExecute(string cmdText, object InputParams, params IDataParameter[] Params) where T : class, new()
用途：执行Sp存储过程(Select类)
参数：（1）string cmdText – Sp存储过程名（2）object InputParams – 输入参数对象（3）params IDataParameter[] Params – 参数组返回：IEnumerable – 多数据结果集

7.SpExecute

声明：int SpExecute(string cmdText, params IDataParameter[] Params)
用途：执行Sp存储过程(Insert/Update/Delete类)
参数：（1）string cmdText – Sp存储过程名（2）params IDataParameter[] Params – 参数组返回：int – 受影响的行数

8.SpExecute

声明：int SpExecute(string cmdText, object InputParams, params IDataParameter[] Params)
用途：执行Sp存储过程(Insert/Update/Delete类)
参数：（1）string cmdText – Sp存储过程名（2）object InputParams – 输入参数对象（3）params IDataParameter[] Params – 参数组返回：int – 受影响的行数

	using DeveloperSharp.Framework.QueryEngine;


	DatabaseInfo DIF;

	DIF.DatabaseType = DatabaseType.SQLServer; //设置数据库类型
	DIF.ConnectionString = "Server=localhost;Database=PSIDBase;Uid=sa;Pwd=1";
	IDataAccess IDA = DataAccessFactory.Create(DIF);

	using DeveloperSharp.Framework.QueryEngine;
	using System.Data;

	DatabaseInfo DIF;

	DIF.DatabaseType = DatabaseType.SQLServer; //设置数据库类型
	DIF.ConnectionString = "Server=localhost;Database=PSIDBase;Uid=sa;Pwd=1";
	IDataAccess IDA = DataAccessFactory.Create(DIF);


	//查询多数据
	var Students1 = IDA.SqlExecute<UserInfos>("select * from UserInfos");

	//查询多数据（带参数）
	var Students2 = IDA.SqlExecute<UserInfos>("select * from UserInfos where UserId>@IdMin and UserName like @LikeName", new { IdMin = 5, LikeName = "%lyc%" });
	//另一种写法1
	var IdMin = IDA.CreateParameterInput("IdMin", DbType.Int32, 4);
	var LikeName = IDA.CreateParameterInput("LikeName", DbType.String, 50, "%lyc%");
	var Students3 = IDA.SqlExecute<UserInfos>("select * from UserInfos where UserId>@IdMin and UserName like @LikeName", LikeName, IdMin);
	//另一种写法2
	var Students4 = IDA.SqlExecute<UserInfos>("select * from UserInfos").Where(t => t.UserId > 2 && t.UserName.Contains("lyc"));

	//选出单数据
	var OneStudent = Students2.FirstOrDefault();


	public class UserInfos
	{
	public int UserId { get; set; }
	public string UserName { get; set; }
	public int Age { get; set; }
	public string UserPwd { get; set; }
	public int UserState { get; set; }
	}

	//--------------------------------------------分页------------------------------------------------
	var Page1 = Students1.PagePartition(3, 1);

	//一气呵成的写法
	var Page3 = IDA.SqlExecute<UserInfos>("select * from UserInfos").PagePartition(3, 2);

	try
	{
	//开启事务
	IDA.TransactionBegin();

	//修改数据（多语句）
	int affectedRows1 = IDA.SqlExecute("insert into UserInfos(UserName,Age)values('ww','96');update UserInfos set Age=100 where Id=1006");

	//修改数据（带参数）
	int affectedRows2 = IDA.SqlExecute("insert into UserInfos(UserName,Age)values(@N,@A)", new { N = "愚公", A = 200 });
	//另一种写法
	var NewAge = IDA.CreateParameterInput("NewAge", DbType.Int32, 200);
	var NewName = IDA.CreateParameterInput("NewName", DbType.String, 50, "愚公");
	int affectedRows3 = IDA.SqlExecute("insert into UserInfos(UserName,Age)values(@NewName,@NewAge)", NewName, NewAge);

	//完成事务
	IDA.TransactionCommit();
	}
	catch
	{
	//回滚事务
	IDA.TransactionRollBack();
	}

	var op1 = IDA.CreateParameterOutput("TotalCount", DbType.Int32);//此项为输出参数
	var op2 = IDA.CreateParameterOutput("MyName", DbType.String, 50);//此项为输出参数

	//以下sql语句混杂了多个“输入”与“输出”参数，注意看
	IDA.SqlExecute("insert into Friend(Birth,Name,height)values(@B,@N,@h);" +
	"select @TotalCount=count(*) from Friend;" +
	"select @MyName=Name from Friend where Id=@Id",
	new { N = "愚公", B = "1999-02-28 12:03:45", h = 11.023, Id = 2 },
	op1, op2);

	int tc = Convert.ToInt32(op1.Value);
	string mn = op2.Value.ToString();

	CREATE PROCEDURE Test5
	@B as datetime,
	@N as nvarchar(50),
	@h as float,
	@TotalCount as int output,
	@MyName as nvarchar(50) output,
	@Id as int
	AS
	BEGIN
	insert into Friend(Birth,Name,height)values(@B,@N,@h);
	select @TotalCount=count(*) from Friend;
	select @MyName=Name from Friend where Id=@Id;
	return @TotalCount+100;
	END

	var op1 = IDA.CreateParameterOutput("TotalCount", DbType.Int32);//输出参数
	var op2 = IDA.CreateParameterOutput("MyName", DbType.String, 50);//输出参数
	var op3 = IDA.CreateParameterReturn();//返回参数

	IDA.SpExecute("Test5", new { N = "愚公", B = "1999-02-28 12:03:45", h = 11.023, Id = 2 }, op1, op2, op3);

	int tc = Convert.ToInt32(op1.Value);
	string mn = op2.Value.ToString();
	int ret = Convert.ToInt32(op3.Value);

【愚公系列】2023年01月 .NET CORE工具案例-DeveloperSharp的使用（数据库）