Solved

Inserting thousands of records into a MS SQL Database using List

Posted on 2013-01-29
7
297 Views
Last Modified: 2013-01-30
I have this class:
==============================================
   public class Anchor
        {
            public string url;
            public string title;          
        }
==============================================
I am gathering a bunch of data into

==============================================
public static List<Anchor> recListLinksAll = new List<Anchor>();
==============================================


Now by the time I am done recording everything into my recListLinksAll class I need to insert all of these records into a MS SQL database and here is what I am trying to do:

==============================================
conn.Open();
foreach (Anchor anchor in recListAnchorsAll)
            {              
                stSQL+= " INSERT INTO allAnchors ";
                stSQL += " VALUES(  ";
                stSQL += "'" + anchor.url + "',";
                stSQL += "'" + anchor.title.Replace("'", "''") + "'";
                stSQL += ") ";

                SqlCommand cmdInsert = new SqlCommand(stSQL, conn);

                try
                {
                    cmdInsert.ExecuteNonQuery();
                }
                catch (System.Net.WebException e)
                {
                    throw (e);
                }
             
            }
conn.Close();
==============================================

And what happens is it inserts about 4000 records and times out. Can anyone provide me with a more efficient way? Perhaps an XMLSerializer or something like that where I wouldn't have to insert each one at a time, but rather in bulk.
0
Comment
Question by:vituxa
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 42

Expert Comment

by:sedgwick
ID: 38832972
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 38833510
Providing you are using SQL Server 2008 you can use Table Valued Parameters with .NET.  Check out the following link and ask if you have any more questions:
http://msdn.microsoft.com/en-us/library/bb510489(v=sql.100).aspx
0
 
LVL 13

Accepted Solution

by:
jonnidip earned 500 total points
ID: 38834341
You may use the SqlBulkCopy class:
DataTable dt = new DataTable();
dt.Columns.Add("url", typeof(String));
dt.Columns.Add("title", typeof(String));

// This is just to add a sample element for your Anchor object
List<Anchor> recListLinksAll = new List<Anchor>();
recListLinksAll.Add(new Anchor() { url = "test_url", title = "test_title" });
// end - sample element

foreach (Anchor a in recListLinksAll)
    dt.Rows.Add(new Object[2] { a.url, a.title });
            
SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(new SqlConnection("myConnectionString"), SqlBulkCopyOptions.TableLock, null);
sqlBulkCopy.DestinationTableName = "TableName";
sqlBulkCopy.WriteToServer(dt);

Open in new window


Regards.
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 1

Author Closing Comment

by:vituxa
ID: 38835188
Thank you! When I am looking for a solution -  I am not looking for links, I AM looking for sample code. Thank you again!
0
 
LVL 42

Expert Comment

by:sedgwick
ID: 38835198
the link has a full example code...
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 38835258
When I am looking for a solution -  I am not looking for links, I AM looking for sample code.
They pray tell why did you not say so?  All you did was ask "Can anyone provide me with a more efficient way?"  We did and you chose to use another approach and that is your choice.
0
 
LVL 1

Author Comment

by:vituxa
ID: 38835323
OK. My apologies. You are right. I will be more specific next time.
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question