如何使用SqlCommand参数为select查询指定架构名称 [英] How to use a SqlCommand Parameter to specify the schema name for a select query

问题描述

在我们的数据库架构的名称是动态的。为什么不下面的工作？

The name of the Schema in our database is dynamic. Why won't the following work?

public void ReadVersion(string connString, string schemaName) 
{
    string selectCommand = "SELECT major FROM [@SchemaName].[version]");
    using (SqlConnection sqlConn = new SqlConnection(connString)) 
    {
        using (SqlCommand cmd = new SqlCommand(selectCommand, sqlConn)) 
        {
            sqlConn.Open();
            cmd.Parameters.AddWithValue("@SchemaName", schemaName);
            object result = cmd.ExecuteScalar();
        }
    }
}

当执行该命令，参数值不会被取代。这是SqlCommand的参数的限制？

When the command is executed, the parameter value is not substituted. Is this a limitation of the SqlCommand Parameters?

推荐答案

您的不能的做纯SQL以下（所以忽略所有的ADO.Net开销）：

You can't do the following in plain SQL (so ignoring all of the ADO.Net overheads):

DECLARE @SchemaName sysname
SET @SchemaName = 'dbo'
SELECT major FROM @SchemaName.[version]

和的原因很简单。什么SQL想以后从是名称的。一个的串什么你想给它的。 SQL服务器的不的随机启动对等字符串里，看他们是否像一些其他的构造。

And the reason is simple. What SQL wants after FROM is a name. What you're trying to give it is a string. SQL Server doesn't randomly start peering inside of strings to see if they resemble some other construct.

要您的环卫查询，假设你在保持架构名称理智，只使用一个简单的正则表达式的架构名称，你相信它（例如： ^ [AZ] [A-Z0-9前] + $ 应足以满足大多数用途）。请确保您使用白名单（允许的字符），而不是黑名单。

To your sanitation query, assuming that you're keeping the schema names sane, just use a simple regex on the schema name before you trust it (e.g. ^[A-Z][A-Z0-9]+$ should be enough for most uses). Make sure you use a whitelist (allowed characters) rather than a blacklist.

这篇关于如何使用SqlCommand参数为select查询指定架构名称的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！