Return schema of query but no data

The user provides my vb.net code with a SQL query that would look like this  for example:

select DISTINCT *from employees a INNER JOIN orders b on b.[Employee ID] = a.ID

I want to find a way to populate the datatable I create in code with the query columns and information but not actually get data. Is there a way to do this? The code also accepts queries from MySQL, MS Access, CSV as well as SQL.
Kelly MartensAsked:
Who is Participating?
 
Dirk StraussConnect With a Mentor Senior Full Stack DeveloperCommented:
I found something similar once. Just do a select top 0. So in other words
SELECT DISTINCT TOP 0 * FROM employees a INNER JOIN orders b ON b.[Employee ID] = a.ID

Open in new window

You can then use SqlDataReader.GetSchemaTable Method () to get the schema. Have a look at this article too Getting Schema Information from the DataReader which is the heading about half way down the page.
0
 
Kelly MartensAuthor Commented:
that works for SQL and I think Access. However if connected to MySQL or a CSV file it does not.
I tried parsing the statement and replacing "SELECT" with "SELECT TOP 0" but then somebody threw in a "DISTINCT" keyword and messed that solution up.
0
 
Dirk StraussSenior Full Stack DeveloperCommented:
Can't you just replace * with TOP 0 *
So then SELECT * FROM table becomes SELECT TOP 0 * FROM table and SELECT DISTINCT * FROM table becomes SELECT DISTINCT TOP 0 * FROM table
You would need to cater for other data providers, but this would work for SQL. Perhaps I'm shooting from the hip too much here.
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
Ryan ChongCommented:
have you tried tools such as Entity Framework or Dapper before? it may help...

Entity Framework
https://msdn.microsoft.com/en-us/library/ee712906(v=vs.113).aspx

Dapper
https://github.com/StackExchange/Dapper
0
 
Kelly MartensAuthor Commented:
Hey Ryan... I am somewhat handcuffed to do this in the existing code and am unable to rewrite how the application gets it data at this point.

I will never be sure of what query the user will enter. The solution by Dirk would absolutely work if every statement fed into it used *FROM or in my solution if every statement entered never used a DISTINCT like keyword. I THINK I can append LIMIT 1 at the end of any MySQL query. Access and CSV only accepts TOP 1 but appears to work like the TOP in SQL.
0
 
Kelly MartensAuthor Commented:
using the reader was the ticket to what I am looking for. Made something complicated that didn't need to be.
0
 
Kelly MartensAuthor Commented:
Thank you Dirk.....
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.