What is the correct way to deal with a MySql database from a .Net application

Question

In the applications I currently write, I deal a lot with a couple of MySql databases. I know I am doing this the wrong way, so you don't need to tell me that, but how do I do this the correct way? As in, what is the correct practice for dealing with a MySql database from a .Net application.

At the minute I use a class, which is below:

using System;
using MySql.Data.MySqlClient;

namespace WhateverProjectImWorkingOn
{
    class MySql
    {
        public string myConnectionString = String.Format("SERVER={0}; DATABASE={1}; UID={2}; PASSWORD={3}", "8.8.8.8", "foobar", "foo", "barr");

        public string Select(string mySqlQuery)
        {
            MySqlConnection connection = new MySqlConnection(myConnectionString);
            MySqlCommand command = connection.CreateCommand();
            MySqlDataReader Reader;

            command.CommandText = mySqlQuery;

            connection.Open();

            Reader = command.ExecuteReader();

            string thisrow = "";

            while (Reader.Read())
            {
                for (int i = 0; i < Reader.FieldCount; i++)
                {
                    thisrow += Reader.GetValue(i).ToString();
                }
            }

            Reader.Close();
            connection.Close();
            return thisrow;
        }

        public void Update(string mySqlQuery)
        {
            MySqlConnection mangoConnection = new MySqlConnection(myConnectionString);
            MySqlCommand command = mangoConnection.CreateCommand();

            command.CommandText = mySqlQuery;

            mangoConnection.Open();
            MySqlDataReader reader = command.ExecuteReader();
            mangoConnection.Close();
        }
    }
}

I instanciate this class and then use the Select method to select data like so:

MySql mySql = new MySql();
string whateverIWant = mySql.Select("Select `MyValue` From `FooBarr` Where `Foo` = 'Barr'");

I run update queries like so:

mySql.Update("UPDATE `tblFooBarr` SET `acme`='Foo' WHERE `tnt`='barr';");

Before you start, yes I am thoroughly ashamed of myself for my sloppy sloppy code, but if you could help me improve I would be most appreciative!

Thanks

You should wrap your MySqlConnection classes in a using statement to ensure that the connection is closed even in case of an error. — Hans, Jan 15 '12 at 17:47
That seems doable, what about the raw SQL in my code? I am of the understanding that this is just a massive nono, as is hardcoding the connection parameters. — JMK, Jan 15 '12 at 17:49
You should also protect your code against SQL injection by using the type safe MySqlParameter. For SQL injection see the following link http://msdn.microsoft.com/en-us/library/ff648339.aspx. — Hans, Jan 15 '12 at 18:21
You could use NHibernate - see for instance [How to configure fluent nHibernate with MySQL](http://stackoverflow.com/questions/626339/how-to-configure-fluent-nhibernate-with-mysql). — TrueWill, Jan 15 '12 at 19:51

Steve Wilkes · Accepted Answer · 2012-01-15T19:33:42.897

First off, I'd make an interface to put between your MySql database and your code. This decouples your application from the MySql database classes; something like this:

public interface IDbProvider : IDisposable
{
    void Open();
    void BeginTransaction();
    IDataReader ExecuteReader(string query);
    int ExecuteNonReader(string query);
    int GetLastInsertId();
    void Commit();
    void Rollback();
    void Close();
}

Within your MySql-specific IDbProvider implementation you should get the connection string from the ConfigurationManager.ConnectionStrings collection rather than hard-coding it.

Next, you could put your queries in a custom config section which gets hard-coded, MySql-syntax-specific queries out of your code, like this:

<queries>
  <SelectFoo>
    <![CDATA
    Select `MyValue` From `FooBarr` Where `Foo` = '{value}'
    ]>
  </SelectFoo>
</queries>

...then use a custom configuration provider to expose those queries to your application via an enumeration and a library class, which decouples your application from the knowledge it's using SQL:

public enum AvailableQuery
{
    SelectFoo
}

public class QueryLibrary
{
    private readonly AvailableQueryConfigSection _availableQueries;

    public QueryLibrary()
    {
        this._availableQueries = 
            (AvailableQueryConfigSection)
            ConfigurationManager.GetSection("queries");
    }

    public string GetQuery(AvailableQuery query)
    {
        // return query from availableQueries
    }
}

Finally, you can have a repository class which uses the QueryLibrary to get the queries to send to the IDbProvider so it can return objects or execute updates, which decouples your application from databases altogether:

public class FooRepository
{
    public Foo GetFooByValue(string value)
    {
        string query = this._queryLibrary
            .GetAvailableQuery(AvailableQuery.SelectFoo)
            .Replace("{value}", value); // <- or better still, use parameters

        using (IDataReader reader = this._dbProvider.ExecuteReader(query))
        {
            // Or get the values out of the reader here and pass them into 
            // a constructor instead of passing in the reader itself:
            return new Foo(reader);
        }
    }
}

Obviously there's a bunch of error handling, dependency injection setup and other stuff to go in there, but hopefully this should give you a structure to start from :)

This is great, I knew there must be a better way to do this! Thankyou! — JMK, Jan 16 '12 at 07:56

score 3 · Answer 2 · answered Jan 15 '12 at 18:24

Nothing specific to MySQL in here. If you are dealing with multiple DBMS's then one way to go is to use the .net db interfaces IDbCommand, IDbConnection etc..

Put the connection string in app.config or some such.

Raw SQL in your code, bad? You could look at entity frameworks, LinQToSQL, a stored procedure based design, but that is have a go, learn and see what fits. The real problem with a lot of raw SQL, is your code is inextricably tied to your backend. The trick to managing that is to 'put it all in one place'. One 'model'.dll, one interface in each class. Once class to deal with each table etc. Don't litter your code base with it. Trawling through your entire code base because you renamed the orders table, that's the no no.

Why are you using a reader in that Update() method??? Command has a method for that.

And as @Hans said look at using, you have potential resource leaks all over the place.

Thanks, I updated the tag from MySql to DBMS, and also for the reply, it helped a lot! — JMK, Jan 16 '12 at 07:54

Spikolynn · Answer 3 · 2017-09-13T17:14:25.107

Expanding TrueWill's comment:

install fluent nhibernate and dependencies with nuget
write database configuration code to register the database connection on application startup, load mappings and create a session
write class mappings for your objects YourObjectMap : ClassMap<YourObject> listing database keys, relations etc. in the constructor or use Auto mapping - best if you don't have an existing/too complex database
use the session.Save(yourObject), .Delete, .SaveOrUpdate etc. to manipulate the database

Take care of session type, locking, transactions etc. depending on your needs for concurrency

Here is a tutorial

What is the correct way to deal with a MySql database from a .Net application

3 Answers3