compare file in database

Hi, I have a system that can save and read CSV files and store the records in the database.

The problem is how to compare if the file already save in database. If there is a new file, the system will save the file name and last write time.

foreach (FileInfo file in filess)
                {
                    var lf = "insert into listfile(filename,lastWriteTime)values(@filename,@lastWriteTime)";
                    var cmd = new NpgsqlCommand(lf, conn);

                    DateTime lastWriteTime = file.LastWriteTime;

                    cmd.Parameters.AddWithValue("filename", Convert.ToString(file));
                    string filename = cmd.Parameters["@filename"].Value.ToString();
                    Console.WriteLine("Record inserted successfully. filename = " + file);

                    cmd.Parameters.AddWithValue("file last write time", Convert.ToDateTime(lastWriteTime));
                    string lastwritetime = cmd.Parameters["@lastWriteTime"].Value.ToString();
                    Console.WriteLine("Record inserted successfully. file last write time = " + lastWriteTime);

                    Console.WriteLine("File list : " + file);
                    Console.WriteLine("Time last Edit : " + lastWriteTime);

                    if (file.Exists)
                    {
                        var sel = "select * from listfile";
                        var cmd1 = new NpgsqlCommand(sel, conn);
                    }
                    else
                    {

                    }
                }

I'm stuck at here, help me please

foreach(FileInfo file in filess) {
if (file.Exists) {
var sel = "select * from listfile where filename=@filename";
var cmd1 = new NpgsqlCommand(sel, conn);
cmd1.Parameters.AddWithValue("filename", Convert.ToString(file));
cmd1.ExecuteNonQuery()
}
else {
var lf = "insert into listfile(filename,lastWriteTime)values(@filename,@lastWriteTime)";
var cmd = new NpgsqlCommand(lf, conn);
DateTime lastWriteTime = file.LastWriteTime;
cmd.Parameters.AddWithValue("filename", Convert.ToString(file));
string filename = cmd.Parameters["@filename"].Value.ToString();
Console.WriteLine("Record inserted successfully. filename = " + file);
cmd.Parameters.AddWithValue("file last write time", Convert.ToDateTime(lastWriteTime));
string lastwritetime = cmd.Parameters["@lastWriteTime"].Value.ToString();
Console.WriteLine("Record inserted successfully. file last write time = " + lastWriteTime);
Console.WriteLine("File list : " + file);
Console.WriteLine("Time last Edit : " + lastWriteTime);
cmd.ExecuteNonQuery()
}
}

//Get the result of the query as a CSV stream.
//http://www.bin-co.com/php/scripts/csv_import_export/
function CSVExport($query) {
$sql_csv = mysql_query($query) or die("Error: " . mysql_error()); //Replace this line with what is appropriate for your DB abstraction layer
header("Content-type:text/octect-stream");
header("Content-Disposition:attachment;filename=data.csv");
while($row = mysql_fetch_row($sql_csv)) {
print '"' . stripslashes(implode('","',$row)) . "\"\n";
}
exit;
}
//Import the contents of a CSV file after uploading it
//http://www.bin-co.com/php/scripts/csv_import_export/
//Aruguments : $table - The name of the table the data must be imported to
// $fields - An array of fields that will be used
// $csv_fieldname - The name of the CSV file field
function CSVImport($table, $fields, $csv_fieldname='csv') {
if(!$_FILES[$csv_fieldname]['name']) return;
$handle = fopen($_FILES[$csv_fieldname]['tmp_name'],'r');
if(!$handle) die('Cannot open uploaded file.');
$row_count = 0;
$sql_query = "INSERT INTO $table(". implode(',',$fields) .") VALUES(";
$rows = array();
//Read the file as csv
while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
$row_count++;
foreach($data as $key=>$value) {
$data[$key] = "'" . addslashes($value) . "'";
}
$rows[] = implode(",",$data);
}
$sql_query .= implode("),(", $rows);
$sql_query .= ")";
fclose($handle);
if(count($rows)) { //If some recores were found,
//Replace these line with what is appropriate for your DB abstraction layer
mysql_query("TRUNCATE TABLE $table") or die("MySQL Error: " . mysql_error()); //Delete the existing records
mysql_query($sql_query) or die("MySQL Error: " . mysql_error()); // and insert the new ones.
print 'Successfully imported '.$row_count.' record(s)';
} else {
print 'Cannot import data - no records found.';
}
}

6 Replies

Know the answer? Post it — somebody with the same question will find it here.

Jenith Thakkar6y ago
Accepted answer
1. foreach(FileInfo file in filess) {
3. if (file.Exists) {
4. var sel = "select * from listfile where filename=@filename";
5. var cmd1 = new NpgsqlCommand(sel, conn);
6. cmd1.Parameters.AddWithValue("filename", Convert.ToString(file));
7. cmd1.ExecuteNonQuery()
8. }
9. else {
10. var lf = "insert into listfile(filename,lastWriteTime)values(@filename,@lastWriteTime)";
11. var cmd = new NpgsqlCommand(lf, conn);
13. DateTime lastWriteTime = file.LastWriteTime;
15. cmd.Parameters.AddWithValue("filename", Convert.ToString(file));
16. string filename = cmd.Parameters["@filename"].Value.ToString();
17. Console.WriteLine("Record inserted successfully. filename = " + file);
19. cmd.Parameters.AddWithValue("file last write time", Convert.ToDateTime(lastWriteTime));
20. string lastwritetime = cmd.Parameters["@lastWriteTime"].Value.ToString();
21. Console.WriteLine("Record inserted successfully. file last write time = " + lastWriteTime);
23. Console.WriteLine("File list : " + file);
24. Console.WriteLine("Time last Edit : " + lastWriteTime);
25. cmd.ExecuteNonQuery()
26. }
27. }
Hi the code should look like this
Try and update me