C# CLR sp to export query to file system

#save as batch file - useful for compiling c# from the command line

cd C:\Users\shane.ryan\Desktop\code
csc /reference:Microsoft.VisualBasic.dll  /target:library /out:DB_WRITETOFILE.dll /warn:0 *.cs

pause

#end of batch file

#save below as writetofile.cs

using System;
using System.Data;
using Microsoft.SqlServer.Server;
using System.Data.SqlTypes;
using System.Data.SqlClient;
using System.IO;
using System.Collections;
using System.Text;

 public class SQLCLRIO
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static int WriteToFile(String content, String filename, String databasename, String delim)
    {
            SqlConnection conn;
conn = new SqlConnection("Data Source=ROGUE;Initial Catalog=My_DB;Integrated Security=false;Enlist=false;connection timeout=1000; Max Pool Size = 100;User Id=db_admin;password=monday!");
 conn.Open();
            //Querying Data
            SqlCommand cmd = new SqlCommand(content, conn);
            SqlDataReader rdr1 = cmd.ExecuteReader();
            StringBuilder str = new StringBuilder();
            // bool to determine if we write or append
            bool flag = false;
            int count = 0;
            // loop through resultset to get a count
            while (rdr1.Read())
            {
                count = count + 1;
            }
            rdr1.Close();
            int count_j = 0;
            SqlDataReader rdr = cmd.ExecuteReader();
            try
            {
            while (rdr.Read())
            {
                count_j = count_j + 1;
                for (int i = 0; i < rdr.FieldCount; i++)
                {
                    // append column to string
                    str.Append(rdr[i].ToString());
                    if (i < rdr.FieldCount - 1)
                    {
                        str.Append(delim);
                    }
                    //SqlContext.Pipe.Send("str length: " + str.Length.ToString());
                    if (str.Length > 5000000)
                    {
                        //SqlContext.Pipe.Send("string is over 500,000 ncharacters");
                        //we need to output the current string
                        using (StreamWriter writer = new StreamWriter(filename, true))
                        {
                            //SqlContext.Pipe.Send("write new file");
                            writer.WriteLine(str);
                        }
                        //we need to set a flag so the remaining code
                        //appends the string to the file instead of writing over it
                        flag = true;
                        //we need to reset the str variable
                        str.Length = 0;
                    }
                }
                //we do not want to add an extra line for the EOF
                if (count_j < count)
                {
       
                    str.Append("\r\n");

                }
            }
            if (flag == true)
            {
                string a = str.ToString();
                string b = "\r\n";
                if(a!=b)
                {
                //SqlContext.Pipe.Send("appending");
                File.AppendAllText(filename, str.ToString());
                }
            }
            else
            {
                //SqlContext.Pipe.Send("new file");
                using (StreamWriter writer = new StreamWriter(filename, true))
                {
                    writer.WriteLine(str);
                }

            }
            rdr.Close();
            conn.Close();
            }
            catch (Exception ex)
            {
                SqlContext.Pipe.Send("Error writing to file " + ex.Message);
                rdr.Close();
                rdr1.Close();
                conn.Close();
                count = -1;
                return (count);
            }
            rdr.Dispose();
            rdr1.Dispose();
            conn.Dispose();
            return (count);
        }

}
# end of writetofile.cs




--
-- the database name needs to be updated in the line below
--
ALTER DATABASE edit_databasename_here SET TRUSTWORTHY ON
--
GO
--
-- need to drop the proc before the assembly
--
IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'writetofile'))
   DROP PROCEDURE writetofile
--
GO
--
IF EXISTS (SELECT * FROM sys.assemblies WHERE name = 'WriteToFile')
   DROP ASSEMBLY WriteToFile
GO
--
-- the DB_WRITETOFILE.dll file needs to be copied to the same directory specified in the line below
--
CREATE ASSEMBLY WriteToFile FROM 'D:\Assembly\DB_WRITETOFILE.dll'
WITH PERMISSION_SET = external_access
--
GO
--
CREATE PROCEDURE DB_Writetofile
(
   @content      nvarchar(max),
   @filename     nvarchar(255),
   @databasename nvarchar(255),
   @delimeter    nvarchar(1)
)
AS EXTERNAL NAME WriteToFile.SQLCLRIO.WriteToFile

/**
    -- CLR must be enabled
    sp_configure 'clr enabled', 1
    GO
     RECONFIGURE
    GO
   
    --Example of running an export
    EXEC dbo.DB_Writetofile 'select top 10 * from [TestTable]',
    'D:\Export\export_file_test.txt','My_DB', '|'