inserting list generic collection in sql

please follow as below

find .cs code

using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;

public static class ListExtension
{
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("");

return sb.ToString();
}
}

public class UserImage
{
public long Id { get; set; }
public long UserId { get; set; }
public string OriginalName { get; set; }
public string StorageName { get; set; }

public UserImage() { }
public UserImage(long id, long userId, string originalName, string storageName)
{
this.Id = id;
this.OriginalName = originalName;
this.StorageName = storageName;
this.UserId = userId;
}

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);
}
}

public partial class InsertingCollection : System.Web.UI.Page
{

protected void Page_Load(object sender, EventArgs e)
{
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"));

//For testing purpose
Response.ContentType = "text/xml";
Response.Write(imageList.ToXMLString());

DataManager.InsertUserImage(imageList.ToXMLString());
}
}

public static class DataManager
{
internal static void InsertUserImage(string userImages)
{
using (SqlConnection con = new SqlConnection("[YOUR_CONNECTION_STRING]"))
{
SqlCommand cmd = con.CreateCommand();
SqlParameter param = new SqlParameter();
cmd.CommandText = "[STOREDPROCEDURE_NAME";
cmd.CommandType = CommandType.StoredProcedure;
param.ParameterName = "@UserImages";
param.Value = userImages;
param.DbType = DbType.Xml;

cmd.Parameters.Add(param);
cmd.ExecuteNonQuery();
con.Close();
}
}
}


and at the sql server side code


Declare @UserImages as XML

SELECT @UserImages =
' <userimages>
<userimage hashcode="60272281" id="1" userid="786" originalname="Test1.jpg" storagename="C:\Temp\User_786_1.jpg">
<userimage hashcode="55244443" id="2" userid="786" originalname="Test2.jpg" storagename="C:\Temp\User_786_2.jpg">
<userimage hashcode="50429688" id="3" userid="786" originalname="Test3.jpg" storagename="C:\Temp\User_786_3.jpg">
<userimage hashcode="54351321" id="4" userid="786" originalname="Test4.jpg" storagename="C:\Temp\User_786_4.jpg">
<userimage hashcode="13811662" id="5" userid="786" originalname="Test5.jpg" storagename="C:\Temp\User_786_5.jpg">
<userimage hashcode="29407249" id="6" userid="786" originalname="Test6.jpg" storagename="C:\Temp\User_786_6.jpg">
'

SET NOCOUNT ON

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

INSERT INTO @UserImageTable

SELECT
--UserImage.value ('@HashCode[1]', 'VARCHAR(10)') as HashCode,
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)

SELECT * FROM @UserImageTable


this is the solution