?
Solved

Return schema of query but no data

Posted on 2017-06-19
7
Medium Priority
?
57 Views
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.
0
Comment
Question by:Kelly Martens
[X]
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
7 Comments
 
LVL 9

Accepted Solution

by:
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.
0
 

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.
0
 
LVL 9

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.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
https://msdn.microsoft.com/en-us/library/ee712906(v=vs.113).aspx

Dapper
https://github.com/StackExchange/Dapper
0
 

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.
0
 

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.
0
 

Author Comment

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

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
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.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Suggested Courses

765 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