Exporting database to client

hans · March 26th, 2009, 11:15 AM

Hi,

I want to give my users the opportunity to download part of my database (some fields are excluded for privacy reasons). After downloading they should be able to process the data as they please (could be importing into Excel or a statiscal package such SSPS or STATA). I've been searching the web for 5 days now, but I have no idea in what direction I should look in order to find a solution.
1. How can I write a file on a client computer?
2. Should I try to export SQL files or should it be text files?
3. Can I use C# or is it better to use another language?

thanks

Hans Koolschijn

Imar · March 26th, 2009, 11:58 AM

Hi Hans,

Is this related to the book Beginning ASP.NET 3.5? If not, please consider posting in a different category as more users will see you post.

There are many ways to do what you want, from simple to complex.

Simple would be: Query the data and while iterating over the result set, Response.Write the output (you may need to set the content type to text)

Or you could export to Excel. Search Google for Excel export ASP.NET for some ideas.

You could also use tools like Sql Server Integration Services which is designed for these tasks. However, that requires you to use SQL Server.

Finally, you could search Google for FileHelpers, a .NET class library for exporting objects to CSV. The project seems dead now, but the 2.0 version works reasonably well...

Imar

hans · March 26th, 2009, 06:32 PM

Hi Imar,

I guess you're right, I'd better post in the PRO category. However, I build my website by reading and rereading your book and googling.
As for my question, using Response.write might generate a lot of code and won't look very neat (or am I wrong).
I found a script for exporting to Excel, which doesn't look too complicated.
I also found some scripts using BulkCopy. But that seems to require a destination string. Do my users have to install SQL server in that case?

thanks

Hans

Imar · March 27th, 2009, 04:38 AM

Yes, bcp (or the .NET classes that enable the same functionality) work between instances of SQL Server only.

Why would outputting to the response be a lot of code? It can be as simple as:

Code:

 
while (myReader.read())
{
  Response.Write(string.Format("{0};{1}\r\n", myReader.GetString(0), myReader.GetString(1)));
}

Note: I am not saying it's the best thing to do, but it certainly is an option for simple exports.

Cheers,

Imar

hans · March 30th, 2009, 03:08 PM

Hi Imar,

the following code works:

Code:

    protected void Button1_Click(object sender, EventArgs e)
    {

string queryString = "SELECT achternaam, voornamen FROM Personen;";

      string connectionString = WebConfigurationManager.ConnectionStrings["ScaDatabaseConnectionString"].ConnectionString;

      using (SqlConnection connection = new SqlConnection(connectionString))
      {
        SqlCommand command = new SqlCommand(queryString, connection);
        connection.Open();

        SqlDataReader myReader = command.ExecuteReader();

        // Call Read before accessing data.

        while (myReader.Read())
        {
          StringBuilder myString = new StringBuilder("");
          for (int field = 0; field < myReader.FieldCount; field++)
          {
            
            if (!myReader.IsDBNull(field))
            {
              myString.Append(myReader[field]);
              myString.Append(";");
            }
            else
            {
              myString.Append(string.Empty);
              myString.Append(";");
            }
            
          }
          myString.Append("\r\n");
          Response.Write(myString);
        }
        // Call Close when done reading.
        myReader.Close();
      }
      Response.ContentType = "text/richtext";
      Response.AppendHeader("Content-Disposition", "attachment; filename=TextFile.txt");
      Response.End();
    }

but:
1. The file only contains values and not the field names.
2. I need to add a check for the variable type, which can be int or bool as well. I assume I can use GetType for this.

I also tried the code below. I assume bcp copies field names as well and the code is shorter (and maybe runs faster).
When running the code however. I get a syntax error near the '.' in 'cmd.ExecuteNonQuery();' at the end of the code.
Do you have any idea about what's wrong here? Or is this code a bad idea anyway?

Code:

    protected void Button1_Click(object sender, EventArgs e)
    {
string connectionString = WebConfigurationManager.ConnectionStrings["ScaDatabaseConnectionString"].ConnectionString;
          string sCommandText;

          sCommandText = "exec xp_cmdShell 'bcp.exe'" + "..Personen"+ " out " +
                           "C:\\Test\\personen.txt" + " -c -q -U " + " -P " + "-t  ";

          SqlConnection conn = new SqlConnection(connectionString);
          conn.Open();

          SqlCommand cmd = new SqlCommand();
          cmd.Connection = conn;
          cmd.CommandType = CommandType.Text;
          cmd.CommandText = sCommandText;

           cmd.ExecuteNonQuery();  
    }

thanks,

Hans

Imar · March 30th, 2009, 03:33 PM

You can use GetName to get the names of the columns and ouput them to StringBuilder: http://msdn.microsoft.com/en-us/libr...r.getname.aspx

Alternaticely, since you already know the fields (as they are in your SQL statement), you can directly add them.

With regards to xp_cmdShell: I don't think you want that. There are some serious security issues associated with incorrect use of the procedure and security configuration: http://www.google.com/search?hl=en&q...+security+hack

Cheers,

Imar

hans · April 1st, 2009, 03:53 PM

Hi Imar,

GetName works fine.
The only problem left is that when using

Response.AppendHeader("Content-Disposition", "attachment; filename=" + myFile);

I can only send just one file to the user, while I have 10. Is there a way to send the user multiple files or a folder containing those files?

thanks,

Hans

Imar · April 1st, 2009, 05:51 PM

Hi Hans,

Because of how HTTP works, you can't send multiple files from the same HTTP response.

You could zip them at the server and send the zip file as a single download.

Cheers,

Imar

hans · April 2nd, 2009, 03:49 PM

Dear Imar,

You really helped me out! The program works fine now. Using a zip file would have been the best solution I guess, but since I have only a few users and since they will only download one or a few files on a regular basis, I decided to use a radiobuttonlist and let them download the files one by one.

For anyone interested, I'll copy the code below.

Code:

// Part of the aspx file

    <tr><td>
      <asp:RadioButtonList ID="RadioButtonList1" runat="server">
      </asp:RadioButtonList>
      <asp:RadioButtonList ID="rblExport" runat="server">
      <asp:ListItem Value="personen">Personen Algemeen en Genetica</asp:ListItem>
      <asp:ListItem Value="celbiologie">Celbiologie</asp:ListItem>
      </asp:RadioButtonList></td></tr>

    <tr><td><asp:Button ID="btnSubmit" runat="server" onclick="btnSubmit_Click" 
      Text="Exporteer" /></td></tr>

// Code behind

    protected void btnSubmit_Click(object sender, EventArgs e)
    {

      foreach (ListItem item in rblExport.Items)
      {
        if (item.Selected == true)
        {
          string connectionString = WebConfigurationManager.ConnectionStrings["ScaDatabaseConnectionString"].ConnectionString;
          string filename = "";
          using (SqlConnection connection = new SqlConnection(connectionString))
          {
            string queryString = "";

            if (item.Value == "personen")
            {
              queryString = "SELECT achternaam, voornamen, vaderId FROM Personen;";
              filename = "Personen.txt";
            }
            else if (item.Value == "celbiologie")
            {
              queryString = "SELECT * FROM Celbiologie;";
              filename = "Celbiologie.txt";
            }

            SqlCommand command = new SqlCommand(queryString, connection);
            connection.Open();

            SqlDataReader myReader = command.ExecuteReader();

            StringBuilder myColString = new StringBuilder("");
            for (int field = 0; field < myReader.FieldCount; field++)
            {
              myColString.Append(myReader.GetName(field)); myColString.Append(";");
            }
            myColString.Append("\r\n");
            Response.Write(myColString);

            while (myReader.Read())
            {
              StringBuilder myString = new StringBuilder("");
              for (int field = 0; field < myReader.FieldCount; field++)
              {

                if (!myReader.IsDBNull(field))
                {
                  myString.Append(myReader[field]);
                  myString.Append(";");
                }
                else
                {
                  myString.Append(string.Empty);
                  myString.Append(";");
                }

              }
              myString.Append("\r\n");
              Response.Write(myString);
            }

            // Call Close when done reading.
            myReader.Close();
          }
 
          Response.ContentType = "text/richtext";
          Response.AppendHeader("Content-Disposition", "attachment; filename=" + filename);
          Response.End();
        }
      }
    }

thanks a lot

Hans

Imar · April 2nd, 2009, 03:53 PM

Great. Glad it's working and thanks for posting a follow up...

Imar