Live Chat Icon For mobile
Live Chat Icon

How to create a .csv file that grabs the data from the database

Platform: ASP.NET| Category: Files

<asp:Button id='Button1' runat='server' Text='Button'></asp:Button>

VB.NET


Dim cn As SqlConnection
Dim cmd As SqlCommand
Dim filename As String
Dim dr As SqlDataReader
Dim i As Integer
Dim sb As System.Text.StringBuilder

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
	cn = New SqlConnection('server=localhost;uid=sa;pwd=;database=northwind')
	filename = 'products.csv'
        	cmd = New SqlCommand('select * from products ', cn)
	cmd.Connection.Open()
        	dr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
	sb = New System.Text.StringBuilder
        
	’For field Names
        	For i = 0 To dr.FieldCount - 1
            		If i < (dr.FieldCount - 1) Then
                		sb.Append(Chr(34) & dr.GetName(i) & _
                			Chr(34) & ',')
            		Else
                		sb.Append(Chr(34) & dr.GetName(i) & _
                			Chr(34) & vbCrLf)
            		End If
        	Next
        
	’For field Values
        	While dr.Read()
            		For i = 0 To dr.FieldCount - 1
                		If i < (dr.FieldCount - 1) Then
                    			sb.Append(Chr(34) & _
                    				dr.GetValue(i).ToString & Chr(34) & ',')
                		Else
                    			sb.Append(Chr(34) & _
                    				dr.GetValue(i).ToString & Chr(34) & vbCrLf)
                		End If
            		Next
        	End While
        	dr.Close()
        	cn.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

C#


SqlConnection cn ; 
SqlCommand cmd ; 
string filename ; 
SqlDataReader dr ; 
System.Text.StringBuilder sb ; 

private void Button1_Click(object sender, System.EventArgs e)
{
	cn = new SqlConnection('server=localhost;uid=sa;pwd=;database=northwind');
	filename = 'products.csv';
	cmd = new SqlCommand('select * from products ', cn);
	cmd.Connection.Open();
	dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
	sb = new System.Text.StringBuilder();

	//For field Names
	for(int i = 0 ;i<= dr.FieldCount - 1;i++)
	{
		if( i < (dr.FieldCount - 1) )
		{
			sb.Append('\t' + dr.GetName(i) + '\t' + ',');
		}
		else
		{
			sb.Append('\t' + dr.GetName(i) + '\t'  + '\n');
		}
	}

	//For field Values
	while( dr.Read())
	{
		for(int i = 0 ;i<= dr.FieldCount - 1;i++)
		 {
			 if( i < (dr.FieldCount - 1) )
			 {
				 sb.Append('\t' + dr.GetValue(i) + '\t' + ',');
			 }
			 else
			 {
				 sb.Append('\t' + dr.GetValue(i) + '\t'  + '\n');
			 }
		 }
	}
	 dr.Close();
	 cn.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();
}

Share with

Share on twitter
Share on facebook
Share on linkedin

Related FAQs

Couldn't find the FAQs you're looking for?

Please submit your question and answer.