Fixing SQL Parameterization

Question

I'm having some trouble updating some of my SQL queries to use parameters as opposed to string concatenation. Nothing super hard, I'm just trying to figure out what I'm missing or am doing wrong.

The end query should look like

SELECT [DeviceSeq], [DeviceName], [SerialNumber], [Premise], [InsertDate], [VersionNumber], [LastUpdateDate], [IsDeleted] FROM [ITAM].[dbo].[AllDevices] WHERE DeviceName LIKE '%InputFilter%' OR SerialNumber = 'InputFilter'

Previously I was doing it like so, and it returns the results I expect.

command.CommandText = "SELECT [DeviceSeq], [DeviceName], [SerialNumber], [Premise], [InsertDate], [VersionNumber], [LastUpdateDate], [IsDeleted] FROM [ITAM].[dbo].[AllDevices] WHERE DeviceName LIKE '%@" + filter + "%' OR SerialNumber = '" + filter + "'";

But what I'm trying to do is this. However, every time that I try to do it this way (parameterized) it returns 0 results or errors out depending on how I try and format it.

List<dynamic> hosts = new List<dynamic>();
using (SqlCommand command = conn.CreateCommand())
{
    command.CommandText = "SELECT [DeviceSeq], [DeviceName], [SerialNumber], [Premise], [InsertDate], [VersionNumber], [LastUpdateDate], [IsDeleted] FROM [ITAM].[dbo].[AllDevices] WHERE DeviceName LIKE '%@filter%' OR SerialNumber = '@filter'";

    var param = new SqlParameter("filter", System.Data.SqlDbType.VarChar);
    param.Value = filter;
    command.Parameters.Add(param);


    using (var reader = command.ExecuteReader())
    {
        while (reader.Read())
        {
            dynamic h = new ITAMHost()
            {
                DeviceSeq = reader[0].ToString(),
                DeviceName = reader[1].ToString(),
                SerialNumber = reader[2].ToString(),
                Premise = reader[3].ToString(),
                InsertDate = reader[4].ToString(),
                VersionNumber = reader[5].ToString(),
                LastUpdateDate = reader[6].ToString(),
                IsDeleted = reader[7].ToString(),
            };
            hosts.Add(h);
        }
    }
}

Answer 1

hi try changing your code like this :

List<dynamic> hosts = new List<dynamic>();
using (SqlCommand command = conn.CreateCommand())
{
    command.CommandText = "SELECT [DeviceSeq], [DeviceName], [SerialNumber], [Premise], [InsertDate], [VersionNumber], [LastUpdateDate], [IsDeleted] FROM [ITAM].[dbo].[AllDevices] WHERE DeviceName LIKE @filter OR SerialNumber = @filter";

    var param = new SqlParameter("filter", System.Data.SqlDbType.VarChar);
    param.Value = "%YOUR_FILTER_VALUE%";
    command.Parameters.Add(param);

    using (var reader = command.ExecuteReader())
    {
        while (reader.Read())
        {
            dynamic h = new ITAMHost()
            {
                DeviceSeq = reader[0].ToString(),
                DeviceName = reader[1].ToString(),
                SerialNumber = reader[2].ToString(),
                Premise = reader[3].ToString(),
                InsertDate = reader[4].ToString(),
                VersionNumber = reader[5].ToString(),
                LastUpdateDate = reader[6].ToString(),
                IsDeleted = reader[7].ToString(),
            };
            hosts.Add(h);
        }
    }
}