Visits management

The stored procedure that you can use to save data to the Visit table and link it with the visitors:

CREATE PROCEDURE SaveVisit
(
    @VisitDate DATE,
    @VisitStartTime TIME,
    @VisitEndTime TIME,
    @VisitPurpose NVARCHAR(100),
    @VisitLocation NVARCHAR(100),
    @VisitorData AS VisitorData READONLY
)
AS
BEGIN
    DECLARE @VisitId INT;

    -- Insert the visit data into the Visit table
    INSERT INTO Visit (VisitDate, VisitStartTime, VisitEndTime, VisitPurpose, VisitLocation)
    VALUES (@VisitDate, @VisitStartTime, @VisitEndTime, @VisitPurpose, @VisitLocation);

    -- Get the ID of the newly inserted visit
    SET @VisitId = SCOPE_IDENTITY();

    -- Insert the visitor data into the Visitor table and link it with the visit
    INSERT INTO Visitor (Nationality, NationalID, OrgName, Name, VisitId)
    SELECT Nationality, NationalID, OrgName, Name, @VisitId
    FROM @VisitorData;
END

This stored procedure takes in the visit data as parameters (@VisitDate, @VisitStartTime, @VisitEndTime, @VisitPurpose, and @VisitLocation), as well as a table-valued parameter @VisitorData that contains the visitor data. The VisitorData table type needs to be created first, like this:

CREATE TYPE VisitorData AS TABLE
(
    Nationality NVARCHAR(50),
    NationalID NVARCHAR(50),
    OrgName NVARCHAR(100),
    Name NVARCHAR(100)
)

To call the stored procedure, you can use something like this in your application code:

// Assuming you have a DataTable called "visitors" that contains the visitor data
using (SqlConnection conn = new SqlConnection(connectionString))
{
    conn.Open();

    using (SqlCommand cmd = new SqlCommand("SaveVisit", conn))
    {
        cmd.CommandType = CommandType.StoredProcedure;

        cmd.Parameters.AddWithValue("@VisitDate", visitDate);
        cmd.Parameters.AddWithValue("@VisitStartTime", visitStartTime);
        cmd.Parameters.AddWithValue("@VisitEndTime", visitEndTime);
        cmd.Parameters.AddWithValue("@VisitPurpose", visitPurpose);
        cmd.Parameters.AddWithValue("@VisitLocation", visitLocation);

        SqlParameter visitorDataParam = cmd.Parameters.AddWithValue("@VisitorData", visitors);
        visitorDataParam.SqlDbType = SqlDbType.Structured;
        visitorDataParam.TypeName = "VisitorData";

        cmd.ExecuteNonQuery();
    }
}

This assumes that you have a connection string stored in a variable called connectionString, and that you have variables for the visit data (visitDate, visitStartTime, visitEndTime, visitPurpose, and visitLocation) that you're passing to the stored procedure.