streaming text (csv) files

Discussion in 'ASP .Net' started by Troy, Feb 11, 2004.

  1. Troy

    Troy Guest

    Hi all,

    I want to export the contents of a datatable out to a web client and I
    am wondering what the best performance and maintanence concious method
    of doing this is.

    Would it be possible to have the text file streamed out to the client
    directly? Meaning, I don't even want to write the text file to disk -
    just create it in memory and then have the server stream the file
    directly to the browser as a download.

    Is this too much coding? Is this extremely advanced and time consuming?

    Regards,

    Troy
     
    Troy, Feb 11, 2004
    #1
    1. Advertising

  2. Hi Troy,

    Here's some code that should get you going. It grabs data from the SQL
    database as a datareader and pushes it out as a CSV. No storage of the file
    required. You should see Excel open with the data.

    Does this help?

    Ken
    Microsoft MVP [ASP.NET]


    Imports System.Data.SqlClient
    Imports System.IO
    Public Class csv
    Inherits System.Web.UI.Page
    Protected WithEvents Button1 As _
    System.Web.UI.WebControls.Button
    Protected WithEvents SqlConnection1 As _
    System.Data.SqlClient.SqlConnection
    #Region " Web Form Designer Generated Code "
    'This call is required by the Web Form Designer.
    <System.Diagnostics.DebuggerStepThrough()> _
    Private Sub InitializeComponent()
    Me.SqlConnection1 = _
    New System.Data.SqlClient.SqlConnection
    '
    'SqlConnection1
    '
    Me.SqlConnection1.ConnectionString = _
    "data source=P4320;initial catalog=" & _
    "Northwind;password="""";persist security info=Tru" & _
    "e;user id=sa;workstation id=P4320;packet size=4096"
    End Sub
    Private Sub Page_Init _
    (ByVal sender As System.Object, _
    ByVal e As System.EventArgs) _
    Handles MyBase.Init
    'CODEGEN: This method call is
    'required by the Web Form Designer
    'Do not modify it using the code editor.
    InitializeComponent()
    End Sub
    #End Region
    Private Sub Button1_Click _
    (ByVal sender As System.Object, _
    ByVal e As System.EventArgs) _
    Handles Button1.Click
    'Set the appropriate ContentType.
    Dim filename As String = "orderdetails.csv"
    Dim myCommand As New SqlCommand _
    ("select * from [order details] ", SqlConnection1)
    myCommand.Connection.Open()
    Dim myReader As SqlDataReader = _
    myCommand.ExecuteReader _
    (CommandBehavior.CloseConnection)
    Dim i As Integer
    Dim sb As New System.Text.StringBuilder
    For i = 0 To myReader.FieldCount - 1
    If i < (myReader.FieldCount - 1) Then
    sb.Append(Chr(34) & myReader.GetName(i) & _
    Chr(34) & ",")
    Else
    sb.Append(Chr(34) & myReader.GetName(i) & _
    Chr(34) & vbCrLf)
    End If
    Next
    While myReader.Read()
    For i = 0 To myReader.FieldCount - 1
    If i < (myReader.FieldCount - 1) Then
    sb.Append(Chr(34) & _
    myReader.GetValue(i).ToString & Chr(34) & ",")
    Else
    sb.Append(Chr(34) & _
    myReader.GetValue(i).ToString & Chr(34) & vbCrLf)
    End If
    Next
    End While
    myReader.Close()
    SqlConnection1.Close()
    Response.ContentType = "Application/x-msexcel"
    Response.AddHeader _
    ("content-disposition", "attachment; filename=""" & _
    filename & """")
    'Write the file directly to the HTTP output stream.
    Response.Write(sb.ToString)
    Response.End()
    End Sub
    End Class



    <%@ Page Language="vb" AutoEventWireup="false" Codebehind="csv.aspx.vb"
    Inherits="p4320work.csv"%>
    <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
    <HTML>
    <HEAD>
    <title>csv</title>
    <meta name="GENERATOR" content="Microsoft Visual Studio .NET 7.1">
    <meta name="CODE_LANGUAGE" content="Visual Basic .NET 7.1">
    <meta name="vs_defaultClientScript" content="JavaScript">
    <meta name="vs_targetSchema"
    content="http://schemas.microsoft.com/intellisense/ie5">
    </HEAD>
    <body MS_POSITIONING="FlowLayout">
    <form id="Form1" method="post" runat="server">
    <asp:Button id="Button1" runat="server" Text="Launch"></asp:Button>
    </form>
    </body>
    </HTML>

    "Troy" <> wrote in message
    news:...
    >
    > Hi all,
    >
    > I want to export the contents of a datatable out to a web client and I
    > am wondering what the best performance and maintanence concious method
    > of doing this is.
    >
    > Would it be possible to have the text file streamed out to the client
    > directly? Meaning, I don't even want to write the text file to disk -
    > just create it in memory and then have the server stream the file
    > directly to the browser as a download.
    >
    > Is this too much coding? Is this extremely advanced and time consuming?
    >
    > Regards,
    >
    > Troy
     
    Ken Cox [Microsoft MVP], Feb 11, 2004
    #2
    1. Advertising

  3. Troy

    Troy Guest

    Ken Cox [Microsoft MVP],

    >>Hi Troy,
    >>
    >>Here's some code that should get you going. It grabs data from the SQL
    >>database as a datareader and pushes it out as a CSV. No storage of the file
    >>required. You should see Excel open with the data.
    >>
    >>Does this help?



    That did the trick! Thanks!
    --
    --

    Regards,

    Troy
     
    Troy, Feb 11, 2004
    #3
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Michal Mikolajczyk
    Replies:
    0
    Views:
    694
    Michal Mikolajczyk
    Feb 13, 2004
  2. Skip Montanaro
    Replies:
    0
    Views:
    767
    Skip Montanaro
    Feb 13, 2004
  3. Tintin92
    Replies:
    1
    Views:
    1,813
    Andrew Thompson
    Feb 14, 2007
  4. jliu66
    Replies:
    0
    Views:
    566
    jliu66
    Oct 19, 2007
  5. sso
    Replies:
    20
    Views:
    2,778
    Martin Gregorie
    Apr 26, 2009
Loading...

Share This Page