Database Driven Web Application Questions
Hello VB.NE Heaven!
First time poster, but have used the forums to find quality answers in the past with VBA problems I've encountered.
I have undertaken a project that has me a little lost on how to accomplish the goal.
Here is some background on what I need to do:
1) I am currently developing a client application that can be downloaded from a web page and installed on a PC which will allow a series exported CSV file from a computer program to be loaded into a dataset, manipulated in a minimal fashion (only need to add one column with a unique identifier so we know who sent the data to the web server), and upload the series of CSV files as one master CSV file to a web server hosted by a third party. This is pretty straight forward, and I have a good handle on this project already...
2) I need to then develop a database to be run on the third party web server which will store the data being uploaded by the client computers. This data will be made available via a web page which will offer a series of reports on the data. Again, I have a good handle on the database , as I have a working prototype in MS SQL Server 2008 Express edition.
3) Here is where I am at a loss. I need something on the server that can do data manipulation on the database that operates outside of the web page itself. For instance, the subject matter is freight logistics/management. Users are uploading CSV files with items that are at suppliers and retailers. The scenario is this, Supplier ABC has 1000 Fax Machines on hand. They cost $100 each. Retailer DEF needs 100 Fax Machines, and are paying $125 each. Also, Retailer GHI needs 250 Fax Machines and are paying $135 each. I have to build a plan that looks something like this (forgive the crude nature of this, I don't know how to make a table in the form look very nice:
|| Stop Type || Item || Quantity || Price || Sequence || Order ID || Vendor ID ||
| Pickup | FaxM | 350 | 350000 | 1 | 100 | ABC |
| Drop Off | FaxM | 100 | 125000 | 2 | 100 | DEF |
| Drop Off | FaxM | 250 | 337500 | 3 | 100 | GHI |
The sequencing of each route is predetermined already. Vendor ABC is in Miami FL, for Example. Vendor DEF is in Orlando, FL, and GHI is in New York, NY. We already know you need to start in Miami, and one would pass Orlando on the way to New York. Likewise, if suddenly an order came in for Louisville, KY, it knows that it becomes Sequence 3, and GHI is bumped to Sequence 4.
I need this table data to be continuously updated in the background on the web server, and the web page will simply display the results when you enter search criteria, such as Vendor ID, Item, or Order ID (that much is easy)
I need this to happen in the background because the web page is on-demand querying. A user enters in what they want to know such as how many Fax Machines are being shipped, where are they going, and in what quantities. The data needs to be there already. They can not/ do not want to wait for these results to build as the complex SQL statements execute. Also, imagine the database load if 1000 people were trying to run these queries at the same time. If one process did the analysis and added the records into the Order Plan table, the database would run much smoother. The 1000 people can run simpler select statements on the Order Plan table once the work has been done.
Now here is what I think I know:
I think that I can not build a vb.net program which continuously builds the order plans in the bakground. My reasoning behind this is simple, I do not own the web server, and the hosting company who does will not allow me to install applications on it. I can develop with the tools they provide (MS SQL Server, ASP.NET, etc)
I think Database triggers are a bad approach to this solution. As I understand, they execute each time a record is inserted. Sometimes 1000 records could be added with one upload. I do not want the trigger to execute 1000 times, when I would only really need just one run at the end of the 1000 record insert. (Trigger calls stored proc. Stored proc runs updates/inserts on data in the order plan table. Order Plan table is locked during this time. Bad things happen because of those who wait)
I think that I can maybe build something (What? I don't know, which is why I am here!) that is .NET based which will do this order planning independent of the web page itself. It needs to be something that runs continuously, like a listener waiting for new orders to be placed. How I accomplish this is a mystery to me. I don't need code examples, but what I do need is a real world approach to the technologies and general architecture of how I could pull this off.
Feel free to ask more questions if i was unclear about what I am doing, and what I need help with.
Thank you in advance to those who read this, and an extra thank in advance you to those who help!