Serialized collection of classes or List of objects into XML and bulk insert using OpenXML

• Download demo project - 141 KB

Introduction 

A lot of the time in our daily coding we need to implement bulk insert update data from UI or Business Layers where most of the time we use looping to insert data, but bulk insert is a very useful trick to use. Another useful thing which we need most of the time is serialization which I implement here for generating XML.

This a simple application where I have used collection of a class and created XML from it using serialization which we can do most of the time using datasets, like this:

StringWriter sw = new StringWriter();
XmlTextWriter xw = new XmlTextWriter(sw);
XmlTextWriter xw1 = new XmlTextWriter(sw);Dataset ds=new Dataset();
ds.WriteXml(xw);
strXML = sw.ToString();

But serialization and desalinization from a collection is the most important part which we need for some cases like this.

This is the SP for Bulk Insert into a table from XML, which takes an XML string as parameter and inserts rows into table using OpenXML. Following is an example where ArrayOfUser is the root node which is generated from a collection of classes or an IList object through the SerializeToXML<T>(List<T> source) method. 

-----------------------------------------------------------------------------------------
Sample Xml generates from collectin of User.
---------------------------------------------------------
<ArrayOfUser>
<User>
<Name>Test 1</Name>
<Age>10</Age> 
<DOB>2012-07-20T12:35:17.203125+05:30</DOB>
</User>
<User> 
<Name>Test 2</Name> 
<Age>20</Age> 
<DOB>2012-07-20T12:35:17.203125+05:30</DOB>
</User> 
<User>
<Name>Test 3</Name>
<Age>30</Age> 
<DOB>2012-07-20T12:35:17.203125+05:30</DOB> 
</User> 
</ArrayOfUser>

Sample Stored Procedure:

CREATE Procedure [dbo].[sp_Bulk_Insert_User]   
   @strXML NVARCHAR(MAX)   
  AS   
  BEGIN   
  SET NOCOUNT ON;   
  DECLARE @XMLDocPointer INT
  
  
    begin
  EXEC sp_xml_preparedocument @XMLDocPointer OUTPUT, @strXML
  BEGIN TRANSACTION   
    
    
		INSERT INTO [User]
           ([Name]
           ,[Age]
           ,[DOB]
           )
           select    
			[Name]
           ,[Age]
           ,[DOB]
           from    
 OPENXML(@XMLDocPointer,'/ArrayOfUser/User',2)with(
			[Name]  VARCHAR(50)
           	,[Age] INT
           	,[DOB] DATETIME
           
 ) 

		  IF @@Error<> 0   
		  Begin   
		  Raiserror('Not saved. Please try again',16,1)   
		  Rollback Transaction   
		  End   
		  Else   
		  COMMIT transaction   
		  EXEC sp_xml_removedocument @XMLDocPointer   
	end
END
GO

"SerializeToXML" is a generic method that takes a list objects of any class and parses it into XML and returns the XML as a string in the Utility class.

public static string SerializeToXML<T>(List<T> source)
{
    XmlSerializer serializer = new XmlSerializer(source.GetType());

    StringWriter _StringWriter = new StringWriter();
    XmlTextWriter _XmlTextWriter = new XmlTextWriter(_StringWriter);
    
    //Setting for xml
    var settings = new XmlWriterSettings();
    settings.Indent = true;
    settings.OmitXmlDeclaration = true;

    XmlWriter _XmlWriter = XmlWriter.Create(_StringWriter, settings);

	//Remove Qualifier feolds from nodes
    var emptyNs = new XmlSerializerNamespaces(new[] { XmlQualifiedName.Empty });

    serializer.Serialize(_XmlWriter, source, emptyNs);

    return _StringWriter.ToString();
}

From the Button Click event we create a list for User class and sends the list to the Business Layer class SaveUserData(IList<User> ItemListUser).

public static string SerializeToXML<T>(List<T> source)
{
    XmlSerializer serializer = new XmlSerializer(source.GetType());

    StringWriter _StringWriter = new StringWriter();
    XmlTextWriter _XmlTextWriter = new XmlTextWriter(_StringWriter);
    
    //Setting for xml
    var settings = new XmlWriterSettings();
    settings.Indent = true;
    settings.OmitXmlDeclaration = true;

    XmlWriter _XmlWriter = XmlWriter.Create(_StringWriter, settings);

	//Remove Qualifier feolds from nodes
    var emptyNs = new XmlSerializerNamespaces(new[] { XmlQualifiedName.Empty });

    serializer.Serialize(_XmlWriter, source, emptyNs);

    return _StringWriter.ToString();
}

From the Button Click event of UI we create a list for User class and sends the list to the Business Layer class SaveUserData(IList<User> ItemListUser).

protected void btnData_Add_Click(object sender, EventArgs e)
{
    try
    {
        List<User> Userlist;
        Userlist = new List<User>();
        for (int i = 0; i < int.Parse(txtcnt.Text); i++)
        {
            User Userobj = new User();
            Userobj.Name = "Test " + (i + 1).ToString();
            Userobj.DOB = DateTime.Now;
            Userobj.Age = i + 1;
            Userlist.Add(Userobj);
        }
        BussinessUtility _BussinessUtility = new BussinessUtility();
        if (_BussinessUtility.SaveUserData(Userlist))
            lblMessage.Text = "Saved";
        else
            lblMessage.Text = "Error";
    }
    catch
    {
    }
}

This is the method of the Business Layer which calls the method of serialization from the Utility class and sends to the data access class. 

public bool SaveUserData(IList<User> ItemListUser)
{
    DataAccessUtility DA = new DataAccessUtility();

    bool j = false;
    try
    {
        j = DA.SavaBulkData(WebUtility.SerializeToXML(ItemListUser));

    }
    catch
    {

    }
    return j;
}

The Save method in the DataAccessUtility class calls the Stored Procedure sp_Bulk_Insert_User and takes an XML string as parameter.

public bool SavaBulkData(string strXML)
{
    SqlConnection _conn = null;
    SqlCommand _cmd = null;
    SqlTransaction _tran = null;
    bool _retVal = false;

    try
    {
        DBAccess.DBConnection(ref _conn);
        _cmd = new SqlCommand("sp_Bulk_Insert_User", _conn);
        _cmd.CommandTimeout = 0;
        _cmd.CommandType = CommandType.StoredProcedure;
        _tran = DBAccess.OpenTransaction(_conn, _tran, _cmd);

        _cmd.Parameters.Add(new SqlParameter("@strXML", SqlDbType.NVarChar));
        _cmd.Parameters["@strXML"].Value = strXML;

        int rowsAffected = _cmd.ExecuteNonQuery();

        //create a transaction
        DBAccess.EndTransaction(ref _tran, false);
        DBAccess.CloseConnection(ref _conn, ref _cmd);
        _retVal = true;

    }
    catch (SqlException ex)
    {
        _retVal = false;
        DBAccess.EndTransaction(ref _tran, true);
        DBAccess.CloseConnection(ref _conn, ref _cmd);

        throw ex;
    }
    catch (Exception ex)
    {
        _retVal = false;
        DBAccess.EndTransaction(ref _tran, true);
        DBAccess.CloseConnection(ref _conn, ref _cmd);
        
        throw ex;
    }
    return _retVal;
}

Conclusion

In this article I have tried to cover a few topics like serialization to XML, bulk insertion, etc.