Connect Azure MI SQL View to MVC app as read only code first

Question

One aspect of an ASP.net core (6) MVC app I am working on needs to query an SQL View that already resides in an Azure SQL MI.

I need to be able to query this SQL View to be able to retrieve the data based on user input but with the following conditions.

• I cannot use Entity Framework.
• The connection has to be read only.
• This has to be database first.

As of yet I do not have access to this View or any of the tables it draws from. However I am expected to have code ready to plug a connection string into.

Unfortunately any resources I have been able to find don't seem to apply to my specific conditions. So any advice in what direction or approach would work best would be appreciated.

Answer 1

Those are by no means "silly" conditions. You didn't specify the language or the database but I'll make assumptions

I cannot use Entity Framework

Just use standard ado.net https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/ado-net-code-examples#sqlclient

(I know that Link only answers are frowned upon)

The connection has to be read only.

Ensure that the account you connect under is read only. In SQL Server this is achieved by making you a member of the db_datareader group. This is something that should be enforced by the DBA that gives you an account

This has to be database first.

That's not really relevant. Just use the linked sample code to read from the existing view.

Literal copy paste of code at the link above:

using System;
using System.Data;
using System.Data.SqlClient;

class Program
{
    static void Main()
    {
        string connectionString =
            "Data Source=(local);Initial Catalog=Northwind;"
            + "Integrated Security=true";

        // Provide the query string with a parameter placeholder.
        string queryString =
            "SELECT ProductID, UnitPrice, ProductName from dbo.products "
                + "WHERE UnitPrice > @pricePoint "
                + "ORDER BY UnitPrice DESC;";

        // Specify the parameter value.
        int paramValue = 5;

        // Create and open the connection in a using block. This
        // ensures that all resources will be closed and disposed
        // when the code exits.
        using (SqlConnection connection =
            new SqlConnection(connectionString))
        {
            // Create the Command and Parameter objects.
            SqlCommand command = new SqlCommand(queryString, connection);
            command.Parameters.AddWithValue("@pricePoint", paramValue);

            // Open the connection in a try/catch block.
            // Create and execute the DataReader, writing the result
            // set to the console window.
            try
            {
                connection.Open();
                SqlDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    Console.WriteLine("\t{0}\t{1}\t{2}",
                        reader[0], reader[1], reader[2]);
                }
                reader.Close();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            Console.ReadLine();
        }
    }