none
Convert directory structure into SQL RRS feed

  • Question

  • im trying to insert the directory and its subsdirectories and the pdf files inside of it into SQL Server 

    when i run the code im NOT getting any errors, but NOthing is inserted into the DB table , can anyone help ? 

    below is the code :

    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=.\\SQLEXPRESS;Database=naf;Trusted_Connection=True;";
            private SqlConnection Connection;
            private SqlCommand Command;
    
    
            private SqlParameter ParentDirectoryId;
            private SqlParameter DirectoryName;
            private SqlParameter thefilename;
    
            public DirectoryTreeLoader()
            {
                Connection = new SqlConnection(connectionString);
                Command = Connection.CreateCommand();
                ParentDirectoryId = new SqlParameter("@parent_id", SqlDbType.Int, 4);
                DirectoryName = new SqlParameter("@name", SqlDbType.VarChar, 256);
                thefilename = new SqlParameter("@file", SqlDbType.VarChar, 256);
    
    
                ParentDirectoryId.IsNullable = true;
    
                DirectoryName.IsNullable = false;
    
                Command.Parameters.Add(ParentDirectoryId);
                Command.Parameters.Add(DirectoryName);
                Command.Parameters.Add(thefilename);
                Command.CommandType = CommandType.Text;
                Command.CommandText = @"
          insert dbo.directory ( parent_id , name , thefilename ) values ( @parent_id , @name , @file ) ;
          select id = scope_identity() ;
          ".Trim();
    
                return;
            }
    
            public void Load(DirectoryInfo root)
            {
                if (Connection.State == ConnectionState.Closed)
                {
                    Connection.Open();
                    Command.Prepare();
                }
    
                Visit(null, root,null);
    
                return;
            }
    
            private void Visit(int? parentId, DirectoryInfo dir, FileInfo file)
            {
                // insert the current directory
                ParentDirectoryId.SqlValue = parentId.HasValue ? new SqlInt32(parentId.Value) : SqlInt32.Null;
                DirectoryName.SqlValue = new SqlString(dir.Name);
                thefilename.SqlValue = new SqlString(file.Name);
    
                object o = Command.ExecuteScalar();
                int id = (int)(decimal)o;
    
    
    
    
                // visit each subdirectory in turn
                foreach (DirectoryInfo subdir in dir.EnumerateDirectories())
                {
    
                    foreach (var F in subdir.GetFiles())
                    {
    
    
    
    
                        Visit(id, subdir,file);
                    }
    
                    return;
                }
    
            }
    
            public void Dispose()
            {
                if (Command != null)
                {
                    Command.Cancel();
                    Command.Dispose();
                    Command = null;
                }
                if (Connection != null)
                {
                    Connection.Dispose();
                    Connection = null;
                }
                return;
            }
        }
    }

    button click event :

      private void button1_Click(object sender, EventArgs e)
            {
                DirectoryInfo root = new DirectoryInfo(@"C:\KWT_JARIDA\KWT-OG\");
                using (DirectoryTreeLoader loader = new DirectoryTreeLoader())
                {
                    loader.Load(root);
                }
                MessageBox.Show("Done");
                return;
             
            }

    Monday, April 2, 2018 1:50 PM

All replies

  • Hello,

    You never call ExecuteNonQuery

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

    Also, you have code to get the new id but never use it so I would get rid of select id = scopy_identity.

    See Operations.cs, look at the add method to see how to do a proper insert.

            public bool Add(Person pPerson) 
            { 
                bool succcess = false; 
     
                using (SqlConnection cn = new SqlConnection() { ConnectionString = ConnectionString }) 
                { 
                    using (SqlCommand cmd = new SqlCommand() { Connection = cn }) 
                    { 
                        // insert statement followed by select to get new primary key 
                        cmd.CommandText = "INSERT INTO dbo.People (FirstName,LastName,Gender,BirthDay) VALUES (@FirstName,@LastName,@Gender,@BirthDay)" + 
                                          ";SELECT CAST(scope_identity() AS int);"; 
     
     
                        cmd.Parameters.AddWithValue("@FirstName", pPerson.FirstName); 
                        cmd.Parameters.AddWithValue("@LastName", pPerson.LastName); 
                        cmd.Parameters.AddWithValue("@Gender", pPerson.Gender); 
                        cmd.Parameters.AddWithValue("@BirthDay", pPerson.BirthDay); 
     
                        cn.Open(); 
     
                        try 
                        { 
                            pPerson.Id = Convert.ToInt32(cmd.ExecuteScalar()); 
                            succcess = true; 
                        } 
                        catch (Exception) 
                        { 
                            succcess = false; 
                        } 
                    } 
                } 
     
                return succcess; 
            } 

    Also how to insert files

    https://code.msdn.microsoft.com/SQL-Server-insert-binary-0de8aef3?redir=0


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites





    Monday, April 2, 2018 1:53 PM
    Moderator