Set C# variable as a Select from database

Question

How would I take info stored in a Select method and transfer it to a string? I'm trying to get the max value from the match_id column and get its value from command.CommandText into the matchCode string. Where would I go from here?

    string connectString = "Server=myServer;Database=myDB;Uid=myUser;Pwd=myPass;";
    string matchCode = "";
    MySqlConnection connect = new MySqlConnection(connectString);
    MySqlCommand command = connect.CreateCommand();
    command.CommandText = "SELECT MAX(VAL(match_id)) FROM `data`";

    try
    {
       connect.Open();
       command.ExecuteNonQuery();
       matchCode = "??";
       connect.Close();
    }
    catch (Exception ex)
    {
       Console.WriteLine(ex.Message);
    }

I'm new to C#, as it's like my fourth day trying it out. Thanks for the help!

ExecuteNonQuery() is a problem: you're telling it you don't want a result. You want ExecuteScalar() - that returns an object containing the first value in the first row of your resultset. — Blorgbeard
– Blorgbeard, Commented Aug 29, 2014 at 2:21
Please don't edit your question and implement the answer you got - people reading later won't understand what's going on. — Blorgbeard
– Blorgbeard, Commented Aug 29, 2014 at 2:34

Joel Coehoorn · Accepted Answer · 2014-08-29 13:18:13Z

2

The ExecuteNonQuery() method is for INSERT/UPDATE/DELETE queries. If you're just getting a single value back, use ExecuteScalar(). If you're getting a whole result set back, use ExecuteReader() or Fill() a DataSet object.

Also, there are some things that are idiomatic to C# that you should be doing:

public int GetMatchCode()
{
    //this could be loaded from config file or other source
    string connectString = "Server=myServer;Database=myDB;Uid=myUser;Pwd=myPass;";
    string sql = "SELECT MAX(VAL(match_id)) FROM `data`";
    using (var connect = new MySqlConnection(connectString))
    using (var command = new MySqlCommand(sql, connect))
    {
        connect.Open();
        var result = command.ExecuteScalar();
        if (result == DBNull.Value)
        {              
           //what you do here depends on your application
           // if it's impossible for the query to return NULL, you can even skip this
        }
        return (int)result;
    }
}

Some of the changes need explanation:

I don't ever call .Close(). The using block takes care of that for me, even if an exception was thrown. The old code would have left the connection hanging if an exception occured.
.Net developers tend to believe in very small methods. More than that, this method ought to be part of a class that has nothing but other simple public data access methods and maybe a few private helper methods or properties for abstracting common code in the class.
There is no exception handling code here. If you have small methods that are part of a generic database access class, exception handling should be at higher level, where you are better positioned to make decisions about how to proceed.

edited Aug 29, 2014 at 13:18

answered Aug 29, 2014 at 2:25

Joel Coehoorn

418k114 gold badges582 silver badges820 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Daniel Park Over a year ago

+1 for managed IDisposable lifecycles with using. Cleans up the code much more as you can get a better grasp on object scope.

Kragalon Over a year ago

Thanks, it's working. One more thing though, how could this work if the data I was looking for was needed in Int form?

Joel Coehoorn Over a year ago

@Kragalon You'd have to change the return type to int, add a line to check for DBNull, and add a cast to int. I see now that you're using the Val() function, so I'll update the answer to show this.

Collectives™ on Stack Overflow

Set C# variable as a Select from database

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related