CODEWITHSUNDEEP
c#mysqlstringvariables
Kragalon
Kragalon

Reputation: 450

Set C# variable as a Select from database

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!

Upvotes: 0

Views: 2055

Answers (1)

Joel Coehoorn
Joel Coehoorn

Reputation: 416131

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.

Upvotes: 2

Related Questions