Export the root and subdirectories to the SQL Server database

advertisements

I was wondering does anyone know of a way to export a folder structure from the file system into a SQL Server database table? I can successfully create the structure in a treeview and thought about exporting the structure into the database but can't figure out the best way to do that so that it is represented in the database table correctly. Basically what I am trying to do is create a recursive table structure with a parent/child relationship. For example - ID, Name, Parentid, etc.

If anyone has an idea or any idea - thanks in advanced.

Edit:

I can display the folder structure on the UI with a treeview with no problem which is not the issue. The issue is taking that tree structure and exporting it into the database (parent/child representation as stated above). For example I can represent the tree structure FROM the database with no problem or even from the file system, its basically doing it from the file system and exporting/importing that structure into the database. Getting it out is not the problem.

Hope that makes sense.


It's a simple tree walk. Given a table like this:

create table dbo.directory
(
  id        int not null identity(1,1) primary key clustered ,
  parent_id int     null               foreign key references dbo.directory( id ) ,
  name      varchar(256) collate SQL_Latin1_General_CP1_CI_AS not null , -- case-insensensitive, accent-sensitive

  unique ( id , name ) ,

)
go

A class like this ought to do you

class DirectoryTreeLoader : IDisposable
{
  const string          connectionString = "Server=localhost;Database=sandbox;Trusted_Connection=True;" ;
  private SqlConnection Connection       ;
  private SqlCommand    Command          ;
  private bool          CommandPrepared  ;

  private SqlParameter ParentDirectoryId ;
  private SqlParameter DirectoryName     ;

  public DirectoryTreeLoader()
  {
    Connection          = new SqlConnection(connectionString) ;
    Command             = Connection.CreateCommand() ;
    ParentDirectoryId   = new SqlParameter( "@parent_id" , SqlDbType.Int     ,   4 ) ;
    DirectoryName       = new SqlParameter( "@name"      , SqlDbType.VarChar , 256 ) ;

    ParentDirectoryId.IsNullable = true  ;

    DirectoryName.IsNullable     = false ;

    Command.Parameters.Add( ParentDirectoryId ) ;
    Command.Parameters.Add( DirectoryName     ) ;
    Command.CommandType = CommandType.Text ;
    Command.CommandText = @"
      insert dbo.directory ( parent_id , name ) values ( @parent_id , @name ) ;
      select id = scope_identity() ;
      ".Trim() ;

    return ;
  }

  public void Load( DirectoryInfo root )
  {
    if ( Connection.State == ConnectionState.Closed )
    {
      Connection.Open() ;
      Command.Prepare() ;
    }

    Visit( null , root ) ;

    return ;
  }

  private void Visit( int? parentId , DirectoryInfo dir )
  {
    // insert the current directory
    ParentDirectoryId.SqlValue = parentId.HasValue ? new SqlInt32( parentId.Value ) : SqlInt32.Null ;
    DirectoryName.SqlValue     = new SqlString( dir.Name ) ;

    object o  = Command.ExecuteScalar() ;
    int    id = (int)(decimal) o ;

    // visit each subdirectory in turn
    foreach ( DirectoryInfo subdir in dir.EnumerateDirectories() )
    {
      Visit(id,subdir) ;
    }

    return ;
  }

  public void Dispose()
  {
    if ( Command != null )
    {
      Command.Cancel();
      Command.Dispose();
      Command = null ;
    }
    if ( Connection != null )
    {
      Connection.Dispose() ;
      Connection = null ;
    }
    return ;
  }
}

Usage?

static void Main( string[] args )
{
  DirectoryInfo root = new DirectoryInfo( @"c:\inetpub" ) ;
  using ( DirectoryTreeLoader loader = new DirectoryTreeLoader() )
  {
    loader.Load(root) ;
  }

  return ;
}

If you want to eliminate the recursive Visit(), just use a stack:

public void Load( DirectoryInfo root )
{
  if ( Connection.State == ConnectionState.Closed )
  {
    Connection.Open() ;
    Command.Prepare() ;
  }

  Stack<Tuple<int?,DirectoryInfo>> pending = new Stack<Tuple<int?, DirectoryInfo>>();
  pending.Push(new Tuple<int,DirectoryInfo>(null,root) ) ;
  while ( pending.Count > 0 )
  {
    Tuple<int?,DirectoryInfo> dir = pending.Pop() ;

    // insert the current directory
    ParentDirectoryId.SqlValue = dir.Item1.HasValue ? new SqlInt32( dir.Item1.Value ) : SqlInt32.Null ;
    DirectoryName.SqlValue     = new SqlString( dir.Item2.Name ) ;
    object o  = Command.ExecuteScalar() ;
    int parentId = (int)(decimal) o ;

    // push each subdirectory onto the stack
    foreach ( DirectoryInfo subdir in dir.Item2.EnumerateDirectories() )
    {
      pending.Push( new Tuple<int?, DirectoryInfo>(parentId,subdir));
    }

  }

  return ;
}