CODEWITHSUNDEEP
c#sql-serversql-server-2008
barsan
barsan

Reputation: 2491

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

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();
 }

Upvotes: 2

Views: 4054

Answers (6)

Solomon Rutzky
Solomon Rutzky

Reputation: 48776

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.

Upvotes: 1

Sarvesh Mishra
Sarvesh Mishra

Reputation: 2072

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.

Upvotes: 1

Jamaxack
Jamaxack

Reputation: 2460

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

Upvotes: 1

Tejas Vaishnav
Tejas Vaishnav

Reputation: 458

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();
                }
            }

Upvotes: 1

void
void

Reputation: 7880

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)

Upvotes: 1

Riad Baghbanli
Riad Baghbanli

Reputation: 3319

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

Upvotes: 1

Related Questions