Geek Answers Handbook

What is a more efficient way to query MySQL using C #?

Based on the links on the StackOverflow website (links below), I came up with this block of code to execute queries from my C # application to the MySQL database.

using (var dbConn = new MySqlConnection(config.DatabaseConnection)) { using (var cmd = dbConn.CreateCommand()) { dbConn.Open(); cmd.CommandType = CommandType.Text; cmd.CommandText = "SELECT version() as Version"; using (IDataReader reader = cmd.ExecuteReader()) { if (reader.Read()) { Console.WriteLine("Database Version: " + reader.GetString(reader.GetOrdinal("Version"))); } } } }

The problem with this is that I have to create this massive block of code every time I have a group of requests, because I do not (and should not) leave the connection open for the life of the application.

Is there a more efficient way to create a supporting structure (nested using s, opening a connection, etc.), and instead pass my connection string and the query I want to run and return the results?

Reference questions:

These are three of those that I looked at. There were a few more, but my Google Fu can't remake them right now. All of them provide answers to the question of how to fulfill one request. I want to fulfill individual business logic requests — several of them more than once — and I don’t want to repeat unnecessary code.

What I tried: Based on a comment from nawfal, I have two methods:

 private MySqlDataReader RunSqlQuery(string query) { Dictionary<string, string> queryParms = new Dictionary<string, string>(); MySqlDataReader QueryResult = RunSqlQuery(query, queryParms); return QueryResult; } private MySqlDataReader RunSqlQuery(string query, Dictionary<string, string> queryParms) { MySqlDataReader reader = null; if (queryParms.Count > 0) { // Assign parameters } try { using (var dbConn = new MySqlConnection(config.DatabaseConnection)) { using (var cmd = dbConn.CreateCommand()) { dbConn.Open(); cmd.CommandType = CommandType.Text; cmd.CommandText = query; using (reader = cmd.ExecuteReader()) { return reader; } } } } catch (MySqlException ex) { // Oops. } return reader; }

The problem with this attempt is that the reader closes when it returns from the method.

+6
source share
5 answers

Do you consider using Object Relational Mapper (ORM) ? I love Active Record Castle and NHibernate myself, but there are many others . Entity Framework and Linq to SQL are popular Microsoft solutions.

With these tools, your queries become fairly simple CRUD method calls that do the communication and session processing for you (mostly).

+3
source

Instead of creating a reader in the using statement inside your RunSqlQuery method, you can return it directly:

 return cmd.ExecuteReader();

Then end the call to RunSqlQuery in the using statement:

 using( var reader = RunSqlQuery(....) ) { // Do stuff with reader. }
+2
source

You can use Action or Func to get what I think you need.

called like this ...

 RunSqlQuery("SELECT * FROM ...", reader => ReadResult(reader)); private bool ReadResult(MySqlDataReader reader) { //Use the reader to read the result if (!success) return false; return true; }

implemented so ...

 private bool RunSqlQuery(string query, Func<MySqlDataReader, bool> readerAction) { Dictionary<string, string> queryParms = new Dictionary<string, string>(); return RunSqlQuery(query, readerAction, queryParms); } private bool RunSqlQuery(string query, Func<MySqlDataReader, bool> readerAction, Dictionary<string, string> queryParms) { MySqlDataReader reader = null; if (queryParms.Count > 0) { // Assign parameters } try { using (var dbConn = new MySqlConnection(config.DatabaseConnection)) { using (var cmd = dbConn.CreateCommand()) { dbConn.Open(); cmd.CommandType = CommandType.Text; cmd.CommandText = query; using (reader = cmd.ExecuteReader()) { return readerAction.Invoke(reader); } } } } catch (MySqlException ex) { // Oops. return false; } }
+2
source

Why do you want to return the datareader from the method? It will be closed as soon as u wrap it inside the using . You can also assign parameters only after receiving an IDbCommand instance, so I moved this part inside the using block.

If you strictly want to return the datareader, then it is better to return the IEnumerable<IDataRecord> using the yield keyword.

 private IEnumerable<IDataRecord> RunSqlQuery(string query, Dictionary<string, string> queryParms) { using (var dbConn = new MySqlConnection(config.DatabaseConnection)) { using (var cmd = dbConn.CreateCommand()) { if (queryParms.Count > 0) { // Assign parameters } cmd.CommandText = query; cmd.Connection.Open(); using (var reader = cmd.ExecuteReader()) foreach (IDataRecord record in reader as IEnumerable) yield return record; } } }

Or it’s even better to read the data there yourself and return the data back as in this question . Thus, you do not need to rely on classes in db namespaces outside of your db class.

+1
source

I was on that road. As recommended by ORM, I would recommend EF Code First. Sorry I'm a little off topic, but I never thought about returning to this template after using EF Code First.

Before Code First, EF was quite painful, but now it is ripe, and if you have a database, you potentially change the structure, that is, a new table or column is required for a new application function, then my approach of EF Code First is my recommendation, If is it another third-party database or a database for another application, then someone else controls its structure, then you need to update your data model only when they are deployed, then I would not use Code First and instead just use the traditional EF, where you generate / update in shu model based on an existing database.

Please note that you can use EF and start using it while you keep the existing code base as is. It depends on how much your infrastructure depends on the use of ADO objects. The EF Power Tools extension allows you to generate the First code model, or you can simply use the traditional non-code first EF to create a modal from the database.

When you want to request, you can qualify for the business of what you are trying to request without having a lot of infrastructure code or wrappers. Another thing about wrappers like the one above is when you have to revert to using the ADO API instead of your RunSqlQuery helper.

This is a trivial example, since I usually don’t have methods like GetActivePeopleNames, but just send the request to where it is needed. Overhead from the point of view of fluffy code is not enough, so impose my request on top of everything else. Although I am implementing some presenter patterns for abstractly transforming queries and data from business logic.

 HREntities db = new HREntities(); private ICollection<string> GetActivePeopleNames() { return db.People.Where(p => p.IsActive).Select(p => p.FirstName + " " + p.LastName) .ToList(); }

I did not need to create a parameter object. I could use some variable for Where(p => p.IsActive == someBool) , and that would be safe from SQL injection in this context. The connection is processed automatically. I can use .Include to capture related objects in the same connection, if necessary.

0
source

More articles:


All Articles