Click here to Skip to main content
Click here to Skip to main content

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

, 19 Aug 2010
Rate this:
Please Sign up or sign in to vote.
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.

 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.

    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)

About the Author

martin.nedopil

Czech Republic Czech Republic
No Biography provided

Comments and Discussions

 
GeneralMy vote of 5 PinmemberSavalia Manoj M9-Jun-13 17:56 
GeneralMy vote of 5 PinmvpKanasz Robert26-Sep-12 4:57 
GeneralMy vote of 5 Pinmemberjay_rod10126-Feb-12 14:34 
Questionhelpful post & a question for Martin [modified] Pinmemberjay_rod10126-Feb-12 14:31 
Thanks for posting this, it helped me to understand how to use Dokan.
 
I see this post has not received much attention so I probably know the answer already but I'll ask anyway... Martin, have you spent any more time developing this project or given it any further thought? This seems like a very nice front end to a project I'm working on and I'd love to know how you might have proceeded with this to have made it into a bit more of a robust program. For an example, I played around and uploaded movies/music to the database but was unable to play them back ... this did not surprise me but I'm lost as to how to add this functionality (probably something to do with streaming a file). I also tried saving files from programs (like word/excel) but the save would always fail with "the file name is not valid"
 
For information if anyone else runs into compile problems and SQL parameter passing errors:
- I'm using windows 7 x64
- I had to make the target x86 so it would find the dokan.dll. Not sure why because I thought the Dokan installer had the x64 option selected.
- Modify the 'Cmd.CommandText = "ReadFile";' in MSSqlFS.cs to use "ReadFile2" (a different stored procedure that came in the Metadata folder's SqlFS.sql file). Without this modification I could not properly write to the database.
- in the cleanup function the procedure "WriteFile" also had to be modified with respect to passed parameters in C# code and the sql procedure code.

modified 26-Feb-12 20:48pm.

AnswerRe: helpful post & a question for Martin Pinmembermartin.nedopil28-Feb-12 5:05 
GeneralMy vote of 5 PinmemberPranay Rana19-Aug-10 23:42 
GeneralInteresting article Pinmemberleppie19-Aug-10 1:35 

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

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

| Advertise | Privacy | Mobile
Web01 | 2.8.140721.1 | Last Updated 19 Aug 2010
Article Copyright 2010 by martin.nedopil
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid