Click here to Skip to main content
12,634,927 members (31,099 online)
Rate this:
 
Please Sign up or sign in to vote.
See more: ASP.NET SQL
how to insert List collection in sql in single time
Posted 9-Nov-12 3:21am
bhargavpp1.5K
Updated 9-Nov-12 3:24am
v2
Comments
bhargavpp 9-Nov-12 8:22am
   
resolve my self
Mehdi Gholam 9-Nov-12 8:22am
   
Please edit your question as it makes little sense.

1 solution

Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

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
  Permalink  

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

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web02 | 2.8.161208.2 | Last Updated 9 Nov 2012
Copyright © CodeProject, 1999-2016
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100