Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.

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);
        }
share|improve this question
What problem are you having with parameters? Parameters are the right way to do this. (or do it all in SQL) – SLaks Aug 28 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 Aug 28 at 17:08

2 Answers

up vote 0 down vote accepted

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.

share|improve this answer
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... – TXChetG Aug 28 at 17:14
@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. – evanmcdonnal Aug 28 at 17:17
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. – TXChetG Aug 28 at 17:20
@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. – evanmcdonnal Aug 28 at 17:23
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. – TXChetG Aug 28 at 17:33
show 5 more comments

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. */
}
share|improve this answer
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. – TXChetG Aug 28 at 17:29
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. – Nathan Aug 28 at 17:35

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.