Sending multiple parameters to stored procedure with jQuery Ajax

Question

I've got a web method that looks like:

[WebMethod]
        public void InsertDrugNameAndColorToDatabase(string drugName,string drugColor)
        {
            string cs = ConfigurationManager.ConnectionStrings["dbcs"].ConnectionString;
            using (var con = new SqlConnection(cs))
            {
                using (var cmd = new SqlCommand("spInsertDrugText", con))
                {
                    con.Open();
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@drugName", drugName);
                    cmd.Parameters.AddWithValue("@drugColor", drugColor);
                    cmd.ExecuteNonQuery();
                }
            }
        }

and a little JS:

<script type="text/javascript">
        $(document).ready(function () {
            $(".drugQuizzes").draggable({ tolerance: "fit" });
            $(".drugAnswers").droppable({
                drop: function (event, ui) {
                    var drugName = JSON.stringify({ "drugName": $(ui.draggable).find("span").text() });
                    var drugColor = JSON.stringify({ "drugColor": $(ui.draggable).css("background-color") });
                    console.log(drugColor);
                    console.log(drugName);
                    $.ajax({
                        type: "POST",
                        contentType: "application/json; charset=utf-8",
                        url: "SendDrugName.asmx/InsertDrugNameToDatabase",
                        data: drugName,
                        dataType: "json",
                        success: function (data) {
                            //response(data.d);
                        },
                        error: function (xhr, ajaxOptions, thrownError) {
                            console.log(xhr.status);
                            console.log(thrownError);
                        }
                    });
                }
            });
        });
    </script>
<script type="text/javascript">
        $(document).ready(function () {
            $(".drugQuizzes").draggable({ tolerance: "fit" });
            $(".drugAnswers").droppable({
                drop: function (event, ui) {
                    var drugName = JSON.stringify({ "drugName": $(ui.draggable).find("span").text() });
                    var drugColor = JSON.stringify({ "drugColor": $(ui.draggable).css("background-color") });
                    console.log(drugColor);
                    console.log(drugName);
                    $.ajax({
                        type: "POST",
                        contentType: "application/json; charset=utf-8",
                        url: "SendDrugName.asmx/InsertDrugNameToDatabase",
                        data: drugName,
                        dataType: "json",
                        success: function (data) {
                            //response(data.d);
                        },
                        error: function (xhr, ajaxOptions, thrownError) {
                            console.log(xhr.status);
                            console.log(thrownError);
                        }
                    });
                }
            });
        });
    </script>

I have a version of the stored procedure ans JS where only one parameter is sent to the stored procedure, and that works. From the console.log(drugName) and console.log(drugColor) I get

{"drugColor":"rgb(255, 69, 0)"} 
{"drugName":"ORACEA"}

How can I make the data parameter of the ajax call take multiple parameters at once? What are some names of general techniques that I need to be aware of for sending more than one parameter to a stored procedure at once using jQuery ajax?

Send the parameters back as properties of a single object, or as separate values in a single array. — user400654
– user400654, Commented Aug 20, 2013 at 15:34

Karl Anderson · Accepted Answer · 2013-08-20 15:52:34Z

Consider building an object literal on the client-side and passing that entire object to the service, thus you have one parameter in your service call, like this:

Client-side:

var myData = {};
myData.DrugName' = $(ui.draggable).find("span").text();
myData.DrugColor' = $(ui.draggable).css("background-color");

// Create a data transfer object (DTO) with the proper structure, which is what we will pass to the service.
var DTO = { 'theData' : myData };

$.ajax({
    type: "POST",
    contentType: "application/json; charset=utf-8",
    url: "SendDrugName.asmx/InsertDrugNameToDatabase",
    data: JSON.stringify(DTO),
    dataType: "json",
    success: function (data) {
        //response(data.d);
    },
    error: function (xhr, ajaxOptions, thrownError) {
        console.log(xhr.status);
        console.log(thrownError);
    }
});

Now on the service-side, you will need to build a class that represents the contents of the object literal created above, like this:

public class ServiceData
{
    public string DrugName { get; set; }
    public string DrugColor { get; set; }
}

Finally, change your web service code to accept one parameter, like this:

[WebMethod]
public void InsertDrugNameAndColorToDatabase(ServiceData theData)
{
    string cs = ConfigurationManager.ConnectionStrings["dbcs"].ConnectionString;
    using (var con = new SqlConnection(cs))
    {
        using (var cmd = new SqlCommand("spInsertDrugText", con))
        {
            con.Open();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@drugName", theData.DrugName);
            cmd.Parameters.AddWithValue("@drugColor", theData.DrugColor);
            cmd.ExecuteNonQuery();
        }
    }
}

Note: The data passed from the jQuery call is automatically matched up with the class properties you have in your ServiceData class as long as the names match on both the client-side and server-side.

Habib · Accepted Answer · 2013-08-20 15:35:33Z

1

You can specify multiple data items like:

data: 'drugName='+ drugName  + '&drugColor=' + drugColor;

answered Aug 20, 2013 at 15:35

Habib

224k30 gold badges420 silver badges446 bronze badges

4 Comments

Karl Anderson Over a year ago

This becomes unwieldy on both the service side and client-side, as the number of parameters grows.

wootscootinboogie Over a year ago

What techniques would be more extensible? Say, if there were 10 parameters to the stored procedure? Would you create an object like you could in C# with whatever properties? How would you send all of those properties of a particular object at the same time? (I do appreciate the quick and dirty, too, though).

Rory McCrossan Over a year ago

@wootscootinboogie check my answer - that's exactly what it does.

Karl Anderson Over a year ago

@RoryMcCrossan - your answer solves half the problem, sort of. You will still end up with a list of parameters on the service side, one for each value you are passing in your object literal.

Rory McCrossan · Accepted Answer · 2013-08-20 15:36:17Z

1

You don't need to stringify it at all, you can pass the parameters as an object. Try this:

$(".drugAnswers").droppable({
    drop: function (event, ui) {
        $.ajax({
            type: "POST",
            contentType: "application/json; charset=utf-8",
            url: "SendDrugName.asmx/InsertDrugNameToDatabase",
            data: {
                'drugName': $(ui.draggable).find("span").text(),
                'drugColor': $(ui.draggable).css("background-color")
            },
            dataType: "json",
            success: function (data) {
                //response(data.d);
            },
            error: function (xhr, ajaxOptions, thrownError) {
                console.log(xhr.status);
                console.log(thrownError);
            }
        });
    }
});

The ajax() function will then stringify it for you and pass it across to your C# endpoint.

answered Aug 20, 2013 at 15:36

Rory McCrossan

338k41 gold badges322 silver badges353 bronze badges

4 Comments

Karl Anderson Over a year ago

This becomes unwieldy on the service side, as the number of parameters grows.

user400654 Over a year ago

@KarlAnderson sounds like a code organization issue in the service to me.

Rory McCrossan Over a year ago

@KarlAnderson you could convert the action to take a Dictionary<string,string> which you then loop through to populate the SP paramaters. However, the OP was asking how he could send multiple params in the data attribute of his ajax call so I didn't include it in the answer. Feel free to add one yourself if you feel it beneficial to the OP.

wootscootinboogie Over a year ago

I'm not familiar enough with AJAX to know if this is going to work offhand, but it looks nice. +1 if it works after testing.

Collectives™ on Stack Overflow

Sending multiple parameters to stored procedure with jQuery Ajax

3 Answers 3

Comments

4 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

4 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related