Correctly commit SQL statement with parameters

Question

I have two SQL statements in my C # code to get some values. I know that they are open for SQL injection, since I do not use parameters, but I'm not sure if I am implementing them correctly.

(Note: each of them is in cycles that cycle through the rows of the data table) First example:

string sql2 = "select max(day) as day from users u join days d on d.User_ID = u.id where u.ActiveUser = 1 and u.id = " + Users["ID"].ToString(); command.CommandText = sql2; string dt = command.ExecuteScalar().ToString(); 

In the above description, it retrieves the date-time and assigns the string dt to it. All that has an identifier or identifier is bigint.

 string sql = "SELECT MAX(Day) FROM Days WHERE Project_ID IN (SELECT ID FROM Projects WHERE Parent_ID = -1 AND ID = " + row["ID"] + ") HAVING MAX(Day) < DATEADD(dd, -730, getdate())"; command.CommandText = sql; object val = command.ExecuteScalar(); 

The above statement is the same as the first statement, since it retrieves the datetime value. All that has an identifier or identifier is bigint.

That's what I came up with for the first, did I miss something or did something wrong?

 string sql2 = "select max(day) as day from users u join days d on d.User_ID = u.id where u.ActiveUser = 1 and u.id = @userID"; using (conn) { using (SqlCommand cmd = new SqlCommand(sql2, conn)) { command.Parameters.AddWithValue("@userID", drUsers["ID"]); conn.Open(); dt = (DateTime)command.ExecuteScalar(); } } 

Note. I asked a question last week about DateTime conversions, and there was a problem that could not be resolved, so I might just have to use the string version of the returned datetime. Will it affect anything?

Answer 1

It will look like this:

 string sql2 = @"select max(day) as day from users u join days d on d.User_ID = u.id where u.ActiveUser = 1 and u.id = @id"; string sql = @"SELECT MAX(Day) FROM Days WHERE Project_ID IN (SELECT ID FROM Projects WHERE Parent_ID = -1 AND ID = @id) HAVING MAX(Day) < DATEADD(dd, -730, getdate())"; DateTime dt; using (conn) { using (SqlCommand cmd = new SqlCommand(sql2, conn)) { cmd.Parameters.AddWithValue("@id", (Int64)Users["ID"]); conn.Open(); dt = (DateTime)cmd.ExecuteScalar(); } } using (conn) { using (SqlCommand cmd = new SqlCommand(sql, conn)) { cmd.Parameters.AddWithValue("@id", (Int64)row["ID"]); conn.Open(); dt = (DateTime)cmd.ExecuteScalar(); } } 

However, for performance reasons, I would not do this once per line in a DataTable. You can simply get all max (day) values ​​grouped by identifier once on the client side. i.e:

 string sql = @"select u.Id, max(day) as day from users u join days d on d.User_ID = u.id where u.ActiveUser = 1 group by u.id"; 

EDIT: I saw your editing later and an added question about time and time. Do not convert the date and time value to a string. This is where the mistakes begin. If you "HAVE", use only the canonical ODBC format, which is independent of the SQL server settings - yyyyMMdd. Other datetime string types will only work by chance if they ever will.

Answer 2

 string sql = @"select u.Id, max(day) as day from users u join days d on d.User_ID = u.id where u.ActiveUser = 1 group by u.id"; DataTable tbl = new DataTable(); using (conn) { using (SqlCommand cmd = new SqlCommand(sql, conn)) { conn.Open(); tbl.Load( cmd.ExecuteReader() ); conn.Close(); } } var dates = tbl.AsEnumerable() .ToDictionary(t => t.Field<Int64>("ID"), t => t.Field<DateTime?>("Day")); 

Then in your loop you could say the following:

 var myId = 4; var myDate = dates[myId];