0

It has been a long time since I have used .NET, but thankfully have almost finished writing a tool to compare an sqlite and mysql database. I am running into an issue though when trying to write a function for my wrapper that will handle SELECT calls as I cannot entirely figure out the Data Reader.

My understanding is that each iteration of a loop on the reader is the next row, and GetString(x) returns the column value for "x" as the index. All the examples I found though went into it knowing the row/column names they needed. How can I do this with a "SELECT * FROM" call and save the column names/values for later access? Microsoft seems to have a "FieldCount" function but I am coming up empty on the MySQL Connector.

Thanks!

public void Query(string query, string tableName)
{
    //Open connection
    if (this.OpenConnection() == true)
    {
        //Create Command
        MySqlCommand cmd = new MySqlCommand(query, connection);
        MySqlDataReader dataReader = cmd.ExecuteReader();

        //Read the data and store them in the list
        while (dataReader.Read())
        {
            int count = Count(tableName);

            for (int x = 0; x < count; x++)
            {
                Console.WriteLine(dataReader.GetString(count));
            }
        }

        //close Data Reader
        dataReader.Close();

        //close Connection
        this.CloseConnection();
    }
}

2 Answers2

1

You can use DbDataReader.GetName to get the name of a column given its ordinal x.

Jon
  • 428,835
  • 81
  • 738
  • 806
  • Thank you for the help. I think I was a bit confusing in my question. I was rather asking how the best way to store this data would be (List/Dictionary/2D Array/etc) for easy reference? –  Feb 13 '13 at 09:29
  • @BrettPowell: What data exactly? The column names? – Jon Feb 13 '13 at 09:31
  • Just the column names and the values for each row. I basically need to take a value from sqlite and check to see if it matches any values in a specific column from mysql. –  Feb 13 '13 at 09:35
  • @BrettPowell: I really don't know, it depends on many things. In general pulling in all the data in memory might not be a good idea if the dataset is large. Otherwise you can just put them in e.g. a `List` since there is a `GetValues` method that gives an `object[]`. – Jon Feb 13 '13 at 09:38
0

use the "mysql connector" to access data in MySql it is more simple then to write the queries by your self: http://dev.mysql.com/downloads/connector/net/

Then use the EntityFramework to access the data through this connector. Also you can automaticly generate *.edmx model from your existing DB in mysql, this will let you to fastly access and work with the Data in your database. Here is information about adding *.edmx model from existing DB: http://msdn.microsoft.com/en-us/library/vstudio/cc716703(v=vs.100).aspx

The query which will select all data from the table, for example - "Products" will look like that:

List products = dbContext.Products.Where(e=>e.ProductId!=-1).ToList();

this will return the whole list of products in your data base in table Products.

then you can work with products as you want. for example taking the "Name" column for the first product in 'products' will look like that:

String firstProductName = products[0].name;

I hope it helps.

Maris
  • 4,608
  • 6
  • 39
  • 68
  • Thank you, although this project is already nearly complete now and changing over to that seems rather complicated. Great answer though :) –  Feb 13 '13 at 09:40
  • No problem. I want to tell one more thing. When I started using C# and .NET I was also writting sql queries by the hands and using DataReader for transfering in to workable objects in .NET, but in one great day I found for myself EntityFramework and Linq. Now I cant even expect my life without this technologies. You should try it once. Anyway I'm happy that you like my answer. – Maris Feb 13 '13 at 10:12