How to Import Excel to SQL Server in C#

Introduction

In many different business contexts, importing data from Excel into SQL Server is a typical necessity. Reading data from an Excel file and entering it into an SQL Server database are the tasks involved in this activity. While the export wizard is often used, IronXL provides a more programmatic and flexible approach to data handling. IronXL is a powerful C# library that can import Excel data from files; therefore, it's possible to expedite this operation. To that end, this post will offer a thorough how-to guide that addresses the configuration, execution, and enhancement of the import Excel to SQL Server using C#.

How to Import Excel to SQL Server in C#

1. Set Up Your Development Environment
2. Prepare Your Excel File
3. Connect to Your SQL Server Database
4. Read Data from Excel Files Using IronXL
5. Export Data and Generate a PDF Report Using IronPDF
6. Review the PDF Report

What is IronXL?

IronXL, sometimes referred to as IronXL.Excel, is a feature-rich C# library made to make working with Excel files in .NET applications easier. This robust tool is ideal for server-side applications since it enables developers to read, create, and edit Excel files without needing to install Microsoft Excel on the computer. Excel 2007 and later (.xlsx) and Excel 97–2003 (.xls) formats are supported by IronXL, providing versatility in managing various Excel file versions. It allows for significant data manipulation, such as manipulating worksheets, rows, and columns in addition to inserting, updating, and removing data.

IronXL also supports cell formatting and Excel formulas, enabling the programmed generation of complex and well-formatted spreadsheets. With its performance optimization and compatibility with multiple .NET platforms, including .NET Framework, .NET Core, and .NET 5/6, IronXL guarantees effective handling of huge datasets. It is a flexible option for developers wishing to integrate Excel file operations into their applications, whether for straightforward data import/export activities or intricate reporting systems, thanks to its smooth interface with other .NET frameworks.

Key Features

Read and Write Excel Files

Developers can read and write data to and from Excel files using IronXL. It's simple to make new Excel files and edit ones that already exist.

No Installation Required for Microsoft Excel

IronXL does not require the installation of Microsoft Excel on the computer that is hosting the application, in contrast to certain other libraries. It's perfect for server-side apps because of this.

Support for Various Excel Formats

The library offers versatility in managing various Excel file types by supporting the .xls (Excel 97-2003) and .xlsx (Excel 2007 and later) formats.

Create a New Visual Studio Project

A Visual Studio console project is simple to create. In Visual Studio, take the following actions to create a Console Application:

1. Open Visual Studio: Make sure you have installed Visual Studio on your computer before opening it.
2. Start a New Project: Choose File -> New -> Project.

3. From the Create a new project box's left panel, choose your preferred programming language—for example, C#.
4. Select the Console App or Console App (.NET Core) template from the list of available project templates.
5. In the Name area, give your project a name.

6. Decide on a location to save the project.
7. Click Create to launch an application project for a Console.

Installing IronXL Library

Installing the IronXL library is required because of the upcoming update. Lastly, to finish the procedure, launch the NuGet Package Manager Console and type the following command:

Install-Package IronXL.Excel

Using the NuGet Package Manager to search for the IronXL package is another method. This allows us to select which of the NuGet packages linked to IronXL to download.

Import Excel to SQL with IronXL

Reading Data from Excel Using IronXL

The process of reading data from Excel files is made easier with IronXL. The example that follows shows you how to use IronXL to read data from an Excel file. With this approach, the data is read and saved in a list of dictionaries, each of which corresponds to a row in the Excel sheet.

using IronXL;
using System;
using System.Collections.Generic;

public class ExcelReader
{
    public static List<Dictionary<string, object>> ReadExcelFile(string filePath)
    {
        var data = new List<Dictionary<string, object>>();
        WorkBook workbook = WorkBook.Load(filePath);
        WorkSheet sheet = workbook.WorkSheets[0];

        var headers = new List<string>();
        foreach (var header in sheet.Rows[0].Columns)
        {
            headers.Add(header.ToString());
        }

        for (int i = 1; i < sheet.Rows.Count; i++)
        {
            var rowData = new Dictionary<string, object>();
            for (int j = 0; j < headers.Count; j++)
            {
                rowData[headers[j]] = sheet.Rows[i][j].Value;
            }
            data.Add(rowData);
        }

        return data;
    }
}

using IronXL;
using System;
using System.Collections.Generic;

public class ExcelReader
{
    public static List<Dictionary<string, object>> ReadExcelFile(string filePath)
    {
        var data = new List<Dictionary<string, object>>();
        WorkBook workbook = WorkBook.Load(filePath);
        WorkSheet sheet = workbook.WorkSheets[0];

        var headers = new List<string>();
        foreach (var header in sheet.Rows[0].Columns)
        {
            headers.Add(header.ToString());
        }

        for (int i = 1; i < sheet.Rows.Count; i++)
        {
            var rowData = new Dictionary<string, object>();
            for (int j = 0; j < headers.Count; j++)
            {
                rowData[headers[j]] = sheet.Rows[i][j].Value;
            }
            data.Add(rowData);
        }

        return data;
    }
}

Imports IronXL
Imports System
Imports System.Collections.Generic

Public Class ExcelReader
	Public Shared Function ReadExcelFile(ByVal filePath As String) As List(Of Dictionary(Of String, Object))
		Dim data = New List(Of Dictionary(Of String, Object))()
		Dim workbook As WorkBook = WorkBook.Load(filePath)
		Dim sheet As WorkSheet = workbook.WorkSheets(0)

		Dim headers = New List(Of String)()
		For Each header In sheet.Rows(0).Columns
			headers.Add(header.ToString())
		Next header

		For i As Integer = 1 To sheet.Rows.Count - 1
			Dim rowData = New Dictionary(Of String, Object)()
			For j As Integer = 0 To headers.Count - 1
				rowData(headers(j)) = sheet.Rows(i)(j).Value
			Next j
			data.Add(rowData)
		Next i

		Return data
	End Function
End Class

Connecting to SQL Server

Use the SqlConnection class from the System.Data.SqlClient namespace to establish a connection to SQL Server. Make sure you have the right connection string, which normally consists of the database name, server name, and authentication information. How to connect to a SQL Server database and add data is covered in the following example.

using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;

public class SqlServerConnector
{
    private string connectionString;

    public SqlServerConnector(string connectionString)
    {
        this.connectionString = connectionString;
    }

    public void InsertData(Dictionary<string, object> data, string tableName)
    {
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            connection.Open();
            var columns = string.Join(",", data.Keys);
            var parameters = string.Join(",", data.Keys.Select(key => "@" + key));
            string query = $"INSERT INTO {tableName} ({columns}) VALUES ({parameters})";

            using (SqlCommand command = new SqlCommand(query, connection))
            {
                foreach (var kvp in data)
                {
                    command.Parameters.AddWithValue("@" + kvp.Key, kvp.Value ?? DBNull.Value);
                }
                command.ExecuteNonQuery();
            }
        }
    }
}

using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;

public class SqlServerConnector
{
    private string connectionString;

    public SqlServerConnector(string connectionString)
    {
        this.connectionString = connectionString;
    }

    public void InsertData(Dictionary<string, object> data, string tableName)
    {
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            connection.Open();
            var columns = string.Join(",", data.Keys);
            var parameters = string.Join(",", data.Keys.Select(key => "@" + key));
            string query = $"INSERT INTO {tableName} ({columns}) VALUES ({parameters})";

            using (SqlCommand command = new SqlCommand(query, connection))
            {
                foreach (var kvp in data)
                {
                    command.Parameters.AddWithValue("@" + kvp.Key, kvp.Value ?? DBNull.Value);
                }
                command.ExecuteNonQuery();
            }
        }
    }
}

Imports System
Imports System.Collections.Generic
Imports System.Data.SqlClient
Imports System.Linq

Public Class SqlServerConnector
	Private connectionString As String

	Public Sub New(ByVal connectionString As String)
		Me.connectionString = connectionString
	End Sub

	Public Sub InsertData(ByVal data As Dictionary(Of String, Object), ByVal tableName As String)
		Using connection As New SqlConnection(connectionString)
			connection.Open()
			Dim columns = String.Join(",", data.Keys)
			Dim parameters = String.Join(",", data.Keys.Select(Function(key) "@" & key))
			Dim query As String = $"INSERT INTO {tableName} ({columns}) VALUES ({parameters})"

			Using command As New SqlCommand(query, connection)
				For Each kvp In data
					command.Parameters.AddWithValue("@" & kvp.Key, If(kvp.Value, DBNull.Value))
				Next kvp
				command.ExecuteNonQuery()
			End Using
		End Using
	End Sub
End Class

Combining IronXL with SQL Server

Once the logic for reading Excel files and inserting data into an SQL database has been established, integrate these features to finish the import process. The application that follows receives information from an Excel file and adds it to a Microsoft SQL Server database.

using System;
using System.Collections.Generic;

class Program
{
    static void Main(string[] args)
    {
        string excelFilePath = "path_to_your_excel_file.xlsx";
        string connectionString = "your_sql_server_connection_string";
        string tableName = "your_table_name";

        // Read data from Excel
        List<Dictionary<string, object>> excelData = ExcelReader.ReadExcelFile(excelFilePath);

        // Insert data into SQL Server
        SqlServerConnector sqlConnector = new SqlServerConnector(connectionString);
        foreach (var row in excelData)
        {
            sqlConnector.InsertData(row, tableName);
        }

        Console.WriteLine("Data import completed successfully.");
    }
}

using System;
using System.Collections.Generic;

class Program
{
    static void Main(string[] args)
    {
        string excelFilePath = "path_to_your_excel_file.xlsx";
        string connectionString = "your_sql_server_connection_string";
        string tableName = "your_table_name";

        // Read data from Excel
        List<Dictionary<string, object>> excelData = ExcelReader.ReadExcelFile(excelFilePath);

        // Insert data into SQL Server
        SqlServerConnector sqlConnector = new SqlServerConnector(connectionString);
        foreach (var row in excelData)
        {
            sqlConnector.InsertData(row, tableName);
        }

        Console.WriteLine("Data import completed successfully.");
    }
}

Imports System
Imports System.Collections.Generic

Friend Class Program
	Shared Sub Main(ByVal args() As String)
		Dim excelFilePath As String = "path_to_your_excel_file.xlsx"
		Dim connectionString As String = "your_sql_server_connection_string"
		Dim tableName As String = "your_table_name"

		' Read data from Excel
		Dim excelData As List(Of Dictionary(Of String, Object)) = ExcelReader.ReadExcelFile(excelFilePath)

		' Insert data into SQL Server
		Dim sqlConnector As New SqlServerConnector(connectionString)
		For Each row In excelData
			sqlConnector.InsertData(row, tableName)
		Next row

		Console.WriteLine("Data import completed successfully.")
	End Sub
End Class

This class is in charge of using IronXL to read the data from the given Excel file. The ReadExcelFile function loads the Excel workbook, opens the first worksheet, and gathers data by looping through the rows of the data worksheet. To facilitate handling the tables, the information is kept in a list of dictionaries.

The data is inserted into the designated database table by this class, which also manages the connection to the SQL Server database. The InsertData method employs parameterized queries to prevent SQL injection and builds an SQL INSERT query dynamically based on the dictionary's keys, which stand in for column names.

Using the ExcelReader class to read the data into the SQL table from the Excel file and the SqlServerConnector class to insert each row into the SQL Server table, the Main function manages the entire process.

Error handling and optimization are crucial for ensuring a robust and efficient import process. Implementing robust error handling can manage potential issues such as missing files, invalid data formats, and SQL exceptions. Here’s an example of incorporating error handling.

try
{
    // Insert the importing logic here
}
catch(Exception ex)
{
    Console.WriteLine("An error occurred: " + ex.Message);
}

try
{
    // Insert the importing logic here
}
catch(Exception ex)
{
    Console.WriteLine("An error occurred: " + ex.Message);
}

Try
	' Insert the importing logic here
Catch ex As Exception
	Console.WriteLine("An error occurred: " & ex.Message)
End Try

Conclusion

Finally, an effective and reliable method for managing Excel files within .NET applications is to import data from Excel into an MS SQL database using C# and IronXL. IronXL is compatible with multiple Excel formats and has strong capabilities that make reading and writing Excel data easier without the need to install Microsoft Excel. Through the integration of System.Data.SqlClient with IronXL, developers can easily move data between SQL Servers using parameterized queries to improve security and prevent SQL injection.

Finally, adding IronXL and Iron Software to your toolset for .NET development allows you to efficiently manipulate Excel, create PDFs, do OCR, and utilize barcodes. Combining Iron Software's flexible suite with IronXL's simplicity of use, interoperability, and performance guarantees streamlined development and improved application capabilities. With clear license options that are customized to the requirements of the project, developers may select the right model with confidence. By utilizing these benefits, developers can effectively tackle a range of difficulties while maintaining compliance and openness.