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