.Net How to use commandtext instead of calling Stored proceduce and store it in a list with IDbConnection

Question

I'm using stored procedures to get results from Sql database using IDbconnection. As an alternative of using stored procedures what are the other options to execute query inside the code?

If I use stored procedure, each machine must have it and in case the user deletes/ change the stored procedure the application will not run so what is the best way to execute SQL statement ( as text) ?

For example, I'm calling the stored procedure and then putting the result into a list.

 public List<BestMatchModel> GetBestMatchBooks() 
        List<BestMatchModel> output;
        using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(GlobalConfig.GetCnnstring()))
        {
            var p = new DynamicParameters();
            output = connection.Query<BestMatchModel>("dbo.spGetBestMatchBooks", p, commandType: CommandType.StoredProcedure).ToList();
        }
        return output;
    }

How can I use commandText for the above code?

This is the sql statement, which I have saved as stored procedure dbo.spGetBestMatchBooks.

select ba2.[BookID] as b2BookiD ,ba2.[Cupboard], bl2.[Rank] as b2Rank,ba.[BookID] as b1BookiD,ba.[Shelf],bl.[Rank] as b1Rank FROM [dbo].[Book_Arrange] ba,[dbo].[Book_Arrange] ba2, [dbo].[Book_List] bl, [dbo].[Book_List] bl2 WHERE ba.BookID = bl.BookID and ba2.BookID = bl2.BookID and ba.ArrangeDate IS NOT NULL and ba2.ArrangeDate IS NOT NULL and ba.[Shelf] IS NOT NULL and ba2.[Cupboard] IS NOT NULL and ba.BookID <> ba2.BookID and bl.[Selected] ='yes' and bl2.[Selected] ='yes' order by bl.[Rank],bl2.[Rank]

and I'm trying to use Idbcommand instead of the stored procedure.

public List<BestMatchModel> GetBestMatchBooks()
    {
        string commandText = "select ba2.[BookID] as b2BookiD ,ba2.[Cupboard], bl2.[Rank] as b2Rank" +
                              ", ba.[BookID] as b1BookiD,ba.[Shelf],bl.[Rank] as b1Rank" +
                              " FROM [dbo].[Book_Arrange] ba,[dbo].[Book_Arrange] ba2, [dbo].[Book_List] bl, [dbo].[Book_List] bl2 " +
                              " WHERE ba.BookID = bl.BookID and ba2.BookID = bl2.BookID " +
                              " and ba.ArrangeDate IS NOT NULL" +
                              " and ba2.ArrangeDate IS NOT NULL" +
                              " and ba.[Shelf] IS NOT NULL" +
                              " and ba2.[Cupboard] IS NOT NULL" +
                              " and ba.BookID<> ba2.BookID" +
                              " and bl.[Selected] = 'yes'" +
                              " and bl2.[Selected] = 'yes'" +
                              " order by bl.[Rank], bl2.[Rank]";
        List<BestMatchModel> output;
        using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(GlobalConfig.GetCnnstring()))
        {
            IDbCommand command = connection.CreateCommand();
            command.CommandText = commandText;
            var p = new DynamicParameters();
            output =  command.ExecuteScalar()<BestMatchModel>;
        }
        return output;
    }

But I cannot pass the result into the list.

cannot create an instance of the abstract class or interface 'IDbcommand'.

How to use Idbcommand using Idbconnection?

Thanks Alot,

AA