Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1561
  • Last Modified:

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

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
vt96
Asked:
vt96
  • 4
  • 4
1 Solution
 
carlnorrbomCommented:
Hi,

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

/Carl.
0
 
carlnorrbomCommented:
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
 
vt96Author Commented:
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
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
vt96Author Commented:
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
 
carlnorrbomCommented:
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
 
carlnorrbomCommented:
So,

Did this solve it for You?

/Carl.
0
 
vt96Author Commented:
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
 
vt96Author Commented:
sorry for the delay on accepting your answer since i've been pre-occuppy with so many things.  thanks again.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now