how to store the response from external API to an existing table

To store the API response in an existing table using a stored procedure, you can follow these steps:

1. Create a stored procedure in your database that accepts the necessary parameters to store the data. In this case, the parameters would be the "Employee Joined Date" and "Employee ID".

2. Parse the API response and extract the required data. You can use a JSON parser or any other suitable method to extract the values of "Employee Joined Date" and "Employee ID" from the API response.

3. Call the stored procedure from your code and pass the extracted values as parameters. Here's an example of how you can do this in C# using ADO.NET:

4. using (SqlConnection connection = new SqlConnection(connectionString))
   {
       SqlCommand command = new SqlCommand("YourStoredProcedureName", connection);
       command.CommandType = CommandType.StoredProcedure;
   
       // Add parameters
       command.Parameters.AddWithValue("@EmployeeJoinedDate", employeeJoinedDate);
       command.Parameters.AddWithValue("@EmployeeID", employeeID);
   
       connection.Open();
       command.ExecuteNonQuery();
   }
   

   Make sure to replace "YourStoredProcedureName" with the actual name of your stored procedure, and provide the correct connection string.

5. Execute the stored procedure within the code block, passing the extracted values as parameters. This will insert the data into the existing table.

6. By following these steps, you will be able to store the "Employee Joined Date" and "Employee ID" from the API response into an existing table using a stored procedure.

Hello Meghana,

There are various ways to save the response in the database, but a clear understanding of your requirements is essential. Once we have clarity on your needs, we can propose a more tailored solution.

You can do it like this in c# app

CREATE PROCEDURE InsertEmployeeData
    @EmployeeJoinedDate DATETIME,
    @EmployeeID NVARCHAR(50),
    @JobID NVARCHAR(50),
    @Status NVARCHAR(50)
AS
BEGIN
    INSERT INTO EmployeeTable (EmployeeJoinedDate, EmployeeID, JobID, Status)
    VALUES (@EmployeeJoinedDate, @EmployeeID, @JobID, @Status)
END

public class EmployeeData
{
    public DateTime EmployeeJoinedDate { get; set; }
    public string EmployeeID { get; set; }
    public string JobID { get; set; }
    public string Status { get; set; }
}

your api method

// Your JSON data
        string jsonData = "your API Response";

        // Deserialize JSON data into a list of EmployeeData objects
        List employeeList = JsonConvert.DeserializeObject>(jsonData);

        // Connection string for your SQL Server database
        string connectionString = "YourConnectionStringHere";

        // Insert data into the database using a stored procedure
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            connection.Open();

            foreach (EmployeeData employee in employeeList)
            {
                using (SqlCommand command = new SqlCommand("InsertEmployeeData", connection))
                {
                    command.CommandType = CommandType.StoredProcedure;

                    command.Parameters.AddWithValue("@EmployeeJoinedDate", employee.EmployeeJoinedDate);
                    command.Parameters.AddWithValue("@EmployeeID", employee.EmployeeID);
                    command.Parameters.AddWithValue("@JobID", employee.JobID);
                    command.Parameters.AddWithValue("@Status", employee.Status);

                    command.ExecuteNonQuery();
                }
            }
        }

I would have written some generic method to read JSON in to c# table and store in datatbase. Below is example to read Json in table. Below are the steps:

•  Deserialize your class  =>  List EmployeeList = JsonConvert.DeserializeObject>(jsonString);
• Store in table => EmployeeList.ToDataTable();

public static DataTable ToDataTable(this IList data)
{
    PropertyDescriptorCollection props =
    TypeDescriptor.GetProperties(typeof(T));
    DataTable table = new DataTable();
    for(int i = 0 ; i < props.Count ; i++)
    {
        PropertyDescriptor prop = props[i];
        table.Columns.Add(prop.Name, prop.PropertyType);
    }
    object[] values = new object[props.Count];
    foreach (T item in data)
    {
        for (int i = 0; i < values.Length; i++)
        {
            values[i] = props[i].GetValue(item);
        }
        table.Rows.Add(values);
    }
    return table;        
}

Please accept answer, if you find it useful

You may use OPENJSON function to retrive the json data and store it in SQL table. Below is the sample code snipet to achieve it. 

INSERT INTO YourExistingTable (JoinedDate, EmployeeID, JobID, Status)
    SELECT 
        CONVERT(DATETIME, json.Value, 126) AS JoinedDate,
        json.EmployeeID,
        json.JobID,
        json.Status
    FROM OPENJSON(@JsonData)
    WITH (
        JoinedDate NVARCHAR(50),
        EmployeeID INT,
        JobID NVARCHAR(50),
        Status NVARCHAR(50)
    ) AS json
    ON DUPLICATE KEY UPDATE
        YourExistingTable.JoinedDate = json.JoinedDate,
        YourExistingTable.JobID = json.JobID,
        YourExistingTable.Status = json.Status;