0

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

John Woo
  • 258,903
  • 69
  • 498
  • 492
user3094480
  • 43
  • 1
  • 8
  • You are using Dapper. *Why* do you want to go back to raw ADO.NET? You just saw that it makes your code a lot uglier. – Panagiotis Kanavos Feb 01 '18 at 10:15
  • This sounds like [an XY Problem](https://meta.stackexchange.com/questions/66377/what-is-the-xy-problem). You have a problem with X, think Y is the answer and when that fails you ask for Y instead of X. What are you trying to do? You don't need all that code to execute a stored procedure with Dapper, or to pass parameters to it. A simple `.Query("dbo.spGetBestMatchBooks",new {myParam=5}, commandType: CommandType.StoredProcedure)` will work – Panagiotis Kanavos Feb 01 '18 at 10:18
  • Yes, I’m using Dapper but dont want to use store procedure in dapper. Because I have create or make sure all store procedures are in the database. – user3094480 Feb 01 '18 at 11:40
  • Is it possible to embed query statement inside the code? Like select * from dbo. Employees rather than calling a dbo.spEmployees stored procedure? – user3094480 Feb 01 '18 at 11:41
  • That's actually the most common usage and [the very first example](https://github.com/StackExchange/Dapper#execute-a-query-and-map-the-results-to-a-strongly-typed-list) in the docs: `connection.Query("select Age = @Age, Id = @Id", new { Age = (int?)null, Id = guid });`. Again *WHY*? There's nothing wrong with using a stored procedure, especially when you have a big query. You can use a view to simplify the query, or pass parameters to the stored procedure so you don't have to write 12 lines of SQL inside C# – Panagiotis Kanavos Feb 01 '18 at 11:55
  • Thank you. I will read more on Dapper and try the best method. – user3094480 Feb 01 '18 at 12:09
  • Here is what I don't get in your question. You claim "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". However your last code sample indicates you are using SQL Server. So unless you are using it as an embeded database, then all users are accessing the same database on **your** server, not everyone to a database on their machine. – Zohar Peled Feb 23 '18 at 07:42

0 Answers0