c# - Insert JavaScript array into database table -

the following script creates person object , inserts values 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 have 1 person insert. let's user had ability create several person objects , insert them @ once. best method doing this? 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 printing 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 @ point json have 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 error system.invalidoperationexception: insertpersonlist web service method name not valid. @ system.web.services.protocols.httpserverprotocol.initialize() @ system.web.services.protocols.serverprotocolfactory.create(type type, httpcontext context, httprequest request, httpresponse response, boolean& abortprocessing)

i know if tried possible wouldn't idea. know somehow i'll have deserialize js array list of person objects. what's best way go this? stored procedure inserting names table

create proc spinsertnames  @firstname varchar(50) ,@lastname varchar(50) begin insert names(firstname, lastname) values (@firstname,@lastname) end 

(which why tried foreach loop since values inserted row constructor). seems should use datatable select table variable in sql. anywhere near mark?

you can use datatable insert set table-valued parameter (which not same table variable).

create type dbo.namelist table (   firstname nvarchar(255),    lastname  nvarchar(255) ); 

then procedure:

create procedure dbo.insertnames_bytvp   @names dbo.namelist readonly begin   set nocount on;    insert dbo.names(firstname, lastname)     select firstname, lastname @names; end go 

your c# code pass 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();