1.运行效果
2.初始化数据库表
CREATE TABLE [dbo].[RC_File_FileUpload](
[guid] [nvarchar](40) NOT NULL,
[type] [nvarchar](10) NULL,
[name] [nvarchar](500) NULL,
[fullpath] [nvarchar](1000) NULL,
[parentpath] [nvarchar](600) NULL,
[extension] [nvarchar](30) NULL,
[length] [nvarchar](30) NULL,
[creationtime] [nvarchar](20) NULL,
[level] [int] NULL,
[isroot] [int] NULL,
[inputdate] [datetime] NULL,
CONSTRAINT [PK_RC_File_FileUpload] PRIMARY KEY NONCLUSTERED
(
[guid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[RC_File_FileUpload] ADD CONSTRAINT [DF_RC_Api_File_inputdate] DEFAULT (getdate()) FOR [inputdate]
3.C#代码(请将代码中的ConnectionString替换为你自己的数据库链接字符串)
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.IO;
namespace RC.Software.FileManager
{
/// <summary>
/// 读取文件结构信息保存到SQL Server数据库辅助类
/// </summary>
public abstract class FileManager
{
/// <summary>
/// 存放文件结构数据库链接字符串
/// </summary>
protected static string ConnectionString = "Server=.;DataBase=***;User Id=***;Pwd=***;Persist Security Info=True;pooling=false;";
#region 第一步:将文件信息读取成DataTable
/// <summary>
/// 文件表格
/// </summary>
protected static DataTable FileTable = new DataTable();
/// <summary>
/// 将文件结构信息转换为DataTable
/// </summary>
/// <param name="path"></param>
/// <returns></returns>
public static DataTable GetFileTable(string path)
{
InitTable();
int exsit;
using (var conn = new SqlConnection(ConnectionString))
{
conn.Open();
var cmd = new SqlCommand("SELECT COUNT(*) FROM RC_File_FileUpload WHERE fullpath=@fullpath AND type='dir' AND isroot=1", conn);
var parm = new SqlParameter() { ParameterName = "fullpath", Value = path };
cmd.Parameters.Add(parm);
exsit = Convert.ToInt32(cmd.ExecuteScalar());
}
if (exsit == 0)
{
var dir = new DirectoryInfo(path);
var row = FileTable.NewRow();
row["guid"] = Guid.NewGuid().ToString("N");
row["type"] = "dir";
row["name"] = dir.Name;
row["fullpath"] = dir.FullName;
row["extension"] = "";
row["length"] = "";
row["creationtime"] = dir.CreationTime;
if (dir.Parent != null) row["parentpath"] = dir.Parent.FullName;
row["level"] = System.Text.RegularExpressions.Regex.Matches(dir.FullName, @"\\").Count;
row["isroot"] = 1;
FileTable.Rows.Add(row);
}
ProcessPath(path);
return FileTable;
}
/// <summary>
/// 初始化文件表结构并清空数据
/// </summary>
protected static void InitTable()
{
InitColumn("guid");
InitColumn("type");
InitColumn("name");
InitColumn("fullpath");
InitColumn("parentpath");
InitColumn("extension");
InitColumn("length");
InitColumn("creationtime");
InitColumn("level");
InitColumn("isroot");
FileTable.Clear();
}
/// <summary>
/// 初始化列
/// </summary>
/// <param name="name"></param>
protected static void InitColumn(string name)
{
if (!FileTable.Columns.Contains(name)) FileTable.Columns.Add(name);
}
/// <summary>
/// 根据路径将文件信息读取成DataTable
/// </summary>
/// <param name="path"></param>
protected static void ProcessPath(string path)
{
var dir = new DirectoryInfo(path);
var files = dir.GetFiles();
var directories = dir.GetDirectories();
foreach (var file in files)
{
var row = FileTable.NewRow();
row["guid"] = Guid.NewGuid().ToString("N");
row["type"] = "file";
row["name"] = file.Name;
row["fullpath"] = file.FullName;
if (file.Directory != null) row["parentpath"] = file.Directory.FullName;
row["extension"] = file.Extension;
row["length"] = file.Length;
row["level"] = System.Text.RegularExpressions.Regex.Matches(file.FullName, @"\\").Count;
row["creationtime"] = file.CreationTime;
row["isroot"] = 0;
FileTable.Rows.Add(row);
}
foreach (var info in directories)
{
var row = FileTable.NewRow();
row["guid"] = Guid.NewGuid().ToString("N");
row["type"] = "dir";
row["name"] = info.Name;
row["fullpath"] = info.FullName;
if (info.Parent != null) row["parentpath"] = info.Parent.FullName;
row["extension"] = "";
row["length"] = "";
row["creationtime"] = info.CreationTime;
row["level"] = System.Text.RegularExpressions.Regex.Matches(info.FullName, @"\\").Count;
row["isroot"] = 0;
FileTable.Rows.Add(row);
ProcessPath(info.FullName);
}
}
#endregion
#region 第二步:将文件结构信息保存到数据库
/// <summary>
/// 初始化表结构
/// </summary>
/// <returns></returns>
protected static bool InitDbFileTable()
{
try
{
var sql = @"IF NOT EXISTS ( SELECT *
FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[RC_File_FileUpload]')
AND type IN ( N'U' ) )
BEGIN
CREATE TABLE [dbo].[RC_File]
(
[guid] [nvarchar](50) NULL,
[type] [nvarchar](10) NULL,
[name] [nvarchar](500) NULL,
[fullpath] [nvarchar](2000) NULL,
[extension] [nvarchar](50) NULL,
[length] [nvarchar](50) NULL,
[creationtime] [nvarchar](20) NULL
)
ON [PRIMARY]
END";
using (var con = new SqlConnection(ConnectionString))
{
con.Open();
var cmd = new SqlCommand(sql, con);
cmd.ExecuteNonQuery();
}
return true;
}
catch (Exception)
{
return false;
}
}
/// <summary>
/// 将文件结构表保存到数据库
/// </summary>
/// <param name="table"></param>
/// <returns></returns>
public static bool SaveFileTable(DataTable table)
{
try
{
InitDbFileTable();
using (var conn = new SqlConnection(ConnectionString))
{
var bulkCopy = new SqlBulkCopy(conn)
{
DestinationTableName = "RC_File_FileUpload",
BatchSize = FileTable.Rows.Count
};
conn.Open();
bulkCopy.WriteToServer(table);
conn.Close();
}
return true;
}
catch (Exception ex)
{
return false;
}
}
#endregion
}
}
因为使用了SqlBulkCopy,写入数据库时间相对还算比较快。