Execute DataBase Query for Inserted, Modified, & Deleted Rows only with the help of DataSet

Introduction

Here, what I am trying to do is that there will be a textbox in which the User will enter a unique CustomerId and then when he’ll click the Add button, the value should be displayed in a GridView (The GridView will be displaying the existing Customers for that User which, will be loaded from the database).

Similarly, if he wishes, he can add multiple Customers which will be displayed in the GridView.

 

And, he can edit the existing Customer Details also.

Finally, when he’ll click the update button the database query should be executed for only the Added, Modified, & Deleted rows.

Solution

When the Page is loaded for the very first time I will get the Customers details for the current User in a DataSet (which will be the source of the GridView) which I have written the DataSet in the ViewState of that page.


ViewState["customertData"] = ds;

Now whenever user is adding a new Customer by giving its Customer Id I will do the following


DataSet ds = new DataSet();
DataTable dt;

if (ViewState["customertData"] == null)  //If the ViewState is not defined
{
	dt = new DataTable();
	DataColumn uniqueID = new DataColumn("uniqueID");
	DataColumn customerId = new DataColumn("customerId");
	DataColumn excluded = new DataColumn("IsExcluded");
	dt.Columns.Add(customerId);
	dt.Columns.Add(excluded);
	ds.Tables.Add(dt);
}
else
{
	ds = ViewState["customertData"] as DataSet;
	dt = ds.Tables[0];
}

DataRow dRow = dt.NewRow();
dRow["uniqueID"] = System.Guid.NewGuid();
dRow["customerId"] = txtcustomerId.Text;
dRow["IsExcluded"] = false;

dt.Rows.Add(dRow);
grdDsClient.DataSource = ds;
grdDsClient.DataBind();

//store the current changes in the ViewState
ViewState["customertData"] = ds;
txtClientNo.Text = String.Empty;

Similarly In the RowUpdating & RowDeleting event of the gridView, I am modyfying the DataSet value with the updated records & accordingly writing it into the ViewState. NOTE: To Delete a row from the DataSet I am using the DataRow.Delete() method & not DataRow.Remove() OR DataRow.RemoveAt() methods.

RowDeleting 


protected void grdDsClient_RowDeleting(object sender, GridViewDeleteEventArgs e)
{
  DataSet ds = ViewState["customertData"] as DataSet;
  //To get the deleted row in the DataSet.GetChanges method we need to use Delete method
  ds.Tables[0].Rows[e.RowIndex].Delete();

  grdDsClient.DataSource = ds;
  grdDsClient.DataBind();
  ViewState["customertData"] = ds;
  hdnAllowRedirect.Value = "false";
}

 

RowUpdating 


protected void grdDsClient_RowUpdating(object sender, GridViewUpdateEventArgs e)
{
  string key = e.Keys["uniqueID"].ToString();
  string dsClient = ((TextBox)(grdDsClient.SelectedRow.FindControl("txtCustomerId"))).Text;
  DataSet ds = ViewState["customertData"] as DataSet;

  DataView dv = ds.Tables[0].DefaultView;
  dv.Sort = "customerId";
  DataRowView[] drv = dv.FindRows(dsClient);

  if (drv.Count() > 0)
  {
	foreach(DataRowView dvTemp in drv)
	{
	  if (dvTemp["uniqueID"].ToString() != key)
	  {
		lblMessage.Text = "Duplicate value for the field DS-Client# is not allowed.";
		lblMessage.ForeColor = System.Drawing.Color.FromArgb(170, 46, 46);//red
		return;
	  }
	}
  }

  dv.Sort = "uniqueID";
  drv = dv.FindRows(key);
  drv = dv.FindRows(key);

  if (drv.Count() > 0)
  {
	drv[0]["customerId"] = ((TextBox)(grdDsClient.SelectedRow.FindControl("txtCustomerId"))).Text;
	drv[0]["IsExcluded"] = ((CheckBox)(grdDsClient.SelectedRow.FindControl("chkIsExcluded"))).Checked;
  }

  grdDsClient.EditIndex = -1;
  grdDsClient.SelectedIndex = -1;
  grdDsClient.DataSource = ds;
  grdDsClient.DataBind();

  ViewState["customertData"] = ds;
  hdnAllowRedirect.Value = "false";
}

Now finally, when the user clicks the Update Button, the application will fire the database query for only those rows of the DataSet which has been either added, modified, or deleted.


try
{
	foreach (DataRow dr in dsClient.Tables[0].Rows)
	{
		if (dr.RowState == DataRowState.Deleted)
		{
			cmd = new SqlCommand();
			cmd.CommandText = "Update [ManageCustomer] SET IsDeleted = 1, ModifiedBy = @ModifiedByDel, ModifiedOn = @ModifiedOnDel WHERE customerId = @customerIdDel";
			cmd.Connection = con;
			cmd.Transaction = tran;
			cmd.Parameters.Add("@customerIdDel", SqlDbType.VarChar, 255).Value = dr["customerId", DataRowVersion.Original];
			cmd.Parameters.Add("@ModifiedByDel", SqlDbType.UniqueIdentifier).Value = varProviderUserKey;
			cmd.Parameters.Add("@ModifiedOnDel", SqlDbType.DateTime).Value = DateTime.Now;cmd.ExecuteNonQuery();RecordUserActivity(varProviderUserKey, "VX", dr["customerId",DataRowVersion.Original].ToString(), con, tran);
		}
		else if (dr.RowState == DataRowState.Added)
		{
			cmd = new SqlCommand();
			cmd.CommandText = "INSERT INTO [ManageCustomer] (uniqueID, customerId, VarID, [IsExcluded], [AddedBy], [AddedOn]) VALUES (@uniqueIDAdd, @customerIdAdd, @VarIDAdd, @IsExcludedAdd, @AddedByAdd, @AddedOnAdd)";
			cmd.Connection = con;
			cmd.Transaction = tran;
			cmd.Parameters.Add(new SqlParameter("@uniqueIDAdd", dr["uniqueID"]));
			cmd.Parameters.Add(new SqlParameter("@customerIdAdd", dr["customerId"]));
			cmd.Parameters.Add(new SqlParameter("@VarIDAdd", varProviderUserKey.ToString()));
			cmd.Parameters.Add(new SqlParameter("@IsExcludedAdd", dr["IsExcluded"]));
			cmd.Parameters.Add(new SqlParameter("@AddedByAdd", varProviderUserKey.ToString()));
			cmd.Parameters.Add(new SqlParameter("@AddedOnAdd", DateTime.Now));cmd.ExecuteNonQuery();
			RecordUserActivity(varProviderUserKey, "VA", dr["customerId"].ToString(), con, tran);
		}
		else if (dr.RowState == DataRowState.Modified)
		{
			cmd = new SqlCommand();
			cmd.CommandText = "Update [ManageCustomer] SET customerId = @customerIdNew, IsExcluded = @IsExcludedNew, ModifiedBy = @ModifiedByUpdt, ModifiedOn = @ModifiedOnUpdt WHERE uniqueID = @uniqueIDUpdt";
			cmd.Connection = con;
			cmd.Transaction = tran;
			cmd.Parameters.Add("@customerIdNew", SqlDbType.VarChar, 255).Value = dr["customerId", DataRowVersion.Current];
			cmd.Parameters.Add("@IsExcludedNew", SqlDbType.Bit).Value = dr["IsExcluded"];
			cmd.Parameters.Add("@ModifiedByUpdt", SqlDbType.UniqueIdentifier).Value = varProviderUserKey;
			cmd.Parameters.Add("@ModifiedOnUpdt", SqlDbType.DateTime).Value = DateTime.Now;
			cmd.Parameters.Add("@uniqueIDUpdt", SqlDbType.UniqueIdentifier).Value = dr["uniqueID", DataRowVersion.Original];

			cmd.ExecuteNonQuery();

			if(dr["IsExcluded",DataRowVersion.Original] != dr["IsExcluded",DataRowVersion.Current] &&
			((bool)(dr["IsExcluded",DataRowVersion.Current])) == true)
				RecordUserActivity(varProviderUserKey, "VE", dr["customerId"].ToString(), con, tran);
		}
	}
	tran.Commit();
}
catch (Exception ex)
{
	tran.Rollback();
}
finally
{
	con.Close();
}

 

Explanation

Here, when the page is loaded for the very first time, I have loaded the information from the DataBase into a DataSet. This DataSet is then getting added in the ViewState of the page. Now whenever the User is doing some modifications, it will get updated in the ViewState of that Page only. Once the User clicks the Update button, I am checking, inside a seperate function(which is being called from the click event of the Update button), the RowState of each DataRow from the DataSet. More info regarding the enum DataRowState can be found here.

So for the modified rows only I am firing the database query. I have also kept the entire queries in the same transaction tran, so that I can Roll Back all the changes for any errors.

Finally in the click event of the Update Button, I am calling the DataBase.AcceptChanges() method to clear the RowState of all the rows of the DataBase before adding it again to the ViewState of the page.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s