CODEWITHSUNDEEP
c#mysqldatabase
user123_456
user123_456

Reputation: 5805

How to read certain data from mysql results in C#

I want to make certain query to mysql from C# so I can get back for example name and lastname.

I don't know how to handle this in c# and I want to minimize sqlinjection.

Here is my code:

con = new MySqlConnection("server=localhost;database=Customers;uid=root;pwd=******");
con.Open();
cmd = new MySqlCommand("SELECT name,lastname FROM customer WHERE ID_customer= ?Parname;", con);
cmd.Parameters.Add("?Parname", MySqlDbType.Float).Value = customer_card;
cmd.ExecuteNonQuery();
con.Close();

I want to be able for example to read results and it will be only one result, in the 2 string variables. How to get trough the data and save it?

Thank you

Upvotes: 1

Views: 5142

Answers (2)

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 726589

A cheating but quick way of getting several strings out of a query returning one row would be as follows:

con = new MySqlConnection("server=localhost;database=Customers;uid=root;pwd=******");
try {
    con.Open();
    cmd = new MySqlCommand("SELECT concat(name,'|',lastname) FROM customer WHERE ID_customer= ?Parname;", con);
    cmd.Parameters.Add("?Parname", MySqlDbType.Float).Value = customer_card; // Are you sure that the ID is float? That's the first time I see anything like that!
    var tokens = ((String)cmd.ExecuteScalar()).Split('|');
    var firstName = tokens[0];
    var lastName = tokens[1];
    Console.Writeln("First={0}, Last={1}", firstName, lastName);
} finally {
    con.Close();
}

The idea is to combine both strings into one on the MySql side, and split them back on the C# side. This trick lets you avoid looping through a reader, closing it, and so on. It assumes that the names do not contain '|' characters.

Upvotes: 0

hjgraca
hjgraca

Reputation: 1714

You need a DataReader to iterate trough the records, instead of using cmd.ExecuteNonQuery(); use cmd.ExecuteReader(); 

try 
  { 
    MySqlDataReader myReader = cmd.ExecuteReader(); 

    // Always call Read before accessing data. 
    while (myReader.Read())  
    { 
      //This will get the value of the column "name"
      Console.WriteLine(myReader.GetString(myReader.GetOrdinal("name"))); 
    } 

    // always call Close when done reading. 
    myReader.Close(); 

    // Close the connection when done with it. 
    } 
  finally 
  { 
    con.Close(); 
  }

Upvotes: 2

Related Questions