Kiss your databse only once!!!

This article explains about how to update the multiple records in the data controls to the server in a single transaction.

 

Introduction

In this article, I have explained about one of the efficient ways to update the maximum number of records in the data control to the database with high performance approach. I have approached the XML to manipulate the DML operations with the database from the front end application. We will see the approach with an example.

Background

I believe that there lot of ways to update the content from the data control to the database in the back end. We can get the key fields which we want to do the data manipulation in the back end from the front end application. Every record in the record set will be send to the database as a single transaction. The record fields will be send as parameter to the stored procedure. I realize that this will be became too cost. Because if you want manipulate 1000 records in the data controls then hitting 1000 times to the database will be concern about performance.

There is one other way like write the DML statement in the application itself as hard code. If you want to update 100 records in the data controls then write the 100 DML statements with the corresponding ID. Then it will be executed in the SqlCommand class.

As we know that the second approach is horrible. Because it will make network traffic when you send the bulk of DML statement and it consumes lot of network bandwidth. It takes lot of time to execute in the sql server database. It uses new execution plan for the execution of the query.

I have implemented a method like sending the XML tags as a parameter to the stored procedure. It will process in the back end and it will return the status of the DML operation to the actual program. I think this will improve the performance of our application.

Data Controls

Normally we use the server side data controls for display the data available in the back end. There we may have several operations like insertion, updation and deletion. When we do the same operation for the group of records, i believe we iterate the data controls and take the every record for the DML operations in the back end.

If we have more than 1 lakhs record in the data controls then we do the operation in the back end. There we hit the database for every record. I think that will give the additional overhead when many number of users connected in the server.

Here I would like to share a method which will help us to approach the database only one. Yes, we can get the data controls data as xml. We can generate in our own way. Suppose, if you want to delete the several records then only key column will be enough. So you can generate the XML with only key column field.

I have taken Northwind database for an example to implement this. I have displayed the Customer details from the Customers table. Here I have only one operation like Delete. The users have to select the record using the check box and then need to click Delete link.

Normally we iterate the data controls and selected row will be deleted every time in the database. If I want to delete 100 rows then we have to send the 100 times to the database. This is the place we have to concentrate to minimize the database hits. First I have written the stored procedure in the Sql server. 
 

 

USE [Northwind] GO    IF OBJECT_ID('Pr_DeleteCustomers') IS NOT NULL BEGIN    DROP PROCEDURE Pr_DeleteCustomers    PRINT '<< Pr_DeleteCustomers procedure dropped >>' END GO   CREATE PROCEDURE Pr_DeleteCustomers @Customers XML AS BEGIN /*  Purpose    : Delete the n number of customers using the XML in the Customer Table.                Here i have created the one more column as Active to update the status.  Input      : Get the Customers ID in the XML format from the front-end application.  Output     : Return 1 as success, and -1 as failure  Created On : July 28, 2009  Created By : Erode Senthilkumar   ************************************************************************** ----------------------------------  Modification History --------------------------------------- ************************************************************************** S.No      Name                  Changes ************************************************************************** 1.      Erode Senthilkumar      Initial Version ************************************************************************** */      SET NOCOUNT ON    SET XACT_ABORT ON      DECLARE @hDoc INT    EXEC sp_xml_preparedocument @hDoc OUTPUT,@Customers      UPDATE Customers SET cStatus='D' WHERE CustomerID IN    (SELECT ID FROM OPENXML(@hDoc,'/Customers/Customer',2) WITH (ID VARCHAR(10)))        IF @@ERROR <> 0    BEGIN         RETURN -1    END    ELSE    BEGIN       RETURN 1    END END GO   IF OBJECT_ID('Pr_DeleteCustomers') IS NOT NULL BEGIN     PRINT '<< Pr_DeleteCustomers procedure created >>' END GO

 

In the above stored procedure, I have passed the xml content with the customer ID. It may contain one or more. Here I have not deleted the record. Instead of that I have changed the status of the customer like inactive. I have written like 'IN' statement to handle the multiple customer id. As you know the output parameter will return the result on the same variable. I have generated the XML data in the following format.

 

<Customers>

  <Customer>

    <ID>CONSH</ID>                                 

  </Customer>

  <Customer>

    <ID>DRACD</ID>   

  </Customer>

  <Customer>

    <ID>DUMON</ID>                               

  </Customer>                        

</Customers>

The system stored procedures in the sql server will return the tags count in the integer value. OPENXML function will return the result set. We have to give the /Customers/Customer to get the ID tag.

After parse the XML data the result set will be like this.

CONSH

DRACD

DUMON

In C# code in the code behind of the application, I have iterated the grid view data control. If the particular row is selected then it will be considered as delete operation. Then I have framed the XML data content. It will be sending the stored procedure as parameter.

 

// Code for update the delete status in the grid view.     protected void lbtnDelete_Click(object sender, EventArgs e)     {         try         {             int Opt = 0;             string XmlData = "";             foreach (GridViewRow gv in gvCustomers.Rows)             {                 CheckBox chkDelete = (CheckBox)gv.FindControl("chkDelete");                 if (chkDelete.Checked)                 {                     Label lblCustID = (Label)gv.FindControl("lblCusID");                     XmlData += "" + lblCustID.Text.Trim() + "";                     Opt = 1;                 }             }             if (Opt == 0)             {                 dvStatusMsg.InnerHtml = "Please select customer(s)";             }             else             {                 XmlData += "";                 BusinessLogics oBusiLogics = new BusinessLogics();                 int iStatus = oBusiLogics.DeleteCustomerDetails(XmlData);                 if (iStatus != 1)                 {                     dvStatusMsg.InnerHtml = "Customer(s) Deleted Successfully";                     LoadCustomer();                 }                 else                 {                     dvStatusMsg.InnerHtml = "Error in deletion";                 }             }         }         catch (Exception oEx)         {             divMessage.InnerHtml = oEx.Message;         }     } }

XML in Sql server

I have passed the XML data content from the front end application to the back end. There I have to take the records using the Sql server system stored procedures. Here I have written the stored procedure which will extract the Customer ID from the XML data which i have sent from the application. 

I have slightly modified the table Customers in the Northwind database. I have added a column like Status which will do the soft delete operation. All the active records will be noted as 'A' and deleted records will be updated as 'D'. Which retrieving we have to look only 'A' records. I will update all the customers' records for delete operation in the application. Using the Sql server system variable @@Error if there is no error then I will send the status to the application.

Similarly we can do the insert, update operation. For an example we want to insert multiple new records then we can send it as xml content to the back end then we can do in the similar way. Suppose if you use the page like product order with the multiple entry in the grid then you can insert only once in the database.

Conclusion

I hope that this will be pretty good idea to approach the database DML operation for the server side data controls. This will help you to avoid the back end hits. It may be one of way to tune your applications.