DataBinding in WPF using Sql Server Compact

After completing my first Article (nearly 6 months back ;-)) I had plans to create a browser application for the same. This article will help you to create WPF browser application with basic technique of data binding using Sql Server compact 3.5 sp1, so without waiting anymore time let's start our first WPF browser application.

Pre-Requisite

1. Visual Studio 2010/2008,
2. Sql Server 2008.

Once you have both the pre-requisite installed on your machine. Launch "Visual Studio 2010" -> Select "New Project"

Select "Windows" -> Select "WPF Browser Application" and provide name for the application, Click "Ok" to create new WPF browser application.

So now we have created new application, our next agenda is to create a GUI. Please find below the controls I have used,

Label	Control	Control Name
First Name	Text Box	FirstName_txt
Last Name	Text Box	LastName_txt
Date Of Birth	DatePicker	DOB_txt
City	ComboBox	City_txt
	DataGrid	Details_Grid
New	Button	New_btn
Add	Button	Add_btn
Delete	Button	Del_btn
Update	Button	Update_btn

I have used ADO.net for data binding to Datagrid, its very simple to bind the data very quickly. Find below the XAML code for the DataGrid,

<DataGrid AutoGenerateColumns="False" Height="181" HorizontalAlignment="Left" Margin="12,188,0,0" Name="Details_Grid" VerticalAlignment="Top" Width="518" ItemsSource="{Binding Path=MyDataBinding}" CanUserResizeRows="False" Loaded="Details_Grid_Loaded" SelectedCellsChanged="Details_Grid_SelectedCellsChanged">
            <DataGrid.Columns>
                <DataGridTextColumn Binding="{Binding Path=fName}" Header="First Name" Width="120" IsReadOnly="True" />
                <DataGridTextColumn Binding="{Binding Path=lName}" Header="Last Name" Width="120" IsReadOnly="True" />
                <DataGridTextColumn Binding="{Binding Path=DOB}" Header="Date Of Birth" Width="150" IsReadOnly="True" />
                <DataGridTextColumn Binding="{Binding Path=City}" Header="City" Width="120" IsReadOnly="True" />
            </DataGrid.Columns>
</DataGrid>

So we are all set to write the code for the functionalities, will start with "Add" a data to the database. Just double click on "Add" button it will create new click event and put the code as shown below,

private void Add_btn_Click(object sender, RoutedEventArgs e)
        {
            try
            {
                SqlCeConnection Conn = new SqlCeConnection(Connection_String);

                // Open the Database Connection
                Conn.Open();

                string Date = DOB_txt.DisplayDate.ToShortDateString();

                // Command String
                string Insert_Cmd = @"insert into Details(fName,lName,DOB,City) Values('" + FirstName_txt.Text + "','" + LastName_txt.Text +"','" + Date.ToString() + "','" + City_txt.Text + "')";

                // Initialize the Command Query and Connection
                SqlCeCommand cmd = new SqlCeCommand(Insert_Cmd, Conn);

                // Execute the Command
                cmd.ExecuteNonQuery();

                MessageBox.Show("One Record Inserted");
                FirstName_txt.Text = string.Empty;
                LastName_txt.Text = string.Empty;
                DOB_txt.Text = string.Empty;
                City_txt.Text = string.Empty;

                this.BindGrid();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }
        }

In the above code I used "sqlCeConnection" to establish connection to sql server compact database, to do this you need to add reference for Sql Server Compact as shown in below screenshots,

Right-Click on References -> Select "Add Reference"

Browse to "C:\Program Files\Microsoft SQL Server Compact Edition\v3.5\Desktop" -> Select "System.Data.SqlServerCe.dll" and Click "Ok". Now you can see "Sql Server Compact" listed in the references as show below,

To use the methods of this namespace, call it in the header as we do for Sql Server.

using System.Data.SqlServerCe;

I have used App.Config file to store the database connection details and I have called the connection string name in the code to establish the database connection.

App.Config

<configuration>
  <connectionStrings>
    <add name="ConnectionString1" connectionString="Data Source=<Location of the Database file Goes here>\DatabindusingWPF.sdf; Password=test@123; Persist Security Info=False;"/>
  </connectionStrings>
</configuration>

To pass the connection string value I have used "ConfigurationManager" of the namespace "System.Configuration" as shown in the below code,

string Connection_String = ConfigurationManager.ConnectionStrings["ConnectionString1"].ConnectionString;

To Bind data to Datagrid I have created a method namely "BindGrid" and I have called the method in all the other events to refresh the Datagrid data.

  public void BindGrid()
        {
            try
            {
                SqlCeConnection Conn = new SqlCeConnection(Connection_String);

                //Open the Database Connection
                Conn.Open();

                SqlCeDataAdapter Adapter = new SqlCeDataAdapter("Select * from Details", Conn);

                // Bind Data to DataSet
                DataSet Bind = new DataSet();
                Adapter.Fill(Bind, "MyDataBinding");

                // Bind Data to DataGrid
                Details_Grid.DataContext = Bind;

                // Close the Database Connection
                Conn.Close();

            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }
        }

So We need to write code for other functionalities like New,Update and Delete (Please refer to the attached Project)

Now we are done with design and coding part of our first WPF Browser project and we need to test our project, to do so hit "F5",





OOP's I got the above Error!!! (if you didn't see this error and our project is executed ...Wow .. you are lucky ;-))
I searched this error on the internet and referred many links nothing worked for me. In one forum I found deleting "app.manifest" and create new "app.manifest" will work, Wow it worked for me.

Yes I know this is not the right solution for this issue, so still I am working on it. If anyone come across any other solution for this issue please do post your finding as comments in this space. Even I will do the same If I come across any ;-) Ok Deal.

Cool so now it's launching without any error.



Found that browser title as "Databinding_WPF_Browser.xbap" which I want to change so I have added "WindowTitle" attribute in Page tag in XAML file as "DataBinding in WPF using Sql Server Compact".



The browser title has been changed now and tested the functionalities by adding,deleting and updating the data in the datagrid. So we have successfully created and tested our project.

You can also download this small example here.

Happy Programming.