Solved

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

Posted on 2008-10-06
8
1,518 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
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.

 

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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

The last time I worked with Flash and Socket connections was in AS1. A recent project required flash connecting to a Socket, and sending receiving information - we figured it would be easy enough - we all know about the socket policy documents and c…
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
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.
The goal of the tutorial is to teach the user how to select which audio input to use. Once you have an audio input plugged into the laptop or computer, you will go into the audio input settings and choose which audio input you want to use.

733 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