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 UserImage
s which are inside the collection.
List<UserImage> imageList = new List<UserImage>();
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