In this post I will show the C# listings of our DailyThought program. Now we’re in the coding stage of development. Feel free to use this code in your own project. Before this will work we’d need to install SQLite and Dapper into our WPF C# project, if we haven’t already done so. How do you install SQLite into a WPF project? We have a post on that called WPF Installing SQLite and Dapper into a WPF Project. Next, we need to install Dapper. How do we do that? We have a post on that called Dapper Installation into your Project in Visual Studio. Also, in the References of Visual Studio (in the Solution Explorer), we need to install the configuration manager. To do that, right-click References, click Add Reference, and select System.Configuration by putting a check mark in the box to the left of System.Configuration.
Below is the listing for the App.config file. The importatn part is the connection string that specifies the name of the database, which is quotes.db in our case.
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<connectionStrings>
<add name="Default" connectionString="Data Source=.\quotes.db;Version=3;" providerName="System.Data.SqlClient"/>
</connectionStrings>
<startup>
<supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.6.1" />
</startup>
</configuration>
We’ve created a class that deals with accessing the database. It’s called SQLiteDataAccess.cs. You would just add a new Class in Solution Explorer. Here is the listing.
using System;
using System.Configuration;
using System.Data;
using Dapper;
using System.Data.SQLite;
using System.Windows;
namespace ThoughtForTheDay
{
class SQLiteDataAccess
{
public static DailyThought LoadThought(double dateREAL)
{
// dateREAL is the SQLite date as a REAL number.
// SQLite does not support built-in date and/or time storage class.
try
{
using (IDbConnection conn = new SQLiteConnection(LoadConnectionString()))
{ // using Dapper
var tableRow = conn.QueryFirstOrDefault("select * from ThoughtsTable where Date = @Date", new { Date = dateREAL });
var ds = new DailyThought();
// what if we do not find the record?
if (tableRow != null) // we found the record/row
{
ds.Id = tableRow.Id;
ds.Date = tableRow.Date;
ds.Thought = tableRow.Thought;
ds.Comments = tableRow.Explanation;
return ds;
}
else
{
ds.Id = 0;
ds.Date = 0;
ds.Thought = "";
ds.Comments = "";
return ds;
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "LoadThought Error", MessageBoxButton.OK, MessageBoxImage.Warning);
return new DailyThought { };
}
}
private static string LoadConnectionString(string id = "Default")
{
// go to App.config and return the string called "Default"
// need to add Reference: ConfigurationManager and using System.Configuration
return ConfigurationManager.ConnectionStrings[id].ConnectionString;
}
}
}
Below is the listing for MainWindow.xaml.cs file. It’s the code behind the XAML front-end. You can see that this code roughly follows the algorithm in the previous post. If the user wants to move very quickly through the days, either back or forward, they can hold the left or right arrow keys down. This is much faster than clicking with the mouse.
using System;
using System.Windows;
using System.Windows.Input;
namespace ThoughtForTheDay
{
public partial class MainWindow : Window
{
private void Window_PreviewKeyDown(object sender, KeyEventArgs e)
{
// ... Test for right or left arrow keys and equate those to
// clicking the right or left arrow buttons.
// do not forget PreviewKeyDown="Window_PreviewKeyDown" in the XAML
// and using System.Windows.Input in this code above
if (e.Key == Key.Right)
{
BtnForward_Click(sender, e);
}
if (e.Key == Key.Left)
{
BtnBack_Click(sender, e);
}
}
public MainWindow()
{
InitializeComponent();
// get the system date (which is today) and display the daily thought
// to the user on the Windows WPF GUI.
//
DateTime todayCSharp = DateTime.Now;
double todayOADate = todayCSharp.ToOADate();
todayOADate = Math.Truncate(todayOADate); // we don't need the time which is the fractional portion
double sqliteDate = todayOADate + 2415019; // convert to SQLite date
var DailyThought = SQLiteDataAccess.LoadThought(sqliteDate); // read db for today
// load the GUI with the results
txtblkThought.Text = DailyThought.Thought;
txtblkExplanation.Text = DailyThought.Comments;
// convert the date in the SQLite db (real) to
lblDate.Content = todayCSharp.ToLongDateString();
lblDateCSharp.Content = todayOADate; // store it in the interface
lblDateSQLite.Content = sqliteDate;
}
private void BtnBack_Click(object sender, RoutedEventArgs e)
{
// The user wants to see the previous thought for the day.
// Get the currently displayed date number from the two hidden labels on the interface
// and subtract one day and re-display the new date and the two hidden numbers on the GUI.
// Find the date in the database and display the content (thought and comments)
double sqliteDate = Convert.ToDouble(lblDateSQLite.Content) - 1; //
double csharpDate = Convert.ToDouble(lblDateCSharp.Content) - 1; //
DateTime dt = DateTime.FromOADate(csharpDate);
lblDate.Content = dt.ToLongDateString();
lblDateCSharp.Content = csharpDate.ToString();
lblDateSQLite.Content = sqliteDate.ToString();
// Read the database
var DailyThought = SQLiteDataAccess.LoadThought(sqliteDate); // read db for the date
// load the GUI with the results
txtblkThought.Text = DailyThought.Thought;
txtblkExplanation.Text = DailyThought.Comments;
}
private void BtnForward_Click(object sender, RoutedEventArgs e)
{
// The user wants to see the next thought for the day.
// Get the currently displayed date from the two hidden labels on the interface
// and add one day and re-display the new date and the two hidden numbers on the GUI.
// Get the date from the database and display the content (thought and comments)
double sqliteDate = Convert.ToDouble(lblDateSQLite.Content) + 1; //
double csharpDate = Convert.ToDouble(lblDateCSharp.Content) + 1; //
DateTime dt = DateTime.FromOADate(csharpDate);
lblDate.Content = dt.ToLongDateString();
lblDateCSharp.Content = csharpDate.ToString();
lblDateSQLite.Content = sqliteDate.ToString();
// Read the database
var DailyThought = SQLiteDataAccess.LoadThought(sqliteDate); // read db for the date
// load the GUI with the results
txtblkThought.Text = DailyThought.Thought;
txtblkExplanation.Text = DailyThought.Comments;
}
}
}