|
需求:网上看了一篇关于 sqlkata 的文章《SqlKata - 方便好用的 Sql query builder》,感觉这个插件挺有用的,可以动态设置表名和条件来生成相应的安全 SQL 语句,前端通过可视化表单配置信息调用 sqlkata 来执行数据库操作,不需要编程和硬编码的方式就可以配置出一个接口。这和我之前用 Java 开发项目中使用 jOOQ 挺像的。
SqlKata
SqlKata查询生成器是一个用C# 编写的功能强大的Sql查询生成器。它是安全的,与框架无关。灵感来源于可用的顶级查询生成器,如Laravel Query Builder和 Knex。
一个功能强大的动态Sql查询生成器,支持Sql Server、MySql、PostgreSql,Oracle和Firebird。
GitHub地址:https://github.com/sqlkata/querybuilder
官方文档地址:https://sqlkata.com/docs
SQL Server 准备测试数据
首先,我们使用 MSSQL 数据库新建一张测试表和生成一些测试数据,脚本如下:
新建 .NET Core 3.1 控制台应用
使用 Nuget 添加 SqlKata 和 SqlKata.Execution、 Microsoft.Data.SqlClient 包,命令如下:
控制台代码如下:
exec sp_executesql N'SELECT * FROM [TestTableSize] WHERE [MyKeyField] = @p0 ORDER BY (SELECT 0) OFFSET @p1 ROWS FETCH NEXT @p2 ROWS ONLY',N'@p0 nvarchar(4000),@p1 bigint,@p2 int',@p0=N'0000000088',@p1=0,@p2=10
尝试查询一个不存在的表,报错如下:
Unhandled exception. Microsoft.Data.SqlClient.SqlException (0x80131904): 对象名 'Hei' 无效。
at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at Microsoft.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
at Microsoft.Data.SqlClient.SqlDataReader.get_MetaData()
at Microsoft.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)
at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean isAsync, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry, String method)
at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at Microsoft.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)
at Microsoft.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
at Dapper.SqlMapper.ExecuteReaderWithFlagsFallback(IDbCommand cmd, Boolean wasClosed, CommandBehavior behavior) in /_/Dapper/SqlMapper.cs:line 1066
at Dapper.SqlMapper.QueryImpl[T](IDbConnection cnn, CommandDefinition command, Type effectiveType)+MoveNext() in /_/Dapper/SqlMapper.cs:line 1094
at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
at Dapper.SqlMapper.Query[T](IDbConnection cnn, String sql, Object param, IDbTransaction transaction, Boolean buffered, Nullable`1 commandTimeout, Nullable`1 commandType) in /_/Dapper/SqlMapper.cs:line 734
at SqlKata.Execution.QueryFactory.Get[T](Query query, IDbTransaction transaction, Nullable`1 timeout)
at SqlKata.Execution.QueryExtensions.Get[T](Query query, IDbTransaction transaction, Nullable`1 timeout)
at SqlKata.Execution.QueryExtensions.Get(Query query, IDbTransaction transaction, Nullable`1 timeout)
at SqlKataDemo.Program.Main(String[] args) in C:\Users\itsvse_pc\source\repos\WindowsFormsApp1\SqlKataDemo\Program.cs:line 20
ClientConnectionId:49158892-5a24-4aa8-8249-232fba2674da
Error Number:208,State:1,Class:16
如果我们只是想通过 SqlKata 来生成 sql 语句,并不想去执行,代码如下:
更多高级查询
JOIN 表
插入数据
更新数据
删除数据
(完)
|
上一篇:海力士(Hynix)、镁光(Micron)内存条标签详解下一篇:【实战】ESXI 7 查看服务器主机的内存型号、大小
|