CODEWITHSUNDEEP
c#sql-serverjsonxmlstored-procedures
Keey
Keey

Reputation: 23

How to return JSON object by executing SQL Server stored procedure

I am using this method to return a XML as result. I need to return a json object after executing the stored procedure. Where should I edit the following code to return a JSON object?

public XmlElement GetGraphData(int eventTypeID, int patientID)
{
    SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["dbconnection"].ToString());
    con.Open();

    SqlCommand cmd = new SqlCommand("sp_GetGraphData", con);
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.AddWithValue("@EventID", eventTypeID);
    cmd.Parameters.AddWithValue("@PatientID", patientID);

    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;
}

Upvotes: 1

Views: 35083

Answers (2)

Shivam Mishra
Shivam Mishra

Reputation: 317

For return in JSON format Create SP like

CREATE PROCEDURE spGetAlluser 

AS
BEGIN
    Select * from AspNetUsers FOR JSON AUTO
END
GO

and for get user detail in class obj

var UserDetails=JsonConvert.DeserializeObject<string>(result)

Upvotes: 4

Jovan MSFT
Jovan MSFT

Reputation: 14630

SQL Server 2016 has FOR JSON clause that formats query result directly in query/stored procedure - see https://msdn.microsoft.com/en-us/library/dn957476.aspx

Upvotes: 4

Related Questions