Redo File Upload

This time I use xmltextwriter to write xml file and convert it into a string( you can also write to a file). I also create dataset and generate xml file with schema or without. I also use use XPathNodeIterater to select a node and use XPathNavigator to move to and write out all the attributes. Pretty much fun. I will post the later 2 some other time. Again, "MCSD xml web services and server component" is a good book to start xml and web service with. A lot of web search is very helpful.

In this lab, I also move connection string to web.config file. I will post this file too. I only made modification in between to create " key" and "value" pair(here "name" and "connectionString"). So that you call "ConfigurationManager.ConnectionStrings ["SQLConnectionString"].ConnectionString;" to use the value.


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;
using System.Xml;
using System.Xml.XPath;
using System.IO;

///

/// Summary description for Class1
///

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


public ZUpload(string uploadFile)
{
_xlsFile = uploadFile;
_sqlConnection = ConfigurationManager.ConnectionStrings ["SQLConnectionString"].ConnectionString;
_xlsConnection = ConfigurationManager.ConnectionStrings["OLEDBConnectionString"].ConnectionString;
_xlsConnection = string.Format(_xlsConnection, _xlsFile);
}
public void Upload()
{
int timeOut = 90;


try
{
string str;
StringBuilder sb = new StringBuilder();
StringWriter sw = new StringWriter();
using (OleDbConnection Ocn = new OleDbConnection(_xlsConnection))
{

XmlTextWriter tWriter = new XmlTextWriter(sw);
//tWriter.WriteStartDocument();
tWriter.WriteRaw("");
tWriter.WriteStartElement("SUSPENSE_ITEMS");


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

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

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

while (Ord.Read())
{
tWriter.WriteStartElement("Order");
tWriter.WriteAttributeString(" _NUMBER", Ord["NUMBER"].ToString());
tWriter.WriteAttributeString(" _STATUS", Ord["STATUS"].ToString());
tWriter.WriteAttributeString(" _RUSH", Ord["RUSH"].ToString());
//Ord["NUMBER"] is the way to retrieve data in column name NUMBER.
}

}
tWriter.WriteEndElement();
sw.ToString();
tWriter.Flush();
tWriter.Close();
sw.Flush();


}
using (SqlConnection Scn = new SqlConnection(_sqlConnection))
{

string sql = "insert TableName(FileName, FileContent) values ('whatever', '" + sw + "')";
SqlCommand Scmd = new SqlCommand(sql, Scn);

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

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

}


}
}



Here is the configuration file:
OMG! can't post configuration file! Well, I only make changes in between connectionStrings tags.


//put these in between<   />
add name="SQLConnectionString" connectionString="Data Source=servername;Initial Catalog=databasename;Integrated Security=True"
   providerName="System.Data.SqlClient"

//put these in between<   />
    add name="OLEDBConnectionString" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR=YES;'"
   providerName="System.Data.OLEDB"