I’m retrieving an HTTP response which I am deserializing the values into an Azure SQL database. I cannot find any way to format the SQL command to UPDATE string variables into the last record of the table. The intent is to overwrite the last record with each new token to avoid database maintenance requirements. I can statically write a value as in the example below, but I need it to be dynamically passed by variable which seems to throw off the syntax or command execution for some reason. No errors or exceptions are thrown – it just doesn’t do anything.
Does anyone have any ideas on how I could go about accomplishing this? The SQL specific code is listed below for reference.
dynamic access = JsonConvert.DeserializeObject(response.Content);
string accessToken = access.access_token;
string timestamp = centralTime.ToString();
System.Data.SqlClient.SqlConnection connect = new System.Data.SqlClient.SqlConnection(connectionString);
System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.SqlCommand();
command.CommandType = System.Data.CommandType.Text;
// this works as needed to insert a new record where required, but builds record counts over time
// command.CommandText = @"INSERT INTO AccessTokens (Token, LastRefreshDate) VALUES (@Token, @LastRefreshDate)";
// command.Parameters.AddWithValue("@Token", accessToken);
// command.Parameters.AddWithValue("@LastRefreshDate", centralTime);
// I've tried as many variations as possible based on what's available online upon searching; these are two examples that don't work
// command.CommandText = @"UPDATE TOP 1 * FROM AccessTokens (Token, LastRefreshDate) VALUES (@Token, @LastRefreshDate)";
// command.CommandText = @"UPDATE AccessTokens SET Token = " + accessToken + " , LastRefreshDate = " + timestamp;
// this will update the record with the new value, but it's static
command.CommandText = @"UPDATE AccessTokens SET Token = 12345";
string test = "test";
// attempting to update with a string variable does not work, which is the desired outcome in order to maintain only one record with a dynamically changing value over time
// command.CommandText = @"UPDATE AccessTokens SET Token = " + test;
command.Connection = connect;
connect.Open();
command.ExecuteNonQuery();
connect.Close();
Answers:
Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.
Method 1
The understanding is that you need to continually update the last token inserted into your AccessToken table. Assuming your LastRefreshDate is unique enough into the milliseconds, this should look like
command.CommandText = @"UPDATE AccessTokens set Token = @Token where LastRefreshDate = (select Max(LastRefreshDate) from AccessTokens)"
Ideally, if this token exists with a bunch of other access tokens you would have a unique ID in that AccessToken where it’s restricted to a particular id like:
command.CommandText = @"UPDATE AccessTokens set Token = @Token where LastRefreshDate = (select Max(LastRefreshDate) from AccessTokens and <a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="d79e93ea979e93">[email protected]</a>) and <a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="39707d0479707d">[email protected]</a>"
All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0