I'm really stuck with this..I have never used web services but needed a web service to supply json data for a jquery datatable.
The following webservice ( i thought ) should create a json file but its actually creating an xml file
Here is the routine
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Services;
using System.Text;
using System.Web.Script.Services;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.ServiceModel.Web;
namespace NewWebsite
{
/// <summary>
/// Summary description for WebService1
/// </summary>
[WebService(Namespace = "NewWebsite")]
[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 class WebService1 : System.Web.Services.WebService
{
[WebMethod]
[ScriptMethod(UseHttpGet = true)]
[WebInvoke(ResponseFormat = WebMessageFormat.Json,
BodyStyle = WebMessageBodyStyle.Bare, Method = "GET")]
public string GetItems()
{
var sb = new StringBuilder();
var connectionString = ConfigurationManager.ConnectionStrings["kudos"].ConnectionString;
SqlConnection conn = new SqlConnection(connectionString);
try
{ conn.Open(); }
catch (Exception e)
{ Console.WriteLine(e.ToString()); }
var DB = new SqlCommand();
DB.Connection = conn;
DB.CommandText = "select customer_name,town from customers";
var data = DB.ExecuteReader();
string outputJson = string.Empty;
while (data.Read())
{
sb.Append("{");
sb.AppendFormat(@"""Name"": ""{0}""", data["Customer_Name"]);
sb.Append(",");
sb.AppendFormat(@"""Town"": ""{0}""", data["Town"]);
sb.Append("},");
}
outputJson = sb.ToString();
return outputJson;
}
}
}
The output looks like this :-
<string xmlns="NewWebsite">
{"Name": "A Q S Bathrooms Lyons Electrical","Town": "SPENNYMOOR"},{"Name": "H & S Bathrooms","Town": "BLACKBURN"},{"Name": "Homecare Heating","Town": "DARLINGTON"},{"Name": "Midland Showers & Bathrooms Ltd","Town": "NOTTINGHAM"},{"Name": "City Plumbing Supplies","Town": "CARDIFF"},{"Name": "Home & Trade Ltd","Town": "BURTON-ON-TRENT"},{"Name": "City Plumbing Supplies DUPP AT 4838","Town": "PLYMOUTH"},{"Name": "City Plumbing Supplies","Town": "HALIFAX"},{"Name": "Harmony Interiors","Town": "LUTON"},{"Name": "Pure Bathrooms","Town": "CAMBRIDGE"},{"Name": "Bathrooms Kitchens By Design","Town": "NUNEATON"},{"Name": "Fire & Water","Town": "WHITEHAVEN"},{"Name": "City Plumbing Supplies","Town": "EXETER"},{"Name": "City Plumbing Supplies","Town": "GOSPORT"},{"Name": "City Plumbing Supplies","Town": "GLASGOW"},................................................
Any idea why ?
If anyone has a good example of how to link jquery datatables with sql via json I;d be REALLY happy !