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" <yo*@oceanfree.net> wrote in message
news:0d****************************@phx.gbl...

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()

[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

-----Original Message-----
All

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

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.AppSettin gs
("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()
.

[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.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA
Author: ADO.NET and XML: ASP.NET on the Edge

************************************************** **************************
****
Think Outside the Box!
************************************************** **************************
****
"yop" <yo*@oceanfree.net> wrote in message
news:0d****************************@phx.gbl...

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

-----Original Message-----
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.AppSettin gs
("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()
.

[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

-----Original Message-----
MySQL has limitations. It is missing some really neat features. It will getthese 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 youshould load a connection in Application, however.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA
Author: ADO.NET and XML: ASP.NET on the Edge

************************************************* ******** ***********************
Think Outside the Box!
************************************************* ******** ***********************
"yop" <yo*@oceanfree.net> wrote in message
news:0d****************************@phx.gbl...

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

>-----Original Message-----
>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.AppSettin gs
>("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()
>.
>

.