ARTICLE

WPF Database Communication Adding new Record to the Database

Posted by Munir Shaikh in Articles | XAML with C# on September 06, 2007

Tags: C#, Database Connectivity using XAML, WPF, XAML

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.

Tweet
23855
0

Reader Level: Advanced

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.ColumnDefinitions>

</Grid.ColumnDefinitions>

<Grid.RowDefinitions>

</Grid.RowDefinitions>

<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>

<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.