henri_007 henri_007 - 1 month ago 9
MySQL Question

How to read one row from mysql?

I have profiles of users in mysql and I want to read one row (of some user) and add values to object "user"

Here is part of my code: ( function that return user)

connection.Open();

person person_1 = new person();

//1. Read name
string sql = "select name from profili where name=@name and surname=@surname";
cmd = new MySqlCommand(sql, konekcija);
cmd.Parameters.Add("@name", name);
cmd.Parameters.Add("@surname", surname);

MySqlDataReader reader = cmd.ExecuteReader();

if (reader.Read()) {

person_1.name = reader["name"].ToString();


//2. Read surname
string sql = "select surname from profili where name=@name and surname=@surname";
cmd = new MySqlCommand(sql, konekcija);
cmd.Parameters.Add("@name", name);
cmd.Parameters.Add("@surname", surname);
person_1.surname = reader["surname"].ToString();
return person_1;


With this I only get name, surname is null ( see that in debbuger)

Answer

Your code makes no sense. You already know the surname and the name because you pass them as parameters for the WHERE condition. A part from this, the problem with surname being null is the fact that you don't execute the second command.

You should add

//2. Read surname
string sql = "select surname from profili where name=@name and surname=@surname";
cmd = new MySqlCommand(sql, konekcija);
cmd.Parameters.Add("@name", MySqlType.VarChar).Value = name;
cmd.Parameters.Add("@surname", MySqlType.VarChar).Value = surname;
reader = cmd.ExecuteReader();
person_1.surname = reader["surname"].ToString();

Said that, it makes no sense to execute a command two times to retrieve a single row. Your query text could easily add all the column names that you want to retrieve and execute just one read

//1. Read the row matching the known surname and name 
string sql = @"select name, surname, column1, column2, colx 
              from profili where name=@name and surname=@surname";

cmd = new MySqlCommand(sql, konekcija);
cmd.Parameters.Add("@name", MySqlType.VarChar).Value = name;
cmd.Parameters.Add("@surname", MySqlType.VarChar).Value = surname;
MySqlDataReader reader = cmd.ExecuteReader();
if (reader.Read()) {
   person_1.name = reader["name"].ToString();
   person_1.surname = reader["surname"].ToString();
   person_1.Property1 = reader["column1"].ToString();
   ...and so on for other columns