Marvinatorrr Marvinatorrr - 4 years ago 163
C# Question

How to select mysql value and store it into a variable C#

so I have a table and I want to select a value from the table and store it into a C# variable.

What I have right now is this

String connString = System.Configuration.ConfigurationManager.ConnectionStrings["WebAppConnString"].ToString();
conn = new MySql.Data.MySqlClient.MySqlConnection(connString);
try
{
conn.Open();
queryStr = "";
queryStr = "SELECT ipaddress from robolinks where roboID=?roboID";

cmd = new MySql.Data.MySqlClient.MySqlCommand(queryStr, conn);
cmd.Parameters.AddWithValue("?roboID", roboID);
cmd.ExecuteReader();
conn.Close();

//after closing the connection, get the ipaddress and key it into web browser.

}
catch(Exception)
{

}


So I know I have already selected it but how do I manipulate ipaddress in C#?

Thanks for the assistance!

Answer Source

First of all if you use ExecuteReader then you need to get values first and only then close connection.
Second is ExecuteReader is returning SqlDataReader so if you want to get some value from Sql you need to assign the return variable of ExecuteReader to some value

SqlDataReader dr = cmd.ExecuteReader();

Now, using manual ( https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldatareader(v=vs.110).aspx ) start coding:

string roboIp;
// Because you know that id is int so imho you don't need parameters.
queryStr = "SELECT ipaddress from robolinks where roboID="+roboID+";";
// This is right.
cmd = new MySql.Data.MySqlClient.MySqlCommand(queryStr, conn);
conn.Open();
var dr = cmd.ExecuteReader();
// Now check if any rows returned.
if (dr.HasRows)
{
    dr.Read();// Get first record.
    roboIp = dr.GetString(0);// Get value of first column as string.
}
dr.Close();// Close reader.
conn.Close();// Close connection.

But if you select only one value, then better use ExecuteScalar instead of ExecuteReader

string roboIp;
queryStr = "SELECT ipaddress from robolinks where roboID="+roboID+";";
cmd = new MySql.Data.MySqlClient.MySqlCommand(queryStr, conn);
conn.Open();
var queryResult = cmd.ExecuteScalar();//Return an object so first check for null
if(queryResult!=null)
    // If we have result, then convert it from object to string.
    roboIp = Convert.ToString(queryResult);
else
    // Else make id = "" so you can later check it.
    roboIp = "";
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download