Raw SQL in .NET Core

Question

I have this problem: I need to execute raw SQL from my .NET Core app. So I have this code

var sqlConnection1 = new SqlConnection("Server=(localdb)\\mssqllocaldb;Database=MyDB;Trusted_Connection=True;MultipleActiveResultSets=true");
var cmd = new SqlCommand
{
    CommandText = "SELECT * FROM dbo.Candidates WHERE id = " + model.CandidateId,
    CommandType = CommandType.Text,
    Connection = sqlConnection1
};

sqlConnection1.Open();
var wantedRow = cmd.ExecuteReader();
sqlConnection1.Close();

I can't access the data in wantedRow... (When I use Entity Framework this query works, but I can't use Entity Framework). Is it possible in .NET Core?

Answer 1

First, your code is an open door for sql injection attacks. Use parameterized queries instead of concatenating strings.

Second, use the using statement for everything that implements the IDisposable interface. In this case - connection, command and reader.

Third, getting the reader is just a part of the job. You still need to use reader.Read() and get the values.

using(var sqlConnection1 = new SqlConnection("Server=(localdb)\\mssqllocaldb;Database=MyDB;Trusted_Connection=True;MultipleActiveResultSets=true"))
{ 
    using(var cmd = new SqlCommand()
    {
        CommandText = "SELECT * FROM dbo.Candidates WHERE id = @id",
        CommandType = CommandType.Text,
        Connection = sqlConnection1
    })
    {
        cmd.Parameters.Add("@id", SqlDbType.Int).Value = model.CandidateId
        sqlConnection1.Open();

        using(var reader = cmd.ExecuteReader())
        {
            if(reader.Read())
            {
                var id = reader[0];
                var whatEver = reader[1];
                // get the rest of the columns you need the same way
            }
        }
    }
}

Answer 2

ExecuteReader should return you SqlDataReader, which means you should do something like

while(wantedRow.Read())
{
    var aValue = wantedRow[0].Value;
}