如何从 CSV 文件中读取数据并将其存储在数据库 C# 中

What is a CSV File?

A CSV (Comma-Separated Values) file is a plain text file that contains data separated by commas. It's a popular format for transferring data due to its simplicity and compatibility with various applications, such as Excel.

The Role of SQL Server and Databases

SQL Server is a database management system by Microsoft. It's used to store and manage data in a structured way. In our case, we'll store the CSV data in a SQL Server table.

Prerequisites

1. Visual Studio: Ensure you have Visual Studio installed.
2. SQL Server: You should have SQL Server installed and accessible.
3. IronXL Installation: Install IronXL by running the following NuGet command:

dotnet add package IronXL

Make sure to run these commands within the project directory where you want to install IronXL.

Creating a SQL Server Table

Before importing data, create a destination table in your SQL Server database. This table will store the CSV data.

CREATE TABLE YourTableName (
    Column1 DataType,
    Column2 DataType,
    ...
);

Replace YourTableName, Column1, Column2, and DataType with your specific details.

Step 1: Reading the CSV File

When beginning the task of importing data from a CSV file, the first crucial step is to read the data within it accurately. Each line in a CSV file typically represents a data record, and each record consists of one or more fields, separated by commas.

We then employ the IronXL library to handle the CSV file. To read the CSV file using IronXL, you'll use its WorkBook and WorkSheet classes. The WorkBook class represents an entire spreadsheet, which could be an Excel file or, in our case, a CSV file. When you load the CSV file path into a WorkBook object, IronXL treats the CSV file as a spreadsheet/data table.

using IronXL;
using System.Data;

public class CSVReader
{
    // Reads a CSV file and converts it to a DataTable
    public DataTable ReadCSV(string filePath)
    {
        WorkBook workbook = WorkBook.Load(filePath);
        WorkSheet sheet = workbook.DefaultWorkSheet;
        // Convert to DataTable for easier processing
        DataTable dataTable = sheet.ToDataTable(true); // Set to 'true' if your CSV has a header row
        return dataTable;
    }
}

using IronXL;
using System.Data;

public class CSVReader
{
    // Reads a CSV file and converts it to a DataTable
    public DataTable ReadCSV(string filePath)
    {
        WorkBook workbook = WorkBook.Load(filePath);
        WorkSheet sheet = workbook.DefaultWorkSheet;
        // Convert to DataTable for easier processing
        DataTable dataTable = sheet.ToDataTable(true); // Set to 'true' if your CSV has a header row
        return dataTable;
    }
}

Imports IronXL
Imports System.Data

Public Class CSVReader
	' Reads a CSV file and converts it to a DataTable
	Public Function ReadCSV(ByVal filePath As String) As DataTable
		Dim workbook As WorkBook = WorkBook.Load(filePath)
		Dim sheet As WorkSheet = workbook.DefaultWorkSheet
		' Convert to DataTable for easier processing
		Dim dataTable As DataTable = sheet.ToDataTable(True) ' Set to 'true' if your CSV has a header row
		Return dataTable
	End Function
End Class

Step 2: Establishing a Database Connection

Establishing a connection to your SQL Server database is a fundamental step in the process of storing your CSV data. This step involves setting up a communication link between your application and the database server. A successful connection is crucial because, without it, transferring data to the database would be impossible.

This step focuses on creating and opening a connection using a connection string in C#. The connection string is a vital component as it contains the information needed to establish the connection. It's like a key that unlocks the door to your database.

using System.Data.SqlClient;

public class DatabaseConnector
{
    // Connection string to connect to the database
    private string connectionString = "your_connection_string_here";

    public SqlConnection ConnectToDatabase()
    {
        SqlConnection connection = new SqlConnection(connectionString);
        connection.Open();
        return connection;
    }
}

using System.Data.SqlClient;

public class DatabaseConnector
{
    // Connection string to connect to the database
    private string connectionString = "your_connection_string_here";

    public SqlConnection ConnectToDatabase()
    {
        SqlConnection connection = new SqlConnection(connectionString);
        connection.Open();
        return connection;
    }
}

Imports System.Data.SqlClient

Public Class DatabaseConnector
	' Connection string to connect to the database
	Private connectionString As String = "your_connection_string_here"

	Public Function ConnectToDatabase() As SqlConnection
		Dim connection As New SqlConnection(connectionString)
		connection.Open()
		Return connection
	End Function
End Class

The connectionString variable holds all the necessary details to connect to your SQL Server. It typically includes the server name, database name, user ID, and password. An example connection string looks like this: Server=myServerAddress;Database=myDataBase;User Id=myUsername;Password=myPassword;. It's essential to replace these placeholders with your actual server details.

Step 3: Storing Data in the Database

Having established a connection with the SQL Server database, the next critical step is to store the CSV data in the database. This step involves transferring the data you've read and processed into your SQL Server table. It's a crucial part of the data handling process, as it involves actual data migration from a local file to a database server.

In this step, we'll focus on how the CSV data, now stored in a DataTable, is transferred to the SQL Server database. We use a combination of C# and SQL Server features to accomplish this task efficiently.

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

public class DataImporter
{
    public void ImportData(DataTable dataTable)
    {
        using (SqlConnection connection = new DatabaseConnector().ConnectToDatabase())
        {
            // Check if the table exists and create it if it does not.
            string tableName = "CSVData"; // Use a valid SQL table name format
            string checkTable = $"IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = '{tableName}') BEGIN ";
            string createTable = "CREATE TABLE " + tableName + " (";
            for (int i = 0; i < dataTable.Columns.Count; i++)
            {
                createTable += $"[{dataTable.Columns[i].ColumnName}] NVARCHAR(MAX)";
                if (i < dataTable.Columns.Count - 1)
                    createTable += ", ";
            }
            createTable += ") END";
            SqlCommand createTableCommand = new SqlCommand(checkTable + createTable, connection);
            createTableCommand.ExecuteNonQuery();

            // Now we use SqlBulkCopy to import the data
            using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection))
            {
                bulkCopy.DestinationTableName = tableName;
                try
                {
                    bulkCopy.WriteToServer(dataTable);
                    Console.WriteLine("Data imported successfully!");
                }
                catch (Exception ex)
                {
                    Console.WriteLine(ex.Message);
                }
            }
        }
    }
}

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

public class DataImporter
{
    public void ImportData(DataTable dataTable)
    {
        using (SqlConnection connection = new DatabaseConnector().ConnectToDatabase())
        {
            // Check if the table exists and create it if it does not.
            string tableName = "CSVData"; // Use a valid SQL table name format
            string checkTable = $"IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = '{tableName}') BEGIN ";
            string createTable = "CREATE TABLE " + tableName + " (";
            for (int i = 0; i < dataTable.Columns.Count; i++)
            {
                createTable += $"[{dataTable.Columns[i].ColumnName}] NVARCHAR(MAX)";
                if (i < dataTable.Columns.Count - 1)
                    createTable += ", ";
            }
            createTable += ") END";
            SqlCommand createTableCommand = new SqlCommand(checkTable + createTable, connection);
            createTableCommand.ExecuteNonQuery();

            // Now we use SqlBulkCopy to import the data
            using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection))
            {
                bulkCopy.DestinationTableName = tableName;
                try
                {
                    bulkCopy.WriteToServer(dataTable);
                    Console.WriteLine("Data imported successfully!");
                }
                catch (Exception ex)
                {
                    Console.WriteLine(ex.Message);
                }
            }
        }
    }
}

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

Public Class DataImporter
	Public Sub ImportData(ByVal dataTable As DataTable)
		Using connection As SqlConnection = (New DatabaseConnector()).ConnectToDatabase()
			' Check if the table exists and create it if it does not.
			Dim tableName As String = "CSVData" ' Use a valid SQL table name format
			Dim checkTable As String = $"IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = '{tableName}') BEGIN "
			Dim createTable As String = "CREATE TABLE " & tableName & " ("
			For i As Integer = 0 To dataTable.Columns.Count - 1
				createTable &= $"[{dataTable.Columns(i).ColumnName}] NVARCHAR(MAX)"
				If i < dataTable.Columns.Count - 1 Then
					createTable &= ", "
				End If
			Next i
			createTable &= ") END"
			Dim createTableCommand As New SqlCommand(checkTable & createTable, connection)
			createTableCommand.ExecuteNonQuery()

			' Now we use SqlBulkCopy to import the data
			Using bulkCopy As New SqlBulkCopy(connection)
				bulkCopy.DestinationTableName = tableName
				Try
					bulkCopy.WriteToServer(dataTable)
					Console.WriteLine("Data imported successfully!")
				Catch ex As Exception
					Console.WriteLine(ex.Message)
				End Try
			End Using
		End Using
	End Sub
End Class

It starts by opening a connection to the SQL Server database using the DatabaseConnector class, ensuring a pathway for data transactions. The method checks for the existence of the table named "CSVData" within the database.

If the table is not found, it proceeds to create it. The table's schema is constructed based on the DataTable schema passed to the method, with all columns set to NVARCHAR(MAX) to accommodate any text data. This is a generic approach and may need refinement to match specific data types more closely.

After that, an SQL command is formulated and executed to either verify the existence of the table or to create it. This ensures that the subsequent bulk copy operation has a destination table ready for data insertion. With the table ready, the SqlBulkCopy class is employed to transfer data from the DataTable directly into the SQL Server table. This operation is designed for high-performance bulk data transfers, making it suitable for handling large volumes of data efficiently.

Step 4: Putting It All Together

After diligently working through the earlier steps of reading CSV data, establishing a database connection, and preparing to transfer the data, we arrive at the final and crucial stage: integrating these individual components into a cohesive process.

This integration is done in the Main method of your C# application, where everything comes together, enabling the actual execution of data import from the CSV file to the SQL Server database.

class Program
{
    static void Main(string[] args)
    {
        string filePath = "path_to_your_csv_file.csv";
        CSVReader reader = new CSVReader();
        DataTable dataTable = reader.ReadCSV(filePath);
        DataImporter importer = new DataImporter();
        importer.ImportData(dataTable);
        Console.WriteLine("Data imported successfully!");
    }
}

class Program
{
    static void Main(string[] args)
    {
        string filePath = "path_to_your_csv_file.csv";
        CSVReader reader = new CSVReader();
        DataTable dataTable = reader.ReadCSV(filePath);
        DataImporter importer = new DataImporter();
        importer.ImportData(dataTable);
        Console.WriteLine("Data imported successfully!");
    }
}

Friend Class Program
	Shared Sub Main(ByVal args() As String)
		Dim filePath As String = "path_to_your_csv_file.csv"
		Dim reader As New CSVReader()
		Dim dataTable As DataTable = reader.ReadCSV(filePath)
		Dim importer As New DataImporter()
		importer.ImportData(dataTable)
		Console.WriteLine("Data imported successfully!")
	End Sub
End Class

Replace path_to_your_csv_file.csv with the path to your CSV file.

Running the Project

Once you run the project, you'll see the following output. The success messages represent that all operations have been executed successfully and data is copied to the database.

Data imported successfully!

Now, you can open SQL Server Management Studio (SSMS) and check the table under the database. You'll see the following data in the table.