将目录结构转换为SQL [英] Convert directory structure into SQL

问题描述

我试图将目录及其子目录及其中的pdf文件插入SQL Server 

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 ? 

下面是代码：

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

按钮点击事件：

推荐答案

您好，

您永远不会调用ExecuteNonQuery

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;

此外，你有代码来获取新的id但从不使用它所以我会摆脱select id = scopy_identity。

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

参见
Operations.cs ，查看add方法以了解如何正确插入。

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

另外如何插入文件

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

这篇关于将目录结构转换为SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！