Click here to Skip to main content
15,867,686 members
Articles / Containers / Virtual Machine

FUSE(file system in userspace) for Microsoft-SQL using C#

Rate me:
Please Sign up or sign in to vote.
4.91/5 (20 votes)
19 Aug 2010CPOL4 min read 46.2K   43   7
Map your database table as network disk

Introduction

Every time I use Linux, I feel a piece of jealousy to see its filesystems. Not only NTFS, FAT32, but a huge palette of data storages included GDocs, etc. Now I can raise head up! Dokan rocks!

What is Dokan?

What is Dokan? Simply device driver encapsulated filesystem calls. Using proxy functions programmatically creates response for ReadFile, WriteFile, FindFiles and other core I/O operations. Even on .NET! There are also Dokan.NET interfaces for using C#.
Now you are enabled to write your own filesystems. You can create your own filesystem or you can be inspired by another application using Dokan. What about filesystem using memory as ramdisk, system used encrypted file as data storage? Mirroring of some disc or directories, access to registry via filesystem, list of processes as files on attached disk? Open your mind and try to find another use for Dokan. This article will help you. You will be able to store your files on Microsoft SQL database, copy, rename, update, delete, etc. Simply file versioning is also included. This was the reason why I started play with Dokan. Create an external storage with some versioning, mounted as disk, easy to use for end users. To create a version of file, just add extension ".version" to the end of filename and copy to disk. File will be renamed to previous extension and previous version of file receives a version number. You can choose if you want to see all versions or only the actual version.

Prerequisities

Installed Dokan: Download actual version (0.53) from Dokan main pages and install. (The code was tested with version 0.52.)

Prepared Microsoft-SQL: For testing purposes, Microsoft-SQL Express edition on your PC is suitable, but I used SQL server located on virtual machine to simulate some network traffic and network accidents. For production, be careful and create a special user account and allow to run stored procedures for this account. Do not miss this step. It is a good security strategy.
On SQL machine, run metadata script to create a stored procedures used by C# application and create table "DOKANFS", main and only table used by your app. If you want to use another table, you have to rename all occurrences in all stored procedures.

VS 2008 Standard edition: If you have Express edition, you have to use Microsoft-SQL Express on your machine. Express edition doesn't allow remote connection to Microsoft-SQL server.

Play with Code

Let's peek into code to find something useful. As you can see, files are "pre-loaded" into simple Dictionary object. Files are loaded from SQL database when first ReadFile method call is used to receive data. File is downloaded and "ungzipped" if it was gzipped. Files are gzipped when it is written on SQL server due to reduce network traffic. But if file has extension enlisted, file is not zipped.
File is read and written using FileCache objects. When file is closed, use Cleanup procedure called by Dokan.NET. Cleanup procedure performs gzipping of data and calls SP WriteFile to store data into Microsoft-SQL.

C#
public int Cleanup(string filename, DokanFileInfo info)
       {
           lock (FileCache)
           {
               if ((FileCache.ContainsKey(filename) == true) &&
           (FileCache[filename].MemStream.Length > 0))
               {
                   using (SqlConnection conn = new SqlConnection(ConnectionString))
                   {
                       using (SqlCommand Cmd = new SqlCommand())
                       {
                           MemoryStream mem =
               ((FileCaching)FileCache[filename]).MemStream;
                           Cmd.CommandText = "WriteFile";
                           Cmd.Parameters.Add("@iszipped", SqlDbType.Bit, 1);
                           Cmd.Parameters["@iszipped"].Value = 0;
                           Cmd.Parameters.Add("@OriginalSize", SqlDbType.BigInt);
                           Cmd.Parameters["@OriginalSize"].Value = mem.Length;

                           if (this.ZippedExtension.ToLower().IndexOf
               (Path.GetExtension(Regex.Split(filename.ToLower(),
               ".version")[0])) == -1)
                           {
                               if (FileCache[filename].MemStream.Length > 256)
                               {
                                   Cmd.Parameters["@iszipped"].Value = 1;
                                   MemoryStream dummy = new MemoryStream();
                                   Compress(mem, dummy);
                                   mem.SetLength(0);
                                   dummy.WriteTo(mem);
                               }
                           }

                           mem.Seek(0, SeekOrigin.Begin);
                           Cmd.Parameters.Add("@data", SqlDbType.VarBinary,
                   (int)mem.Length);
                           Cmd.Parameters["@data"].SqlValue = mem.ToArray();

                           Cmd.Parameters.AddWithValue("@filename", filename);

                           Cmd.CommandType = CommandType.StoredProcedure;
                           Cmd.Connection = conn;
                           conn.Open();

                           Cmd.ExecuteNonQuery();
                           FileCache.Remove(filename);
                       }
                   }
               }
           };
           return DokanNet.DOKAN_SUCCESS;
       }

Code written in T-SQL on Microsoft-SQL is not so complicated. The most complicated stored procedure is FindFiles, due to using versions.
Drive to show old version of files or not is set first bit in content field of root directory "\". If the first bit is set to 1, the procedure shows the previous version in extension.

SQL
    ALTER PROCEDURE [dbo].[FindFiles]
	(
	@filename varchar(255)
	)
	
AS
    /*
    exec FindFiles @filename = '\' 
    */
	SET NOCOUNT ON 
	
	if @filename = '\' set @filename = '\' else set @filename = @filename+'\'
	
	select filename, isdirectory, IsNull(OriginalSize,_
		DATALENGTH([CONTENT])) as size, filename as fullfilename,
	       LastAccessTime,LastWriteTime,CreationTime
	  into #TEMP
	  from DOKANFS 
	 where (filename like @filename+'%' and FILENAME _
		not like @filename+'%\%' and Version is null)
	
	/* all versions */
	declare @allVersion int
	select @allVersion = (isnull(cast(content as int),0) & 1) _
		from DOKANFS where FILENAME = '\'
	
	if @allVersion = 1 begin
	   select filename + '.'+ cast(ISNULL(version,'0') as varchar(10)) as filename, 
	         isdirectory, 
	         IsNull(OriginalSize,DATALENGTH([CONTENT])) as size, 
	         filename+ '.'+ cast(ISNULL(version,'0') as varchar(10)) as fullfilename,
	         LastAccessTime,LastWriteTime,CreationTime
	  into #TEMP2
	  from DOKANFS 
	  where (filename like @filename+'%' and FILENAME _
		not like @filename+'%\%' and Version is not null)
	  update #TEMP2 set filename = SUBSTRING(filename, _
		CHARINDEX(@filename,filename)+LEN(@filename),255)
	  end
	
	
	update #TEMP set filename = SUBSTRING(filename, _
		CHARINDEX(@filename,filename)+LEN(@filename),255)
	
	insert into #TEMP (filename, isdirectory,size,fullfilename,_
		LastAccessTime,LastWriteTime,CreationTime) 
	       values ('.',1,0,'.',GETDATE(),GETDATE(),GETDATE())
	if @filename <> '\' 
	   insert into #TEMP (filename,isdirectory,size,fullfilename,_
		LastAccessTime,LastWriteTime,CreationTime) 
	          values ('..',1,0,'..',GETDATE(),GETDATE(),GETDATE())
	
	if @allVersion = 1 begin
	   select * from #TEMP 
	   union
	   select * from #TEMP2
	   order by filename
	   
	   end else begin
	   
	   select * from #TEMP 
	   order by filename
	   end
	
	RETURN    

Points of Interest

Dokan in version 0.52 works great, but there are some unexpected behaviours when file is opened by Notepad++. I'm still waiting for Dokan to grow to be in use similar to TrueCrypt and its loading drivers on demand.

What is Missing

Huge testing on various networks. I tested this concept on 1Mbit VPN without any problems. This is only proof of concept, no code "beautifier" was applied. Also some "registration" functions can be done... for example, prepare database by creating all stored procedures, create table, etc. You can also use Azure, PostgreSQL, MySQL, Firebird, Oracle or any database in which you want to store data.
All code is published as is. I apologize for some inconvenience. Time is passing too quickly for me and I have other things to do, not just this project.
Enjoy this code and contact me if you want to participate in this project seriously. Some examples of using cloud will be nice.

History

This is the first release, proof of concept.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Czech Republic Czech Republic
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
GeneralMy vote of 5 Pin
Savalia Manoj M9-Jun-13 17:56
Savalia Manoj M9-Jun-13 17:56 
GeneralMy vote of 5 Pin
Kanasz Robert26-Sep-12 4:57
professionalKanasz Robert26-Sep-12 4:57 
GeneralMy vote of 5 Pin
jay_rod10126-Feb-12 14:34
jay_rod10126-Feb-12 14:34 
Questionhelpful post & a question for Martin Pin
jay_rod10126-Feb-12 14:31
jay_rod10126-Feb-12 14:31 
AnswerRe: helpful post & a question for Martin Pin
martin.nedopil28-Feb-12 5:05
martin.nedopil28-Feb-12 5:05 
GeneralMy vote of 5 Pin
Pranay Rana19-Aug-10 23:42
professionalPranay Rana19-Aug-10 23:42 
GeneralInteresting article Pin
leppie19-Aug-10 1:35
leppie19-Aug-10 1:35 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.