Get dictionary (or keyvaluepair or whatever) of columns and tables used in a SQL Query

Question

I'm looking for a way to take an SQL query, pass it to a method, and retrieve a dictionary back of all of the columns and their associated table. I was able to get a list of tables from a query based on this: List of tables used in an SQL Query

For example, the following query:

SELECT P.FirstName, P.LastName, P.BirthDate, P.DeathDate, VP.FullName 
FROM Presidents P
INNER JOIN VicePresidents VP ON P.VicePresidentID = VP.VicePresidentID

Ideally, it would return a dictionary (or keyvaluepair or whatever really) item with the following:

{FirstName, Presidents},
{LastName, Presidents},
{BirthDate, Presidents},
{DeathDate, Presidents},
{Fullname, VicePresidents}

Ideally, this should not touch SQL at all (if possible). The identifiers (P,VP) may not always be there. My method that returns tables does return Presidents and VicePresidents, but I really need the column/table relationship. Any thoughts?

I'm creating an MVC control, that you pass an object from the model and it automatically creates the entire form based on the model. The object from the model matches a sql query used to fill it. I have parameters for lookups and many of the input types. The one thing I can't do automatically though is the table name for the column.

I have this query:

SELECT
    AppSettingsTypeID,
    AppSettingsTypeName
FROM MasterImport.DBAppSettingsType
WHERE AppSettingsTypeID = 13

That creates an instance of this class

public class DBAppSettingFull
    {
        [FormField("App Setting Type", InputType.Dropdown, new int[] { 12, 12, 6, 6, 4 })]
        public int AppSettingsTypeID { get; set; }

        [FormField("App Setting Name", InputType.Textbox, new int[] { 12, 12, 6, 6, 4 })]
        public string AppSettingsTypeName { get; set; }
    }

With this line of code

model.Record = Interaction.Single<DBAppSettingFull>(sqlGeneric.GetFirstDBAppSetting.Resource());

Which is used in the cshtml with this line of code

@(Html.GenerateForm<Cold.Framework.Classes.DBAppSettingFull>(Model.Record))

and it creates the form on the cshtml page. I'm in the process of writing my ProcessForm method. My last version of this, I had to do manually give each property/column its table relationship. I'd like to do that automatically.

EF, Linq To SQL, NHibernate, Dapper.net ? What have you tried yet? — CodeNotFound, May 07 '18 at 17:54
Sounds like an [xy problem](https://meta.stackexchange.com/questions/66377/what-is-the-xy-problem). What is the actual reason you want to do this? — Igor, May 07 '18 at 17:55
Is there a reason you're using Dictionaries instead of Classes? A class of Presidents with fields FirstName, LastName, BirthDate, etc seems much easier to use (and build)? — Jacob M., May 07 '18 at 17:56
So basically you want to re-create the existing MVC + Entity framework feature to generate a scafold template(s)? — Igor, May 07 '18 at 18:08
Sort of. Except that, unless I'm missing something, Entity doesn't automatically create a form. You still have to create the entire form with the object attached to it, pass it to the controller, and process it. My code takes an object, creates the form, and handles the save to the database. Each action is a single line of code. — Tim Southard, May 07 '18 at 18:10
@TimSouthard i dunno the answer to the question, but i'm a fan of your adorable penguin avatar. — , May 07 '18 at 18:11
You might need to use something like the `Microsoft.Data.Schema.ScriptDom.Sql.TSql100Parser ` class to parse the SQL into its pieces. I don't know how to do this though. — , May 07 '18 at 18:18
@Amy - I'm using that to get the list of tables, but can't figure out how to make the connection to the columns. I'm assuming I'm missing something here that would help me. Thanks! — Tim Southard, May 07 '18 at 19:01
Use GetSchema() : https://msdn.microsoft.com/en-us/library/ms136366(v=vs.110).aspx — jdweng, May 07 '18 at 19:05

Get dictionary (or keyvaluepair or whatever) of columns and tables used in a SQL Query

0 Answers0