Read and Write Binary Files with the SQL Server CLR


By:   |   Updated: 2011-04-01   |   Comments   |   Related: More > Functions - User Defined UDF


Problem

You are a database developer looking for a common approach for handling read and write access to binary files. You may be a DBA wanting to read various information from binary files and collect it into tables. The code sample presented in this tip will get you started with binary 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 binary file access, the two classes mostly used are the BinaryReader class and the BinaryWriter class.

The BinaryReader class reads primitive data types as binary values; it has a number of methods like ReadBoolean, ReadDecimal, ReadInt32, ReadSingle, ReadString, etc., for reading specific data types, and also a method called Read which reads characters one by one from the underlying stream. The BinaryWriter class writes a value of a specific data type like bool, float, string, etc., to the current stream. Both the read and write type methods advance the current position of the stream after the operation completes.

The first section of the sample code shows a helper function called WriteBinaryRecord, which writes a few binary values to a location specified in the input variable path; the file is created, or, overwritten if the append parameter is false. This function will be called a few times to populate the sample file.

The second section contains a CLR stored procedure which starts by writing three records to a binary file specified in the path input variable using the helper function; then continues by reading the data from the file, and outputting it record by record, using the SqlPipe class' methods, to possibly display it in SQL Server Management Studio. The code sample contains proper comments to improve clarity.

using System;
using System.Data;
using System.Data.SqlTypes;
using System.IO;
using Microsoft.SqlServer.Server;
public class ReadWriteBinaryTips
{
  private static string WriteBinaryRecord(string path,
                                          bool append,
                                          int one,
                                          long two,
                                          float three,
                                          double four,
                                          string five)
  {
    // Writes to a binary file a sequence of values.
    // Parameters
    // path: The complete file path to write to.
    // append: Determines whether data is to be appended to the file;
    // if append is false, the file is created or overwritten;
    // if append is true, data is appended to the file.
    // one, two, three, four, five: various data types to be written.
    try
    {
      // Create or append to binary file.
      using (var bw = new BinaryWriter(File.Open(path,
                                      append == true
                                      ? FileMode.Append
                                      : FileMode.Create)))
      {
        // Write int value.
        bw.Write(one);
        // Write long value.
        bw.Write(two);
        // Write float value.
        bw.Write(three);
        // Write double value.
        bw.Write(four);
        // Write string value.
        bw.Write(five);
        // Return empty string on success.
        return string.Empty;
      }
    }
    catch (Exception ex)
    {
      // Return exception message on failure.
      return ex.Message;
    }
  }
  [SqlProcedure]
  public static void ProcessBinaryFile(SqlString path)
  {
    // Parameters
    // path: The complete file path to process.
    string ret;
    try
    {
      // Check for null input.
      if (!path.IsNull)
      {
        // Write 3 records to path location.
        // Return error message on failure.
        // Create binary file and write record.
        if ((ret = WriteBinaryRecord(path.Value, false,
          1, 2L, 3.4F, 5.6, "One")) != string.Empty)
        {
          SqlContext.Pipe.Send(ret);
          return;
        }
        // Append record to binary file.
        if ((ret = WriteBinaryRecord(path.Value, true,
          2, 3L, 4.56F, 7.89, "Two")) != string.Empty)
        {
          SqlContext.Pipe.Send(ret);
          return;
        }
        // Append record to binary file.
        if ((ret = WriteBinaryRecord(path.Value, true,
          3, 4L, 5.678F, 9.012, "Three")) != string.Empty)
        {
          SqlContext.Pipe.Send(ret);
          return;
        }
        // Read the binary file just written to.
        // Initialize a new instance of the BinaryReader class for the specified path.
        using (var br = new BinaryReader(File.Open(path.Value, FileMode.Open)))
        {
          // Create the record and specify the metadata for the column.
          var rec = new SqlDataRecord(
                            new SqlMetaData("One", SqlDbType.Int),
                            new SqlMetaData("Two", SqlDbType.BigInt),
                            new SqlMetaData("Three", SqlDbType.Real),
                            new SqlMetaData("Four", SqlDbType.Float),
                            new SqlMetaData("Five", SqlDbType.NVarChar, SqlMetaData.Max));
          // Mark the beginning of the result-set.
          SqlContext.Pipe.SendResultsStart(rec);
          // Initialize position in the stream to be read from.
          long position = 0;
          // Test for current position in the stream.
          while (position < br.BaseStream.Length)
          {
            // Read and set value for the columns.
            rec.SetInt32(0, br.ReadInt32());
            rec.SetInt64(1, br.ReadInt64());
            rec.SetFloat(2, br.ReadSingle());
            rec.SetDouble(3, br.ReadDouble());
            // Read string
            var five = br.ReadString();
            // Set value for column string
            rec.SetString(4, five);
            // Send the row back to the client.
            SqlContext.Pipe.SendResultsRow(rec);
            // Advance stream position by sum of each data type length in bytes;
            // for string data type add sizeof(int).
            position += sizeof(int) + sizeof(long) + sizeof(float) + sizeof(double) +
                        sizeof(int) + five.Length;
          }
          // Mark the end of the result-set.
          SqlContext.Pipe.SendResultsEnd();
        }
      }
    }
    catch (Exception ex)
    {
      // Return exception message on failure.
      SqlContext.Pipe.Send(ex.Message);
    }
  }
};

The script below instructs you how to deploy the sample code to 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)
*/

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

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.


Last Updated: 2011-04-01


get scripts

next tip button



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



Comments For This Article





download





Recommended Reading

Generate a parameter list for all SQL Server stored procedures and functions

Validate Integer and Decimal Values in SQL Server

Auto Generate Create Table Script Based on SQL Server Query

Four ways to improve scalar function performance in SQL Server

SQL Server User Defined Function Example














get free sql tips
agree to terms