?
Solved

Return schema of query but no data

Posted on 2017-06-19
7
Medium Priority
?
70 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
  • 4
  • 2
7 Comments
 
LVL 10

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 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.
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 54

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

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

Question has a verified solution.

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

In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
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…
Suggested Courses

750 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