How to get data from multiple tables without knowing table definition and the database may not be SQL through WEB API

Question

0.00/5 (No votes)

See more:

I have a Requirement of passing a table name in the url like http://localhost:81/api/TableName

every time a table is passed in this fashion a Web API will fetch data for that particular table.

We don't know the table definitions and the tables will be added in the database in particular time interval.There may be more than 100 tables

Is there any way this can be achieved or if not any other suggestions will help. Please note I have to use c# only.

Posted 22-Jul-15 2:22am

Member 11220943

Updated 22-Jul-15 2:23am

v3

Add a Solution

Comments

Michael_Davies 22-Jul-15 8:46am

If you do not know the back end database engine it is nigh impossible for anyone to help.

You must know the API and what/how it is obtaining its responses to requests.

Member 11220943 22-Jul-15 8:54am

@Michael_Davies: Let's say if the BackEnd dataBase is SQL Server in that case How it can be done.

Michael_Davies 22-Jul-15 9:12am

Then the API can SELECT the table names from the schema.

Kornfeld Eliyahu Peter 22-Jul-15 8:57am

To do so, at some point some code MUST know about the database to make the connection and run the query...even your client does not know...

Member 11220943 22-Jul-15 9:12am

@Kornfeld Eliyahu Peter lets say if we know the database connection then in that case how we can fetch the data without knowing the Table Column Definitions.What I mean is like there may be more than 100 tables present in the database and we donot want to create models for each of the tables since it will cause huge number of model classes

Michael_Davies 22-Jul-15 9:15am

So how do you know which tables you want? You are not giving all the information in your question, there must be some factor for you to know which table(s) you are interested in, even if the tables are created "in particular time interval" you must have a determining factor like a table name prefix.

Member 11220943 22-Jul-15 11:39am

@Michael_Davies:- My case is simple user will add a table lets say "employeename" and then user will pass the table name in the url like http://localhost81/api/employeename and the api will fetch the result. This will be done without creating Model Classes

virusstorm 22-Jul-15 12:09pm

Have you looked at using OData? I believe this will solve your issue.

Member 11220943 23-Jul-15 2:43am

@virusstorm : It would be better if you can throw some light on how to use odata in this case.

2 solutions

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)

arussell · Answer 1 · 2015-07-22T06:40:00

Solution 1

If you have the table name you'll need to get the table schema to get the column names etc... Once you have that then you can build the queries to get the data.

google "c# get table schema"

This is a good MS article on use the GetSchema and Schema collections

https://msdn.microsoft.com/en-us/library/ms254934(v=vs.100).aspx

Posted 22-Jul-15 6:40am

arussell

Comments

Member 11220943 23-Jul-15 3:54am

What if the Connection is not sql then in that case will this approach work.

arussell · Answer 2 · 2015-07-24T09:01:00

Solution 2

You may have to know at least what type of database you are connecting to and use slightly different code if it is Oracle, or ??

And you'll need the correct libraries and drivers installed for each type of database you need to connect to.

Posted 24-Jul-15 9:01am

arussell