Tip [Sharing] MySqlExpress - Simplifies the Usage of MySQL in C#

[adriancs]

Hi, guys, I have published a class library called "MySqlExpress" and I'm excited to share it here with you.

The class library stands on top of MySqlConnector and it aims to simplify the usage of MySQL in C# / .NET environment.

The primary purpose of this library is to simplify the implementation/usage of MySQL in C#. This is to encourage the rapid application development with MySQL in C#.

Project Site: GitHub - adriancs2/MySqlExpress: MySqlExpress simplifies the implementation of MySQL in C#

Here is one of the example of what MySqlExpress can do.

Assuming that we have a MySQL table like this:

CREATE TABLE `player` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `code` varchar(10),
  `name` varchar(300),
  `date_register` datetime,
  `tel` varchar(100),
  `email` varchar(100),
  `status` int unsigned,
  PRIMARY KEY (`id`));

MySqlExpress Helper can help to generate the C# class object of "player":

public class obPlayer
{
    public int id { get; set; }
    public string code { get; set; }
    public string name { get; set; }
    public DateTime date_register { get; set; }
    public string tel { get; set; }
    public string email { get; set; }
    public int status { get; set; }
}

To get a row object of "player", in C#, we can do like this:

int id = 1;

// declare the object
obPlayer p = null;

using (MySqlConnection conn = new MySqlConnection(config.ConnString))
{
    using (MySqlCommand cmd = new MySqlCommand())
    {
        cmd.Connection = conn;
        conn.Open();

        MySqlExpress m = new MySqlExpress(cmd);

        // parameterize the values
        Dictionary<string, object> dicParam = new Dictionary<string, object>();
        dicParam["@vid"] = id;

        p = m.GetObject<obPlayer>($"select * from player where id=@vid;", dicParam);

        conn.Close();
    }
}

To get multiple rows of "player" object, we can do it like this:

// declare the object list
List<obPlayer> lst = null;

using (MySqlConnection conn = new MySqlConnection(config.ConnString))
{
    using (MySqlCommand cmd = new MySqlCommand())
    {
        cmd.Connection = conn;
        conn.Open();

        MySqlExpress m = new MySqlExpress(cmd);

        // parameterize the values
        Dictionary<string, object> dicParam = new Dictionary<string, object>();
        dicParam["@vname"] = "%adam%";

        lst = m.GetObjectList<obPlayer>($"select * from player where name like @vname;", dicParam);

        conn.Close();
    }
}

There are some more "simplified" methods available.

You guys are welcome to visit the github page of the project. I have included a demo project in the source code to show case the usage of MySqlExpress that you might be interested with.

Cheers, guys

Here's the screenshot of the Demo of using MySqlExpress included in the source code:

 

[Skydiver]

Ouch! The code deals with DataTables as its default return type when selecting data, and uses DataTables internally when trying to bind to objects. The data tables are not very efficient, but I can see how it makes writing the code more convenient.

Also kind of interesting to see that the code uses Convert.ConvertType() for the return values of ExecuteScalar<T>(), but uses its own custom type conversions/mapping when trying to bind to objects. Lots of potential there for inconsistent results unless a lot of care is taken. I recommend routing everything to the same type conversions be it always using the Convert class or always using the custom type conversions/mapping.

 

[Skydiver]

Oh! Another interesting thing: Unlike most database to object mappers which default to binding only the public fields and properties, the database operations in this code binds to the private fields of the class. To make matters more confusing, it also exposes a helper method BindPublicFields which binds not just the public fields, it also tries to bind the public properties.

And also interesting is that you can automatically bind objects or lists of objects when querying the database, but there's no way to do an insert or update passing in an object or list of objects. You'll need pass in the values for insert or update.

 

[adriancs]

Hi, Skydiver. Thanks for your feedback

Ouch! The code deals with DataTables as its default return type when selecting data, and uses DataTables internally when trying to bind to objects. The data tables are not very efficient, but I can see how it makes writing the code more convenient.

I might consider using MySqlDataReader in future development of this library, as it runs faster and consumes less memory.

Also kind of interesting to see that the code uses Convert.ConvertType() for the return values of ExecuteScalar<T>(), but uses its own custom type conversions/mapping when trying to bind to objects. Lots of potential there for inconsistent results unless a lot of care is taken. I recommend routing everything to the same type conversions be it always using the Convert class or always using the custom type conversions/mapping.

I'll try to spend some time in future to study what you've mentioned about the data conversion/mapping.

Oh! Another interesting thing: Unlike most database to object mappers which default to binding only the public fields and properties, the database operations in this code binds to the private fields of the class. To make matters more confusing, it also exposes a helper method BindPublicFields which binds not just the public fields, it also tries to bind the public properties.

I have just updated the library to auto bind any matching fields and properties (both public and private).

The helper method BindPublicFields is no more using and it should be deleted from the library (and I will surely delete it later).

The Helper app can now generate the fields/properties in 3 modes.

Mode 1: public properties only
Mode 2: public fields only
Mode 3: private fields + public properties

and Mode 1 is the default option.

And also interesting is that you can automatically bind objects or lists of objects when querying the database, but there's no way to do an insert or update passing in an object or list of objects. You'll need pass in the values for insert or update.

Back to the very early stage (a few years ago), I did pass the object (or list of objects) for insert and update. But, soon, in practical, I faced some problems of using this attempt.

For example, let's say a table of "user" that has about 50 columns/fields.

I need to perform an update for the password column/field only, which is only 1 or 2 columns/fields.

Assuming that I only needed to update 2 columns/fields (password & salt)

2 fields out of 50 fields are needed to be updated.

If I pass the whole object of "user", 48 fields are blank, only 2 fields have data. The 48 blank fields will overwrite data in the database.

So, to avoid the data being overwritten, I need to first load the full object "user" from database, then update the 2 fields, then only pass the object "user" back for updating the database.

Another workaround is to create a new custom object that contains 3 fields only:

public class UpdatePwd
{
    public int id { get; set; }
    public string pwd { get; set; }
    public string salt { get; set; }
}

Then, in stead of passing the full object of "user", just pass this tiny class of "UpdatePwd".

Which means, for every unique situation of "insert" or "update", a customized class object is needed.

Which also means, I need to create a tool that can easily allows me to create this custom class with ease.

Objects that are created for the purpose of "insert" or "update" are normally not re-usable.

I came out with the solution of generating the "dictionary entries".
Create a tool that can generate the "dictionary entries" with ease,
and the only work left is to choose the "entries" that we need and fill in the data into the dictionary.

Configuring required dictionary entries are less effort than setting up a custom class.

dictionary entries are quite direct and easy to be maintained and expanded on the go directly at the MySQL connection block.

class object on the other hand, is normally located on another separate file, or it can located at the same file, but different sections.
The programmer needs to refer to different files or scrolling up and down the code to look for the sections for maintaining the code or expanding the code.
this is like taking care 2 different locations for 1 purpose. code maintenance work is more.

by using the dictionary, it will be taking care at just 1 location. code maintenance work is lesser.

setting up class object requires some extra efforts.

Therefore, I find out that, passing object requires quite some time and efforts to type out the class. and I felt this approach is not practical in development time saving.

 

[adriancs]

You might be have different opinion, but that's ok. I'm open up to different opinions/ways of how a thing can be done differently.

cheers

 

[adriancs]

So, in comparison of passing object or dictionary, I have made the following simulation:

Method 1: passing object

creates attributes and class object

class PrimaryKey : Attribute
{

}

class TableName : Attribute
{
    public string Name = "";

    public TableName(string name)
    {
        Name = name;
    }
}

[TableName("user")]
class UpdatePwd
{
    [PrimaryKey]
    public int id { get; set; }
    public string pwd { get; set; }
    public string salt { get; set; }
}

Perform the saving:

UpdatePwd p = new UpdatePwd()
{
    id = 1,
    pwd = "new password",
    salt = "new salt"
};

using (MySqlConnection conn = new MySqlConnection(config.ConnString))
{
    using (MySqlCommand cmd = new MySqlCommand())
    {
        cmd.Connection = conn;
        conn.Open();

        MySqlExpress m = new MySqlExpress(cmd);

        m.Save(p);

        conn.Close();
    }
}

Method 2: passing the dictionary

using (MySqlConnection conn = new MySqlConnection(config.ConnString))
{
    using (MySqlCommand cmd = new MySqlCommand())
    {
        cmd.Connection = conn;
        conn.Open();

        MySqlExpress m = new MySqlExpress(cmd);

        Dictionary<string, object> dic = new Dictionary<string, object>();
        dic["pwd"] = "new password";
        dic["salt"] = "new salt";

        m.Update("user", dic, "id", 1);

        conn.Close();
    }
}

I can make the Helper tool to automatic generate the keywords for class attribute of "PrimaryKey" and "TableName".

but anyway, which method do you prefer? in terms of time and efforts saving for code development

 

[Skydiver]

Unless you aren't writing object oriented code in C#, the reality will be that you will already have the UpdatePwd class holding the information. So your method will look closer to:

void UpdatePassword(UpdatePwd updatePwd)
{
    using (MySqlConnection conn = new MySqlConnection(config.ConnString))
    {
        using (MySqlCommand cmd = new MySqlCommand())
        {
            cmd.Connection = conn;
            conn.Open();

            MySqlExpress m = new MySqlExpress(cmd);

            Dictionary<string, object> dic = new Dictionary<string, object>();
            dic["pwd"] = updatePwd.pwd;
            dic["salt"] = updatePwd.salt;
            
            m.Update("user", dic, "id", updatePwd.id);

            conn.Close();
        }
    }
}

In an ideal situation where I would need to manage the object oriented vs relational database impedance mismatch, I would much rather do something closer to:

class MySqlExpress
{
    :
    public void Update<T>(T entity, params string[] updateFields)
    {
        // build up the command text

        // get the table name attribute from the entity

        foreach(var fieldName in updateFields)
        {
            // Match up the field name with entity fields and create parameters
        }

        // build the where clause by finding the primary key attribute
        // on the entity and pulling the value of the primary key

        // invoke the command
    }
    :
}

:

void UpdatePassword(UpdatePwd updatePwd)
{
    using (MySqlConnection conn = new MySqlConnection(config.ConnString))
    using (MySqlCommand cmd = new MySqlCommand())
    {
        cmd.Connection = conn;
        conn.Open();

        MySqlExpress m = new MySqlExpress(cmd);

        m.Update(updatePwd, "pwd", "salt");
    }
}

 

[Skydiver]

but anyway, which method do you prefer? in terms of time and efforts saving for code development

Neither. I'm more of a NoSQL guy. I was convinced that if I'm working in an object oriented world, my database should also be object oriented. So take any advice I may give related to relational databases with a huge grain of salt.

 

[adriancs]

Unless you aren't writing object oriented code in C#, the reality will be......

Thanks for the sharing of this idea, I'll appreciate the idea posted above

 

[cjard]

How does this differ to Dapper (other than being more complicated)?

How does it make MySQL simpler to use than EF Core does (which, by the way, creates C# that is naming standards compliant)?