How to Reference Filenames against SQL Data - Please Help

Question

Hi,
I'll explain a little...
1, I have a list of files invoices in pdf format (invoice filenames are the CustomerCode) that i want to send to the customer
2, I have a database with the customercode's and email address's.
3, I know how to do the mailing side of things but i do i make a match between the invoice filenames and the CustomerCode to Add that invoice as an attachment and send to the email address record that holds that account number?
Invoice files are held C:\billing\Invoices\*.pdf
And i have a linq to SQL Class with the Customer Table in which holds all the customercodes and email addresses.
I'd be so grateful if you could help me with this.
Thanks
Anthony

Micke Blomqvist · Accepted Answer

Hi Anthony,
Here is a sample project. Simple, but I think you get the point.
Just select the solder containing your PDF invoices, make sure that you change the method private DataTable loadDataTable()  so that you fill your customerrecords in a datatable here. The app assumes that the customercode column is named customercode and that the column with the e-mailaddres are named emailaddress, but this you can easilly change as you wish.
Please click the good post button if you think this was useful, thanks!
Micke

Anthony Clarke · Answer

Thanks alot Micke, greatly appreciated.
Regards
Anthony

Anthony Clarke · Answer

Hi Micke,
That sounds good but i still cannot get it to function. Would you be able to provide me with it in a solution file please.
It's really stressing me out.
Thanks alot
Anthony

Suthish Nair · Answer

Anthony,
Can we do like this., just an example. Work on this logic with LINQ.
As you said files are stored with CustomerCode.pdf.
Select CustomerCode, 'C:\billing\Invoices\' + CustomerCode + '.pdf' from Customer
where CustomerCode = 123

Micke Blomqvist · Answer

Hi Anthony,
Here is a short example achiving what you want just using standard DataTable Select method, just another approach
1. Create a method for sending the mail, parameter customercode and filepath, for example SendInvoice(string customercode, string filePath)
2. Select the customercodes and e-mail addressrecords from the database and read them into a dataset.
3. Do a foreach in the folder with the PDF invoices, like this:
foreach(string file in System.IO.Directory.GetFiles(@"C:\billing\invoices"))
{
string fallback_emailaddress = your_email@your_domain.com ;
// Split the filename into "customercode.pdf"
string [] filename_with_slashes = file.Split(new char [] {'\'});
string filename_without_slashes = filename_with_slashes[filename_with_slashes.Length - 1];
// Retreive the customercode only from the filename
string [] filename_without_extention = filename_without_slashes.Split(new char [] {'.'});
string customercode = filename_without_extention[filename_without_extention.Length - 1];
//Select the DataRow from the DataSet containing the correct e-mail address
System.Data.DataRow [] drs = ds_with_customercode_and_emailaddress.Table[0].Select("custmercode = '" + customercode + "'");
if(drs.Length == 1)
{
SendInvoice(drs[0]["column_with_customer_emailaddress"].ToString(), filename_with_slashes);
}
else
{
//Send mail using fallback e-mailaddress
SendInvoice(fallback_emailaddress, filename_with_slashes);
}
}
Helpful?

Mamta M · Answer

Here's what you can do:
1) Enumerate through all the files, one by one (this article may help you for the same)
2) As you retrieve each file name, store into a string variable.
3) Retrieve customer codes too one by one from the LINQ to SQL class. Compare the customer code with the string variable containing the file name. And you will find a match when they are equal!