|
|
Requirements: online read an article about sqlkata "SqlKata - convenient and easy to use Sql query builder", the feeling that this plug-in is quite useful, you can dynamically set the table name and conditions to generate the appropriate security SQL statement, the front-end through the visualization of the form configuration information to call sqlkata to perform database operations, do not need to program and hard-coded This is the same as my previous Java development project. This is quite similar to my previous Java development projects using jOOQ.
SqlKata
SqlKata query builder is a powerful Sql query builder written in C#. It is secure and framework independent. Inspired by the top query builders available , such as Laravel Query Builder and Knex.
A powerful dynamic Sql query builder with support for Sql Server, MySql, PostgreSql, Oracle and Firebird.
GitHub address: https: //github.com/sqlkata/querybuilder
Official documentation: https: //sqlkata.com/docs
SQL Server Preparing Test Data
First, we use MSSQL database to create a new test table and generate some test data, the script is as follows:
New .NET Core 3.1 Console Application
Add the SqlKata and SqlKata.Execution, Microsoft.Data.SqlClient packages using Nuget with the following command:
The console code is as follows:
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
Trying to query a table that doesn't exist, the error is reported as follows:
Unhandled exception. Microsoft.Data.SqlClient.SqlException (0x80131904): object name 'Hei' is invalid.
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 OnError(SqlException exception, Boolean breakConnection, Action`1)
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). String method)
at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at Microsoft.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)
SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) at Microsoft.Data.SqlClient.
at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
SqlMapper.ExecuteReaderWithFlagsFallback(IDbCommand cmd, Boolean wasClosed, 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)
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
If we just want to generate sql statements with SqlKata and don't want to execute them, the code is as follows:
More Advanced Queries
JOIN Table
Insert Data
Update Data
Delete Data
(End)
|
Previous: Hynix (Hynix), Magnesium (Micron) memory stick labeling detailsNext: [Practice] ESXI 7 view server host memory model, size
|
发表于 2022-10-12 22:07:53
Relay
发表于 2022-10-13 15:19:47