Ms-excel as database using c#

Question

0.00/5 (No votes)

See more:

Hello All,

Am relatively very new to this coding stuff and currently working on the ASP.NET project. My question is:

I have a two Excel files
1. Data_file_area.xls
2. Data_file_capacity.xls

and a third excel file
3. Data_operations_functions.xls
which contains functions and operation which is derived from those two excel files.

Now i have developed a front end form in asp.net where i can enter new data regularly & it is saved/updated in those two Excel files, now i need to access the third excel file with all its functions/operations in a Gridview. And the functions has to be updated automatically based on the two excel files and display it in Gridview(Realtime) as the data is updated regularly.

Is it possible?

Your answers will be very helpful.

Update:

For example:
Table1 contains following column and data.
ID | Name | Target
11 | Abcd | 123
12 | xyzx | 135

Table2 contains following columns and data.
ID | Candidate | Roll_Number
11 | asdeff | 12345
11 | dfrsf | 12346
11 | dfesf | 12347
12 | erfgv | 12348
12 | dsfdv | 12349
12 | dasfr | 12350
12 | dsadsa | 12351

Note: I have taken example names.

Table3 should store data from table1 & table2 after applying functions.
Name | Number of Candidate | Target
Abcd | 3 | 123
xyzx | 4 | 135

Note: In the third table the "Number of candidate" column should be COUNTED from table2, respectively.

I need a SQL statements which generates above data in table3.

Thanks
Saiekrishna

What I have tried:

Am unable to link Excel file to the front end.

Posted 19-Feb-16 20:51pm

Member 10376341

Updated 21-Feb-16 22:38pm

v3

Add a Solution

Comments

Sinisa Hajnal 20-Feb-16 4:13am

Delete all those files. After you transfer their data to proper database.
Advantages:
- database has better access control.
- It is harder for some fool with delete rights to completely ruin your system
- better big data set performance (after you hit certain number of rows) because, you know, database is made to work with big data sets
- easier to get custom data relations when users demand some strange reports
- better concurrent access

2 solutions

Solution 2

In addition to solution 1 by CHill60[^]...

As Caroline and Sinisa mentioned using Excel files as databases might provide several problems, because those files aren't real databases. So, you shouldn't use Excel files if you plan to store more than few records. For large data storage, every database is better then Excel file! But...

If you would like to play with Excel files as databases, you have to use OleDb[^] to be able to provide CRUD[^] operations.
See: Accessing Microsoft Office Data from .NET Applications[^]

In the past i wrote small tip, which might be helpful for you: How to: Get Data from Multiple Workbooks using One OleDbConnection?[^]

Posted 20-Feb-16 1:19am

Maciej Los

Comments

Member 10376341 20-Feb-16 13:26pm

Thank you for the answers.

I agree with @Caroline @Sinisa Hajnal @Maciej Los and @CHill60 that a database would be far more better in terms of storing and manipulating the data but am inexperienced in SQL query writing and the project which am working on needs to have a lots of complex queries to be written.

I have an excel file with all the functions and operations, i thought of using the same file to show in the front end and each time when a user enters the data it will store in the same file and do the operations accordingly.

A random question, is there any query builder which takes the functionalities from the excel file and build a SQL query?

Any help is appreciated.

Thanks
Saiekrishna

Maciej Los 20-Feb-16 13:32pm

If you have an MS Access software installed, you can create new database and connect Excel files to it. There is inbuilt query designer, which you can use to create queries. Then switch view from query designer to SQL to see sql code. This is the simplest way.

CHill60 20-Feb-16 13:46pm

A virtual 5! I have actually used the SQL view in Access to teach a colleague how to write queries properly... let her do it with the "wizard" then swapped over to sql view to demonstrate how much easier it was :-)

Maciej Los 20-Feb-16 13:51pm

Thank you, Caroline.
;)

Member 10376341 21-Feb-16 2:57am

Thank you for the reply.

Well, my SQL queries are bit more complex and very confusing. Though i shall try SQL View in Access.

Thank you for your time.
Saiekrishna

Member 10376341 21-Feb-16 3:16am

A quick question, is there any way to replicate Excel formulas to SQL query?

Maciej Los 21-Feb-16 3:31am

There's no chance to do it directly, but you can achieve the same using sql statements. There's several inbuilt functions which you can use to calculate sum, average, etc.

Member 10376341 22-Feb-16 4:21am

Maciej Los 22-Feb-16 5:19am

It may look like:

SELECT a.ID, COUNT(b.ID) AS NoOfCandidates, a.Target
FROM Table1 AS a LEFT JOIN Table2 AS b ON a.ID = b.ID
GROUP BY a.ID, a.Target

See: How to: Perform Joins Using Access SQL [Access 2007 Developer Reference][^]

Maciej Los 22-Feb-16 5:18am

-- posted by mistake --

Member 10376341 22-Feb-16 11:01am

Here is the statement for it.
INSERT INTO Table3
SELECT Table1.Name, COUNT(Table2.ID),Table1.Target
FROM Table2
INNER JOIN Table1 ON Table2.ID=Table1.ID
GROUP BY Table1.Name,Table1.Target

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Accepted Answer · 2016-02-20T00:45:00

See the comment from Sinisa Hajnal.

They have provided a few reasons why a database is better than excel. There are several more, not least the fact that you would have to have Excel drivers installed on your server in order to access the data. That can be an expensive overhead that is just not required.

Secondly, any functions and operations should not be in your "database" in this way - you should be putting these in the code behind, or as Stored Procedures / User Defined Functions on your actual Database.

Using Access would be a step forward from using Excel, but even that will come with it's own issues.

Consider using one of the many free database systems that are available e.g. Free Database | Free SQL Server Database | Database Management Solution[^]