Installation

Nuget package url: https://www.nuget.org/packages/MySql.Simple/ On the package manager console enter:

Install-Package MySql.Simple

Connecting to a database

In order to set up a new connection, you will need to create a new instance of Mysql.Simple.Database by providing the connection string. Since the Database class allows an implicit operator for the connection string, you can directly pass the connection string without a constructor to create a new Database connection. The following is an example of a connection string and creating a new insance of a database.

    string ConnectionString = "Server=;Uid=;Database=;password=;SslMode=none";

    Database db = ConnectionString;

However for readability, if needed, there is always the option to use the constructor with the connection as a parameter as follows:

    Database db = new Database(ConnectionString);

The database object contains an object of MySql.Data's MySqlConnection that has been connected with the connection string. If required, this object can be accessed by the help of an implicit conversion or by using the Connection property. The two lines below help fetch the same object in two different ways.

    MySqlConnection conn1 = db;

    var conn2 = db.Connection;

The database object also implements IDisposable. On dispose, it closes the connection and disposes the MySqlConnection object. Hence this object can be created with using to ensure that the connection is closed and the object is disposed.

        using (Database db = ConnectionString)
        {
            
        }

Quering

There are four ways (four functions) queries can be excuted. These are similar to those of Webmatrix.Data. All four of these function support a syntax that simplifies the usage of String.Format when calling the function. So instead of using: (String.Format("INSERT INTO User (Username, FirstName) Values ('{0}', '{1}')", Username, Firstname)) you could simply do:("INSERT INTO User (Username, FirstName) Values ('@0', '@1')", Username, Firstname). Note that @0 and @1 are @ followed by the index of the params. So @0 refers to the param at index 0 which is 'Username'.

The following are the four different ways to execute sqls.

Simply execute a query (no output)

To directly execute a query you can simply call the .Execute() method in the Database object. This would return an int that specifies the number of rows effected in the database.

        int RowsEffected = db.Execute("INSERT INTO Users (Username, FirstName) Values ('@0', '@1')", Username, Firstname);

Run query to get a single value output

To get just a single value as an output, use the .QueryValue() method of the Database object. This function would return the first column of the first row of the result of the query.

    using (var result = db.QueryValue("SELECT COUNT(ID) FROM Users"){

        // setting to an existing variable with the same datatype
        User user = new User(){
            ID = result;
        };

        // or directly returning assuming the return type is the same as the datatype
        return result;
    }

Run query to get an entire row

To fetch a entire row, use the function .QuerySingle(). This function returns an object of the class MySql.Simple.QueryResult. This object contains and uses an object of MySql.Data.MySqlDataReader. Since this object implements disposable, it can be used with using. The dispose function disposes the MySqlDataReader object. Once you fetch the QueryResult object, you can use this object to access columns of the row by the column name or index.

    using (var result = db.QuerySingle("SELECT * FROM User WHERE ID = @0", ID))
    {
        
        if (result){ // check if a row was returned. avoid if you are sure the query returns atleast one row
           User user = new User(){
                ID = result["ID"],
                UserName = result["username"],
                FirstName = result["FirstName"],
                LastName = result["LastName"],
                DateOfBirth = result["DateOfBirth"]
            };
        }

        
    }    

In the above code, note that once the value of the column is fetched, when it is being assigned to a variable, it returns in the same type as the variable it is being assigned to. For instance user.ID is an int. So result["ID"] returns an int. Since UserName is a string, result["username"] will also return string. Since DateOfBirth is of type DateTime, result["DateOfBirth"] returns DateTime. Note that all these assignments directly use implisit operators and the functions of MySqlDataReader, .GetInt32(), .GetString() and .getDateTime() respectively. In other words these operation have no performance issues as compared to directly using these functions. These assignments are compatable with every primitive datatypes and with the types MySqlDateTime, MySqlDecimal and MySqlGeometry.

If you require to use the reader object within the result object, you can either use the implicit converter or the property reader.The two lines below help fetch the same object in two different ways.

    MySqlDataReader reader1 = result;

    var reader2 = result.reader;

Using a query to fetch multiple rows

Fetching multiple rows can be done with the Query() function. The process is very similar to using QuerySingle. The main difference between the two is looping. In order to loop the result, the result..Read() function is called which gets each line and returns a boolean (false if empty).

   using (var result = db.Query("SELECT username, FName, LName FROM tblUsers"))
   {
       while (result.Read())
       {
           users.Add(new TUser() {
              UserName = result["username"],
              FName = result["FName"],
              LName = result["LName"],
              DateOfBirth = result["DateOfBirth"]
           });
       }
   } 

Code friendly quering

The database object also has functions to execute code friendly sql. That is instead of adding entire large queries into one string without any syntax lighliting and later looking for the different parts of your sql, there are functions to run almost all kinds of queries to make the sql commmands more readable in the code. All these functions can be followed by the four functions mentioned above: Query(), QuerySingle(), QueryValue() and Query().

Select function

    db.Select("id, fname", "lname")
        .From("user u")
        .Join("entries e", "u.id = e.userID")
        .Where("username LIKE $0", query)
        .Order("fname")
        .Limit(20)
        .Query();

• Select: Select is optional (default *), you can directly start with the second function From. You can mention all fields under a string with commas or as multiple strings or both.
• From: From is the only mandatory. Can be used directly from db or continuing from select. From can again take multple table names similar to the select function above
• Join: Multiple joins can be chained one after another. All four join functions are available.

Debug functionality

For debugging porpose, a property 'FullQuery' is provided that can be used in place of the four functions. This property provides the exact query that would be executed when the chained functions run.

    string sql =
    db.Select("id, fname", "lname")
        .From("user u")
        .Join("entries e", "u.id = e.userID")
        .Where("username LIKE $0", query)
        .Order("fname")
        .Limit(20)
        .FullQuery;

Or to reuse the query, the following code such as the following can be used:

    var q = db.Select("id, fname", "lname")
        .From("user u")
        .Join("entries e", "u.id = e.userID")
        .Where("username LIKE $0", query)
        .Order("fname")
        .Limit(20);

    string sql = q.FullQuery;
    using (var users = q.Query());

Advanced Conditions

If the where clause is complex with nested conditions, functionalities for convenient management are provided. These can be used in the where clause inside under the select and update functions.

First You will need to create the conditions. Creating a condition can be done as follows.

    var cond = Conditioner.Build("name LIKE $0", name);  // the usual format with the object(s)
    var cond2 = Conditioner.Build("fname = $0", fname);  // the usual format with the object(s)
    var cond3 = Conditioner.Build("lname", lname);  // with no $ or @, automatically converts to "lname = 'lame'" if lname were 'lame'
    var cond4 = Conditioner.Build("fname = lname"); // without any object

To simplify the condition creations, you can take the help of the Conditioner class

    Conditioner c = new Conditioner(); // creates a helper object

    // now the same above code can be written with the helper object c
    var cond = c["name LIKE $0", name]; 
    var cond2 = c["fname = $0", fname]; 
    var cond3 = c["id", id];  
    var cond4 = c["fname = lname"];

Now these conditions can be uses in complex conditional statments as follows

    .WhereConditional(cond.And(cond2.Or(cond3).And(Cond4)));

Or now you can use it as follows

    .WhereConditional(cond & (cond2 ! cond3 & Cond4));