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);
}
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