动态生成完整的sql [英] generating complete sql dynamically

问题描述

我想动态生成完整的sql查询.我将ASP.NET/C#用于UI&业务逻辑部分.

在详细介绍之前，让我先详细介绍数据库设计:

1. TableColumn(此表包含所有表及其列)
2. TableRelations(表x，表y，where子句联接表x和y)

现在在用户界面上，我有3个框
1.列列表
2. SQL函数
3.操作(布尔和关系)

我在UI上也有何时"和然后"部分.
WHEN是要测试的条件，而THEN是要执行的部分，它将返回单个变量-每行或每组行.

因此，从逻辑上讲，何时应转到=> where子句
然后THEN部分应转到=>选择子句

我正在使用的当前结构会生成

选择"THEN CLAUSE"作为XYZ
从tabl1，表2
当"WHEN CLAUSE"和表关系表中的where子句"时

现在的问题是分组依据"和具有子句"，如果用户选择sql聚合函数，则将需要使用它们.

我的问题是:
1.我的逻辑行得通吗?
2.我的逻辑中有哪些谬误.
3.是否有更好的方法来实现此
4.有人实现了它(开源)或一些指针吗?
5.如何获得按条款分组

在此先感谢.

I want to generate the complete sql query dynamically. I am using ASP.NET/C# for UI & Business Logic part.

let me detail database design before going in details:

1. TableColumn ( this table contains all tables and their columns)
2. TableRelations ( table x, table y, where clause joing table x & y )

Now on UI I have 3 boxes
1. Column List
2. SQL functions
3. Operation (boolean and relational)

I also have "When" & "Then" parts on UI.
WHEN is the condition to be tested, and THEN is the part to be executed which will return a single variable - per row or per set of rows.

So logically WHEN part should go to => where clause
and THEN part should go to => select clause

current structure I am using will generate

select "THEN CLAUSE" AS XYZ
FROM tabl1, table 2
WHEN "WHEN CLAUSE" AND "where clause from table relations table"

now the problem is with "Group By" and "Having clause" which will be needed in case user select the sql aggregate function.

The questions I have are:
1. Will my logic work?
2. what are the fallacies in my logic.
3. Is there a better way to implement this
4. has someone implemented it (open source) or some pointers?
5. how to get group by clause

Thanks in Advance.

推荐答案

是的，可以动态构建所有sql.您是否正确执行是另一个问题，我无法回答.

我本人将在存储过程中进行此操作，但这仅是我自己.

Yes, it''s possible to build all of your sql dynamically. Whether or not you do it correctly is another question, and one I cannot answer.

I would personally do it in a stored procedure, but that''s just me.

1.我的逻辑行得通吗?
这次不能说...但是如果一切正常，那肯定可以正常工作...
2.我的逻辑中有哪些谬误.
取决于您的问题和解决方案的可用选项
3.是否有更好的方法来实现此
取决于您的问题和解决方案的可用选项
4.有人实现了它(开源)或一些指针吗?
我们在sp
中做到了 5.如何获得按条款分组
您可以在select语句中检查按列分组应与集合函数一起使用

1. Will my logic work?
Cannot say this time...but if every thing alright it will work surely...
2. what are the fallacies in my logic.
depends upon you problem and available options for the solution
3. Is there a better way to implement this
depends upon you problem and available options for the solution
4. has someone implemented it (open source) or some pointers?
we r doing it in sp
5. how to get group by clause
you can check that group by columns should be used with aggregate function in the select statement

这篇关于动态生成完整的sql的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！