How to Use Variable Data in C# SQL Query String

Question

I have a project I'm working in, which I did not create. I am still relatively new to C# and ASP.NET as a whole. I am faced with this SQL query:

var sql = @"SELECT * FROM [database] WHERE [submitDate] >= Convert(datetime,'20130301')";

var sql = @"SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY id)AS rowNum, * FROM [webDBs].[dbo].[employeeRecognition] WHERE [submitDate] >= Convert(datetime,'20130301')
) AS E
WHERE rowNum >= {0}
AND rowNum < {1}";

These of course behaves exactly as expected. What I need to do, however, is make the 2013 part of the Convert(datetime,'20130301') bit actually equal to the current yet, so that we don't have to update this query every single year.

Based on my limited experience I started by trying to concatenate in a C# variable, not only did that not work, but after some research I learned that method can be an opening for potential SQL Injections.

I read a bit about parameterizing the SQL Query, but everything I saw on that led me to believe that I would have to rewrite/rethink how this data is being pulled from the database in the first place.

Any advice on how to accomplish my goal?

Here's what I'm working with:

protected string RecordCount()
        {
            EmployeeRecognitionDataContext db = new EmployeeRecognitionDataContext();

            var sql = @"SELECT * FROM [database] WHERE [submitDate] >= Convert(datetime,'20130301')";

            var query = db.ExecuteQuery<employeeRecognition>(sql);

            //return "[{\"count\":\"" + query.Count() + "\"}]";
            return query.Count().ToString();
        }

Function the second var is being used in:

 protected string SelectRecords(int startIndex, int pageSize) {

            int rowNum = startIndex + pageSize;

            EmployeeRecognitionDataContext db = new EmployeeRecognitionDataContext();

            var sql = @"SELECT * FROM (
                        SELECT ROW_NUMBER() OVER(ORDER BY id)AS rowNum, * FROM [database] WHERE [submitDate] >= Convert(datetime,'20130301')
                        ) AS E
                        WHERE rowNum >= {0} 
                        AND rowNum < {1}";

            var query = db.ExecuteQuery<employeeRecognition>(sql, startIndex, rowNum);

            List<Employee> eList = new List<Employee>();

            foreach (var employee in query)
            {
                eList.Add(new Employee { 
                    value = employee.id.ToString(),
                    firstName = employee.firstName, 
                    lastName = employee.lastName, 
                    department = employee.department,
                    drop = employee.shortAchievement, 
                    recognition = employee.longAchievement,
                    submitDate = employee.submitDate.ToString()
                });

            }

            JavaScriptSerializer serializer = new System.Web.Script.Serialization.JavaScriptSerializer();

            return serializer.Serialize(eList);
        }

What problem are you having with parameters? Parameters are the right way to do this. (or do it all in SQL) — SLaks
– SLaks, Commented Aug 28, 2013 at 17:06
The problem starts with me not knowing how to properly add parameters to this particular string. The methods of doing so that I have found show Parameter.Add as methods that do not seem to work in this particular instance where the SQL Query is simply a string. — TXChetG
– TXChetG, Commented Aug 28, 2013 at 17:08

Nathan · Accepted Answer · 2013-08-28 17:23:25Z

2

I don't know what the rest of your code looks like but here's a possible example of using parameters.

using (SqlConnection connection = new SqlConnection("<connection string>"))
{
    var cmd = connection.CreateCommand();
    cmd.CommandText = @"SELECT * FROM [database] WHERE [submitDate] >= @myDate";
    DateTime myDate = DateTime.Parse("<date string>");
    cmd.Parameters.AddWithValue("@myDate", myDate);
    var reader = cmd.ExecuteReader();
    /* etc. */
}

answered Aug 28, 2013 at 17:23

Nathan

1,0907 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

TXChetG Over a year ago

I added more to my original question to give you a better idea of what I'm facing. Your suggestion is the parametizing method I had seen earlier, but am not yet sure how to properly implement it.

Nathan Over a year ago

Now that I see the rest of the picture, I don't think you really need parameters. You would have to overload the ExecuteQuery<T> method to accept a sql parameter.

evanmcdonnal · Accepted Answer · 2013-08-28 18:00:26Z

1

You could just change the code which generates that string. Something like;

   String.Format(@"SELECT * FROM [database] WHERE [submitDate] >= Convert(datetime,'{0}0301')", DateTime.Now.Year.ToString());

Will make it so the string always has the current year there.

The docs for String.Format can be found here http://msdn.microsoft.com/en-us/library/system.string.format.aspx

It basically works like this. You call String.Format with the first argument being your string. Within that string, you put format specifiers (the {0} is a format specifier). Each instance of {x} is replaced with the corresponding argument. So, you can do something like this;

   string replacingThreeValues = String.Format("Replacing {0}, {1}, {2}", "one", "two", "three");

And it will result in replaceingThreeValues == "Replacing one, two, three". So in your second example of var sql = ... you've put in some format specifiers, but you're not calling Format, also you're not passing any arguments to replace those values with. Instead you just get a string with the literal {0} and {1} in it. Only when you're calling String.Format and passing the appropriate arguments will those values be replaced with the arguments you pass it.

edited Aug 28, 2013 at 18:00

answered Aug 28, 2013 at 17:08

evanmcdonnal

48.5k17 gold badges111 silver badges119 bronze badges

9 Comments

TXChetG Over a year ago

Thanks for stopping in. When I try to use this method I am met with the following error: An object reference is required for the non static field...

evanmcdonnal Over a year ago

@TXChetG sorry I put in an edit, that should be DateTime.Now.Year, I was calling it like it was a static property when it's an instance property.

TXChetG Over a year ago

Thanks for the edit. That at least doesn't cause a build error. It's claiming to return no records at this point which is fairly confusing, since it works just fine if I just use 2013.

evanmcdonnal Over a year ago

@TXChetG it might still need the ToString() call. Look at what the string is in the debugger. If it's the same as the static string you're using then it's obv not the string.

TXChetG Over a year ago

I've made a huge mistake. Your suggestion is working very well at the moment. I had stupidly forgotten to replace [database] in your suggestion with my actual database name.

|

Collectives™ on Stack Overflow

How to Use Variable Data in C# SQL Query String

2 Answers 2

2 Comments

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related