Solved

Inserting thousands of records into a MS SQL Database using List

Posted on 2013-01-29
7
302 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
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.

 
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
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

749 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