CheckDBNull, RawSqlQuery, ExecRDR, RawSqlQueryAsync, ExecNonQueryAsync (with transaction)- amazing extension for working with data.
In my last project BackendAPI (Net Core 5) project template with Custom Attribute, Service and Controller Example, MySQL database with masked password in config, SwaggerUI, EF Core and extension to EF Core I use two my amazing extension for working with MySQL data.
Firstly, most my software use MySQL, because this is free SQL server and can be run in Linux and Windows. I usually use two library for working with MySQL:
- Pomelo.EntityFrameworkCore.MySql for working with EF Core.
- MySqlConnector for working with MySQL in simple ADO.NET style.
What different with EF Core and simple ADO.NET? Simple ADO.NET use only raw query like Query.ExecuteReader / ExecuteNonQuery / ExecuteScalar / [Async] , despite of this simplicity and loose coupling there are alternative view to link with code and data. You can use Tight coupling and build full reflection of database structure in you code with POCO classes.
And with EF Core you can various LINQ service like _dbContext.MyTable.Any/All/Where and so on. Also EF code is native service for ASP.NET Core DJ container. EF also have other future as automatically loading dependence table (Lazy/Eager loading), caching and other future Entity Framework missing FAQ.
However both of my extension can be working with both way - simple ADO.NET and EF.CORE.
1:
2: Imports System.Data
3: Imports System.Data.Common
4: Imports BackendAPI.Model
5: Imports Microsoft.EntityFrameworkCore
6:
7: Namespace Helper
8: Public Module RawSqlQuery
9: <Runtime.CompilerServices.Extension>
10: Public Function RawSqlQuery(Of T)(Context As ApplicationDbContext, ByVal SqlQuery As String, ByVal RowMapperFunc As Func(Of DbDataReader, T)) As List(Of T)
11: Try
12: Using Command = Context.Database.GetDbConnection().CreateCommand()
13: Command.CommandText = SqlQuery
14: Command.CommandType = CommandType.Text
15: Context.Database.OpenConnection()
16:
17: Using RDR = Command.ExecuteReader()
18: Dim ResultList = New List(Of T)()
19:
20: While RDR.Read()
21: ResultList.Add(RowMapperFunc(RDR))
22: End While
23:
24: Return ResultList
25: End Using
26: End Using
27: Catch ex As Exception
28: 'Debug only, because this function show password in AES_DECRYPT()
29: Debug.WriteLine(ex.Message & " : " & SqlQuery)
30: End Try
31: End Function
32:
33: End Module
34: End Namespace
1: Namespace Helper
2: Module CheckDBNull
3: <Runtime.CompilerServices.Extension>
4: Public Function CheckDBNull(Of T)(RDR As Data.Common.DbDataReader, ByVal DbField As Object) As T
5: If DbField Is Nothing OrElse DbField Is DBNull.Value Then
6: Return Nothing
7: Else
8: Return CType(DbField, T)
9: End If
10:
11: End Function
12:
13: End Module
14: End Namespace
And look how to use this extensions.
The same idea with transaction.
1: Namespace Helper
2: Public Module Sql
3: <Runtime.CompilerServices.Extension>
4: Public Function ExecNonQuery(_DB As ApplicationDbContext, SQL As String, Optional Transaction As Data.Common.DbTransaction = Nothing) As Integer
5: Dim CMD1 = _DB.Database.GetDbConnection().CreateCommand()
6: CMD1.CommandText = SQL
7: If Transaction IsNot Nothing Then
8: CMD1.Transaction = Transaction
9: End If
10: Return CMD1.ExecuteNonQuery()
11: End Function
12:
13: Public Function ExecRDR(Of T)(_DB As ApplicationDbContext, SQL As String, RowMapperFunc As Func(Of DbDataReader, T), Optional Transaction As Data.Common.DbTransaction = Nothing) As List(Of T)
14: Dim Ret1 As New List(Of T)
15: Dim CMD1 = _DB.Database.GetDbConnection().CreateCommand()
16: CMD1.CommandText = SQL
17: If Transaction IsNot Nothing Then
18: CMD1.Transaction = Transaction
19: End If
20: Dim RDR1 = CMD1.ExecuteReader
21: While RDR1.Read
22: Dim X = RowMapperFunc(RDR1)
23: Ret1.Add(X)
24: End While
25: RDR1.Close()
26: Return Ret1
27: End Function
And ASYNC version of this functions.
1: Imports System.Data
2: Imports System.Data.Common
3: Imports System.Threading
4: Imports BackendAPI.Model
5: Imports Microsoft.EntityFrameworkCore
6: Imports MySqlConnector
7:
8: Namespace Helper
9: Public Module RawSqlQueryAsync
10: <Runtime.CompilerServices.Extension>
11: Public Async Function RawSqlQueryAsync(Of T)(Context As ApplicationDbContext, ByVal SqlQuery As String, ByVal RowMapperFunc As Func(Of DbDataReader, T)) As Task(Of Tuple(Of List(Of T), Exception))
12: Try
13: Dim EF_CN As DbConnection = Context.Database.GetDbConnection()
14: Using CN = New MySqlConnection(EF_CN.ConnectionString)
15: Await CN.OpenAsync
16:
17: Using Command = CN.CreateCommand()
18: Command.CommandText = SqlQuery
19: Command.CommandType = CommandType.Text
20:
21: Using RDR = Await Command.ExecuteReaderAsync
22: Dim ResultList = New List(Of T)()
23:
24: While RDR.Read()
25: ResultList.Add(RowMapperFunc(RDR))
26: End While
27:
28: RDR.Close()
29: Return New Tuple(Of List(Of T), Exception)(ResultList, Nothing)
30: End Using
31: End Using
32: End Using
33: Catch ex As Exception
34: 'Debug only, because this function show password in AES_DECRYPT()
35: Debug.WriteLine(ex.Message & " : " & SqlQuery)
36: Return New Tuple(Of List(Of T), Exception)(Nothing, ex)
37: End Try
38: End Function
40: Public Async Function ExecNonQueryAsync(_DB As ApplicationDbContext, SQL As String, Optional Transaction As Data.Common.DbTransaction = Nothing) As Task(Of Integer)
41: Try
42: Dim EF_CN As DbConnection = _DB.Database.GetDbConnection()
43: Using CN = New MySqlConnection(EF_CN.ConnectionString)
44: Await CN.OpenAsync
45: Using CMD = CN.CreateCommand
46: CMD.CommandText = SQL
47: If Transaction IsNot Nothing Then
48: CMD.Transaction = Transaction
49: End If
50: Dim Ret = CMD.ExecuteNonQueryAsync
51: Await Ret
52: Return Ret.Result
53: End Using
54: End Using
55: Catch ex As Exception
56: Console.WriteLine(ex.Message & vbCrLf & SQL)
57: End Try
58: End Function
59:
60:
61: End Module
62:
63: End Namespace
Performing this function is Asynchronous code is possible with Await, for example:
In Synchronous code performing is possible with Task.Run:
1: Dim BackendHubNotifiedTsk As Task(Of Integer) = Task.Run(Async Function()
2: Dim BackendHubNotified = Await Sql.ExecNonQueryAsync(_Db,
3: $"UPDATE `cryptochestmax`.`BashJob` SET `IsBackendHubNotified`= 1, " &
4: $"`LastUpdate`=Now() WHERE `i`={NextJob.i}")
5: Return BackendHubNotified
6: End Function)
)
|
|