A better way to obtain SQL data than I am currently using

I need to retrieve data from an external SQL Server database and view it (not store it) in my ASP.NET MVC application. The only way I can connect to the server is by using a server name, port number and access to a SQL Server stored procedure that the server owners provide.

Currently the only way I know how to do this is by:

a) Writing a .sql script to retrieve the data. Note I can’t see any of the SQL Server tables, I just have the name of the stored procedure and the criteria. I save the result as a .txt file

EXEC dbo.listData @Criteria = '<Portal><Data Name="Data" Format="Narrow" Interval="5m">

<Where>
<Column Name="Point" Project="XXX" Value="XXXX" Operator="LIKE"  />
<Column Name="Point" Project="YYY" Value="YYYY" Operator="LIKE"  />
</Where>
</Data>
</Portal>'

, @StartDateTime = '12/28/2020', 
  @EndDateTime = '12/29/2020'  

b) creating a model class

public class Alarm_DataModel
{       
    public string Project { get; set; }
    public string Point { get; set; }
}

c) Creating a controller to put the data into the model to pass to the view

public ActionResult Index()
{
    string[] texts = System.IO.File.ReadAllLines(Server.MapPath("~/App_Data/Test/test.txt"));
    texts = texts.Skip(2).ToArray();

    List<Alarm_DataModel> Alarm_Data = new List<Alarm_DataModel>();

    foreach (string row in texts)
    {
        if (!string.IsNullOrEmpty(row))
        {
            int x = row.Length;

            Alarm_Data.Add(new Alarm_DataModel
                    {
                        Project = row.Substring(0, 25),
                        Point = row.Substring(26, 60), 6

                    });
        }
    }

    ViewBag.Data = texts;
    return View(Alarm_Data);
}

My question may have been answered many times, but I have looked and can’t find anything that I can interpret.

Is there a way that I can obtain the data using my controller without having to rely on the .sql script being ran and generating the .txt file?

With my limited access to the database, what is the best way to query using the provided stored procedure and populating my model to pass to the view?

Answer

Thank you Frank Nielsen, your recommended code worked with just some minor edits with the criteria values. For some reason I needed to include them in the query for it to work.

Much appreciated, thanks

Here is the final code:

 public class AlarmDataController : Controller
{
    // GET: AlarmData
    private IEnumerable<Alarm_DataModel> GetAlarmList()
    {
        var sql = @"EXEC dbo.listData @Criteria = '<Portal><Data Name=""Data"" Format=""Narrow"" Interval=""5m"">
                    <Where>
                    <Column Name=""Point"" Project=""XX"" Value=""XXXX"" Operator=""LIKE""  />
                    <Column Name=""Point"" Project=""YY"" Value=""YYYY"" Operator=""LIKE""  />
                    </Where>
                    </Data>
                    </Portal>', @StartDateTime = '12/28/2020',@EndDateTime = '12/29/2020'";

        string connectionString = "Integrated Security=True;Data Source=XXXX;Timeout=30;Encrypt=False;TrustServerCertificate=False;ApplicationIntent=ReadWrite;MultiSubnetFailover=False";


            using (var connection = new SqlConnection(connectionString))
            {
               
                return connection.Query<Alarm_DataModel>(sql).ToList();
            }
    }

    [HttpGet]
    public ActionResult Index()
    {
        var alarmList = GetAlarmList();

        return View(alarmList);
    }
}