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

DeveloperSharp

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);