示例#1
0
        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);
        }
示例#2
0
        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);
        }
示例#3
0
        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);
        }
示例#4
0
        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);
        }
示例#5
0
        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);
        }
示例#6
0
        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);
        }
示例#7
0
        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());
        }
示例#8
0
        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());
        }
示例#9
0
        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());
        }
示例#10
0
        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);
        }
示例#11
0
        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);
        }