传递除存储过程之外的SQL查询的最佳方法(Best way to pass a SQL query other than a stored procedure)

传递除存储过程之外的SQL查询的最佳方法(Best way to pass a SQL query other than a stored procedure)

我正在开发一个C＃控制台应用程序，它运行两个SQL查询，这些查询作为字符串传递如下

private DataTable GetData(string resultsQuery) { SqlCommand sqlCmd = new SqlCommand(resultsQuery, sqlcon); DataTable dtlist = new DataTable(); SqlDataAdapter dalist = new SqlDataAdapter(); dalist.SelectCommand = sqlCmd; dalist.Fill(dtlist); sqlcon.Close(); return dtlist; }

但问题是这些查询不断变化，每次更改时，我都会在安装更新的应用程序之前重新构建，重新发布和卸载旧应用程序，我认为这是一种不好的做法。 我不能使用存储过程的原因是我只有对数据库的读访问权限，我无法创建存储过程。

任何人都可以建议我更好的方法和最佳实践来处理这个？

I am working on a C# console application which runs two SQL queries which are passed as strings as follows

private DataTable GetData(string resultsQuery) { SqlCommand sqlCmd = new SqlCommand(resultsQuery, sqlcon); DataTable dtlist = new DataTable(); SqlDataAdapter dalist = new SqlDataAdapter(); dalist.SelectCommand = sqlCmd; dalist.Fill(dtlist); sqlcon.Close(); return dtlist; }

but the thing is these queries keep changing very frequently and everytime they change, I have re-build, re-publish and uninstall the older application before installing the updated application which I think is a bad practice. The reason I cannot use a stored procedure is that I have only read access to the database and I cannot create a stored procedure.

Can anyone suggest me a better way and best practice to deal with this?

最满意答案

从本质上讲，您的查询是程序配置的一部分，该配置目前是硬编码的。 因此，您需要的解决方案与访问数据库几乎没有关系：您需要一种方法来升级已安装应用程序的配置设置。

虽然有一个存储过程是一个很好的选择，但还有其他方法可以实现您正在寻找的效果：

一种方法是配置您具有写访问权限的单独数据库，并将其用作查询字符串的来源。 创建一个“映射”查询名称以查询内容的表：

QueryKey Query -------- -------------------------------------- query1 SELECT A, B, C FROM MyTable1 WHERE ... queryX SELECT X, Y, Z FROM MyTable2 WHERE ...

您的程序可以在启动时读取此其他数据库，并存储查询以便在运行时使用。 当最终用户请求他们的数据时，您的程序将执行从配置数据库获取的对只读数据库的查询。

您可以采用其他方法来分发此配置。 替代方案包括将字符串存储在应用程序可见的文件服务器上的共享文件夹中，设置自己的网络服务以在启动时为应用程序提供查询，或使用内置的配置方法。净。 最后一种方法要求您逐个更改单个计算机上的设置，这可能不是理想的部署方案。

Essentially, your query is part of your program's configuration, which is currently hard-coded. Therefore, a solution you need has little to do with accessing databases: you need a way to upgrade configuration settings of an installed application.

Although having a stored procedure would be a fine choice, there are other ways of achieving the effect that you are looking for:

One approach would be to configure a separate database to which you have a write access, and use it as your source of query strings. Make a table that "maps" query names to query content:

QueryKey Query -------- -------------------------------------- query1 SELECT A, B, C FROM MyTable1 WHERE ... queryX SELECT X, Y, Z FROM MyTable2 WHERE ...

Your program can read this other DB at startup, and store queries for use at runtime. When end-users request their data, your program would execute the query it got from the configuration database against your read-only database.

You can take other approaches to distributing this piece of configuration. Alternatives include storing the strings in a shared folder on a file server to which your application has visibility, setting up a network service of your own to feed your application its queries at start-up, or using built-in means of configuration available in .NET. The last approach requires you to change the settings on individual machines one-by-one, which may not be an ideal roll-out scenario.