Solved

Inserting thousands of records into a MS SQL Database using List

Posted on 2013-01-29
7
305 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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how the fundamental information of how to create a table.

688 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