Using post to update existing records in a database

Question

I'm writing an API controller that inserts and updates records in a table. I'm able to insert new items into the database pretty easily, but I'm having a hard time understanding how to update existing records. My current solution is to query the number of records that have the same UserName and DeviceId as the request. If the count is > 0, then we execute the update query. Else, we execute the insert query. But I'm not sure how to return the count of records from the countQuery. Also, I would rather not use the patch or put methods for this. I want all the logic in the post method. Thanks for your help!

public BaseResponse Post([FromBody]PendingAttachmentRequest pending)
    {
        var datasource = "";
        var appVersion = "";
        var sessionId = "";
        var updateQuery = "UPDATE PendingAttachments SET PendingCount = @PendingCount,LastUpdated = @LastUpdated,DataSource = @DataSource WHERE DeviceId = @deviceId AND WHERE UserName = @userName";
        var countQuery = "SELECT count(*) PendingAttachments WHERE DeviceId = @DeviceId AND WHERE UserName = @UserName";
        MobileCompleteServer.Helpers.Connection.GetHeaderInfo(out sessionId, out datasource, out appVersion);
        using (var onbaseConnection = MobileCompleteServer.Helpers.Connection.Connect(sessionId, datasource))
        {
            var connectionString = System.Configuration.ConfigurationManager.AppSettings["ConnectionString"];
            try
            {
                using (SqlConnection sqlConnection = new SqlConnection(connectionString))
                {
                    sqlConnection.Open();
                    using (SqlCommand comm = new SqlCommand(countQuery, sqlConnection))
                    {
                        if (/*how to check if the result of countQuery is > 0*/)
                        {
                            using (SqlCommand sqlComm = new SqlCommand(updateQuery, sqlConnection))
                            {
                                sqlComm.CommandType = System.Data.CommandType.Text;
                                //replace that row with request body
                                sqlComm.Parameters.Add(new SqlParameter("@DataSource", pending.DataSource));
                                sqlComm.Parameters.Add(new SqlParameter("@LastUpdated", pending.LastUpdated));
                                sqlComm.Parameters.Add(new SqlParameter("@PendingCount", pending.PendingCount));
                                sqlComm.Parameters.Add(new SqlParameter("@DeviceId", pending.DeviceId));
                                sqlComm.Parameters.Add(new SqlParameter("@UserName", pending.UserName));
                            }
                        }
                        using (SqlCommand sqlCommand = new SqlCommand("sp_InsertPendingAttachments", sqlConnection))
                        {
                            sqlCommand.CommandType = System.Data.CommandType.StoredProcedure;
                            sqlCommand.Parameters.Add(new SqlParameter("@DataSource", pending.DataSource));
                            sqlCommand.Parameters.Add(new SqlParameter("@UserName", pending.UserName));
                            sqlCommand.Parameters.Add(new SqlParameter("@DeviceId", pending.DeviceId));
                            sqlCommand.Parameters.Add(new SqlParameter("@PendingCount", pending.PendingCount));
                            sqlCommand.Parameters.Add(new SqlParameter("@LastUpdated", pending.LastUpdated));
                            sqlCommand.ExecuteNonQuery();
                        }

                    }
                }
                return new BaseResponse();
            }
            catch (Exception e)
            {
                if (e.Message == Constants.SessionNotFound)
                {
                    return new BaseResponse
                    {
                        Exception = Constants.SessionNotFound,
                        ExceptionStackTrace = e.ToString()
                    };
                }
                else
                {
                    return new BaseResponse
                    {
                        Exception = Constants.PendingAttachmentError,
                        ExceptionStackTrace = e.ToString()
                    };
                }
            }
        }
    }

Answer 1

If you don't care about how many records are there and you just want to check whether at least a record exists or not in table, then use "exists". It will definitely improve query performance. So you need to only check for true or false condition: https://learn.microsoft.com/en-us/sql/t-sql/language-elements/exists-transact-sql?view=sql-server-ver15

You also need to use "ExecuteReader" / "ExecuteScalar" method: Check if a record exists in the database

Try to use Repository design pattern which will separate your data access logic and domain logic and it will also help in making your code testable. Also, Your method is doing lots of things at a time so its violating Single responsibility principle.

Answer 2

Do you not already have a method serving a GET endpoint taking deviceId and userName as parameters? If not create it and call that method to check for existence and, depending on the result, you either call an update or insert handler. That way your API will be more RESTful and modular thus less coupled to the business logic and more testable.