C# - How to Export Stored Procedure Results to Text File in C Sharp

Scenario : Download Script

You are working as C# developer, You need to developer a program that should export the results of a Stored Procedure to Text file. Every time the program executes, it should run the Stored Procedure and export the results to new flat file. The file should be create with date-time.

Let's create sample table and Stored Procedure first.

Create table dbo.Customer(
Id INT,
Name VARCHAR(100),
Dob Date)

insert into dbo.Customer values(1,'John','1980-01-02')
go

Create procedure dbo.prc_Customer
AS
BEGIN
Select * From dbo.Customer
END

The below C# will be used to export the Stored Procedure results to text file.

 

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.IO;
using System.Data.SqlClient;
using System.Data;


namespace TechBrothersIT.com_CSharp_Tutorial
{
    class Program
    {
        static void Main(string[] args)
        {

            string datetime = DateTime.Now.ToString("yyyyMMddHHmmss");
            string LogFolder = @"C:\Log\";
            try
            {

                //Declare Variables and provide values
                string FileNamePart = "Customer";//Datetime will be added to it
                string DestinationFolder = @"C:\Destination\";
                string StoredProcedureName = "dbo.prc_Customer";//Provide SP name,you Can provide with Parameter if you like
                string FileDelimiter = ","; //You can provide comma or pipe or whatever you like
                string FileExtension = ".txt"; //Provide the extension you like such as .txt or .csv


                //Create Connection to SQL Server in which you like to load files
                SqlConnection SQLConnection = new SqlConnection();
                    SQLConnection.ConnectionString = "Data Source = (local); Initial Catalog =TechBrothersIT; "
                       + "Integrated Security=true;";

                //Execute Stored Procedure and save results in data table
                string query = "EXEC " + StoredProcedureName;
                SqlCommand cmd = new SqlCommand(query, SQLConnection);
                SQLConnection.Open();
                DataTable d_table = new DataTable();
                d_table.Load(cmd.ExecuteReader());
                SQLConnection.Close();

                //Prepare the file path 
                string FileFullPath = DestinationFolder + "\\" + FileNamePart + "_" + datetime + FileExtension;

                StreamWriter sw = null;
                sw = new StreamWriter(FileFullPath, false);

                // Write the Header Row to File
                int ColumnCount = d_table.Columns.Count;
                for (int ic = 0; ic < ColumnCount; ic++)
                {
                    sw.Write(d_table.Columns[ic]);
                    if (ic < ColumnCount - 1)
                    {
                        sw.Write(FileDelimiter);
                    }
                }
                sw.Write(sw.NewLine);

                // Write All Rows to the File
                foreach (DataRow dr in d_table.Rows)
                {
                    for (int ir = 0; ir < ColumnCount; ir++)
                    {
                        if (!Convert.IsDBNull(dr[ir]))
                        {
                            sw.Write(dr[ir].ToString());
                        }
                        if (ir < ColumnCount - 1)
                        {
                            sw.Write(FileDelimiter);
                        }
                    }
                    sw.Write(sw.NewLine);

                }

                sw.Close();
             
            }
            catch (Exception exception)
            {
                // Create Log File for Errors
                using (StreamWriter sw = File.CreateText(LogFolder
                    + "\\" + "ErrorLog_" + datetime + ".log"))
                {
                    sw.WriteLine(exception.ToString());
                   
                }

            }

        }
    }
}

I executed the script couple of times and it generated the flat files from Stored Procedure results. The files were created with date-time.

How to Export Stored Procedure Results to Text file in C#