GET method error from c# backend System.NotSupportedException

Question

I'm SUPER new to C# and I have no clue what I am doing. I got a task to use C# backend with local sql server and get/post data into the DB.

I was following a tutorial until i ran into a problem trying to GET data from the sql table:

System.NotSupportedException: Serialization and deserialization of 'System.Type' instances are not supported. Path: $.Columns.DataType. ---> System.NotSupportedException: Serialization and deserialization of 'System.Type' instances are not supported. at System.Text.Json.Serialization.Converters.UnsupportedTypeConverter1.Write(Utf8JsonWriter writer, T value, JsonSerializerOptions options) at System.Text.Json.Serialization.JsonConverter1.TryWrite(Utf8JsonWriter writer, T& value, JsonSerializerOptions options, WriteStack& state) at System.Text.Json.Serialization.Metadata.JsonPropertyInfo1.GetMemberAndWriteJson(Object obj, WriteStack& state, Utf8JsonWriter writer) at System.Text.Json.Serialization.Converters.ObjectDefaultConverter1.OnTryWrite(Utf8JsonWriter writer, T value, JsonSerializerOptions options, WriteStack& state) at System.Text.Json.Serialization.JsonConverter1.TryWrite(Utf8JsonWriter writer, T& value, JsonSerializerOptions options, WriteStack& state) at System.Text.Json.Serialization.JsonConverter1.TryWriteAsObject(Utf8JsonWriter writer, Object value, JsonSerializerOptions options, WriteStack& state) at System.Text.Json.Serialization.JsonConverter1.TryWrite(Utf8JsonWriter writer, T& value, JsonSerializerOptions options, WriteStack& state) at System.Text.Json.Serialization.Converters.IEnumerableConverter1.OnWriteResume(Utf8JsonWriter writer, TCollection value, JsonSerializerOptions options, WriteStack& state) at System.Text.Json.Serialization.JsonCollectionConverter2.OnTryWrite(Utf8JsonWriter writer, TCollection value, JsonSerializerOptions options, WriteStack& state) at System.Text.Json.Serialization.JsonConverter1.TryWrite(Utf8JsonWriter writer, T& value, JsonSerializerOptions options, WriteStack& state) at System.Text.Json.Serialization.Metadata.JsonPropertyInfo1.GetMemberAndWriteJson(Object obj, WriteStack& state, Utf8JsonWriter writer) at System.Text.Json.Serialization.Converters.ObjectDefaultConverter1.OnTryWrite(Utf8JsonWriter writer, T value, JsonSerializerOptions options, WriteStack& state) at System.Text.Json.Serialization.JsonConverter1.TryWrite(Utf8JsonWriter writer, T& value, JsonSerializerOptions options, WriteStack& state) at System.Text.Json.Serialization.JsonConverter1.WriteCore(Utf8JsonWriter writer, T& value, JsonSerializerOptions options, WriteStack& state) --- End of inner exception stack trace --- at System.Text.Json.ThrowHelper.ThrowNotSupportedException(WriteStack& state, NotSupportedException ex) at System.Text.Json.Serialization.JsonConverter1.WriteCore(Utf8JsonWriter writer, T& value, JsonSerializerOptions options, WriteStack& state) at System.Text.Json.Serialization.JsonConverter1.WriteCoreAsObject(Utf8JsonWriter writer, Object value, JsonSerializerOptions options, WriteStack& state) at System.Text.Json.JsonSerializer.WriteCore[TValue](JsonConverter jsonConverter, Utf8JsonWriter writer, TValue& value, JsonSerializerOptions options, WriteStack& state) at System.Text.Json.JsonSerializer.WriteStreamAsync[TValue](Stream utf8Json, TValue value, JsonTypeInfo jsonTypeInfo, CancellationToken cancellationToken) at System.Text.Json.JsonSerializer.WriteStreamAsync[TValue](Stream utf8Json, TValue value, JsonTypeInfo jsonTypeInfo, CancellationToken cancellationToken) at System.Text.Json.JsonSerializer.WriteStreamAsync[TValue](Stream utf8Json, TValue value, JsonTypeInfo jsonTypeInfo, CancellationToken cancellationToken) at Microsoft.AspNetCore.Mvc.Infrastructure.SystemTextJsonResultExecutor.ExecuteAsync(ActionContext context, JsonResult result) at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.g__Awaited|30_0[TFilter,TFilterAsync](ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted) at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.Rethrow(ResultExecutedContextSealed context) at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.ResultNext[TFilter,TFilterAsync](State& next, Scope& scope, Object& state, Boolean& isCompleted) at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.InvokeResultFilters() --- End of stack trace from previous location --- at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.g__Awaited|20_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted) at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.g__Awaited|17_0(ResourceInvoker invoker, Task task, IDisposable scope) at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.g__Awaited|17_0(ResourceInvoker invoker, Task task, IDisposable scope) at Microsoft.AspNetCore.Routing.EndpointMiddleware.g__AwaitRequestTask|6_0(Endpoint endpoint, Task requestTask, ILogger logger) at Microsoft.AspNetCore.Authorization.AuthorizationMiddleware.Invoke(HttpContext context) at Swashbuckle.AspNetCore.SwaggerUI.SwaggerUIMiddleware.Invoke(HttpContext httpContext) at Swashbuckle.AspNetCore.Swagger.SwaggerMiddleware.Invoke(HttpContext httpContext, ISwaggerProvider swaggerProvider) at Microsoft.AspNetCore.Diagnostics.DeveloperExceptionPageMiddleware.Invoke(HttpContext context)

HEADERS

Accept: /

Connection: keep-alive

Host: localhost:5291

User-Agent: PostmanRuntime/7.29.2

Accept-Encoding: gzip, deflate, br

Postman-Token: ad516da9-02bb-424f-8979-64365d710b2c

Error message

This is my controller

using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
using Microsoft.Extensions.Configuration;
using webApi.Models;
using Microsoft.AspNetCore.Hosting;
using System.IO;

namespace webApi.Controllers
{
    [Route("api/[controller]")]
    [ApiController]
    public class ContactController : ControllerBase
    {
        private readonly IConfiguration _configuration;
        private readonly IWebHostEnvironment _env;
        public ContactController(IConfiguration configuration, IWebHostEnvironment env)
        {
            _configuration = configuration;
            _env = env;
        }

        [HttpGet]
        public JsonResult Get()
        {
            string query = @"select ContactName from dbo.Contact";

            DataTable table = new DataTable();
            string sqlDataSource = _configuration.GetConnectionString("ContactAppCon");
            SqlDataReader myReader;
            using (SqlConnection myCon = new SqlConnection(sqlDataSource))
            {
                myCon.Open();
                using (SqlCommand myCommand = new SqlCommand(query, myCon))
                {
                    myReader = myCommand.ExecuteReader();
                    table.Load(myReader);
                    myReader.Close();
                    myCon.Close();
                }
            }

            return new JsonResult(table);
        }
    }
}

This is model

using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;

namespace webApi.Models
{
    public class Contact
    {
        public int ContactID { get; set; }

        public string ContactName { get; set; }

        public string ContactEmail { get; set; }

        public string ContactSubject { get; set; }

        public string ContactMessage { get; set; }


   
    }
}

This is startup.cs

using Microsoft.AspNetCore.Builder;
using Microsoft.AspNetCore.Hosting;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Hosting;
using Microsoft.Extensions.Logging;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Newtonsoft.Json.Serialization;
using Microsoft.Extensions.FileProviders;
using System.IO;

namespace WebApplication1
{
    public class Startup
    {
        public Startup(IConfiguration configuration)
        {
            Configuration = configuration;
        }

        public IConfiguration Configuration { get; }

        // This method gets called by the runtime. Use this method to add services to the container.
        public void ConfigureServices(IServiceCollection services)
        {
            //Enable CORS
            services.AddCors(c =>
            {
                c.AddPolicy("AllowOrigin", options => options.AllowAnyOrigin().AllowAnyMethod().AllowAnyHeader());
            });


            //JSON Serializer
            _ = services.AddControllersWithViews().AddNewtonsoftJson(options =>
            options.SerializerSettings.ReferenceLoopHandling = Newtonsoft.Json.ReferenceLoopHandling.Ignore)
                .AddNewtonsoftJson(options => options.SerializerSettings.ContractResolver
                = new DefaultContractResolver());

            services.AddControllers();
        }

        // This method gets called by the runtime. Use this method to configure the HTTP request pipeline.
        public void Configure(IApplicationBuilder app, IWebHostEnvironment env)
        {
            //Enable CORS
            app.UseCors(options => options.AllowAnyOrigin().AllowAnyMethod().AllowAnyHeader());

            if (env.IsDevelopment())
            {
                app.UseDeveloperExceptionPage();
            }

            app.UseRouting();

            app.UseAuthorization();

            app.UseEndpoints(endpoints =>
            {
                endpoints.MapControllers();
            });

            app.UseStaticFiles(new StaticFileOptions
            {
                FileProvider = new PhysicalFileProvider(
                   Path.Combine(Directory.GetCurrentDirectory(), "Photos")),
                RequestPath = "/Photos"
            });
        }
    }
}

Do you *really* want to work with a DataTable instead of strongly typed objects? Using EF Core or Dapper would reduce the database code to a single line *and* solve the problem. — Panagiotis Kanavos, Aug 31 '22 at 12:15
Did you add `Microsoft.AspNetCore.Mvc.NewtonsoftJson` to the project? If not, `AddNewtonsoftJson()` won't have any effect — Panagiotis Kanavos, Aug 31 '22 at 12:16
Yes, i have added Microsoft.AspNetCore.Mvc.NewtonsoftJson. Thanks for your response! — Henryy, Aug 31 '22 at 12:23

score 1 · Accepted Answer · answered Aug 31 '22 at 12:00

This is a bad idea:

return new JsonResult(table);

A DataTable object contains all sorts of internal workings that (1) probably won't serialize well and (2) you don't want to send to your users. (Do you know for a fact that it doesn't contain your database connection string anywhere? If not, showing it to your users isn't wise.)

If what you want to send to your users is a collection of Contact instances then send them that instead. It's been a while since I've used ADO directly, but I imagine it's at least reasonably close to this:

var result = new List<Contact>();
using (SqlConnection myCon = new SqlConnection(sqlDataSource))
{
    myCon.Open();
    using (SqlCommand myCommand = new SqlCommand(query, myCon))
    {
        myReader = myCommand.ExecuteReader();
        while (myReader.Read())
        {
            result.Add(new Contact
            {
                ContactName = myReader["ContactName"].ToString()
            });
        }
        myReader.Close();
        myCon.Close();
    }
}
return new JsonResult(result);

Since you're just selecting the one column in the query, I'm only mapping one column to the resulting object(s). You can of course map more if you select more.

There are likely other ways to do it as well, and you can reduce the amount of code significantly by using a more modern database access technology (Entity Framework, Dapper, etc.), but overall the goal is to map the raw database records into your typed objects and return those objects.

The OP tried to use JSON.NET which does support DataTable but for some reason, STJ was still used — Panagiotis Kanavos, Aug 31 '22 at 12:13

GET method error from c# backend System.NotSupportedException

1 Answers1