In my previous article WPF DataGrid Control - Performing Update and Delete Operations, I explained Update and Delete operations in a WPF DataGrid control. In this article I will explain how to perform an Insert operation in WPF with DataGrid control. In this article I have used following events of the WPF DataGrid:
· CellEditEnding.
· SelectionChanged.
· RowEditEnding.
CellEditEnding allows us to retrieve values entered in every cell of the DataGrid. SelectionChanged event returns an object instance bind with DataGird. (In this case it returns an instance of ‘cleEmployee’ class). RowEditEnding events is fired when all cells are edited.
The script for creating the Employee Database table is in the db.sql file in the source code of this article.
Step 1: Open VS2010 and create a WPF application, name it as ‘WPF_DataGridInsert’. To this project, add a class file and name it as ‘DataFiles’ and add the following two classes in it.
C#
using System;
using System.Collections.ObjectModel;
using System.Data.SqlClient;
namespace WPF_DataGridInsert
{
public class clsEmployee
{
public int EmpNo { get; set; }
public string EmpName { get; set; }
public int Salary { get; set; }
public int DeptNo { get; set; }
}
public class DataAccess
{
SqlConnection Conn;
SqlCommand Cmd;
public DataAccess()
{
Conn = new SqlConnection("Data Source=.;Initial Catalog=Company;Integrated Security=SSPI");
}
public ObservableCollection<clsEmployee> GetAllEmployee()
{
ObservableCollection<clsEmployee> EmpCol = new ObservableCollection<clsEmployee>();
Conn.Open();
Cmd = new SqlCommand();
Cmd.Connection = Conn;
Cmd.CommandText = "Select * from Employee";
SqlDataReader Reader = Cmd.ExecuteReader();
while (Reader.Read())
{
EmpCol.Add(new clsEmployee()
{
EmpNo=Convert.ToInt32(Reader["EmpNo"]),
EmpName = Reader["EmpName"].ToString (),
Salary = Convert.ToInt32(Reader["Salary"]),
DeptNo = Convert.ToInt32(Reader["DeptNo"])
});
}
Conn.Close();
return EmpCol;
}
public void InsertEmployee(clsEmployee objEmp)
{
Conn.Open();
Cmd = new SqlCommand();
Cmd.Connection = Conn;
Cmd.CommandText = "Insert into Employee Values(@EmpNo,@EmpName,@Salary,@DeptNo)";
Cmd.Parameters.AddWithValue("@EmpNo", objEmp.EmpNo);
Cmd.Parameters.AddWithValue("@EmpName", objEmp.EmpName);
Cmd.Parameters.AddWithValue("@Salary", objEmp.Salary);
Cmd.Parameters.AddWithValue("@DeptNo", objEmp.DeptNo);
Cmd.ExecuteNonQuery();
Conn.Close();
}
}
VB.NET (Converted Code)
Imports System
Imports System.Collections.ObjectModel
Imports System.Data.SqlClient
Namespace WPF_DataGridInsert
Public Class clsEmployee
Public Property EmpNo() As Integer
Public Property EmpName() As String
Public Property Salary() As Integer
Public Property DeptNo() As Integer
End Class
Public Class DataAccess
Private Conn As SqlConnection
Private Cmd As SqlCommand
Public Sub New()
Conn = New SqlConnection("Data Source=.;Initial Catalog=Company;Integrated Security=SSPI")
End Sub
Public Function GetAllEmployee() As ObservableCollection(Of clsEmployee)
Dim EmpCol As New ObservableCollection(Of clsEmployee)()
Conn.Open()
Cmd = New SqlCommand()
Cmd.Connection = Conn
Cmd.CommandText = "Select * from Employee"
Dim Reader As SqlDataReader = Cmd.ExecuteReader()
Do While Reader.Read()
EmpCol.Add(New clsEmployee() With {.EmpNo=Convert.ToInt32(Reader("EmpNo")), .EmpName = Reader("EmpName").ToString (), .Salary = Convert.ToInt32(Reader("Salary")), .DeptNo = Convert.ToInt32(Reader("DeptNo"))})
Loop
Conn.Close()
Return EmpCol
End Function
Public Sub InsertEmployee(ByVal objEmp As clsEmployee)
Conn.Open()
Cmd = New SqlCommand()
Cmd.Connection = Conn
Cmd.CommandText = "Insert into Employee Values(@EmpNo,@EmpName,@Salary,@DeptNo)"
Cmd.Parameters.AddWithValue("@EmpNo", objEmp.EmpNo)
Cmd.Parameters.AddWithValue("@EmpName", objEmp.EmpName)
Cmd.Parameters.AddWithValue("@Salary", objEmp.Salary)
Cmd.Parameters.AddWithValue("@DeptNo", objEmp.DeptNo)
Cmd.ExecuteNonQuery()
Conn.Close()
End Sub
End Class
The class ‘clsEmployee’ is an entity class and the class ‘DataAccess’ defines methods for ‘Get All’ and ‘Insert’ operations.
Step 2: Open MainWindow.Xaml and write the following xaml code:
<Grid>
<DataGrid AutoGenerateColumns="False" Height="237"
HorizontalAlignment="Left" Margin="18,66,0,0" Name="dgEmp"
VerticalAlignment="Top" Width="466" ColumnWidth="*"
CellEditEnding="dgEmp_CellEditEnding"
SelectionChanged="dgEmp_SelectionChanged"
RowEditEnding="dgEmp_RowEditEnding">
<DataGrid.Columns>
<DataGridTextColumn Binding="{Binding EmpNo}" Header="EmpNo"></DataGridTextColumn>
<DataGridTextColumn Binding="{Binding EmpName}" Header="EmpName"></DataGridTextColumn>
<DataGridTextColumn Binding="{Binding Salary}" Header="Salary"></DataGridTextColumn>
<DataGridTextColumn Binding="{Binding DeptNo}" Header="DeptNo"></DataGridTextColumn>
</DataGrid.Columns>
</DataGrid>
<TextBlock Height="42" HorizontalAlignment="Left" Margin="18,15,0,0" Name="textBlock1" Text="Employee Information System" VerticalAlignment="Top" Width="462" TextAlignment="Center" FontSize="28" FontWeight="ExtraBold" />
</Grid>
The above DatGrid control defines events to be raised and columns bound with the Data class.
Step 3: In the MainWindow.Xaml.cs or .vb, write the following code in the Loaded event of the Windows. This code will fetch data in the DataGrid:
C#
private void Window_Loaded(object sender, RoutedEventArgs e)
{
objDs = new DataAccess();
dgEmp.ItemsSource = objDs.GetAllEmployee();
}
VB.NET (Converted Code)
Private Sub Window_Loaded(ByVal sender As Object, ByVal e As RoutedEventArgs)
objDs = New DataAccess()
dgEmp.ItemsSource = objDs.GetAllEmployee()
End Sub
Write the following code in the SelectionChanged Event which will return an object of the ‘clsEmployee’ class. Since we are going to click on the last row of the DataGrid to insert a new record, this object will return ‘null’.
C#
private void dgEmp_SelectionChanged(object sender, SelectionChangedEventArgs e)
{
objEmpToAdd = dgEmp.SelectedItem as clsEmployee;
}
VB.NET (Converted Code)
Private Sub dgEmp_SelectionChanged(ByVal sender As Object, ByVal e As SelectionChangedEventArgs)
objEmpToAdd = TryCast(dgEmp.SelectedItem, clsEmployee)
End Sub
Write the following code in ‘CellEditEnding’ event. This code will read the data entered in each cell. When you edit the cell in the DataGrid, the cell contains the ‘TextBox’ as its content, so to read every editable cell, the code reads the Content type in that cell using ‘GetCellContent()’ method, for a selected row index.
From this ‘TextBox’ the valued entered is read and assigned to the ‘objEmpToAdd’ object of the ‘clsEmployee’ class.
C#
private void dgEmp_CellEditEnding(object sender, DataGridCellEditEndingEventArgs e)
{
try
{
FrameworkElement element_EmpNo = dgEmp.Columns[0].GetCellContent(e.Row);
if (element_EmpNo.GetType() == typeof(TextBox))
{
var eno = ((TextBox)element_EmpNo).Text;
objEmpToAdd.EmpNo = Convert.ToInt32(eno);
}
FrameworkElement element_EmpName = dgEmp.Columns[1].GetCellContent(e.Row);
if (element_EmpName.GetType() == typeof(TextBox))
{
var ename = ((TextBox)element_EmpName).Text;
objEmpToAdd.EmpName = ename;
}
FrameworkElement element_Salary = dgEmp.Columns[2].GetCellContent(e.Row);
if (element_Salary.GetType() == typeof(TextBox))
{
var salary = ((TextBox)element_Salary).Text;
objEmpToAdd.Salary = Convert.ToInt32(salary);
}
FrameworkElement element_DeptNo = dgEmp.Columns[3].GetCellContent(e.Row);
if (element_DeptNo.GetType() == typeof(TextBox))
{
var dno = ((TextBox)element_DeptNo).Text;
objEmpToAdd.DeptNo = Convert.ToInt32(dno);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
VB.NET
Private Sub dgEmp_CellEditEnding(ByVal sender As Object, ByVal e As DataGridCellEditEndingEventArgs)
Try
Dim element_EmpNo As FrameworkElement = dgEmp.Columns(0).GetCellContent(e.Row)
If element_EmpNo.GetType() Is GetType(TextBox) Then
Dim eno = (CType(element_EmpNo, TextBox)).Text
objEmpToAdd.EmpNo = Convert.ToInt32(eno)
End If
Dim element_EmpName As FrameworkElement = dgEmp.Columns(1).GetCellContent(e.Row)
If element_EmpName.GetType() Is GetType(TextBox) Then
Dim ename = (CType(element_EmpName, TextBox)).Text
objEmpToAdd.EmpName = ename
End If
Dim element_Salary As FrameworkElement = dgEmp.Columns(2).GetCellContent(e.Row)
If element_Salary.GetType() Is GetType(TextBox) Then
Dim salary = (CType(element_Salary, TextBox)).Text
objEmpToAdd.Salary = Convert.ToInt32(salary)
End If
Dim element_DeptNo As FrameworkElement = dgEmp.Columns(3).GetCellContent(e.Row)
If element_DeptNo.GetType() Is GetType(TextBox) Then
Dim dno = (CType(element_DeptNo, TextBox)).Text
objEmpToAdd.DeptNo = Convert.ToInt32(dno)
End If
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
End Sub
Write the following code in ‘RowEditEnding’ event which will be fired when all cells are edited. This code uses a MessageBox to ask the user if the entered row is to be inserted. If the user selects ‘Yes’, then the object is inserted in the database table.
C#
private void dgEmp_RowEditEnding(object sender, DataGridRowEditEndingEventArgs e)
{
try
{
var Res = MessageBox.Show("Do you want to Create this new entry", "Confirm", MessageBoxButton.YesNo);
if (Res == MessageBoxResult.Yes)
{
objDs.InsertEmployee(objEmpToAdd);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
VB.NET
Private Sub dgEmp_RowEditEnding(ByVal sender As Object, ByVal e As DataGridRowEditEndingEventArgs)
Try
Dim Res = MessageBox.Show("Do you want to Create this new entry", "Confirm", MessageBoxButton.YesNo)
If Res = MessageBoxResult.Yes Then
objDs.InsertEmployee(objEmpToAdd)
End If
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
End Sub
Step 4: Run the application. Enter the data in the last row of the DataGrid and once you enter data in all the cells, a MessageBox will be displayed. If the user selects yes, the row will be added in the database table.
Note, the Red marked represents the row added. Click on yes and the row will be inserted.
Using the technique shown in this article, you can insert rows in the database using the WPF DataGrid. If you have implemented an alternate or a better way to insert rows using the WPF DataGrid, I would love to hear that. Use the comments section to share your views. You may also want to read my previous article WPF DataGrid Control - Performing Update and Delete Operations
The entire source code of this article can be downloaded over here
This article has been editorially reviewed by Suprotim Agarwal.
C# and .NET have been around for a very long time, but their constant growth means there’s always more to learn.
We at DotNetCurry are very excited to announce The Absolutely Awesome Book on C# and .NET. This is a 500 pages concise technical eBook available in PDF, ePub (iPad), and Mobi (Kindle).
Organized around concepts, this Book aims to provide a concise, yet solid foundation in C# and .NET, covering C# 6.0, C# 7.0 and .NET Core, with chapters on the latest .NET Core 3.0, .NET Standard and C# 8.0 (final release) too. Use these concepts to deepen your existing knowledge of C# and .NET, to have a solid grasp of the latest in C# and .NET OR to crack your next .NET Interview.
Click here to Explore the Table of Contents or Download Sample Chapters!
Was this article worth reading? Share it with fellow developers too. Thanks!
Mahesh Sabnis is a DotNetCurry author and a Microsoft MVP having over two decades of experience in IT education and development. He is a Microsoft Certified Trainer (MCT) since 2005 and has conducted various Corporate Training programs for .NET Technologies (all versions), and Front-end technologies like Angular and React. Follow him on twitter @
maheshdotnet or connect with him on
LinkedIn