What is best practice to initialize c# objects from an sqlite database performant

Question

considering an sqlite table like this :

+--------------+-----------+--------------+ -----------+
| Col1(string) | Col2(int) | Col3(double) | ...        | 
+--------------+-----------+--------------+------------+
| A_string     | B_int     | C_double     | ...        |    
+--------------+-----------+--------------+------------+

The ... means hunderts of more columns. Currently, we get the data from the table in c# like this:

using( SQLiteConnection con = new SQLiteConnection( databaseConnectionString ) )
{
    con.Open(); //Open connection

    using( SQLiteCommand cmd = new SQLiteCommand( "SELECT * FROM table", con ) )
    {
        var dataTable = new DataTable();
        using( var sqlDataAdapter = new SQLiteDataAdapter( cmd ) )
        {
            sqlDataAdapter.Fill( dataTable );
            if( dataTable.Rows.Count > 0 ) //ensure, there are some results
            {
                foreach( DataRow row in dataTable.Rows )
                {
                    //Now initialize the c# objects
                    //But we do not wannt to hardcode every cast, 
                    //cause the type information is already in the database 
                    //therefore the function "getColumnValueAsRuntimeType()" is used to get the runntime type

                    string a = getColumnValueAsRuntimeType( "Col1", row );
                    int b    = getColumnValueAsRuntimeType( "Col2", row );
                    double c = getColumnValueAsRuntimeType( "Col3", row );
                    ...
                }
            }
        }
    }
}

private dynamic getColumnValueAsRuntimeType( string columnName, DataRow row )
{
    int index = row.Table.Columns.IndexOf( columnName );
    Type castTo = row.Table.Columns[ index ].DataType.UnderlyingSystemType;

    if( typeof( Int64 ) == castTo )
    {
        return Convert.ChangeType( row[ columnName ], typeof( Int32 ) );
    }

    return Convert.ChangeType( row[ columnName ], castTo );
}

Now there are some performance issues due to the call of :

  int index = row.Table.Columns.IndexOf( columnName );
  Type castTo = row.Table.Columns[ index ].DataType.UnderlyingSystemType;

at the function "getColumnValueAsRuntimeType()"

So my main question is :

What is the fastest/performantest way to get data from an sqlite database table in c# ? Without hardcoding every typcast in the source (We dont wannt to recompile the source everytime a database type is changed. The database should be the master). And why do i have to cast anyway? I am using a c# class to access the database, the data should already be typed.

We are talking here about an sqlite table with 1000 columns and million of rows.

So, in what objects would you store your results from the database? How will you work with them later on? — derpirscher, Jul 26 '18 at 10:43
The runntime objects would look like: https://stackoverflow.com/questions/50872322/c-is-it-possible-to-define-databinding-from-sqlite-database-to-c-sharp-object But not only A, B, C . There are hunderts of these. — user1911091, Jul 26 '18 at 10:56
You are performing the check for each column in each row. You could just analyse the first row and then apply the same method to each via it's index. — ste-fu, Jul 26 '18 at 11:04

score 1 · Answer 1 · answered Jul 26 '18 at 11:58

I would use something like Dapper to handle the mapping. If you create a class that corresponds to the data returned by your SQL statement then Dapper will analyse the class and do the column mapping for you just once for all the rows returned.

It would look a bit like this:

public class DataClass
{
    public string Astring { get; set; }

    public int Bint { get; set; }

    public double Cdouble { get; set; }
}

Your sql statement would look like

var sql = "SELECT Col1 Astring, Col2 Bint, Col3 Cbouble FROM table";

By using an alias in the SQL you can handle different naming conventions. Manually Map column names with class properties

Then you would just do

using( SQLiteConnection con = new SQLiteConnection( databaseConnectionString ) )
{
    con.Open();

    var dataClassList =  con.Query<DataClass>(sql);
}

Dapper can also handle nested objects: How do I map lists of nested objects with Dapper

What is best practice to initialize c# objects from an sqlite database performant

1 Answers1