How can I code a switching scene by typing and searching for a room name?

Unity Engine
, , ,
1

I created a navmesh indoor wayfinding and a script for searching a room. This project is connected to SQLITE and I have different building and floors scene design. Each floors with room location is stored differently as a table in my database. For example, table B1_rooms_first_floor, table B1_rooms_second_floor, etc. I wanted to switch the scene by triggering the stair.
This is my code for handling Search for room.

public class NavmeshSearch : MonoBehaviour
{
    public InputField searchField;       
    public Button searchButton;          
    public CanvasGroup modalCanvasGroup; 
    public VideoPlayer videoPlayer;     

    private NavMeshAgent nma;
    private string connectionString;

    void Start()
    {

        connectionString = "URI=file:C:/Users/bausa/Documents/database/geographic.db";
        nma = GetComponent<NavMeshAgent>();


        modalCanvasGroup.alpha = 0;
        modalCanvasGroup.interactable = false;
        modalCanvasGroup.blocksRaycasts = false;

   
        searchButton.onClick.AddListener(SearchAndNavigate);
    }


    public void SearchAndNavigate()
    {
        string roomName = searchField.text;
        Vector3 targetPosition = GetLocationByName(roomName);

        if (targetPosition != Vector3.zero)
        {
            nma.SetDestination(targetPosition);
        }
        else
        {
            ShowErrorModal();
        }
    }

 
    private void ShowErrorModal()
    {

        StartCoroutine(FadeInAndPlayVideo());
    }

    private IEnumerator FadeInAndPlayVideo()
    {

        float fadeDuration = 1f; 
        float timeElapsed = 0f;


        modalCanvasGroup.alpha = 0;
        modalCanvasGroup.interactable = true;
        modalCanvasGroup.blocksRaycasts = true;

        while (timeElapsed < fadeDuration)
        {
            modalCanvasGroup.alpha = Mathf.Lerp(0, 1, timeElapsed / fadeDuration);
            timeElapsed += Time.deltaTime;
            yield return null;
        }
        modalCanvasGroup.alpha = 1;


        videoPlayer.Play();


        while (videoPlayer.isPlaying)
        {
            yield return null;
        }

        timeElapsed = 0f;
        while (timeElapsed < fadeDuration)
        {
            modalCanvasGroup.alpha = Mathf.Lerp(1, 0, timeElapsed / fadeDuration);
            timeElapsed += Time.deltaTime;
            yield return null;
        }

        modalCanvasGroup.alpha = 0;
        modalCanvasGroup.interactable = false;
        modalCanvasGroup.blocksRaycasts = false;
    }

    public Vector3 GetLocationByName(string objectName)
    {
        Vector3 position = Vector3.zero;
        string query = $"SELECT PositionX, PositionY, PositionZ FROM rooms WHERE room_name = @roomName";

        using (var conn = new SqliteConnection(connectionString))
        {
            try
            {
                conn.Open();
                UnityEngine.Debug.Log("Database connection established successfully!");

                using (var cmd = new SqliteCommand(query, conn))
                {
                    cmd.Parameters.AddWithValue("@roomName", objectName);

                    using (var reader = cmd.ExecuteReader())
                    {
                        if (reader.Read())
                        {
                            float x = reader.GetFloat(0);
                            float y = reader.GetFloat(1);
                            float z = reader.GetFloat(2);
                            position = new Vector3(x, y, z);
                            UnityEngine.Debug.Log($"Room '{objectName}' found at position: {position}");
                        }
                        else
                        {
                            UnityEngine.Debug.Log($"Room '{objectName}' not found in the database.");
                        }
                    }
                }
            }
            catch (System.Exception ex)
            {
                UnityEngine.Debug.Log("Database connection error: " + ex.Message);
            }
        }
        return position;
    }
}