Creating a database view in code

Question

So I've been tasked with creating a tool to allow ours users to create their own 'worklists' which they use to work through their data. In our app these worklists are driven by SQL views, so for now my program is having to dynamically create views in our database based on the users input. I don't like this, but for now I have to make the best of it an am brainstorming the best ways to go about this.

Basically every view I create has a similar skeleton, it has several columns that are always pulled and several joins that always happens. Based on the users input I may add additional SELECT columns, as well as additional joins if they are necessary to access the added display columns.

So basically right now my code looks like this...

string SQL = string.Format(@"CREATE VIEW {0}
                             AS
                             SELECT
                             Foo.A,
                             Bar.B,
                             {1}
                             FROM
                             Table
                             INNER JOIN Foo on Foo.ID = Table.FooID
                             INNER JOIN Bar on Bar.ID = Table.BarID
                             {2}", viewName, displayNames, extraJoins);
Database.ExecuteNonQuery(SQL);

I really don't like this for obvious reasons. However, I cannot seem to find the equivalent of a parametrized query for view creation with ADO. I could perhaps create a stored procedure to do this, but even that seems sloppy. Is there any reasonable way to do something like this that doesn't make me sick to my stomach? Also we are using MS SQL, and have to support as far back as 2005.

Why do they have to be SQL views? Is the worklist for each user composed of the same tables and join between those tables? From a naive perspective it would seem that a stored procedure (or parameterized query) that takes an identifier for a user would work. Or is it more complex? Could you give more details around these worklists? — Russ Cam, May 30 '12 at 18:30
@RussCam This is a rather simplistic view of our quite complex problem. The code that creates these worklists is a large mess that is driven from these views. To change how that code operates is implausible at this current time(I will be pushing for it for our next release, but for now I'm stuck). In my example Foo and Bar are always the same tables, however what the user picks can be from many different tables, I already have a mechanism for finding the join needed to utilize those tables. — Kevin DiTraglia, May 30 '12 at 18:33

score 1 · Accepted Answer · edited May 23 '17 at 12:04

1

In contrast to DML (like SELECT / UPDATE / INSERT / DELETE) there is no support for parameters in DDL(see here too). So basically you either hide that inside a Stored Procedure with dynamic SQL or do it the way you describe...

edited May 23 '17 at 12:04

Community

1
1

answered May 30 '12 at 18:33

Yahia

69,653
9
115
144

Ugh, that is unfortunate. I'm going to leave the question open for a while in case of a hail mary pass from someone who may have figured out a more clever way to do this, but if not I'll give you the check. – Kevin DiTraglia May 30 '12 at 19:01

Creating a database view in code

1 Answers1