mysql basics in c#

Today I want to describe how we can work with MySQL-DBs in C#. Before Using queries and other things we have to connect to the database. Todo this set up an MySQLMgt-code-file that contains all things we need to have to work with our database. Here we got some properties like this. To use the MySsqlConnection just include the library “MySql.Data.MySqlClient”.

    static private MySqlConnection connection;
    static private string server;
    static private string database;
    static private string uid;
    static private string password;

I decide to fill these properties in the constructor and connect than to the server:

 
    static MySqlManagement()
    {
        loadServerSettings();
        connectServer();
    }

For sure we can fill the properties hard-coded but I want to make it better and I put the things into the appsettings. Just put in the things you need and you can write these values into the class-properties:

static private void loadServerSettings()
    {
        server = ConfigurationManager.AppSettings["server"];
        database = ConfigurationManager.AppSettings["database"];
        uid = ConfigurationManager.AppSettings["uid"];
        password = ConfigurationManager.AppSettings["password"];
    }

To connect to the server we need a connection-string and a MySQL-Connection.

static private void connectServer()
    {
        string connectionString =
            "SERVER=" + server + ";" +
            "DATABASE=" + database + ";" +
            "UID=" + uid + ";" +
            "PASSWORD=" + password + ";";
 
        connection = new MySqlConnection(connectionString);
 
        try
        {
            connection.Open();
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }

Wit the connection. Open we are able to give some instruction/queries into our database. Here we can insert, modify and delete data or change the structure of the data as well. In the following you will see an easy insert-mysql-stament fired into the mysql-db.

string query = "INSERT INTO link (url, provider_id, posting_datetime) VALUES(" +
                    "'" + url + "', " +
                    "'" + providerId + "', " +
                    "'" + postingDateTime.ToString("yyyy-MM-dd H:mm:ss") + "')";
 
                MySqlCommand cmd = new MySqlCommand(query, connection);
                cmd.ExecuteNonQuery();

Another thing that you should know in term of mysql-c# basics is the use of return value of queries. Here is method that tells me if an entry already exists:

static public bool UrlExists(string url)
       {
           string query = "SELECT COUNT(*) FROM link WHERE url = '" + url + "'";
           MySqlCommand cmd = new MySqlCommand(query, connection);
 
           int Count;
           Count = int.Parse(cmd.ExecuteScalar() + "");
 
           return Count > 0;
       }

@ the end of your code: Make sure to close the connection to the MySQLServer with:

connection.Close();

Have fun with it! 🙂
 

Advertisements