How to use SQLite with Entity Framework 6 in WPF Application

When you are looking for a free and light weight tool to store your data locally SQLite is the best alternative to go for, SQLite is a small, fast and reliable database which can be used by end user without any installation requirement.

Getting Started:

Here are the steps you need to follow:

1> Create File->New Project -> WPF Application.

2> Right click on your project -> Properties -> Build Tab -> Change Platform Target to x86 and build solution.

3> Now  you first need to install Entity Framework via nuget.

Go to Tools -> Library Package Manager - > Package Manager Console, 

Your Package Mangager Console will open, paste the below line and hit enter.

Install-Package EntityFramework

It will install latest version of Entity Framework from nuget in your application.

Also you need to have SQLite to be install in your application, so paste the below line in your PMC and hit enter.

Install-Package System.Data.SQLite

4> Now as SQLite and EF is reference in our application, we need to create SQLite  database to use with our application.

There is a good Firefox plugin "SQLite Manger" for creating and managing SQLite database.

I have created a database name "MyDB", with one table "UserInfo", below is the structure of this table:


CREATE TABLE "UserInfo" ("ID" INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL , "FirstName" TEXT, "LastName" TEXT)

Also add few records in the table, so that you can view that in your application.


5> Once your database is created, In your application add new folder give it any name say "DataFile" and put your SQLite database here in this folder.

Now right click your database file -> Properties -> Select "Copy if newer" in Copy to Output Directory field and build project.


6> Open MainWindow.xaml and insert below code in your grid.


<DataGrid x:Name="dataGrid" HorizontalAlignment="Stretch" VerticalAlignment="Stretch" />

7> Go to MainWindow.cs file and add following code(This is code base configuration instead of configuring in app.config):

        public class SQLiteConnectionFactory : IDbConnectionFactory
        {
            public DbConnection CreateConnection(string nameOrConnectionString)
            {
                return new SQLiteConnection(nameOrConnectionString);
            }
        }

        public class SQLiteConfiguration : DbConfiguration
        {
            public SQLiteConfiguration()
            {
                SetDefaultConnectionFactory(new SQLiteConnectionFactory());
                SetProviderFactory(&quot;System.Data.SQLite&quot;, SQLiteFactory.Instance);
                SetProviderFactory(&quot;System.Data.SQLite.EF6&quot;, SQLiteProviderFactory.Instance);
                Type t = Type.GetType(&quot;System.Data.SQLite.EF6.SQLiteProviderServices, System.Data.SQLite.EF6&quot;);
                FieldInfo fi = t.GetField(&quot;Instance&quot;, BindingFlags.NonPublic | BindingFlags.Static);
                SetProviderServices(&quot;System.Data.SQLite&quot;, (DbProviderServices)fi.GetValue(null));
            }
        }


8> We need to create a class that represent our UserInfo table, so here is the class for that:

        [Table("UserInfo")]
        public class User
        {
            public int ID { get; set; }
            public string FirstName { get; set; }
            public string LastName { get; set; }
        }


9> No we need to create DataContext of use Entity Framework with Sqlite:

         public class MyDBContext : DbContext
        {
            public MyDBContext()
                : base(new SQLiteConnection()
                {
                    ConnectionString =
                        new SQLiteConnectionStringBuilder() { DataSource = "I:\\WPF\\MyDB.sqlite"}
                        .ConnectionString
                }, true)
            {

            }
            public DbSet<User> Users { get; set; }
        }


You can see that I have give static path of my sqlite database in DataSource field, but you can give relative path using Assembly.GetExecutingAssembly() method.


10> As our DataContext is created, we need to fetch record from the database to display in our datagrid, so write this below code in your MainWindow_Loaded:

                MyDBContext context = new MyDBContext();
                context.Users.OrderBy(c => c.FirstName).Load();
                this.dataGrid.ItemsSource = context.Users.Local;


11> Now run the application and you will see that all your records in database file will be display in datagrid.


Happy Coding :)

How to integrate Font Awesome in WPF Application.

Font Awesome is really a good choice for those who are looking for icons fonts instead of heavy images that needs to embed in to the application, that makes application heavy with lots of images.

Font Awesome has many advantages, like you don't have to care of colors, if you need to change it any time, you have to just change simple property value of  XAML controls. Also you don't have to care of the size and pixel rendering, Font Awesome is really good in that as it uses vector graphics, so simply settings FontSize or relative properties on control will do your job.

Lets have a look on how to integrate Font Awesome in your WPF application:

1> First you have to download Font Awesome which you can from below link :

    http://fortawesome.github.io/Font-Awesome/

2>Now create an File->New Project -> WPF Application.

3>Add new folder "Fonts" in your application.

4>Extract Font Awesome downloaded zip, inside fonts you will find "fontawesome-webfont.ttf" file, add this file to your "Fonts" folder in your application. Below is the screenshot of how its in VS 2012

5>Add below snippet to App.xaml file inside <Application.Resources> tag
  
<FontFamily x:Key="FontAwesome">/Fonts/fontawesome-webfont.ttf#FontAwesome</FontFamily>

6> Open MainWindow.xaml and replace the grid with below snippet:

<Grid VerticalAlignment="Center" HorizontalAlignment="Center">

<StackPanel Orientation="Horizontal" >

<TextBlock Text="I" FontSize="32" Margin="10" VerticalAlignment="Center"></TextBlock>

           
<TextBlock x:Name="tbFontAwesome" Text="&#xf004;" FontFamily="{StaticResource FontAwesome}" Foreground="Red" FontSize="32" Margin="10" VerticalAlignment="Center"></TextBlock>
<TextBlock Text="Font Awesome" FontSize="32" Margin="10" VerticalAlignment="Center"></TextBlock>

</StackPanel>

</Grid>


7> Notice "Text" property of  "tbFontAwesome" textblock, its the Unicode for Heart.You can find more others from this below link:

http://fortawesome.github.io/Font-Awesome/cheatsheet/


8>Now run the application and you will see the output as below.So now you can set size or color of your choice.