Retrieve/insert data using SQLiteDataAdapter

Question

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!

Steve · Accepted Answer · 2013-05-04 13:00:42Z

3

INSERT, UPDATE and DELETE operations are the working of a DbCommand. You need a different method that takes the sql string and a collection of SQLiteParameter that you use for the INSERT.

I will try to write some pseudocode for the INSERT operation

public class MyHelperClass
{
    public static int InsertCommand(string sql, SQLiteParameter[] parameters)
    {
        int result = 0;
        using (SQLiteConnection connection = new SQLiteConnection(connectionString))
        using (SQLiteCommand cmd = new SQLiteCommand(sql, connection))
        {
            cmd.Parameters.AddRange(parameters);
            result = cmd.ExecuteNonQuery();
        }  
        return result;
    }
}

Now you have to build the parameter array to pass to the help method and this should be done from your UI code

string sqlCommand = "INSERT INTO table1 (FirstName, LastName) VALUES (@fName, @lName)";
SQLiteParameter[] p = new SQLiteParameter[2];
p[0] = new SQLiteParameter("@fName", TextBox1.Text);
p[1] = new SQLiteParameter("@lName", TextBox2.Text);
int rowAdded = MyHelperClass,InsertCommand(sql, p);

The operation for the UPDATE and DELETE command are similar. Also I suggest you to add a version of your GetDataTable that accepts a parameter array instead of building sql commands with string concatenation. As repetead innumerable times here string concatenation leads to errors and, worst of all, to weak code easily exposed to sql injection.

answered May 4, 2013 at 13:00

Steve

217k22 gold badges242 silver badges296 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Lahey Over a year ago

Thank you for your quick response. If I make my own INSERT, UPDATE and DELETE methods, does it even make sense for me to use SQLiteDataAdapter? If the DataGridView only displays the table, but altering it is done through some text boxes, does this make data binding from db to DGV obsolete?

Steve Over a year ago

Well, in this case the dataadapter is useless, however you could find useful in future. And if you are building a base library to reuse in different projects I will try to keep the method

Collectives™ on Stack Overflow

Retrieve/insert data using SQLiteDataAdapter

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related