Issue using .sql command in resource file

Question

Im trying to store my sql statement in .sql files in a resource folder,

I need to pass in 2 values to the sql command but im having difficulty doing this

my sql is as follows:

SELECT * FROM claim_header WHERE @columnname = @testcase;

My class calling it:

class DataValidation
{
    public string testCase { get; set; }
    public string elementName { get; set; }
    public string fileDestination = ConfigurationSettings.AppSettings["ValidationReportDestination"];
    public List<string> testCasesList = new List<string>();

    public void PrintValidationResults()
    {
        int counter = 0;
        SqlConnection sqlConnection = new SqlConnection(constring);
        string sqlConnectionStringPassed = "select * from claim_header";
        string sqlConnectionStringFailed = "select * from dbo.[SSIS Exception Info] where data_entity_name like '%CLAIM%' and package_name like '%Extract%' order by 1 desc;";
        SqlDataReader myReaderPassed, myReaderFailed = null;
        SqlCommand sqlCommandPassed = new SqlCommand(sqlConnectionStringPassed, sqlConnection);
        SqlCommand sqlCommandFailed = new SqlCommand(sqlConnectionStringFailed, sqlConnection);

        string sqlPass = EmbeddedResource.GetString("PassQuery.sql");
        SqlCommand cmd = new SqlCommand(sqlPass,sqlConnection);

        cmd.Parameters.AddWithValue("@testcase", "'" + testCasesList[counter] + "'");
        cmd.Parameters.AddWithValue("@columnname", elementName);

        try
        {
            sqlConnection.Open();
            myReaderPassed = sqlCommandPassed.ExecuteReader();
            myReaderFailed = sqlCommandFailed.ExecuteReader();
            object query = cmd.ExecuteScalar();
            string test = cmd.CommandText;

            //Print Passed Results
            Directory.CreateDirectory(fileDestination);
            using (StreamWriter resultWriter = new StreamWriter((fileDestination + elementName + ".txt"), true))
            {
                    while (myReaderPassed.Read())
                    {
                        foreach (string testCase in testCasesList)
                        {
                            if (myReaderPassed[elementName].ToString() == testCase)
                            {
                                {
                                    resultWriter.WriteLine("Field: " + elementName.ToString());
                                    resultWriter.WriteLine("Test Case: " + testCasesList[counter]);
                                    resultWriter.WriteLine("Passed:" + "Yes");
                                    resultWriter.WriteLine("Test: " + cmd.ExecuteScalar());
                                }
                            }
                        }
                        counter++;
                    }           
                }
            }           

        catch (Exception e)
        {
            MessageBox.Show(e.ToString());
        }

    }

At this line: object query = cmd.ExecuteScalar(); query always shows up as null. If I dont pass any paramenters the statement works perfectly? What am I missing?

Answer 1

While it's not exactly going to prevent your query from running, I don't think you will get the result you're after because you can't parameterize a column name, so your query will have to take the form:

SELECT * FROM claim_header WHERE columnname = @testcase;

And you have one or more queries depending on what column you want to search by

If you provide an SQL like:

SELECT * FROM claim_header WHERE @columnname = @testcase;

the only thing you can really do with such a query is set the two parameters to the same value (in which case you will get all rows from the table, because it's like saying SELECT * FROM table WHERE 1=1 ) or set them to different values, in which case you will get 0 rows in your results (because it's like saying SELECT * FROM table WHERE 1=0)

If you want dynamic column names, you're really going to have to make your SQL in your resource file like this:

SELECT * FROM claim_header WHERE {0} = @testcase;

And your code like this:

string sqlCommand = string.Format(Resources.Whatever.SQL, elementName);

Thus the {0} is replaced with "claim_id" or similar.. But be aware of the dangers of SQL injection

Answer 2

the single quotes don't need to be added when creating the parameter

Change

cmd.Parameters.AddWithValue("@testcase", "'" + testCasesList[counter] + "'");

To

cmd.Parameters.AddWithValue("@testcase", testCasesList[counter]);

Answer 3

Without seeing your SQL statement, it's hard to say. However, I would venture that it's an error in your string handling. You've got this line:

cmd.Parameters.AddWithValue("@testcase", "'" + testCasesList[counter] + "'");

You are explicitly wrapping your string in ''s. This is unnecessary when using command parameters. Any escaping that needs to be done will be performed by the .NET data provider (although it's likely no escaping is necessary -- most RDBMSes provide a means to pass parameters directly without them being converted to strings and inserted into the SQL).