DataGrid in WPF using SQL Server Compact 3.5 Sp1

This article will help you to create a simple WPF application to bind data in datagrid using SQL Server Compact 3.5 sp1 as data source. In this article, I would like to show the simple way of data binding to datagrid in WPF.
In this article, I have used Visual Studio 2010 to demonstrate the example. Below find the steps to create 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 application name for your new WPF applications, mention the location and solution name.

Start Designing and Programming using WPF

Designing 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 width="481" height="199" name="dataGrid1" 
selectedcellschanged="dataGrid1_SelectedCellsChanged" 
canuserresizerows="False" loaded="dataGrid1_Loaded" 
itemssource="{Binding Path=MyDataBinding}" verticalalignment="Top" 
margin="14,65,0,0" horizontalalignment="Left" grid.row="4" autogeneratecolumns="False">
            <datagrid.columns>
                <datagridtextcolumn width="120" isreadonly="True" 
                header="First Name" binding="{Binding Path=fName}">
                <datagridtextcolumn width="110" isreadonly="True" 
                header="Last Name" binding="{Binding Path=lName}">
                <datagridtextcolumn width="150" isreadonly="True" 
                header="Date Of Birth" binding="{Binding Path=DOB}">
                <datagridtextcolumn width="90" isreadonly="True" 
                header="City" binding="{Binding Path=City}">
</datagridtextcolumn></datagridtextcolumn><
/datagridtextcolumn></datagridtextcolumn></datagrid.columns>           
</datagrid>

We can bind the data to datagrid by assigning it to datagrid’s “ItemsSource” and mention the data path which will connect to 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:

<configuration>
     <connectionstrings>
         <add name="ConnectionString1" 
         connectionstring="Data Source=(Database file location goes here) 
         \DatabindusingWPF.sdf; Password=test@123; Persist Security Info=False;">
        </add></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 exact path of the database file to make this code work).
Whenever we add, delete, update datagrid has to 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 database and used dataset to bind the data to datagrid. The below code will add new record using the values from the textbox.

Add New Record

To add new record on database, I had used simple insert query and it will be populated in the datagrid. The below will do the trick:

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 gives trim the time.

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 once you click “Ok”, you should see the added record to be listed in datagrid (refer to the below image).

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 on the datagrid and the values will be edited, will be listed in their respective textboxes as shown in the below image:

The below code will perform editing 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 currentcell of the datagrid and fetch each cell value and have assigned it to a textbox.
You can’t change the first name as we are using its value as primary key in the database, so you can change on 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 Update query to update the record on the database by referring to the primary key “First Name”.

Here is the screenshots of all refrences and namespace i have used

Download source code here:
Download source - 308.08 KB

for some more Effective try this:
Source code:
<Window x:Class="ShibashishWpfApp.MainWindow"
        xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
        xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
        Title="MainWindow" Height="540" Width="555" DataContext="{Binding}" Loaded="Window_Loaded">
    <Grid Height="759" Width="558">
        <Grid.ColumnDefinitions>
            <ColumnDefinition Width="94*" />
            <ColumnDefinition Width="464*" />
        </Grid.ColumnDefinitions>
        <Grid.Background>
            <ImageBrush ImageSource="/ShibashishWpfApp;component/Images/shibashish.ico" />
        </Grid.Background>
        <Label Content="Om Namah Shivaya" Height="28" Width="119" HorizontalAlignment="Center"  Name="Label" VerticalAlignment="Top" Margin="56,20,290,0" OpacityMask="#FF71D3D6" Foreground="Blue" FontWeight="Bold" ToolTip="om namah shivaya" FontStretch="Normal" Grid.Column="1">
            <Label.Background>
                <LinearGradientBrush EndPoint="1,0.5" StartPoint="0,0.5">
                    <GradientStop Color="Black" Offset="0" />
                    <GradientStop Color="#FFFF6EE8" Offset="1" />
                </LinearGradientBrush>
            </Label.Background>
        </Label>
        <Label Content="First Name" Height="28" HorizontalAlignment="Left" Margin="14,61,0,0" Name="fName_lbl" VerticalAlignment="Top" Width="96" Grid.ColumnSpan="2" />
        <TextBox Height="23" HorizontalAlignment="Left" Margin="0,66,0,0" Name="fName_Txt" VerticalAlignment="Top" Width="187" Visibility="Visible" IsEnabled="True" Grid.Column="1" />
        <Label Content="Last Name" Height="28" HorizontalAlignment="Left" Margin="14,95,0,0" Name="lName_lbl" VerticalAlignment="Top" Width="66" />
        <TextBox Height="23" HorizontalAlignment="Left" Margin="0,97,0,0" Name="lName_Txt" VerticalAlignment="Top" Width="187" Grid.Column="1" />
        <Label Content="Date Of Birth" Height="28" HorizontalAlignment="Left" Margin="14,138,0,0" Name="DOB_lbl" VerticalAlignment="Top" Width="94" Grid.ColumnSpan="2" />
        <DatePicker Height="25" HorizontalAlignment="Left" Margin="0,141,0,0" Name="DOB_Txt" VerticalAlignment="Top" Width="187" Grid.Column="1" />
        <Label Content="City" Height="30" HorizontalAlignment="Left" Margin="14,181,0,0" Name="City_lbl" VerticalAlignment="Top" Width="94" Grid.ColumnSpan="2" />
        <ComboBox Height="23" HorizontalAlignment="Left" Margin="0,181,0,0" Name="City_Txt" VerticalAlignment="Top" Width="188" Background="White" Grid.Column="1">
            <ComboBoxItem Name="orissa" Content="Orissa"/>
            <ComboBoxItem Name="Bangalore" Content="Bangalore" />
            <ComboBoxItem Name="Mumbai" Content="Mumbai" />
        </ComboBox>
        <Button Content="New" Height="23" HorizontalAlignment="Left" Margin="14,233,0,0" Name="New" VerticalAlignment="Top" Width="70" Click="New_Click">
            <Button.Background>
                <LinearGradientBrush EndPoint="1,0.5" StartPoint="0,0.5">
                    <GradientStop Color="Black" Offset="0" />
                    <GradientStop Color="#FF70AE6D" Offset="1" />
                </LinearGradientBrush>
            </Button.Background>
            <Button.Foreground>
                <LinearGradientBrush EndPoint="1,0.5" StartPoint="0,0.5">
                    <GradientStop Color="Black" Offset="0" />
                    <GradientStop Color="#FF137070" Offset="1" />
                </LinearGradientBrush>
            </Button.Foreground>
        </Button>
        <Button Content="Add" Height="23" HorizontalAlignment="Left" Margin="22,233,0,0" Name="Add_Btn" VerticalAlignment="Top" Width="70" Grid.Column="1" Click="Add_Btn_Click">
            <Button.Background>
                <LinearGradientBrush EndPoint="1,0.5" StartPoint="0,0.5">
                    <GradientStop Color="Black" Offset="0" />
                    <GradientStop Color="#FF70AE6D" Offset="1" />
                </LinearGradientBrush>
            </Button.Background>
            <Button.Foreground>
                <LinearGradientBrush EndPoint="1,0.5" StartPoint="0,0.5">
                    <GradientStop Color="Black" Offset="0" />
                    <GradientStop Color="#FF137070" Offset="1" />
                </LinearGradientBrush>
            </Button.Foreground>
        </Button>
        <Button Content="Update" Height="23" HorizontalAlignment="Left" Margin="118,233,0,0" Name="Update" VerticalAlignment="Top" Width="70" Grid.Column="1" Click="Update_Click">
            <Button.Background>
                <LinearGradientBrush EndPoint="1,0.5" StartPoint="0,0.5">
                    <GradientStop Color="Black" Offset="0" />
                    <GradientStop Color="#FF70AE6D" Offset="1" />
                </LinearGradientBrush>
            </Button.Background>
            <Button.Foreground>
                <LinearGradientBrush EndPoint="1,0.5" StartPoint="0,0.5">
                    <GradientStop Color="Black" Offset="0" />
                    <GradientStop Color="#FF137070" Offset="1" />
                </LinearGradientBrush>
            </Button.Foreground>
        </Button>
        <Button Content="Delete" Height="23" HorizontalAlignment="Right" Margin="0,233,188,0" Name="Delete" VerticalAlignment="Top" Width="70" Grid.Column="1" Click="Delete_Click">
            <Button.Background>
                <LinearGradientBrush EndPoint="1,0.5" StartPoint="0,0.5">
                    <GradientStop Color="Black" Offset="0" />
                    <GradientStop Color="#FF70AE6D" Offset="1" />
                </LinearGradientBrush>
            </Button.Background>
            <Button.Foreground>
                <LinearGradientBrush EndPoint="1,0.5" StartPoint="0,0.5">
                    <GradientStop Color="Black" Offset="0" />
                    <GradientStop Color="#FF137070" Offset="1" />
                </LinearGradientBrush>
            </Button.Foreground>
        </Button>
        <DataGrid Name="GridView1" ItemsSource="{Binding Path=Details}" AutoGenerateColumns="True"  Height="150" Width="450" Margin="30,292,78,231" Grid.ColumnSpan="2" SelectedCellsChanged="GridView1_SelectedCellsChanged"></DataGrid>
    </Grid>
</Window>
code behind:
 using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using System.Windows.Navigation;
using System.Windows.Shapes;
using System.Data.SqlServerCe;
using System.Configuration;
using System.Data;




namespace ShibashishWpfApp
{
    /// <summary>
    /// Interaction logic for MainWindow.xaml
    /// </summary>
    public partial class MainWindow : Window
    {
        public MainWindow()
        {
            InitializeComponent();
        }
        string _ConnectionString = ConfigurationManager.ConnectionStrings["Connectionstring"].ConnectionString;
        string Query;
        SqlCeDataAdapter da;
        SqlCeConnection con;
        DataSet ds;
        SqlCeCommand cmd;
        private void Add_Btn_Click(object sender, RoutedEventArgs e)
        {

            insertData();
            clearData();
        }

        public void common()
        {
             con = new SqlCeConnection(_ConnectionString);
            con.Open();
           
        }
        public void ExecuteAndBindData()
        {
            cmd = new SqlCeCommand(Query, con);
            cmd.ExecuteNonQuery();
            BindGrid();
        }
        public void insertData()
        {
            common();
        Query = "insert into Details(fName,lName,Dob,City)" + "values('" + fName_Txt.Text + "','" + lName_Txt.Text + "','" + DOB_Txt.ToString() + "','" + City_Txt .Text+ "')";
        ExecuteAndBindData();

        }
        public void upadateData()
        {
         
            common();
            Query = "update  Details set lName='" + lName_Txt.Text + "',Dob='" + DOB_Txt.ToString() + "',City='" + City_Txt.Text + "' where fName='" + fName_Txt.Text + "'";
            ExecuteAndBindData();
        }
        public void DeleteData()
        {
            common();
            Query = "delete from Details where fName='" + fName_Txt.Text + "'";
            ExecuteAndBindData();
        }
        public void BindGrid()
        {
            try
            {

                 Query = "select * from Details";
                 common();
                 da = new SqlCeDataAdapter(Query, con);
                 ds = new DataSet();
                 da.Fill(ds, "Details");
                 GridView1.DataContext = ds;
             
                 con.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        public void clearData()
        {
            fName_Txt.Text = lName_Txt.Text = DOB_Txt.Text = City_Txt.Text = "";
        }
     

        private void Window_Loaded(object sender, RoutedEventArgs e)
        {
            BindGrid();

        }

        private void Update_Click(object sender, RoutedEventArgs e)
        {
            upadateData();
            clearData();
        }

        private void Delete_Click(object sender, RoutedEventArgs e)
        {
            DeleteData();
            clearData();
        }

        private void New_Click(object sender, RoutedEventArgs e)
        {
            if (fName_Txt.IsEnabled != true)
            {
                fName_Txt.IsEnabled = true;
            }
            clearData();
        }

        private void GridView1_SelectedCellsChanged(object sender, SelectedCellsChangedEventArgs e)
        {
            DataRowView RowChk = (DataRowView)GridView1.CurrentCell.Item;
            if (RowChk != null)
            {
                fName_Txt.Text = RowChk.Row[0].ToString();
                fName_Txt.IsEnabled = false;
                lName_Txt.Text = RowChk.Row[1].ToString();
                DOB_Txt.Text = RowChk.Row[2].ToString();
                City_Txt.Text = RowChk.Row[3].ToString();
            }
        }

     
    }
}

App.config:
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <connectionStrings>
    <add name="ConnectionString1" connectionString="Data Source=C:\Users\shibashish\Desktop\All Download\DemoData\Databinding\Databinding\Databinding\DatabindusingWPF.sdf; Password=test@123; Persist Security Info=False;"/>
    <add name="Connectionstring" connectionString="Data Source=c:\users\shibashish\documents\visual studio 2010\Projects\ShibashishWpfApp\ShibashishWpfApp\Shibashish.sdf" />
  </connectionStrings>
</configuration>

Add database:
Right click on project -->Add New Item -->Select Local Database

Thanks
Shibashish mohanty