Websrvice sql for server stored procedure

[billythekidney]

Hi, I have a Sql Server 2008 database on a website and need to develop a web service using JSON to call from remote devices for running stored procedures. Does anyone have any sample code of a web service running a sp with input parameters, producing a JSON result set that I can have a look at? Thanks

Anthony

[Jasmine2501]

Several things have to come together to give JSON back to the browser... your service has to be decorated like this...

[ScriptService]
public class wMDTServices {
[WebMethod]
//public String Read(int skip, int take, IEnumerable<Sort> sort, Filter filter)
public List<DataRow> Read() {
try {
//changed this to use something I could find that might work, it had a compilation error - DA
DataSet result = OracleSQLHelper.RetrieveFromSQL("Some Query");

        var myList = new List<DataRow>();

        foreach (DataRow r in result.Tables\[0\].Rows) {
            myList.Add(r);
        }

        return myList;
    } catch {
        return null;// Newtonsoft.Json.JsonConvert.SerializeObject(exception);
    } finally { }
}

}

Then, you have to call it with the right content type, like this... (part of a JQuery Ajax call)

transport: {
read: {
url: myWebServiceUrl + "/Read",
dataType: "json",
type: "post",
contentType: "application/json; charset=utf-8"
}
}

The important things there are the contentType, the "post" request type, and the datatype "json" which are all needed to tell the web service that you want JSON back. If all these things are received by the server correctly, it will send back JSON strings.