Query returns result in SQL Server Management Studio, but does not return anything in an application

Question

I have a query below. It returns data in Microsoft SQL Server Management Studio, but does not return anything in an application.

SELECT name FROM CHANGETABLE(CHANGES client, 2) AS Result

At the same time query below returns data in both places:

SELECT name FROM client

Below is the code responsible for query execution:

var adapter = Factory.CreateDataAdapter(); // Creates instance of SqlDataAdapter
adapter.SelectCommand = Command; // Contains query in text format

using (var connection = Factory.CreateConnection())
{
    adapter.SelectCommand.Connection = connection;
    connection.ConnectionString = DatabaseSchema.ConnectionString;
    connection.Open();

    adapter.Fill(dataTable);

    connection.Close();
}

For the first query dataTable does not contain any rows, for the second query it contains rows.

User specified in a connection string is the same as I use to authenticate in Management Studio.

Why I am getting such result?

Answer 1

don't use string concatenation and interpolation for query building, instead use Parameterized queries. You cannot forsee problems you may encounter (for instance datatype mismatches). Also your code is subject to Sql Injection attacks.

Check This for more information about parameterized queries and their use cases