DataReader returns no rows -- rows are present in database

Okay world, I'm having some trouble. No, this is not 'homework'

I've got some code which needs to do two things:

1) Read a list of artists from a database

2) Find all tracks written by that artist and store it for later

What I'm doing:

1) SELECT ArtistID FROM artists gets me my list of artists. I build it up in a List<string> datatype, using a DataReader, etc. works great

2) SELECT count(*) as track_numbers from tracks WHERE ArtistID = @ArtistID does not work so great

My problem:

The second select statement will succeed if I manually specify the ArtistID (ie: SELECT count(*) as track_numbers FROM Tracks WHERE ArtistID = 0 works) but if I am iterating through my list, it will always return zero results.

I am using Connector/Net 6.3.6, Visual Studio 2010 (all updates), .NET 4.0 Client Profile, MySQL Community Server 5.5.9 x64, Windows 7 Home Premium x64

I have tried:

• Using string concatenation to build up my query string with the artistid
• casting artistid as a string, then an int, then vice-versa
• re-arranging where the parameters are defined, the value put in, the command text

Relevant (I think) code below:

List<string> list = new List<string>();
Hashtable table = new Hashtable();
DbProviderFactory factory = DbProviderFactories.GetFactory(setting.ProviderName);
using (DbConnection conn = factory.CreateConnection())
{
conn.ConnectionString = setting.ConnectionString;
conn.Open();

using (DbCommand cmd = conn.CreateCommand())
{
    cmd.CommandType = CommandType.Text;
    cmd.CommandText = "SELECT ArtistID FROM artists;";
    using (DbDataReader reader = cmd.ExecuteReader())
    {
        while (reader.Read())
        {
            list.Add(reader["ArtistID"].ToString()); //this works, and I can iterate no proble开发者_StackOverflow中文版m through this list
        }
    }
}

using (DbCommand cmd = conn.CreateCommand())
{
    cmd.CommandType = CommandType.Text;
    cmd.CommandText = "SELECT count(*) as counts FROM tracks WHERE ArtistID = @ArtistID;";
    //cmd.CommandText = "SELECT count(*) as counts FROM tracks WHERE ArtistID = 0;"; <-- this line works


    DbParameter param = cmd.CreateParameter();
    param.ParameterName = "@ArtistID";

    foreach (string artist in list)
    {
        param.Value = artist;
        cmd.Parameters.Add(param);
        using (DbDataReader reader = cmd.ExecuteReader())
        {
            while (reader.Read())
            {
                Console.WriteLine(artist + "\t" + reader["counts"]); //reader["counts"] is always '0'
            }
            cmd.Parameters.Clear();
        }
    }
}

}

The database table format is: Artist has one value/key called ArtistID, type int

Tracks has three columns, TrackID (int, pk), artistid (int), albumid(int)

Any suggestions? Thanks.

I think you need to remove the cmd.Parameters.Add(param); call. That adds a new parameter on each iteration. It should be sufficient to just set the parameter value each time for the existing parameter.

foreach (string artist in list)
{
    param.Value = artist;
    cmd.Parameters.Add(param)

You are currently re-adding the parameter for every artist - I'm surprised that this works at all, certainly it shouldn't for SqlParameter - if DbParameter works the same way it should be like this:

cmd.Parameters.Add(param);
foreach (string artist in list)
{
    param.Value = artist;

After smashing my head against the wall for another hour, I discovered the problem: 1) I needed to do a 'clean solution' for reasons I cannot fathom 2) Somehow the foreign key relationships ensuring that there actually was an artistid in tracks that matched an artistid in artists I had somehow managed to screw up

I am in fact a huge idiot.

The above answers while not they did not fix my problem, did teach me something new about c#, so I'm giving credit to the 1st poster.