Use the SQL Server CLR to Read and Write Text Files

http://www.mssqltips.com/sqlservertip/2349/read-and-write-binary-files-with-the-sql-server-clr/

Problem

You are a database developer looking for a common approach for handling read write access to text files. You may be a DBA wanting to write procedure execution results to files for documentation purposes. The code samples presented in this tip will get you started with text file content handling in SQL Server.

Solution

The .NET Framework class library provides the System.IO namespace containing types supporting reading from, and writing to files and data streams. For text file access, the 2 classes mostly used are the StreamReader class and the StreamWriter class, with their corresponding methods, ReadLine and WriteLine respectively.

The first section shows you how easy it is to append information to a file (the file is created if it does not exist). The code sample contains a CLR function called WriteTextFile, which writes a line of text to a location specified in the input variable path; the file is overwritten if the append parameter is false.

The second section contains a stored procedure which allows you to read the content of any existing text file - specified by the path input variable - and display it in SQL Server Management Studio. The ReadTextFile CLR stored procedure can process any file size, as long as the file contains line terminators at least every 8,000 bytes (which is the maximum size of a T-SQL string). The code samples contain proper comments to enhance readability.

using System;
using System.Data;
using System.Data.SqlTypes;
using System.IO;
using Microsoft.SqlServer.Server;
public class ReadWriteFileTips
{
  [SqlFunction]
  public static SqlBoolean WriteTextFile(SqlString text,
                                        SqlString path,
                                        SqlBoolean append)
  {
    // Parameters
    // text: Contains information to be written.
    // path: The complete file path to write to.
    // append: Determines whether data is to be appended to the file.
    // if the file exists and append is false, the file is overwritten.
    // If the file exists and append is true, the data is appended to the file.
    // Otherwise, a new file is created.
    try
    {
      // Check for null input.
      if (!text.IsNull &&
          !path.IsNull &&
          !append.IsNull)
      {
        // Get the directory information for the specified path.
        var dir = Path.GetDirectoryName(path.Value);
        // Determine whether the specified path refers to an existing directory.
        if (!Directory.Exists(dir))
          // Create all the directories in the specified path.
          Directory.CreateDirectory(dir);
        // Initialize a new instance of the StreamWriter class
        // for the specified file on the specified path.
        // If the file exists, it can be either overwritten or appended to.
        // If the file does not exist, create a new file.
        using (var sw = new StreamWriter(path.Value, append.Value))
        {
          // Write specified text followed by a line terminator.
          sw.WriteLine(text);
        }
        // Return true on success.
        return SqlBoolean.True;
      }
      else
        // Return null if any input is null.
        return SqlBoolean.Null;
    }
    catch (Exception ex)
    {
      // Return null on error.
      return SqlBoolean.Null;
    }
  }
  [SqlProcedure]
  public static void ReadTextFile(SqlString path)
  {
    // Parameters
    // path: The complete file path to read from.
    try
    {
      // Check for null input.
      if (!path.IsNull)
      {
        // Initialize a new instance of the StreamReader class for the specified path.
        using (var sr = new StreamReader(path.Value))
        {
          // Create the record and specify the metadata for the column.
          var rec = new SqlDataRecord(
                            new SqlMetaData("Line", SqlDbType.NVarChar, SqlMetaData.Max));
          // Mark the beginning of the result-set.
          SqlContext.Pipe.SendResultsStart(rec);
          // Determine whether the end of the file.
          while (sr.Peek() >= 0)
          {
            // Set value for the column.
            rec.SetString(0, sr.ReadLine());
            // Send the row back to the client.
            SqlContext.Pipe.SendResultsRow(rec);
          }
          // Mark the end of the result-set.
          SqlContext.Pipe.SendResultsEnd();
        }
      }
    }
    catch (Exception ex)
    {
      // Send exception message on error.
      SqlContext.Pipe.Send(ex.Message);
    }
  }
};

The script below instructs you how to deploy the sample code in a database on your server:

/*
======================
HOW TO DEPLOY THE CODE
======================
Configure your SQL Server instance
for CLR (if not already configured)
-----------------------------------
USE master
GO
sp_configure 'clr enabled', 1
GO
RECONFIGURE
GO
Create your test database or choose one
---------------------------------------
CREATE DATABASE db_tips
GO
Configure your database
-----------------------
USE db_tips
GO
ALTER DATABASE db_tips SET TRUSTWORTHY ON
GO
Save text in article code window to
C:\MSSQLTips\BinaryFileTips.cs
-----------------------------------
Run the 3 lines below as one in a DOS command window;
this will compile the code to BinaryFileTips.dll
-----------------------------------------------------
C:\Windows\Microsoft.NET\Framework\v3.5\csc.exe 
 /target:library /out:C:\MSSQLTips\BinaryFileTips.dll 
 C:\MSSQLTips\BinaryFileTips.cs
Register the assembly
---------------------
CREATE ASSEMBLY BinaryFileTips
FROM 'C:\MSSQLTips\BinaryFileTips.dll'
WITH PERMISSION_SET = EXTERNAL_ACCESS
Create CLR stored procedure from the assembly
---------------------------------------------
CREATE PROCEDURE [dbo].[ProcessBinaryFile]
 @path [nvarchar](4000)
WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [BinaryFileTips].[ReadWriteBinaryTips].[ProcessBinaryFile]
GO
*/

A sample run of our binary file processing stored procedure is shown below; notice that if the file path does not exist you get the error from the helper function passed to the stored procedure:

/*
exec dbo.ProcessBinaryFile 'C:\MSSQLTips\Test\WriteBinary.bin'
--if the full path of the file does not exist
--prints
Could not find a part of the path 'C:\MSSQLTips\Test\WriteBinary.bin'.
--if the full path of the file exists
--returns
One         Two                  Three         Four                   Five
----------- -------------------- ------------- ---------------------- ------------------------
1           2                    3.4           5.6                    One
2           3                    4.56          7.89                   Two
3           4                    5.678         9.012                  Three
(3 row(s) affected)
*/

Next Steps

• Compile, deploy, and use the ProcessBinaryFile stored procedure.
• Explore the functionality of other .NET System.IO class' BinaryWriter and BinaryReader methods.
• Use complex binary file processing in your SQL Server CLR development toolkit.
• Check other SQL CLR tips on this site.