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

Question

I am new to .net core application .I need to import 4 different tables value from Sql server into 4 separate CSV files using windows Console app .net core

Naimish Makwana · Answer

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 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

Vishal Yelve · Answer

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; }

Tuhin Paul · Answer

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); } } } } } } }