[SOLVED] How to insert a file under a directory in SQL Filetable

How to insert a file under a directory in SQL Filetable

I created a directory in File Table. Now I need to insert files inside that directory. As parent_path_locator can not be set, I can't think of how to achieve this. I am doing all these in code.

This is how i created directory;

 Dim insertDir = "insert into dbo.DocumentStore(name,is_directory,is_archive) output INSERTED.path_locator values(@name,1,0)"
    Using sqlCommand As New SqlCommand(insertDir, con)
        sqlCommand.Parameters.Add("@name", SqlDbType.VarChar).Value = Me.txtGroupName.Text
        parentPathLocator = sqlCommand.ExecuteScalar()
    End Using

Note: I saw we can use dbo.GetNewPathLocator(path) to get a sub directory path based on the path locator. But I'm not sure how to use this in my case to insert a file.

Update: I found how to do it in TSQL TSQL but how to do this in code?

Solution

Finally I figured out how to do it:

Create folder and get path locator

 Dim insertDir = "insert into dbo.DocumentStore(name,is_directory,is_archive) output INSERTED.path_locator values(@name,1,0)"
 Using sqlCommand As New SqlCommand(insertDir, con)
     sqlCommand.Parameters.Add("@name", SqlDbType.VarChar).Value = Me.txtGroupName.Text
     parentPathLocator = sqlCommand.ExecuteScalar()
 End Using

Create a new hierachyID

 Dim retnewpath = "select CONVERT(VARCHAR(20), CONVERT(BIGINT, SUBSTRING(CONVERT(BINARY(16), NEWID()), 1, 6))) +'.'+ CONVERT(VARCHAR(20), CONVERT(BIGINT, SUBSTRING(CONVERT(BINARY(16), NEWID()), 7, 6))) +'.'+ CONVERT(VARCHAR(20), CONVERT(BIGINT, SUBSTRING(CONVERT(BINARY(16), NEWID()), 13, 4))) as path"

 Using sqlCommand As New SqlCommand(retnewpath, con)
     subpathlocator = sqlCommand.ExecuteScalar()
     subpathlocator = parentPathLocator.ToString() & subpathlocator & "/"
 End Using

Insert file with new hierarchyid as path locator

 Dim insertStr = "insert into dbo.DocumentStore(name,file_stream,path_locator) output INSERTED.stream_id values(@name,@file_stream,@parent_path_locator)"

 Using sqlCommand As New SqlCommand(insertStr, con)
    sqlCommand.Parameters.Add("@name", SqlDbType.VarChar).Value = Path.GetFileName(filename)
    sqlCommand.Parameters.Add("@file_stream", SqlDbType.VarBinary).Value = fileStream
    sqlCommand.Parameters.AddWithValue("@parent_path_locator", subpathlocator)
    streamID = sqlCommand.ExecuteScalar()

 End Using

I used this link to create the hierachyId, so I don't understand it completely yet.