Forums (Obsolete)


Member	nanite2000 - 4/16/2013 1:28:35 AM Pass Paramters to Stored Procedure Hi, I'm using Kentico v7.0.26. I have a repeater which I am using to call a stored procedure (added through Document Types -> [Document] -> Queries). How can I pass parameters to this Stored Procedure from within a standard Repeater Web Part (e.g. from a K# Macro)? Thanks!

Member

nanite2000 - 4/18/2013 6:59:53 PM

RE:Pass Paramters to Stored Procedure

For anyone wondering, it is not possible to do this in the Portal engine. You can only do this using the API.

Use this code sample to get the results of a Stored Procedure into a DataSet (see: http://devnet.kentico.com/docs/devguide/index.html?data_layer_code_examples.htm):

QueryDataParameters parameters = new QueryDataParameters();
parameters.Add("@Parameter1", CMSContext.CurrentDocument.NodeID);
parameters.Add("@SecondParameter", 5);

// Execute stored procedure
GeneralConnection gn = ConnectionHelper.GetConnection();
DataSet ds = gn.ExecuteQuery("HeinekenLive_RelatedContent", 
parameters, QueryTypeEnum.StoredProcedure, false);

Ignore the advice given here and here - it doesn't work.

Also, it won't work if assign this to a CMSRepeater web part's DataSource - the Repeater will never render. I don't know why (and neither does Support).

If you specifically want to use it with a repeater, you have to clone the Repeater with Query Web Part. It's repeater has a 'QueryParameters' property, to which you can assign the QueryDataParameters object above. Apparently this will work (although I haven't tried it).

If you have a different scenario, you're on your own.

Member

matt-awg - 5/15/2013 1:10:10 PM

RE:Pass Paramters to Stored Procedure

Can someone that works for Kentico please comment on this thread and confirm that nanite2000's comment above is correct and that this:

http://devnet.kentico.com/Forums.aspx?forumid=45&threadid=22327

does not actually work in version 7? I was following that post and created a custom clone of the SQLDataSource web part that set the SQLDataSource.QueryParameters to a custom QueryParameters object I create by passing in data through a custom property I added to my custom web part. I did some debugging and I proved that it was getting the data and building the QueryParameters object with the correct data and setting the SQLDataSource.QueryParameters to my custom QueryParameters object with no errors, but it always returns no data! I call the same stored procedure in SQL Management Studio with the same parameter values and it works fine. Any ideas?

(also, on an unrelated note, I suggest you guys make a change to your forum so that it does not throw an error when I post a reply and check the "subscribe to post" option when I am already subscribed. Not only is it annoying (it should just work and leave me subscribed) but that error message ("Given email address is already subscribed to this forum.") appears in small black font and I did not even see it. I reposted two times before I finally noticed that error message and understood why it didn't accept my post.)

Thanks,
Matt

Kentico Legend

Accepted solution

Brenden Kehren - 5/15/2013 3:29:18 PM

RE:Pass Paramters to Stored Procedure

I'm not from Kentico but know quite a bit about the API and webparts. The example your post refers to and the previous posts refer to are for v5.x so there is a good chance they will not work. I created a simple stored procedure

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

And in the web part I added 2 simple lines of code

QueryDataParameters parms = new QueryDataParameters() { new DataParameter("@UserID", 67) };
srcSQL.QueryParameters = parms;

And the data source returned the user infor for UserID 67 and displayed it in the basic repeater I had on the page. In the web part properties, make sure you are selecting Stored Procedure vs. Text. Also make sure in the code behind you are using the right object to assign to the QueryParameters property.

Member

matt-awg - 5/16/2013 1:43:17 PM

RE:Pass Paramters to Stored Procedure

Thanks for the help Froggeye but that is exactly what I am doing. You are doing this inside this method right?:

protected void SetupControl()

first I create a new param object

QueryDataParameters prmsSP = new QueryDataParameters();

then I loop through all my params and add them like this:

prmsSP.Add(spNameValueAry[0], spNameValueAry[1]);

and then exactly like your example I set this:

srcSQL.QueryParameters = prmsSP;

I put event log outputs at each step of the way and all my code is being executed and setting the right names and values for each parameter, and it doesn't work. I get a result set when I use the same values to call the same stored procedure in SQL management studio.

Oh and yes I also am setting it to stored procedure not text in the web part settings for my custom web part.

What are you putting into the "Query text" on your custom data source web part?

I am leaving out the connection string and server name which should connect it to the kentico cms database by default according to the documentation. Is that not right?

Maybe its something on my repeater? I set the "Data source name" on the repeater to be the "Web part control ID" of my custom data source. Is there anything else special I need to do that I am missing here?

Some of my parameters are INT but I am passing them in as string so I even added logic to convert to the correct data type before adding them to the param collection. I tried restarting the app and clearing the cache just to make sure, and still "no data found" in my repeater. I am not sure what I am missing here... any idea?

Thanks again,
Matt

Kentico Legend

Brenden Kehren - 5/16/2013 2:08:24 PM

RE:Pass Paramters to Stored Procedure

Feel free to shoot me an email and I can send you the webpart I customized bkehren at gmail dot com.

If not, here is the code for the property I created:

/// <summary>
    /// Gets or sets Query Data Parameters
    /// </summary>
    public QueryDataParameters QueryParameters
    {
        get
        {
            string paramString = ValidationHelper.GetString(GetValue("QueryParameters"), null);
            if (!string.IsNullOrEmpty(paramString))
            {
                // split the string and put into a dictionary
                var keyValuePairs = paramString.Split(new string[] { System.Environment.NewLine }, StringSplitOptions.RemoveEmptyEntries)
                    .Select(x => x.Split(';'))
                    .Where(x => x.Length == 2)
                    .ToDictionary(x => x.First(), x => x.Last());

                QueryDataParameters parameters = new QueryDataParameters();

                // iterate dictionary items into kvp and add them to parameter collection
                foreach (KeyValuePair<string, string> kvp in keyValuePairs)
                {
                    parameters.Add(kvp.Key, kvp.Value);
                }
                return parameters;
            }
            else
            {
                return null;
            }
        }
        set
        {
            SetValue("QueryParameters", value);
            srcSQL.QueryParameters = value;
        }
    }

Then assign it in the SetupControl() method

            if (QueryParameters != null)
            {
                srcSQL.QueryParameters = QueryParameters;
            }

Make sure you have that property added in the Site Manager>Development>Web Parts.

Then in the query text field you can simply set a query or put in the name of the stored procedure.

SELECT * 
FROM CMS_User
WHERE UserID = @UserID OR UserName = @UserName

When you enter your stored procedure name, don't enter anything else, just the name: Proc_GetUserByIdOrName. For either example simply enter your parameters like so:

@UserID;53
@UserName;andy

Member

matt-awg - 5/18/2013 12:32:51 PM

RE:Pass Paramters to Stored Procedure

In case anyone else is having this issue, the answer turned out to be that you apparently can't use a "repeater" web part for this, you have to use a "basic repeater". So it turned out my code was fine all along and that I was just using the wrong repeater. I did not see any error messages, just the no data was found message on the repeater. As soon as I changed it to a basic repeater it worked.

THANKS to FroggEye for helping me figure it out!!!! And CONGRATS to him for becoming a certified Kentico developer!

Thanks again for taking the time FroggEye! It was very much appreciated.

-Matt