By using this code I want to retrieve from the DB some rows from a table:
ID int
ParentID int?
IsVisible bool
MySqlCommand myCommand = new MySqlCommand(mySelectQuery, myConnection);
myConnection.Open();
MySqlDataReader myReader = myCommand.ExecuteReader();
try
{
while(myReader.Read())
{
//here I want to retrieve the all returned data
}
}
finally
{
myReader.Close();
myConnection.Close();
}
Something like
while(myReader.Read())
{
ID = AsInteger(myReader["ID"]).Value;
Parent = AsInteger(myReader["Parent"]);
IsVisble = AsBoolean(myReader["IsVisible"]);
}
Where As... are helper functions may be like
private static int? AsInteger(Object argValue)
{
if ((argValue == null) || (argValue == DBNull.Value))
{
return null;
}
return Convert.ToInt(argValue);
}
etc
Whapping all the helper methods in the ubiquitious static helper class is usally goer, unless you rea doing all this in one class.
Watch for DBNull, it's not null. :(
And Blobs which are byte[] if they aren't DBNull. Haven't done it either but some of MySql's funny types like Date and Time might cause a furrowed brow, and enums come across as a string I seem to remember. What I remember is from an investigation some time ago whether to have MySQl as backend for one of our products as an alternative to sql server.
You can also access the columns of the result with an index
e.g. myReader[0].
Ther are loads of methods on the class, GetValues returns an array of object based on the current row.
This might come in handy as well
if (myReader.Read())
{
StringBuilder sb = new StringBuilder(myReader.FieldCount);
for(int i = 0; i<myReader.FieldCount; i++)
{
sb.AppendLine(myReader.GetName(i);
}
Console.Write(sb.ToString());
}
myReader["ParentID"] then I get an exception Could not find specified column in results: ParentID even if I want to get the select * from Table. Of course that column exist in that table
foreach (var item in myReader){} it is invoked 4 times (it's correct) but the compiler doesn't enter inside that loop