C#使用现在不起作用的sql参数 [英] C# Using sql parameters which now won't work

问题描述

嘿，我在我的应用程序中使用参数化查询，但效果很好，但现在(我不知道为什么)，它们不再被值取代...

Hey I was using parametrized queries for my application wich worked just fine but now (I don't know why) they aren't replaced anymore with the values...

因此，它不会执行类似"SELECT [TABLE_NAME] FROM [MyDefinetelyExistingDatabase]";的操作，而是尝试执行"SELECT [TABLE_NAME] FROM [@targetDatabase]";，这当然会失败.

So instead of running something like "SELECT [TABLE_NAME] FROM [MyDefinetelyExistingDatabase]"; it tries to execute "SELECT [TABLE_NAME] FROM [@targetDatabase]"; wich, of course, will fail.

 var dataBaseToGetTablesFrom = "MyDefinetelyExistingDatabase";
 var results = new List<string>();

        const string query = @"SELECT
                                    [TABLE_NAME] AS tableName
                                FROM 
                                    [@targetDatabase].[INFORMATION_SCHEMA].[TABLES] ;";

        using (var context = new ConnectionHandler(true))
        {
            if (context.Connection.State != ConnectionState.Open)
                throw new ConnectionFailedException(context.Connection.State);

            using (var command = new SqlCommand(query, context.Connection))
            {
                command.Parameters.AddWithValue("@targetDatabase", dataBaseToGetTablesFrom);

                using (var reader = command.ExecuteReader())
                {
                    if (!reader.HasRows)
                        return results.ToArray();

                    while (reader.Read())
                        results.Add(reader.GetString(0));
                }
            }
        }
        return results.ToArray();

我现在尝试了不同的格式和东西来添加参数，但是结果相同... 我不想通过直接通过string.Format将值插入查询中来执行此操作，例如，但我想拥有这些参数(可在代码中的不同位置(???)正常工作，而不是我想要的位置.

I now tried different formats and things to add the parameters but it results in the same... I don't want to do this by inserting the values into the query directly via string.Format eg but I want to have those parameters (wich work properly at different places in the code (???) but not where I want.

实际上，我需要在每个语句中使用参数，并且必须能够通过调用[DB].[Table-Schema].[Table]

In fact, I need to use parameters in every statement and must be able to adress different databases by calling them like [DB].[Table-Schema].[Table]

伙计们，几天前解决了这个问题，以为我与您分享了这个问题. 据我所知，我的整体问题是尝试替换数据库名称和/或在其他示例中替换表名称. 所以这将无法正常工作，这对我来说很有意义，因为如果服务器甚至不知道应该在哪个表上工作并且因此对结构等一无所知，那么服务器将无法准备执行语句.

Hey guys, figured the problem some days ago and thought I share it with you. As far as I have noticed, my problem at the whole was to try to replace the databasename and / or in some other examples, the table name as well. So this won't work which makes clearly sense to me as the server can't prepare to execute a statement if it doesn't even know on which table it should work and therefore doesn't know anything about the structure etc. :D

因此我更改了声明以适应我的新知识，并且它像一种魅力一样令人期待.

So I changed my statements to fit my new knowledge and it worked as excpected like a charm.

推荐答案

您将需要动态sql来实现类似.....

You would need dynamic sql for this something like.....

DECLARE @Sql NVARCHAR(MAX);

SET @Sql = N' SELECT [TABLE_NAME] AS tableName '
         + N' FROM ' + QUOTENAME(@targetDatabase) + N'.[INFORMATION_SCHEMA].[TABLES]'

Exec sp_executesql @Sql 

这篇关于C#使用现在不起作用的sql参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！