Insert chunk using linq to sql?

Question

in a c# application, I need to insert into sql server 2005 table a lot or records. I split datatable in 2000 records chunk, using linq to sql server but dont work good becasue dont stop ever! I've 900.000 recors and insert a lot more thant this. What I'm doing wrong? This is my code:

int jump = 0;
while (ds.Tables[0].Rows.Count < ds.Tables[0].Rows.Count + 1)
{
    String xmlData = ConvertDataTableToXML(ds.Tables[0].AsEnumerable().Skip(jump).Take(2000 + jump).CopyToDataTable());
    jump = jump + 2001;

    SqlConnection conn = new SqlConnection
        ("Data Source=SERVER;Initial Catalog=DATABASE;Persist Security Info=True;User ID=USER;Password=PASS;");
    conn.Open();
    SqlCommand insert = new SqlCommand
        ("sp_InsertData'" + xmlData + "'", conn);
    insert.ExecuteNonQuery();
    conn.Close();
}

You don't need to open an close the connection on each iteration. Profile the SP. Also, define a variable with ds.Tables[0].AsEnumerable() so you don't have to do it on each iteration. — Francisco Goldenstein, Jul 28 '15 at 18:08
are you inserting the same data..? you are not incrementing a counter anywhere in your loop which is actually structured incorrectly — MethodMan, Jul 28 '15 at 18:10
Ok, you're right! but I think this is no my problem, I think some thing wrong is in my while or in my linq query. What do you think? Tanks! — Patricio, Jul 28 '15 at 18:11
@MethodMan yes, but I dont want to do that. I just need to split my datatable and do a 2000 records insert. — Patricio, Jul 28 '15 at 18:13
you can do a 100,000 record insert if you structure your stored procedure along with a temp table to match your xml schema and insert all the records in a single call try googling for something like `C# bulk insert into sql server database xml and temp table` I do something like this for way more records currently on a daily basis and I don't even need to implement a `While Loop` — MethodMan, Jul 28 '15 at 18:15
@MethodMan thanks! but I cant allowed to use bulk insert, so I need to do insert using chunk short size. — Patricio, Jul 28 '15 at 18:19
@Patricio you don't have to use SQL Servers Bulk Insert .. I am telling you that I do bulk inserts by using XML which bulk inserts are not enabled on our server and devised a work around by creating a temp table and passing xml data to the temp table would you like to see an example perhaps .. there are many ways to skin a cat.. I will post an example along with an explanation on how this works it's fast and once again you do not have to have bulk inserts enabled on the server.. — MethodMan, Jul 28 '15 at 18:21

MethodMan · Answer 1 · 2015-07-28T19:00:15.910

if you understand Temp Tables in SQL Server as well as how to use the OPENXML command you can try the following. it works even if your DBA disables BULK INSERTS

substitute my field names with your own and the field mappings in this portion of the code should match your tables schema / field definitions / data types FROM OPENXML (@xmlHandle, '/NewDataSet/XMLDataTable',1) WITH (

ALTER PROCEDURE [dbo].[sp_InsertData]
(@xmlString VARCHAR(MAX))
AS
BEGIN
    /* Initialize a handle for the XmlDocument */ 
    DECLARE @xmlHandle INT 

    /* 
        Created by @MethodMan you first want to create / declare a TEMP TABLE which 
        Mimic's the structure of the Target table that you are inserting into
    */ 

    DECLARE @someTargetTable TABLE 
    ( 
        [EN_INTFC_ID]           varchar(25), 
        [EN_INTFC_LINE_NUM]     varchar(5), 
        [EN_BILL_SOURCE_ID]     varchar(10),
        [EN_BUSINESS_UNIT]      varchar(12),
        [EN_ASSET_NAME]         varchar(4),
        [EN_POSTING_DATE]       DateTime,
        [EN_FISCAL_YEAR]        varchar(4),
        [EN_FISCAL_PERIOD]      varchar(3),
        [EN_CUSTOMER_ID]        varchar(50),
        [EN_DOC_TYPE]           varchar(4),
        [EN_TARGET_INVOICE]     varchar(16),
        [EN_INVOICE_DT]         DateTime,
        [EN_REVNUE_TYPE]        varchar(15),
        [EN_QTY]                decimal(15,0),
        [EN_GROSS_EXT_AMT]      decimal(25,2),
        [EN_DESCR]              varchar(50),
        [EN_CONTRACT]           varchar(20),
        [EN_PRODUCT_TYPE]       varchar(15),
        [EN_UNIT_OF_MEASURE]    varchar(3)
    ) 

    /* 
    Create the XmlDocument using the handle above and the Xml 
    string as parameters. If your stored procedure has an varchar input 
    parameter named @xmlString, it would look like this instead: 
    EXEC sp_xml_preparedocument @xmlHandle output,@xmlString 
    */ 
    EXEC sp_xml_preparedocument @xmlHandle output, @xmlString 


    /* 
    Use the OPENXML method to query the XmlDocument starting at 
    /NewDataSet/SampleDataTable node.
    */ 
    INSERT INTO @someTargetTable 
    SELECT  [EN_INTFC_ID],  
            [EN_INTFC_LINE_NUM],  
            [EN_BILL_SOURCE_ID],     
            [EN_BUSINESS_UNIT],     
            [EN_ASSET_NAME],    
            [EN_POSTING_DATE], 
            [EN_FISCAL_YEAR],   
            [EN_FISCAL_PERIOD], 
            [EN_CUSTOMER_ID],     
            [EN_DOC_TYPE],    
            [EN_TARGET_INVOICE],   
            [EN_INVOICE_DT],    
            [EN_REVNUE_TYPE],
            [EN_QTY],   
            [EN_GROSS_EXT_AMT],  
            [EN_DESCR],
            [EN_CONTRACT],
            [EN_PRODUCT_TYPE],
            [EN_UNIT_OF_MEASURE] 
    FROM OPENXML (@xmlHandle, '/NewDataSet/XMLDataTable',1) 
        WITH (
            [EN_INTFC_ID]         varchar(25) '@EN_INTFC_ID', 
            [EN_INTFC_LINE_NUM]   varchar(5) '@EN_INTFC_LINE_NUM', 
            [EN_BILL_SOURCE_ID]   varchar(10) '@EN_BILL_SOURCE_ID',
            [EN_BUSINESS_UNIT]    varchar(12) '@EN_BUSINESS_UNIT',
            [EN_ASSET_NAME]       varchar(4) '@EN_ASSET_NAME',
            [EN_POSTING_DATE]     DateTime '@EN_POSTING_DATE',
            [EN_FISCAL_YEAR]      varchar(4) '@EN_FISCAL_YEAR',
            [EN_FISCAL_PERIOD]    varchar(3) '@EN_FISCAL_PERIOD',
            [EN_CUSTOMER_ID]      varchar(50) '@EN_CUSTOMER_ID',
            [EN_DOC_TYPE]         varchar(4) '@EN_DOC_TYPE',
            [EN_TARGET_INVOICE]   varchar(16) '@EN_TARGET_INVOICE',
            [EN_INVOICE_DT]       DateTime '@EN_INVOICE_DT',
            [EN_REVNUE_TYPE]      varchar(15) '@EN_REVNUE_TYPE',
            [EN_QTY]              decimal(15,0) '@EN_QTY',
            [EN_GROSS_EXT_AMT]    decimal(25,2) '@EN_GROSS_EXT_AMT',
            [EN_DESCR]            varchar(50) '@EN_DESCR',
            [EN_CONTRACT]         varchar(20) '@EN_CONTRACT',
            [EN_PRODUCT_TYPE]     varchar(15)   '@EN_PRODUCT_TYPE',
            [EN_UNIT_OF_MEASURE] varchar(3)  '@EN_UNIT_OF_MEASURE'
             )

    /*Insert the records into the table variable  */ 

    INSERT INTO Your_Actual_Table_Name (
           [EN_INTFC_ID],  
           [EN_INTFC_LINE_NUM],  
           [EN_BILL_SOURCE_ID],     
           [EN_BUSINESS_UNIT],     
           [EN_ASSET_NAME],    
           [EN_POSTING_DATE], 
           [EN_FISCAL_YEAR],   
           [EN_FISCAL_PERIOD], 
           [EN_CUSTOMER_ID],     
           [EN_DOC_TYPE],    
           [EN_TARGET_INVOICE],   
           [EN_INVOICE_DT],    
           [EN_REVNUE_TYPE],
           [EN_QTY],   
           [EN_GROSS_EXT_AMT],  
           [EN_DESCR],
           [EN_CONTRACT],
           [EN_PRODUCT_TYPE],
           [EN_UNIT_OF_MEASURE] ) 
    (SELECT [EN_INTFC_ID],  
           [EN_INTFC_LINE_NUM],  
           [EN_BILL_SOURCE_ID],     
           [EN_BUSINESS_UNIT],     
           [EN_ASSET_NAME],    
           [EN_POSTING_DATE], 
           [EN_FISCAL_YEAR],   
           [EN_FISCAL_PERIOD], 
           [EN_CUSTOMER_ID],     
           [EN_DOC_TYPE],    
           [EN_TARGET_INVOICE],   
           [EN_INVOICE_DT],    
           [EN_REVNUE_TYPE],
           [EN_QTY],   
           [EN_GROSS_EXT_AMT],  
           [EN_DESCR],
           [EN_CONTRACT],
           [EN_PRODUCT_TYPE],
           [EN_UNIT_OF_MEASURE] 
    FROM @someTargetTable)
    /* Remove the document from memory */ 
    EXEC sp_xml_removedocument @xmlHandle
END

// your sql command below.
SqlCommand insert = new SqlCommand ("sp_InsertData '" + xmlData + "'", conn); 
insert.CommandTimeout = 5000; 
insert.ExecuteNonQuery(); 
conn.Close()

// my code and how you can utilize the using(){} statement along with code to convert a DataTable to XML then pass that xml to the stored procedure which I have depicted above

private bool ProcessSomeDataTableToXML(DataTable dataTable)
{
    String xmlData = ConvertDataTableToXML(dataTable);
    var ConnString = System.Configuration.ConfigurationManager.ConnectionStrings["yourdatabase"].ConnectionString;
    using (SqlConnection connection = new SqlConnection(ConnString))
    {
        using (SqlCommand command = new SqlCommand("sp_InsertData '" + xmlData + "'", connection))
        {
            connection.Open();
            try
            {
                command.ExecuteNonQuery();
                fileInserted = true;
            }
            catch (SqlException sqlEx)
            {
                fileInserted = false;

                Console.WriteLine(sqlEx.Message);
            }
        }
    }
    return fileInserted;
}

private static string ConvertDataTableToXML(DataTable dtData)
{
    DataSet dsData = new DataSet();
    StringBuilder sbSQL;
    StringWriter swSQL;
    string XMLformat;
    try
    {
        sbSQL = new StringBuilder();
        swSQL = new StringWriter(sbSQL);
        dsData.Merge(dtData, true, MissingSchemaAction.AddWithKey);
        dsData.Tables[0].TableName = "XMLDataTable";
        foreach (DataColumn col in dsData.Tables[0].Columns)
        {
            col.ColumnMapping = MappingType.Attribute;
        }
        dsData.WriteXml(swSQL, XmlWriteMode.WriteSchema);
        XMLformat = sbSQL.ToString();
        sbSQL = null;
        swSQL = null;
        return XMLformat;
    }
    catch (Exception sysException)
    {
        throw sysException;
    }
}

thank you very much! I'm doing exactly like you say. But when I do insert.ExecuteNonQuery() I get throw exception because take a lot of time. I use insert.CommandTimeout = 5000 but, is this a correct way? Thank you very much again! — Patricio, Jul 28 '15 at 18:39
I would need to see your exact stored procedure.. I am doing this with the default commandtime out and never have issues.. if I could see your stored procedure I wonder if I could spot the errors .. also I think that the way that you are calling your execute is incorrect — MethodMan, Jul 28 '15 at 18:41
I use this example: [link](http://www.codeproject.com/Articles/30722/How-to-save-data-from-a-DataSet-into-a-T-SQL-table) Same as your example. And I send xml with this code: SqlCommand insert = new SqlCommand ("sp_InsertData'" + xmlData + "'", conn); insert.CommandTimeout = 5000; insert.ExecuteNonQuery(); conn.Close(); — Patricio, Jul 28 '15 at 18:45
I would recommend wrapping your code around a using but I added a space in your connection so try the following .. look at my update `Notice the space I added between the sp_InsertData '"` — MethodMan, Jul 28 '15 at 18:52
i will add another method that you can also use to easily convert your DataTable to XML.. then pass the XML to the procedure.. I will paste my exact code that I am using which I just test and also works.. I hope this helps — MethodMan, Jul 28 '15 at 18:55
Thanks @methodman, I get "'System.OutOfMemoryException'." importing 900.000 records. This is when I convert datatable to xml method ConvertDataTableToXML(DataTable dtData) (this [link](http://www.codeproject.com/Articles/30722/How-to-save-data-from-a-DataSet-into-a-T-SQL-table) — Patricio, Jul 28 '15 at 19:03
you will need to use the same code in regards to the `Skip(jump).Take(2000 + jump)` the thing you need to do is jump needs to be incremented properly through each iteration check out this link for a good example as well http://stackoverflow.com/questions/15414347/how-to-loop-through-ienumerable-in-batches — MethodMan, Jul 28 '15 at 19:05
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/84499/discussion-between-methodman-and-patricio). — MethodMan, Jul 28 '15 at 19:11

score 2 · Answer 2 · answered Jul 28 '15 at 18:10

2

An obvious problem is in your while condition:

ds.Tables[0].Rows.Count < ds.Tables[0].Rows.Count + 1

i.e. count < count + 1

i.e. true

Your loop is designed never to stop. You might change it to

while (jump < ds.Tables[0].Rows.Count)

answered Jul 28 '15 at 18:10

Brian

3,850
3
21
37

score 2 · Answer 3 · answered Jul 28 '15 at 18:11

2

1) You have an infite loop (ds.Tables[0].Rows.Count < ds.Tables[0].Rows.Count + 1)

2) Connection management: You don't need to open an close the connection on each iteration. Use a using block for disposing the connection once you are done with it.

answered Jul 28 '15 at 18:11

Francisco Goldenstein

13,299
7
58
74

To be clear - the `using` block should be _outside_ the loop - otherwise you're _still_ creating, opening, and closing a connection each time (it's just hidden by the `using` block. – D Stanley Jul 28 '15 at 18:13
Of course, the connection should also be opened outside the loop. That's the only way to avoid opening and closing the connection on each iteration. – Francisco Goldenstein Jul 28 '15 at 18:15

Insert chunk using linq to sql?

3 Answers3