Retrieve and insert data using SQLiteDataAdapter

See more:

C#

SQLite

Hi,

I'm currently creating a small application using Windows Forms and SQLite. After reading some tutorials I implemented this method for data retrieval:

public DataTable GetDataTable(ref SQLiteDataAdapter adapter, string sql)
        {
            DataTable dt = new DataTable();
         
            // Connect to database.
            using (SQLiteConnection connection = new SQLiteConnection(connectionString))
            // Create database adapter using specified query
            using (adapter = new SQLiteDataAdapter(sql, connection))
            // Create command builder to generate SQL update, insert and delete commands
            using (SQLiteCommandBuilder command = new SQLiteCommandBuilder(adapter))
            {
                // Populate datatable to return, using the database adapter                
                adapter.Fill(dt);
            }
            return dt;
        }

(As well as another GetDataTable which doesn't take an SQLiteDataAdapter as parameter)

I have three classes, let's call them UI, Link and Database. The UI does nothing but displaying the data and raising events upon user interaction. The Link creates the Database and a SQLiteDataAdapter, retrieves a data table through the method mentioned above, and binds it to a data grid view on the UI. The user cannot alter the table through the data grid view, but should do so through some text boxes. (does this make binding the table to the dgv obosolete?)

What's the best way to get the user input from the text boxes to the database, using the adapter? Or should I use DataReader and some Insert method instead of an adapter?

As of know, the UI exposes its controls through Get-methods. Is there a better solution?

        private void Initialize()
        {
            // Subscribe to userInterface events
            userInterface.DataGridViewSelectionChanged += new EventHandler(userInterface_DataGridViewSelectionChanged);
            userInterface.NewClicked += new EventHandler(userInterface_NewClicked);
            userInterface.SaveClicked += new EventHandler(userInterface_SaveClicked);

            // Get dataGridView from userInterface and bind to database
            bindingSource = new BindingSource();
            bindingSource.DataSource = database.GetDataTable(ref adapter, "SELECT * FROM SomeTable");
            userInterface.GetDataGridView().DataSource = bindingSource;
        }  

        void userInterface_DataGridViewSelectionChanged(object sender, EventArgs e)
        {
            if (userInterface.GetDataGridView().SelectedRows.Count != 0)
            {
                DataGridViewRow row = userInterface.GetDataGridView().SelectedRows[0];
                userInterface.GetIDTextBox().Text = row.Cells["PrimaryKey].Value.ToString();
                userInterface.GetOtherIDTextBox().Text = row.Cells["ForeignKey"].Value.ToString();

                DataTable dt = database.GetDataTable("SELECT * from SomeTable WHERE ForeignKey=" + row.Cells["ForeignKey"].Value);
                userInterface.GetLastNameTextBox().Text = dt.Rows[0]["LastName"].ToString();
                userInterface.GetFirstNameTextBox().Text = dt.Rows[0]["FirstName"].ToString();
                userInterface.GetCompanyTextBox().Text = dt.Rows[0]["Company"].ToString();

            }            
        }

        void userInterface_NewClicked(object sender, EventArgs e)
        {
            // Get all text boxes and clear them
            // Let the UI take care of this by itself?                     
        }

void userInterface_SaveClicked(object sender, EventArgs e)
        {
            // Get text/data from all text boxes and insert (or update if editing table) into database
            // adapter.Update(...)?
        }

Cheers!