Solved

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

Posted on 2008-10-06
8
1,496 Views
Last Modified: 2012-05-05
I'm using amCharts (http://www.amcharts.com) 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 vb.net 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) vb.net 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?

Thanks!
0
Comment
Question by:vt96
  • 4
  • 4
8 Comments
 
LVL 18

Expert Comment

by:carlnorrbom
ID: 22650567
Hi,

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

/Carl.
0
 
LVL 18

Expert Comment

by:carlnorrbom
ID: 22650636
Hi,

Well, looking more closely at AmCharts they do offer asp.net 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: http://www.amcharts.com/aspnet/documentation/

/Carl.
0
 

Author Comment

by:vt96
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 vb.net, 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?
0
 

Author Comment

by:vt96
ID: 22656298
http://www.amcharts.com/docs/v.1/stock/data/data_setup
http://www.amcharts.com/docs/v.1/bundle/data/dynamic_data

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.
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 18

Expert Comment

by:carlnorrbom
ID: 22669625
Hi,

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.

/Carl.
ChartPage.aspx:
 

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

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
 

<html xmlns="http://www.w3.org/1999/xhtml">

<head runat="server">

    <title>Untitled Page</title>

</head>

<body>

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

    <div>

    

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

    

    </div>

    </form>

</body>

</html>
 

ChartPage.aspx.vb:
 

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()

        sda.Fill(dt)

        '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

            IO.File.Delete(strPath)

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

        Else

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

        End If

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

        sw.WriteLine(strLineToWrite)

        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)

                sw.WriteLine(strLineToWrite)

            Next

        End If

        sw.Close()

    End Sub
 

End Class

Open in new window

StockTable.gif
0
 
LVL 18

Accepted Solution

by:
carlnorrbom earned 500 total points
ID: 22703557
So,

Did this solve it for You?

/Carl.
0
 

Author Comment

by:vt96
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
0
 

Author Closing Comment

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

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Suggested Solutions

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
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…
The goal of the tutorial is to teach the user how to set there setting in Adobe Flash Media Live Encoder and YouTube for optimal video and audio quality.
The goal of the tutorial is to teach the user how to select the video input device. Make sure you have an input device that in connected and work and recognized by Adobe Flash Media Live Encoder and select it in the “video input” menu.

707 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

15 Experts available now in Live!

Get 1:1 Help Now