About connection with EF Core and Pomelo provider for MySQL (GetProcessList, EF Core connection count, Setup Max MySQL Connection count. Separate connection for Attributes, Calculate connection for API).
If you use attributes in EF core (for example in this way Simplest way to create tracing and authorization layers in Backend API. Special attribute to arrange API for authorization layers.) we usually need to dispose connection.
This process induce exception during reopend connection.
This is my solution to overcome this issue.
15: <Runtime.CompilerServices.Extension>
16: Public Function ReOpen(_DB As ApplicationDbContext) As ApplicationDbContext
17: If _DB.Database.GetDbConnection.State = _DB.Database.GetDbConnection.State.Closed Then
18: Try
19: _DB.Database.GetDbConnection.Open()
20: Return _DB
21: Catch ex As System.ObjectDisposedException
22: _DB.Database.GetDbConnection.Dispose()
23: Interlocked.Increment(ConnectionCount)
24: Debug.WriteLine($"Creating new EF Core connection {ConnectionCount}")
25: _DB = New ApplicationDbContext(_DB._Options)
26: If Not ReOpenMySQL(_DB.Database.GetDbConnection) Then
27: Throw New Exception($"MySqlConnection can not open {_DB.Database.GetDbConnection.ConnectionString.Substring(0, _DB.Database.GetDbConnection.ConnectionString.IndexOf("Password=") + 9) & "***********"}")
28: End If
29: End Try
30: ElseIf _DB.Database.GetDbConnection.State = _DB.Database.GetDbConnection.State.Open Then
31: _DB.Database.GetDbConnection.Close()
32: '_DB.Database.GetDbConnection.Dispose()
33: _DB.Database.GetDbConnection.Open()
34: End If
35: Return _DB
36: End Function
...
114: Public Function ReOpenMySQL(ByRef CN As MySqlConnection) As Boolean
115: If CN Is Nothing Then
116: CN = New MySqlConnection(CN.ConnectionString)
117: Open:
118: Try
119: CN.Open()
120: If CN.State = Data.ConnectionState.Open Then
121: Dim CMD1 = CN.CreateCommand()
122: CMD1.CommandText = "SELECT count(*) as Count FROM cryptochestmax.GetProcessList;"
123: Dim RDR1 = CMD1.ExecuteReader
124: RDR1.Read()
125: Dim Count As Integer = RDR1("Count")
126: RDR1.Close()
127: Debug.Print($"Found {Count} MySQL connection.")
128: Return True
129: Else
130: Return False
131: End If
132: Catch ex As Exception
133: Console.WriteLine(ex.Message & vbCrLf & CN.ConnectionString)
134: Return False
135: End Try
136: Else
137: If CN.State = Data.ConnectionState.Open Then
138: Return True
139: Else
140: GoTo Open
141: End If
142: End If
143: End Function
144:
145: End Module
146: End Namespace
With this approach I can reopen new MySQL connection when it need for me, I reopen it 130 times in my project.
EF Code first context reserved for future use in my project and currently look as:
This is my definition of EF Core connection.
This is my definition of GetProcessList procedure.
And this is finally result of one API - more than 20 reopned connection.
So, as you can see default MySQL connection is not enough.
And we need to setup more connection to MySQL.
)
|
|