In Part 1 of this series i have showed to you how to use a IDataReader in creating a function that would generate a CSV file. In part 2 of this series I'm going to show you how to accomplish the same task but this time using a different object. We will still implement the same concept that we used in Part 1 but shortening code by using native properties of a DataTable in accessing rows and columns.
You can get the running solution for both of this examples here:
C# Version: CreateCSVCSpart2.zip (20.37 KB)
VB.NET Version: CreateCSVVBpart2.zip (21.21 KB)
A listing for the C# source code of our modified project 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 sqldataadapter that would hold the result set
SqlDataAdapter sqlAdapter = new SqlDataAdapter(command);
//create our datatable
DataTable dataTable = new DataTable();
//fill the datatable with the values fetched from our query
sqlAdapter.Fill(dataTable);
//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 and generate our csv file
Response.Write( ToCSV(dataTable) );
//close the connection
command.Connection.Close();
//end the http response
Response.End();
}
private string ToCSV(DataTable dataTable)
{
//create the stringbuilder that would hold our data
StringBuilder sb = new StringBuilder();
//check if there are columns in our datatable
if(dataTable.Columns.Count != 0)
{
//loop thru each of the columns so that we could build the headers
//for each field in our datatable
foreach(DataColumn column in dataTable.Columns)
{
//append the column name followed by our separator
sb.Append(column.ColumnName + ',');
}
//append a carriage return
sb.Append("\r\n");
//loop thru each row of our datatable
foreach(DataRow row in dataTable.Rows)
{
//loop thru each column in our datatable
foreach(DataColumn column in dataTable.Columns)
{
//get the value for tht row on the specified column
// and append our separator
sb.Append(row[column].ToString() + ',');
}
//append a carriage return
sb.Append("\r\n");
}
}
//return our values
return sb.ToString();
}
Here is the Vb.Net version of code listing:
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 sqldataadapter that would hold the result set
Dim sqlAdapter As SqlDataAdapter = New SqlDataAdapter(command)
'create our datatable
Dim dataTable As DataTable = New DataTable
'fill the datatable with the values fetched from our query
sqlAdapter.Fill(dataTable)
'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 and generate our csv file
Response.Write(ToCSV(dataTable))
'close the connection
command.Connection.Close()
'end the http response
Response.End()
End Sub
Public Function ToCSV(ByVal dataTable As DataTable) As String
'create the stringbuilder that would hold our data
Dim sb As StringBuilder = New StringBuilder
'check if there are columns in our datatable
If (dataTable.Columns.Count <> 0) Then
'loop thru each of the columns so that we could build the headers
'for each field in our datatable
For Each column As DataColumn In dataTable.Columns
'append the column name followed by our separator
sb.Append(column.ColumnName & ",")
Next
'append a carriage return
sb.Append(vbCrLf)
'loop thru each row of our datatable
For Each row As DataRow In dataTable.Rows
'loop thru each column in our datatable
For Each column As DataColumn In dataTable.Rows
'get the value for tht row on the specified column
'and append our separator
sb.Append(row(column).ToString() & ",")
Next
'append a carriage return
sb.Append(vbCrLf)
Next
End If
Return sb.ToString()
End Function
Please take note that we have change the object being pass to our function. The code looks almost the same as the code in part one except that on this example we are not looping to each object using mapped indexes but we are now looping thu each object based on thgeir defined position on the DataTable.
You can get the running solution for both of this examples here:
C# Version: CreateCSVCSpart2.zip (20.37 KB)
VB.NET Version: CreateCSVVBpart2.zip (21.21 KB)
On part 3 of this series, we would be using a DataGrid in creating a CSV file.