I have done webservice with sqlconnection using asmx . now i need to develop webservice using asp .net web api with json response.

See more:

ASP.NET

SQL-Server

JSON

hi, I have done webservice with sqlconnection using asmx .

step 1: asmx.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Services;
using System.Xml;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;


namespace webserxml
{
/// <summary>
/// Summary description for WebService1
/// </summary>
[WebService(Namespace = "http://tempuri.org/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
[System.ComponentModel.ToolboxItem(false)]
// To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line. 
// [System.Web.Script.Services.ScriptService]
public partial class WebService1 : System.Web.Services.WebService
{

[WebMethod]
public XmlElement GetUserDetails(string sqlcmd)
{
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["sqlconn"].ToString());
con.Open();
SqlCommand cmd = new SqlCommand(sqlcmd, con);
cmd.ExecuteNonQuery();
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
con.Close();
XmlDataDocument xmldata = new XmlDataDocument(ds);
XmlElement xmlElement = xmldata.DocumentElement;
return xmlElement;
}
}
}

step 2: web config file

<connectionstrings>
<add name="sqlconn" connectionstring="data source=sourcename;initial catalog=databasename;user id=idname;password=pwd">
providerName="System.Data.SqlClient" />
</add></connectionstrings>

now i run the the application. i will have search colum in webservice right like follows:

parameter value

sqlcmd select * from sportstablename

invoke btn.
so, if i click invoke i will get data of spoststable in xml format. right?. in sqlcmd, i can write any kind of query and can get data from data base. so the same thing i need to develop in asp.net web api which return json format. becuase i new for asp.net web api. while browsing i came across wcf service and asp.net web api. more comments said go for asp.net web api which is good and having more advantantage compring asmx and wcf service. so, please the above same requirment i need by in json format using asp.net web api.



here using above connectionstring. i can retrieve whatever existing table in database. few article did using sql to LINQ which takes only one table relation and few article ADO .NET entity that also does connection any particular table which both are not flexile and not user defined. the above asmx webserce what i mentioned is more user defined for my project. everyone just connect database using connection string and can retrieve. but the thing entirly i wanted to do JSON response using asp.net web api. pls give full explanation with brief example.

thanks a lot.