[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Creating a soft copy report from a database connected to an asp.net website?

Posted on 2011-10-07
2
Medium Priority
?
378 Views
Last Modified: 2012-05-12
Hi,

I want to design a asp.net website that is connected to a database. What's the easist way to export information fom the tables or queries into a soft copy report like Excel or text file?

Thanks.
0
Comment
Question by:WhyDidntItWork
2 Comments
 
LVL 10

Accepted Solution

by:
P1ST0LPETE earned 2000 total points
ID: 36935058
This is the quick and easy approach.  Note you have to provide the database IP address, database name, username and password into this line:

command.Connection = new SqlConnection("Server=192.168.1.1;Database=DatabaseName;User ID=sa;Password=xxxx;");

Html Markup:
 
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="WebForm2.aspx.cs" Inherits="WebApplication4.WebForm2" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:Button ID="btnExportData" runat="server" Text="Export Data" OnClick="ExportData_Click" />
    </div>
    </form>
</body>
</html>

Open in new window



Code Behind:
 
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Text;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

namespace WebApplication4
{
    public partial class WebForm2 : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {

        }

        protected void ExportData_Click(object sender, EventArgs e)
        {
            DataTable table = new DataTable();

            using (SqlCommand command = new SqlCommand())
            {
                try
                {
                    command.Connection = new SqlConnection("Server=192.168.1.1;Database=DatabaseName;User ID=sa;Password=xxxx;");
                    command.CommandText = "SELECT * FROM [Table] ORDER BY [Column]";
                    command.Connection.Open();

                    SqlDataAdapter adapter = new SqlDataAdapter(command);
                    adapter.Fill(table);
                }
                catch(Exception ex)
                {
                    //error happened while getting data:
                    string error = ex.ToString();
                }
            }

            GridView grid = new GridView();
            grid.DataSource = table;
            grid.DataBind();

            StringWriter stringWriter = new StringWriter();
            HtmlTextWriter htmlWriter = new HtmlTextWriter(stringWriter);
            grid.RenderControl(htmlWriter);

            Response.Clear();
            Response.AddHeader("content-disposition", "attachment; filename=ExcelFileName.xls");
            Response.Charset = "";
            Response.Cache.SetCacheability(HttpCacheability.NoCache);
            Response.ContentType = "application/excel";
            Response.Write(stringWriter.ToString());
            Response.Flush();
        }
    }
}

Open in new window



Also, see these other links for further info and examples:
- http://forums.asp.net/t/1185493.aspx
- http://msdn.microsoft.com/en-us/library/as2f1fez.aspx
- http://attemptsatprogramming.blogspot.com/2011/08/sending-dynamic-file-to-client-in.html
- http://www.codeproject.com/KB/webforms/DAtaGridExportToExcel.aspx
- http://www.dotnetspider.com/forum/168566-How-Export-DataTable-Values-Excel.aspx
0
 

Author Closing Comment

by:WhyDidntItWork
ID: 36936463
Wow! Thanks for the detailed response. I should have given more guidance as I wasn't initially looking for code example.  At this point, I was just looking for a sense of general direction so I can do more investigating of my own.  However, you read my mind and was able to push me in the right direction.  Much appreciated for the help.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

One of the pain points with developing AJAX, JavaScript, JQuery, and other client-side behaviors is that JavaScript doesn’t allow for cross domain request for pulling content. For example, JavaScript code on www.johnchapman.name could not pull conte…
ASP.Net to Oracle Connectivity Recently I had to develop an ASP.NET application connecting to an Oracle database.As I am doing it first time ,I had to solve several problems. This article will help to such developers  to develop an ASP.NET client…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…
Suggested Courses
Course of the Month18 days, 16 hours left to enroll

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question