MySql Connector/NET Pooling

Hi,

I am trying to configure my asp.net application to work with MySQL
using Connector/NET and I wish to use connection pooling. What is the
best way to do this?

This is what I have so far:

MySqlConnection myConnection;
MySqlDataAdapter myDataAdapter;
DataSet myDataSet;

String strSql;

myConnection = new MySqlConnection("server=localhost; user id =
testuser; password=abc123; database=test;pooling=false;");

strSql = "Select * From Users;";

myDataAdapter = new MySqlDataAdapter(strSql, myConnection);

myDataSet = new DataSet();

myDataAdapter.Fill(myDataSet, "mytable");

DataGrid1.DataSource = myDataSet;
DataGrid1.DataBind();

Now, I was thinking about putting the connection string in the
web.config file with pooling=true, but I a not sure what that would do.
I checked the documentation for Connection/NET and it doesn't give any
information on the pooling parameter. What happens if I set it to true?
Where do I specify the size of the pool? Do I need to make any changes
on IIS or MySQL itself?

Thanks,

Asad

asadikhan@gmail.com

Hi Dear ,

If you maintain your very same connectionstring all the place, it will
automatically goes to connection pooling. And If you want to control pooling
related that you can do using these

Pooling = 'true/false'
=====
'true'
When true, the SQLConnection object is drawn from the appropriate pool, or
if necessary, is created and added to the appropriate pool. Recognized values
are true, false, yes, and no.

Max Pool Size=100
============
The maximum number of connections allowed in the pool.

Min Pool Size=0
===========
The minimum number of connections allowed in the pool.

for further reading here are the links
-------------------------------------------
SqlConnection.ConnectionString Property

http://msdn.microsoft.com/library/de...tringTopic.asp


Tuning Up ADO.NET Connection Pooling in ASP.NET Applications
By Dmitri Khanine
---------------------------------------------------------------------------
http://www.15seconds.com/issue/040830.htm

Bye
Venkat_KL

Hi Venkant,

Thanks for your reply, however, I believe the parameters you have
shared are for the SqlConnection object that is part of the .NET
Framework and is intended for use with Sql Server. I am using MySql
database, and am using the Connector/NET driver with it. I don't
believe the parameters mentioned above with the exception of pooling =
true, false work for this driver i.e. Max Pool Size and Min Pool Size
are not valid parameters for the connection string when using
Connector/NET.

I wish to know what parameters I can use in the connection string for
the Connector/NET driver.

Could you please verify this in case I'm wrong?

Asad

Hi Dear

My apologies for that overlook, I am really sorry.

in fact, I saw your code where you declared the variable as MySqlConnection
looking at your code, i thought it is SQL Server.

This is your sample code:
=================
------------------------------------------------------------------------------------------------
MySqlConnection myConnection;
MySqlDataAdapter myDataAdapter;
DataSet myDataSet;

String strSql;

myConnection = new MySqlConnection("server=localhost; user id =
testuser; password=abc123; database=test;pooling=false;");
------------------------------------------------------------------------------------------------
But any way, here are few other ways to connect to mysql.

just have a look. these might me useful to you.

MySQLDirect .NET Data Provider
CoreLab.MySql
======================
The MySQLDirect .NET Data Provider is an add-on component to the
..NET Framework that allows you to access the MySQL database using
native MySQL network protocol or MySQL client, without going through
OLE DB or ODBC.

here is the link
-----------------
http://crlab.com/mysqlnet/download.html

Using C#
================
using CoreLab.MySql;

MySqlConnection oMySqlConn = new MySqlConnection();
oMySqlConn.ConnectionString = "User ID=myUsername;" +
"Password=myPassword;" +
"Host=localhost;" +
"Port=3306;" +
"Database=myDatabaseName;" +
"Direct=true;" +
"Protocol=TCP;" +
"Compress=false;" +
"Pooling=true;" +
"Min Pool Size=0;" +
"Max Pool Size=100;" +
"Connection Lifetime=0";
oMySqlConn.Open();

Using VB.NET
===========

Imports CoreLab.MySql

Dim oMySqlConn As MySqlConnection = New MySqlConnection()
oMySqlConn.ConnectionString = "User ID=myUsername;" & _
"Password=myPassword;" & _
"Host=localhost;" & _
"Port=3306;" & _
"Database=myDatabaseName;" & _
"Direct=true;" & _
"Protocol=TCP;" & _
"Compress=false;" & _
"Pooling=true;" & _
"Min Pool Size=0;" & _
"Max Pool Size=100;" & _
"Connection Lifetime=0"
oMySqlConn.Open()

For more information, see: CoreLab's MySqlDirect .NET Data Provider

http://crlab.com/mysqlnet/

************************************************** **********

MySQL .NET Data Provider
EID.MySqlClient
The MySQL .NET Native Provider is an add-on component to the .NET Framework
that allows you to access the MySQL database through
the native protocol, without going through OLE DB or ODBC.

Using C#

using EID.MySqlClient;
....
MySqlConnection oMySqlConn = new MySqlConnection();
oMySqlConn.ConnectionString = "Data Source=(local);" +
"Database=myDatabaseName;" +
"User ID=myUsername;" +
"Password=myPassword;" +
"Command Logging=false";
oMySqlConn.Open();

Using VB.NET

Imports EID.MySqlClient
....
Dim oMySqlConn As MySqlConnection = New MySqlConnection()
oMySqlConn.ConnectionString = "Data Source=(local);" & _
"Database=myDatabaseName;" & _
"User ID=myUsername;" & _
"Password=myPassword;" & _
"Command Logging=false"
oMySqlConn.Open()

For more information, see: EID's MySQL ADO.NET native provider

http://www.einfodesigns.com/


here is one link on 23.2.4.2. Connecting to MySQL Using MySQL Connector/NET

but not given the attributes of connectionstring like pool, pool size.....

http://dev.mysql.com/doc/refman/5.0/...onnecting.html


Bye
Venkat_KL

Thanks a lot for the detailed reply.

One more question though to make sure I implement the pooling right.
You mentioned in your first reply that if I maintain the very same
connectionstring all the place, it will
automatically go to connection pooling. So the way I am planning on
doing this is I keep the connection string in the web.config file and
each time I need to connect to the database I use the following code:

string strcon = ConfigurationSettings.AppSettings("connectionstrin g");
MySqlConnection myConnection = new MySqlConnection(strcon);

Is this the correct way to implement connection pooling? Could you
please confirm.

Thanks again.

Asad

Hi ,

string strcon = ConfigurationSettings.AppSettings("connectionstrin g");
MySqlConnection myConnection = new MySqlConnection(strcon);

This is absolutely right, you can go ahed.

I do not know 100%, Whether maintaining the very same connectionstring in
all the places will automatically go to connection pooling in MySql. I have
not palyed around in this regard. Whatever I said is based on MS SQL Server.

I have given you the link know. could you please go through it and confirm.
I am bit busy, otherwise, I would have done that also for you.

I am once again giving you that same link to go through.

http://dev.mysql.com/doc/refman/5.0/...onnecting.html

Bye
Venkat_KL