存储应用程序的SQL代码的最佳做法是什么 [英] What is best practice for storing SQL code for an app

问题描述

我有一个从Access数据库中检索数据的应用程序。目前我

在我的应用程序中将SQL字符串作为Const。我明白这不是最好的

练习。我不希望用户有权阅读或修改此字符串

所以我不想将其存储在INI /文本文件或注册表中。可以

有人请告诉我这方面的最佳做法。


谢谢

Mike

I have an app that retrieves data from an Access database. At the moment I
have the SQL string as a Const in my app. I understand this is not best
practice. I don''t want the user to have access to read or modify this string
so I don''t want to store it in an INI / Text file or in registery. Can
someone please tell me the best practice for this.

Thanks
Mike

推荐答案

< user>在消息新闻中写道：OY ************** @ TK2MSFTNGP09.phx.gbl ...

<user> wrote in message news:OY**************@TK2MSFTNGP09.phx.gbl...

我有一个从Access数据库中检索数据的应用程序。目前我在我的应用程序中将SQL字符串作为Const。我明白这不是最好的练习。我不希望用户有权读取或修改此
字符串，因此我不想将其存储在INI /文本文件或注册表中。可以
有人请告诉我这方面的最佳做法。

I have an app that retrieves data from an Access database. At the moment I
have the SQL string as a Const in my app. I understand this is not best
practice. I don''t want the user to have access to read or modify this string so I don''t want to store it in an INI / Text file or in registery. Can
someone please tell me the best practice for this.

我认为你有最好的做法。


过去被认为是打破多行查询的最佳实践

使用字符串连接和行继续。据认为，这个

使查询更具可读性。这是垃圾。唯一的借口是
是一行VB代码的长度有限，所以你不能将b $ b存储在一行上无论如何。这也鼓励了通过字符串连接将参数绑定到查询中的不良习惯。


在VB.NET中，行可以根据需要进行，并且使用

中的参数标记您的查询要容易得多。所以SQL查询应该是CONST'并且在一行上写成

。像这样


CONST sqlGetCustomer as string =" select * from customer where id =？"


这从VB分离SQL ，并且可以轻松地将

SQL剪切并粘贴到数据库工具中，然后将其粘贴回来。


它''也是最小化存储在

应用程序中的SQL复杂性的最佳实践。

复杂的连接和过程逻辑（对于支持该数据库的数据库），

应该尽可能地下推到数据库中。例如

如果你有一个连接8个表的查询，在

数据库中创建一个Access QueryDef，然后从你的VB代码中做一个简单的选择。这个

限制了源代码中不同语言的混合量。


交错使用不同语言是不好的形式，并使两者都成为

语言难以阅读和维护。这是旧的一个重要原因。 ASP

sucked：一个文件将包含混乱的VBScript，HTML，JavaScript，

和可能的SQL。这就是为什么你不应该通过字符串连接和续行来多次破坏你的SQL查询

。

David

I think you have the best practice.

It used to be considered best practice to break queries over multiple lines
with string concatenation and line continuation. It was thought that this
made queries more "readable". This was crap. And the only excuse for it
was that there was a limit to the length of a line of VB code, so you
couldn''t store the SQL on one line anyway. This also encouraged the bad
habit of binding parameters into your query through string concatenation.

In VB.NET lines can be as long as you want, and using parameter markers in
your queries is much easier. So SQL queries should be CONST''s and written
all on one line. Like this

CONST sqlGetCustomer as string = "select * from customer where id = ?"

This seperates the SQL from the VB, and makes it easy to cut and paste the
SQL out and work on it in a database tool, then paste it back in.

It''s also best practice to minimize the complexity of SQL stored in your
application.
Complicated joins and procedural logic (for databases that support that),
should be pushed down into the database as much as possible. For instance
if you have a query that joins 8 tables, create an Access QueryDef in the
database, and then do a simple select from that in your VB code. This
limits the amount of mixing of different languages in your source code.

Interleaving different languages is bad form, and makes both of the
languages hard to read and maintain. This is one big reason "old" ASP
sucked: one file would contain a tangled mess of VBScript, HTML, JavaScript,
and possibly SQL. And this is also why you shouldn''t break your SQL queries
over multiple lines with string concatenation and line continuation.
David

嗨大卫


我一直习惯按关键字拆分SQL。例如，


SELECT Column1，Column2，ColumnN

来自MyTable

WHERE ColumnX = SomeValue

按柱2排序


就个人而言，我发现这种方式更易于阅读和维护，并且它与行长限制无关。只有当字符串超过

屏幕宽度时，我才会继续使用新行（不是连续行

），只是为了避免向左和向右滚动为了查看

整个字符串。


Charles

" David Browne" < davidbaxterbrowne没有盆栽 me**@hotmail.com >写在

消息新闻：u3 ************** @ TK2MSFTNGP11.phx.gbl ...

Hi David

It has always been my practice to split SQL by keyword. For example,

SELECT Column1, Column2, ColumnN
FROM MyTable
WHERE ColumnX = SomeValue
ORDER BY Column2

Personally, I find it easier to read and maintain this way, and it has
nothing to do with limits on line length. Only if the string exceeds the
screen width would I continue on a new line (not with a continuation line
though), just to avoid having to scroll left and right in order to view the
whole string.

Charles
"David Browne" <davidbaxterbrowne no potted me**@hotmail.com> wrote in
message news:u3**************@TK2MSFTNGP11.phx.gbl...

< ;使用者>在消息新闻中写道：OY ************** @ TK2MSFTNGP09.phx.gbl ...

<user> wrote in message news:OY**************@TK2MSFTNGP09.phx.gbl...

我有一个从Access数据库中检索数据的应用程序。目前
我在我的应用程序中将SQL字符串作为Const。我明白这不是最好的练习。我不希望用户有权读取或修改此字符串

I have an app that retrieves data from an Access database. At the moment I have the SQL string as a Const in my app. I understand this is not best
practice. I don''t want the user to have access to read or modify this string

所以我不想将其存储在INI /文本文件或注册表中。可以
有人请告诉我这方面的最佳做法。

so I don''t want to store it in an INI / Text file or in registery. Can
someone please tell me the best practice for this.

我认为你有最佳实践。

以前它被认为是最佳实践使用字符串连接和行连续来打破多个

I think you have the best practice.

It used to be considered best practice to break queries over multiple

行的查询。据认为，这使得查询更具可读性。这是垃圾。唯一的借口是，VB代码行的长度是有限的，所以你无论如何都无法将SQL存储在一行上。这也鼓励了通过字符串连接将参数绑定到查询中的不良习惯。

在VB.NET中，行可以根据需要随意使用，并使用
中的参数标记您的查询更容易。所以SQL查询应该是CONST'并且写在一条线上。像这样

CONST sqlGetCustomer as string =" select * from customer where id =？"

这将从VB中分离SQL，并且易于切割和将
SQL粘贴并在数据库工具中处理，然后将其粘贴回来。

最佳做法是尽量减少存储在
应用程序。
复杂的连接和程序逻辑（对于支持它的数据库），应尽可能地下推到数据库中。例如，如果您有一个连接8个表的查询，请在
数据库中创建一个Access QueryDef，然后从VB代码中进行简单的选择。这限制了源代码中不同语言的混合量。

交错使用不同的语言是不好的形式，并且使得两种语言都难以阅读和维护。这是旧的一个重要原因。 ASP糟糕：一个文件将包含乱七八糟的VBScript，HTML，
JavaScript，以及可能的SQL。这也是为什么你不应该通过字符串连接和续行来破坏多行上的SQL
查询。

David

lines with string concatenation and line continuation. It was thought that this
made queries more "readable". This was crap. And the only excuse for it
was that there was a limit to the length of a line of VB code, so you
couldn''t store the SQL on one line anyway. This also encouraged the bad
habit of binding parameters into your query through string concatenation.

In VB.NET lines can be as long as you want, and using parameter markers in
your queries is much easier. So SQL queries should be CONST''s and written
all on one line. Like this

CONST sqlGetCustomer as string = "select * from customer where id = ?"

This seperates the SQL from the VB, and makes it easy to cut and paste the
SQL out and work on it in a database tool, then paste it back in.

It''s also best practice to minimize the complexity of SQL stored in your
application.
Complicated joins and procedural logic (for databases that support that),
should be pushed down into the database as much as possible. For instance
if you have a query that joins 8 tables, create an Access QueryDef in the
database, and then do a simple select from that in your VB code. This
limits the amount of mixing of different languages in your source code.

Interleaving different languages is bad form, and makes both of the
languages hard to read and maintain. This is one big reason "old" ASP
sucked: one file would contain a tangled mess of VBScript, HTML, JavaScript, and possibly SQL. And this is also why you shouldn''t break your SQL queries over multiple lines with string concatenation and line continuation.
David

嗨David，


我是第二个Charle的方法 - 为了便于阅读而分开。


问候，

Fergus

Hi David,

I second Charle''s method - split for readability.

Regards,
Fergus

这篇关于存储应用程序的SQL代码的最佳做法是什么的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！