I have a controller that receives a list of objects. First it checks if any of these objects already exists in the table by using ExecuteScalar. If rows already exist, we run the Update stored procedure. If not, we run the Insert stored procedure. However, my code fails at the Execute Scalar function which tells us 'Must declare the scalar variable @DeviceId'. I'm not sure if the error message is referring to the query or the Sql Parameters.
public BaseResponse Post([FromBody]List<PendingAttachment> pendingAttachmentRequest)
{
PendingAttachment pendingAttachment = new PendingAttachment();
List<SqlParameter> sqlParameters = new List<SqlParameter>()
{
new SqlParameter("@Datasource", pendingAttachment.DataSource),
new SqlParameter("@LastUpdated", pendingAttachment.LastUpdated),
new SqlParameter("@PendingCount", pendingAttachment.PendingCount),
new SqlParameter("@DeviceId", pendingAttachment.DeviceId),
new SqlParameter("@Username", pendingAttachment.UserName),
};
try
{
RequestHeaders headers = new RequestHeaders();
var query = "SELECT count(*) FROM PendingAttachments WHERE DeviceId = @DeviceId AND UserName = @UserName";
using (var onbaseConnection = MobileCompleteServer.Helpers.OnbaseAuth.Connect(headers))
{
var connectionString = System.Configuration.ConfigurationManager.AppSettings["ConnectionString"];
using (SqlConnection sqlConnection = new SqlConnection(connectionString))
{
sqlConnection.Open();
using (SqlCommand comm = new SqlCommand(query, sqlConnection))
{
comm.CommandType = System.Data.CommandType.Text;
foreach(PendingAttachment attachment in pendingAttachmentRequest)
{
comm.Parameters.AddRange(sqlParameters.ToArray());
comm.Parameters.Clear();
}
int rowCount = (int)comm.ExecuteScalar(); //FAILS HERE
if (rowCount > 0)
{
using (SqlCommand sqlComm = new SqlCommand("sp_UpdatePendingAttachments", sqlConnection))
{
sqlComm.CommandType = System.Data.CommandType.StoredProcedure;
foreach (PendingAttachment attachment in pendingAttachmentRequest)
{
sqlComm.Parameters.AddRange(sqlParameters.ToArray());
sqlComm.Parameters.Clear();
}
sqlComm.ExecuteNonQuery();
}
}
else
{
using (SqlCommand sqlCommand = new SqlCommand("sp_InsertPendingAttachments", sqlConnection))
{
sqlCommand.CommandType = System.Data.CommandType.StoredProcedure;
foreach(PendingAttachment attachment in pendingAttachmentRequest)
{
sqlCommand.Parameters.AddRange(sqlParameters.ToArray());
sqlCommand.Parameters.Clear();
}
sqlCommand.ExecuteNonQuery();
}
}
}
}
return new BaseResponse();
}
}
catch (Exception ex)
{
return new BaseResponse
{
Exception = ErrorCodes.Get(ex, ErrorCodes.PendingAttachmentError),
ExceptionStackTrace = ex.ToString()
};
}
}
}
