Update Excel Database Records

VB C#

using IronXL;
using System.Data;
using System.Data.SqlClient;

// Supported for XLSX, XLS, XLSM, XLTX, CSV and TSV
WorkBook workBook = WorkBook.Load("sample.xlsx");

// Convert the workbook to ToDataSet
DataSet dataSet = workBook.ToDataSet();

// Your sql query
string sql = "SELECT * FROM Users";

// Your connection string
string connectionString = @"Data Source=.\SQLEXPRESS;Initial Catalog=usersdb;Integrated Security=True";
using (SqlConnection connection = new SqlConnection(connectionString))
{
    // Open connections to the database
    connection.Open();
    SqlDataAdapter adapter = new SqlDataAdapter(sql, connection);

    // Update the values in database using the values in Excel
    adapter.Update(dataSet);
}

Imports IronXL
Imports System.Data
Imports System.Data.SqlClient

' Supported for XLSX, XLS, XLSM, XLTX, CSV and TSV
Private workBook As WorkBook = WorkBook.Load("sample.xlsx")

' Convert the workbook to ToDataSet
Private dataSet As DataSet = workBook.ToDataSet()

' Your sql query
Private sql As String = "SELECT * FROM Users"

' Your connection string
Private connectionString As String = "Data Source=.\SQLEXPRESS;Initial Catalog=usersdb;Integrated Security=True"
Using connection As New SqlConnection(connectionString)
	' Open connections to the database
	connection.Open()
	Dim adapter As New SqlDataAdapter(sql, connection)

	' Update the values in database using the values in Excel
	adapter.Update(dataSet)
End Using

Install-Package IronXL.Excel

Update Excel Database Records

The ToDataSet method of IronXL allows developers to synchronize and update SQL database records directly from Excel data with minimal effort. This functionality provides seamless integration between tabular Excel data and relational databases by converting an Excel workbook into a DataSet. It eliminates the need for manual data entry or complex transformations, significantly streamlining data management workflows.

This feature is particularly useful for applications requiring regular updates to database records from external Excel files, such as customer data, financial records, or inventory management systems. The compatibility with SQL queries and adapters allows developers to map Excel data to existing database schemas. IronXL supports multiple Excel formats (XLSX, CSV, etc.), ensuring versatility across various use cases. By leveraging SqlDataAdapter for bulk updates, this feature reduces data processing time, minimizes errors, and ensures consistency between Excel and database records, making it an ideal solution for enterprise-grade applications.

5 Steps to Update a SQL Database with Data from Excel

var workBook = WorkBook.Load("Products.xlsx");
DataSet dataSet = workBook.ToDataSet();
string sql = "SELECT * FROM products";
using var adapter = new SqlDataAdapter(sql, new SqlConnection("Your Connection String"));
new SqlCommandBuilder(adapter).DataAdapter.Update(dataSet);

// Step 1: Load the Excel file into a WorkBook object
var workBook = WorkBook.Load("Products.xlsx");

// Step 2: Convert the workbook into a DataSet
DataSet dataSet = workBook.ToDataSet();

// Step 3: Define the SQL query to interact with the specific table
string sql = "SELECT * FROM products";

// Step 4: Initialize the SqlDataAdapter with the SQL query and connection
using var adapter = new SqlDataAdapter(sql, new SqlConnection("Your Connection String"));

// SqlCommandBuilder automatically generates SQL commands for updates
new SqlCommandBuilder(adapter);

// Step 5: Update the database with changes from the DataSet
adapter.Update(dataSet);

// Step 1: Load the Excel file into a WorkBook object
var workBook = WorkBook.Load("Products.xlsx");

// Step 2: Convert the workbook into a DataSet
DataSet dataSet = workBook.ToDataSet();

// Step 3: Define the SQL query to interact with the specific table
string sql = "SELECT * FROM products";

// Step 4: Initialize the SqlDataAdapter with the SQL query and connection
using var adapter = new SqlDataAdapter(sql, new SqlConnection("Your Connection String"));

// SqlCommandBuilder automatically generates SQL commands for updates
new SqlCommandBuilder(adapter);

// Step 5: Update the database with changes from the DataSet
adapter.Update(dataSet);

' Step 1: Load the Excel file into a WorkBook object
Dim workBook = WorkBook.Load("Products.xlsx")

' Step 2: Convert the workbook into a DataSet
Dim dataSet As DataSet = workBook.ToDataSet()

' Step 3: Define the SQL query to interact with the specific table
Dim sql As String = "SELECT * FROM products"

' Step 4: Initialize the SqlDataAdapter with the SQL query and connection
Dim adapter = New SqlDataAdapter(sql, New SqlConnection("Your Connection String"))

' SqlCommandBuilder automatically generates SQL commands for updates
Dim tempVar As New SqlCommandBuilder(adapter)

' Step 5: Update the database with changes from the DataSet
adapter.Update(dataSet)

$vbLabelText $csharpLabel

This code demonstrates the process of updating a SQL database with data from an Excel file using IronXL and ADO.NET. The WorkBook.Load method loads the Excel file named Products.xlsx into memory, and the ToDataSet method converts the workbook into a DataSet, which organizes the data into a tabular structure compatible with ADO.NET.

A SQL query is defined to interact with a specific table in the database (products). The SqlDataAdapter is initialized with the query and a connection string, enabling it to act as a bridge between the DataSet and the database. The SqlCommandBuilder is used to automatically generate the necessary SQL commands for inserting, updating, or deleting data in the database. Finally, the adapter.Update(dataSet) method synchronizes the changes in the DataSet (populated from the Excel file) with the corresponding database table. This streamlines the data transfer process and ensures the database reflects the latest changes in the Excel file.

Click here to view the How-to Guide, including examples, sample code, and files