This post is a follow-up to a previous post called WPF ComboBox Colors. In this project, I have added a SQLite database that remembers the last color the user picked and shows that color, instead of always showing the default white color. This technique is very useful for storing the state of an application from use to use. For example, users’ preferences or most recent choices can be displayed when they return to the program. Please refer to the previous post for more information.
All of the code is presented below. Go ahead and copy it for your project. In order for this to work, you will also have to create a SQLite database file in the directory of your project, not in the bin directories. Click on the screenshot below to enlarge it.
Notice that one row. Alice Blue is #FFF0F8FF. It is the first one in the drop-down list and therefore its index number is 0. We use this index number to select the first one in the list when the window is loaded. When the user picks a different color, we’ll edit this row. Below is a screenshot of DB Browser for SQLite showing the table Preferences. This is the database in the bin\degug folder.
You will also need to install Dapper to follow along here.
Class
There is a class in our project that is the singlar name (Preference) of the database table name (Preferences). The class names are the same as the column names in the table.
namespace DropDownColorsInWPFSQLite
{
class Preference
{
public int Id { get; set; }
public string HexColor { get; set; }
public int IndexColor { get; set; }
}
}
App.config
For convenience I have named the database the same as the project. It doesn’t have to be the same, but it would make sense for it to be at least very similar because the database goes with this project. The dot in the Data Source means “this directory”. Therefore this is a relative path. The database file will be in the same directory as the program file, now and when you release it.
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<connectionStrings>
<add name="Default" connectionString="Data Source=.\DropDownColorsSQLite.db;Version=3;" providerName="System.Data.SqlClient"/>
</connectionStrings>
<startup>
<supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.6.1" />
</startup>
</configuration>
MainWindow.xaml
The XAML for this project is not very interesting except for the DataTemplate.
<Window x:Class="DropDownColorsInWPFSQLite.MainWindow"
xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
xmlns:d="http://schemas.microsoft.com/expression/blend/2008"
xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006"
xmlns:local="clr-namespace:DropDownColorsInWPFSQLite"
mc:Ignorable="d"
WindowStartupLocation="CenterScreen"
Title="DropDownColorsInWPFSQLite" Height="350" Width="400"
Loaded="Window_Loaded">
<Grid Margin="6">
<Grid.RowDefinitions>
<RowDefinition Height="20"/>
<RowDefinition Height="20"/>
<RowDefinition Height="25"/>
<RowDefinition/>
</Grid.RowDefinitions>
<Grid.ColumnDefinitions>
<ColumnDefinition/>
</Grid.ColumnDefinitions>
<TextBlock Name="txtblkTitle" Grid.Row="0">Welcome</TextBlock>
<TextBlock Name="txtblkRGBNumbers" Grid.Row="1">RGB</TextBlock>
<TextBlock Name="txtblkDescription" Grid.Row="3" TextWrapping="Wrap">Change the color of the background
of the RGB section above and this background with the drop-down selector. This background uses a different Alpha than
the above RGB section. Here the Alpha is not 255 as it is above, but it is arbitrarily set to 128 by the programmer
so that this text can be more easily viewed.
</TextBlock>
<ComboBox Grid.Row="2" Name="cboboxColors" ScrollViewer.CanContentScroll="True"
ScrollViewer.VerticalScrollBarVisibility="Auto"
SelectionChanged="CboboxColors_SelectionChanged">
<ComboBox.ItemTemplate>
<DataTemplate>
<StackPanel Orientation="Horizontal" Margin="0,3,0,0">
<StackPanel.Resources>
<Style TargetType="TextBlock">
<Setter Property="Margin" Value="5,0,0,0"/>
</Style>
</StackPanel.Resources>
<Rectangle Width="30" Fill="{Binding SampleBrush}"/>
<TextBlock Name="ColorOfItem" Width="130" Text="{Binding ColorName}"/>
<TextBlock Width="70" Text="{Binding HexValue}"/>
</StackPanel>
</DataTemplate>
</ComboBox.ItemTemplate>
</ComboBox>
</Grid>
</Window>
Code Behind MainWindow.xaml.cs
using System.Linq;
using System.Windows;
using System.Reflection;
using System.Windows.Controls;
using System.Windows.Media;
namespace DropDownColorsInWPFSQLite
{
public partial class MainWindow : Window
{
public MainWindow()
{
InitializeComponent();
}
// List samples of the named colors.
private void Window_Loaded(object sender, RoutedEventArgs e)
{
var color_query = from PropertyInfo property in typeof(Colors).GetProperties()
orderby property.Name
//orderby ((Color)property.GetValue(null, null)).ToString()
select new ColorInfo(
property.Name,
(Color)property.GetValue(null, null));
cboboxColors.ItemsSource = color_query;
// read the database to get the color index and hex color
var hexcolor = "";
hexcolor = SqliteDataAccess.GetColor();
var indexcolor = SqliteDataAccess.GetIndexColor();
cboboxColors.SelectedIndex = indexcolor; // White is 137
}
private void CboboxColors_SelectionChanged(object sender, SelectionChangedEventArgs e)
{
ComboBox cb = sender as ComboBox;
int indx = cb.SelectedIndex;
// change the background of the second row (second TextBox)
txtblkRGBNumbers.Background = new SolidColorBrush(Color.FromRgb((cb.SelectedItem as ColorInfo).Color.R,
(cb.SelectedItem as ColorInfo).Color.G, (cb.SelectedItem as ColorInfo).Color.B));
// txtblkDescription
txtblkDescription.Background = new SolidColorBrush(Color.FromArgb(128,
(cb.SelectedItem as ColorInfo).Color.R,
(cb.SelectedItem as ColorInfo).Color.G,
(cb.SelectedItem as ColorInfo).Color.B));
// display the RBG and A of the selected color
object ci = cb.SelectedItem;
txtblkRGBNumbers.Text = (cb.SelectedItem as ColorInfo).ColorName +
" is Red " + (ci as ColorInfo).Color.R +
" Green " + (ci as ColorInfo).Color.G +
" Blue " + (ci as ColorInfo).Color.B +
" Alpha " + (ci as ColorInfo).Color.A;
// Write to SQLite database the HexValue
// Table: Preferences with 2 columns: Id and HexColor
Preference p = new Preference();
p.HexColor = (ci as ColorInfo).HexValue;
p.IndexColor = indx;
SqliteDataAccess.EditColor(p);
}
}
// Used to display color name, RGB value, and sample.
// From C Sharp Helper
// http://csharphelper.com/blog/2015/10/list-colors-in-wpf-and-c/
//
public class ColorInfo
{
public ColorInfo(string color_name, Color color)
{
ColorName = color_name;
Color = color;
}
public string ColorName { get; set; }
public Color Color { get; set; }
public int IndexColor { get; set; }
public SolidColorBrush SampleBrush
{
get { return new SolidColorBrush(Color); }
}
public string HexValue
{
get { return Color.ToString(); }
}
}
}
SqliteDataAccess.cs
This is the most interesting part of the project.
using System.Data;
using System.Data.SQLite;
using Dapper;
using System.Configuration;
using System;
using System.Windows;
namespace DropDownColorsInWPFSQLite
{
class SqliteDataAccess
{
public static void EditColor(Preference p)
{
string hex = p.HexColor;
int ic = p.IndexColor;
try
{
using (IDbConnection conn = new SQLiteConnection(LoadConnectionString()))
{
string sql = "update Preferences set HexColor = @HexColor";
var parameters = new { HexColor = hex };
var r = conn.Execute(sql, parameters);
string sql2 = "update Preferences set IndexColor = @IndexColor";
var parameters2 = new { IndexColor = ic };
var q = conn.Execute(sql2, parameters2);
}
}
catch (Exception ex)
{
MessageBox.Show("A handled exception just occurred: " + ex.Message,
"DropDownColorsInWPFSQLite", MessageBoxButton.OK, MessageBoxImage.Warning);
}
}
public static string GetColor()
{
try
{
using (IDbConnection conn = new SQLiteConnection(LoadConnectionString()))
{
string sql = "select HexColor from Preferences";
string hexcolor = conn.ExecuteScalar<string>(sql);
return hexcolor;
}
}
catch (Exception ex)
{
MessageBox.Show("A handled exception just occurred: " + ex.Message,
"DropDownColorsInWPFSQLite", MessageBoxButton.OK, MessageBoxImage.Warning);
return "FFFFFFFF"; //white is FFFFFF
}
}
public static int GetIndexColor()
{
try
{
using (IDbConnection conn = new SQLiteConnection(LoadConnectionString()))
{
string sql = "select IndexColor from Preferences";
int indexcolor = conn.ExecuteScalar<int>(sql);
return indexcolor;
}
}
catch (Exception ex)
{
MessageBox.Show("Default color is white. Setting to white. A handled exception just occurred: " + ex.Message,
"DropDownColorsInWPFSQLite", MessageBoxButton.OK, MessageBoxImage.Warning);
return 107; // white by default; white is index 107
}
}
private static string LoadConnectionString(string id = "Default")
{
// go to App.config and return the string called "Default"
// programmer needs to add to project Reference: ConfigurationManager and using System.Configuration
return ConfigurationManager.ConnectionStrings[id].ConnectionString;
}
}
}