json - Timeout expired VB WebService Method Error -
i'm using method shows database content json arrays. times works it's giving me error lot of times:
timeout expired. timeout period elapsed prior obtaining connection pool. may have occurred because pooled connections in use , max pool size reached
by way other methods works 1 doing this
this webmethod code:
<webmethod> public sub afterordersinsert(jsonstring [string]) try dim context httpcontext = me.context dim strjson string = "" dim orderitems jarray = jarray.parse(jsonstring) 'dim incrementalconnection new sqlconnection() dim conn new sqlconnection(configurationmanager.connectionstrings("mycstring").connectionstring) each orderitem jobject in orderitems conn.open() dim selectquery string = "select * orders orderid = @a , name = @b" dim selectcommand new sqlcommand(selectquery, conn) selectcommand.parameters.addwithvalue("@a", orderitem.item("orderid").tostring()) selectcommand.parameters.addwithvalue("@b", orderitem.item("name").tostring()) dim reader = selectcommand.executereader() dim isexist = reader.hasrows dim orderquantity integer = 0 if isexist reader.read() orderquantity = convert.toint32(reader.getstring(reader.getordinal("productquantity"))) + convert.toint32(orderitem.item("productquantity").tostring()) end if conn.close() 'using reader = selectcommand.executereader() if isexist 'if reader.read() conn.open() dim price = orderquantity * convert.todouble(orderitem.item("price").tostring()) dim updatequery string = "update orders set productquantity = @c, price = @d orderid = @e , name = @f" dim updatecommand new sqlcommand(updatequery, conn) updatecommand.parameters.addwithvalue("@c", orderquantity.tostring()) updatecommand.parameters.addwithvalue("@d", price.tostring()) updatecommand.parameters.addwithvalue("@e", orderitem.item("orderid").tostring()) updatecommand.parameters.addwithvalue("@f", orderitem.item("name").tostring()) updatecommand.executenonquery() conn.close() else dim strsql string = "insert orders(productnumber,name,category,price,orderid,tablenumber,waitername,cashiername,dateandtime,isactive,iscoocked,ispaid,isyescanceled,productquantity) values(@x,@y,@a,@b,@c,@d,@e,@f,@g,@h,@i,@j,@k,@l)" conn.open() dim cmd new sqlcommand(strsql, conn) cmd.parameters.addwithvalue("@x", orderitem.item("productnumber").tostring()) cmd.parameters.addwithvalue("@y", orderitem.item("name").tostring()) cmd.parameters.addwithvalue("@a", orderitem.item("category").tostring()) cmd.parameters.addwithvalue("@b", convert.todouble(orderitem.item("price").tostring()) * convert.toint32(orderitem.item("productquantity").tostring())) cmd.parameters.addwithvalue("@c", orderitem.item("orderid").tostring()) cmd.parameters.addwithvalue("@d", orderitem.item("tablenumber").tostring()) cmd.parameters.addwithvalue("@e", orderitem.item("waitername").tostring()) cmd.parameters.addwithvalue("@f", orderitem.item("cashiername").tostring()) cmd.parameters.addwithvalue("@g", orderitem.item("dateandtime").tostring()) cmd.parameters.addwithvalue("@h", orderitem.item("isactive").tostring()) cmd.parameters.addwithvalue("@i", orderitem.item("iscoocked").tostring()) cmd.parameters.addwithvalue("@j", orderitem.item("ispaid").tostring()) cmd.parameters.addwithvalue("@k", orderitem.item("isyescanceled").tostring()) cmd.parameters.addwithvalue("@l", orderitem.item("productquantity").tostring()) cmd.executenonquery() conn.close() end if 'end using next 'productnumber [string], name [string], category [string], price [string], orderid [string]' 'this bridge database\ strjson = "{" + "" + "contact" + "" + ":" + "[" + "{" + "result" + "" + ":" + serializer.serialize("1") + "}" + "]" + "}" context.response.write(strjson) catch ex exception dim strjson string strjson = "{" + "" + "contact" + "" + ":" + "[" + "{" + "result" + "" + ":" + serializer.serialize("0") + ex.message + "}" + "]" + "}" context.response.write(strjson) end try end sub
your problems are:
- you not disposing of connections (you not closing them in cases). should wrap code
usingblocks know connections , other resources (e.g. command) disposed. - your repeatedly opening , closing connection in loop:
for each orderitem jobject in orderitems conn.open()
change code to:
<webmethod> public sub afterordersinsert(jsonstring [string]) try dim context httpcontext = me.context dim strjson string = "" dim orderitems jarray = jarray.parse(jsonstring) 'dim incrementalconnection new sqlconnection() using conn new sqlconnection(configurationmanager.connectionstrings("mycstring").connectionstring) conn.open() ' don't repeatedly open & close connection. each orderitem jobject in orderitems dim orderquantity integer = 0 dim isexist boolean = false dim selectquery string = "select * orders orderid = @a , name = @b" using selectcommand new sqlcommand(selectquery, conn) selectcommand.parameters.addwithvalue("@a", orderitem.item("orderid").tostring()) selectcommand.parameters.addwithvalue("@b", orderitem.item("name").tostring()) using reader = selectcommand.executereader() isexist = reader.hasrows if isexist reader.read() orderquantity = convert.toint32(reader.getstring(reader.getordinal("productquantity"))) + convert.toint32(orderitem.item("productquantity").tostring()) end if end using end using if isexist dim price = orderquantity * convert.todouble(orderitem.item("price").tostring()) dim updatequery string = "update orders set productquantity = @c, price = @d orderid = @e , name = @f" using updatecommand new sqlcommand(updatequery, conn) updatecommand.parameters.addwithvalue("@c", orderquantity.tostring()) updatecommand.parameters.addwithvalue("@d", price.tostring()) updatecommand.parameters.addwithvalue("@e", orderitem.item("orderid").tostring()) updatecommand.parameters.addwithvalue("@f", orderitem.item("name").tostring()) updatecommand.executenonquery() end using else dim strsql string = "insert orders(productnumber,name,category,price,orderid,tablenumber,waitername,cashiername,dateandtime,isactive,iscoocked,ispaid,isyescanceled,productquantity) values(@x,@y,@a,@b,@c,@d,@e,@f,@g,@h,@i,@j,@k,@l)" using cmd new sqlcommand(strsql, conn) cmd.parameters.addwithvalue("@x", orderitem.item("productnumber").tostring()) cmd.parameters.addwithvalue("@y", orderitem.item("name").tostring()) cmd.parameters.addwithvalue("@a", orderitem.item("category").tostring()) cmd.parameters.addwithvalue("@b", convert.todouble(orderitem.item("price").tostring()) * convert.toint32(orderitem.item("productquantity").tostring())) cmd.parameters.addwithvalue("@c", orderitem.item("orderid").tostring()) cmd.parameters.addwithvalue("@d", orderitem.item("tablenumber").tostring()) cmd.parameters.addwithvalue("@e", orderitem.item("waitername").tostring()) cmd.parameters.addwithvalue("@f", orderitem.item("cashiername").tostring()) cmd.parameters.addwithvalue("@g", orderitem.item("dateandtime").tostring()) cmd.parameters.addwithvalue("@h", orderitem.item("isactive").tostring()) cmd.parameters.addwithvalue("@i", orderitem.item("iscoocked").tostring()) cmd.parameters.addwithvalue("@j", orderitem.item("ispaid").tostring()) cmd.parameters.addwithvalue("@k", orderitem.item("isyescanceled").tostring()) cmd.parameters.addwithvalue("@l", orderitem.item("productquantity").tostring()) cmd.executenonquery() end using end if next end using ' close connection 'productnumber [string], name [string], category [string], price [string], orderid [string]' 'this bridge database\ strjson = "{" + "" + "contact" + "" + ":" + "[" + "{" + "result" + "" + ":" + serializer.serialize("1") + "}" + "]" + "}" context.response.write(strjson) catch ex exception dim strjson string strjson = "{" + "" + "contact" + "" + ":" + "[" + "{" + "result" + "" + ":" + serializer.serialize("0") + ex.message + "}" + "]" + "}" context.response.write(strjson) end try end sub
Comments
Post a Comment