Batch add data inside .xlsx inside Azure SQL using .net console application

Question

I have this code which reads data from .xlsx and update SQL inside Azure :-

using System;

using System.Data;

using Microsoft.Data.SqlClient;

using System.IO;

using ExcelDataReader;

class Program

{

static void Main()

{

    string excelFilePath = @"C:\****.xlsx"; // Update with your file path

    string connectionString = "Server=*******";

    // Read data from Excel file

    DataTable dataTable = ReadExcelFile(excelFilePath);

    if (dataTable.Rows.Count > 0)

    {

        // Insert or Update SQL Server table

        UpsertDataToSqlServer(dataTable, connectionString);

        Console.WriteLine("Data successfully inserted/updated in SQL Server!");

    }

    else

    {

        Console.WriteLine("No data found in Excel file.");

    }

}

static DataTable ReadExcelFile(string filePath)

{

    DataTable dt = new DataTable();

    System.Text.Encoding.RegisterProvider(System.Text.CodePagesEncodingProvider.Instance); // Needed for ExcelDataReader

    using (var stream = File.Open(filePath, FileMode.Open, FileAccess.Read))

    {

        using (var reader = ExcelReaderFactory.CreateReader(stream))

        {

            DataSet result = reader.AsDataSet(new ExcelDataSetConfiguration

            {

                ConfigureDataTable = (_) => new ExcelDataTableConfiguration { UseHeaderRow = true }

            });

            dt = result.Tables[0]; // Assuming data is in the first sheet

        }

    }

    return dt;

}

static void UpsertDataToSqlServer(DataTable dataTable, string connectionString)

{

    using (SqlConnection conn = new SqlConnection(connectionString))

    {

        conn.Open();

        foreach (DataRow row in dataTable.Rows)

        {

            using (SqlCommand cmd = new SqlCommand(@"


                

                    INSERT INTO LeadsData (Id, FirstAssignmentDistributionUser, BrokerAssigned, Milestone2, UTM_Content, Keyword_Placement, LeadSource, subid,Created,Modified)

                    VALUES (@Id, @FirstAssignmentDistributionUser, @BrokerAssigned, @Milestone2, @UTM_Content, @Keyword_Placement, @LeadSource, @subid,getdate(),getdate());

            ", conn))

            {

                cmd.Parameters.AddWithValue("@Id", row["Id"] ?? DBNull.Value);

                cmd.Parameters.AddWithValue("@FirstAssignmentDistributionUser", row["First Assignment / Distribution User"] ?? DBNull.Value);

                cmd.Parameters.AddWithValue("@BrokerAssigned", row["Broker Assigned"] ?? DBNull.Value);

                cmd.Parameters.AddWithValue("@Milestone2", row["Milestone2"] ?? DBNull.Value);

                cmd.Parameters.AddWithValue("@UTM_Content", row["UTM_Content"] ?? DBNull.Value);

                cmd.Parameters.AddWithValue("@Keyword_Placement", row["Keyword/Placement"] ?? DBNull.Value);

                cmd.Parameters.AddWithValue("@LeadSource", row["Lead Source"] ?? DBNull.Value);

                cmd.Parameters.AddWithValue("@subid", row["subid"] ?? DBNull.Value);

                cmd.ExecuteNonQuery();

            }

        }

    }

}
```}

this is working well, but it is adding one row each time, but since we have around 1.5 millions items inside excel, so i want to speed up the process? can i from example update SQL in bulk? Thanks

Answer 1

There are several options, but I don't think BULK INSERT is the most practical one, since you have the data in Excel.

Rather than sending one row at time, you can send a reasonable chunk a time with help of a table variable. I have an article on my web site that shows how to do that: Using Table-Valued Parameters in SQL Server and .NET.