locked
inserting directory structure into SQL RRS feed

  • Question

  • User181930479 posted

    Im trying to insert a directory and its subdirectories into SQL , its working great but i cant figure out how to insert the filenames of the files inside the subdirectory...

    below is my complete code

    private void button1_Click(object sender, EventArgs e)
            {
                DirectoryInfo root = new DirectoryInfo(@"D:\website\AIO_GO");
                using (DirectoryTreeLoader loader = new DirectoryTreeLoader())
                {
                    loader.Load(root);
                }
    
                return;
              
            }

    Class :

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    using System.Data.Sql;
    using System.Data.SqlClient;
    using System.Data;
    using System.IO;
    using System.Data.SqlTypes;
    
    namespace CCMM
    {
        class DirectoryTreeLoader : IDisposable
        {
            const string connectionString = "Server=.;Database=WAM;Trusted_Connection=True;";
            private SqlConnection Connection;
            private SqlCommand Command;
            
    
            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;
            }
        }
    }
    
    

    any help ?

    Tuesday, March 27, 2018 10:09 AM

All replies

  • User516094431 posted

    As per my understanding, you need to create one separate table for Files or you can store filenames with comma (,) separator. But I recommend create separate table for containing file name info. As well you can store other information related with specific file for instance FileSize, FileType, FileVersion ETC

    If you wan to create separate table then make one column DirectoryId (it's foreign key of main directory table). Then get all files using following line of code from selected directory and insert into file name table.

    string[] allfiles = Directory.GetFiles(@"D:\website\AIO_GO", "*.*", SearchOption.AllDirectories);
    foreach (var file in allfiles){
         FileInfo info = new FileInfo(file);
     // insert file into filename table with DirectoryId
     }

    Note:

    Use SQLTransaction for inserting filename.

    Tuesday, March 27, 2018 10:29 AM
  • User181930479 posted

    can you update my code please ? 

    Tuesday, March 27, 2018 10:33 AM
  • User753101303 posted

    Hi,

    Before "// visit each directory in turn" it seems you couild just scan the directory to insert related files :

    foreach(var file in dir.GetFiles())
    {
         ExecuteNonQuery(cmdInsertFile,id,file.Name); // just a helper that populate the query with the directory id and file name and then run the query
    }

    If using a single table you could reuse the Command you are already using (and set  "is_directory" to true or false as needed).

    Or do you run into some issue ? As I said in my previous response to the same question, it is not always easy to figure out the exact problem one can have in doing something especially as it seems basically the same than what you have done already.

    Tuesday, March 27, 2018 10:54 AM
  • User181930479 posted

    Not Working :(

    Tuesday, March 27, 2018 11:00 AM
  • User753101303 posted

    And the problem is ? Did you choose if you want a single table or two tables ?

    Tuesday, March 27, 2018 11:08 AM
  • User181930479 posted

    2 tables , can you please update my code  ? thank you in advance ..

    Tuesday, March 27, 2018 11:09 AM