Requirements: I read an article about sqlkata on the Internet, "SqlKata - Convenient and Easy to Use Sql Query Builder", and I feel that this plugin is very useful, you can dynamically set table names and conditions to generate corresponding secure SQL statements, and the front-end calls sqlkata to perform database operations through visual form configuration information, and you can configure an interface without programming and hardcoding. This is similar to my previous use of jOOQ in Java development projects.
SqlKata
SqlKata Query Generator is a powerful Sql query generator written in C#. It is secure and frame-agnostic. Inspired by the top query builders available, such as Laravel Query Builder and Knex.
A powerful dynamic Sql query generator that supports Sql Server, MySql, PostgreSql, Oracle, and Firebird.
GitHub address:The hyperlink login is visible.
Official document address:The hyperlink login is visible.
SQL Server prepares the test data
First, we use the MSSQL database to create a new test table and generate some test data, the script is as follows:
Create a new .NET Core 3.1 console app
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
Try querying oneA table that does not exist, the error is as follows:
Unhandled exception. Microsoft.Data.SqlClient.SqlException (0x80131904): Invalid object name '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
If we just want to generate sql statements through 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)
|
Posted on 2022-10-12 22:07:53
|
|
|
|
Posted on 2022-10-13 15:19:47
|
Landlord|
Posted on 2025-1-2 12:15:02
|
