locked
How to update large quantity of data in database using C#? RRS feed

  • Question

  • How to update large quantity of data in database using C#? Could you give some code or sample to me?

    I would appreciate it.

    Thursday, November 12, 2015 1:29 PM

Answers

  • Hi Greed,

    You can have a performance gain with the bulk insert and massive update if the amount of rows is big, compared to updating the data row by row.

    Here is a example,

    public static void UpdateData<T>(List<T> list,string TableName)
    {
        DataTable dt = new DataTable("MyTable");
        dt = ConvertToDataTable(list);
        ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.PerUserRoamingAndLocal);
        using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["SchoolSoulDataEntitiesForReport"].ConnectionString))
        {
            using (SqlCommand command = new SqlCommand("", conn))
            {
                try
                {
                    conn.Open();
    
                    //Creating temp table on database
                    command.CommandText = "CREATE TABLE #TmpTable(...)";
                    command.ExecuteNonQuery();
    
                    //Bulk insert into temp table
                    using (SqlBulkCopy bulkcopy = new SqlBulkCopy(conn))
                    {
                        bulkcopy.BulkCopyTimeout = 660;
                        bulkcopy.DestinationTableName = TableName;
                        bulkcopy.WriteToServer(dt);
                        bulkcopy.Close();
                    }
    
                    // Updating destination table, and dropping temp table
                    command.CommandTimeout = 300;
                    command.CommandText = "UPDATE T SET ... FROM " + TableName + " T INNER JOIN #TmpTable Temp ON ...; DROP TABLE #TmpTable;";
                    command.ExecuteNonQuery();
                }
                catch (Exception ex)
                {
                    // Handle exception properly
                }
                finally
                {
                    conn.Close();
                }
            }
        }
    }

    Best regards,

    Kristin


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Proposed as answer by ArifSolkar Friday, November 13, 2015 6:53 AM
    • Unproposed as answer by ArifSolkar Friday, November 13, 2015 6:53 AM
    • Marked as answer by GreeeeeeeeeeeeeD Friday, November 13, 2015 9:57 AM
    Friday, November 13, 2015 2:49 AM
  • It depends on the source of data...are you holding the data in any object or you have data in some csv or xml file.

    you can use bulkcopy

    see sample code here

    http://www.aspsnippets.com/Articles/SqlBulkCopy--Bulk-Insert-records-and-Update-existing-rows-if-record-exists-using-C-and-VBNet.aspx

    http://www.jarloo.com/c-bulk-upsert-to-sql-server-tutorial/



    Mark ANSWER if this reply resolves your query, If helpful then VOTE HELPFUL
    INSQLSERVER.COM Mohammad Nizamuddin

    Thursday, November 12, 2015 2:00 PM

All replies

  • It depends on the source of data...are you holding the data in any object or you have data in some csv or xml file.

    you can use bulkcopy

    see sample code here

    http://www.aspsnippets.com/Articles/SqlBulkCopy--Bulk-Insert-records-and-Update-existing-rows-if-record-exists-using-C-and-VBNet.aspx

    http://www.jarloo.com/c-bulk-upsert-to-sql-server-tutorial/



    Mark ANSWER if this reply resolves your query, If helpful then VOTE HELPFUL
    INSQLSERVER.COM Mohammad Nizamuddin

    Thursday, November 12, 2015 2:00 PM
  • C# is not appropriate for this in most cases. If you need to import data then use SQL Bulk Copy or SSIS. If you need to transform existing data then use SQL and SSMS or SSIS.

    Michael Taylor
    http://blogs.msmvps.com/p3net

    • Proposed as answer by Christopher84 Thursday, November 12, 2015 3:09 PM
    Thursday, November 12, 2015 3:00 PM
  • Hi Greed,

    You can have a performance gain with the bulk insert and massive update if the amount of rows is big, compared to updating the data row by row.

    Here is a example,

    public static void UpdateData<T>(List<T> list,string TableName)
    {
        DataTable dt = new DataTable("MyTable");
        dt = ConvertToDataTable(list);
        ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.PerUserRoamingAndLocal);
        using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["SchoolSoulDataEntitiesForReport"].ConnectionString))
        {
            using (SqlCommand command = new SqlCommand("", conn))
            {
                try
                {
                    conn.Open();
    
                    //Creating temp table on database
                    command.CommandText = "CREATE TABLE #TmpTable(...)";
                    command.ExecuteNonQuery();
    
                    //Bulk insert into temp table
                    using (SqlBulkCopy bulkcopy = new SqlBulkCopy(conn))
                    {
                        bulkcopy.BulkCopyTimeout = 660;
                        bulkcopy.DestinationTableName = TableName;
                        bulkcopy.WriteToServer(dt);
                        bulkcopy.Close();
                    }
    
                    // Updating destination table, and dropping temp table
                    command.CommandTimeout = 300;
                    command.CommandText = "UPDATE T SET ... FROM " + TableName + " T INNER JOIN #TmpTable Temp ON ...; DROP TABLE #TmpTable;";
                    command.ExecuteNonQuery();
                }
                catch (Exception ex)
                {
                    // Handle exception properly
                }
                finally
                {
                    conn.Close();
                }
            }
        }
    }

    Best regards,

    Kristin


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Proposed as answer by ArifSolkar Friday, November 13, 2015 6:53 AM
    • Unproposed as answer by ArifSolkar Friday, November 13, 2015 6:53 AM
    • Marked as answer by GreeeeeeeeeeeeeD Friday, November 13, 2015 9:57 AM
    Friday, November 13, 2015 2:49 AM
  • Hi Greed,

    You can use SQL bulk data update.

    Here is the sample code.

    https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopy.aspx

    Important point that you should consider is:

    1. First create the table in the DB where you want to update.
    2. The column order from the source should match the destination table in the DB.
    3. Datatype of the source column should also match with the destination table.

    AS

    Friday, November 13, 2015 7:04 AM