Solved

Inserting thousands of records into a MS SQL Database using List

Posted on 2013-01-29
7
295 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
Backup Your Microsoft Windows Server®

Backup 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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

914 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now