Buy Questpond's video subscriptions on huge discount.
DotNetFunda.com
Online: 1028
Home > Forums > ASP.NET >

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

Posted by Christxavier under ASP.NET on 9/10/2013 | Points: 10 | Views : 4472 | Status : [Member] | Replies : 1
Write New Post | Search Forums | AnsweredResolved Posts | Un Answered Posts | Forums Home 
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" />
</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.



Reply | Reply with Attachment
Alert Moderator 

Responses

Posted by: Allemahesh on: 9/10/2013 [Member] [MVP] Silver | Points: 25

Up
0
Down
Dear Christxavier,

I hope you know how to create a Web API Project in ASP.Net. If dot know then you can see the below link:-
http://www.asp.net/web-api

Well Come to you questions. For JSON response using asp.net web api, you need to do the following things.

1. Enable AJAX Request to Get JSONP Data
We need to set callback and data type attributes of Ajax request as highlighted below: 

<script type="text/javascript">

$("#getJsonp").click(function () {

$.ajax({

       	type: 'GET',

              url: "http://localhost:5511/api/values/GetUserName/1",

              callback: 'callbackFunc',

              contentType: "application/json",

              dataType: 'jsonp'

              })

});

 

function callbackFunc(resultData) {

alert(resultData);

}

</script>


2. Enable ASP.NET Web API to Return JSONP Data
-> Install the JSONP MediaTypeFormatter by entering the following command using NuGet Package Manager console in Visual Studio. 
Install-Package WebApiContrib.Formatting.Jsonp

-> Add a FormatterConfig class in App_Start folder: 
public class FormatterConfig

    {

        public static void RegisterFormatters(MediaTypeFormatterCollection formatters)

        {

            var jsonFormatter = formatters.JsonFormatter;

            jsonFormatter.SerializerSettings = new JsonSerializerSettings

            {

                ContractResolver = new CamelCasePropertyNamesContractResolver()

            };

 

            // Insert the JSONP formatter in front of the standard JSON formatter.

            var jsonpFormatter = new JsonpMediaTypeFormatter(formatters.JsonFormatter);

            formatters.Insert(0, jsonpFormatter);

        }

    }

-> Add the following routes in /App_Start/RouteConfig.cs: 
routes.MapHttpRoute(

                name: "DefaultApi",

                routeTemplate: "api/{controller}/{id}/{format}",

                defaults: new { id = RouteParameter.Optional, format = RouteParameter.Optional }

            );

-> Add the following line in the Application_Start method of Global.ascx.cs: 
FormatterConfig.RegisterFormatters(GlobalConfiguration.Configuration.Formatters);


3. If you want your routes mapping with action, please specify the following in App_Start/RouteConfig.cs: 
routes.MapHttpRoute(

                name: "DefaultApi",

                routeTemplate: "api/{controller}/{action}/{id}/{format}",

                defaults: new { id = RouteParameter.Optional, format = RouteParameter.Optional }

            );


You can also see the below links_;
http://stevenhollidge.blogspot.in/2013/03/how-to-return-jsonp-from-webapi.html
https://github.com/WebApiContrib/WebApiContrib.Formatting.Jsonp

Happy Coding.

If it helps you or directs U towards the solution, MARK IT AS ANSWER


Christxavier, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response

Latest Posts