How to insert large amount of data into SQL Server 2008 with c# code?

Question

I have developed a program which calculates and inserts around 4800 rows within a loop to SQL Server 2008. But after inserting 200+ rows it gets stuck every time and does not insert the rest of the rows.

Now I am writing a text file with the insert command inside the loop instead inserting into the DB. If I try to copy the whole 4800 insert command from the text log and paste it into the query editor of the SQL Server then it inserts all within 1 minute. I would like to get suggestion on how I may solve this issue? I would appreciate any suggestion or help.

Here is my code sample what I am trying now:

 strSQL = "Insert into performance Values (@Rptdate,@CP_Name, @Shortcode, @Keyword, @MO_Count, @MO_Revenue,";
 strSQL = strSQL + "@PMT_Sent_Count, @MT_Revenue, @ZMT_Sent_Count, @Infra_Revenue, @Total_MT, @UM_rev_Share, @CP_Rev_Share, ";
 strSQL = strSQL + "@MCP_Rev_Share, @UM_Total_Revenue, @CP_Revenue)";

 try
 {
     db.openconn("MOMT_Report", "Report");
     cmd = new SqlCommand(strSQL, db.cn);

     cmd.Parameters.AddWithValue("@Rptdate", Rptdate);
     cmd.Parameters.AddWithValue("@Name", Name);
     cmd.Parameters.AddWithValue("@Shortcode", Shortcode);
     cmd.Parameters.AddWithValue("@Keyword", Keyword);
     cmd.Parameters.AddWithValue("@MO_Count", MO_Count);
     cmd.Parameters.AddWithValue("@MO_Revenue", MO_Revenue);
     cmd.Parameters.AddWithValue("@PMT_Sent_Count", PMT_Sent_Count);
     cmd.Parameters.AddWithValue("@MT_Revenue", MT_Revenue);
     cmd.Parameters.AddWithValue("@ZMT_Sent_Count", ZMT_Sent_Count);
     cmd.Parameters.AddWithValue("@Infra_Revenue", Infra_Revenue);
     cmd.Parameters.AddWithValue("@Total_MT", Total_MT);
     cmd.Parameters.AddWithValue("@rev_Share", rev_Share);
     cmd.Parameters.AddWithValue("@Rev_Share", Rev_Share);
     cmd.Parameters.AddWithValue("@MCP_Rev_Share", MCP_Rev_Share);
     cmd.Parameters.AddWithValue("@Total_Revenue", Total_Revenue);
     cmd.Parameters.AddWithValue("@Revenue", Revenue);

     cmd.CommandTimeout = 0;
     cmd.ExecuteNonQuery();
 }

Is there a reason why you won't break it down in batches of say 100 records at a time. That way you won't end up locking up your Db objects for long. — Neel, Dec 09 '14 at 04:24

score 1 · Answer 1 · answered Dec 09 '14 at 04:14

1

Use Bulk Insert. It is nicely described here: http://blogs.msdn.com/b/nikhilsi/archive/2008/06/11/bulk-insert-into-sql-from-c-app.aspx

answered Dec 09 '14 at 04:14

Riad Baghbanli

3,105
1
12
20

score 1 · Answer 2 · edited Dec 09 '14 at 05:55

1

I think you need set timeout option first in link below:

http://msdn.microsoft.com/en-us/library/ms189470.aspx

then try to change max allowed packet:

http://msdn.microsoft.com/en-us/library/ms177437.aspx

hope it will work

edited Dec 09 '14 at 05:55

marc_s

732,580
175
1,330
1,459

answered Dec 09 '14 at 04:25

Jamaxack

2,400
2
24
42

score 1 · Accepted Answer · edited May 23 '17 at 12:32

Is there a db.closeconn(); somewhere after the try block that was pasted into the question? If not then that is a huge issue (i.e. to keep opening connections and not closing them, and that could explain why it freezes after opening 200+ of them). If there is a close connection method being called then great, but still, opening and closing the connection per each INSERT is unnecessary, let alone horribly inefficient.

At the very least you can:

define the query string, SqlParameters, and SqlCommand once
in the loop, set the parameter values and call ExecuteNonQuery();
(it is also preferred to not use AddWithValue() anyway)

Example:

// this should be in a try block

strSQL = "INSERT...";
db.openconn("MOMT_Report", "Report");
cmd = new SqlCommand(strSQL, db.cn);

SqlParameter _Rptdate = new SqlParameter("@Rptdate", DbType.Int);
cmd.Parameters.Add(_Rptdate);

...{repeat for remaining params}...

// optional begin transaction

for / while loop
{
  _Rptdate.Value = Rptdate;
  // set other param values
  cmd.ExecuteNonQuery();
}

// if optional transaction was started, do commit

db.closeconn(); // this should be in a finally block

However, the fastest and cleanest way to get this data inserted is to use Table-Valued Parameters (TVPs) which were introduced in SQL Server 2008. You need to create a User-Defined Table Type (one time) to define the structure, and then you can use it in either an ad hoc insert like you current have, or pass to a stored procedure. But this way you don't need to export to a file just to import. There is no need for that additional steps.

Rather than copy/paste a large code block, I have noted three links below where I have posted the code to do this. The first two links are the full code (SQL and C#) to accomplish this. Each is a slight variation on the theme (which shows the flexibility of using TVPs). The third is another variation but not the full code as it just shows the differences from one of the first two in order to fit that particular situation. But in all 3 cases, the data is streamed from the app into SQL Server. There is no creating of any additional collection or external file; you use what you currently have and only need to duplicate the values of a single row at a time to be sent over. And on the SQL Server side, it all comes through as a populated Table Variable. This is far more efficient than taking data you already have in memory, converting it to a file (takes time and disk space) or XML (takes cpu and memory) or a DataTable (for SqlBulkCopy; takes cpu and memory) or something else, only to rely on an external factor such as the filesystem (the files will need to be cleaned up, right?) or need to parse out of XML.

Thank you so much your suggestion really saved my life. at this moment I am implementing the first solution as I need to study a bit TVPs then later I may try to make it more clean. Thanks a lot. — barsan, Dec 09 '14 at 06:21
@barsan np. You can also put a transaction around the loop as Sarvesh suggested to make it even faster. How fast is it now as compared to the 1 minute it was taking in SSMS? — Solomon Rutzky, Dec 09 '14 at 06:33
@barsan Also, if you do move to the TVP method, be sure to review those first two links I posted that have full examples. You need to make sure to supply the parameter.Value from a method that return `IEnumerable` and not a `DataTable`. — Solomon Rutzky, Dec 09 '14 at 15:38

score 1 · Answer 4 · answered Dec 09 '14 at 04:51

executing one sqlcommand is much better than executing 4800 sqlcommands there is several ways, for the Bulk Insert you need your .txt file be accessed from your database server(transfer the file to database server or it can access the file through network) and after that use:

BULK INSERT Your_table
FROM 'full_file_name'
WITH
 (
    FIELDTERMINATOR =' terminator character',
    ROWTERMINATOR = ':\n'
  );

another way: you can build a new text in your C# code which has an insert command for each row and execute the whole text once (it's better to put it into a transaction)

score 1 · Answer 5 · answered Dec 09 '14 at 05:03

I think if you need to bulk insert using XML then you can use this type of approach also..

first of all create a store procedure like this..

CREATE PROCEDURE SP_INSERT_BULK
    @DataXML XML 
AS
BEGIN
    INSERT INTO performance
    SELECT 
        d.value('@Rptdate','varchar') AS Rptdate
        ,d.value('@Name','varchar') AS Name
        ,d.value('@Shortcode','varchar') AS Shortcode
        ,d.value('@Keyword','varchar') AS Keyword
        ,d.value('@MO_Count','varchar') AS MO_Count
        ,d.value('@MO_Revenue','varchar') AS MO_Revenue
        ,d.value('@PMT_Sent_Count','varchar') AS PMT_Sent_Count
        ,d.value('@MT_Revenue','varchar') AS MT_Revenue
        ,d.value('@ZMT_Sent_Count','varchar') AS ZMT_Sent_Count
        ,d.value('@Infra_Revenue','varchar') AS Infra_Revenue
        ,d.value('@Total_MT','varchar') AS Total_MT
        ,d.value('@rev_Share','varchar') AS rev_Share
        ,d.value('@Rev_Share','varchar') AS Rev_Share
        ,d.value('@MCP_Rev_Share','varchar') AS MCP_Rev_Share
        ,d.value('@Total_Revenue','varchar') AS Total_Revenue
        ,d.value('@Revenue','varchar') AS Revenue
    FROM @DataXML.nodes('Reports/Report') n(d)

END

the above store procedure is just for demonstration, you can modify it with your own logic

now next step is to create data XML to pass into your store procedure as parameter

            //prepare your data xml here
            //you can use any of your logic to prepare dataxml
            string xmlstring = @"<?xml version='1.0' encoding='utf-8'?><Reports>";
            for (int i = 0; i < recordcout; i++)
            {   
                xmlstring += string.Format(@"<Report Rptdate='{0}' 
                                              Name='{1}' 
                                              Shortcode='{2}' 
                                              Keyword='{3}' 
                                              MO_Count='{4}' 
                                              MO_Revenue ='{5}' 
                                              PMT_Sent_Count='{6}' 
                                              MT_Revenue='{7}' 
                                              ZMT_Sent_Count ='{8}' 
                                              Infra_Revenue='{9}' 
                                              Total_MT='{10}' 
                                              rev_Share='{11}' 
                                              Rev_Share='{12}' 
                                              MCP_Rev_Share='{13}' 
                                              Total_Revenue='{14}' 
                                              Revenue='{15}' />");
            }
            xmlstring += "</Reports>";

Now next step is to pass this XML string to your store procedure

using (SqlConnection dbConnection = new SqlConnection("CONNECTIONSTRING"))
            //Create database connection  
            {
                // Database command with stored - procedure  
                using (SqlCommand dbCommand =
                       new SqlCommand("SP_INSERT_BULK", dbConnection))
                {
                    // we are going to use store procedure  
                    dbCommand.CommandType = CommandType.StoredProcedure;
                    // Add input parameter and set its properties.
                    SqlParameter parameter = new SqlParameter();
                    // Store procedure parameter name  
                    parameter.ParameterName = "@DataXML";
                    // Parameter type as XML 
                    parameter.DbType = DbType.Xml;
                    parameter.Direction = ParameterDirection.Input; // Input Parameter  
                    parameter.Value = xmlstring; // XML string as parameter value  
                    // Add the parameter in Parameters collection.
                    dbCommand.Parameters.Add(parameter);
                    dbConnection.Open();
                    int intRetValue = dbCommand.ExecuteNonQuery();
                }
            }

Just FYI: it is best to _not_ prefix stored procedure names with `sp_` as that is a reserved prefix that causes SQL Server to first check the `[master]` database to see if the proc is in there, and if not, then it will execute the local proc. Also, when building the `xmlstring`, you need to properly escape certain characters, such as `<`, `"`, and `&`. — Solomon Rutzky, Dec 09 '14 at 06:00
yes, you are right srutzky, i need to taken care for special character before building xml string, but i have just created sample code quickly to answer this question so forget to add those part, and thanks guiding me for sp prefix naming... — Tejas Vaishnav, Dec 09 '14 at 14:13

Sarvesh Mishra · Answer 6 · 2014-12-09T06:24:20.627

1

You should do all this in one transaction.

Open DB connection.
Create command.
Begin transaction.
Start loop.
Clear parameters if added
Set parameters and execute it.
End loop.
Commit transaction.
Close DB connection.

edited Dec 09 '14 at 06:24

answered Dec 09 '14 at 05:19

Sarvesh Mishra

2,014
15
30

You don't need to create the command in the loop. You do that before the "begin transaction". You only need to set the parameter values in the loop. Also, it is not true that it is a single transaction in SSMS. Unless an explicit BEGIN TRAN has been executed, each DML statement is its own transaction. The app code is slower due to opening and closing the connection each time. But they both equally execute 4800 transactions in the current model. In SSMS, starting with BEGIN TRAN and ending with COMMIT would make it a single transaction. – Solomon Rutzky Dec 09 '14 at 06:07
1

@srutzky That was my setting in SSMS. Answer edited. – Sarvesh Mishra Dec 09 '14 at 06:25

How to insert large amount of data into SQL Server 2008 with c# code?

6 Answers6