(CORE) CORE (2022)

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:

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)


Comments ( )
<00>  <01>  <02>  <03>  <04>  <05>  <06>  <07>  <08>  <09>  <10>  <11>  <12>  <13>  <14>  <15>  <16>  <17>  <18>  <19>  <20>  <21
Link to this page: http://www.vb-net.com/MySqlData/Index.htm
<SITEMAP>  <MVC>  <ASP>  <NET>  <DATA>  <KIOSK>  <FLEX>  <SQL>  <NOTES>  <LINUX>  <MONO>  <FREEWARE>  <DOCS>  <ENG>  <CHAT ME>  <ABOUT ME>  < THANKS ME>