Is there easy method to read all tables from SQLite database to DataSet object?

Question

Now I use method in C# to read table from SQLite database into DataTable, but I want to send all table into other object.

So I think I have to use DataSet to combine all DataTable(s) and send it to object as parameter.

Is there method that easy read all tables from SQLite database to DataSet? Or I have to read all tables from SQLite database to DataTable each table and combine to DataSet by hand?

Answer 1

The sql for listing all the tables is:

SELECT name FROM sqlite_master WHERE type = 'table' ORDER BY 1

you could then get all the tables as databases seperately and then add them into a dataset - an example here: http://www.dotnetperls.com/dataset

so i guess the code would be something like:

Dataset d = new Dataset()
foreach (tableName in GetTables()){
  d.Tables.Add(GetDataTable("select * from "+tableName);
}

code for GetTables and GetDataTable (i'll leave the piecing it together to you):

    public ArrayList GetTables()
    {
        ArrayList list = new ArrayList();

        // executes query that select names of all tables in master table of the database
            String query = "SELECT name FROM sqlite_master " +
                    "WHERE type = 'table'" +
                    "ORDER BY 1";
        try
        {

            DataTable table = GetDataTable(query);

            // Return all table names in the ArrayList

            foreach (DataRow row in table.Rows)
            {
                list.Add(row.ItemArray[0].ToString());
            }
        }
        catch (Exception e)
        {
            Console.WriteLine(e.Message);
        }
        return list;
    }

    public DataTable GetDataTable(string sql)
    {
        try
        {
            DataTable dt = new DataTable();
            using (var c = new SQLiteConnection(dbConnection))
            {
                c.Open();
                using (SQLiteCommand cmd = new SQLiteCommand(sql, c))
                {
                    using (SQLiteDataReader rdr = cmd.ExecuteReader())
                    {
                        dt.Load(rdr);
                        return dt;
                    }
                }
            }
        }
        catch (Exception e)
        {
            Console.WriteLine(e.Message);
            return null;
        }
    }