Bulk CRUD Operation using XQuery and Reflection in ASP.NET

• Download source code - 1.33 MB

Introduction

In database terms, CRUD stands for the four essential database operations: Create, Read, Update and Delete. Regardless of the diversity of applications, most real world applications are data centric, more precisely CRUD centric. At some point, a huge amount of data from the user has to be written to the database. Typically, this is done by the repetitive DB calls for DML. A much bigger set of data requires more expensive db calls in terms of memory and process and more risk of data inconsistency is incurred. If we can do the operation with in minimal db calls, we can achieve faster process, consistent data as well as better user experience. Keeping this in mind for one of my large data driven projects, I used xml/xquery for CRUD which I want to share with a simple example.

Using XML for CRUD?

XML use is widespread across modern information systems in all industry, government, and academic sectors. Many commercial database management systems support XML storage. Yet, the problem of translating between XML and objects automatically is largely unsolved, due to differences in the expressive power of their type systems and the difficulty of translating object queries into an XML query language such as XQuery. In hybrid relational/XML databases, this problem is compounded by the Object-relational impedance mismatch, since XML data can be partitioned across multiple relational tables. The much appreciated XML data type was introduced into SQL Server 2005. The new data type allows variables and columns to be created to specifically hold XML data, whether it be an entire XML document or just some XML content.

Using XQuery for CRUD

One of the most recent developments in XML is the emergence of a native XML query and transformation language XQuery. XQuery is usually the perfect language, providing a simple and elegant way to compute values from the content of an XML document. There are several reasons for using XML instead of the typical method of carrying out CRUD operations. The reasons are:

1. The best possible performance, XML parsing is extremely fast in TSQL using XQuery
2. Isolate the CRUD logics separate from the layers of the application
3. Prevents SQL injection attacks
4. Save application to database round trip time

SQL Server performs best with set-based operations. Updating row by row is slower than updating multiple rows in one shoot. In our example, say if the marketing staff wants to change the email ID of customer ID 1, 3, 5 they would select the items on the data grid, specify the new email ID, and then click on the Save button. How do you pass the list of IDs as a table to SQL Server? The easiest way is to pass the IDs in an instance of XML data type, and then use nodes() and value() method to convert the XML instance to a table and join the table with the Product table. That way, the updates are set-based.

Example Problem Scenario

Let's say we are going to build a dashboard for an marketing system, where marketing agents continuously enter potential new customer info and update existing customer info. The requirement is, via dashboard agents could enter multiple customer info and be able to save all data at once instead of making agents updating/inserting individual record. As you can see, below is the application dashboard UI where agents can add multiple customer by hitting Add More button. Bulk CRUD operation is carried out by 'save all'/'update all'/'delete all' buttons.

Solution to the Problem

The scenario mentioned above with the feature can be easily implementable by using xml/xquery. We can summarize the implementation steps as follows:

• We need a custom class that will contain all the customer property that will be needed in insert/update/delete in DB.
• Populate the class with respect to each customer and form a collection in runtime.
• Use reflection to translate that collection to an in memory XML format.
• Pass this formatted XML as a parameter value to database stored procedure for CRUD
• Using xquery, we will parse the parameter value and apply DML as needed.

The benefit of using this approach is:

• We can pass the data to database using minimum parameters (more precisely, we need to pass only one parameter of type XML).
• We can pass large set of data for CRUD operation at once.
• As data grows larger, it has no effect on DB call from application.
• Maintaining data integrity is simple.
• Due to flexible data structure, future change accommodation is easy at DAL (Data Access Layer).

Implementation

Now I will implement the solution following the steps mentioned above. For this particular implementation, I am using the problem scenario mentioned above and implement accordingly. After getting the idea, you can customize according to your need to implement your version of the solution. We will create an entity class that will hold the customer property and let's name it Customer. This class will populate after each update/save event from UI and added to a collection customerList for bulk operation. We will use reflection to convert this collection to XML.

foreach (Customer xml in customerList)
{
    XmlElement element = doc.CreateElement("data");
    PropertyInfo[] allProperties = xml.GetType().GetProperties();
    foreach (PropertyInfo thisProperty in allProperties)
    {
        object value = thisProperty.GetValue(xml, null);
        XmlElement tmp = doc.CreateElement(thisProperty.Name);
        if (value != null)
        {
            tmp.InnerXml = value.ToString();
        }
        else
        {
            tmp.InnerXml = string.Empty;
        }
        element.AppendChild(tmp);
    }
    node.AppendChild(element);
}   

After conversion, the resultant XML will look like:

After XML generation, this XML is passed to SQL Server stored procedure as parameter:

using (SqlConnection cn = new SqlConnection(GetConnectionString()))
{
    SqlCommand sqlCmd = new SqlCommand();
    sqlCmd.Connection = cn;
    sqlCmd.Connection.Open();
    sqlCmd.CommandType = CommandType.StoredProcedure;
    sqlCmd.CommandText = "spr_Sync_Customer";

    sqlCmd.Parameters.Add("@XMLDATA", SqlDbType.Xml, Int32.MaxValue);
    sqlCmd.Parameters["@XMLDATA"].Direction = ParameterDirection.Input;
    sqlCmd.Parameters["@XMLDATA"].Value = xml;

    sqlCmd.ExecuteScalar();
    sqlCmd.Connection.Close();
    sqlCmd.Connection.Dispose();
}   

In this particular case, spr_Sync_Customer is the stored procedure. In the stored procedure, XML data is parsed using Xquery and carried out respective insert/update/delete operations.

In this particular case, @XMLDATA is the stored procedure input parameter for supplied XML. Parse the XML and you can use the parsed data to populate database tables according to your need, in this example, I populate Customer table.

Conclusion

The example given above is very basic and intended to provide a basic startup to work with Xquery for CRUD. You can use the concept to work with much more complex ideas, for better performance, use the exist() method on the XML data type whenever possible. If you want to learn more about Xquery, you will find very useful guidelines here. The example source is included with this article, you are free to modify it according to your need.

History

• Version 1.0 @Nov 30^th, 2010