none
Directory to SQL RRS feed

  • Question

  • im trying to insert directory and its subs and pdf files into SQL , no error but noting inserted , pleas help 

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

    and the class of the tree loader is :

    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;
            }
        }
    }


    Monday, April 2, 2018 1:53 PM

Answers