VB.NET & Excel: please advise.

[dsk4]

Please forgive my ignorance, I'm still a babe in the woods when it comes to programming - I'm sorry if my terminology is not clear.

I would like to build a user accessible (changeable) database for an independent project. I am going to be using VB.NET for the UI and front end, and would like to use Excel (07) for the backend. My questions are these:

- Is this an appropriate choice of front/back?
- Where can I find good info on how to use VB.NET to read/write to Excel?
- Any pitfalls to watch for?

- finally, the biggest question: what am I not thinking about that I should be taking into consideration?

I am pouring through the msdn sites trying to figure this out on my own, but thought that I would ask those that know for some pointers, reference tools, etc.

Below I have pasted the abstract and proposed requirements for the project:

Abstract

The majority of the billions of stars in the sky can be classified using stellar spectroscopy. Classifying stars gives information about their temperature, luminosity, size, chemical composition and age. The goal of this project is to construct a (universally accessible) database which will take spectroscopic inputs from the user, perform a simple analysis based on those inputs, and classify the star as spectral class O, B, A, F, G, K or M. The stars information (catalogue number, user input spectroscopic data, and suggested spectral class) will then be stored in the database, and will be searchable by other users. Multiple entries for the same star will result in redundant database information.

Proposed Requirements

o Database with large, expandable size architecture
o Universally accessible user interface (via web)
o Ability to input user defined number of spectral data
o Ability to classify stellar objects by spectral data
o Ability to search database using two criteria
--> Catalog number and spectral class
o Ability to store multiple lines of user input data under one catalog number

 

[Crax]

Excel is a spreadsheet, not a database. If you plan on hosting this information online somewhere for people to interact with, excel is definitely the wrong choice.

If you plan on going the microsoft route and using vb.net, you should be using microsoft sql server as your database. You can get the free express version, or get the full one if you have an msdn license through work/school etc to do your development with before getting it hosted through a hosting company. Having a web interface means you'll be using asp.net. You can do asp.net with vb or c#, but it is a bit different than windows development.

 

[dsk4]

Excel is a spreadsheet, not a database. If you plan on hosting this information online somewhere for people to interact with, excel is definitely the wrong choice.
.

Ok, fair enough. The reason that I would like to use excel is this: there are some mature "databases" out there (~250,000 entries) with the majority of the info I want, that are downloadable in the excel format. It would be nice to be able to populate my "database" with that information. Given that excel is not a "database":

- what is the difference btwn an excel sheet (when used to store data for a VB UI that allows the user to search the sheet and query info) and a database?
- is it possible to use an excel workbook to as the read/write destination of a VB.net UI?

 

[Crax]

There are many applications that will export data from their database to a flat excel or csv file. I highly doubt they are storing 250,000+ rows in an excel spreadsheet for access over the web.

You can take the data from an excel/csv file and import it into your table structure you create. To work with excel files, you can either use one of the many 3rd party utilities out there or use the Microsoft.Office.Interop.Excel namespace: http://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel(office.11).aspx

As far as your questions go:
An excel sheet is a flat file that will have file locking issues when multiple people are accessing it at the same time and won't support any of the common things like security, stored procedures, functions, etc that are supported in a database, not to mention the basic differences between a relational database and a spreadsheet. This might help explain it better: http://valentina-db.com/en/support/knowledge-base/142-why-excel-isnt-a-database

Yes, it's possible, but slow, very slow and you generally are using COM, which sucks to work with(imo). You aren't going to have any of the advantages you get with a database when it comes to querying and storing large amounts of data

 

[PTNL]

Crax brings up several good points. Don't (necessarily) let an outside data export determine the data schema to fit your needs. Querying an Excel file doesn't scale well under load, and is not as robust or feature-rich in comparison to a DBMS (querying Excel has its own list of caveats ad considerations, such as cell type inconsistencies within a single column).

However, you at least know what the desired export format is: Excel. So import the data into a database, run your queries against it, and have an "Export to Excel" button on your form.

For the export feature, I'd recommend not using the COM Interops as previously suggested. The NPOI framework has proven a lot more robust and hardy in some recent national and international multi-user client sites I've done recently when compared to a previous iteration with the Interops. It also has the added bonus of not requiring Office to be installed on the servers instantiating the Excel generation code.

 

[Crax]

I actually agree with using NPOI, I've used it and it was great. I mentioned com as it's the standard microsoft solution to it. If you don't have any problems using a 3rd party library, NPOI is a good one.

 

[Sgraffite]

Using a database would also add more flexibility to adding new features to the site.

For example when a user enters a new star, you could store the timestamp in the database, and then display on the site somewhere the most recently added stars.

Another fictional example, it could also help teach people how to classify stars, by an explanation of how that works. Then it could pull a random star from the database and ask them to classify it, and compare what they think the classification is to what it is in the database.

 

[dsk4]

Thanks guys (& gals)! You've been a great help so far as I try to wrap my head around how I'm going to do this.

have been playing around w/ msft SQL server and it is very straight forward in terms of moving data from excel to SQL. which is happy

any tips on references/resources for using VB & SQL in tandem?

 

[PTNL]

any tips on references/resources for using VB & SQL in tandem?

Don't persist database connections; open a connection, get/set something, close connection.
If you have a good understanding about data flow and have your database schema stable, then LINQ is a good topic to look into.
Microsoft Enterprise Library has tons of interesting packages to facilitate common tasks: database communication, security, roles and memberships, encryption, etc.

 

[dsk4]

a question about SQL and its use of primary keys:

is there a work around to having a unique primary key for each row of data? I would like be able to use a catalog number to organize the data (ie: be the primary key), but would also like to have multiple lines of data for one catalog number....is there a way to do this that VB won't hate? if there is not, then what is the workaround? Would a primary key based on a row count allow for unique keys while allowing multiple rows to have the same catalog number?

Thanks!

 

[heatlesssun]

You need a unique to update a row. You can create an artificial unique primary key using an identity column.

 

[PTNL]

is there a work around to having a unique primary key for each row of data? I would like be able to use a catalog number to organize the data (ie: be the primary key), but would also like to have multiple lines of data for one catalog number....is there a way to do this that VB won't hate? if there is not, then what is the workaround? Would a primary key based on a row count allow for unique keys while allowing multiple rows to have the same catalog number?

Treat the rows with a unique key, and apply your updates based on that. You can apply any GROUP BY statements, aggregations, and ORDER BY statements in your SELECT statement.

Assuming it is a "OrderHeader" and "OrderDetail" table relationship with an OrderHeader having zero-to-many child OrderDetail records... If your OrderNumber in the OrderHeader table is absolutely unique and never changing, then your idea can work. However, I prefer to have PK/FK relationships based upon unique data that is not something the customer would ever see or recognize (e.g. GUIDs). This way, the customer can muck around with changing practically any data and all without the risk of breaking the actual parent-child links in the database.