I am attempting to make my first UltraCategoryChart. I will describe the end result I'd like first. We are comparing quantities for the last three years. So the X axis will be months, January through December. For each month there will be 3 columns grouped together representing each year. So January will have 2018, 2019, and 2020 with those counts supplied by a SQL database query:
select year(acctdate) as year, month(acctdate) as month, FORMAT(AcctDate, 'MMMM') as MyMonthName,count(*) as count from Acct where year(acctdate) >=2018group by year(acctdate), month(acctdate), FORMAT(AcctDate, 'MMMM')order by month(acctdate), year(acctdate)
Resulting in a dataset that looks like this (showing just the first two months here):
2018 1 January 13792019 1 January 19482020 1 January 14072018 2 February 14172019 2 February 11362020 2 February 1126
I've cobbled together code from online documentation, but I don't see anything on how to create the chart using a SQL database. I've attempted to replace the UltraCategoryChart.DataSource with a SQL DbDataReader resultset, but I get nothing displayed on the chart. There are no errors. Can anyone tell me what is missing?
Dim strSQL As String = "select year(acctdate) as Year, month(acctdate) as Month, FORMAT(AcctDate, 'MMMM') as MonthName,count(*) as Count from Acct " _ & "where year(acctdate) >=2018 " _ & "group by year(acctdate), month(acctdate), FORMAT(AcctDate, 'MMMM') " _ & "order by month(acctdate), year(acctdate)"
Dim adoRs As DbDataReader adoRs = My.Application.GlobalAuditDB.MExecuteReader(strSQL)
Dim series As New AreaSeries() Dim xAxis As New CategoryXAxis() Dim yAxis As New NumericYAxis()
UltraCategoryChart1.ChartType = CategoryChartType.Column UltraCategoryChart1.DataSource = adoRs
UltraCategoryChart1.IncludedProperties = New String() {"Year", "Month", "Count"} UltraCategoryChart1.ExcludedProperties = New String() {"MonthName"}
UltraCategoryChart1.Visible = True UltraCategoryChart1.BringToFront()
Hello Ted,
I am unsure of what other SQL interfaces that would work here, but DataSet certainly will not. The UltraCategoryChart expects an IEnumerable as its DataSource, which is the reason that in the sample code I sent you above, I bound the DataSource to table.DefaultView instead of just the DataTable, as DataTable is not an IEnumerable.
As an alternative to using a DbDataReader though, perhaps something you could try is to utilize the Entity Framework to access your SQL database? There is a code-example of this in Microsoft’s documentation, here.
Please let me know if you have any other questions or concerns on this matter.
For this example the data set is small enough that would be feasible, but I anticipate larger sets. Is there any other VB accessible SQL interface that would work? DataSet for example? This is an old and large project that accesses SQL data using several methods - whatever was in vogue at the time of programming updates, so I am flexible on the SQL query aspect. A VB example would be useful, but just knowing the compatibility of UltraChart vis-à-vis various SQL access methods would be very helpful.
I have been investigating into the behavior you are looking to achieve, and it appears that the DbDataReader is not a valid data source for the UltraCategoryChart at the moment. I would recommend reading your DbDataReader into a DataTable. For example, here is a code-example that I used using the AdventureWorks 2012 database:
SqlConnection connection = new SqlConnection(connectionString); SqlCommand cmd = new SqlCommand("select * from DimEmployee", connection); connection.Open(); SqlDataReader reader = cmd.ExecuteReader(); DataTable table = new DataTable(); table.Columns.Add("Name", typeof(string)); table.Columns.Add("BaseRate", typeof(double)); while (reader.Read()) { DataRow row = table.NewRow(); string name = reader["FirstName"] + " " + reader["LastName"]; var rate = reader["BaseRate"]; row[0] = name; row[1] = rate; table.Rows.Add(row); } ultraCategoryChart1.DataSource = table.DefaultView;
I hope this helps you. Please let me know if you have any other questions or concerns on this matter.
Do I need to add the DataSource in the property pages prior (and in addition) to running the above code?