C#: Save SQL query result as CSV file with Column Headers

Scenario:

I want to write SQL SELECT query in c# pages then save the query result in excel file with column headers

Solution – Working code: 

Add the following #namespaces
/*extra namespaces*/
using System.IO;
using System.Data.SqlClient;
using System.Collections.Generic;
/* MAIN FUNCTION*/
public void Main()
{
bool useHeaders = true;
bool firstCounter = true;
String strToday = DateTime.Now.ToString(“yyyyMMdd_HHmm”);
/*Name of the new file name*/
String fileName = “D:\\MAhmed\\SocialCareFolders\\batch output\\2015_Batch_Trace_Output_” + strToday + “.csv”;
/*connect to the sql server and get the result set*/

/*Gets or sets the connection string used to establish a connection to a data source.*/

ConnectionManager cm;
SqlConnection sqlCon = new SqlConnection();
cm = (ConnectionManager) Dts.Connections[“ADO.NET_OutComesStats”];

/*When you call the AcquireConnection method of a connection manager, the connection manager connects to the data source. Associate the connections with the active transaction by using the Transaction property*/
sqlCon=(SqlConnection)cm.AcquireConnection(Dts.Transaction);
/*sqlCon = (SqlConnection)Dts.Connections[“Dev01_OutComesStats”].AcquireConnection(Dts.Transaction);*/

/*String sqlQuery = “SELECT * FROM [OutComesStats].[temp].[SocialCare_BatchTraceOutput]”;*/
if(sqlCon.State!=ConnectionState.Open)
{
sqlCon.Close();
sqlCon.Open();
}

using(SqlCommand sqlCom = new SqlCommand(“SELECT * FROM [OutComesStats].[temp].[SocialCare_BatchTraceOutput]”, sqlCon))
using(SqlDataReader reader = sqlCom.ExecuteReader())
/*read the sql result set using SqlDataReader, read header line from the reader, read one record at a time and write to the fileName file*/

/*You can’t change reference once you used in using clause. It means you cannot change the value of that variable.*/
using (StreamWriter writer = new StreamWriter(fileName))
{
/*get the ColumnNames from the SqlDataReader*/
if (useHeaders)
{
List<String> columnNames = getColumnNames(reader);
String line=””;
foreach(String columnName in columnNames)
{
if (firstCounter == true)
{
line += string.Format(“{0}”, columnName);
firstCounter = false;
}
else
line += string.Format(“,{0}”, columnName);
}
writer.WriteLine(line);

}
/*read a record at a time and then write to the fileName file*/
while (reader.Read())
{
writer.WriteLine(
“{0}, {1}, {2}, {3}, {4}, {5}, {6}, {7}, {8}, {9}, {10}, {11}, {12}, {13}, {14}, {15}, {16}”,

reader[“RecordType”]
,reader[“LocalPatientID”]
,reader[“DateOfBirth”]
,reader[“Col4”]
,reader[“Col5”]
,reader[“NHSNumber”]
,reader[“Surname”]
,reader[“Col8”]
,reader[“FirstName”]
,reader[“Col10”]
,reader[“Sex”]
,reader[“Col12”]
,reader[“Col13”]
,reader[“Col14”]
,reader[“Col15”]
,reader[“Col16”]
,reader[“PostCode”]);
}
}
sqlCon.Close();
cm.Dispose();

}

 

 

1st example:

Add the following #namespaces

/* extra name spaces
*add Microsoft.CSharp
*add a COM reference in your project to the “Microsoft Excel 11.0 Object Library”
*/
using System.IO;
using Excel = Microsoft.Office.Interop.Excel;
using Marshal = System.Runtime.InteropServices.Marshal; /*to release the resources consumed by excel app*/
using XlPlatform=Microsoft.Office.Interop.Excel.XlPlatform;

 

string separator = ";";
string fieldDelimiter = "";
bool useHeaders = true;

string connectionString = "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx";

using (SqlConnection conn = new SqlConnection(connectionString))
{
     using (SqlCommand cmd = conn.CreateCommand())
     {
          conn.Open();
          string query = @"SELECT whatever";

          cmd.CommandText = query;

          using (SqlDataReader reader = cmd.ExecuteReader())
          {
                if (!reader.Read())
                {
                     return;
                }

                List<string> columnNames = GetColumnNames(reader);

                // Write headers if required
                if (useHeaders)
                {
                     first = true;
                     foreach (string columnName in columnNames)
                     {
                          response.Write(first ? string.Empty : separator);
                          line = string.Format("{0}{1}{2}", fieldDelimiter, columnName, fieldDelimiter);
                          response.Write(line);
                          first = false;
                     }

                     response.Write("\n");
                }

                // Write all records
                do
                {
                     first = true;
                     foreach (string columnName in columnNames)
                     {
                          response.Write(first ? string.Empty : separator);
                          string value = reader[columnName] == null ? string.Empty : reader[columnName].ToString();
                          line = string.Format("{0}{1}{2}", fieldDelimiter, value, fieldDelimiter);
                          response.Write(line);
                          first = false;
                     }

                     response.Write("\n");
                }
                while (reader.Read());
          }
     }
}

And you need to have a function GetColumnNames:

List<string> GetColumnNames(IDataReader reader)
{
    List<string> columnNames = new List<string>();
    for (int i = 0; i < reader.FieldCount; i++)
    {
         columnNames.Add(reader.GetName(i));
    }

    return columnNames;
}

2nd Example:
using(SqlConnection conn = new SqlConnection(connectionString))
using(SqlCommand cmd = conn.CreateCommand())
{
  conn.Open();
  cmd.CommandText = QueryLoader.ReadQueryFromFileWithBdateEdate(
    @"Resources\qrs\qryssysblo.q", newdate, newdate);

  using(SqlDataReader reader = cmd.ExecuteReader())
  using(StreamWriter writer = new StreamWriter("c:\temp\file.txt"))
  {
    while(reader.Read())
    {
      // Using Name and Phone as example columns.
      writer.WriteLine("Name: {0}, Phone : {1}", 
        reader["Name"], reader["Phone"]);
    }
  }
}
 
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s