August 17, 2011 at 8:32 am
I am adding records in c# list.
after adding item to list I need to insert all the records in this list into database using a stored procedure.
I knew that I could use temp tables. But its not preferred in my team.
Can any one please let me know best approach to do this.
foreach (var bcVideo in initialHundredVideos)
{
int compareValue = DateTime.Compare(Video.LastModifiedDate, DateTime.Parse(strLatestDate));
if (compareValue > 0)
{
listModifiedVideos.Add(Video);
connection.Open();
// Create the stored procedure command.
SqlCommand cmd = new SqlCommand("USP_InsertorUpdateRecords", connection);
}
}
Video is an object which holds data in this format VideoId, date, title, lmddate, links, etc.
Can anyone please help me with passing this "video" object as parameter to Stored proc and do the insert(if NOT EXIST) or update (IF EXIST)
August 17, 2011 at 8:35 am
have a look at the merge statement
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
August 17, 2011 at 10:01 am
If you're passing a single instance of the object, your best bet is to pass the individual properties of the video object as parameters to your stored proc. You could then synchronize the entire list in C#, and only pass to the database the videos that are modified.
If you really want to pass a list of videos, you have two options; If you're ok limiting yourself to SQL server 2008, you can use the table-valued parameter approach outlined here: http://www.codeproject.com/KB/cs/CSharpAndTableValueParams.aspx
If you need to be able to run on sql server 2005, you can use an xml parameter, which you parse in your stored proc.
-a
Dan Guzman - Not the MVP (7/22/2010)
All questions have to be prefaced by Server version and 'according to MS Docs' or 'my own personal opinion based on how much detail I felt like digging into at the time.'
August 17, 2011 at 11:35 am
Thank you For your resposne .. will try and get back..
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply