Click here to Skip to main content
Rate this: bad
good
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 2:21am
bhargavpp1.5K
Edited 9-Nov-12 2:24am
v2
Comments
bhargavpp at 9-Nov-12 8:22am
   
resolve my self
Mehdi Gholam at 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 list)
{
StringBuilder sb = new StringBuilder();
 
sb.Append("");
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("",
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 imageList = new List();
 
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 =
'






'
 
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
0 Sergey Alexandrovich Kryukov 480
1 mhegazy94 460
2 Ravi Bhavnani 190
3 Kornfeld Eliyahu Peter 185
4 Shemeemsha RA 160
0 Sergey Alexandrovich Kryukov 7,205
1 OriginalGriff 6,801
2 CPallini 5,350
3 George Jonsson 3,644
4 Gihan Liyanage 2,797


Advertise | Privacy | Mobile
Web02 | 2.8.140922.1 | Last Updated 9 Nov 2012
Copyright © CodeProject, 1999-2014
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