Best way to generate csv data file from SQL Server for flash object (chart)?

Posted on 2008-10-06
Last Modified: 2012-05-05
I'm using amCharts ( to display a flash chart on an intranet webpage.  I have data that I would like to add dynamically from MS SQL Server 2008.  I am developing the ASP.NET page using and javascript in VS2008.

amCharts (and other flash charting controls) require csv or xml files to read in data (why can't they access sql directly?).  I was originally going to use shell bcp from the code behind page, but that would require an install on many users' computers.  My question is...

What is the fastest way to do this?

1) code to query sql server, then run a loop to create the csv file
2) sql stored procedure using bcp/xp_cmdshell
3) some other way?

Any examples I can look at?

Question by:vt96
  • 4
  • 4
LVL 18

Expert Comment

ID: 22650567

Which version of the .NET framework are you targeting? 2.0? 3.5?

LVL 18

Expert Comment

ID: 22650636

Well, looking more closely at AmCharts they do offer 2.0 server controls which offers:
- design-time support for creation and customization of amCharts in Visual Studio 2005 or newer;
- databinding to any .NET compatible data source or data source controls;
- support for inline or external data generation and settings;
- supports all settings available in amCharts

Is there a specific reason that You cannot use this? A complete reference to the API can be found at:


Author Comment

ID: 22656266
Thanks for the response.  I looked at it, but I'm not sure if I can use that with the stock charts they have (which is what I'm using)...I've emailed the author.  Currently I'm reading that I can use an SQL connection in, building a string in csv or xml format, get that into the .aspx page somehow, then add it to the chart?  Any further suggestions or examples?

Author Comment

ID: 22656298

It looks like the usage with stockcharts is a little different.  Looking at that page, how should I go about creating a string from a sql query and feeding it into the chart?  I'm not new to database programming, but I'm very new to web programming and am not sure how it works.
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

LVL 18

Expert Comment

ID: 22669625

Well, building a csv file for instance from a sql query isn't very tricky. If you look at the AmCharts example you mention where they get some stock information from Nasdaq we can use that in an example. What I did was putting the figures from the example into a sql database table called Stock. In the attached code sample I query the sql db to retrieve the data in form of a datatable. I then create a csv file using a streamwriter and iterate over the datatable rows to write the lines to the csv file.

The method is called by clicking a button in the page but could be through any other available means really. Also worth mentioning is the writing of the first line containing the headers which is performed before the iteration. The csv file is created in the web root in the example but could be located elsewhere in the hierarchy. I have not gone into the aspects of assigning the file as a datasource for the chart, I guess you got that part covered already. Please note You will need to change parts related to connectionstrings etc.


<%@ Page Language="VB" AutoEventWireup="false" CodeFile="ChartPage.aspx.vb" Inherits="ChartPage" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "">

<html xmlns="">

<head runat="server">

    <title>Untitled Page</title>



    <form id="form1" runat="server">



        <asp:Button ID="btnGenerateCSV" runat="server" Text="Generate CSV" />







Imports System.Data

Imports System.Data.SqlClient

Imports System.IO

Partial Class ChartPage

    Inherits System.Web.UI.Page

    Protected Sub btnGenerateCSV_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnGenerateCSV.Click

        'Retrieve data in datatable

        Dim sc As New SqlConnection(ConfigurationManager.ConnectionStrings("test_dbConnectionString").ConnectionString)

        Dim sda As New SqlDataAdapter("SELECT * FROM Stock WHERE (StockSign = @StockSign)", sc)

        sda.SelectCommand.Parameters.AddWithValue("@StockSign", "NASDAQ")

        Dim dt As New DataTable()


        'Set up a *.csv file to write the results to that can be used a datasource for the chart

        Dim strPath As String = Server.MapPath("~/stockSource.csv")

        Dim sw As StreamWriter

        Dim strLineToWrite As String = Nothing

        If IO.File.Exists(strPath) Then


            sw = New StreamWriter(File.Open(strPath, FileMode.CreateNew, FileAccess.Write))


            sw = New StreamWriter(File.Open(strPath, FileMode.CreateNew, FileAccess.Write))

        End If

        strLineToWrite = "Date,Open,High,Low,Close,Volume,Adj Close"


        If dt.Rows.Count > 0 Then

            Dim sb As New StringBuilder()

            For Each row As DataRow In dt.Rows

                sb.Append(row.Item("Date").ToString + ",")

                sb.Append(row.Item("pOpen").ToString.Replace(",", ".") + ",")

                sb.Append(row.Item("pHigh").ToString.Replace(",", ".") + ",")

                sb.Append(row.Item("pLow").ToString.Replace(",", ".") + ",")

                sb.Append(row.Item("pClose").ToString.Replace(",", ".") + ",")

                sb.Append(row.Item("Volume").ToString + ",")

                sb.Append(row.Item("pAdjClose").ToString.Replace(",", "."))

                strLineToWrite = sb.ToString

                sb.Remove(0, sb.Length)



        End If


    End Sub

End Class

Open in new window

LVL 18

Accepted Solution

carlnorrbom earned 500 total points
ID: 22703557

Did this solve it for You?


Author Comment

ID: 22717962
Thanks for the response, but I already had a solution (very similar to what you have) to create CSV files and load it in.  I guess my question was more which is the fastest way to do it?  I was reading something about generating CSV data and loading it directly into the chart, but I am not sure how this is done.  Possibly using Response.QueryString or something like that?

Sorry if I wasn't too clear in my request.  If/when I get a solution I will assign some of the points to your previous response.  Thanks

Author Closing Comment

ID: 31503347
sorry for the delay on accepting your answer since i've been pre-occuppy with so many things.  thanks again.

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have found that much of my time doing support ends up being a constant repetition of the same steps to different people.  Early on I stated using web pages with Frequently Asked Questions (FAQs) to alleviate most of the burden.  Sometimes this jus…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
This Micro Tutorial will teach to how to utilize bit rate in Adobe Flash Media Live Encoder.

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now