Storing Connection in Application_OnStart

[yop]

All

I need to store a connection in the application object
due to the need to use MYSQL and its very poor connection
pooling.

Would someone have an example as the code below is not
working correctly.

Thanks

Sub Application_Start(ByVal sender As Object, ByVal e As
EventArgs)
' Fires when the application is started
Dim myConn As MySqlConnection = New
MySqlConnection(ConfigurationSettings.AppSettings
("ConnectionString"))
Application("myConnection") = myConn

End Sub


Dim myConn As new MySqlConnection = CType(Application
("myConnection", MySqlConnection))

Dim query As String = "SELECT * FROM users WHERE
username='" & strUserName & "' AND usrPassword=PASSWORD
('" & strPassword & "')"

Dim myCommand As New MySqlCommand(query, myConn)
myConn.Open()

 

[George Ter-Saakov]

The problem is that 2 simultaneous requests are going to grab the same
connection and you will have a problem.
So you need some sort of locking mechanism. Also your design will fail if
MYSQL server will close the connection (server was stopped for maintenance
for example). Then you APP will fail each time even if MYSQL is back and
working fine.

So I think you will be better of with opening connection every time.
And hopping that the pooling will be fixed someday. (Never used MYSQL but
also never heard complains about it's pooling.).



George.

 

[yop]

I do not want to do it this way but due to the pooling
issue, that is more to do with the DbProvider, the
solution I was given was to use a connection in the
application.
MYSQL is fine but there is no good dBProvicder available
at the moment as far I can see. Each one has its problem
or limitations.

thanks

 

[Cowboy \(Gregory A. Beamer\)]

MySQL has limitations. It is missing some really neat features. It will get
these features, but it will take time. The pains of open source, I guess.

I will stand behind Kevin 100% on suspecting the person who stated you
should load a connection in Application, however.

 

[yop]

Believe it or not it was the dBProvider who wrote the
native driver who suggested this! He told me that this
was a MYSQL issue and that the solution he would use was
this!
MY choice was to use SQLServer due to the amounts of
issues I have had with .NET and MYSQL but management
decisions dictate this.

So can I take it that you would suggest the best way to
handle this is to open and close the connection within
each function.

thanks for your help