Response returned by API

To save the provided JSON response into a SQL database using .NET Core, you'll need to follow these steps:

1. Define a model class that represents the structure of the JSON response.
2. Set up a database context and define a corresponding table.
3. Write code to parse the JSON response and save the data into the database.

Here's a basic example:

1. Model class representing the JSON structure:

public class EWayBillData
{
    public int Status { get; set; }
    public string Uuid { get; set; }
    public string Message { get; set; }
    public string Alert { get; set; }
    public DateTime ValidUpto { get; set; }
    public long EwayBillNo { get; set; }
    public DateTime EwayBillDate { get; set; }
    public string EWayBillPdfUrl { get; set; }
    public string EWayBillQrCodeUrl { get; set; }
    public string Distance { get; set; }
}

1. Database context and entity:

public class YourDbContext : DbContext
{
    public YourDbContext(DbContextOptions options) : base(options) { }

    public DbSet EWayBillData { get; set; }
}

1. Code to parse JSON and save data:

using Newtonsoft.Json;

public class YourService
{
    private readonly YourDbContext _context;

    public YourService(YourDbContext context)
    {
        _context = context;
    }

    public void SaveJsonResponse(string jsonResponse)
    {
        var data = JsonConvert.DeserializeObject(jsonResponse);

        _context.EWayBillData.Add(data);
        _context.SaveChanges();
    }
}

Make sure you have your database connection configured in your appsettings.json or appsettings.Development.json if you're using Entity Framework Core. You'll also need to register your DbContext and any services you're using in your Startup.cs file.

This example assumes you're using Entity Framework Core for database access and Newtonsoft.Json for JSON serialization/deserialization. 

Hello Ramco,

Please use this way,

IRestResponse response = client.Execute(request);

// Check if the response is successful
if (response.IsSuccessful)
{
    
    string responseContent = response.Content;

    dynamic data = JsonConvert.DeserializeObject(responseContent);

    // Now you can work with the deserialized data
}
else
{
    
    Console.WriteLine("Request failed with status code: " + response.StatusCode);
}

If you are using like IRestResponse response = client.Execute(request); then you need to check statuscode with api response sucess code and then need to convert response.content to string then it will wotk for you.

Hi All

  I have below code but it is giving error - Argument 1 : Cannot convert from RestSharp.IrestResponse to string.

IRestResponse response = client.Execute(request);

                dynamic data = JsonConvert.DeserializeObject(response);

Thanks

Hi Ramco, 

Both solutions are provided to you. Jaimin Shethiya shared code using an entity framework and  Jignesh Kumar has shared ADO.Net. 
Do let us know if you want more help :)

Hello Ramco,

You need to create table if you have not created. please creaete table as below,

CREATE TABLE EWayBill (
    Id INT IDENTITY(1,1) PRIMARY KEY,
    Status INT,
    Uuid VARCHAR(50),
    Message VARCHAR(255),
    Alert VARCHAR(255),
    ValidUpto DATETIME,
    EwayBillNo BIGINT,
    EwayBillDate DATETIME,
    EWayBillPdfUrl VARCHAR(255),
    EWayBillQrCodeUrl VARCHAR(255),
    Distance VARCHAR(50)
);

Then you need to deserialize your api respone in C# and you can store in above table,

using System;
using System.Data.SqlClient;
using Newtonsoft.Json;

class Program
{
    static void Main()
    {
	     // Let's consider this json string is your api response
        string json = @"{
            ""status"": 1,
            ""uuid"": ""fcfc0321-838a-4014-b88f-3e31f57fe331"",
            ""message"": ""EWB Generated"",
            ""alert"": ""Total invoice value is more than the sum of total assessible value and tax values, "",
            ""validUpto"": ""2024-04-18T23:59:00"",
            ""ewayBillNo"": 121010463262,
            ""ewayBillDate"": ""2024-04-17T17:04:00"",
            ""EWayBillPdfUrl"": ""/~fnsjdjszvw/a/eway-bill/fcfc0321-838a-4014-b88f-3e31f57fe331/ewb/.pdf/"",
            ""EWayBillQrCodeUrl"": ""/~fnsjdjszvw/a/ewaybill/fcfc0321-838a-4014-b88f-3e31f57fe331/qr-code/"",
            ""Distance"": ""100""
        }";

        // Deserialize API JSON response to dynamic object
        dynamic data = JsonConvert.DeserializeObject(json);

        // Connection string for SQL Server
        string connectionString = "Your connection string";

        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            // Open the connection
            connection.Open();

            // SQL command to insert data into the table
            string sql = @"INSERT INTO EWayBill (Status, Uuid, Message, Alert, ValidUpto, EwayBillNo, EwayBillDate, EWayBillPdfUrl, EWayBillQrCodeUrl, Distance)
                           VALUES (@Status, @Uuid, @Message, @Alert, @ValidUpto, @EwayBillNo, @EwayBillDate, @EWayBillPdfUrl, @EWayBillQrCodeUrl, @Distance)";

            using (SqlCommand command = new SqlCommand(sql, connection))
            {
                // Add parameters
                command.Parameters.AddWithValue("@Status", data.status);
                command.Parameters.AddWithValue("@Uuid", data.uuid);
                command.Parameters.AddWithValue("@Message", data.message);
                command.Parameters.AddWithValue("@Alert", data.alert);
                command.Parameters.AddWithValue("@ValidUpto", DateTime.Parse(data.validUpto.ToString()));
                command.Parameters.AddWithValue("@EwayBillNo", data.ewayBillNo);
                command.Parameters.AddWithValue("@EwayBillDate", DateTime.Parse(data.ewayBillDate.ToString()));
                command.Parameters.AddWithValue("@EWayBillPdfUrl", data.EWayBillPdfUrl);
                command.Parameters.AddWithValue("@EWayBillQrCodeUrl", data.EWayBillQrCodeUrl);
                command.Parameters.AddWithValue("@Distance", data.Distance);

                // Execute the command
                int rowsAffected = command.ExecuteNonQuery();
                Console.WriteLine($"Rows affected: {rowsAffected}");
            }
        }
    }
}

Hello Ramco,

Please refer below code.

Public class Eway
{
	[JsonProperty("uuid")]
	public Guid Id { get; set; }
	
	public string Message { get; set; }
	
	public string Alert { get; set; }
	
	public DateTime ValidUpto { get; set; }
	
	public long EwayBillNo { get; set; }
	
	public DateTime EwayBillDate { get; set; }
	
	public string EWayBillPdfUrl { get; set; }
	
	public string EWayBillQrCodeUrl { get; set; }
	
	public string Distance { get; set; }
	
	public bool Status { get; set; }
}

var eway = JsonConvert.DeseializeObject(response);

_dbContext.Eway.Add(eway);
_dbContext.SaveChanges();

Thanks