Bulk Update and Insert of Object State Stored by SQL Server 2000, Using .NET (C#)

This is a how to/quick start for doing bulk inserts or updates of data on SQL Server 2000 and .NET.

1. Create a simple class:

2. Create a class to manage a set of Entities:

    ...
    ...

    using System;
    using System.Data;
    using System.Collections;
    using System.Data.SqlClient;
    using Microsoft.Practices.EnterpriseLibrary.Data;
    using Microsoft.Practices.EnterpriseLibrary.Data.Sql;
      
    public class Entities: IDisposable
    {
        private ArrayList _entityList = new ArrayList();

        public Entity this[int idx]
        {
            get
            {
                return _entityList[idx] as Entity;
            }
        }
       

        public void Add( Entity entity )
        {
            lock ( _entityList.SyncRoot )
            {
                _entityList.Add( entity );
            }
        }
       

        public void Dispose()
        {
            _store();
        }


        private void _store()
        {
            ArrayList dirtyEntityList = new ArrayList();

            lock ( _entityList.SyncRoot )
            {
                for ( int iEnt = 0; iEnt < _entityList.Count;   iEnt++ )
                {
                    Entity entity = _entityList[iEnt] as Entity;

                    if ( entity.IsDirty )
                    {
                        dirtyEntityList.Add( entity );
                    }
                }
            }

            // Copy ArrayList to Entity array
            Entity[] entities = dirtyEntityList.ToArray( typeof( Entity ) ) as Entity[];
           
            // Serialize array
            string xml = Serialization.XmlSerialize( entities );

            // Create SQL command
            Database db = DatabaseFactory.CreateDatabase();   
            DBCommandWrapper command = db.GetStoredProcCommandWrapper( "Entity_Set" );
            command.AddInParameter( "@entities", DbType.String, xml );
           
            // Send dirty Entities to SQL Server
            db.ExecuteNonQuery( command );
        }
       }

    ...
    ...

3. Create a method to serialize objects:

    using System;
    using System.IO;
    using System.Text;
    using System.Xml.Serialization;

    ...
    ...

    /// <summary>
    /// Gets the XML of the passed object.
    /// </summary>
    public static string XmlSerialize( object obj )
    {
        // Validate argument 'obj'
        if ( obj == null )
            throw new ArgumentNullException( "obj", "Argument 'obj' is null" );

               XmlSerializer ser = new XmlSerializer( obj.GetType() );

        MemoryStream ms = new MemoryStream();
        UTF8Encoding utf8 = new UTF8Encoding(false,false);
        StreamWriter sw = null;
        string xml = null;
           
        using ( ms )
        {
            using ( sw )
            {
                sw = new StreamWriter( ms, utf8 );
                ser.Serialize( sw, obj );
                xml = utf8.GetString( ms.GetBuffer() );
            }
        }
           
        return xml;
    }

    ...
    ...
 

4. Create a stored procedure that will de-serialize the object’s XML into a table, from which new and current records are inserted or updated:


    CREATE PROCEDURE dbo.Entity_Set
    ( @entities    VARCHAR( 5000 ) )        
    AS
    
    SET NOCOUNT ON
    
    DECLARE @xml INT
          EXEC SP_XML_PREPAREDOCUMENT @xml OUTPUT,
       
    @entities

    -- Insert new entities
    INSERT INTO    Entity
                            ( Name
                            , Description )
    SELECT            XMLEntity.Name,
                            XMLEntity.Description
    FROM              OPENXML ( @xml, ‘//Entity’, 2 )
    WITH               ( ID    INT
                           , Name    VARCHAR( 50 )
                           , Description VARCHAR( 100 ) )
                           XMLEntity
     WHERE          XMLEntity.ID = 0
        
    -- Update current entitites
    UPDATE           Entity
    SET                  Name = XMLEntity.Name,
                            Description = XMLEntity.Description
     FROM             OPENXML ( @xml, ‘//Entity’, 2 )
     WITH               ( ID        INT
                            , Name        VARCHAR( 50 )
                            , Description     VARCHAR( 100 ) )
                            XMLEntity
     WHERE           XMLEntity.ID = Entity.ID

     EXEC SP_XML_REMOVEDOCUMENT @xml
   
     GO
   

      The OpenXml method’s last parameter indicates that this is an element centric XML table. .NET by default serializes objects with an element based schema. Use 0       or 1 for attribute centric XML documents.

5. Now create a NUnit test class, to demonstrate the new bulk insert and update of data:

    /// <summary>
    /// Summary description for EntityTests.
    /// </summary>
    [TestFixture]
    public class EntityTests
    {
        private static Random _random = new Random();

        [Test]
        public void Entities_Update_Test()
        {
            using ( Entities ents = new Entities() )
            {
                ents.Add( _generate() );
                ents.Add( _generate() );
                ents.Add( _generate() );
                ents.Add( _generate() );
            }
        }

        private static Entity _generate()
        {
            Entity ent = new Entity();           
            ent.Name = _random.NextDouble().ToString();           
            ent.Description = _random.NextDouble().ToString();           
            return ent;
        }
    }


Run Entities_Update_Test() in NUnit, and see how SQL Server bulk updates the Entity table in one go!