DataGrid in WPF using Sql Server Compact

Introduction

Windows Presentation Foundation (WPF) is a technology which is an improvement of Windows Forms. The main advantage of WPF is a User Interface design; a developer can have good design using 2D and 3D rendering and adaptive documents, typography, vector graphics, runtime animation, and pre-rendered media.

Background

This article helps you to create a simple WPF application and to bind data in a datagrid using SQL Server Compact 3.5 sp1 as a data source. In this article I would like to show the simple way of data binding to a datagrid in WPF.

Pre requisites

1. Visual Studio 2008/2010
2. SQL Server 2008

Using the code

In this article I have used Visual Studio 2010 to demonstrate the example. Find the steps below to create a new WPF project.

Create New WPF Project

Step 1: Click Start All Program -> Microsoft Visual Studio 2010 -> Microsoft Visual Studio 2010

Step 2: Click "New Project"

Step 3: Under "Visual C#" -> Windows -> WPF Application



Give the application name for your new WPF Application; provide a location and solution name.

Start Designing and programming using WPF

Designing a Windows or web application is quite easy and interesting also. Let's start designing our first application using WPF.



WPF will generate XAML tags similar to HTML. Here I have used the below mentioned tools for design:

fName_lbl -> "First Name" Label
fName_Txt -> Textbox
lName_lbl -> "Last Name" Label
lName_Txt -> Textbox
DOB_lbl -> "Date of Birth" Label
DOB_Txt -> Date Picker
City_lbl -> "City" Label
City_Txt -> Combo Box
New_Btn -> "New" Button
Add_Btn -> "Add" Button
Del_Btn -> "Delete" Button
Update_Btn -> "Update" Button
Datagrid1

Databinding to "Datagrid1" in WPF is simple, in this example I have used ADO.net for binding.

<DataGrid AutoGenerateColumns="False" Grid.Row="4" Height="199" HorizontalAlignment="Left" Margin="14,65,0,0" Name="dataGrid1" VerticalAlignment="Top" Width="481" ItemsSource="{Binding Path=MyDataBinding}" Loaded="dataGrid1_Loaded" CanUserResizeRows="False" SelectedCellsChanged="dataGrid1_SelectedCellsChanged">
            <DataGrid.Columns>
                <DataGridTextColumn Binding="{Binding Path=fName}" Header="First Name" Width="120" IsReadOnly="True" />
                <DataGridTextColumn Binding="{Binding Path=lName}" Header="Last Name" Width="110" IsReadOnly="True" />
                <DataGridTextColumn Binding="{Binding Path=DOB}" Header="Date Of Birth" Width="150"
IsReadOnly="True" />
                <DataGridTextColumn Binding="{Binding Path=City}" Header="City" Width="90" IsReadOnly="True" />
   </DataGrid.Columns>          
</DataGrid>

We can bind the data to the datagrid by assigning it to datagrid's "ItemsSource" and providing the data path which will connect to the database to get the data. This is an add-on feature in WPF.
App.config is a configuration file which will have the setting and other stuff for an application. Here I have used this to store the database connectionstring and it is as follows:

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
     <connectionStrings>
         <add name="ConnectionString1" connectionString="Data Source=(Database file location goes here) \DatabindusingWPF.sdf; Password=test@123; Persist Security Info=False;"/>
        </connectionStrings>
</configuration>

Here I have used SQL Server Compact 3.5 sp1 as my data source so we have to give the exact path of the database file where it is stored. (Note: You need to give the exact path of the database file to make this code work).

Whenever we add, delete or update a table the datagrid must be changed accordingly, so I created a public method named "BindGrid()".

// Establishing Connection String from Configuration File
string _ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionString1"].ConnectionString;

public void BindGrid()
 {

    SqlCeConnection _Conn = new SqlCeConnection(_ConnectionString);

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

    SqlCeDataAdapter _Adapter = new SqlCeDataAdapter("Select * from Details", _Conn);

    DataSet _Bind = new DataSet();
    _Adapter.Fill(_Bind, "MyDataBinding");

    dataGrid1.DataContext = _Bind;

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

   }

In the above I have shown a very simple way to get the SQL connection string which will connect to the database and use the dataset to bind to the data to datagrid. The code shown below will add a new record using the values from the textbox.

ADD:

private void Add_Btn_Click(object sender, RoutedEventArgs e)
  {
      try
      {
         SqlCeConnection _Conn = new SqlCeConnection(_ConnectionString);

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

         string _Date = DOB_Txt.DisplayDate.ToShortDateString();

         // Command String
         string _Insert = @"insert into Details
                          (fName,lName,DOB,City)
                          Values('" + fName_Txt.Text + "','" + lName_Txt.Text + "','" +     _Date.ToString() + "','" + City_Txt.Text + "')";

          // Initialize the command query and connection
          SqlCeCommand _cmd = new SqlCeCommand(_Insert, _Conn);

          // Execute the command
          _cmd.ExecuteNonQuery();

          MessageBox.Show("One Record Inserted");
          fName_Txt.Text = string.Empty;
          lName_Txt.Text = string.Empty;
          DOB_Txt.Text = string.Empty;
          City_Txt.Text = string.Empty;

          this.BindGrid();

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

    }

To store only date into the database here I have used "ToShortDateString" which calls trim.

string _Date = DOB_Txt.DisplayDate.ToShortDateString();



To confirm that a record has been added I have a messagebox with the message "One Record Inserted" and after clicking "Ok", you should see the new record in the datagrid (refer to the image shown below).

private void Del_Btn_Click(object sender, RoutedEventArgs e)
        {
            try
            {
                SqlCeConnection _conn = new SqlCeConnection(_ConnectionString);

                // Open Database Connection
                _conn.Open();

                // Command String
                string _DelCmd = @"Delete from Details
                              Where fName='" + fName_Txt.Text + "'";

                // Initialize the command query and connection
                SqlCeCommand _CmdDelete = new SqlCeCommand(_DelCmd, _conn);

                // Execute the command
                _CmdDelete.ExecuteNonQuery();

                MessageBox.Show("One Record Deleted");
                fName_Txt.Text = string.Empty;
                lName_Txt.Text = string.Empty;
                DOB_Txt.Text = string.Empty;
                City_Txt.Text = string.Empty;

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

To update the existing data just double-click on the record in the datagrid and the values will be edited; they will be listed in their respective textboxes as shown in the image below:

The code below will perform editing of the datagrid value:

DataRowView _DataView = dataGrid1.CurrentCell.Item as DataRowView;

if (_DataView != null)
  {
      fName_Txt.Text = _DataView.Row[0].ToString();
      fName_Txt.IsEnabled = false;
      lName_Txt.Text = _DataView.Row[1].ToString();

      DOB_Txt.Text = _DataView.Row[2].ToString();
      City_Txt.Text = _DataView.Row[3].ToString();
  }

Here I have used "DataRowView" to read the current cell of the datagrid and fetch each cell value and have it assigned to a textbox.

You can't change the first name as we are using its value as primary key in the database, but you can change the other available fields. In this example I have changed the last name to "Sellamuthu".



Update Code

private void Update_Btn_Click(object sender, RoutedEventArgs e)
        {
            try
            {
                SqlCeConnection _Conn = new SqlCeConnection(_ConnectionString);
 
                // Open Database Connection
                _Conn.Open();
 
                string _Date = DOB_Txt.DisplayDate.ToShortDateString();
                               
                // Command String
                string _UpdateCmd = @"Update Details Set
                                    lName = '" + lName_Txt.Text + "',DOB = '" + _Date.ToString() + "',City = '" + City_Txt.Text + "' where fName = '" + fName_Txt.Text + "'";
 

                // Initialize the command query and connection
                SqlCeCommand _CmdUpdate = new SqlCeCommand(_UpdateCmd,_Conn);

                // Execute the command
                _CmdUpdate.ExecuteNonQuery();

                MessageBox.Show("One Record Updated");
                fName_Txt.Text = string.Empty;
                lName_Txt.Text = string.Empty;
                DOB_Txt.Text = string.Empty;
                City_Txt.Text = string.Empty;

                this.BindGrid();

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


I have used an Update query to update the record on the database by referring to the primary key "First Name".

Conclusion

Thus we have created a new WPF application using Visual Studio 2010 with SQL Server Compact. Happy Programming.