How do I call SQL Server functions?

ServiceStack.OrmLite SQL Server
1

I am currently working on a service that uses the FILESTREAM & FileTable feature in SQL Server (https://msdn.microsoft.com/en-us/library/ff929144.aspx). This feature essentially allows you to map a local path to a special FileTable which you can query just like any other table for things like file type, file size, file name, etc. One benefit to this feature is that when files are modified through the file system, the table is automagically updated. You can also INSERT, UPDATE, and DELETE files from the database.

Anyhow, one thing I haven’t quite figured out, and sometimes the obvious eludes me, is how can I craft a SqlExpression to include a server-side expression in a Select statement. It would be stellar if I could include an attribute on a property that would substitute the field name with an expression.

[Compute("file_stream.GetFileNamespacePath()"]
public string FullPath { get; }

As a side note to my question, FileTables also return a file_stream field (VARBINARY(MAX) => byte[]), which essentially is the content of the entire file (yes, if the files are text, you can even query this field). Anyhow, I have noticed that if I use AutoQuery in conjunction with the FileTables, ServiceStack blows up with an OutOfMemoryException if the file is very large. For now, I have just removed this field from my DTO, but is there a cleaner way to handle this exception? I currently have one call for the file’s info and another for the file’s content which uses a stream.

2

There’s no Typed API in OrmLite that lets you call non-standard SQL Functions. What SQL are you trying to execute? Maybe you can use an SQL Fragment to just specify that part.

3

I currently just have my model hooked up to QueryDb. However, I was hoping to expand my model to include some calculated results.

The SqlServer FileTable model would look something like the following (using ServiceStack.OrmLite.SqlServer.Converters to include SqlHierarchyId support). Note that when a FileTable is created, the server defines the fields, so you can not add or rename fields (makes sense considering they are tied to the filesystem).

public class FileStream
{
[Alias(“stream_id”)]
public Guid ID { get; set; }

[Alias("file_stream")]
[Ignore()]
public byte[] FileContent { get; set; }

[Alias("name")]
[StringLength(255)]
public string Name { get; set; }

[PrimaryKey()]
[Alias("path_locator")]
public SqlHierarchyId Path { get; set; }

[ForeignKey(typeof(FileStream))]
[Alias("parent_path_locator")]
public SqlHierarchyId? ParentPath { get; set; }

[Alias("file_type")]
[Compute]
[StringLength(255)]
public string FileType { get; set; }

[Alias("cached_file_size")]
[Compute]
public long? FileSize { get; set; }

[Alias("creation_time")]
public DateTimeOffset CreationDateTime { get; set; }

[Alias("last_write_time")]
public DateTimeOffset LastWriteDateTime { get; set; }

[Alias("last_access_time")]
public DateTimeOffset? LastAccessDateTime { get; set; }

[Alias("is_directory")]
public bool IsDirectory { get; set; }

[Alias("is_offline")]
public bool IsOffline { get; set; }

[Alias("is_hidden")]
public bool IsHidden { get; set; }

[Alias("is_readonly")]
public bool IsReadOnly { get; set; }

[Alias("is_archive")]
public bool IsArchive { get; set; }

[Alias("is_system")]
public bool IsSystem { get; set; }

[Alias("is_temporary")]
public bool IsTemporary { get; set; }

}

Would love to include a property decorated with an attribute to return a computed result with AutoQuery.

Something like this:

[Expression(“file_stream.GetFileNamespacePath() + (CASE WHEN is_directory = 1 THEN '' ELSE ‘’ END)”)]
public string FullPath { get; set; }

Then, whenever a select statement is called that includes the new property, the expression would be injected instead of the field’s name. It should be ignored for all other operations.

SELECT
stream_id AS ID,
– file_stream AS FileContent, // ignored due to size (creates an OutOfMemoryException when included)
name AS Name,
path_locator AS Path,
parent_path_locator AS ParentPath,
file_type AS FileType,
cached_file_size AS FileSize,
creation_time AS CreationDateTime,
last_write_time AS LastWriteDateTime,
last_access_time AS LastAccessDateTime,
is_directory AS IsDirectory,
is_offline AS IsOffline,
is_hidden AS IsHidden,
is_readonly AS IsReadOnly,
is_archive AS IsArchive,
is_system AS IsSystem,
is_temporary AS IsTemporary,
file_stream.GetFileNamespacePath() + (CASE WHEN is_directory = 1 THEN '' ELSE ‘’ END) AS FilePath
FROM dbo.FileStream

There are some issues with this approach such as trying to use an aggregate function on a non-aggregate query – database would shed tears.

You probably have a much easier solution or better ideas on how to implement this. So I would love to hear your thoughts/recommendations.

Regards

4

I’ve added support for the new [CustomSelect] attribute in this commit which should hopefully do what you want, I wasn’t able to get it working with the FileStream support in SQL Server as for some reason enabling it seems the support and option to enable it seem to be disabled, but in theory you should now be able to declare a field with:

[CustomSelect("file_stream.GetFileNamespacePath() + (CASE WHEN is_directory = 1 THEN '\' ELSE '' END)")]
public string FullPath { get; set; }

Here’s a test example of it working with a simple expression:

public class CustomSelectTest
{
    public int Id { get; set; }
    public int Width { get; set; }
    public int Height { get; set; }

    [CustomSelect("Width * Height")]
    public int Area { get; set; }
}

db.DropAndCreateTable<CustomSelectTest>();

db.Insert(new CustomSelectTest { Id = 1, Width = 10, Height = 5 });

var row = db.SingleById<CustomSelectTest>(1);

Assert.That(row.Area, Is.EqualTo(10 * 5));

This change is available from v4.0.57 that’s now available on MyGet.

5

Thanks @mythz for the quick response and feature addition!

I haven’t had a chance to check it out yet, but will let you know how it works. Looking at your unit test, it appears that it will function as expected.

I had some initial trouble setting up the FILESTREAM feature for SqlServer, but figured it out.

To enable it, you must run the SqlConfiguration application and turn it on before updating the database to support FileStreams or before creating a FileGroup or FileTable.

I would recommend checking at least the first two, but all three if you want to remotely access the files, and setting the Windows share name (which I believe you can leave blank if you want) to something else. Once this has been done, the options that were previously disabled should be accessible.

https://msdn.microsoft.com/en-us/library/gg509097.aspx

Once your FileGroup and Filetable(s) are set up and if you selected the first two options in the configuration app, you can access the files by navigating to the UNC path "\<instance-level FILESTREAM share><database-level directory><FileTable directory>" and any changes made to the files will be immediately reflected in the database. Plus, any changes made from the database side, will be reflected by the file-system. If you can’t remember the path, you can right click on the FileTable in SSMS and select it from there.

This puts a nice database layer between the file-system and the application, but keep in mind that path bound to the FileGroup must be a local path.

Many Thanks,
Kevin

6

Thanks for the info Kevin, it looks like I was able to progress a bit further, where I was able to add a FileGroup and then a File, but I kept running into errors, first it said I needed to have a ROWGUIDCOL PK column and now it will let me insert rows, but the custom select wont work, I’ve tried condensing the example into a simpler table which leaves me with:

public class TestFile
{
    [PrimaryKey]
    [CustomField("uniqueidentifier ROWGUIDCOL NOT NULL")]
    public Guid Id { get; set; }

    [CustomField("varbinary(max) FILESTREAM")]
    public byte[] Contents { get; set; }

    public bool IsDirectory { get; set; }

    [CustomSelect("Contents.GetFileNamespacePath() + (CASE WHEN is_directory = 1 THEN '\' ELSE '' END)")]
    public string FullPath { get; set; }
}

db.DropAndCreateTable<TestFile>();

db.Insert(new TestFile { Contents = "contents".ToUtf8Bytes() });

db.Select<TestFile>().PrintDump();

But the last line trying to select the data with the custom expression throws the following Exception:

System.Data.SqlClient.SqlException : Function GetFileNamespacePath is only valid on the varbinary(max) FILESTREAM column in a FileTable.
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)

Which is where I’m blocked on now, not sure how to get the custom SELECT to work.