WPF DataGrid Control – Insert New Rows in Database

Posted by: Mahesh Sabnis , on 9/2/2010, in Category WPF
Views: 89994
Abstract: In this article I will explain how to perform an Insert operation in WPF with DataGrid control.
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.
EIS
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
Give a +1 to this article if you think it was well written. Thanks!
Recommended Articles
Mahesh is having 10 years 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). Follow him on twitter @maheshdotnet


Page copy protected against web site content infringement by Copyscape


User Feedback
Comment posted by Tom on Wednesday, February 15, 2012 4:32 AM
Hi Mahesh.
I followed this article. When I edit cell, the CellEditEnding event throws an exception "Object reference not set to an instance of an object" in the line "objEmpToAdd.EmpName = ename". The same for others properties in clsEmployee class.
I found out this is because the clsEmployee class when debugging but I don't know why.
I download your source code and run it, the problem happens again on your source code but just on the first time I run it.
Hope you can give me some advises.
Thank you.
// Sorry for my bad English
Comment posted by nishu on Thursday, September 12, 2013 2:18 AM
XAML
___________________________________________________________________________________________

<Grid>
        <Label Content="Name" Height="28" HorizontalAlignment="Left" Margin="50,54,0,0" Name="lblName" VerticalAlignment="Top" />
        <TextBox Height="23" HorizontalAlignment="Left" Margin="169,54,0,0" Name="txtName" VerticalAlignment="Top" Width="120" />
        
        <Label Content="Emp ID" Height="28" HorizontalAlignment="Left" Margin="50,88,0,0" Name="lblEmpId" VerticalAlignment="Top" />
        <TextBox Height="23" HorizontalAlignment="Left" Margin="169,83,0,0" Name="txtEmpId" VerticalAlignment="Top" Width="120" />
        
        <Label Content="Address" Height="28" HorizontalAlignment="Left" Margin="50,122,0,0" Name="lblAddress" VerticalAlignment="Top" />
        <TextBox Height="23" HorizontalAlignment="Left" Margin="169,122,0,0" Name="txtAddress" VerticalAlignment="Top" Width="120" />
        
        <Label Content="Email ID" Height="28" HorizontalAlignment="Left" Margin="52,156,0,0" Name="lblEmailId" VerticalAlignment="Top" />
        <TextBox Height="23" HorizontalAlignment="Left" Margin="169,156,0,0" Name="txtEmailID" VerticalAlignment="Top" Width="120" />

        <Button Content="Save" Height="23" HorizontalAlignment="Left" Margin="362,78,0,0" Name="btnSave" VerticalAlignment="Top" Width="75" Click="btnSave_Click" />
        
        <Button Content="Delete" Height="23" HorizontalAlignment="Left" Margin="362,127,0,0" Name="btnDelete" VerticalAlignment="Top" Width="75" Click="btnDelete_Click" />                
        
        <DataGrid AutoGenerateColumns="False" Height="182" HorizontalAlignment="Left" Margin="34,197,0,0" Name="dgEmpDetails" VerticalAlignment="Top" Width="421">
            <DataGrid.Columns>
                <DataGridTextColumn Binding="{Binding NAME}" Header="Employee Name" ></DataGridTextColumn>
                <DataGridTextColumn Binding="{Binding EMP_ID}" Header="Employee ID"></DataGridTextColumn>
                <DataGridTextColumn Binding="{Binding ADDRESS}" Header="Employee Address" ></DataGridTextColumn>
                <DataGridTextColumn Binding="{Binding EMAILID}" Header="Employee Email ID"></DataGridTextColumn>
            </DataGrid.Columns>
        </DataGrid>
        <Label Height="28" HorizontalAlignment="Left" Margin="163,12,0,0" Name="lblMessage" VerticalAlignment="Top" Width="237" Foreground="#FFF91E1E" />
    </Grid>



______________________________________________________________________________________________________________________________________________________________________________________


C#
___________________________________________________________________________________________

public partial class MainWindow : Window
    {
        public MainWindow()
        {
            InitializeComponent();
            FillDataGrid();
        }

        private void btnSave_Click(object sender, RoutedEventArgs e)
        {
            if (txtName.Text == "" || txtEmpId.Text == "" || txtAddress.Text == "" || txtEmailID.Text == "")
            {
                lblMessage.Content = "Please enter all the details";
                return;
            }

             string ConString = Properties.Settings.Default.StoreDBConnString;
            string CmdString = string.Empty;
            using (SqlConnection con = new SqlConnection(ConString))
            {
                SqlCommand cmd = new SqlCommand();
                cmd.CommandText = "INSERT INTO EMP_DETAILS(NAME,EMP_ID,ADDRESS,EMAILID) VALUES (" + "'" + txtName.Text + "','" + txtEmpId.Text + "','" + txtAddress.Text + "','" + txtEmailID.Text + "')";
                cmd.CommandType = CommandType.Text;
                cmd.Connection = con;
                con.Open();
                cmd.ExecuteNonQuery();                            
            }

            FillDataGrid();

            txtName.Text = "";
            txtEmpId.Text ="";
            txtAddress.Text = "";
            txtEmailID.Text = "";
            lblMessage.Content = "Record saved";
        }

        private void FillDataGrid()
        {
            string ConString = Properties.Settings.Default.StoreDBConnString;
            string CmdString = string.Empty;
            using (SqlConnection con = new SqlConnection(ConString))
            {
                CmdString = "SELECT * FROM EMP_DETAILS ORDER BY EMP_ID";
                SqlCommand cmd = new SqlCommand(CmdString, con);
                SqlDataAdapter sda = new SqlDataAdapter(cmd);
                DataTable dt = new DataTable("Tables");
                sda.Fill(dt);
                dgEmpDetails.ItemsSource= dt.DefaultView;              
            }                      
        }

        private void btnDelete_Click(object sender, RoutedEventArgs e)
        {
            if (dgEmpDetails.SelectedValue != null)
            {
                string ConString = Properties.Settings.Default.StoreDBConnString;
                string CmdString = string.Empty;
                using (SqlConnection con = new SqlConnection(ConString))
                {
                    SqlCommand cmd = new SqlCommand();
                    cmd.CommandText = "DELETE FROM EMP_DETAILS WHERE EMP_ID=" + ((System.Data.DataRowView)((dgEmpDetails.Items).CurrentItem)).Row.ItemArray[1];
                    cmd.CommandType = CommandType.Text;
                    cmd.Connection = con;
                    con.Open();
                    cmd.ExecuteNonQuery();
                }
                lblMessage.Content = "Record deleted";
                FillDataGrid();
            }
            else
            {
                lblMessage.Content = "Please select a Record to delete";
            }  
        }
    }

Comment posted by Jell on Thursday, February 20, 2014 12:18 PM
I don't know how to thank you enough!
Comment posted by DEVI on Monday, September 8, 2014 2:36 AM
I have a datagrid with filled items which filled by another datagrid.
My requirement is save the datagrid values into db.
Data base is mysql.
wpf application.
Thanks in advance

Post your comment
Name:  
E-mail: (Will not be displayed)
Comment:
Insert Cancel