Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 attend our next webcast













































   Got a SQL tip?
            We want to know!

Use the SQL Server CLR to Read and Write Text Files

MSSQLTips author Alex Tocitu By:   |   Read Comments (5)   |   Related Tips: More > Functions - User Defined UDF
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.
  • Check other SQL CLR tips on this site.


Last Update: 3/23/2011


About the author
MSSQLTips author Alex Tocitu
Alex Tocitu is an engineer with an MSEE in Automation and MBA in Finance.

View all my tips


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Post a comment or let the author know this tip helped you.

       All comments are reviewed, so stay on subject or we may delete your comment.

*Name   *Email Notify for updates



       Note: your email address is not published. Required fields are marked with an asterisk (*)


Get free SQL tips:

*Enter Code refresh code     



Friday, May 10, 2013 - 6:45:46 AM - raza Read The Tip

it returns error when trying to write in the file. Error is..   Access to the path 'C:\5feb13.txt' is denied. What is the problem?


Monday, January 07, 2013 - 11:22:59 AM - Oxiane Read The Tip

Thanks Alex, only 10 minutes from "Google, is it possible to ... ?" to "Open this text file with notepad"... thanks to you. Great job !


Friday, October 28, 2011 - 11:19:48 PM - opc.three Read The Tip

http://filesystemhelper.codeplex.com/


Wednesday, March 23, 2011 - 6:54:52 PM - Alex Tocitu Read The Tip

Thanks, Daniel. Glad to be of help.


Wednesday, March 23, 2011 - 5:42:40 PM - Daniel Read The Tip

Alex,
This worked perfectly.  Exactly what I needed exactly when I needed it.




 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.