Question regarding SQL and ASP.NET

[Byrons_Ghost]

For a website I'm working on, I need to make an ASP script that will read from the MS SQL database and then write certain results to a delimited text file. The text file will eventually be sent off to another server via FTP, but I'm not at that stage yet.

I don't know much about ASP at all, but since the project is on a windows hosting plan that's pretty much all I can use. Every example I've seen on the web for reading SQL data into ASP binds it into a DataGrid and then dumps the whole set into an HTML table. Since I need plain text, and need to be able to control the layout, this doesn't work for me.

Can anyone tell me of an alternative that would do what I want, or point me to a site with some good docs or examples? Thanks!

 

[IcyCool]

You just want to connect to a database, pull the data, and put it in a text file? Have you done that before in any other languages?

Here's a pretty simple way to connect to an Oracle DB and pull data:

In the web.config file, you'll want an entry under your <connectionstrings> tag, mine looks similar to the following:

<add name="SampleConnection" connectionString="Data Source=[i]datasource[/i];Persist Security Info=True;User ID=[i]userid[/i];Password=[i]password[/i];Unicode=True" providerName="System.Data.OracleClient"/>

Then in your code you'll do something like this:

string sampleConnString = ConfigurationManager.ConnectionStrings["SampleConnection"].ConnectionString;
string sampleCommand = "Select * from Table1";

OracleDataReader simpleReader;
OracleCommand queryCommand = new OracleCommand();
OracleConnection dbConnection = new OracleConnection(sampleConnString);

In a try-catch block you'll do this:

dbConnection.Open();
queryCommand.Connection = dbConnection;
queryCommand.CommandText = sampleCommand;

simpleReader = queryCommand.ExecuteReader();

And now you have a dataReader object containing the results of your SQL statement. From there you instantiate a file object, and write out the contents of the dataReader to the file.

There are other ways of doing this, and this was for an Oracle database, so you may have to play around with it.

Edit - I should also note that the above is in C#. You didn't specify C# or VB.NET, so I pulled an example from what I'm most familiar with.

You might also want to read the info at the following links:

MSDN Magazine article about the differences between using datareaders and datasets.
Querying data overview

Or just search for anything on ADO.NET.

 

[Byrons_Ghost]

Sorry, it's actually in VB. I wasn't thinking when I posted.

I've got pretty much the same thing up through the DataReader (see below), so it looks like I just need to dump the datagrid assignment for a file assignment. I'll have to start checking around on that, instead of the SQL part, since that's taken care of. Thanks for the pointer!

	Sub Page_Load(sender As Object, e As EventArgs)
		Dim myConnection As SqlConnection 
		Dim myCommand    As SqlCommand
		Dim myDataReader As SqlDataReader

		' Create a new Connection object that connects to our SQL Server.

		myConnection = New SqlConnection("...")

		' Create a new Command object that uses the Connection object we
		' just created and selects all the records from our scratch table.
		myCommand = New SqlCommand("SELECT * FROM UserDefinedFields;", myConnection)

		' Open the connection to the database
		myConnection.Open

		' Use the ExecuteReader method of the Command object to execute
		' our query and return the results via a DataReader
		myDataReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection)

		' Specify the DataReader as the source of the data for our DataGrid
		' and then DataBind to display the data in our DataGrid.
		SimpleDataGrid.DataSource = myDataReader
		SimpleDataGrid.DataBind()

		' Close our DataReader and Connection
		myDataReader.Close()
		myConnection.Close()
	End Sub

 

[IcyCool]

Yep, unless you need to use the datagrid for something, there's no need for it here. I haven't fiddled with files all that much myself, so if there is a slick way of dumping the data from the datareader straight into it, I don't know of it. But a simple while(datareader.Read()) loop will let you write each individual record in the datareader to the file fairly simply.

On the off chance that you've never "manually" retrieved something from a DataReader before, the retrieval looks something like this (again, this is in C#):

sampleString = simpleReader["Section"];

Where Section is one of the field/column names in your SQL query. The Read() method steps you to the next record in the DataReader (or the first, if you haven't called the Read() method yet on a fresh DataReader).

Pretty simple.

 

[drothgery]

As for that text file...

You'll want to look at the System.IO namespace to actually write a file (and you'll need to make sure that the user ASP.NET is running as can write the file somewhere other than the temp directory).

Basically, you'll want to...

create a TextWriter object

- write the field names, separated by commas, quoting strings
- write a newline character

for each iteration of looping through your datareader
- write the fields, separated by commas, with string values quoted
- write a newline character

close the TextWriter

If you've got some tricky-VB questions, I can answer them (having written almost nothing but VB.NET for a living since 2003...), but it's your homework...