Fetching MSSQL data on to the itwin widget

[Zubair-KPM]

Hi,

the above image is showing the table containing 4 columns, I want that the column should have the database data inside .

// Init NodeModules and Express server
const express = require('express');
const app = express(); // Use app.listen() instead of var express
const sql = require('mssql');

// Define configuration
const config = {
  user: '', // Update me
  password: '', // Update me
  server: '', // Update me
  database: '', // Update me
  options: {
    trustServerCertificate: true, // Allow using self-signed certificates
  },
};


// Function to execute SQL query
async function getDataFromDatabase(query) {
  try {
    await sql.connect(config);
    console.log("Connected to the database");

    const result = await sql.query(query);
    console.log("Query executed successfully");

    // Check if any records were returned
    if (result.recordset.length > 0) {
      console.log("Query results:");
      console.table(result.recordset); // Display query results
  } else {
      console.log("No records found.");
  }
  
    return result.recordset; // Return the query result
  } catch (err) {
    if (err.code === 'ESOCKET' && err.message.includes('unable to get local issuer certificate')) {
      console.error("Error: Unable to establish a secure connection. This may be due to a missing or invalid SSL/TLS certificate.");
      throw err; // Re-throw the error to be handled by the caller
    } else {
      console.error("Error:", err.message);
      throw err; // Re-throw the error to be handled by the caller
    }
  } finally {
    await sql.close(); // Close the connection
    console.log("Connection closed");
  }
}

const PORT = 1433;

// Setting up the server
app.listen(PORT, function () {
  console.log('Server running at port ' + PORT + ': http://127.0.0.1:' + PORT);
});

// GET request handler
app.get("/", async function (req, res) {
  try {
    const query = "SELECT * FROM tablename"; // Sample SQL query
    const result = await getDataFromDatabase(query);
    res.json(result); // Send the query result as JSON response
  } catch (error) {
    res.status(500).send("Error executing SQL query: " + error.message);
  }
});

The above code is used to test or to run the database data in the node js.

import React, { useEffect, useState } from "react";

export function DatabaseListWidgetComponent() {
  const [smartTableList, setSmartTableList] = useState([]);

  useEffect(() => {
    async function fetchData() {
      try {
        const response = await fetch('http://127.0.0.1:1433'); // Assuming your Express server is running on localhost:1433
        if (!response.ok) {
          throw new Error('Failed to fetch data');
        }
        const data = await response.json();
        setSmartTableList(data.map((value: any) => (
          <tr key={value.EmployeeID}>
            <td>{value.EmployeeID}</td>
            <td>{value.LogDate}</td>
            <td>{value.LogTime}</td>
            <td>{value.BranchName}</td>
          </tr>
        )));
      } catch (error) {
        console.error('Error fetching data:', (error as Error).message);
      }
    }

    fetchData();
  }, []);
  
  return (
    <table className="smart-table">
      <thead>
        <tr>
          <th>EmployeeID</th>
          <th>LogDate</th>
          <th>LogTime</th>
          <th>BranchName</th>
        </tr>
      </thead>
      <tbody>
        {smartTableList}
      </tbody>
    </table>
  );
}

In this code we r using all the logic and columns.

import React from "react"; // Import React if not already imported
import { Widget, StagePanelLocation, StagePanelSection, WidgetState, UiItemsProvider } from "@itwin/appui-react";
import { DatabaseListWidgetComponent } from "../database/databaseListWidgetComponents";

export class dataListWidget implements UiItemsProvider {
    public readonly id = "dataListWidget";

    public provideWidgets(_stageId: string, _stageUsage: string, location: StagePanelLocation, _section?: StagePanelSection): ReadonlyArray<Widget> {
        const widgets: Widget[] = [];
    
        if (location === StagePanelLocation.Bottom && _section === StagePanelSection.Start) {
          widgets.push(
            {
              id: "dataListWidget",
              label: "Database Data",
              defaultState: WidgetState.Open,
              content: <DatabaseListWidgetComponent></DatabaseListWidgetComponent>
            });
        }
        return widgets;
      }
    }

Here the WIDGET code where will show all the data .

The Fetching of the data is not working below the column as per the column.

Please any reponse will appreciated!