Click here to Skip to main content
15,850,921 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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:

C#
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);
        }
    }
}


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 ...

SQL
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);


How do I incorporate the query above into my aspx C# code-
behind? I would so appreciate any assistance!
Posted
Updated 30-Oct-10 13:00pm
v4

1 solution

Create a stored procedure like

SQL
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
SQL
EXEC sp_AddFileNameIfNotExists(filename)
 
Share this answer
 
Comments
CSharpNewcomer 1-Nov-10 17:04pm    
Do I call the stored procedure from within the foreach loop?
Also, is there a special namespace required to use EXEC for the stored procedure?

Thank you!
RDBurmon 2-Nov-10 5:54am    
yehp, you can .

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900