We use cookies to give you the best experience on our website. If you continue to browse, then you agree to our privacy policy and cookie policy. (Last updated on: November 16, 2018).
Unfortunately, activation email could not send to your email. Please try again.
Syncfusion Feedback

Using a SQL Server stored procedure

Thread ID:

Created:

Updated:

Platform:

Replies:

131216 Jun 28,2017 03:57 PM UTC Jun 29,2017 01:56 PM UTC ASP.NET MVC 1
loading
Tags: Chart
Michael Easterbrook
Asked On June 28, 2017 03:57 PM UTC

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 [Syncfusion]
Replied On June 29, 2017 01:56 PM UTC

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. 
 
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. 


CONFIRMATION

This post will be permanently deleted. Are you sure you want to continue?

Sorry, An error occured while processing your request. Please try again later.

Warning Icon You are using an outdated version of Internet Explorer that may not display all features of this and other websites. Upgrade to Internet Explorer 8 or newer for a better experience.Close Icon

;