WPF Database Communication Adding new Record to the Database

WPF Database communication Adding new record to the database (XAML-MS-Access), I have a XAML form with FirstName, LastName, Email & Contact fields in it on click of submit button it will first check if the same email address is exists in the table else it will add new record I have made use of OleDbTransaction to perform add functionality.

In the first WPF tutorial I have spoken much about Data Binding.

 

http://www.c-sharpcorner.com/UploadFile/munnamax/Databinding08232007013021
AM/Databinding.aspx?ArticleID=ce6c61df-e439-4c25-9295-443595a59850


In the second tutorial I have spoken little about how we can file menu.

http://www.c-sharpcorner.com/UploadFile/munnamax/WPFMenuItem09032007085818
AM/WPFMenuItem.aspx?ArticleID=4307094e-45dc-4eae-8e2f-aebc0d10bc6d


Here I am going to show how you can create new records using XAML & MS-Access.

I am having database table as below in ms-access.

 

"emp"  table

 

ID

Autonumber

FirstName

Text

LastName

Text

Email

Text

Contact 

Text

 

 

 

 

 

 

 

I have taken file called as "Contact.xaml" code as below

 

Contact.xaml:

 

<Page x:Class="AddressBook.Contact" xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"

xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"

Title="Contact">   

<Grid Name ="contactGrid" VerticalAlignment="Center" HorizontalAlignment= "Center">

    <Grid.ColumnDefinitions>

        <ColumnDefinition Width="200"/>

        <ColumnDefinition Width="200"/>

    </Grid.ColumnDefinitions>

    <Grid.RowDefinitions>

        <RowDefinition Height="30"/>

        <RowDefinition Height="38"/>

        <RowDefinition Height="36"/>

        <RowDefinition Height="46"/>

        <RowDefinition Height="54"/>

        <RowDefinition Height="63"/>

        <RowDefinition Height="123"/>

    </Grid.RowDefinitions>

  

    <!-- Labels go here -->

    <TextBlock Width="200" Height="30"Grid.Column="0" Grid.Row="1">First Name

    </TextBlock>

    <TextBlock Grid.ColumnSpan="2" Margin="2,3,198,10" Grid.Row="2">Last Name

    </TextBlock>

    <TextBlock Grid.ColumnSpan="2" Margin="1,8,199,17" Grid.Row="3">Email

    </TextBlock>

    <TextBlock Grid.ColumnSpan="2" Margin="3,5,197,0" Grid.Row="4">Contact

    </TextBlock>

  

    <!-- Input fields -->

    <TextBox Name="txtFirstName"Width="200" Height="30"Grid.Column="1" Grid.Row="1"/>

    <TextBox Name="txtLastName" Width="200" Height="30"Grid.Column="1"Grid.Row="2"/>

    <TextBox Name="txtEmailAddress" Width="200" Height="30" Grid.Column="1" Grid.Row="3"/>

    <TextBox Grid.Column="1" Grid.Row="4" Name="txtContact"Width="200" Height="30"> </TextBox>

 

    <!-- Navigation buttons -->

    <Button Click="AddNewContact" Margin="69.5,6,55.5,0" Grid.Row="5" Height="24" VerticalAlignment="Top">Add New

    </Button>   

    <Button Grid.Row="5" Margin="13,9,0,31" Name="btnClear" Grid.Column="1" Click="ClearAll"  HorizontalAlignment="Left" Width="75" >Clear All

    </Button>

</Grid>

</Page>

 

On Submit button click I am calling backend event as "AddNewContact" so code behind is as follows:

 

"Contact.xaml.cs": 

 

using System;

using System.Collections.Generic;

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;

using System.Data.OleDb;

namespace AddressBook

{

    /// <summary>

    /// Interaction logic for Contact.xaml

    /// </summary>

    public partial class Contact : System.Windows.Controls.Page

    {

        public Contact()

        {

            InitializeComponent();

        }

        public void AddNewContact(object sender, RoutedEventArgs e)

        {

            if (txtFirstName.Text.Trim() == "")

            {

                txtFirstName.Focus();

                MessageBox.Show("First name is required!", "Validation");

            }

            else if (txtLastName.Text.Trim() == "")

            {

                txtLastName.Focus();

                MessageBox.Show("Last name is required!", "Validation");

            }

            else if (txtEmailAddress.Text.Trim() == "")

            {

                txtEmailAddress.Focus();

                MessageBox.Show("Email address is required!", "Validation");

            }

            else if (txtContact.Text == "")

            {

                txtContact.Focus();

                MessageBox.Show("Contact is required!", "Validation");

            }

            else

            {

                // if similar entry already exists else add new entry

                OleDbConnection oleCon = new OleDbConnection ("Provider=Microsoft.Jet.OLEDB.4.0;DataSource=E://AddressBook//AddressBook//EmployeeDb.mdb");

                string strSql = "SELECT Email FROM emp WHERE Email='" +txtEmailAddress.Text + "'";

                OleDbCommand oleComd = new OleDbCommand(strSql, oleCon);

                DataSet dtstIns = new DataSet();

                OleDbDataAdapter adpt = new OleDbDataAdapter();

                try

                {

                    oleCon.Open();

                    adpt.SelectCommand = oleComd;

                    adpt.Fill(dtstIns, "emp");

                    if (dtstIns.Tables[0].Rows.Count > 0)

                    {

                        //MessageBox.Show("Similar Contact already available", "Notification");

                        MessageBox.Show(dtstIns.Tables[0].Rows[0][0].ToString());

                    }

                    else

                    {

                        //call to insert record

                        int inval = doInsert();

                        if (inval > 0)

                        {

                            MessageBox.Show("Done", "Notification");

                        }

                    }

                }

                catch (Exception er)

                {

                    MessageBox.Show(er.Message, "Error");

                }

                finally

                {

                    oleCon.Close();

                }

            }

        }

 

        public void ClearAll(object sender, RoutedEventArgs e)

        {

            txtFirstName.Text = "";

            txtLastName.Text = "";

            txtEmailAddress.Text = "";

            txtContact.Text = "";

        }

 

        public int doInsert()

        {

            int retVal = 0;

            OleDbConnection myConnection = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E://AddressBook//AddressBook//EmployeeDb.mdb");

            myConnection.Open();

            OleDbCommand myCommand = myConnection.CreateCommand();

            OleDbTransaction myTrans;

            int retval = 0;

            // Start a local transaction

            myTrans = myConnection.BeginTransaction(IsolationLevel.ReadCommitted);

            // Assign transaction object for a pending local transaction

            myCommand.Connection = myConnection;

            myCommand.Transaction = myTrans;

            try

            {

                myCommand.CommandText = "INSERT INTO emp(FirstName,LastName,Email,Contact) VALUES('" + txtFirstName.Text + "','"+txtLastName.Text + "','" + txtEmailAddress.Text + "','" + txtContact.Text + "')";

                retval = myCommand.ExecuteNonQuery();

                myTrans.Commit();

                Console.WriteLine("Records added to database.");

            }

            catch (Exception e)

            {

                MessageBox.Show(e.Message);

                myTrans.Rollback();

            }

            finally

            {

                myConnection.Close();

            }

            return retval;

        }

    }

}

 

Note: Please mention "E://AddressBook//AddressBook//EmployeeDb.mdb" datasource as per your machine else you will not able to insert record to the database.