Part 1 shows us how to create CSV files in both C# and VB.NET using techniques that were used back on the old days of ADO and OLEDB but implementing them using native ADO.NET objects(IDataReader) instead of recordsets. We use a IDataReader instead of a provider specific reader (SqlDataReader, OleDbDataReader) for usability of code. This makes our function not provider dependent.
Here is the solution for both C# and VB.NET projects
CreateCSVCS.zip (20.05 KB)
CreateCSVVB.zip (21.06 KB)
The C# source code for part 1 is listed below:
private void createButton_Click(object sender, System.EventArgs e)
{
//create the command object for the connection to the database
//also assign the sql statement to be use in querying our database
SqlCommand command = new SqlCommand(sqlStatementField.Text,new SqlConnection(connectionStringField.Text));
//open the connection
command.Connection.Open();
//create the sqlreader that would hold the result set
SqlDataReader reader = command.ExecuteReader();
//set the conttent type of the file to be downloaded
Response.ContentType = "Application/x-msexcel";
//add the response headers
Response.AddHeader("content-disposition", "attachment; filename=\"" + filenameField.Text + "\"");
//add the contents of the result set to the response stream
Response.Write( ToCSV(reader) );
//close the connection
command.Connection.Close();
//end the http response
Response.End();
}
public static string ToCSV(IDataReader iDr)
{
//declare the stringbuilder object that would serve as the placeholder for our data
StringBuilder sb = new StringBuilder();
//check to see if there the reader has columns
if(iDr.FieldCount != 0)
{
//loop thru each column
for(int i = 0; i <= iDr.FieldCount -1; i++)
{
//check to see if we are on the last column
// this is necessary so that we could append the nextline symbol
if(i<iDr.FieldCount -1)
{
//if not the last column, append the column name and a ','
sb.Append(iDr.GetName(i) + ",");
}
else
{
//append the last column name then append a carriage return and line feed
sb.Append(iDr.GetName(i));
sb.Append("\r\n");
}
}
//loop until the record reaches eol(end of line)
while(iDr.Read())
{
//loop thru each column, this is necessesary so that we could map
//the columns to their proper place
for(int i = 0; i <= iDr.FieldCount -1; i++)
{
//check to see if we are on the last column
//this is necessary so that we could append the nextline symbol
if(i<iDr.FieldCount -1)
{
//if not the last column, append the column value and a ','
sb.Append(iDr.GetValue(i) + ",");
}
else
{
//append the last column value then append a carriage return and line feed
sb.Append(iDr.GetValue(i));
sb.Append("\r\n");
}
}
}
}
//return our stringbuilder
return sb.ToString();
}
Here is the VB.NET version of for part 1:
Private Sub createButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles createButton.Click
'create the command object for the connection to the database
'also assign the sql statement to be use in querying our database
Dim command As SqlCommand = New SqlCommand(sqlStatementField.Text, New SqlConnection(connectionStringField.Text))
'open the connection
command.Connection.Open()
'create the sqlreader that would hold the result set
Dim reader As SqlDataReader = command.ExecuteReader()
'set the conttent type of the file to be downloaded
Response.ContentType = "Application/x-msexcel"
'add the response headers
Response.AddHeader("content-disposition", "attachment; filename=""" & filenameField.Text & """")
'add the contents of the result set to the response stream
Response.Write(ToCSV(reader))
'close the connection
command.Connection.Close()
'end the http response
Response.End()
End Sub
Public Function ToCSV(ByVal iDr As IDataReader) As String
'declare the stringbuilder object that would serve as the placeholder for our data
Dim sb As StringBuilder = New StringBuilder
'check if there are columns on the reader
If (iDr.FieldCount <> 0) Then
'loop to the first column to the last column of our reader
For i As Integer = 0 To iDr.FieldCount - 1
'check to see if we are on the last column
'this is necessary so that we could append the nextline symbol
If (i < iDr.FieldCount - 1) Then
'if not the last column, append the column name and a ','
sb.Append(iDr.GetName(i) & ",")
Else
'append the last column name then append a carriage return and line feed
sb.Append(iDr.GetName(i))
sb.Append(vbCrLf)
End If
Next
'loop until the record reaches eol(end of line)
Do While (iDr.Read())
'loop thru each column, this is necessesary so that we could map
'the columns to their proper place
For i As Integer = 1 To iDr.FieldCount - 1
'check to see if we are on the last column
'this is necessary so that we could append the nextline symbol
If (i < iDr.FieldCount - 1) Then
'if not the last column, append the column value and a ','
sb.Append(iDr.GetValue(i) & ",")
Else
'append the last column value then append a carriage return and line feed
sb.Append(iDr.GetValue(i))
sb.Append(vbCrLf)
End If
Next
Loop
End If
'return our stringbuilder
Return sb.ToString()
End Function
Here is the solution for both C# and VB.NET projects
CreateCSVCS.zip (20.05 KB)
CreateCSVVB.zip (21.06 KB)
On part 2, i'm going to show you how to use DataTables instead of DataReaders toAccomplish this task.