Kiss Your Databse Only Once!!!

Introduction

In this article, I have explained one of the efficient ways to update the maximum number of records in the data control to the database with a 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 that we want to do the data manipulation in the back end from the front-end application. Every record in the record set will be sent to the database as a single transaction. The record fields will be sent as parameters to the stored procedure. I realize that this will become too costly. Because if you want to manipulate 1000 records in the data controls then hitting 1000 times to the database will be a 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 the second approach is horrible. Because it will make network traffic when you send the bulk of the DML statement and it consumes a lot of network bandwidth. It takes a lot of time to execute in the SQL server database. It uses a 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 to 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 every record for the DML operations in the back end.

If we have more than 1 lakh records 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 a large number of users are connected to the server.

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

I have taken the Northwind database as 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 the Delete link.

Normally we iterate the data controls and the 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.

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 to inactive. I have written like 'IN' statement to handle the multiple customer IDs. 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 parsing the XML data the result set will be like this.

CONCH
DRACD
DUMON

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

// Code for updating 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

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 that 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'. When 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, and update operation. For 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 you use a page like product order with multiple entries in the grid then you can insert only once in the database.

Conclusion

I hope that this will be a 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 the ways to tune your applications.