Compare Two DataTables

[Simple Simon]

Hi,
I'm filling DataTable1 with the filenames from within a directory, and
filling another DataTable2 with filenames from a SQL Db table. I'd
like to compare the two, and delete the files from the directory that
do not occur in DataTable2

StringBuilder sb = new StringBuilder();
string strDirectory = "C:\\Temp\\";

// DataTable1 ************************************************
DataTable dt = new DataTable("DiFiles");
dt.Columns.Add(new DataColumn("filename",
Type.GetType("System.String")));

System.IO.DirectoryInfo di = new DirectoryInfo(strDirectory);

// loop thru the files in the directory
foreach (FileInfo fi in di.GetFiles())
{
DataRow dr;
dr = dt.NewRow();
dr["filename"] = fi.Name;
dt.Rows.Add(dr);

sb.Append(fi.Name);
sb.Append("<br>");
}
Response.Write(sb.ToString());


//DataTable2 ***********************************************
string strCon, strSQL;
DataSet ds = new DataSet();

strCon = "***** ************* ************** ******";

strSQL = "SELECT filename FROM Files";
SqlDataAdapter da = new SqlDataAdapter(strSQL, strCon);
da.Fill(ds, "DbFiles");

// need help here on

TIA,
~Gordon

 

[Carl Prothman [MVP]]

I'm filling DataTable1 with the filenames from within a directory, and
filling another DataTable2 with filenames from a SQL Db table. I'd
like to compare the two, and delete the files from the directory that
do not occur in DataTable2

Gordon,
There is no need to read files into DataTables.
Just use the File.Exists property.
e.g.

Imports System.IO
....

Dim path1 As String = "c:\temp1\"
Dim path2 As String = "c:\temp2\"

' Get the list of files from path1
Dim dirInfo As New DirectoryInfo(path1)
Dim fileInfoArray As FileInfo() = dirInfo.GetFiles()

' For each file in path1
Dim file1 As FileInfo
For Each file1 In fileInfoArray

' If the file does not existing in path2, delete it from path1
If File.Exists(path2 & file1.ToString()) = False Then
File.Delete(path1 & file1.ToString())
End If
Next

--

Thanks,
Carl Prothman
Microsoft ASP.NET MVP
http://www.able-consulting.com

 

[Simple Simon]

Thanks for the reply Carl...
But I'm comparing the files in a directory with the filenames stored
in a SQL Db table...what now?

~Gordon

 

[Simple Simon]

Heres what i got so far...

private void Page_Load(object sender, System.EventArgs
e)
{
StringBuilder sb = new StringBuilder();
int _count = 0;

// database files
DataTable dtDatabase = DataBaseFiles();
foreach (DataRow dr in dtDatabase.Rows)
{
sb.Append(dr["fileName"].ToString());
sb.Append("<br>");
_count++;
}
sb.Append("<b>Total: </b>" +
_count.ToString());

// reset
_count = 0;
sb.Append("<br><br><br>");

// directory files
DataTable dtDirectory = DirectoryFiles();
foreach (DataRow dr in dtDirectory.Rows)
{
sb.Append(dr["fileName"].ToString());
sb.Append("<br>");
_count++;
}
sb.Append("<b>Total: </b>" +
_count.ToString());

Response.Write(sb.ToString());
}

private DataTable DataBaseFiles()
{
string strCon, strSQL;
StringBuilder sb = new StringBuilder();
DataSet ds = new DataSet();

strCon =
"Server=SIMPLE-CLIENT\\MYSQLSERVER;Trusted_Connection=true;Database=lacoronadetucsondb2";

strSQL = "SELECT MLNumber, ListingPictureTotal
FROM Residential";
SqlDataAdapter da = new SqlDataAdapter(strSQL,
strCon);
da.Fill(ds, "DbFiles");

strSQL = "SELECT MLNumber, ListingPictureTotal
FROM LandLot";
da.SelectCommand.CommandText = strSQL;
da.Fill(ds, "DbFiles");

strSQL = "SELECT MLNumber, ListingPictureTotal
FROM Commercial";
da.SelectCommand.CommandText = strSQL;
da.Fill(ds, "DbFiles");

DataTable dt = new DataTable();
dt.Columns.Add("fileName", typeof(string));

foreach (DataRow dr in
ds.Tables["DbFiles"].Rows)
{
int _picTotal = 0;
_picTotal = (dr["ListingPictureTotal"]
== System.DBNull.Value ? 0 :
Convert.ToInt32(dr["ListingPictureTotal"]));

for (int i = 0; i < _picTotal; i++)
{
string fileName;
if (i == 0)
fileName =
dr["MLNumber"].ToString() + ".jpg";
else
fileName =
dr["MLNumber"].ToString() + "_0" + i.ToString() + ".jpg";

if (i >= 10)
fileName =
dr["MLNumber"].ToString() + "_" + i.ToString() + ".jpg";

DataRow dr2 = dt.NewRow();
dr2["fileName"] = fileName;
dt.Rows.Add(dr2);
}
}
return dt;
}

private DataTable DirectoryFiles()
{
string strDirectory = "C:\\Temp\\";

DataTable dt = new DataTable();
dt.Columns.Add("fileName", typeof(string));

System.IO.DirectoryInfo di = new
DirectoryInfo(strDirectory);
foreach (FileInfo fi in di.GetFiles())
{
DataRow dr;
dr = dt.NewRow();
dr["filename"] = fi.Name;
dt.Rows.Add(dr);
}
return dt;
}