Fetch rows from database using ExecuteStoreQuery, without knowing the number of columns in the table

Question

I'm trying to do some manual SQL queries against my SQLite database using the ExecuteStoreQuery method on my ObjectContext.

The catch is that I don't always know how many columns are in the table that I'm querying. Ideally, I would like each fetched row to simply be an string[] object.

I've looked at Example 2 here: http://msdn.microsoft.com/en-us/library/vstudio/dd487208(v=vs.100).aspx

It's close to what I want to do, except that I don't know the structure of the TElement I'm fetching, so I can't define a struct as they do in the example.

Below is some of my code (not compiling due to the ???? TElement). The code below is trying to fetch the table info, so in this case I do know the structure of the rows, but in general I don't.

Is there a way to do this with ExecuteStoreQuery? Or is there a different way of doing it, while still using the existing connection of my ObjectContext (rather than opening a new SQL connection to the DB)?

public void PrintColumnHeaders(NWRevalDatabaseEntities entities, string tableName)
{
    string columnListQuery = string.Format("PRAGMA table_info({0})", tableName);

    var result = entities.ExecuteStoreQuery<????>(columnListQuery);

    foreach (string[] row in result)
    {
        string columnHeader = row[1]; // Column header is in second column of table
        Console.WriteLine("Column Header: {0}", columnHeader);
    }
}

I think this call for a lower level approach: issue a command against the store connection (returning object[]). — Gert Arnold
– Gert Arnold, Commented Mar 1, 2013 at 18:39
@GertArnold Thanks, I got this working thanks to you. Would you like to post as answer so I can accept? — Pieter Müller
– Pieter Müller, Commented Mar 2, 2013 at 6:57
You did all the work! I suggest you move your UPDATE to an answer and mark it. — Gert Arnold
– Gert Arnold, Commented Mar 2, 2013 at 8:13

Community · Accepted Answer · 2017-05-23 12:21:31Z

I got this working based on Gert Arnold's comment. Also, it took me some effort to figure out that I need a SQLiteConnection, not the EntityConnection that I could get directly from the ObjectContext. The answer to this question helped me with that.

The working code is below:

public static void PrintColumnHeaders(NWRevalDatabaseEntities entities, string tableName)
{
    var sc = ((System.Data.EntityClient.EntityConnection)entities.Connection).StoreConnection;
    System.Data.SQLite.SQLiteConnection sqliteConnection = (System.Data.SQLite.SQLiteConnection)sc;

    sqliteConnection.Open();
    System.Data.Common.DbCommand cmd = sc.CreateCommand();
    cmd.CommandType = System.Data.CommandType.Text;
    cmd.CommandText = string.Format("PRAGMA table_info('{0}');", tableName);
    System.Data.Common.DbDataReader reader = cmd.ExecuteReader();

    if (reader.HasRows)
    {
        object[] values = new object[reader.FieldCount];
        while (reader.Read())
        {
            int result = reader.GetValues(values);
            string columnHeader = (string)values[1]; // table_info returns a row for each column, with the column header in the second column.
            Console.WriteLine("Column Header: {0}", columnHeader);
        }
    }
    sqliteConnection.Close();
}

Collectives™ on Stack Overflow

Fetch rows from database using ExecuteStoreQuery, without knowing the number of columns in the table

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related