public virtual string CreateDirectory(string table, string dir, string pathId, SqlConnection conn)
{
SqlConnManager.IsConnected(conn);
if (!FileTableExists(table, conn))
{
throw new Exception("Table does not exists or is not a FileTable.");
}
var CreateDirectoryQry = "INSERT INTO {0} (name, is_directory)"
+ " OUTPUT Inserted.path_locator.ToString()"
+ " VALUES (@dir, 1)";
var qry = string.Format(CreateDirectoryQry, table);
var cmd = new SqlCommand(qry, conn);
cmd.Parameters.Add(new SqlParameter("@dir", dir));
if (!string.IsNullOrWhiteSpace(pathId))
{
pathId = HierarchyBuilder.NewChildHierarchyId(pathId);
CreateDirectoryQry = "INSERT INTO {0} (name, is_directory, path_locator)"
+ " OUTPUT Inserted.path_locator.ToString()"
+ " VALUES (@dir, 1, @pathId)";
cmd.CommandText = string.Format(CreateDirectoryQry, table);
cmd.Parameters.Add(new SqlParameter("@pathId", pathId as object));
}
pathId = cmd.ExecuteScalar() as string;
return(pathId);
}
public virtual SqlHierarchyId CreateDirectory(string table, string dir, SqlHierarchyId pathId, SqlConnection conn, bool pipeToOutput = false)
{
SqlConnManager.IsConnected(conn);
if (!FileTableExists(table, conn))
{
throw new Exception("Table does not exists or is not a FileTable.");
}
var CreateDirectoryQry = "INSERT INTO {0} (name, is_directory)"
+ " OUTPUT Inserted.path_locator"
+ " VALUES (@dir, 1)";
var qry = string.Format(CreateDirectoryQry, table);
var cmd = new SqlCommand(qry, conn);
cmd.Parameters.Add(new SqlParameter("@dir", dir));
if (!pathId.IsNull)
{
pathId = HierarchyBuilder.NewChildHierarchyId(pathId);
CreateDirectoryQry = "INSERT INTO {0} (name, is_directory, path_locator)"
+ " OUTPUT Inserted.path_locator"
+ " VALUES (@dir, 1, @pathId)";
cmd.CommandText = string.Format(CreateDirectoryQry, table);
var param1 = new SqlParameter("@pathId", pathId)
{
UdtTypeName = Constants.HierarchyId
};
cmd.Parameters.Add(param1);
}
pathId = (SqlHierarchyId)(cmd.ExecuteScalar() ?? SqlHierarchyId.Null);
if (pipeToOutput)
{
PipeFile(table, pathId, conn);
}
return(pathId);
}
public Guid CreateFile(string table, string fileName, SqlHierarchyId pathId, byte[] data, SqlConnection conn)
{
SqlConnManager.IsConnected(conn);
if (!FileTableExists(table, conn)) // This is used to prevent SQL injection
{
throw new Exception("Table does not exists or is not a FileTable.");
}
if (pathId.IsNull)
{
pathId = HierarchyBuilder.NewChildHierarchyId(SqlHierarchyId.Null);
}
var insertQry = "INSERT INTO {0} (name, file_stream, path_locator) "
+ " OUTPUT Inserted.stream_id"
+ " VALUES (@fileName, @data, @pathId)";
var qry = string.Format(insertQry, table);
var cmd = new SqlCommand(qry, conn);
cmd.Parameters.Add(new SqlParameter("@fileName", fileName));
cmd.Parameters.Add(new SqlParameter("@data", data));
cmd.Parameters.Add(new SqlParameter("@pathId", pathId)
{
UdtTypeName = Constants.HierarchyId
});
var streamId = (Guid)cmd.ExecuteScalar();
PipeScalar(SqlDbType.UniqueIdentifier, streamId);
return(streamId);
}
public virtual bool FileTableExists(string table, SqlConnection conn)
{
SqlConnManager.IsConnected(conn);
const string qry = "SELECT Count(name) FROM Sys.Tables WHERE name = @table and is_filetable = 1";
var cmd = new SqlCommand(qry, conn);
cmd.Parameters.Add(new SqlParameter("@table", table));
var result = (int)cmd.ExecuteScalar();
return(result == 1);
}
public virtual SqlHierarchyId FindPath(string table, string path, bool isDirectory, SqlConnection conn, bool pipeToOutput = false)
{
SqlConnManager.IsConnected(conn);
if (!FileTableExists(table, conn))
{
throw new Exception("Table does not exists or is not a FileTable.");
}
var tableRoot = GetTableRootPath(table, 0, conn);
var tableRootFqdn = GetTableRootPath(table, 2, conn);
if (IsTableRoot(path, tableRoot, tableRootFqdn))
{
return(new SqlHierarchyId());
}
var relativePath = path.GetRelativePath(tableRoot);
if (path.Length == relativePath.Length)
{
relativePath = path.GetRelativePath(tableRootFqdn);
}
var dirs = relativePath.SplitByDirectory();
SqlHierarchyId pathLocator = SqlHierarchyId.Null;
foreach (var dir in dirs)
{
var qry1 = string.Format(GetPathLocatorQry, table, pathLocator.IsNull ? "is null" : "= @pathLocator");
SqlCommand cmd1 = new SqlCommand(qry1, conn);
if (!pathLocator.IsNull)
{
var pathLocatorParam = new SqlParameter("@pathLocator", pathLocator)
{
UdtTypeName = Constants.HierarchyId
};
cmd1.Parameters.Add(pathLocatorParam);
}
cmd1.Parameters.Add(new SqlParameter("@dir", dir));
pathLocator = (SqlHierarchyId)(cmd1.ExecuteScalar() ?? SqlHierarchyId.Null);
if (pathLocator.IsNull)
{
break;
}
}
if (pipeToOutput)
{
PipeFile(table, pathLocator, conn);
}
return(pathLocator);
}
public virtual string GetTableRootPath(string table, int option, SqlConnection conn)
{
SqlConnManager.IsConnected(conn);
if (!FileTableExists(table, conn))
{
throw new Exception("Table does not exists or is not a FileTable.");
}
option = (option < 0 || option > 2) ? 0 : option;// Only allow valid options
const string qry = "SELECT FileTableRootPath(@table, @option)";
var tableRootCmd = new SqlCommand(qry, conn);
tableRootCmd.Parameters.Add(new SqlParameter("@table", table));
tableRootCmd.Parameters.Add(new SqlParameter("@option", option));
var tableRoot = tableRootCmd.ExecuteScalar() as string;
return(tableRoot);
}
public int DeleteFile(string table, SqlHierarchyId hierarchyid, SqlConnection conn)
{
SqlConnManager.IsConnected(conn);
if (!FileTableExists(table, conn)) // This is used to prevent SQL injection
{
throw new Exception("Table does not exists or is not a FileTable.");
}
var deleteQry = "DELETE FROM {0} WHERE path_locator = @hierarchyid";
var qry = string.Format(deleteQry, table);
var cmd = new SqlCommand(qry, conn);
cmd.Parameters.Add(new SqlParameter("@hierarchyid", hierarchyid)
{
UdtTypeName = Constants.HierarchyId
});
return(cmd.ExecuteNonQuery());
}
public int DeleteFile(string table, Guid stream_id, SqlConnection conn)
{
SqlConnManager.IsConnected(conn);
if (!FileTableExists(table, conn)) // This is used to prevent SQL injection
{
throw new Exception("Table does not exists or is not a FileTable.");
}
var deleteQry = "DELETE FROM {0} WHERE stream_id = @id";
var qry = string.Format(deleteQry, table);
var cmd = new SqlCommand(qry, conn);
cmd.Parameters.Add(new SqlParameter("@id", SqlDbType.UniqueIdentifier)
{
Value = stream_id
});
return(cmd.ExecuteNonQuery());
}
public int RenameFile(string table, Guid stream_id, string filename, SqlConnection conn)
{
SqlConnManager.IsConnected(conn);
if (!FileTableExists(table, conn)) // This is used to prevent SQL injection
{
throw new Exception("Table does not exists or is not a FileTable.");
}
if (string.IsNullOrWhiteSpace(filename))
{
throw new ArgumentNullException("filename");
}
var insertQry = "UPDATE {0} SET Name = @fileName WHERE stream_id = @id";
var qry = string.Format(insertQry, table);
var cmd = new SqlCommand(qry, conn);
cmd.Parameters.Add(new SqlParameter("@fileName", filename));
cmd.Parameters.Add(new SqlParameter("@id", stream_id));
return(cmd.ExecuteNonQuery());
}
public virtual string FindPath(string table, string path, bool isDirectory, SqlConnection conn)
{
SqlConnManager.IsConnected(conn);
if (!FileTableExists(table, conn))
{
throw new Exception("Table does not exists or is not a FileTable.");
}
var tableRoot = GetTableRootPath(table, 0, conn);
var tableRootFqdn = GetTableRootPath(table, 2, conn);
if (IsTableRoot(path, tableRoot, tableRootFqdn))
{
return("/");
}
var relativePath = path.GetRelativePath(tableRoot);
if (path.Length == relativePath.Length)
{
relativePath = path.GetRelativePath(tableRootFqdn);
}
var dirs = relativePath.SplitByDirectory();
string pathLocator = null;
foreach (var dir in dirs)
{
var qry1 = string.Format(GetPathLocatorQry, table, string.IsNullOrEmpty(pathLocator) ? "is null" : "= @pathLocator");
SqlCommand cmd1 = new SqlCommand(qry1, conn);
cmd1.Parameters.Add(new SqlParameter("@pathLocator", pathLocator));
cmd1.Parameters.Add(new SqlParameter("@dir", dir));
pathLocator = cmd1.ExecuteScalar() as string;
if (string.IsNullOrWhiteSpace(pathLocator))
{
break;
}
}
return(pathLocator);
}
public string CreateFile(string table, string fileName, string pathId, byte[] data, SqlConnection conn)
{
SqlConnManager.IsConnected(conn);
if (!FileTableExists(table, conn))
{
throw new Exception("Table does not exists or is not a FileTable.");
}
if (string.IsNullOrWhiteSpace(pathId))
{
pathId = HierarchyBuilder.NewChildHierarchyId(null);
}
var insertQry = "INSERT INTO {0} (name, file_stream, path_locator) "
+ " OUTPUT Inserted.path_locator.ToString()"
+ " VALUES (@fileName, @data, HierarchyId::Parse(@pathId))";
var qry = string.Format(insertQry, table);
var cmd = new SqlCommand(qry, conn);
cmd.Parameters.Add(new SqlParameter("@fileName", fileName));
cmd.Parameters.Add(new SqlParameter("@data", data));
cmd.Parameters.Add(new SqlParameter("@pathId", pathId));
var hierarchyid = cmd.ExecuteScalar() as string;
return(hierarchyid);
}
