How to write to a text file in pipe delimited format from SQL Server / ASP.Net?

Question

I have a text file which needs to be constantly updated (regular intervals).

All I want is the syntax and possibly some code that outputs data from a SQL Server database using ASP.Net. The code I have so far is :

<%@ Import Namespace="System.IO" %>
<script language="vb" runat="server">
  sub Page_Load(sender as Object, e as EventArgs)
  Dim FILENAME as String = Server.MapPath("Output.txt")

    Dim objStreamWriter as StreamWriter
   ' If Len(Dir$(FILENAME)) > 0 Then Kill(FILENAME)
   objStreamWriter = File.AppendText(FILENAME)

    objStreamWriter.WriteLine("A user viewed this demo at: " & DateTime.Now.ToString())

    objStreamWriter.Close()

    Dim objStreamReader as StreamReader
    objStreamReader = File.OpenText(FILENAME)

    Dim contents as String = objStreamReader.ReadToEnd()

    lblNicerOutput.Text = contents.Replace(vbCrLf, "<br>")

    objStreamReader.Close()
  end sub
</script>

<asp:label runat="server" id="lblNicerOutput" Font-Name="Verdana" />

With PHP, it is a breeze, but with .Net I have no clue. If you could help me with the database connectivity and how to write the data in pipe delimited format to an Output.txt file, that had be awesome. Thanks guys!

Answer 1

I would start by at least putting this code into a code-behind file so that your page at least follows the principle of least surprise.

Easiest way to write stuff to a file is to use the File.WriteAllText method (assuming your application has appropriate permissions for the file system).

You can access the database using the SqlConnection class and execute whatever commands you need with a SqlCommand. Once you've gotten data back from the database, just turn it into an array (assuming there's not a massive amount of data) and call String.Join method like so String.Join("|", yourData).

Answer 2

  Dim dr As SqlDataReader

  Dim FILENAME as String = Server.MapPath("Output.txt")

  Dim objStreamWriter as StreamWriter
  objStreamWriter = File.CreateText(FILENAME)

  sqlConn.Open()
   'opening the connection
  myCommand = New SqlCommand("SELECT  id, title, expirydate, creationdate from tbl where tbl.isactive=1 and getdate()<=ExpiryDate order by  creationdate asc", sqlConn)
  'executing the command and assigning it to connection 
  dr = myCommand.ExecuteReader()
  While dr.Read()

      objStreamWriter.WriteLine("{0}|{1}|{2:yyyy-MM-dd}|{3:yyyy-MM-dd}", dr(0),  dr(1), dr(2), dr(3))

  End While
  dr.Close()
  sqlConn.Close()

  objStreamWriter.Close()

  Dim objStreamReader as StreamReader
  objStreamReader = File.OpenText(FILENAME)