How to read select query on mysql db using c#

Question

I have a C# based api and I send queries to a mysql server. I wonder how can i read the id from a select to a table on C# Note that I am using MySql.Data.MySqlClient;

My code until the execute is this one below. But in this step I wonder how can I retrieve the desired id. I used ExecuteNotQuery but it seems it does not fit on what I need.

 string connectionString = @"server=x.x.x.x;userid=xxxx;password=xxxxxx;database=testdatabase";
 string getLastStoryIdQuery = "SELECT MAX(ID) FROM  testdatabase.test";

 MySqlCommand getLastTestIdCommand = new MySqlCommand(getLastStoryIdQuery, mySqlConnection);

 int lastId = getLastStoryIdCommand.ExecuteNonQuery();

How can I retrieve the result as an Integer or in worst case as a string response? Thank you in advance. :)

Possible duplicate of [How to read certain data from mysql results in C#](https://stackoverflow.com/questions/10623002/how-to-read-certain-data-from-mysql-results-in-c-sharp) — gunr2171, Mar 26 '19 at 19:20
See also https://stackoverflow.com/questions/7604893/how-do-i-select-an-entire-row-which-has-the-largest-id-in-the-table — gunr2171, Mar 26 '19 at 19:20
@gunr2171, as for the sql part I was ok, I asked how can I read the result :) — Grigoris Loukidis, Mar 26 '19 at 19:28

Robert McKee · Accepted Answer · 2019-03-26T19:47:41.430

int lastId = Convert.ToInt32(getLastStoryIdCommand.ExecuteScalar());

You can find the documentation on MySqlCommand here: https://dev.mysql.com/doc/dev/connector-net/8.0/html/T_MySql_Data_MySqlClient_MySqlCommand.htm

The method ExecuteNonQuery returns the number affected by the query, while ExecuteScalar returns the first column of the first row. You can also use ExecuteReader to get a datareader so that you can read a resultset the database produces.

In practice, I rarely use DbCommand/DbReader anymore and prefer to just use Dapper for database access in most cases where performance isn't absolutely critical. It simplifies parameter creation, and object filling which serves the vast majority of my use cases.

Dapper would look like this:

string connectionString = @"server=x.x.x.x;userid=xxxx;password=xxxxxx;database=testdatabase";
string getLastStoryIdQuery = "SELECT MAX(ID) FROM  testdatabase.test";
int lastId;
using(var conn = new MySqlConnection(connectionString))
{
  lastId=conn.Query<int>(getLastStoryIdQuery).First();
  // you can also do the following in this instance, but you will use the
  // above for results that return multiple rows or multiple columns
  //lastId=conn.ExecuteScalar<int>(getLastStoryIdQuery);

  // Here is how you use parameters:
  // var something = conn.ExecuteScalar<int>("SELECT id FROM testdatabase.test WHERE id=@param",new {param = 10});

  // This gets multiple columns and rows into a List<person> (assuming you have a person class with fname,lname,dob properties):
  // var people = conn.Query<person>("SELECT fname,lname,dob FROM persons WHERE dob>@start", new {start=new DateTime(2000,1,1)}).ToList();
}

How to read select query on mysql db using c#

1 Answers1