Problems with checking particular columns of uploaded data against the existing database

Question

I have got an upload button on my webportal through which the customers can upload a csv file. After uploading the .csv file the csv data will be displayed on datagrid view on the screen, After uploading, the customer is suppose to verify data by clicking on verify button which basically checks the uploaded data have got the right data in particular columns against the existing database.What I want to do is to verify particular column like (productid) against the dbo.existingcustomers*. If the respective productid is present in thedbo.existing customer then status(column in the dbo.exisitingcustomers) should be populated with "ok" If the productid is notpresent in the dbo.exisiting then the Status(column in the dbo.exisitingcustomers) should be populated with "Please Check Your productid". After checking each row of the uploaded .csv file the system should display the data with an extra column named Status which will show the above messages for each row.Any help will be highly appreciated.I can upload the csv file and insert the data into sql server but I am having problems with checking the existing productid. As I want to make sure that the customers enterthe right productid which is present on my sql server, If they upload the wrong one which does not exist on the live database, they should amend it in order to proceed to next step. At present the customers have all the respective productid, I just want to check it if by mistake they typed the wrong productid which will save a huge amount of time*dbo.existingcustomers = table(sql server I have got an upload button on my webportal through which the customers can upload a csv file. After uploading the .csv file, the csv data will be displayed on datagrid view on the screen, After uploading, the customer is suppose to verify data by clicking on verify button which basically checks the uploaded data have got the right data in particular columns against the existing database.

What I want to do is to verify particular column like (productid) against the dbo.existingcustomers*. If the respective productid is present in the dbo.existing customer then status(column in the dbo.exisitingcustomers) should be populated with "ok" If the productid is not present in the dbo.exisiting then the Status(column in the dbo.exisitingcustomers) should be populated with "Please Check Your productid". After checking each row of the uploaded .csv file the system should display the data with an extra column named Status which will show the above messages for each row.

Any help will be highly appreciated.

I can upload the csv file and insert the data into sql server but I am having problems with checking the existing productid. As I want to make sure that the customers enter the right productid which is present on my sql server, If they upload the wrong one which does not exist on the live database, they should amend it in order to proceed to next step . At present the customers have all the respective productid, I just want to check it if by mistake they typed the wrong productid which will save a huge amount of time.

If any one can help me to write a query which can sort out this issue.

*dbo.existingcustomers = table(sql server)

Answer 1

In general, you'd need to write code to cycle through the datagrid and execute a query to check each field - something like

select * from [dbo.existingcustomers] where productid = ?

Without further information about your database it's tough to be clearer.

Share and enjoy.

Answer 2

How many product id's are exist in the dbo.existingcustomers table? How many rows are exist in a typical csv file?

If user upload a file which contains more than 5-10 rows and you have not more than 100-200 product IDs in the table, the better way is select all IDs at once then search through collection of IDs in your code, because multiple SQL queries in loop is not a good idea.

Moreover, even if you have 1000-2000 product IDs in the table, you can use this way, but need to use more intelectual algorithm for looking ID in a collection of IDs, like binary search (for example, Array.BinarySearch in .net framework).