CuteEditor stores images and documents in a Sql Server 2008's filestream

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[fsitems](
[id] [int] IDENTITY(1,1) NOT NULL,
[path] [nvarchar](300) NOT NULL,
[isdir] [bit] NOT NULL,
[parentpath] [nvarchar](300) NOT NULL,
[createdt] [datetime] NULL,
[filename] [nvarchar](300) NULL,
[filesize] [int] NULL,
[filedata] [varbinary](max) NULL,
[fileguid] [uniqueidentifier] NOT NULL,
[contenttype] [nvarchar](256) NULL,
CONSTRAINT [PK_fsitems] PRIMARY KEY CLUSTERED
(
[path] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
ALTER TABLE [dbo].[fsitems] ADD  DEFAULT (newid()) FOR [fileguid]
GO

using System;
using System.Configuration;
using System.Collections.Generic;
using System.Web.Configuration;
using System.Collections;
using System.IO;
using System.Data;
using System.Data.SqlClient;
using System.Web;
using System.Text;
public class SqlFileStorage : CuteEditor.Impl.FileStorage
{
/// 

/// Connection String
/// 
SqlConnection conn;
/// 

/// the aspx page whose sole responsibility is to display a specific image
/// 
string downfile;
public SqlFileStorage(HttpContext context)
: base(context)
{
string culture;
string policy;
System.Collections.Specialized.NameValueCollection dict;
if (CuteEditor.CEU.SplitEditorSetting(context, out culture, out policy, out dict))
{
downfile = dict["DownFile"];
}
conn = new SqlConnection();
}
//EnterQuery , make sure the connection is open
#region protected IDisposable EnterQuery()
protected IDisposable EnterQuery()
{
if (conn.State == ConnectionState.Closed)
{
conn.ConnectionString = ConfigurationManager.ConnectionStrings["MaragnaNetCS"].ToString();// ConnectionString;
conn.Open();
Disposer d = new Disposer();
d.fs = this;
return d;
}
return new Disposer();
}
class Disposer : IDisposable
{
public SqlFileStorage fs;
public void Dispose()
{
if (fs != null)
{
fs.conn.Dispose();
fs = null;
}
}
}
#endregion
public void ExecuteNonQuery(string commandtext, params object[] args)
{
ExecuteReader(commandtext, args).Close();
}
static public void SetParameter(SqlParameterCollection pcoll, string pname, object obj)
{
if (pcoll == null) throw (new ArgumentNullException("pcoll"));
if (obj == null || Convert.IsDBNull(obj))
{
pcoll.Add(pname, SqlDbType.NVarChar, 50).Value = DBNull.Value;
return;
}
if (obj is string)
{
if (obj.ToString().Length > 4000)
{
pcoll.Add(pname, SqlDbType.NText).Value = obj;
return;
}
}
else if (obj is byte[])
{
byte[] bs = (byte[])obj;
if (bs.Length > 8000)
{
pcoll.Add(pname, SqlDbType.Binary).Value = bs;
return;
}
}
else if (obj is SqlParameter)
{
SqlParameter sp = (SqlParameter)obj;
//pcoll.Add(sp);
pcoll.Add(new SqlParameter(pname, sp.SqlDbType, sp.Size, sp.Direction, true, sp.Precision, sp.Scale, sp.SourceColumn, sp.SourceVersion, sp.Value));
return;
}
else if (obj is DateTime)
{
pcoll.Add(pname, SqlDbType.NVarChar, 50).Value = ((DateTime)obj).ToString("yyyy-MM-dd HH:mm:ss") + "." + ((DateTime)obj).Millisecond.ToString().PadLeft(3, '0');
return;
}
pcoll.Add(pname, obj);
}
//get the id of an item
protected int GetItemId(string path)
{
using (EnterQuery())
{
using (SqlDataReader reader = ExecuteReader("select id from fsitems where path={0}", path))
{
if (reader.Read())
return reader.GetInt32(0);
}
}
return 0;
}
//get the path of an item
protected string GetItemPath(int id)
{
using (EnterQuery())
{
using (SqlDataReader reader = ExecuteReader("select path from fsitems where id={0}", id))
{
if (reader.Read())
return reader.GetString(0);
}
}
return null;
}
public override CuteEditor.Impl.DirectoryItem[] GetDirectoryItems(string dirpath, bool getcount)
{
dirpath = CalcPath(VirtualRoot, dirpath);
ArrayList arr = new ArrayList();
using (EnterQuery())
{
using (SqlDataReader reader = ExecuteReader("select * from fsitems where isdir=1 and parentpath={0} order by path ", dirpath))
{
while (reader.Read())
{
CuteEditor.Impl.DirectoryItem item = new CuteEditor.Impl.DirectoryItem();
item.Path = reader["path"].ToString();
item.Name = Path.GetFileName(item.Path);
arr.Add(item);
}
}
if (getcount)
{
foreach (CuteEditor.Impl.DirectoryItem item in arr)
{
using (SqlDataReader reader = ExecuteReader("select count(*) from fsitems where parentpath={0}", item.Path))
{
reader.Read();
item.ChildCount = reader.GetInt32(0);
}
}
}
}
return (CuteEditor.Impl.DirectoryItem[])arr.ToArray(typeof(CuteEditor.Impl.DirectoryItem));
}
public override CuteEditor.Impl.FileItem[] GetFileItems(string dirpath, string searchpattern)
{
dirpath = CalcPath(VirtualRoot, dirpath);
ArrayList arr = new ArrayList();
using (EnterQuery())
{
if (searchpattern == null) searchpattern = "%";
searchpattern = searchpattern.Replace("*", "%");
using (SqlDataReader reader = ExecuteReader("select id,path,createdt,filename,filesize from fsitems where isdir=0 and parentpath={0} and path like {1} order by path ", dirpath, searchpattern))
{
while (reader.Read())
{
CuteEditor.Impl.FileItem item = new CuteEditor.Impl.FileItem();
item.Path = reader["path"].ToString();
item.Name = Path.GetFileName(item.Path);
item.Length = (int)reader["filesize"];
item.CreationTime = item.LastWriteTime = (DateTime)reader["createdt"];
item.Url = downfile + "?fileid=" + reader["id"];
arr.Add(item);
}
}
}
return (CuteEditor.Impl.FileItem[])arr.ToArray(typeof(CuteEditor.Impl.FileItem));
}
/// 

/// Get the display path of the dirpath
/// 
public override string GetDirectoryText(string dirpath)
{
dirpath = CalcPath(VirtualRoot, dirpath);
return dirpath;
}
public override string GetParentDirectory(string dirpath)
{
dirpath = CalcPath(VirtualRoot, dirpath);
if (dirpath == "/")
return null;
try
{
return Path.GetDirectoryName(dirpath);
}
catch
{
throw (new Exception(dirpath));
}
}
private string GetFileDirectory(string filepath)
{
filepath = CalcPath(VirtualRoot, filepath);
return Path.GetDirectoryName(filepath);
}
public override string GetFileName(string filepath)
{
filepath = CalcPath(VirtualRoot, filepath);
return Path.GetFileName(filepath);
}
public override byte[] GetFileData(string filepath)
{
filepath = CalcPath(VirtualRoot, filepath);
using (EnterQuery())
{
using (SqlDataReader reader = ExecuteReader("select filedata from fsitems where path={0}", filepath))
{
if (reader.Read())
return (byte[])reader[0];
}
}
return null;
}
public string ContentType(string FileExtension)
{
Dictionary d = new Dictionary();
//Images
d.Add(".bmp", "image/bmp");
d.Add(".gif", "image/gif");
d.Add(".jpeg", "image/jpeg");
d.Add(".jpg", "image/jpeg");
d.Add(".png", "image/png");
d.Add(".tif", "image/tiff");
d.Add(".tiff", "image/tiff");
//'Documents'
d.Add(".doc", "application/msword");
d.Add(".docx", "application/vnd.openxmlformats-officedocument.wordprocessingml.document");
d.Add(".pdf", "application/pdf");
//Slideshows'
d.Add(".ppt", "application/vnd.ms-powerpoint");
d.Add(".pptx", "application/vnd.openxmlformats-officedocument.presentationml.presentation");
//'Data'
d.Add(".xlsx", "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
d.Add(".xls", "application/vnd.ms-excel");
d.Add(".csv", "text/csv");
d.Add(".xml", "text/xml");
d.Add(".txt", "text/plain");
//'Compressed Folders'
d.Add(".zip", "application/zip");
//'Audio'
d.Add(".ogg", "application/ogg");
d.Add(".mp3", "audio/mpeg");
d.Add(".wma", "audio/x-ms-wma");
d.Add(".wav", "audio/x-wav");
//'Video'
d.Add(".wmv", "audio/x-ms-wmv");
d.Add(".swf", "application/x-shockwave-flash");
d.Add(".avi", "video/avi");
d.Add(".mp4", "video/mp4");
d.Add(".mpeg", "video/mpeg");
d.Add(".mpg", "video/mpeg");
d.Add(".qt", "video/quicktime");
return d[FileExtension];
}
public override string CreateFile(string dirpath, string filename, byte[] filedata)
{
dirpath = CalcPath(VirtualRoot, dirpath);
filename = Path.GetFileName(filename);
string newpath = Path.Combine(dirpath, filename).Replace(@"\", "/");
string ext="";
int n= filename.LastIndexOf('.');
if(n>0){
ext=filename.Substring(n, filename.Length - n);
}
string contenttype = ContentType(ext);
using (EnterQuery())
{
if (dirpath != "/")
{
int dirid = GetItemId(dirpath);
if (dirid == 0) throw (new Exception("path '" + dirpath + "' not found"));
}
int fileid = GetItemId(newpath);
if (fileid != 0)
{
ExecuteNonQuery("update fsitems set createdt={1},filesize={2},filedata={3}, contenttype={4} where id={0}", fileid, DateTime.Now, filedata.Length, filedata,contenttype);
}
else
{
ExecuteNonQuery("insert into fsitems (path,isdir,parentpath,createdt,filename,filesize,filedata,contenttype) values ({0},0,{1},{2},{3},{4},{5},{6})"
, newpath
, dirpath
, DateTime.Now
, filename
, filedata.Length
, filedata
, contenttype
);
}
return newpath;
}
}
public override string CreateDirectory(string dirpath, string dirname)
{
dirpath = CalcPath(VirtualRoot, dirpath);
dirname = Path.GetFileName(dirname);
string newpath = Path.Combine(dirpath, dirname).Replace(@"\", "/");
using (EnterQuery())
{
if (dirpath != "/")
{
int dirid = GetItemId(dirpath);
if (dirid == 0) throw (new Exception("path '" + dirpath + "' not found"));
}
if (GetItemId(newpath) == 0)
{
ExecuteNonQuery("insert into fsitems (path,isdir,parentpath) values ({0},1,{1})", newpath, dirpath);
}
return newpath;
}
}
public override void DeleteDirectory(string dirpath)
{
dirpath = CalcPath(VirtualRoot, dirpath);
using (EnterQuery())
{
ExecuteNonQuery("delete from fsitems where path={0}", dirpath);
ExecuteNonQuery("delete from fsitems where path like {0}+'/%'", dirpath);
}
}
public override void DeleteFile(string filepath)
{
filepath = CalcPath(VirtualRoot, filepath);
using (EnterQuery())
{
ExecuteNonQuery("delete from fsitems where isdir=0 and path={0}", filepath);
}
}
public override string RenameDirectory(string dirpath, string name)
{
dirpath = CalcPath(VirtualRoot, dirpath);
string dir = GetFileDirectory(dirpath);
string newpath = Path.Combine(dir, name).Replace(@"\", "/");
using (EnterQuery())
{
ExecuteNonQuery("update fsitems set path={0} where path={1}", newpath, dirpath);
ExecuteNonQuery("update fsitems set path={0}+right(path,len(path)-{2}) , parentpath={0}+right(parentpath,len(parentpath)-{2}) where path like {1}+'/%'", newpath, dirpath, dirpath.Length);
}
return newpath;
}
public override string RenameFile(string filepath, string name)
{
filepath = CalcPath(VirtualRoot, filepath);
string dir = GetFileDirectory(filepath);
string newpath = Path.Combine(dir, name).Replace(@"\", "/");
using (EnterQuery())
{
ExecuteNonQuery("update fsitems set path={0} where path={1}", newpath, filepath);
}
return newpath;
}
public override string MoveDirectory(string dirpath, string targetdir)
{
dirpath = CalcPath(VirtualRoot, dirpath);
targetdir = CalcPath(VirtualRoot, targetdir);
string name = Path.GetFileName(dirpath);
string newpath = Path.Combine(targetdir, name).Replace(@"\", "/");
using (EnterQuery())
{
ExecuteNonQuery("update fsitems set path={0} ,parentpath={2} where path={1}", newpath, dirpath, targetdir);
ExecuteNonQuery("update fsitems set path={0}+right(path,len(path)-{2}) , parentpath={0}+right(parentpath,len(parentpath)-{2}) where path like {1}+'/%'", newpath, dirpath, dirpath.Length);
}
return newpath;
}
public override string MoveFile(string filepath, string targetdir)
{
filepath = CalcPath(VirtualRoot, filepath);
GetFileDirectory(filepath);
targetdir = CalcPath(VirtualRoot, targetdir);
string newfilepath = targetdir.TrimEnd('/') + "/" + GetFileName(filepath);
using (EnterQuery())
{
ExecuteNonQuery("update fsitems set path={2} , parentpath={1} where path={0}", filepath, targetdir, newfilepath);
}
return newfilepath;
}
public override string CopyDirectory(string dirpath, string targetdir)
{
dirpath = CalcPath(VirtualRoot, dirpath);
targetdir = CalcPath(VirtualRoot, targetdir);
using (EnterQuery())
{
throw (new NotImplementedException());
}
}
public override string CopyFile(string filepath, string targetdir)
{
using (EnterQuery())
{
throw (new NotImplementedException());
}
}
string root = "/";
public override string VirtualRoot
{
get
{
return root;
}
set
{
if (value == null)
throw (new ArgumentNullException("VirtualRoot"));
if (value.StartsWith("~/"))
value = value.Remove(0, 1);
if (!value.StartsWith("/"))
throw (new ArgumentException("Path must be rooted"));
root = value;
if (root != "/")
root = root.TrimEnd('/');
}
}
private string CalcPath(string path, string relpath)
{
path = path.Replace('\\', '/');
if (!path.StartsWith("/"))
path = CalcPath(VirtualRoot, path);
if (relpath == null || relpath == "")
{
return path;
}
if (relpath.StartsWith("/"))
{
if (relpath.IndexOf("..") != -1)
throw (new Exception("Absolute path can't contain '..'"));
if (relpath == "/")
return relpath;
return relpath.TrimEnd('/');
}
foreach (string str in relpath.Split(@"/\".ToCharArray()))
{
if (str == "")
continue;
if (str == "..")
{
int pos = -1;
if (path != null && path != "")
pos = path.LastIndexOf('/', path.Length - 2);
if (pos == -1)
{
path = null;
}
else
{
path = path.Substring(0, pos);
}
}
else
{
path += "/" + str;
}
}
if (path == "")
return "/";
return path.Replace("//", "/");
}
public override string GetFileUrl(string filepath)
{
filepath = CalcPath(VirtualRoot, filepath);
return downfile + "?fileid=" + GetItemId(filepath);
}
public override string TranslateTargetDirectory(string dirpath, string relpath)
{
if (relpath.StartsWith("/"))
return (VirtualRoot + relpath).Replace("//", "/");
return CalcPath(dirpath, relpath);
}
/// 

/// GetDirectorySize
/// 
///

public override double GetDirectorySize(string dirpath)
{
return 0;
}
//execute the reader , commandtext is formatted by string.Format
#region SqlDataReader ExecuteReader(string commandtext,params object[] args)
SqlDataReader ExecuteReader(string commandtext, params object[] args)
{
int len = args == null ? 0 : args.Length;
SqlCommand cmd = new SqlCommand(commandtext, conn);
object[] argnames = new object[len];
SqlParameterCollection ps = cmd.Parameters;
for (int i = 0; i < len; i++)
{
object val = args[i];
string pname = "@cdq_" + i;
SetParameter(ps, pname, val);
if (val is DateTime)
{
argnames[i] = "CONVERT(DATETIME," + pname + ")";
}
else
{
argnames[i] = pname;
}
}
commandtext = string.Format(commandtext, argnames);
cmd.CommandText = commandtext;
return cmd.ExecuteReader();
}
#endregion
}