Calling Store Proc with parameter.

Mohammad Salman asked on August 14, 2014 15:30

Hi there,

We are using Kentico 8.0. and trying to find some example How to pass parameters to stored procedure. Here is what I'm trying to do

1- I have a stored procdure

CREATE PROCEDURE Proc_Custom_GetAllUsers @UserId int AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON;

-- Insert statements for procedure here
SELECT *
FROM CMS_User
WHERE UserID = @UserId

END

2- I have created custom document type and in queries tab. I have placed the stored procedure name

3- Now I wanted to pass current logged in User ID as a parameter.

Can someone please point to some example to show me how can I pass parameter to stored procedure.

Thanks.

document type custom query

Correct Answer

Brenden Kehren answered on August 14, 2014 16:58

As Josh stated, use the current user object. MembershipContext.AuthenticatedUser.UserID

0 votesVote for this answer Unmark Correct answer

Recent Answers

Joshua Adams answered on August 14, 2014 16:05

Can you just use the kentico api to fetch the users? If not, you could always populate querydataparameters and set the userid parameter to currentuser.userid.

The new dataquery api may come in handy for this, plus you could cache the results, making less hits on the server and faster code.

0 votesVote for this answer Mark as a Correct answer

Mohammad Salman answered on August 14, 2014 16:40

You mean using SQLEvents ?

0 votesVote for this answer Mark as a Correct answer

Mohammad Salman answered on August 14, 2014 16:50

But there problem is How would I get the logged in user ID. Here is what I m trying to do.

[SPParamLoader] public partial class CMSModuleLoader { public class SPParamLoader:CMSLoaderAttribute

{

    public override void Init()
    {
        SqlEvents.ExecuteQuery.Before += ExecuteQuery_Before;

    }


    void ExecuteQuery_Before(object sender, ExecuteQueryEventArgs<System.Data.DataSet> e)
    {

        if (e.Query.Name != null)
        {


            switch (e.Query.Name.ToLowerCSafe())
            {
                case "Custom.ImmformAccessInfo.ImmformAccessInfoQuery":
                   QueryDataParameters param  = new QueryDataParameters() {
                       new DataParameter("@UserID", ??????? From where I would Get the ID )
                   }
                    break;

            }


        }

    }


}

}

0 votesVote for this answer Mark as a Correct answer

Mohammad Salman answered on August 15, 2014 11:22

Brenden thanks a ton. its all working now.

But now facing another problem, it seems like result is getting cached somewhere. even if log of and log back in with different user it shows me the last result ? Any idea

0 votesVote for this answer Mark as a Correct answer

Brenden Kehren answered on August 15, 2014 14:02

I'd suggest checking out the order in which actions happen in the global events. You might look at the different global system events there are and create some stub events and set break points for them to get a better picture of what's happening and when.

When is this particular query being called?

0 votesVote for this answer Mark as a Correct answer

Mohammad Salman answered on August 15, 2014 17:11

Thanks Brenden will try that.

0 votesVote for this answer Mark as a Correct answer