Saving reader information in C #
I know I am asking not to make a lot of sense to C # experts, but I will explain what I want to do and then can you suggest me how to do it better if you want it ok?
I have a C # class called DatabaseManager that handles various MySQL queries (ado.net NET connector, not linq or any ActiveRecord-ish library).
I am doing something like
categories = db_manager.getCategories();
The list of categories is pretty small (10 items), so I would like to know what is the best way to access the resulting information without a lot of extra code.
I am currently using Struct to store information, but I am sure there is a better way to do it.
Here's my code:
public struct Category
{
public string name;
}
internal ArrayList getCategories()
{
ArrayList categories = new ArrayList();
MySqlDataReader reader;
Category category_info;
try
{
conn.Open();
reader = category_query.ExecuteReader();
while (reader.Read())
{
category_info = new Category();
category_info.name = reader["name"].ToString();
categories.Add(category_info);
}
reader.Close();
conn.Close();
}
catch (MySqlException e)
{
Console.WriteLine("ERROR " + e.ToString());
}
return categories;
}
a source to share
Example:
public IEnumerable<Category> GetCategories()
{
using (var connection = new MySqlConnection("CONNECTION STRING"))
using (var command = new MySqlCommand("SELECT name FROM categories", connection))
{
connection.Open();
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
yield return new Category { name = reader.GetString(0) };
}
}
}
}
Notes:
- Let ADO.NET connection pool do the right job for you (avoid storing connections in static fields, etc.)
- Be sure to remove unmanaged resources (using "use" in C #)
- Always return the smallest interface in the hierarchy from your public methods (IEnumerable <Category> in this case).
- Leave the callers to handle exceptions and logging. These are tricky problems and should not mix with your DB access code.
a source to share
There is nothing wrong with bringing them back like that. However, a few things stand out:
- Your catch block logs an error, but then returns either an empty array or a partially filled array. This is probably not a good idea.
- If a blocking exception is thrown in try, you don't close the connection or dispose of the reader. Consider using () statement.
- You should use generic types (List <>) instead of ArrayList.
a source to share
From your code, I think you are using .NET 1.1 because you are not using generic capabilities.
1) Using a structure containing only a string is redundant. Just create an arraylist of strings (or with a generics List)
2) When an exception occurs in the try block, you leave your connection and reader open ... Use this instead:
try
{
conn.open();
//more code
}
catch (MySqlException e) { // code
}
finally {
conn.close()
if (reader != null)
reader.close();
}
a source to share