Get Column name from MySqlDataReader
Recently someone found my blog by searching google asking how to get the column name from a MySqlDataReader. I doubt they found the answer on my blog, so I figured I’d write about, just in case anyone else needs to know.
using System;
using MySql.Data.MySqlClient;
namespace MySqlColumns
{
class Program
{
static void Main(string[] args)
{
using(MySqlConnection conn = new MySqlConnection("Server=localhost;User Id=user;Password=user;Persist Security Info=True;Database=my_database;pooling=true"))
{
MySqlCommand cmd = new MySqlCommand("SELECT * FROM users WHERE id=?id", conn );
cmd.Parameters.AddWithValue("?id", 1313);
try
{
conn.Open();
MySqlDataReader reader = cmd.ExecuteReader();
//How many columns do we have?
int count_column = reader.FieldCount;
Console.Write("Columns:\n\t");
for(int i=0; i < count_column; i++)
{
//Now you can just get the Column name by it's indexer.
Console.Write( reader.GetName(i) );
Console.Write("\n\t");
}
}
catch(Exception ex)
{
Console.WriteLine("\nError:\n" + ex.Message);
}
}
Console.WriteLine("\n\nComplete, Press any key to close.");
Console.ReadKey();
}
}
}
That’s it. You can easily get the number of Columns and the names of each column. When I first did this, I thought it was a silly example. But there is a lot of potential in here, so I decided to expand further on why I might want to know the names.
So I build an object the fills it’s self out based on the Select statement you give it.
using System;
using System.Collections;
using MySql.Data.MySqlClient;
using DB;
namespace MySqlColumns
{
class Program
{
static void Main(string[] args)
{
//Create a Command to get a single user from the database
MySqlCommand cmd = new MySqlCommand("SELECT * FROM users WHERE id=?id" );
cmd.Parameters.AddWithValue("?id", 1313);
DBObject my_user = DBObject.bySelect(cmd, Utility.connMy);
//Now Display some fun facts
Console.Write("\t");
Console.Write(my_user.GetProperty("first_name", "Not Found"));
Console.Write("\t");
Console.Write(my_user.GetProperty("last_name", "Not Found"));
Console.Write("\n");
Console.WriteLine("\n\nComplete, Press any key to close.");
Console.ReadKey();
}
}
/// <summary>
/// Basic Database Object that loads everything from a Select statement
/// </summary>
class DBObject
{
protected Hashtable _properties;
protected DBObject()
{
_properties = new Hashtable();
}
public object GetProperty(string name, object default_value)
{
//Check if it exists yet
if (!_properties.ContainsKey(name))
{
_properties[name] = default_value;
}
return _properties[name];
}
public void SetProperty(string name, object value)
{
_properties[name] = value;
}
// Get the object by the select statement
static public DBObject bySelect(MySqlCommand command, string connection)
{
DBObject obj = new DBObject();
using (MySqlConnection conn = new MySqlConnection(connection))
{
//Connect the command to the connectiong
command.Connection = conn;
try
{
//Open the connection and get the reader
conn.Open();
MySqlDataReader reader = command.ExecuteReader();
//I only want to get the frist record because I'm returning just one object.
if (reader.Read())
{
//Get all the columns
for (int i = 0; i < reader.FieldCount; i++)
{
//Get the name and the value
obj.SetProperty(reader.GetName(i), reader[i]);
}
}
}
catch (Exception ex)
{
Console.WriteLine("\nError:\n" + ex.Message);
}
}
return obj;
}
}
}
It’s a very generic database object, no type checking and you really have to know the column names to get anything useful out of it. I tend to assume that who ever is developing the object knows what the column names are but the people using the object shouldn’t need to. This issue is resolved easily by creating a new Class from the DBObject that defines a property for each column.
Oh, and if you were wondering what using DB: and Utility.connMy are, It’s just where I keep my connection strings. You should just replace Utility.connMy with your connection string.
using System;
using System.Collections;
using MySql.Data.MySqlClient;
using DB;
namespace MySqlColumns
{
class Program
{
static void Main(string[] args)
{
//Get a user by id
User my_user = User.byID(1313, Utility.connMy);
//Now Display some fun facts
Console.Write("\t");
Console.Write(my_user.FirstName);
Console.Write("\t");
Console.Write(my_user.LastName);
Console.Write("\n");
Console.WriteLine("\n\nComplete, Press any key to close.");
Console.ReadKey();
}
}
// Basic Database Object that loads everything from a Select statement
class DBObject
{
protected Hashtable _properties;
protected DBObject()
{
_properties = new Hashtable();
}
// Sets a property in the object
protected object GetProperty(string name, object default_value)
{
//Check if it exists yet
if (!_properties.ContainsKey(name))
{
_properties[name] = default_value;
}
return _properties[name];
}
// Gets a property from the object
protected void SetProperty(string name, object value)
{
_properties[name] = value;
}
// Get the object by the select statement
protected DBObject bySelect(MySqlCommand command, string connection)
{
DBObject obj = new DBObject();
using (MySqlConnection conn = new MySqlConnection(connection))
{
//Connect the command to the connectiong
command.Connection = conn;
try
{
//Open the connection and get the reader
conn.Open();
MySqlDataReader reader = command.ExecuteReader();
//I only want to get the frist record because I'm returning just one object.
if (reader.Read())
{
//Get all the columns
for (int i = 0; i < reader.FieldCount; i++)
{
//Get the name and the value
this.SetProperty(reader.GetName(i), reader[i]);
}
}
}
catch (Exception ex)
{
Console.WriteLine("\nError:\n" + ex.Message);
}
}
return obj;
}
}
// Basic User object
class User : DBObject
{
public string FirstName
{
get
{
return (string)this.GetProperty("first_name", string.Empty);
}
}
public string LastName
{
get
{
return (string)this.GetProperty("last_name", string.Empty);
}
}
public string FullName
{
get
{
return (string)this.GetProperty("first_name", string.Empty) + " " + (string)this.GetProperty("last_name", string.Empty);
}
}
//Get a user by ID
static public User byID(int id, string connection)
{
User obj = new User();
//Create a command to get a user by ID
MySqlCommand cmd = new MySqlCommand("SELECT * FROM users WHERE id=?id");
cmd,Parameters.AddWithValue("?id", id);
//Now fill out the object
obj.bySelect(cmd, connection);
return obj;
}
}
}
So while I was playing around with it, I decided to add some generics and even a little Linq to it. The result is better than I had expected. I can easily get a list from any subclass of DBObject and use link to filter and what not.
using System;
using System.Collections;
using System.Collections.Generic;
using System.Reflection;
using System.Linq;
using MySql.Data.MySqlClient;
using DB;
namespace MySqlColumns
{
class Program
{
static void Main(string[] args)
{
//Let's get all the users by ID
List<User> user_list = User.All(Utility.connMy);
//Lets try some Linq
var list = from user in user_list where user.ID == 1313 select user;
foreach (User user in list)
{
//Get all the properties
user.Fill(Utility.connMy);
//Display some information
Console.WriteLine(user.ID);
Console.WriteLine("\t" + user.FullName);
}
//Get a user by id
User my_user = User.byID(1313, Utility.connMy);
//Now Display some fun facts
Console.Write("\t");
Console.Write(my_user.FirstName);
Console.Write("\t");
Console.Write(my_user.LastName);
Console.Write("\n");
Console.WriteLine("\n\nComplete, Press any key to close.");
Console.ReadKey();
}
}
// Basic Database Object that loads everything from a Select statement
class DBObject
{
protected Hashtable _properties;
protected DBObject()
{
_properties = new Hashtable();
}
// Sets a property in the object
public object GetProperty(string name, object default_value)
{
//Check if it exists yet
if (!_properties.ContainsKey(name))
{
_properties[name] = default_value;
}
return _properties[name];
}
// Gets a property from the object
public void SetProperty(string name, object value)
{
_properties[name] = value;
}
// Fill out our properties from the command.
protected void Fill(MySqlCommand command, string connection)
{
using (MySqlConnection conn = new MySqlConnection(connection))
{
//Connect the command to the connectiong
command.Connection = conn;
try
{
//Open the connection and get the reader
conn.Open();
MySqlDataReader reader = command.ExecuteReader();
//I only want to get the first record because I'm returning just one object.
if (reader.Read())
{
//Get all the columns
for (int i = 0; i < reader.FieldCount; i++)
{
this.SetProperty(reader.GetName(i), reader[i]);
}
}
}
catch (Exception ex)
{
Console.WriteLine("\nError:\n" + ex.Message);
}
}
}
// Get just a single object by a select command.
static protected T SingleBySelect<T>(MySqlCommand command, string connection)
{
//Use the list, just return a single item though
List<T> list = DBObject.BySelect<T>(command, connection);
return list[0];
}
// Get a list of Objects from a query
static protected List<T> BySelect<T>(MySqlCommand command, string connection)
{
List<T> list = new List<T>();
using (MySqlConnection conn = new MySqlConnection(connection))
{
//Connect the command to the connectiong
command.Connection = conn;
try
{
//Open the connection and get the reader
conn.Open();
MySqlDataReader reader = command.ExecuteReader();
//Get all the objects
while (reader.Read())
{
T obj = System.Activator.CreateInstance<T>();
//Get all the columns
for (int i = 0; i < reader.FieldCount; i++)
{
//Get the name and the value
object[] parms = {reader.GetName(i), reader[i]};
//Get the SetProperty Method and Call it
MethodInfo method = obj.GetType().GetMethod("SetProperty");
method.Invoke(obj, parms);
}
//Add it to the list
list.Add(obj);
}
}
catch (Exception ex)
{
Console.WriteLine("\nError:\n" + ex.Message);
}
}
return list;
}
}
// Basic User object
class User : DBObject
{
public int ID
{
get
{
return (int)GetProperty("id", -1);
}
}
public string FirstName
{
get
{
return (string)this.GetProperty("first_name", string.Empty);
}
}
public string LastName
{
get
{
return (string)this.GetProperty("last_name", string.Empty);
}
}
public string FullName
{
get
{
return (string)this.GetProperty("first_name", string.Empty) + " " + (string)this.GetProperty("last_name", string.Empty);
}
}
// Fill out all the properties on this User
public void Fill(string connection)
{
MySqlCommand cmd = new MySqlCommand("SELECT * FROM users WHERE id=?id");
cmd.Parameters.AddWithValue("?id", this.ID);
base.Fill(cmd, connection);
}
// Get a user by his ID
static public User byID(int id, string connection)
{
//Create a command to get a user by ID
MySqlCommand cmd = new MySqlCommand("SELECT * FROM users WHERE id=?id");
cmd.Parameters.AddWithValue("?id", id);
//Now fill out the object
return DBObject.SingleBySelect<User>(cmd, connection);
}
// Get a list of all the users, this will only fill out the ID property
static public List<User> All(string connection)
{
MySqlCommand cmd = new MySqlCommand("SELECT id FROM users");
List<User> user_list = DBObject.BySelect<User>(cmd, connection);
return user_list;
}
}
}
Well there you have it. Enjoy, and please leave comments if you found this interesting/wrong/or anything really.
You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.
