在Delphi中存储许多长SQL查询的最佳实践

发布于 2025-01-25 05:26:50 字数 359 浏览 2 评论 0原文

我正在使用Delphi Xe开发应用程序。我有很多SQL查询，我不想将它们存储在以下代码中，因为此方法可降低代码的可读性：

CONST_SQL1 = 'SELECT ... FROM ..'#13#10 + 'WHERE ...';

也许创建数据amodule并将SQL查询存储在备忘录组件中可以是替代选项。这样，SQL查询将具有更大的可读性，而不是上述方法，但是这样，我必须在诸如memo_sql1，memo_sql2，memo_sql3之类的datamodule中添加许多备忘组件，

我也不想存储这些SQL查询在单独的文件或数据库中。

我可以将所有这些SQL查询存储在其中，还是您对这种情况的最佳实践是什么？

原文

I am developing applications with Delphi XE. I have many long SQL queries and I don't want to store them in code like below, because this method reduces code readability:

CONST_SQL1 = 'SELECT ... FROM ..'#13#10 + 'WHERE ...';

Maybe creating a DataModule and storing the SQL queries in the Memo components can be an alternative option. This way the SQL queries will have more readability rather than the method above but in this way, I have to add many Memo components in the DataModule like Memo_SQL1, Memo_SQL2, Memo_SQL3, ...

Also I don't want to store these SQL queries in separate files or in a database.

Is there a single component that I can store all these SQL queries inside or what is your best practice for this case?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

金兰素衣 2025-02-01 05:26:50

非常感谢您的所有评论。在我的项目上，我只喜欢一个文件，那就是程序的EXE。由于我不想要其他单独的文件，因此我更喜欢将SQL查询存储在EXE资源中。由于SQL查询经常发生变化，并且在数据库中存储SQL查询需要其他插入工作，因此我不希望将它们存储在数据库中。

我试图为我的需求开发最基本和最有用的方法。在此方法中，所有SQL查询都以单个XML文件格式存储在EXE资源中。如果这种方法可以帮助任何其他人，我将在下面分享。

sqlqueries.rc ：
```
  sqlqueries rcdata sqlqueries.xml
 
```

sqlqueries.xml （在UTF-8中）：

 ＆lt;？xml版本=“ 1.0” encoding =“ utf-8”？＆gt;
＆lt; sqlqueries＆gt;
    ＆lt; sqlquery sqlqueryName =“ sql1”＆gt;
        选择
            *
        从
            table1
        在哪里
            1 = 1
    ＆lt;/sqlquery＆gt;
    ＆lt; sqlquery sqlqueryName =“ sql2”＆gt;
        选择
            *
        从
            table2
        在哪里
            2 = 2
    ＆lt;/sqlquery＆gt;
＆lt;/sqlqueries＆gt;

project1.dpr ：

  program project1;

{$ appType控制台}

{$ r project1.res} //程序的默认res文件存储图标，版本信息等。

{$ r'sqlqueries.res''sqlqueries.rc'} //添加此行。如果不是sqlqueries.res自动在编译后创建，请尝试将sqlqueries.rc文件拖动到项目。

用途
  Windows，类，XMLDOC，XMLINTF，generics.collections，ActiveX;

var
  sqldictionary：tdictionary＆lt; string，string＆gt;;

过程loadgetsqlqueries;
var
  ResourceStream：tresourcestream;
  xmldocument：ixmldocument;
  xmlnode：ixmlnode;
  我：整数；
开始
  resourcestream：= tresourcestream.create（hinstance，'sqlqueries'，rt_rcdata）;
  尝试
    xmldocument：= txmldocument.create（nil）;
    尝试
      xmldocument.loadfromstream（ResourceStream）;
      对于i：= 0 to xmldocument.childnodes ['sqlqueries']。子
      开始
        xmlnode：= xmldocument.ChildNodes ['sqlqueries']。儿童nodes [i];
        sqldictionary.addorsetvalue（xmlnode.attributes ['sqlqueryName']，xmlnode.text）;
      结尾;
    最后
      xmldocument：= nil;
    结尾;
  最后
    resourcestream.free;
  结尾;
结尾;

开始
  共同化（NIL）；
  尝试
    sqldictionary：= tdictionary＆lt; string，string＆gt; .create;
    尝试
      loadgetsqlqueries;
      writeln（sqldictionary ['sql1']）;
      readln;
    最后
      sqldictionary.free;
    结尾;
  最后
    咨询;
  结尾;
结尾。

用我的资源命名sqlqueries及其内容：
该程序的输出writeln（sqldictionary ['sql1']）验证SQL查询是否已通过xmldocument.loadfromstream（resourceStream）成功读取了SQL查询。

Thank you very much for all of your comments. On my project I prefer only one file and that is the program's EXE. Since I don't want any other separate files, I prefer the SQL queries to be stored in an EXE resource. Since SQL queries are changing so often and storing the SQL queries in the database needs additional insert efforts, I don't prefer to store them in the database.

I tried to develop the most basic and helpful method for my needs. In this method, all SQL queries are being stored in an EXE resource as a single XML file format. I am sharing below if this method can help any other people.

SQLQueries.rc:
```
SQLQueries RCDATA SQLQueries.xml
```

SQLQueries.xml (in UTF-8):

<?xml version="1.0" encoding="utf-8"?>
<SQLQueries>
    <SQLQuery SQLQueryName="SQL1">
        SELECT
            *
        FROM
            TABLE1
        WHERE
            1 = 1
    </SQLQuery>
    <SQLQuery SQLQueryName="SQL2">
        SELECT
            *
        FROM
            TABLE2
        WHERE
            2 = 2
    </SQLQuery>
</SQLQueries>

Project1.dpr:

program Project1;

{$APPTYPE CONSOLE}

{$R Project1.res} // program's default res file storing the icon, version info, etc..

{$R 'SQLQueries.res' 'SQLQueries.rc'} // add this line. if not SQLQueries.res automatically created after compile, try dragging and dropping SQLQueries.rc file to the project.

uses
  Windows, Classes, XMLDoc, XMLIntf, Generics.Collections, ActiveX;

var
  SQLDictionary: TDictionary<string,string>;

procedure LoadGetSQLQueries;
var
  ResourceStream: TResourceStream;
  XMLDocument: IXMLDocument;
  XMLNode: IXMLNode;
  i: Integer;
begin
  ResourceStream := TResourceStream.Create(HInstance, 'SQLQUERIES', RT_RCDATA);
  try
    XMLDocument := TXMLDocument.Create(nil);
    try
      XMLDocument.LoadFromStream(ResourceStream);
      for i := 0 to XMLDocument.ChildNodes['SQLQueries'].ChildNodes.Count - 1 do
      begin
        XMLNode := XMLDocument.ChildNodes['SQLQueries'].ChildNodes[i];
        SQLDictionary.AddOrSetValue(XMLNode.Attributes['SQLQueryName'], XMLNode.Text);
      end;
    finally
      XMLDocument := nil;
    end;
  finally
    ResourceStream.Free;
  end;
end;

begin
  CoInitialize(nil);
  try
    SQLDictionary := TDictionary<string,string>.Create;
    try
      LoadGetSQLQueries;
      Writeln(SQLDictionary['SQL1']);
      Readln;
    finally
      SQLDictionary.Free;
    end;
  finally
    CoUninitialize;
  end;
end.

Compiled EXE with my resource named SQLQueries and its content:
The program's output of Writeln(SQLDictionary['SQL1']) verifies that the SQL query has been read successfully by XMLDocument.LoadFromStream(ResourceStream):

回复收藏 0 原文

眼泪淡了忧伤 2025-02-01 05:26:50

我会为每个查询购买一个（文本）资源文件。这使追踪源代码管理系统中的更改变得更加容易。

在项目目录中为您的SQL文件添加一个子文件夹，并将其添加到RC-文件中，例如：

SQL1               TEXT  .\SQL\SQL1.sql
SQL2               TEXT  .\SQL\SQL1.sql

将RC文件添加到项目中，您可以将SQL文本直接加载到tquery的SQL成员中，就像一样

program Project2;

{$APPTYPE CONSOLE}

{$R *.res}
{$R 'SQLQueries.res' 'SQLQueries.rc'}

uses
  Windows,
  Data.SqlExpr,
  Classes;

var
  ResourceStream: TResourceStream;
  Query: TSQLQuery;
begin
  ResourceStream := TResourceStream.Create(HInstance, 'SQL1', 'TEXT');
  Query:= TSQLQuery.Create(nil);
  try
    Query.SQL.LoadFromStream(ResourceStream);
    Writeln(Query.SQL.Text);
    ReadLn;
  finally
    Query.Free;
    ResourceStream.free;
  end;
end.

I'd go for a single (text) resource file for each query. That makes it much easier to trace changes in your source code management system.

Add a subfolder in your project's directory for your SQL files and add them to a rc- file like:

SQL1               TEXT  .\SQL\SQL1.sql
SQL2               TEXT  .\SQL\SQL1.sql

Add that rc-file to the project and you can load the SQL text directly into the SQL member of a TQuery, just like

program Project2;

{$APPTYPE CONSOLE}

{$R *.res}
{$R 'SQLQueries.res' 'SQLQueries.rc'}

uses
  Windows,
  Data.SqlExpr,
  Classes;

var
  ResourceStream: TResourceStream;
  Query: TSQLQuery;
begin
  ResourceStream := TResourceStream.Create(HInstance, 'SQL1', 'TEXT');
  Query:= TSQLQuery.Create(nil);
  try
    Query.SQL.LoadFromStream(ResourceStream);
    Writeln(Query.SQL.Text);
    ReadLn;
  finally
    Query.Free;
    ResourceStream.free;
  end;
end.

回复收藏 0 原文

尘曦 2025-02-01 05:26:50

提示，您可以在数据库服务器上使用 views ，在Delphi中的代码上使用select *从vw_customer中选择 *，您始终可以修改那里的查询，加上侧面是逻辑中的所有更改都会自动反映在您的所有exe的所有实例中，弊端是如果您的删除o添加一列，如果添加了exe，则必须有一个exe的升级来显示新列，如果您的删除或删除或更改列的名称，您将有一个例外，其中使用该修改视图上的查询。

回复收藏 0 原文

~没有更多了~