C# mySQL Connection

Ziycon

I'm developing an application in VS2008 using c# and I had a SQLite database work but want to go with mySQL.

Can anyone point me to the best way to use a mySQL connection with c#, should I use a persistent connection!?

Thanks in advance.

SionnachRoe

For a nice write up on the topic of Persistent vs not you might look at

http://stackoverflow.com/questions/3765925/persistent-vs-non-persistent-which-should-i-use

As mentioned, the decision can depend upon the shape of the application but the post is over 2 years old, it doesn't mention that connection pooling is available with mySql (it may not have been then) so a non-persistent shape using a connection pool may be the best solution; see http://www.connectionstrings.com/mysql and have a look down the page for the pooling example.

Ziycon

Pooling looks the way to go, are there any examples of a database class using pooling so that I can just call the class to open the connection and then when all is done call the class to close the connection or do I need to pass the connection back to were ever it is required?

Do either of the below make sense, I would prefer the second option if possible as it doesn't require passing the connection back to were the connection is being called from.

using System;
using Mysql.Data.MySQLClient;

namespace test
{
	class database
	{
		public MySqlConnection openConn() 
		{
			private static MySqlConnection conn = null;
			private string dbServer = "localhost";
			private string dbName = "test";
			private string dbUser = "username";
			private string dbPass = "password";
			private int dbCommandTimeout = 30;

			try
			{
				string connStr = "Server="+dbServer+";Database="+dbName+";Uid="+dbUser+";Pwd="+dbPass+";Pooling=True;default command timeout="+dbCommandTimeout+";";
				conn = new MySqlConnection(connStr);
				conn.Open();
			}
			catch (Exception e)
			{
				Console.WriteLine(e);
			}

			return conn;
		}

		public void closeConn(MySqlConnection connIn) 
		{
			connIn.Close();
		}
	}
}

or

using System;
using Mysql.Data.MySQLClient;

namespace test
{
	class database
	{
		private static MySqlConnection conn = null;

		public void openConn() 
		{
			private string dbServer = "localhost";
			private string dbName = "test";
			private string dbUser = "username";
			private string dbPass = "password";
			private int dbCommandTimeout = 30;

			try
			{
				string connStr = "Server="+dbServer+";Database="+dbName+";Uid="+dbUser+";Pwd="+dbPass+";Pooling=True;default command timeout="+dbCommandTimeout+";";
				conn = new MySqlConnection(connStr);
				conn.Open();
			}
			catch (Exception e)
			{
				Console.WriteLine(e);
			}
		}

		public void closeConn() 
		{
			conn.Close();
		}
	}
}

COYW

The 2nd option is definitely the way to go. However, the conn declaration in the database class will need to change to public/internal, otherwise it will not be accessible in your DAL. I would also have a method in that class called IsReady which will check to see if the connection is available (ie. not null and the connection state is open) for use in your DAL as a check before you execute calls against the database.

ChRoMe

This would probably be a good time to investigate using an ORM such as nHibernate

http://nhforge.org/