Match between Database image file path and file system image file path in C#

Question

using (SqlCommand cmd = new SqlCommand("Select ImageFileName From ImageDetails Where ImageFileName NOT IN (Select ImageFileName From ImageDetails Where ImageFileName=@imgname) ", conn))
            {
                conn.Open();

                try
                {
                    using (SqlDataReader myDataReader = cmd.ExecuteReader())
                    {
                        var files = Directory.GetFiles(rootPath);
                        foreach (string file in files)
                        {
                            cmd.Parameters.AddWithValue("@imgname",file);
                        }

                        while (myDataReader.Read())

                        {  
                            int columnb = 1;
                            string t = (string)myDataReader[columnb];
                            Console.WriteLine(t);
                        }
                    }
                }
                catch (Exception e)
                {
                    Console.WriteLine("Error: " + e.Message);
                }
                finally
                {
                    conn.Close();
                    Console.ReadLine();
                }

**I tried to execute this code but an error occured Error: Must declare the scalar variable "@imgname".
I just want to run a query that search for images path in db and compare it to that in file system ,then I want to delete the unmatched paths from the file system
Thank you **

Answer 1

The code you wrote isn't going to work correctly. The command is executed as soon as you call ExecuteReader. At that point you haven't added the @imgname parameter the command and hence the error. But even if you add the parameter before the command it still isn't going to work correctly. A reader just reads the results of a single command until there are no more results. If you want to run the command again (with a new value for a parameter) you have to execute the command again. Each reader is tied to one and only one execution of a command.

Looking at what you want to do you can execute the command once and pass it the list of all the filenames you want to exclude. Then you can use the reader to read the results so the structure of your code will work but you have to adjust the SQL query itself.

using (var conn = new SqlConnection(connString))
{
    //Query for all images that don't match a file in the file system
    var query = "SELECT ImageFileName FROM ImageDetails WHERE ImageFileName NOT IN (@existingImages)";    
    var cmd = new SqlCommand(query, conn);

    //Build the list of filenames we don't care about                
    var existingParameterList = new List<string>();
    var index = 0;
    foreach (var existingFile in existingFiles)
    {
        var paramName = $"@existing{index++}";
        cmd.Parameters.AddWithValue(paramName, existingFile);
        existingParameterList.Add(paramName);
    };
    cmd.CommandText = cmd.CommandText.Replace("@existingImages", String.Join(",", existingParameterList));

    //Execute the query
    conn.Open();
    using (var reader = cmd.ExecuteReader())
    {
        //Enumerate the results
        while (reader.Read())
        {
            //Columns are zero based
            var filename = reader.GetString(0);
            Console.WriteLine(filename);
        };
    };
};

This code is replacing your existing code. But reading your last line it says you want to find the files in the filesystem that don't exist in the DB but your query isn't actually going to do that. It's going to return the filenames from the DB that aren't in the file system. If you want to go the other way then you'll need to get all the file names from the file system and then remove any that are in the database (so you'll need all the filenames in the DB).

var imagesInDB = new List<string>();
using (var conn = new SqlConnection(connString))
{
    //Get all the image filenames
    var query = "SELECT ImageFileName FROM ImageDetails";
    var cmd = new SqlCommand(query, conn);

    //Execute the query                 
    conn.Open();
    using (var reader = cmd.ExecuteReader())
    {
        while (reader.Read())
        {
            imagesInDB.Add(reader.GetString(0));                        
        };
    };
};

//Now get all the files in the filesystem
//NOTE: This call returns the full path information but we assume the DB is only storing the filename
//so we will strip out the paths
var existingFiles = Directory.GetFiles(rootPath).Select(x => Path.GetFileName(x));

//Get all the files in the file system that aren't also in the DB
var missingFiles = existingFiles.Except(imagesInDB, StringComparer.OrdinalIgnoreCase);

//Remove the files from the filesystem
foreach (var file in missingFiles)
    File.Delete(file);

Answer 2

Rather than use AddWithValue, define the parameter right before the connection .Open using Parameters.Add.

Then in the foreach, assign the value for @imgname

Currently you are repeatedly adding @imgname and as mentioned above define once, assign many.

Or create a WHERE IN using code such as the following and if that is of interest consider it (I can provide a sample call if needed).

using System.Linq;  
using System.Text;  
  
namespace SqlHelperLibrary  
{  
    /// <summary>  
    /// Generate SELECT WHERE IN  
    /// </summary>  
    /// <remarks>  
    /// Assumes SQL-Server, if not the switch in CreateWhereStatement() needs adjustment for dates  
    /// </remarks>  
    public class SqlGenerator  
    {  
        public SqlGenerator()  
        {  
            Separator = ',';  
        }  
        /// <summary>  
        /// used to split values for creating IN clause  
        /// </summary>  
        public char Separator { get; set; }  
        /// <summary>  
        /// Data type for IN clause, used to determine format e.g. string has apostrophes while int does not  
        /// </summary>  
        public DataTypes DataType { get; set; }  
        private string _inClause;  
        /// <summary>  
        /// Final SQL statement  
        /// </summary>  
        public string Statement => _inClause;  
        private string _where;  
        public string Where => _where;  
        private bool _isValid;  
        public bool IsValid => _isValid;  
        /// <summary>  
        /// Column name to create WHERE IN for  
        /// </summary>  
        public string ColumnName { get; set; }  
        /// <summary>  
        /// SELECT without WHERE  
        /// </summary>  
        public string SelectStatement { get; set; }  
        /// <summary>  
        /// Delimited string to use in the IN clause  
        /// </summary>  
        public string DelimitedValues { get; set; }  
        /// <summary>  
        /// Generate SQL SELECT/WHERE IN  
        /// </summary>  
        public void CreateWhereStatement()  
        {  
  
            if (!string.IsNullOrWhiteSpace(DelimitedValues) && !string.IsNullOrWhiteSpace(SelectStatement))  
            {  
  
                if (!string.IsNullOrWhiteSpace(ColumnName) && !(Separator == '\0'))  
                {  
  
                    string[] splitTokensArray = DelimitedValues.Split(Separator);  
  
                    var sb = new StringBuilder();  
                    var whereConcatenate = "";  
                    foreach (var item in splitTokensArray)  
                    {  
                        switch (DataType)  
                        {  
                            case DataTypes.String:  
                                sb.Append($"'{item.Replace("'", "''")}',");  
                                whereConcatenate = $" WHERE {ColumnName} ";  
                                break;  
                            case DataTypes.Integer:  
                            case DataTypes.Double:  
                            case DataTypes.Decimal:  
                                sb.Append($"{item},");  
                                whereConcatenate = $" WHERE {ColumnName} ";  
                                break;  
                            case DataTypes.Datetime2:  
                            case DataTypes.Date:  
                                sb.Append($"'{item}',");  
                                whereConcatenate = $" WHERE CAST({ColumnName} AS date) ";  
                                break;  
                            case DataTypes.DateTimeOffSet:  
                                sb.Append($"'{item}',");  
                                whereConcatenate = $" WHERE CAST({ColumnName} AS DATETIMEOFFSET(4))) ";  
                                break;  
                        }  
                    }  
  
                    var joinedTokens = sb.ToString();  
  
                    if (joinedTokens.Last() == ',')  
                    {  
                        joinedTokens = joinedTokens.Substring(0, joinedTokens.Length - 1);  
                    }  
  
                    var whereTokens = "(" + joinedTokens + ")";  
                    _where = whereTokens;  
  
                    _inClause = $"{SelectStatement} {whereConcatenate} IN " + whereTokens;  
                    _isValid = true;  
  
                }  
                else  
                {  
                    _isValid = false;  
                }  
            }  
            else  
            {  
                _isValid = false;  
            }  
        }  
    }  
}  

Answer 3

@Karen Payne MVP
I tried to rewrite the code the way that you said ,but the error is the same .I'm new to C# .

using (SqlCommand cmd = new SqlCommand("Select ImageFileName From ImageDetails Where ImageFileName NOT IN (Select ImageFileName From ImageDetails Where ImageFileName=@imgname) ", conn))  
            {  
                conn.Open();  
                // 2. define parameters used in command object  
                SqlParameter param = new SqlParameter();  
                param.ParameterName = "@imgname";  
                try  
                {  
                    using (SqlDataReader myDataReader = cmd.ExecuteReader())  
                    {  
                        
                            var files = Directory.GetFiles(rootPath);  
                            foreach (string file in files)  
                            {  
                                param.Value = file;  
                            }  
                        // 3. add new parameter to command object  
                        cmd.Parameters.Add(param);  
                          
                        // write each record  
                        while (myDataReader.Read())  
                        {  
                            Console.WriteLine("{0}",  
                                myDataReader["ImageFileName"]);  
                        }  
                    }  
                }  

Answer 4

every time you add a parameter it needs a unique. sql does not support parameter arrays. so if you were going to check for two files the sql is:

"Select ImageFileName From ImageDetails Where ImageFileName NOT IN (Select ImageFileName From ImageDetails Where ImageFileName in (@imgname1, @imgname2)" 

and add a parameter for each filename. (air code)

using (var conn = new SqlConnection(connString)) 
{ 
    var cmd = new SqlCommand(); 
    var query = ""; 
    var files = Directory.GetFiles(rootPath); 
    var count = 0; 
    foreach (string file in files) 
    { 
        query += $"{count == 0 ? "" : ","}@existingImages{count}"; 
        cmd.Parameters.AddWithValue($"@imgname{count}", file); 
        ++count; 
    } 
    query = $"SELECT ImageFileName FROM ImageDetails {count == 0 ? "" : $"WHERE ImageFileName NOT IN ({query})}"; // fix colorizer " 

    cmd.Commandtext = query; 
    cmd.Connection = conn; 

    try 
    { 
        conn.Open(); 
        using (SqlDataReader myDataReader = cmd.ExecuteReader()) 
        { 
            while (myDataReader.Read()) 
            {   
                int columnb = 1; 
                string t = (string)myDataReader[columnb]; 
                Console.WriteLine(t); 
            } 
        } 
    } 
    catch (Exception e) 
    { 
        Console.WriteLine("Error: " + e.Message); 
    } 
    finally 
    { 
        conn.Close();  //not required due to using 
        Console.ReadLine(); 
    } 
}