Pull data from a SQL table into a DataTable based on a parameter

Question

I'm expanding a class in a project where I need to pull down specific data from a SQL table into a DataTable. Before, the data was calculated and modified all inside a SQL proc but I have amended this so that the calculated data in passed into a new table instead. Now, I'm writing a method that will pull this data out of the database based on the ID value of it.

A lot of this code was written by another developer and he has a thing for tuples which I don't know too much about and confuse me a little bit. Logic wise, I've done something similar but I've only ever pulled down a whole table and not just a specific row.

This is my first pass at the method:

 public DataTable ReadSqlTableToDataTable( List<Tuple<string, string>> parameterValueMappings, DataTable dt)
    {
        string query = "SELECT *  FROM dbo.Mytable WHERE IdValue= ";

        using (var conn = new SqlConnection(_config.ConnectionString))
        {
            conn.Open();

            SqlCommand cmd = new SqlCommand(query + parameterValueMappings, conn);


            using (var da = new SqlDataAdapter(cmd))
            {
                da.SelectCommand.CommandTimeout = 600;
                da.Fill(dt);
            }
        }                           
        return dt;
    }

Then I use it a class like so:

private readonly IDbRepository _repo;
private readonly DataTable _revisedDatabTable;
private List<Tuple<string, string>> _commandParameters;
private readonly int _batchId;

public FooClass(IDbRepository repository, DataTable revisedDatabTable, int batchId) //pass in structure
    {
        _repo = repository;
        _revisedDatabTable = revisedDatabTable;
        _batchId = batchId;
    }

    public void Execute()
    {

        _commandParameters = new List<Tuple<string, string>>
        {
            new Tuple<string, string>("@IDValue",_idValue.ToString() )
        };


        _repo.ReadSqlTableToDataTable(_commandParameters, _revisedDatabTable);
    }

Before finally calling the class like this:

 new FooClass(_repo, dtRevisedData, idValue).Execute();

The idValue is read in via a file and is thrown into my new SQL table.

Now, when I run this I get nothing back. My table has data in it, so I know that isn't the problem. I have a feeling the issue is in how I'm using the tuple. Have I set it up correctly? If not, what is it I have done wrong?

Answer 1

You are misusing the SqlCommand. Instead of concatenating strings you should specify query as string and pass all the parameters using SqlCommand.Parameters. See the sample here.

In your case it should be smth like this. No tuples, no concatenations.

string query = "SELECT *  FROM dbo.Mytable WHERE IdValue = @IDValue;";

SqlCommand command = new SqlCommand(query , conn);
command.Parameters.Add("@IDValue", SqlDbType.Int);