Inserting Value of Collection into Database

• Download source - 1.87 KB

Introduction

The article/code will help people who require inserting multiple objects or a list of objects at the same time with single database interaction.

Background

We always require things like this, when we need to insert a list of objects at the same time with single database operation. Here is the code which includes the same functionality by using the C# 3.0 feature, Extension Method.

Using the Code

This code contains the logic of creating XML by overriding the ToString method:

public override string ToString()
{
    return string.Format("<UserImage HashCode=\"{0}\" Id=\"{1}\" 
	UserId=\"{2}\" OriginalName=\"{3}\" StorageName=\"{4}\" />",
    this.GetHashCode(), this.Id, this.UserId, this.OriginalName, this.StorageName);
}        

This method will generate the XML representation of the UserImage class. The UserImage class contains the basic property that requires to store image. You can change it accordingly and also don't forgot to change the ToString method.

So now, you have a collection of UserImage, and need to generate XML for all the UserImages which are inside the collection.

List<UserImage> imageList = new List<UserImage>();
//Add some images
imageList.Add(new UserImage(1, 786, "Test1.jpg", @"C:\Temp\User_786_1.jpg"));
imageList.Add(new UserImage(2, 786, "Test2.jpg", @"C:\Temp\User_786_2.jpg"));
imageList.Add(new UserImage(3, 786, "Test3.jpg", @"C:\Temp\User_786_3.jpg"));
imageList.Add(new UserImage(4, 786, "Test4.jpg", @"C:\Temp\User_786_4.jpg"));
imageList.Add(new UserImage(5, 786, "Test5.jpg", @"C:\Temp\User_786_5.jpg"));
imageList.Add(new UserImage(6, 786, "Test6.jpg", @"C:\Temp\User_786_6.jpg"));

imageList is ready, we also have ToString method which gives the XML of UserImage. Now what we have to do is... we have to iterate through the collection and get the XML of every object in imageList. For this, we use Extension Methods, and here is the ToXMLString extension method, which applies to List<UserImage>.

public static string ToXMLString(this List<UserImage> list)
{
    StringBuilder sb = new StringBuilder();
    sb.Append("<UserImages>");

    foreach (UserImage image in list)
    {
        sb.Append    (image.ToString());
    }

    sb.Append("</UserImages>");
    return sb.ToString();
}

In the above extension method, we call ToString method of UserImage to generate an individual XML string and then Append the parent tags.

So at the end, you can have XML just like below:

<UserImages>
    <UserImage HashCode="17798814" Id="1" UserId="786" 
	OriginalName="Test1.jpg" StorageName="C:\Temp\User_786_1.jpg" /> 
    <UserImage HashCode="38230786" Id="2" UserId="786" 
	OriginalName="Test2.jpg" StorageName="C:\Temp\User_786_2.jpg" /> 
    <UserImage HashCode="64649019" Id="3" UserId="786" 
	OriginalName="Test3.jpg" StorageName="C:\Temp\User_786_3.jpg" /> 
    <UserImage HashCode="15452120" Id="4" UserId="786" 
	OriginalName="Test4.jpg" StorageName="C:\Temp\User_786_4.jpg" /> 
    <UserImage HashCode="56059077" Id="5" UserId="786" 
	OriginalName="Test5.jpg" StorageName="C:\Temp\User_786_5.jpg" /> 
    <UserImage HashCode="36389945" Id="6" UserId="786" 
	OriginalName="Test6.jpg" StorageName="C:\Temp\User_786_6.jpg" /> 
</UserImages>

Now for the SQL part - we need to pass the string to a stored procedure and then in the procedure, we just need to select values from the XML node that can directly get inserted into the table.

Here we go:

INSERT INTO @UserImageTable 

SELECT 
    UserImage.value ('@Id[1]', 'VARCHAR(10)') as Id,
    UserImage.value ('@UserId[1]', 'VARCHAR(10)') as UserId,
    UserImage.value ('@OriginalName[1]', 'VARCHAR(100)') as OriginalName,
    UserImage.value ('@StorageName[1]', 'VARCHAR(100)') as StorageName
FROM
@UserImages.nodes('/UserImages/UserImage') v(UserImage)

The @UserImageTable is the temporary table:

DECLARE @UserImageTable AS TABLE
(
    Id BIGINT,
    UserId BIGINT,
    OriginalName VARCHAR(100),
    StorageName VARCHAR(100)
)

Points of Interest

The good thing is Extension Methods give us freedom to write extension of any given class. Moreover, XML is a strong part of SQL Server 2005. However, there is the limitation of XML datatype which you can find here.

History

• 7^th August, 2008: Initial post