Save Dataset to SQLite format file

Question

I have a dataset with multiple tables. I can obviously do a Dataset.WriteToXML("Somefile.xml")

What if I want to export the dataset to a SQLite formatted file.

In other words I want to be able to write (i.e. serialize) the contents of the dataset to a SQLite file. Dataset.SerializeToSQLite("Sqliteformatted.bin")

Similarly I want to be able to read the SQLite file into a Dataset.

I would like to do this in c#.

Thanks in advance to any pointers.

Bob

Answer 1

This example may answer your question.

using System;
using System.Data;
using System.Data.SQLite;

namespace DataAdapterExample
    {
    class Program
    {
        static void Main(string[] args)
        {
            // Create a simple dataset
            DataTable table = new DataTable("Students");
            table.Columns.Add("name", typeof(string));
            table.Columns.Add("id", typeof(int));
            table.Rows.Add("Bill Jones", 1);
            table.Rows.Add("Laurie Underwood", 2);
            table.Rows.Add("Jeffrey Sampson", 3);
            DataSet ds = new DataSet();
            ds.Tables.Add(table);
            // Save in an SQLite file
            string desktopPath = 
                Environment.GetFolderPath(Environment.SpecialFolder.DesktopDirectory);
            string fullPath = desktopPath + "\\class.db";
            SQLiteConnection con = new SQLiteConnection("Data Source=" + fullPath);
            con.Open();
            // Create a table in the database to receive the information from the DataSet
            SQLiteCommand cmd = new SQLiteCommand(con);
            cmd.CommandText = "DROP TABLE IF EXISTS Students";
            cmd.ExecuteNonQuery();
            cmd.CommandText = "CREATE TABLE Students(name text, id integer PRIMARY  KEY)";
            cmd.ExecuteNonQuery();
            SQLiteDataAdapter adaptor = new SQLiteDataAdapter("SELECT * from Students", con);
            adaptor.InsertCommand = new SQLiteCommand("INSERT INTO Students  VALUES(:name, :id)", con);
            adaptor.InsertCommand.Parameters.Add("name", DbType.String, 0, "name");
            adaptor.InsertCommand.Parameters.Add("id", DbType.Int32, 0, "id");
            adaptor.Update(ds, "Students");
            // Check database by filling the dataset in the other direction and displaying
            ds = new DataSet();
            adaptor.Fill(ds, "Students");
            foreach (DataTable dt in ds.Tables)
            {
                Console.WriteLine("Table {0}", dt.TableName);
                foreach (DataRow dr in dt.Rows)
                {
                    foreach (DataColumn dc in dt.Columns)
                    {
                        Console.Write("{0,-18}", dr[dc]);
                    }
                    Console.WriteLine();
                }
            }
        }
    }
}

You can find a very similar example in the SQLiteDataAdapter Class documentation.

Answer 2

SQLite is not a file format, it's a database.

If you want to put all the data from a DataSet into a database you need to create a connection to the database (which can be a blank file, SQLite will create it upon initial connection), and then create the database structure. Then issue INSERT statements to append the data to the database. You can use a SQLiteDataAdapter to simplify creating the insert statements.