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?
Advertisement
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); } }