Update Excel Database Records
Converting Excel `WorkBook.ToDataSet()` allows .Net developers to first manipulate the dataset, and then use it to update SQL records very easily using `System.Data`.
using System.Data; using System.Data.SqlClient; using IronXL; WorkBook wb = WorkBook.Load("test.xlsx"); DataSet dataSet = wb.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); //Important - you can (and probably should) modify / crop the data in the dataset here to match the shape of your SQL query //Update the values in database using the values in Excel adapter.Update(dataSet); }
Imports System.Data Imports System.Data.SqlClient Imports IronXL Private wb As WorkBook = WorkBook.Load("test.xlsx") Private dataSet As DataSet = wb.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) 'Important - you can (and probably should) modify / crop the data in the dataset here to match the shape of your SQL query 'Update the values in database using the values in Excel adapter.Update(dataSet) End Using
Converting Excel `WorkBook.ToDataSet()` allows .Net developers to first manipulate the dataset, and then use it to update SQL records very easily using `System.Data`.