How to return JSON object by executing SQL Server stored procedure

Question

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

Side note: you should not use the sp_ prefix for your stored procedures. Microsoft has reserved that prefix for its own use (see Naming Stored Procedures), and you do run the risk of a name clash sometime in the future. It's also bad for your stored procedure performance. It's best to just simply avoid sp_ and use something else as a prefix - or no prefix at all! — marc_s
– marc_s, Commented Oct 23, 2015 at 5:45
Also - you're executing the query twice - once on ExecuteNonQuery, and a second time on da.Fill(ds) - don't do that! What are you trying to do? Load data from the database? Don't use ExecuteNonQuery for that! This call is only intended for SQL statements that don't return any data! (Like INSERT, DELETE) — marc_s
– marc_s, Commented Oct 23, 2015 at 5:46

Jovan MSFT · Accepted Answer · 2015-11-05 19:18:34Z

4

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

answered Nov 5, 2015 at 19:18

Jovan MSFT

14.8k4 gold badges44 silver badges59 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Shivam Mishra · Accepted Answer · 2019-03-20 17:26:42Z

4

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)

answered Mar 20, 2019 at 17:26

Shivam Mishra

3173 silver badges15 bronze badges

Collectives™ on Stack Overflow

How to return JSON object by executing SQL Server stored procedure

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related