How do I return a table from a SQL query as a List in C#?

Question

I have a Xamarin app that connects to a SQL Server (*), and I want to insert the data from the SQL Server's tables into a local SQLite database. My approach is to execute a query like

select * from Table1

retrieve the results of the query as a List, then insert that List into my SQLite table. That said I'm very new at using SqlClient, so please share if there are better ways to do this. Thanks.

Edit: the smallest number of columns these tables have is 5. Don't know if that disqualifies Lists as the best option.

My code:

private void LoadData()
{
    string cs = @"connection string here";

    using (SqlConnection sconn = new SqlConnection(cs))
    {
        sconn.Open();

        SqlDataReader reader = null;

        SqlCommand aml = new SqlCommand("select * from Table1");
        reader = aml.ExecuteReader();

        while (reader.Read())
        {
            // get result of query as List somehow?
        }

        using (SQLiteConnection conn = new SQLiteConnection(App.DatabaseLocation))
        {
            conn.CreateTable<Table1>();

            if (conn.Query<Table1>("select * from Table1").Count() <= 0)
            {
                // insert the list object
            }
        }
    }
}

(*) The app does not use a web service as the app is intended for onsite use only and will not be distributed publicly.

Check the examples in the [document](https://learn.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqldatareader?view=netframework-4.8#examples) and have a look at [this thread](https://stackoverflow.com/questions/12024226/how-to-generate-liststring-from-sql-query) may help. Get the value from reader and add them to a list. — nevermore, Nov 13 '19 at 07:36

score 2 · Accepted Answer · answered Nov 13 '19 at 04:50

2

A better alternative way to do it more easier is to use a ORM like dapper

With the help of dapper, all you need to do is

using (var connection = new SqlConnection(_sqlConnectionString))
{
    var results = connection.Query<YourTableModel>(query).ToList();
    return results;
}

answered Nov 13 '19 at 04:50

HariHaran

3,642
2
16
33

1

This works perfectly, thank you. Note for anyone coming here in the future, you don't need a separate `SqlConnection` for each table; you can retrieve as many tables as you want in the one `using` directive. – Nick Nov 14 '19 at 05:35

score 1 · Answer 2 · answered Nov 13 '19 at 04:47

You can get data from the SQL Server as DataTable or convert it to a list as you prefer.

public DataTable GetDataTable(string connectionString, string tableName)
{
SqlConnection conn = new SqlConnection(connectionString);
conn.Open();
string query = $'SELECT * FROM [{tableName}]';

SqlCommand cmd = new SqlCommand(query, conn);

DataTable t1 = new DataTable();
using (SqlDataAdapter a = new SqlDataAdapter(cmd))
{
    a.Fill(t1);
}
return t1;
}

Then use this table or list returned from the above method to insert in the SQLite table.

string cs = @"Data Source=datasource;Initial Catalog=databasename;User ID=user;Password=password";


    DataTable table = GetDataTable(cs, "Table1");
    using (SQLiteConnection conn = new SQLiteConnection(App.DatabaseLocation))
                    {
                       conn.CreateTable<Table1>();
                       if (conn.Query<Table1>("select * from Table1").Count() <= 0)
                          {
                              foreach(DataRow row in table.Rows) 

                              {
                                //Access values of each row column      row["columnName"]
                                // insert the list object
                              }
                          }
                    }

Refer to this one: Inserting Data from SQL Server to Sqlite

How do I return a table from a SQL query as a List in C#?

2 Answers2