将目录中的文件与SQL Server表中的文件名匹配 [英] Matching file in a directory to a filename in a SQL Server table

问题描述

在aspx页的代码(C#)中，以下
代码检索指定目录中的所有文件
并将它们显示在下拉列表中.这部分代码可以正常工作:

In the code-behind (C#) of an aspx page the following
code retrieves all the files in a specified directory
and displays them in a dropdownlist. This portion of code works correctly:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.IO;

public partial class _Default : System.Web.UI.Page 
{
    protected void Page_Load(object sender, EventArgs 
e)
    {
    }
    protected void Button1_Click(object sender, 
EventArgs e)
        
    {
        DirectoryInfo di = new DirectoryInfo(@"C:\Test");
        foreach (FileInfo f in di.GetFiles())
        {
            
            DropDownList1.Items.Add(f.Name);
        }
    }
}

我需要以下任务的帮助:

检查目录中的每个文件名以查看
它是否存在于SQL Server表tbl_ImportFileNames中.如果tbl_ImportFileNames.ImportFileName中不存在"f.name"，则
然后我需要将其插入表格中.

例如，如果excelImport2003.xls作为文件名存在于我的目录中，但不存在于SQL Server表中，则需要将文件名插入SQL Server表中.
这是我在SQL Server中创建的T-SQL(很可能需要更正)...

I need some assistance with the following task:

Checking each filename in the directory to see
whether it exists in the SQL Server table tbl_ImportFileNames. If "f.name" does not exist in tbl_ImportFileNames.ImportFileName,
then I need to insert it into the table.

For example, if excelImport2003.xls exists as a filename in my directory, but not in the SQL Server table, I need to insert the filename into the SQL Server table.

This is the T-SQL (most likely needs correction) I have created in SQL Server ...

DECLARE @ImportFileName varchar(50)
set @ImportFileName = '??? f.name';

INSERT INTO tbl_ImportFileNames (ImportFileName)
SELECT ImportFileName
FROM tbl_ImportFileNames
WHERE NOT EXISTS (SELECT ImportFileName FROM 
tbl_ImportFileNames
WHERE @ImportFileName = 
tbl_ImportFileNames.ImportFileName);

如何将上面的查询合并到我的aspx C#代码中-
在后面?非常感谢您的帮助！

How do I incorporate the query above into my aspx C# code-
behind? I would so appreciate any assistance!

推荐答案

创建一个类似于
的存储过程

Create a stored procedure like

CREATE PROCEDURE sp_AddFileNameIfNotExists
(
 Filename AS NVARCHAR(MAX)
)
AS
BEGIN
DECLARE @ImportFileName varchar(50)
set @ImportFileName = Filename;

INSERT INTO tbl_ImportFileNames (ImportFileName)
SELECT ImportFileName
FROM tbl_ImportFileNames
WHERE NOT EXISTS (SELECT ImportFileName FROM 
tbl_ImportFileNames
WHERE @ImportFileName = 
tbl_ImportFileNames.ImportFileName);

END

并在代码中使用存储过程
像

And Use The Stored Procedure in your code
Like

EXEC sp_AddFileNameIfNotExists(filename)

这篇关于将目录中的文件与SQL Server表中的文件名匹配的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！