Inserting value of Collection into Database

• Download InsertingCollection - 1.87 KB

Introduction

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

Background

We always require thing like this, when you need to Insert list of object at the same time with single database operation. Here is the code which includes the same functionality by using feature Extension Method" of C# 3.0.

Using the code

This code contains the logic of creating XML by overriding 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 require to store image, you can change it accordingly and also dont forgot to change the ToString method

So now you have collection of UserImage, and need to generate XML for all the UserImage which are inside 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 thru collection and get the XML of every object in imageList. For this we user Extension Methods, and here is the ToXMLString extension method, whichi applies on 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 individual XML string and then Apped the parent tags

so at the end you can have XML just like bellow

<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 Sql part, we need to pass the string to stored procedure and then in procedure we just need to select values form XML node and can directly get inserted to 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 temparory table.

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

Points of Interest

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

You must Sign In to use this message board.

FAQ  Noise Tolerance Very HighHighMediumLowVery Low  Search Messages    Layout Expand All MessagesThread ViewNo Javascript (slow)No Javascript Preview    Per page 102550

Msgs 1 to 2 of 2 (Total in Forum: 2) (Refresh) FirstPrevNext

LINQ? merlin981 5:02 8 Aug '08   You have a good idea, here, but your ToString method could be rewritten using LINQ to XML. The code would be a lot more readable, and you would be assured that the outputted XML is valid. You can find examples of writing an XML file at: How to Write an XML File Using LINQ to XML ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ LINQ Exchange - Learn about LINQ and Lambda Expressions Joke of the Day and Random Jokes - ReallyFunnyQuickJokes.com ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Sign In·View Thread·PermaLink 4.33/5 (2 votes) Re: LINQ? BhadeliaImran 1:07 10 Aug '08   Thanks for your suggession, will update this very soon. Once again thanks. Sign In·View Thread·PermaLink

Last Visit: Wednesday, December 3, 2008 9:07 PM     Last Update:Wednesday, December 3, 2008 9:07 PM