Solved

Return schema of query but no data

Posted on 2017-06-19
7
38 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 3

Accepted Solution

by:
Dirk Strauss earned 500 total points
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
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 3

Expert Comment

by:Dirk Strauss
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
Webinar: Deploying MySQL in production 6/22 11am

Join Percona’s Senior Operations Engineer, Daniel Kowalewski as he presents Deploying MySQL in production on Thursday, June 22, 2017 at 11:00 am PDT / 2:00 pm EDT (UTC-7).

 
LVL 52

Expert Comment

by:Ryan Chong
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
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
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
Thank you Dirk.....
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Join & Write a Comment

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
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.

728 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