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?

Answer 1

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();