Question compare image paths

MelodyS

Member
Joined
Nov 22, 2021
Messages
13
Programming Experience
1-3
So i'm trying to retrieve the image paths from the database as well as the same image path on file system,I need a way to compare both results to check if they are the same.
the paths are being retrieved on console application .
cronjob:
//File System
            string rootPath = @"D:\Images";

            string[] dirs = Directory.GetFiles(rootPath, "*.*", SearchOption.AllDirectories);
            foreach (string dir in dirs)
            {
                Console.WriteLine(dir);
            }

            //Sql Connection
            SqlConnection conn   = new SqlConnection(@"Data Source=DESKTOP-3PSF3SE;Initial Catalog=MediaCleaner_DB;Integrated Security=True");
            conn.Open();
            SqlDataAdapter sqlda = new SqlDataAdapter("Select ImageFileName from ImageDetails", conn);

            DataTable dtb1 = new DataTable();
            sqlda.Fill(dtb1);

            foreach (DataRow row in dtb1.Rows)
            {
                Console.WriteLine(row["ImageFileName"]);
            }
            Console.ReadLine();
 

Skydiver

Staff member
Joined
Apr 6, 2019
Messages
4,431
Location
Chesapeake, VA
Programming Experience
10+
Assuming that the ImageFileName contains the full path to the image. The results of GetFiles() will also have the full paths. So just check if there is a database entry that matches the name. If the ImageFileName does not contain the full path, then you'll have to massage the results that you get back from GetFiles() to match what should be found in the database.

(Most) Database engines are designed to be very good at searching and matching. You should query the database instead of trying to load all the rows from the database into memory and then searching your local DataTable.
 

MelodyS

Member
Joined
Nov 22, 2021
Messages
13
Programming Experience
1-3
I want to retrieve image path from db that is the same as in file system path ,I tried with this code.
Please any help to fix the below error

Search for file system path in database:
 try
            {
                //open connection
                conn.Open();

                StringBuilder strBuilder = new StringBuilder();
                strBuilder.Append("Select ImageFileName From ImageDetails Where ImageID=@imgname");
                
                string sqlQuery = strBuilder.ToString();
                using (SqlCommand command = new SqlCommand(sqlQuery, conn))
                {
                    var files = Directory.GetFiles(rootPath);

                    foreach ( string file in files)
                    {
                        string path = Path.GetFullPath(file);
                        Console.WriteLine(path);

                        command.Parameters.AddWithValue("@imgname", path);
                        int result = (int)command.ExecuteScalar();
                        if(result > 0)
                        {
                            Console.WriteLine("Done");
                        }
                        else
                        {
                            Console.WriteLine("Failed");
                        }
                    }
                }             
            }
            catch (Exception e)
            {
                Console.WriteLine("Error: " + e.Message);
            }
            finally
            {
                Console.Read();
            }

After Execution:
D:\Images\tech1.jpg
Error: Conversion failed when converting the nvarchar value 'D:\Images\tech1.jpg' to data type int.
 

JohnH

C# Forum Moderator
Staff member
Joined
Apr 23, 2011
Messages
1,362
Location
Norway
Programming Experience
10+
int result = (int)command.ExecuteScalar();
SqlCommand.ExecuteScalar Method

Executes the query, and returns the first column of the first row in the result set returned by the query.
You ask for a string path from database, then why try to convert to int?
 

MelodyS

Member
Joined
Nov 22, 2021
Messages
13
Programming Experience
1-3
You ask for a string path from database, then why try to convert to int?
I want a way to check the path from file system and database,so I tried to return result, but it's incorrect.
May you please help me to find a way to compare these paths
 

Skydiver

Staff member
Joined
Apr 6, 2019
Messages
4,431
Location
Chesapeake, VA
Programming Experience
10+
I want a way to check the path from file system and database,so I tried to return result, but it's incorrect.
You should let the database perform the comparison. You should only care if there was a match or not. You already know the path since you passed in the path to the query. Why do you need to get the path back?
 

MelodyS

Member
Joined
Nov 22, 2021
Messages
13
Programming Experience
1-3
You should let the database perform the comparison. You should only care if there was a match or not. You already know the path since you passed in the path to the query. Why do you need to get the path back?
You should let the database perform the comparison. You should only care if there was a match or not. You already know the path since you passed in the path to the query. Why do you need to get the path back?
Is the query in the above code correct ?
 

MelodyS

Member
Joined
Nov 22, 2021
Messages
13
Programming Experience
1-3
You should let the database perform the comparison. You should only care if there was a match or not. You already know the path since you passed in the path to the query. Why do you need to get the path back?
What should I insert into my sql query to let it now that @imgname is the image path from file system
 

Skydiver

Staff member
Joined
Apr 6, 2019
Messages
4,431
Location
Chesapeake, VA
Programming Experience
10+

Skydiver

Staff member
Joined
Apr 6, 2019
Messages
4,431
Location
Chesapeake, VA
Programming Experience
10+
What should I insert into my sql query to let it now that @imgname is the image path from file system
You already have it in your query. Did you read about SQL parameters? Or did you just try to copy and paste code from someone else and hope to understand it?
 

MelodyS

Member
Joined
Nov 22, 2021
Messages
13
Programming Experience
1-3
You already have it in your query. Did you read about SQL parameters? Or did you just try to copy and paste code from someone else and hope to understand it?
No, I wrote this code.
After checking ImageFileName=@imgname how should I continue to execute a delete query if ImageFileName isn't the same as @imgname .
Should I directly execute the delete query under command.Parameters.AddWithValue("@imgname", path);
I'm sorry ,but I'm new to C#.
 
Last edited:

Skydiver

Staff member
Joined
Apr 6, 2019
Messages
4,431
Location
Chesapeake, VA
Programming Experience
10+
Huh? If the file exists in the database and on the file system, you want to delete the entry in the database? It doesn't quite make sense why you may want to do this, but it's your app and you didn't give us the full context of what you are trying to do. In your original post all you said was that you were trying to do a comparison.

Anyway, it's usually best to have another command instance and pass the parameter to that instance.

If you had read about SQL parameters, the reason why you would want two commands is because the server "compiles" the command on its end the first time it sees it. If the command text doesn't change -- only the parameters change -- then it can reuse the previously "compiled" version giving you some efficiency. If you keep on replacing the command text, then the server needs to do more work.

Also consider this: If your only objective is to delete the entry from the database, if you find a match, you might as well just invoke the delete statement in your command and find out if the delete succeeded. If it found a match, the delete would succeed and tell you that at a row was deleted. If there was no match, then there wouldn't be any rows deleted. This way you only do one round trip to the SQL server instead of having to do two trips: one to find a match, and then another to delete the match.
 

MelodyS

Member
Joined
Nov 22, 2021
Messages
13
Programming Experience
1-3
Huh? If the file exists in the database and on the file system, you want to delete the entry in the database? It doesn't quite make sense why you may want to do this, but it's your app and you didn't give us the full context of what you are trying to do. In your original post all you said was that you were trying to do a comparison.

Anyway, it's usually best to have another command instance and pass the parameter to that instance.

If you had read about SQL parameters, the reason why you would want two commands is because the server "compiles" the command on its end the first time it sees it. If the command text doesn't change -- only the parameters change -- then it can reuse the previously "compiled" version giving you some efficiency. If you keep on replacing the command text, then the server needs to do more work.

Also consider this: If your only objective is to delete the entry from the database, if you find a match, you might as well just invoke the delete statement in your command and find out if the delete succeeded. If it found a match, the delete would succeed and tell you that at a row was deleted. If there was no match, then there wouldn't be any rows deleted. This way you only do one round trip to the SQL server instead of having to do two trips: one to find a match, and then another to delete the match.
I mentioned that if the two paths aren't the same I want to delete the path of the image from the file system.
 

Skydiver

Staff member
Joined
Apr 6, 2019
Messages
4,431
Location
Chesapeake, VA
Programming Experience
10+
I mentioned that if the two paths aren't the same I want to delete the path of the image from the file system.
No mention of it at all in post #1. Post #11 seems to be the first mention of deleting things. Perhaps you were cross posting to another forum?

Anyway if you want to delete from the file system, then you can delete it anytime. You already have the path to the file in your path variable on line #16 of your post #3.

As a quick aside, in my experience GetFiles() already returns the full path. I'm not sure why you are trying to get the full path again.
 

MelodyS

Member
Joined
Nov 22, 2021
Messages
13
Programming Experience
1-3
No mention of it at all in post #1. Post #11 seems to be the first mention of deleting things. Perhaps you were cross posting to another forum?

Anyway if you want to delete from the file system, then you can delete it anytime. You already have the path to the file in your path variable on line #16 of your post #3.

As a quick aside, in my experience GetFiles() already returns the full path. I'm not sure why you are trying to get the full path again.
When executing :
C#:
 string filePaths = Directory.GetFiles(@"D:\Images");
  Console.WriteLine(filePaths);

Output

System.String [] returned instead of the image paths​

 
Last edited by a moderator:

Skydiver

Staff member
Joined
Apr 6, 2019
Messages
4,431
Location
Chesapeake, VA
Programming Experience
10+
That expected result because you passed in the entire array. Recall that C# doesn't unpack collections. You need:
C#:
var filePaths = Directory.GetFiles(@"D:\Images");
foreach(var path in filePaths)
    Console.WriteLine(path);
 
Top Bottom