Sqlite CollectionViewSource

Hello,

Time for my first post. I’ll explain how I integrated a CollectionViewSource with my SQLite project and why I did it 🙂

I was studying for my finals. Tomorrow I have my exam “Software Development”.
As preparation I was checking all the exercises we made this semester…

One of those exercises was C# SQLite. When I was looking @ my code, I was amazed how bad it was 🙂
NOTE: When I programmed that exercise, I wasn’t familiar with Databinding (we learnt that a couple weeks later).

The purpose of the exercise was to show some data about school results. Of course you should be able to edit the data that already existed in the DB. You should be able to move trough the data. Later we also had to add a DataGrid.

My first code was something like this:

 private void ButtonNext_OnClick(object sender, RoutedEventArgs e)
 {
     if (_id < _maxid) { _reader = GetData("select * from Schoolresultaat"); if (_reader != null) { while (_reader.Read()) { if (Convert.ToInt32(_reader["ID"]) > _id)
                    {
                          TextBoxNaamVak.Text = Convert.ToString(_reader["NaamVak"]);
                          TextBoxScore.Text = Convert.ToString(_reader["Score"]);
                          TextBoxDatum.Text = Convert.ToString(_reader["Datum"]);
                          TextBoxOpmerking.Text = Convert.ToString(_reader["Opmerking"]);
                          TextBoxCredit.Text = Convert.ToString(_reader["Credit"]);
                          _id = Convert.ToInt32(_reader["id"]);
                          return;
                    }
               }
          }
     }
 }

 private SQLiteDataReader GetData(string selectCommand)
 {
      _connection = new SQLiteConnection("Data Source=MyDatabase.sqlite;Version=3");
      _connection.Open();
      SQLiteCommand command = new SQLiteCommand(selectCommand, _connection);
      SQLiteDataReader reader = command.ExecuteReader();

      return reader;
 }

So as you can see a lot code behind, really awful 😦
I first had to check if you weren’t already on the last row. Than I execute a query. That query results in all information from the hole database… (If there’s a lot of data this is bad programming / performance). Than I loop through all the rows to search for the row that has a bigger id than the current id (In other words the next result). And when I found that result I change all my textboxes to the correct information.

Conclusion: Very bad!

So I was determined to find a better solution….

In my textbook (Dutch) I found some exercises for SQL. There was an example that actually did the same as I needed. They added their database to their Data Sources. Than they choose for details and dragged it to their WPF. It automatically added all the code for you! So I checked that code and it used a CollectionViewSource. 

The code behind was practically empty. So I decided the use this in my SQLite exercise!
I searched on Google if someone had some information. Didn’t found anything but I was easier than I thought it would be!

So first step, add this in your WPF file!

<Window.Resources>
     <CollectionViewSource x:Key="resultatenViewSource" Source="{Binding Klanten}"/>
</Window.Resources>

Second step update your WPF to use Databinding (2 Way because you also want to edit the data). Use as DataContext for the container the CollectionViewSource you created in step 1!

<StackPanel Grid.Row="1" DataContext="{StaticResource resultatenViewSource}">
   <TextBlock Margin="0,0,0,10" Text="Naam van het vak:">
   <TextBox Text="{Binding NaamVak, Mode=TwoWay}">
   <TextBlock Margin="0,10,0,10" Text="Score:">
   <TextBox Text="{Binding Score, Mode=TwoWay}">
   <TextBlock Margin="0,10,0,10" Text="Datum:"></TextBlock>
   <TextBox Text="{Binding Datum, Mode=TwoWay}"></TextBox>
   ...

Third step load data in CollectionViewSource. Essentially it’s the same way as loading data in a DataGrid. I made a little function to do this.

public MainWindow()
{
    InitializeComponent(); 
    GetData("select * from Schoolresultaat");
}

private void GetData(string selectCommand)
{
    //Open up a connection to the SQLite file
    SQLiteConnection connection = new SQLiteConnection("Data Source=MyDatabase.sqlite;Version=3");
    connection.Open();
    SQLiteCommand command = new SQLiteCommand(selectCommand, connection);
    command.ExecuteNonQuery();
    // Create a new data adapter based on the specified query
    _adapter = new SQLiteDataAdapter(command);
    _dt = new DataTable("Schoolresultaat");
    _adapter.Fill(_dt);

    //Load data into DataGrid
    DataGridSchoolresultaat.ItemsSource = _dt.DefaultView

   //Find the CollectionViewSource you made in the UI
   _resultatenViewSource = ((CollectionViewSource)(this.FindResource("resultatenViewSource")));
   //Load data into the COllectionViewSource
   _resultatenViewSource.Source = _dt;
   //Show first row of data in the textboxes
   _resultatenViewSource.View.MoveCurrentToFirst();

   connection.Close();
}

Fourth step update your code behind. I will show you the next button (same example I used above).
So how do you do that?

You simple check if you aren’t on the last record in the CollectionViewSource. If you aren’t open the next record

private void Button_Click_1(object sender, RoutedEventArgs e)
{
    if (_resultatenViewSource.View.CurrentPosition < ((CollectionView)_resultatenViewSource.View).Count - 1)
        _resultatenViewSource.View.MoveCurrentToNext();
}

And now it’s finished, you can move between your data! The only part left to was update the data in the SQLite DB.
Originally is used hand made SQL queries:

private void ButtonEditSave_OnClick(object sender, RoutedEventArgs e)
{
    SetData("UPDATE SchoolResultaat SET NaamVak='" + TextBoxNaamVak.Text + "', Score=" + TextBoxScore.Text + ", Datum='" + TextBoxDatum.Text + "', Opmerking='" + TextBoxOpmerking.Text + "', Credit='" + TextBoxCredit.Text + "' WHERE id=" + _id);
 }

Now I make use of the command builder! It automatically generates the query based on the changes you did!
After that query is built I execute it! And that’s all!

private void ButtonEditSave_OnClick(object sender, RoutedEventArgs e)
{
    SQLiteCommandBuilder commandBuilder = new SQLiteCommandBuilder(_adapter);
    _adapter.Update(_dt);

So the very last thing that you maybe need is to cancel the changes you made. Just reload the data in the DataGrid / CollectionViewSource and the original data is back!

private void ButtonCancel_OnClick(object sender, RoutedEventArgs e)
{
     GetData("select * from Schoolresultaat");
}

So that was it. My code is a lot nicer now! I hope you could use it…
I’m certain there are better solutions but as I said I’m still a student 🙂
Feel free to give comments!

Last but not least, a link to my solution

UPDATE:
I added Entityframework, to this example in another blog post
Later I added the MVVM pattern in this blog post!

You should check these out to make your code better!