C# as a Scripting Language to Import CSV Data into SQL Server

Introduction

Importing data from external sources such as CSV files into SQL Server databases is a common task in database management. Although there are multiple tools and techniques available for this purpose, using C# as a scripting language offers a powerful and flexible approach. In this article, we will explore how to leverage C# to seamlessly import CSV data into SQL Server databases, with practical examples.

Why C#?

C# is a versatile programming language commonly used for developing robust applications, but its capabilities extend beyond traditional software development. Its rich feature set, strong typing system, and extensive libraries make it well-suited for data manipulation tasks, including data import processes. By utilizing C#, developers can benefit from its expressiveness, performance, and integration capabilities when dealing with CSV data and SQL Server.

Step-by-Step Implementation

1. Setting Up Environment: Ensure that you have a working installation of SQL Server and Visual Studio (or any preferred C# IDE) on your system.
2. Creating a C# Console Application: Start by creating a new C# console application in Visual Studio. This will serve as the platform for our scripting solution.
3. Adding Required Libraries: Include necessary namespaces such as System.IO for file operations and System.Data.SqlClient for SQL Server interaction.
4. Reading CSV Data: Write code to read CSV data using StreamReader or other suitable methods. Parse the CSV data into a structured format (e.g., a list of objects or a DataTable).
5. Establishing Database Connection: Create a connection string to connect to the SQL Server database using SqlConnection. Ensure the necessary permissions and security measures are in place.
6. Inserting Data into SQL Server: Using parameterized SQL queries or stored procedures, insert the CSV data into the corresponding SQL Server table. Handle exceptions and error logging appropriately.
7. Testing and Validation: Test the script with sample CSV data to verify its functionality. Validate the imported data in the SQL Server database to ensure accuracy.

Example Script

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

namespace CSVImportScript
{
    class Program
    {
        static void Main(string[] args)
        {
            // Step 4: Reading CSV Data
            DataTable csvData = new DataTable();
            using (StreamReader sr = new StreamReader("data.csv"))
            {
                string[] headers = sr.ReadLine().Split(',');
                foreach (string header in headers)
                {
                    csvData.Columns.Add(header);
                }
                while (!sr.EndOfStream)
                {
                    string[] rows = sr.ReadLine().Split(',');
                    DataRow dr = csvData.NewRow();
                    for (int i = 0; i < headers.Length; i++)
                    {
                        dr[i] = rows[i];
                    }
                    csvData.Rows.Add(dr);
                }
            }

            // Step 5: Establishing Database Connection
            string connectionString = "Data Source=YourServer;Initial Catalog=YourDatabase;Integrated Security=True";
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();
                // Step 6: Inserting Data into SQL Server
                foreach (DataRow row in csvData.Rows)
                {
                    SqlCommand cmd = new SqlCommand("INSERT INTO YourTable (Column1, Column2, ...) VALUES (@Column1, @Column2, ...)", connection);
                    cmd.Parameters.AddWithValue("@Column1", row["Column1"]);
                    cmd.Parameters.AddWithValue("@Column2", row["Column2"]);
                    // Add parameters for other columns as needed
                    cmd.ExecuteNonQuery();
                }
            }

            Console.WriteLine("CSV data imported successfully.");
        }
    }
}

Conclusion

Using C# as a scripting language provides a powerful and efficient approach to importing CSV data into SQL Server databases. By following the outlined steps and utilizing the example script, developers can seamlessly integrate CSV data import functionality into their applications, ensuring reliability and maintainability. With its robust features and extensive support, C# empowers developers to tackle diverse data management challenges with ease.