Use the SQL Server CLR to Read and Write Text Files

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\TextFileTips.cs
-----------------------------------
Run the 3 lines below as one in a DOS command window;
this will compile the code to TextFileTips.dll
-----------------------------------------------------
C:\Windows\Microsoft.NET\Framework\v3.5\csc.exe 
 /target:library /out:C:\MSSQLTips\TextFileTips.dll 
 C:\MSSQLTips\TextFileTips.cs
Register the assembly
---------------------
CREATE ASSEMBLY TextFileTips
FROM 'C:\MSSQLTips\TextFileTips.dll'
WITH PERMISSION_SET = EXTERNAL_ACCESS
Create objects from the assembly
--------------------------------
CREATE FUNCTION [dbo].[WriteTextFile](
@text [nvarchar](4000), 
@path [nvarchar](4000), 
@append [bit])
RETURNS [bit] WITH EXECUTE AS CALLER
AS 
EXTERNAL NAME [TextFileTips].[ReadWriteFileTips].[WriteTextFile]
GO
CREATE PROCEDURE [dbo].[ReadTextFile]
 @path [nvarchar](4000)
WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [TextFileTips].[ReadWriteFileTips].[ReadTextFile]
GO
*/

A sample run of our text writer / reader is shown below, where we create a file by writing 3 lines of text using the function, then read the file using the stored procedure:

------------------------------------------------------------
--
SELECT dbo.WriteTextFile(
'text1', 'C:\MSSQLTips\Line.txt', 0)
GO
--returns 1
--
SELECT dbo.WriteTextFile(
'text2', 'C:\MSSQLTips\Line.txt', 1)
GO
--returns 1
--
SELECT dbo.WriteTextFile(
'text3', 'C:\MSSQLTips\Line.txt', 1)
GO
--returns 1
--
EXEC ReadTextFile 'C:\MSSQLTips\Line.txt'
GO
--prints
Line
----------------
text1
text2
text3
--
------------------------------------------------------------

Our tip discussed here just gets you started writing your own SQL CLR objects to do text file read / write operations.

Next Steps

• Compile, deploy, and use the WriteTextFile function, and the ReadTextFile stored procedure; enhance them to suit your needs.
• Explore the functionality of other .NET System.IO class' StreamWriter and StreamReader methods.
• Use complex text file processing in your SQL Server CLR development toolkit.