How to add multiple tables from Sqlserver to CSV files for console app

Hello Meghna,

Try below code

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

namespace CsvExporter
{
    class Program
    {
        static void Main(string[] args)
        {
            // specify the connection string
            var connectionString = "Server=localhost;Database=YourDatabaseName;Trusted_Connection=True;";

            // specify the path where the CSV files should be saved
            var outputPath = @"C:\MyCsvFiles\";

            using (var connection = new SqlConnection(connectionString))
            {
                // open the connection
                connection.Open();

                // export Table1
                using (var command = new SqlCommand("SELECT * FROM Table1", connection))
                using (var reader = command.ExecuteReader())
                using (var writer = new StreamWriter(Path.Combine(outputPath, "Table1.csv")))
                {
                    WriteCsv(reader, writer);
                }

                // export Table2
                using (var command = new SqlCommand("SELECT * FROM Table2", connection))
                using (var reader = command.ExecuteReader())
                using (var writer = new StreamWriter(Path.Combine(outputPath, "Table2.csv")))
                {
                    WriteCsv(reader, writer);
                }

                // export Table3
                using (var command = new SqlCommand("SELECT * FROM Table3", connection))
                using (var reader = command.ExecuteReader())
                using (var writer = new StreamWriter(Path.Combine(outputPath, "Table3.csv")))
                {
                    WriteCsv(reader, writer);
                }

                // export Table4
                using (var command = new SqlCommand("SELECT * FROM Table4", connection))
                using (var reader = command.ExecuteReader())
                using (var writer = new StreamWriter(Path.Combine(outputPath, "Table4.csv")))
                {
                    WriteCsv(reader, writer);
                }

                // close the connection
                connection.Close();
            }
        }

        private static void WriteCsv(IDataReader reader, TextWriter writer)
        {
            // write the header row
            for (int i = 0; i < reader.FieldCount; i++)
            {
                writer.Write(reader.GetName(i));
                if (i < reader.FieldCount - 1) writer.Write(",");
            }
            writer.WriteLine();

            // write the data rows
            while (reader.Read())
            {
                for (int i = 0; i < reader.FieldCount; i++)
                {
                    if (!reader.IsDBNull(i))
                    {
                        string value = reader.GetValue(i).ToString();
                        writer.Write(value);
                    }
                    if (i < reader.FieldCount - 1) writer.Write(",");
                }
                writer.WriteLine();
            }
        }
    }
}

Another approach is, you can directly use SSMS to export SQL Server data to a CSV file. Use below link:

https://solutioncenter.apexsql.com/how-to-export-sql-server-data-to-a-csv-file/

Thanks

Naimish Makwana

Try the following steps:

Step 1: Add the necessary NuGet packages to your project: Microsoft.Data.SqlClient, CsvHelper.

Step 2: Establish a connection to the SQL Server database using SqlConnection object and create a SqlCommand object to execute the SELECT statement to retrieve the data from the tables.

Step 3: Create a SqlDataReader object to retrieve the data returned from the SELECT statement.

Step 4: Create a CsvWriter object to write the data to a CSV file.

Step 5: Loop through the SqlDataReader object and write the data to the CsvWriter object.

Step 6: Save the CSV file and close the SqlDataReader object and the CsvWriter object.

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

namespace ConsoleApp
{
    class Program
    {
        static void Main(string[] args)
        {
            string connectionString = "";
            string tableName = "";
            string csvFileName = "";

            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();

                using (SqlCommand command = new SqlCommand($"SELECT * FROM {tableName}", connection))
                {
                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        using (CsvWriter writer = new CsvWriter(new StreamWriter(csvFileName)))
                        {
                            writer.WriteRecords(reader);
                        }
                    }
                }
            }
        }
    }
}

Use ClosedXML

            var _dataSet =  BindData(); // pass dataset 

            string contentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
            string fileName = "Report.xlsx";
            try
            {
                using (var workbook = new XLWorkbook())
                {
                    workbook.Worksheets.Add(_dataSet);

                    using (var stream = new MemoryStream())
                    {
                        workbook.SaveAs(stream);
                        var content = stream.ToArray();
                        return File(content, contentType, fileName);
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;

            }