Untitled

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

class Program
{
    static void Main(string[] args)
    {
        string jsonString = System.IO.File.ReadAllText("tripdata.json");
        JObject json = JObject.Parse(jsonString);

        // TODO: Replace this with your actual connection string
        string connectionString = "Data Source=localhost;Initial Catalog=YourDbName;Integrated Security=True;";

        using (SqlConnection conn = new SqlConnection(connectionString))
        {
            conn.Open();
            SqlTransaction transaction = conn.BeginTransaction();

            try
            {
                Console.WriteLine("Inserting into Trip_Master...");

                JObject tripMaster = (JObject)json["Trip_Master"];
                string insertTripMaster = @"
                    INSERT INTO Trip_Master (companyCode, employeeCode, employeeName, desgination, desginationCode,
                        department, departmentCode, costCenter, costCenterCode, bandName, bandCode,
                        locationName, locationCode, isDomestic, isInternational, tripStartDate, tripEndDate,
                        tripStartLocation, tripEndLocation, claimId, legalEntityId, legalEntityName,
                        remarks, sendBackStatus, approvedDate, updatedDate, action)
                    OUTPUT INSERTED.tripId
                    VALUES (@companyCode, @employeeCode, @employeeName, @desgination, @desginationCode,
                        @department, @departmentCode, @costCenter, @costCenterCode, @bandName, @bandCode,
                        @locationName, @locationCode, @isDomestic, @isInternational, @tripStartDate, @tripEndDate,
                        @tripStartLocation, @tripEndLocation, @claimId, @legalEntityId, @legalEntityName,
                        @remarks, @sendBackStatus, @approvedDate, @updatedDate, @action)";
                
                SqlCommand cmdTrip = new SqlCommand(insertTripMaster, conn, transaction);
                foreach (var prop in tripMaster)
                    cmdTrip.Parameters.AddWithValue("@" + prop.Key, prop.Value?.ToObject<object>() ?? DBNull.Value);

                int tripId = (int)cmdTrip.ExecuteScalar();
                Console.WriteLine($"Trip inserted with tripId: {tripId}");

                JArray reportingParams = (JArray)json["Reporting_Params"];
                if (reportingParams != null)
                {
                    Console.WriteLine("Inserting into Reporting_Params...");
                    foreach (JObject param in reportingParams)
                    {
                        string insertParam = @"
                            INSERT INTO Reporting_Params (tripId, parameterName, paramValue)
                            VALUES (@tripId, @parameterName, @paramValue)";
                        
                        SqlCommand cmdParam = new SqlCommand(insertParam, conn, transaction);
                        cmdParam.Parameters.AddWithValue("@tripId", tripId);
                        cmdParam.Parameters.AddWithValue("@parameterName", param["parameterName"]?.ToString());
                        cmdParam.Parameters.AddWithValue("@paramValue", param["paramValue"]?.ToString());
                        cmdParam.ExecuteNonQuery();
                    }
                }

                JArray serviceRequests = (JArray)json["Service_Request"];
                if (serviceRequests != null)
                {
                    Console.WriteLine("Inserting into Service_Request...");
                    foreach (JObject request in serviceRequests)
                    {
                        string insertRequest = @"
                            INSERT INTO Service_Request (tripId, lineItemNumber, lineReferenceNumber, cartReference,
                                amendmentReference, amendmentType, bookingType, bookingStatus, bookingRecievedFrom,
                                taxType, bookingDate, fromStateCode, fromStateGSTNo, toStateCode, billingEntityCurreny,
                                toStateGSTNo, iataCode, glCode, currency, exchangeRate, totalAmountLocalCurrency,
                                totalAmount, paymentMode, travelType, voucherUrl, cancellationDate, issuedByAgentCode,
                                clientRequestId, Product)
                            OUTPUT INSERTED.serviceRequestId
                            VALUES (@tripId, @lineItemNumber, @lineReferenceNumber, @cartReference,
                                @amendmentReference, @amendmentType, @bookingType, @bookingStatus, @bookingRecievedFrom,
                                @taxType, @bookingDate, @fromStateCode, @fromStateGSTNo, @toStateCode, @billingEntityCurreny,
                                @toStateGSTNo, @iataCode, @glCode, @currency, @exchangeRate, @totalAmountLocalCurrency,
                                @totalAmount, @paymentMode, @travelType, @voucherUrl, @cancellationDate, @issuedByAgentCode,
                                @clientRequestId, @Product)";
                        
                        SqlCommand cmdRequest = new SqlCommand(insertRequest, conn, transaction);
                        cmdRequest.Parameters.AddWithValue("@tripId", tripId);
                        foreach (var prop in request)
                        {
                            if (prop.Key != "Service_Details" && prop.Key != "Approver_List")
                                cmdRequest.Parameters.AddWithValue("@" + prop.Key, prop.Value?.ToObject<object>() ?? DBNull.Value);
                        }

                        int serviceRequestId = (int)cmdRequest.ExecuteScalar();
                        Console.WriteLine($"Inserted Service_Request with serviceRequestId: {serviceRequestId}");

                        JArray approvers = (JArray)request["Approver_List"];
                        if (approvers != null)
                        {
                            Console.WriteLine("Inserting into Approver_List...");
                            foreach (JObject approver in approvers)
                            {
                                string insertApprover = @"
                                    INSERT INTO Approver_List (serviceRequestId, approverName, approverEmail, approvedDate, Level)
                                    VALUES (@serviceRequestId, @approverName, @approverEmail, @approvedDate, @Level)";
                                
                                SqlCommand cmdApprover = new SqlCommand(insertApprover, conn, transaction);
                                cmdApprover.Parameters.AddWithValue("@serviceRequestId", serviceRequestId);
                                cmdApprover.Parameters.AddWithValue("@approverName", approver["approverName"]?.ToString());
                                cmdApprover.Parameters.AddWithValue("@approverEmail", approver["approverEmail"]?.ToString());
                                cmdApprover.Parameters.AddWithValue("@approvedDate", approver["approvedDate"]?.ToObject<object>() ?? DBNull.Value);
                                cmdApprover.Parameters.AddWithValue("@Level", approver["Level"]?.ToObject<object>() ?? DBNull.Value);
                                cmdApprover.ExecuteNonQuery();
                            }
                        }

                        JArray serviceDetails = (JArray)request["Service_Details"];
                        if (serviceDetails != null)
                        {
                            Console.WriteLine("Inserting into Service_Details...");
                            foreach (JObject detail in serviceDetails)
                            {
                                string insertDetail = @"
                                    INSERT INTO Service_Details (serviceRequestId, origin, fromCity, fromCountry,
                                        destination, toCity, toCountry, platingCarrier, operatedAirline, airlineCode,
                                        supplierCode, flightNumber, departureTerminal, arrivalTerminal, dealCode,
                                        airLineName, departureDate, arrivalDate, departureTime, arrivalTime, duration,
                                        supplierCurrency, exchangeRate, fareType, supplierType, cabinClass, pnr, noOfTravellers,
                                        travellerName, clientJourneyId)
                                    OUTPUT INSERTED.serviceDetailsId
                                    VALUES (@serviceRequestId, @origin, @fromCity, @fromCountry,
                                        @destination, @toCity, @toCountry, @platingCarrier, @operatedAirline, @airlineCode,
                                        @supplierCode, @flightNumber, @departureTerminal, @arrivalTerminal, @dealCode,
                                        @airLineName, @departureDate, @arrivalDate, @departureTime, @arrivalTime, @duration,
                                        @supplierCurrency, @exchangeRate, @fareType, @supplierType, @cabinClass, @pnr, @noOfTravellers,
                                        @travellerName, @clientJourneyId)";
                                
                                SqlCommand cmdDetail = new SqlCommand(insertDetail, conn, transaction);
                                cmdDetail.Parameters.AddWithValue("@serviceRequestId", serviceRequestId);
                                foreach (var prop in detail)
                                {
                                    if (prop.Key != "Sector" && prop.Key != "Traveller_Info")
                                        cmdDetail.Parameters.AddWithValue("@" + prop.Key, prop.Value?.ToObject<object>() ?? DBNull.Value);
                                }

                                int serviceDetailsId = (int)cmdDetail.ExecuteScalar();
                                Console.WriteLine($"Inserted Service_Details with serviceDetailsId: {serviceDetailsId}");

                                JArray sectors = (JArray)detail["Sector"];
                                if (sectors != null)
                                {
                                    Console.WriteLine("Inserting into Sector...");
                                    foreach (JObject sector in sectors)
                                    {
                                        string insertSector = @"
                                            INSERT INTO Sector (serviceDetailsId, origin, destination, airlineCode, flightNumber,
                                                departureTerminal, arrivalTerminal, rbd, fareBasis, airlineName, departureDate,
                                                arrivalDate, departureTime, arrivalTime, duration)
                                            VALUES (@serviceDetailsId, @origin, @destination, @airlineCode, @flightNumber,
                                                @departureTerminal, @arrivalTerminal, @rbd, @fareBasis, @airlineName, @departureDate,
                                                @arrivalDate, @departureTime, @arrivalTime, @duration)";
                                        
                                        SqlCommand cmdSector = new SqlCommand(insertSector, conn, transaction);
                                        cmdSector.Parameters.AddWithValue("@serviceDetailsId", serviceDetailsId);
                                        foreach (var prop in sector)
                                            cmdSector.Parameters.AddWithValue("@" + prop.Key, prop.Value?.ToObject<object>() ?? DBNull.Value);

                                        cmdSector.ExecuteNonQuery();
                                    }
                                }

                                JArray travellers = (JArray)detail["Traveller_Info"];
                                if (travellers != null)
                                {
                                    Console.WriteLine("Inserting into Traveller_Info...");
                                    foreach (JObject traveller in travellers)
                                    {
                                        string insertTraveller = @"
                                            INSERT INTO Traveller_Info (serviceDetailsId, titleInfo, firstName, lastName, employeeCode,
                                                paxType, mobileNumber, emailID, ticketNumber, baseFare, taxFare, seatFee, mealFee,
                                                baggageFee, miscFee, serviceFee, supplierFee, financialCharges, igst, cgst, sgst,
                                                taxBreakup, airlineGstAmount, totalPrice, totalAmountLocalCurrency, markupFee)
                                            VALUES (@serviceDetailsId, @titleInfo, @firstName, @lastName, @employeeCode,
                                                @paxType, @mobileNumber, @emailID, @ticketNumber, @baseFare, @taxFare, @seatFee, @mealFee,
                                                @baggageFee, @miscFee, @serviceFee, @supplierFee, @financialCharges, @igst, @cgst, @sgst,
                                                @taxBreakup, @airlineGstAmount, @totalPrice, @totalAmountLocalCurrency, @markupFee)";
                                        
                                        SqlCommand cmdTraveller = new SqlCommand(insertTraveller, conn, transaction);
                                        cmdTraveller.Parameters.AddWithValue("@serviceDetailsId", serviceDetailsId);
                                        foreach (var prop in traveller)
                                            cmdTraveller.Parameters.AddWithValue("@" + prop.Key, prop.Value?.ToObject<object>() ?? DBNull.Value);

                                        cmdTraveller.ExecuteNonQuery();
                                    }
                                }
                            }
                        }
                    }
                }

                transaction.Commit();
                Console.WriteLine("All records inserted successfully.");
            }
            catch (Exception ex)
            {
                transaction.Rollback();
                Console.WriteLine("Error occurred: " + ex.Message);
            }
        }
    }
}