Click here to Skip to main content
13,139,254 members (54,719 online)
Click here to Skip to main content
Add your own
alternative version

Stats

21.7K views
111 downloads
22 bookmarked
Posted 7 Aug 2008

Inserting Value of Collection into Database

, 7 Aug 2008
Rate this:
Please Sign up or sign in to vote.
Inserting value of Collection into Database

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

  • 7th August, 2008: Initial post

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

bhadeliaimran
Team Leader Softweb Solutions
India India
Profiles : Code Project, ASP.NET Forums
Blog : Knowledgebase World
Current Company : Softwebsolution INC
User Group : Ahmedabad SQLServer UserGroup
Other : Microsoft Certified Technology Specialist (MCTS)

You may also be interested in...

Comments and Discussions

 
QuestionLINQ? Pin
merlin9818-Aug-08 4:02
membermerlin9818-Aug-08 4:02 
AnswerRe: LINQ? Pin
BhadeliaImran10-Aug-08 0:07
memberBhadeliaImran10-Aug-08 0:07 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.170915.1 | Last Updated 8 Aug 2008
Article Copyright 2008 by bhadeliaimran
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid