Insert JavaScript array into database table

Question

The following script creates a Person object and inserts those values into a SQL Server database

 $(document).ready(function ()
        {
            var person = {};
            person.FirstName = "Bill";
            person.LastName = "Wilson";

            $('#button').click(function ()
            {
                var sender = JSON.stringify({ 'p': person });
                $.ajax(
                {
                    type: "POST",
                    url: "GetDrugQuiz.asmx/InsertPerson",
                    contentType: "application/json; charset=utf-8",
                    dataType: "json",
                    data: sender,
                    success: function (data)
                    {
                        console.log(data.d);
                    },
                    error: function (xhr, ajaxOptions, thrownError)
                    {
                        console.log(xhr.status);
                        console.log(thrownError);
                    }
                });
            });

        });

-

  [WebMethod]
            public void InsertPerson(Person p)
            {
                //var jss = new JavaScriptSerializer();

                string cs = ConfigurationManager.ConnectionStrings["dbcs"].ConnectionString;
                using (var con = new SqlConnection(cs))
                {
                    using (var cmd = new SqlCommand("spInsertNames", con))
                    {
                        con.Open();
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.Parameters.AddWithValue("@firstName",p.FirstName);
                        cmd.Parameters.AddWithValue("@lastName", p.LastName);
                        cmd.ExecuteNonQuery();
                    }
                }
            }

This works fine if I have only one person to insert. But let's say the user had the ability to create several Person objects and I would like to insert them all at once. What is the best method for doing this? I tried:

 $(document).ready(function ()
        {
            var person = {};
            person.FirstName = "Bill";
            person.LastName = "Wilson";

            personArray = new Array();
            var person2 = {};
            person2.FirstName = "Tim";
            person2.LastName = "Thompson";
            personArray.push(person);
            personArray.push(person2);
            var dto = { 'personList': personArray }

            $('#button').click(function ()
            {
                //this is printing to the console correctly
                console.log(dto);
                $.ajax(
                {
                    type: "POST",
                    url: "GetDrugQuiz.asmx/InsertPersonList",
                    dataType: "json",
                    data: JSON.stringify(dto),
                    success: function (data)
                    {
                        alert('success!');
                        alert(data.d);
                    },
                    error: function (xhr, ajaxOptions, thrownError)
                    {
                        alert(xhr.status);
                        alert(thrownError);
                    }
                });
            });

        });

with

[WebMethod]
        public void InsertPersonList(List<Person> personList)
        {
            string cs = ConfigurationManager.ConnectionStrings["dbcs"].ConnectionString;
            //var jss = new JavaScriptSerializer();
            //I know at some point the JSON will have to be deserialized
            foreach (var person in personList)
            {
                using (var con = new SqlConnection(cs))
                {
                    using (var cmd = new SqlCommand("spInsertNames",con))
                    {
                        con.Open();
                        cmd.Parameters.AddWithValue("@firstName", person.FirstName);
                        cmd.Parameters.AddWithValue("@lastName", person.LastName);
                        cmd.ExecuteNonQuery();
                    }
                }
            }
        }

This fails with the error System.InvalidOperationException: InsertPersonList Web Service method name is not valid. at System.Web.Services.Protocols.HttpServerProtocol.Initialize() at System.Web.Services.Protocols.ServerProtocolFactory.Create(Type type, HttpContext context, HttpRequest request, HttpResponse response, Boolean& abortProcessing)

I know that even if what I tried were possible it wouldn't be a good idea. I also know that somehow I'll have to deserialize the JS array to a list of Person objects. What's the best way to go about this? Currently my stored procedure for inserting into the Names table is

create proc spInsertNames
 @firstName varchar(50)
,@lastName varchar(50)
as
begin
insert into Names(FirstName, LastName)
values (@firstName,@lastName)
end

(which is why I tried the foreach loop since the values are inserted with a row constructor). It seems like I should probably use a DataTable to select into a table variable in SQL. Am I anywhere near the mark?

Aaron Bertrand · Accepted Answer · 2013-08-28 20:09:31Z

2

You can use a DataTable to insert your set into a table-valued parameter (which is not the same as a table variable).

CREATE TYPE dbo.NameList AS TABLE
(
  FirstName NVARCHAR(255), 
  LastName  NVARCHAR(255)
);

Then your procedure:

CREATE PROCEDURE dbo.InsertNames_ByTVP
  @Names AS dbo.NameList READONLY
AS
BEGIN
  SET NOCOUNT ON;

  INSERT dbo.Names(FirstName, LastName)
    SELECT FirstName, LastName FROM @Names;
END
GO

Your C# code would simply pass the DataTable:

SqlCommand cmd = new SqlCommand("dbo.InsertNames_ByTVP", connection_object);
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter names = cmd.Parameters.AddWithValue("@Names", DataTableName);
names.SqlDbType = SqlDbType.Structured;
cmd.ExecuteNonQuery();

edited Aug 28, 2013 at 20:09

answered Aug 28, 2013 at 20:03

Aaron Bertrand

282k37 gold badges469 silver badges468 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user645280 Over a year ago

How does this get a serializable list of data from .click() to InsertPersonList()? (Or are you talking about passing System.Data.DataTable which seems almost totally unrelated to everything you posted.)

Aaron Bertrand Over a year ago

@ebyrob that isn't the part of the problem I was addressing. I was merely showing how the OP could pass a DataTable into a stored procedure (note that in the question they suggest that they can get a DataTable no problem, just don't know what to do next). If you want to address the .click() etc. part of the problem (which I'm not convinced is part of the problem), go ahead and post your own answer please.

Collectives™ on Stack Overflow

Insert JavaScript array into database table

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related