File Upload

I am trying to make this work this time. I mean I'm going to post the whole lab.

Downloading is to load something from server to your computer while uploading is to load something, like a data file, from your computer to some server. In this lab, user will click a link to open a excel file, enter data, save to some disk on local computer. Click browse button to browse the saved file and then click the upload button to insert the entered data as a string into some cells in sql server database table on some server.

The tricky part for me is to read data from excel file(.xls): select * from [Sheet1$]. this [Sheet1$] is not the name of the .xls file. It is "Sheet1" at the bottom left of the excel file, below all the rows. There is also a "Sheet2" and "Sheet3" besides it. This is important. Append it with "$" and put them in []. If you use the name of the .xls file, database engine can't find the object(the table).

The other tricky part is: the message they want to insert into the cell contains single quotes " ' ". Use the one below "~" instead the one below double quotes " " ".

1. In visual studio 2005, create website.
2. Open Default.aspx file. Drag a FileUpload control, a link and button to the page.

3. Failed to post source(html) code, sorry. But,set button ID = btnUpload, OnClick="btnUpload_Click"
set FileUploadID="FileUpload1", set link to .xls file.

4. Open microsoft office-excel. Type some column names and save it to the project folder.
5. Right click the project-add new item-class file(.cs) and save it to App_Code folder.
this is the Class1.cs file:
using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.OleDb;
using System.Text;
using System.Data.SqlClient;


public class Class1
{
protected string _xlsFile;
protected string _sqlConnection;
protected string _xlsConnection;


public Class1(string uploadFile)
{
_xlsFile = uploadFile;
_sqlConnection = "Data Source=Database Server (localhost);Initial Catalog=databasename;Integrated Security=True;";
_xlsConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR=YES;'";
_xlsConnection = string.Format(_xlsConnection.ToString(), _xlsFile);
}
public void Upload()
{

int timeOut = int.Parse("90");

try
{
string str;
StringBuilder sb = new StringBuilder();
//use excel connection, read excel data and append them as string.
using (OleDbConnection Ocn = new OleDbConnection(_xlsConnection))
{

string sql = "select * from [Sheet1$]";

OleDbCommand Ocmd = new OleDbCommand(sql, Ocn);
Ocn.Open();

using (OleDbDataReader Ord = Ocmd.ExecuteReader())
{


while (Ord.Read())
{
//I use only 2 columns as an example. Column names are
//NUMBER and STATUS
//Ord["NUMBER"] to retrieve value of column
// named "NUMBER"

sb.Append("_NUMBER=");
sb.AppendFormat("`{0}` ", Ord["NUMBER"]);

sb.Append("_STATUS=");
sb.AppendFormat("`{0}` ", Ord["STATUS"]);


}


}


}

//use sql connection, insert data into sql server database table
using (SqlConnection Scn = new SqlConnection(_sqlConnection ))
{
str = sb.ToString();

//you should have a database table in sql server database and use
//the real column names.
string sql = "insert sqlDatabaseTableName(ColumnName1, ColumnName2) values ('ZZZ', '"+str+"')";

SqlCommand Scmd = new SqlCommand(sql, Scn);

Scn.Open();
Scmd.ExecuteNonQuery();
}
}
catch (Exception ex)
{

throw new Exception("Upload Failed", ex);

}


}
}


6. This is the Default page code behind page Default.aspx.cs:
using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.IO;

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

}
protected void btnUpload_Click(object sender, EventArgs e)
{
//check if has file
if (FileUpload1.HasFile)
{
//create a temperary file and return the path. append ".xls" and
//save file content to server.
string file = System.IO.Path.GetTempFileName();
file = file + ".xls";
FileUpload1.SaveAs(file);

//create intance of the class so that to use its functionalities
Class1 ZClass = new Class1(file) ;

try
{
ZClass.Upload();
}
catch(Exception ex)
{
Response.Write(ex);
//throw;
}


}
}
}