WPF Database connectivity (A sample application)

Hello All,

Today I am sharing you a sample WPF (Windows Presentation Foundation) application. WPF is becoming very popular these days in desktop application. The two things which I am demonstrating here is the Connectivity and the GRIDVIEW.

* DataBase Connectivity :- The connectivity to the data base is simple as the connectivity in the normal desktop application, we simply need to connection string and the object of the provider class like OleDB or ADO.NET

* Grid View :- GridView is a very common data presentation control of the .net, but you will surprise that there is no gridview control in WPF or you can say there is no direct gridvew control, but the good news is that we can have the gridview like functionality using the LISTVIEW. There is a view property which can be used to provide a gridview type style and functionality.. here we have to set the property to the LISTVIEW as the gridview is not having its own property and controls.

Please take a look of the XAML page of the application where the Simple grid is used to insert the fields in the data base, a Normal GRID is used to create a table like structure and the TextBoxes to accept the values entered by the user.

<Window x:Class="testWPF.MainWindow"
xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
Title="MainWindow" Height="350" Width="525" WindowStartupLocation="CenterScreen" BorderThickness="1" BorderBrush="#FF190000">
<Grid Name="grdSample" VerticalAlignment="Center" HorizontalAlignment="Center" Height="auto" Width="auto">
<Grid.ColumnDefinitions>
<ColumnDefinition Width="200"></ColumnDefinition>
<ColumnDefinition Width="200"></ColumnDefinition>
</Grid.ColumnDefinitions>

<Grid.RowDefinitions>
<RowDefinition Height="30"></RowDefinition>
<RowDefinition Height="30"></RowDefinition>
<RowDefinition Height="30"></RowDefinition>
<RowDefinition Height="30"></RowDefinition>
<RowDefinition Height="30"></RowDefinition>
<RowDefinition Height="30"></RowDefinition>
<RowDefinition Height="30"></RowDefinition>
<RowDefinition></RowDefinition>
</Grid.RowDefinitions>

<TextBlock Grid.Column="0" Grid.Row="0" HorizontalAlignment="Center" Background="Aqua">Field Name</TextBlock>
<TextBlock Grid.Column="1" Grid.Row="0" HorizontalAlignment="Center" Background="Aqua">Field Value</TextBlock>

<TextBlock Grid.Column="0" Grid.Row="1">First Name</TextBlock>
<TextBlock Grid.Column="0" Grid.Row="2">Last name</TextBlock>
<TextBlock Grid.Column="0" Grid.Row="3">Address</TextBlock>
<TextBlock Grid.Column="0" Grid.Row="4">Email</TextBlock>
<TextBlock Grid.Column="0" Grid.Row="5">Country</TextBlock>

<TextBox Name="txtFname" Grid.Column="1" Grid.Row="1"></TextBox>
<TextBox Name="txtlname" Grid.Column="1" Grid.Row="2"></TextBox>
<TextBox Name="txtAdd" Grid.Column="1" Grid.Row="3"></TextBox>
<TextBox Name="txtEmail" Grid.Column="1" Grid.Row="4"></TextBox>
<TextBox Name="txtCountry" Grid.Column="1" Grid.Row="5"></TextBox>

<Button Name="btnInsert" Content="Insert" VerticalAlignment="Center" Grid.Column="0" Grid.Row="6" Click="btnInsert_Click"></Button>
<Button Name="btnClear" Content="Clear All" VerticalAlignment="Center" Grid.Column="1" Grid.Row="6" Click="btnClear_Click"></Button>


<ListView Name="lvSample" Grid.Column="0" Grid.ColumnSpan="2" Grid.Row="7" ItemsSource="{Binding Path=Table}">
<ListView.View>
<GridView x:Name="gvsample">
<GridViewColumn Width="80" Header="First Name" DisplayMemberBinding="{Binding Path=Fname}" />
<GridViewColumn Width="80" Header="Last Name" DisplayMemberBinding="{Binding Path=Lname}" />
<GridViewColumn Width="100" Header="Address" DisplayMemberBinding="{Binding Path=Address}" />
<GridViewColumn Width="80" Header="Email" DisplayMemberBinding="{Binding Path=Email}"/>
<GridViewColumn Width="60" Header="Country" DisplayMemberBinding="{Binding Path=Country}" />
</GridView>
</ListView.View>
</ListView>

</Grid>


</Window>

The Page will be create some thing like this.. while the design time. You can customize the page according to your need by setting the height and width of the grid and the listview.

In the Code part of the application, Simply loading the grid to display the existing record. Ans in the Insert button the code to insert the record is placed.. Define your own connection string create a SAMPLE data base as i have used and the table name to insert the record is TEST. Please take a look at the coding part.

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.SqlClient;
using System.Data;

namespace testWPF
{
///

/// Interaction logic for MainWindow.xaml
///
public partial class MainWindow : Window
{
#region "Global Variable"
string strConnString = "Data Source=ServerName;Initial Catalog=Sample;uid=sa;pwd=test;Pooling=true;Min Pool Size=0;Max Pool Size=1000;";

#endregion

public MainWindow()
{
InitializeComponent();
txtFname.Focus();
loadGrid();
}

private void btnInsert_Click(object sender, RoutedEventArgs e)
{
SqlConnection con = new SqlConnection(strConnString);
con.Open();
string strQuery = "insert into test values('" + txtFname.Text.Trim() + "','" + txtlname.Text.Trim() + "','" + txtAdd.Text.Trim() + "','" + txtEmail.Text.Trim() + "','" + txtCountry.Text.Trim() + "')";
SqlCommand cmd = new SqlCommand(strQuery, con);
int a = cmd.ExecuteNonQuery();
if (a > 0)
{
MessageBox.Show("Record Inserted!");
clearAll();
loadGrid();
}
else
{
MessageBox.Show("Error while insertion.");
//clearAll();
}
con.Close();
}

private void btnClear_Click(object sender, RoutedEventArgs e)
{
clearAll();
}
private void clearAll()
{
txtFname.Text = "";
txtlname.Text = "";
txtAdd.Text = "";
txtEmail.Text = "";
txtCountry.Text = "";
txtFname.Focus();
}

private void loadGrid()
{
SqlConnection con = new SqlConnection(strConnString);
SqlDataAdapter ad = new SqlDataAdapter();
SqlCommand cmd = new SqlCommand();
con.Open();
string strQuery = "select * from Test";
cmd.CommandText = strQuery;
ad.SelectCommand = cmd;
cmd.Connection = con;
DataSet ds = new DataSet();
ad.Fill(ds);
lvSample.DataContext = ds.Tables[0].DefaultView;
con.Close();
}
}
}

The first output of the screen is shown below.

The Next screen after entering the data in the fields will be..

and the final output screen shows the newly inserted record in the grid..

I hope you have like this article. As you have seen there is not much effort is involved in working on WPF its very easy and simple. I will come with some more new features and control of WPF, till then enjoy and please please let me know your feedback by your valuable comments.

Thanks
Anil Kumar Pandey
Micorsoft MVP (C#)