ADO.NET DISCONNECTED MODEL

Disconnected Architecture

Data Adapter

Dataset

Steps to access data using Disconnected Model

Steps to perform manipulations in Disconnected Model

Important methods of DataRow:

1.  Contains(PKCOLvalue) : it used to search for a record based on the primary key value. If the record exists, it returns true otherwise false.

2.  Find(PKColValue) : it used to search for a record based on primary key column value. If the record exists, it returns row object otherwise -1 (error msg).

 Steps to search for a record in data table

1.   Define a reference variable for the datarow.

   Syntax:   DataRow dr;

2.   Search for a record in the datatable based on the given value and assign it to the DataRow object.

   Syntax: 

    dr= ds.Tables[TableName / TableIndex].Rows.Find(PKColValue);

3.   Retrieve the column values from the DataRow object and display them.

    syntax:                    textBoxname.Text=dr["columnname"/ColumnIndex];

Steps to insert the record into the datatable:

1.  Define the datarow object.

Syntax:   DataRow dr;

2.  Assign the new row of the dataset table to the row object

Syntax:

                        dr= datasetname.tablename.NewRow();

3.  Assign the values to the columns of the datarow.

Syntax:

dr[columnname/colindex]=value;

4.  Add the datarow to the dataset's table.

Syntax:

ds.tablename.Rows.Add(dr);

5.  Update the dataadapter with the dataset table.

Syntax:

daname.Update(datasetname,tablename);

Steps to Update the record

1.          Define the datarow object.

Syntax:   DataRow dr;

2.          Assign the row which has to modified to the datarow object.

    Syntax:   dr= ds.Tables[TableName / TableIndex].Rows.Find(pkcolvalue);

3.          Call the BeginEdit() on the datarow object.

  Syntax:   dr.BeginEdit();

4.          Assign the new values for the columns which have to be modified.

     Syntax:   dr[colname/colindex]=new value;

5.          Call the EndEdit() on the datarow.

      Syntax:   dr.EndEdit()

6.          Call Update() on the dataadpater to update these changes in the database table.

        Syntax:   da.Update(dsname,tablename);

Steps to delete a record

1.  Call the delete() on the record which has to be deleted.

      Syntax:   ds.Tables[TableName / TableIndex].Rows.Find(pkcolvalue).Delete();

2.  Call the Update() on the dataadapter to update the changes in the database table.

      Syntax:   da.Update(dsname,tablename);

Problem Scenario

E-Cognizance Mobile Company is a global mobile manufacturer, whose sales run in tens of thousands handsets every day, globally. They are providing a default warranty on the phone for one year. Faults of these handsets are repaired by the manufacturer's Authorized Service Vendor (ASV) in cities.

The ASV of the E-Cognizance Mobile Company makes the claim for repair and submits for review. He can also check for the status of the claims based on Claim ID and can update / delete the claim details, if the Claim Status is Submitted. 

Following fields are required to work with the Claim Details:

Claim ID - int, 

Model number - String, 

Model Type ('Basic' / 'Smartphone' / 'Business') - String, 

IMEI number (a unique 16-digit number of the mobile phone) - long, 

Part Cost - double,  Labor Cost - double,  and Status (Submitted / Accepted / Rejected ) - String.

Approach to solve the problem

1.     Create a database table to maintain the Claim Details.

2.     Use disconnected model as ASV is the only person who can add, edit, view and delete the claim details from the database.

3.     Retrieve all the Claim Details from database and store it in a Dataset for easy access and to perform maninpulations.

4.     When the Claim ID is entered and click on Find button, the claim details should be displayed in corresponding controls.

5.     When new claim details are submitted, it should generate a unique Claim ID and its status should be updated to Submitted.

6.     Editing and deleting the claim details from database should be done based on the Claim ID.

Solution

using System.Data;

using System.Data.SqlClient;  using System.Configuration; 

      

      SqlDataAdatper da;

      DataSet ds;

Page_Load():

      SqlConnection connection = new

SqlConnection(ConfigurationManager.ConnectionStrings["DBCon"].ConnectionString()); adapter = new SqlDataAdapter("Select * from tblClaims", connection);                 

ds = new DataSet();                 

adapter.Fill(ds);

      ds.Tables[0].Constraints.Add("pk_depid", ds.Tables[0].Columns[0], true);

      SqlCommandBuilder cmb = new SqlCommandBuilder(adapter);

btnFind_Click

            if (ds.Tables[0].Rows.Contains(txtClaimId.Text))

            {

                DataRow row = ds.Tables[0].Rows.Find(txtClaimId.Text);

                txtModelNo.Text = row[1].ToString();

                txtIMEI.Text = row[2].ToString();

                -------------

                -------------

            } 

            else

            {

                txtModelNo.Text = "";

                txtIMEI.Text = "";

          lblStatus.Text = "No Details found";

            }

btnAddNew_Click

                  txtModelNo.Text = "";

                  txtIMEI.Text = "";

                  int lastRowIndex = ds.Tables[0].Rows.Count - 1;      

                  int newClaimId = (int)ds.Tables[0].Rows[lastRowIndex][0] + 1;

                  txtClaimId.Text = newClaimId.ToString();

                  txtClaimId.ReadOnly = true;

                   

btnSave_Click    

               txtClaimId.ReadOnly=false;           

               DataRow row = ds.Tables[0].NewRow();

         row[0] = this.txtClaimId.Text;                                row[1] = this.txtModelNo.Text;                              row[2] = this.txtIMEI.Text;                                    ds.Tables[0].Rows.Add(row);                                adapter.Update(ds);

               lblStatus.Text = "Details are saved successfully";

btnModify_Click

      DataRow row = ds.Tables[0].Rows.Find(this.txtClaimId.Text);                   row.BeginEdit();             

      row[1] = this.txtModelNo.Text;             

      row[2] = this.txtIMEI.Text;            

      row.EndEdit();             

      adapter.Update(ds);

            lblStatus.Text = "Details are modified successfully";

        

btnRemove_Click

            ds.Tables[0].Rows.Find(this.txtClaimId.Text).Delete(); 

            adapter.Update(ds);

      lblStatus.Text = "Details are removed successfully";                     this.txtClaimId.Text = ""; 

      this.txtModelNo.Text = "";

            this.txtIMEI.Text = "";

        }

Explanation about the solution

Step 1: Include all the required namespaces

Step 2: Store the connection string in web.config file because all these actions need to connect to the same database.

Step 3: DataAdapter and DataSet are declared as Class level members as these are required for each CRUD operation.

Step 4: As DataAdapter is defined with only Select Command, we can’t perform manipulations. So, CommandBuilder object is used to generate other commands to do manipulations. Primary key is defined before generating the CommandBuilder object, to generate all Commands.