SQLCLR Procedure to export query / SP results into CSV

We as database developers, many times have to export data into csv files and send them across to the Business users. The data the needs to be exported can be a retrieved by executing an adhoc-query or a stored procedure based on the users requirements. In this article we will look at a SQL CLR Stored procedure which can be used to export data into CSV from within the Database

The code of the CLR looks as below:

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.IO;
using System.Text;
public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void Export_To_CSV_File(SqlString Query, SqlString FileSpec, SqlBoolean OverwriteIfExists, SqlBoolean HeaderRow)
    {
        /***************** PREMILINARY CHECKS ***************************/
        //check for empty parameters
        if (Query.Value == string.Empty)
            throw new Exception("Query is missing.");
        // parse filespec
        FileInfo fi = new FileInfo(FileSpec.Value);
        string filePath = fi.DirectoryName;
        string fileName = fi.Name;
        if (filePath == string.Empty)
            throw new Exception("Missing file path location.");
        if (fileName == string.Empty)
            throw new Exception("Missing name of file.");
        // does excel spreadsheet already exist?
        if (fi.Exists == true && OverwriteIfExists.IsFalse)
            throw new Exception("File already exists, and OverwriteIfExists was specified as false.");
        /***************** Query the DB and exporting the data to file ***************************/
        DataSet exportData = new DataSet();
        using (SqlConnection conn = new SqlConnection("context connection=true"))
        {
            SqlDataAdapter da = new SqlDataAdapter(Query.ToString(), conn);
            DataSet ds = new DataSet();
            da.Fill(ds, "Q1");
            DataTable table = ds.Tables["Q1"];
            StringBuilder sb = new StringBuilder();
            if (fi.Exists == true && OverwriteIfExists == true)
            {
                fi.Delete();
                SqlContext.Pipe.Send("file deleted");
                //System.Threading.Thread.Sleep(2000);
                //if (fi.Exists == true)
                //    throw new Exception("Unable to overwrite the existing file!");
            }
            StreamWriter sw = new StreamWriter(FileSpec.ToString(), false);
            if (HeaderRow)
            {
                //write the headers.
                for (int colCount = 0;
                     colCount < table.Columns.Count; colCount++)
                {
                    sb.Append(table.Columns[colCount].ColumnName);
                    if (colCount != table.Columns.Count - 1)
                    {
                        sb.Append(",");
                    }
                    else
                    {
                        sb.AppendLine();
                    }
                }
            }
            sw.Write(sb.ToString());
            //sw.Write(sw.NewLine);
            // Write all the rows.
            for (int rowCount = 0;
                 rowCount < table.Rows.Count; rowCount++)
            {
                StringBuilder sb_row = new StringBuilder();
                for (int colCount = 0;
                     colCount < table.Columns.Count; colCount++)
                {
                    sb_row.Append(table.Rows[rowCount][colCount]);
                    if (colCount != table.Columns.Count - 1)
                    {
                        sb_row.Append(",");
                    }
                }
                if (rowCount != table.Rows.Count - 1)
                {
                    sb_row.AppendLine();
                }
                sw.Write(sb_row.ToString());
                //sw.Write(sw.NewLine);
            }
            sw.Close();
        }
    }
};

Now we will go through the code:

The SP declaration

[Microsoft.SqlServer.Server.SqlProcedure]
    public static void Export_To_CSV_File(SqlString Query, SqlString FileSpec
, SqlBoolean OverwriteIfExists, SqlBoolean HeaderRow)

1. Query – The adhoc query / SP that needs to be executed to get the required data.
2. FileSpec – The file directory path along with the filename where data needs to be exported. This path is local to the SQL Server itself and not the client machine.
3. OverwriteIfExists – A flag to say whether to overwrite the file. 1 = true, 0 = false.
4. HeaderRow – Flag to export column header in the file. 1 = true , 0 = false

Preliminary checks

Checks to see whether the query param is set, filepath is valid, filename is passed, file already exists and such other checks.

Query the DB and exporting the data to file

Next we open connection to the DB and populate a datatable. After that we export the datatable to the file.

SQLCLR Deployment

Now once you have written the code build the solution and deploy it to SQL Server. BIDS can automatically deploy the code to sql server or you can do it manually using the following script.

CREATE ASSEMBLY [ExportToCSV]
FROM '<path to compiled dll>'
WITH PERMISSION_SET = EXTERNAL_ACCESS
CREATE PROCEDURE [dbo].[Export_To_CSV_File]
    @Query [nvarchar](4000),
    @FileSpec [nvarchar](4000),
    @OverwriteIfExists [bit],
    @HeaderRow [bit]
WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [ExportToCSV].[StoredProcedures].[Export_To_CSV_File]
GO

Sample RUN

--Run 1 – Executing a query
exec Export_To_CSV_File @query ='select * from Photo_Tags'
,@FileSpec ='C:\Temp\phototags.csv'
,@OverwriteIfExists = 1
,@HeaderRow = 1
--Run 2 - Calling a SP
exec Export_To_CSV_File @query ='exec  dbo.Get_Current_Stats'
,@FileSpec ='C:\Temp\Current_Stats.csv'
,@OverwriteIfExists = 1
,@HeaderRow = 1