By: Alex Tocitu | Updated: 2011-04-01 | Comments | Related: > Common Runtime Language
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.
About the author
Alex Tocitu is an engineer with an MSEE in Automation and MBA in Finance.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2011-04-01
