DataBinding in WPF Browser Application using SQL Server Compact

• Download source - 50.76 KB

Background

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

Pre-Requisite

• Visual Studio 2008/2010
• SQL Server 2008

Using the Code

Once you have both the pre-requisites installed on your machine, launch “Visual Studio 2010” --> Select “New Project” to create a new WPF Browser Application as shown in the below screenshot:

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

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

I have used ADO.NET for data binding to Datagrid, it's 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> 

In the above code, I have binded the data to the Datagrid by giving the binding path name to “ItemsSource” attribute as “MyDataBinding” which will refer to the dataset name which I have declared in “BindGrid()” method. To have a customized view of the data in the datagrid, here I used DataGrid columns so that we can have our own order of displaying data as shown in the below screenshot:

For the first column, I have used “DataGridTextColumn” and referred to the database column name “fname” for “First Name” so that the value directly binded to this column.

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 a 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());
            }
        }

To get the shortdate, I have used “ToShortDateString()” method for DatePicker control. There are many methods available with this control and you can use it according to your requirement.

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 the 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 shown 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 File

<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>

The benefit of using App.Config file to store all the application configurations in one file or one place, similar to Web.Config in ASP.NET applications.

To get the connection string value from the app.config file, we have to create a reference in our code, so I have used “ConfigurationManager” of the namespace “System.Configuration” as shown in the below code:

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

To know more about System.Configuration class, refer to this link.

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 the design and coding part of our first WPF Browser project and we need to test our project, to do so hit “F5”,

OOPs, 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 to many links, but nothing worked for me. In one forum, I found deleting “app.manifest” and create new “app.manifest” will work. Wow, it worked for me. :-)

I know this is not the right solution for this issue, so I am still working on it. If anyone comes 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.

I 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 I have tested the functionalities by adding, deleting and updating the data in the datagrid. So we have successfully created and tested our project.

Happy programming :-).