Inserting Master-Detail Data into a SQLite Database

Create Our Sample SQLite Database
=================================
CREATE TABLE Categories (
    CategoryID integer primary key,
    CategoryName text not null,
);

CREATE TABLE Products (
    ProductID integer primary key,
    ProductName text not null,
    CategoryID integer not null,
    FOREIGN KEY(CategoryID) REFERENCES Categories(CategoryID)
);


Install a Visual Studio (Not the Express)
=========================================
Visual Studio design-time Support, works with all versions of Visual Studio 2005/2008/2010/2012.
You can add a SQLite database to the Servers list, design queries with the Query Designer,
drag-and-drop tables onto a Typed DataSet, etc.
Due to Visual Studio licensing restrictions, the Express Editions can no longer be supported.
Ref: http://system.data.sqlite.org/index.html/doc/trunk/Doc/SQLite.NET.chm?mimetype=application/x-chm


Enable Designer Data Source + Source Explorer in Visual Studio 2012 to work with SQLite
========================================================================================
- go to http://system.data.sqlite.org/index.html/doc/trunk/www/downloads.wiki
Don't download the 64-bit version, even if you're on a 64-bit machine.
You need the 32-bit version with the text
"This setup package is capable of installing the design-time components for Visual Studio 2012."
to get designer support.


New Project
===========
- New "Windows Form Application - Visual C#"


Adding SQLite Lib to .Net Project
=================================
- Using NuGet to install "System.Data.SQLite"
http://www.tsjensen.com/blog/post/2012/11/10/SQLite-on-Visual-Studio-with-NuGet-and-Easy-Instructions.aspx


Create Data source
===================
- Menu  "Tools" -> "Connect to Database..."
- Then "Menu Project" -> "Add New Data source..."


Set Primary Key Properties
===========================
- Open Dataset Designer ( file xxxDataSet.xsd )
- Set CategoryID and ProductID as follow
ReadOnly = False
AutoIncrement = True
AutoIncrementSeed = -1
AutoIncrementStep = -1


Remove ID column when Insert
============================

- Replace "insert into categories (CategoryID, CategoryName) values (@CategoryID, @CategoryName)"
with "insert into categories (CategoryName) values (@CategoryName)"
and the same for productTableAdapter.


Set the relation in the DataSet designer
========================================
- Set the relation in the DataSet designer to "Both Relation and Foreign Key Constraint"
- and then set the "Update and Delete rules" to "Cascade".
as shown at http://blogs.msdn.com/b/bethmassi/archive/2009/05/14/using-tableadapters-to-insert-related-data-into-an-ms-access-database.aspx
in the section of "Setting up the Parent-Child DataSet"


Create Form
===========
- Menu "Project" -> "New Windows form..."
- Press "Shift+Alt+D" to open the "Data source Window"
- drag the Categories and Products to the form
http://blogs.msdn.com/b/bethmassi/archive/2009/09/15/inserting-master-detail-data-into-a-sql-server-compact-edition-database.aspx

Edit Form
=========
namespace xxx
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            this.categoriesTableAdapter.Fill(this.myDataSet.categories);
            this.productsTableAdapter.Fill(this.myDataSet.products);
            categoriesTableAdapter.InitEvents();
            productsTableAdapter.InitEvents();
        }


        private void categoriesBindingNavigatorSaveItem_Click(object sender, EventArgs e)
        {
            this.Validate();
            this.categoriesBindingSource.EndEdit();
            this.productsBindingSource.EndEdit();

            //this.tableAdapterManager.UpdateAll(this.myDataSet);

            this.categoriesTableAdapter.Update(this.myDataSet.categories);
            this.productsTableAdapter.Update(this.myDataSet.products);
        }

        private void productsDataGridView_Enter(object sender, EventArgs e)
        {
            this.categoriesBindingSource.EndEdit();
        }
...

Enhancing the TableAdapter Partial Classes
==========================================
- Right-click on the TableAdapter class in the DataSet Designer
and select "View Code"
- Copy and Paste the following code to that file

using System.Data.SQLite;

namespace xxx.myDataSetTableAdapters {
   
    public partial class categoriesTableAdapter {
        public void InitEvents()
        {
            this._adapter.RowUpdated += new EventHandler<System.Data.Common.RowUpdatedEventArgs>(_adapter_RowUpdated);
        }

        private void _adapter_RowUpdated(object sender, System.Data.Common.RowUpdatedEventArgs e)
        {
            SQLiteIDHelper.SetPrimaryKey(this._transaction, e);
        }
    }
   
    partial class productsTableAdapter
    {
        public void InitEvents()
        {
            this._adapter.RowUpdated += new EventHandler<System.Data.Common.RowUpdatedEventArgs>(_adapter_RowUpdated);
        }

        private void _adapter_RowUpdated(object sender, System.Data.Common.RowUpdatedEventArgs e)
        {
            SQLiteIDHelper.SetPrimaryKey(this._transaction, e);
        }
    }
   
    public class SQLiteIDHelper
    {
        public static void SetPrimaryKey(SQLiteTransaction trans, System.Data.Common.RowUpdatedEventArgs e)
        {
            if ((e.Status == System.Data.UpdateStatus.Continue) && (e.StatementType == System.Data.StatementType.Insert))
            {
                System.Data.DataColumn[] pk = e.Row.Table.PrimaryKey;

                if ((pk != null) && (pk.Length == 1))
                {
                    //e.Row[pk[0]] = trans.Connection.LastInsertRowId;

                    SQLiteCommand com = (SQLiteCommand)e.Command;
                    SQLiteConnection con = (SQLiteConnection)com.Connection;
                    e.Row[pk[0]] = con.LastInsertRowId;
                    e.Row.AcceptChanges();
                }
            }
        }
    }
}