|
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"
|
|
|