Introduction
Windows Presentation Foundation shortly known as WPF is a technology which is betterment of Windows Forms. The main advantage of WPF is 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 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.
Pre requisites
- Visual Studio 2008/2010
- SQL Server 2008
Using the Code
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()
”.
string _ConnectionString = ConfigurationManager.ConnectionStrings
["ConnectionString1"].ConnectionString;
public void BindGrid()
{
SqlCeConnection _Conn = new SqlCeConnection(_ConnectionString);
_Conn.Open();
SqlCeDataAdapter _Adapter = new SqlCeDataAdapter("Select * from Details", _Conn);
DataSet _Bind = new DataSet();
_Adapter.Fill(_Bind, "MyDataBinding");
dataGrid1.DataContext = _Bind;
_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);
_Conn.Open();
string _Date = DOB_Txt.DisplayDate.ToShortDateString();
string _Insert = @"insert into Details
(fName,lName,DOB,City)
Values('" + fName_Txt.Text + "','" + lName_Txt.Text + "','" +
_Date.ToString() + "','" + City_Txt.Text + "')";
SqlCeCommand _cmd = new SqlCeCommand(_Insert, _Conn);
_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);
_conn.Open();
string _DelCmd = @"Delete from Details
Where fName='" + fName_Txt.Text + "'";
SqlCeCommand _CmdDelete = new SqlCeCommand(_DelCmd, _conn);
_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 textbox
es 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);
_Conn.Open();
string _Date = DOB_Txt.DisplayDate.ToShortDateString();
string _UpdateCmd = @"Update Details Set
lName = '" + lName_Txt.Text + "',
DOB = '" + _Date.ToString() + "',
City = '" + City_Txt.Text + "'
where fName = '" + fName_Txt.Text + "'";
SqlCeCommand _CmdUpdate = new SqlCeCommand(_UpdateCmd,_Conn);
_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
”.
Conclusion
Thus, we have created a new WPF application using Visual Studio 2010 with SQL Server Compact. Happy programming. :-)
History
- 5th February, 2011: Initial post