Thursday 6 February 2014

CRUD Operation using XSD Dataset in C#

In this article I am going to explain how to use XSD Dataset to read and write data in xml format using c# in win forms.

With Ado.NET you can fill a Dataset from an XML stream or document. You can use the XML stream or document to supply the Dataset either data, schema information, or both. The information supplied from the XML stream or document can be combined with existing data or schema information already present in the DataSet.

The following example demonstrates how to create a Customer Entry Form using XSD dataset. We are saving customer data in xml file (Customer.xml) and also using linq to find the records of customer.

Before creating the following application, do the following steps:

·         Create a new Windows application using Microsoft Visual C#.

·         Design a Customer entry form as shown in below screenshot:

CustomerID field is read-only because it will be auto generated.

Add some default countries in the Country combobox, as given below:

Design a Customer Search form

·         Make Done button DialogResult property to OK and Cancel button DialogResult property to Cancel.

·         Name the textbox as txtCutomerId.

Create a property CustomerID and bind it to Textbox Text property. Code given below:

        public event PropertyChangedEventHandler PropertyChanged;

        private int customerId;

 

        public int CustomerId

        {

            get

            {

                return customerId;

            }

            set

            {

                customerId = value;

                InvokePropertyChanged(new PropertyChangedEventArgs("CustomerID"));

            }

        }

 

        public void InvokePropertyChanged(PropertyChangedEventArgs e)

        {

            PropertyChangedEventHandler handler = PropertyChanged;

            if (handler != null)

            {

                handler(this, e);

            }

        }

 

        private void Search_Load(object sender, EventArgs e)

        {

            txtCutomerId.DataBindings.Add("Text", this, "CustomerId");

        }

 

To prevent blank entry in CustomerID textbox, add the following code on Form Closing event of Search form:

private void Search_FormClosing(object sender, FormClosingEventArgs e)

{

            if (DialogResult == DialogResult.OK)

            {

                if (string.IsNullOrEmpty(txtCutomerId.Text.Trim()))

                {

                    MessageBox.Show("Please enter customer Id", "Search", MessageBoxButtons.OK, MessageBoxIcon.Error);

                    e.Cancel = true;

                }

            }

}

 

To add a dataset

·         Got to Project -> Add New Item.

·         Select Data from the Installed Templates.

·         Select DataSet -> Enter CustomerDS.xsd in Name textbox.

Add a New DataTable (Customer) in the dataset as shown in the below screenshot and set the CustomerID as primary key and its data type to System.Int32. Change the data type of column DateofBirth to DateTime.

 

Add the following methods in your Customer form:

This method (GetNewCustomerID)will always return a new CustomerID:

private int GetNewCustomerID()

{

       int max = dtCustomer.Rows.Count > 0 ? (from dt in dtCustomer

                                                   select dt.CustomerID).Max() + 1 : 1;

 

       return max;

}

 

The method (SetCustomer) set the customer data into the form fields. For this we have to pass the customer row as a parameter.

private void SetCustomer(CustomerDS.CustomerRow customer)

{

      txtCustomerID.Text = customer.CustomerID.ToString();

      txtName.Text = customer.Name;

      cmbCountry.Text = customer.Country;

      dtDob.Value = customer.DateofBirth;

      txtZipCode.Text = customer.ZipCode;

}

 

 

This method (ResetCustomer) reset the form fields after saving the data.

private void ResetCustomer()

{

      txtName.Text = string.Empty;

      cmbCountry.SelectedItem = null;

      dtDob.Value = DateTime.Now;

      txtZipCode.Text = string.Empty;

 

      int customerId = GetNewCustomerID();

      txtCustomerID.Text = customerId.ToString();

 }

 

Create a new object of Customer DataTable at the class level:

CustomerDS.CustomerDataTable dtCustomer = new CustomerDS.CustomerDataTable();

 

On form load read the xml file (Customer.xml) if exists and set the new customer id by using GetNewCustomerID method.

private void Customer_Load(object sender, EventArgs e)

{

      if (System.IO.File.Exists("Customer.xml"))

           dtCustomer.ReadXml("Customer.xml");

 

      int customerId = GetNewCustomerID();

      txtCustomerID.Text = customerId.ToString();

}

 

On the click of save button add the following code. It will add the customer if the CustomerID not exists otherwise update the data of customer on the basis of CustomerID.

private void btnSave_Click(object sender, EventArgs e)

{

            int customerId = int.Parse(txtCustomerID.Text);

 

            CustomerDS.CustomerRow row = dtCustomer.Rows.Find(customerId) as CustomerDS.CustomerRow;

            if (row == null)

            {

                row = dtCustomer.NewCustomerRow();

                row.CustomerID = customerId;

                dtCustomer.AddCustomerRow(row);

            }

            row.Name = txtName.Text.Trim();

            row.Country = cmbCountry.Text;

            row.DateofBirth = dtDob.Value;

            row.ZipCode = txtZipCode.Text.Trim();

 

            dtCustomer.AcceptChanges();

            dtCustomer.WriteXml("Customer.xml");

            MessageBox.Show("Record saved successfully", "Customer", MessageBoxButtons.OK, MessageBoxIcon.Information);

 

            ResetCustomer();

 }

 

To delete the customer record, add the following on the click of Delete button.

private void btnDelete_Click(object sender, EventArgs e)

{

            int customerId = int.Parse(txtCustomerID.Text);

 

            CustomerDS.CustomerRow row = dtCustomer.Rows.Find(customerId) as CustomerDS.CustomerRow;

            if (row != null)

            {

                row.Delete();

                dtCustomer.AcceptChanges();

                dtCustomer.WriteXml("Customer.xml");

                MessageBox.Show("Record deleted successfully", "Customer", MessageBoxButtons.OK, MessageBoxIcon.Information);

            }

 }

 

To search a customer, paste the given code on the click event of Search button:

private void btnSearch_Click(object sender, EventArgs e)

{

            Search search = new Search();

            DialogResult dr = search.ShowDialog();

            if (dr == DialogResult.OK)

            {

                CustomerDS.CustomerRow row = dtCustomer.Rows.Find(search.CustomerId) as CustomerDS.CustomerRow;

                if (row != null)

                {

                    SetCustomer(row);

                }

            }

 }

 

To find the first record from the DataTable:

private void btnFirst_Click(object sender, EventArgs e)

{

      CustomerDS.CustomerRow row = dtCustomer.FirstOrDefault();

      if (row != null)

      {

          SetCustomer(row);

      }

}

 

To find next record from the DataTable:

private void btnNext_Click(object sender, EventArgs e)

{

       int customerId = int.Parse(txtCustomerID.Text);

 

       CustomerDS.CustomerRow row = (from dt in dtCustomer

                                          where dt.CustomerID > customerId

                                          orderby dt.CustomerID ascending

                                          select dt).FirstOrDefault() as CustomerDS.CustomerRow;

        if (row != null)

        {

           SetCustomer(row);

        }

}

 

To find previous record from the DataTable:

private void btnPrevious_Click(object sender, EventArgs e)

{

            int customerId = int.Parse(txtCustomerID.Text);

 

            CustomerDS.CustomerRow row = (from dt in dtCustomer

                                          where dt.CustomerID < customerId

                                          orderby dt.CustomerID descending

                                          select dt).FirstOrDefault() as CustomerDS.CustomerRow;

            if (row != null)

            {

                SetCustomer(row);

            }

 }

 

To find last record from the DataTable:

private void btnLast_Click(object sender, EventArgs e)

{

       CustomerDS.CustomerRow row = dtCustomer.LastOrDefault();

       if (row != null)

       {

          SetCustomer(row);

       }

 }

 

Now run or debug this application to see the output. You need to add some records in order to test it properly:

 

After adding some records you can also search records on the basis of CustomerID by using Search form.

The data will be saving in the xml file (Customer.xml) in given format:

Thanks for reading this article. You can enter your valuable comments and suggestion to improve this article in the comment box.
In order to get the source code of this application, you can also enter your email id in the comment box.

No comments: