How can I simplify the code ? (DataSet result)

Question

I have a code :

MySqlCommand cmd = connection.CreateCommand();
                cmd.CommandText = "SELECT nom, prenom from liste_personnels where mail ='" + mailTest + "'";
                MySqlDataAdapter adap = new MySqlDataAdapter(cmd);
                DataSet ds = new DataSet();
                adap.Fill(ds);
                nom = ds.Tables[0].Rows[0].ItemArray[0].ToString();
                prenom = ds.Tables[0].Rows[0].ItemArray[1].ToString();

I found how to extract variables nom and prenom but I think it's complicated.. It's possible to simplify my code - result of dataSet to have my two variables separated et in one table ?

Thanks you for help.

You'll probably get some answers here but I think this question would fit better in [CodeReview](https://codereview.stackexchange.com/) — Rui Taborda, Aug 28 '18 at 10:40
You do this `ds.Tables[0].Rows[rowIndex]["ColumnName"].ToString();` — Hossein, Aug 28 '18 at 10:40
You could remove `.ItemArray` but calling `ToString()` without null checking is risky. And if you would like to know about SQL injection [see this](https://stackoverflow.com/questions/601300/what-is-sql-injection) . — Crowcoder, Aug 28 '18 at 12:21

score 1 · Accepted Answer · answered Aug 28 '18 at 14:48

DataSet seems overkill here; I would use a simple MySqlDataReader. (Additionally, use parameters to prevent SQL injection.)

string nom = null, prenom = null;
using (var cmd = connection.CreateCommand())
{
    // create the command and bind the parameter; this prevents SQL injection
    cmd.CommandText = "SELECT nom, prenom from liste_personnels where mail = @mail";
    cmd.Parameters.AddWithValue("@mail", mailtest);
    using (var reader = cmd.ExecuteReader())
    {
        // check if a row was found in the DB; you may need to handle if it's false
        if (reader.Read())
        {
            // read the data
            nom = reader.GetString(0);
            prenom = reader.GetString(1);
        }
    }
}

Finally, if you can add another library to your project, I would use Dapper to simplify the above code:

var result = connection.Query(
        "SELECT nom, prenom from liste_personnels where mail = @mailtest",
        new { mailtest })
    .SingleOrDefault();
var nom = (string) result?.nom;
var prenom = (string) result?.prenom;

Bradley Grainger : and when my request return many results (many rows) what type of variable I must use please? When I write "var result" I have an exception : "The sequence contains several elements" and whit List<> il it does not work — Elddif, Aug 29 '18 at 12:11
In my example, I used `SingleOrDefault()` because I assumed you would only have one row with that condition `where mail = @mailtest`. You must have more than one row. If you just want to process the first one (and ignore the others), use `.FirstOrDefault()` instead of `.SingleOrDefault()`. Otherwise, use `.ToList()` and process all the items in the list. — Bradley Grainger, Aug 29 '18 at 14:20

How can I simplify the code ? (DataSet result)

1 Answers1