Using a SQL Server stored procedure

[Michael Easterbrook]

Can I use a SQL Server stored procedure as the data source for a chart? For example:

var db = Database.Open("BITool");

var data = db.Query("Exec SalesOps.AccountOrders @Account=@0", App.AccountListID);

var myChart = new Chart(width: 835, height: 200, themePath: "chartThemeYellow.xml")

.AddTitle("Account Medical Orders")

.AddSeries("Default",

xValue: data, xField: "Segment",

yValues: data, yFields: "Orders")

.Write();

     

[Dharanidharan Dharmasivam]

Hi Michael, 

Thanks for contacting Syncfusion support. 

We have analyzed your query. Yes, you can able to use SQL stored procedure as data source for chart. We have prepared a sample, in which in ajax request of load event, we have bind the data source to chart as depicted in the below code snippet. 

ASP.NET MVC: 

Stored procedure: 

CREATE PROCEDURE [dbo].[ChartData] 

               

AS 

BEGIN 

SET NOCOUNT ON; 

               

              SELECT * FROM Orders  

 

END         

               

RETURN 0          

View page: 

@(Html.EJ().Chart("container") 

    .Load("chartLoad") 

) 

 

function chartLoad(sender) { 

        $.ajax({ 

            type: "POST", 

            url: '@Url.Action("DataSource", "Home")', 

            async: false, 

            success: function (data) { 

                sender.model.series = []; 

                sender.model.series.push({ dataSource: data.result, xName: "OrderID", yName: "EmployeeID", type: "line" }); 

            } 

        }); 

    } 

 

Controller: 

 

public ActionResult DataSource(DataManager dm) 

        { 

            DataOperations operation = new DataOperations(); 

            DataTable dt = new DataTable("Order"); 

            string constring = ConfigurationManager.ConnectionStrings["NORTHWNDConnectionString"].ConnectionString; 

            using (SqlConnection con = new SqlConnection(constring)) 

            { 

                using (SqlCommand cmd = new SqlCommand("ChartData", con)) 

                { 

                    con.Open(); 

                    SqlDataReader dr = cmd.ExecuteReader(); 

                    // Load into the dataTable 

                    dt.Load(dr);  

                    dr.Close(); 

                    con.Close(); 

                } 

            } 

            //Convert the dataTable into list 

            List<EditableOrder> resList = new List<EditableOrder>(); 

            //... 

            return Json(new { result = resList }, JsonRequestBehavior.AllowGet); 

        } 

 

Here we have bind all the data, you can change this with respect to this scenario. 

Screenshot: 

 

Sample for reference can be find from below link. 

http://www.syncfusion.com/downloads/support/forum/131216/ze/MvcApp-1267543360  

 

Find the steps below to create stored procedure. 

 

Step 1: Click on the Server Explorer, then click on the required db on which you need to create stored procedure and right click on the Store Procedures and create as depicted in the above code snippet. 

 

 

 

Step 2: Click on the Update to reflect back in original db. 

 

Kindly revert us, if you have any concern. 

Thanks, 

Dharani.