Serenity: How to used stored procedure for complex database logic

[laxmanp]

Hello sir,

I want to used stored procedure for complex database logic. How to call stored procedure by passing some parameters to stored procedure and get data in the form of list or table.

How to achieve it.

[volkanceylan]

See ADO.NET guides for this, Serenity has no special stored proc system.

[VictorTomaili]

Serenity include dapper already.

var p = new DynamicParameters();
p.Add("@a", 11);
p.Add("@b", dbType: DbType.Int32, direction: ParameterDirection.Output);
p.Add("@c", dbType: DbType.Int32, direction: ParameterDirection.ReturnValue);

cnn.Execute("spMagicProc", p, commandType: CommandType.StoredProcedure); 

int b = p.Get<int>("@b");
int c = p.Get<int>("@c"); 

[nmajeed]

so, is there a example to retrieve/update data using Stored Procedure...
also, which function is the best to overwrite to place code for Stored Procedure..

for example , should we change ListResponse function in xyzEndPoint.cs?

    public ListResponse<MyRow> List(IDbConnection connection, ListRequest request)
    {

        return new MyRepository().List(connection, request);
    }

[volkanceylan]

Remove this line

return new MyRepository().List(connection, request);

Call your stored proc like Victor suggests, than populate return ListResponse with results returned from your proc.

[nmajeed]

Thanks! - So, what SQL connection name (cnn) should be use or do we have to create our own.
I appreciate if you can provide a complete example.

[volkanceylan]

No i don't have an example

[VictorTomaili]

Its use defaultly MyRow Databases connectionstring. Its writed on MyRow as attribute. If you want to create new one try this
using (var customConnection = SqlConnections.NewByKey("ConnectionKey")){ ... }

[VictorTomaili]

Also this is a closed issue and this question has diffrent problem.

[nmajeed]

Thanks Victor & Volkan for your help. I got the list working .

public ListResponse List(IDbConnection connection, ListRequest request)
{
ListResponse x = new ListResponse();

        var customConnection = SqlConnections.NewByKey("RealWiz");
        x.Entities = (List<MyRow>)customConnection.Query<MyRow> ("uspEntity", null, commandType: CommandType.StoredProcedure);
        x.Skip = 0;
        x.Take = 0;
        x.TotalCount = x.Entities.Count;

        return x;

    }

[VictorTomaili]

I suggest use using(var customConnection = ....) for connection pool optimization.

[volkanceylan]

Why don't use use connection parameter passed to method, and create new one?

[nmajeed]

Thanks for pointing that out.. here is updated List method with paging (Skip & Take) . Still missing Sort functionality.

    public ListResponse<MyRow> List(IDbConnection connection, ListRequest request)
    {
        //return new MyRepository().List(connection, request);
        ListResponse<MyRow> x = new ListResponse<MyRow>();

        var p = new DynamicParameters();
        p.Add("@start", request.Skip);
        p.Add("@numberOfRows", request.Take);

        //get totlal records.
        var p1 = new DynamicParameters();
        p1.Add("@RecordCount", dbType: DbType.Int32, direction: ParameterDirection.Output);
        connection.Execute("uspTransCount", p1, commandType: CommandType.StoredProcedure);

        // get data
        x.Entities = (List<MyRow>)connection.Query<MyRow>("uspTrans", p, commandType: CommandType.StoredProcedure);
        x.Skip = request.Skip;
        x.Take = request.Take;
        x.TotalCount =  p1.Get<int>("@RecordCount");

        return x;
    }

[laxmanp]

Thanks it's working fine.

[ChenZhaobin]

learning more about serenity

[SimeonDD]

THANKS NMajeed!!!
Sharing a code in its entirety makes such a difference!

In so many other cases it almost seems that a person does not want to share the solution.
You sometimes get half a line or my favorite - an empty "Thanks to all, It is working now".

Thanks again.

To others who are fishing for syntax.
In NMajeed's code above:
1. The name of your SP has to be the same in both places:
- connection.Execute("YourSPname", p, ....
- connection.Query("YourSPname" p, ....
1. If you want to add an input parameter then it will look like this (spot the "value:" entry)
p.Add("@TestBit", value: 0, dbType: DbType.Int32, direction: ParameterDirection.Input);
Where @TestBit is spelled exactly like the input parameter in your SP.
In this demo case I am passing a hard-coded "0" but you can replace with whatever works for you.

[SimeonDD]

Adding to exactly the same code as shown by NMajeed.
If you have a case where you need to pass to the SP the current user credentials you could do it this way: