Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


Return schema of query but no data

Posted on 2017-06-19
Medium Priority
Last Modified: 2017-06-19
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.
Question by:Kelly Martens
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
LVL 10

Accepted Solution

Dirk Strauss earned 2000 total points
ID: 42183928
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.

Author Comment

by:Kelly Martens
ID: 42183937
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.
LVL 10

Expert Comment

by:Dirk Strauss
ID: 42183949
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.
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

LVL 53

Expert Comment

by:Ryan Chong
ID: 42183950
have you tried tools such as Entity Framework or Dapper before? it may help...

Entity Framework


Author Comment

by:Kelly Martens
ID: 42183973
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.

Author Closing Comment

by:Kelly Martens
ID: 42184007
using the reader was the ticket to what I am looking for. Made something complicated that didn't need to be.

Author Comment

by:Kelly Martens
ID: 42184011
Thank you Dirk.....

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

609 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question