Hi, [Below is the almost full Code modified. Currently shows illegal character error when being read]. I have a C# ASP.NET application which is currently reading an xml file from the file system and then loading it into a GridView control. In the grid I can Delete rows. There is also an file upload button below the grid which upload PDF files and they show up in the grid. My code is basically a modified version of: http://sureshsharmaaspdotnet.wordpress.com/2008/07/04/editupdatedelete-in-gridview-using-xml-file/
The next stage of my work involves reading the xml data as String from a database field--instead of from the xml file. For that to happen, I think I can start out by just reading from the xml file, making changes in the aspx page, and the writing the 'dataset' into a database field called 'PDF_Storage'. How can I do that. Crucially, I need to be able to convert the dataset into some kind of string format for storage. Here is my code snippet.
My database is Oracle 10 but I can figure out the Update sql syntax.
Thanks!
SAMPLE XML FILE:
<DataSet><PDF><pdf>MyPDF1.pdf</pdf></PDF><PDF><pdf>MyPDF2.pdf</pdf></PDF><PDF><pdf>MyPDF3.pdf</pdf></PDF></DataSet>
using System;
using System.Data;
using System.Configuration;
using System.Collections;
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 Oracle.DataAccess.Client;
using System.Web.Configuration;
using System.IO;
using System.Xml;
using System.Text.RegularExpressions;
public partial class XMLGridTest : System.Web.UI.Page
{
public static string GetConnString()
{
return WebConfigurationManager.ConnectionStrings["ConnectionString"].ToString();
}
protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
binddata();
}
}
void binddata()
{
DataSet ds = new DataSet();
// ds.ReadXml(Server.MapPath("testxml.xml"));
String strConnect = GetConnString();
OracleConnection oracleConn = new OracleConnection();
oracleConn.ConnectionString = strConnect;
oracleConn.Open();
OracleCommand cmd = new OracleCommand();
cmd.Connection = oracleConn;
cmd.CommandType = CommandType.Text;
cmd.CommandText = "SELECT PDF_Storage FROM CampusDev.CU_POLY WHERE OBJECTID = " + Request.QueryString["OBJECTID"];
OracleDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
if (!reader.IsDBNull(0))
{
//## Line Below as the 'illegal characters' problem###
ds.ReadXml(reader[0].ToString(), XmlReadMode.IgnoreSchema);
gv.DataSource = ds;
gv.DataBind();
}
else
{
// Response.Write(reader.GetString(1));
// TextBox1.Text = reader.GetString(1);
}
}
// gv.DataSource = ds;//##Hard coded for XML. Works!
// gv.DataBind();
//Finally, close the connection
oracleConn.Close();
}
protected void Canceldata(object s, GridViewCancelEditEventArgs e)
{
gv.EditIndex = -1;
binddata();
}
protected void pageddata(object s, GridViewPageEventArgs e)
{
gv.PageIndex = e.NewPageIndex;
binddata();
}
protected void insert(object sender, EventArgs e)
{
/////////////////////////////////File Upload Code/////////////////////////////////
// Initialize variables
string sSavePath = "ParcelPDF/"; ;
if (fileupload.PostedFile == null)
{
Label1.Text = "Must Upload a PDF file!";
return;
}
HttpPostedFile myFile = fileupload.PostedFile;
int nFileLen = myFile.ContentLength;
// Check file extension (must be JPG)
if (System.IO.Path.GetExtension(myFile.FileName).ToLower() != ".pdf")
{
Label1.Text = "The file must have an extension of .pdf";
return;
}
// Read file into a data stream
byte[] myData = new Byte[nFileLen];
myFile.InputStream.Read(myData, 0, nFileLen);
// Make sure a duplicate file doesn’t exist. If it does, keep on appending an incremental numeric until it is unique
string sFilename = System.IO.Path.GetFileName(myFile.FileName);
int file_append = 0;
while (System.IO.File.Exists(Server.MapPath(sSavePath + sFilename)))
{
file_append++;
sFilename = System.IO.Path.GetFileNameWithoutExtension(myFile.FileName) + file_append.ToString() + ".pdf";
}
// Save the stream to disk
System.IO.FileStream newFile = new System.IO.FileStream(Server.MapPath(sSavePath + sFilename), System.IO.FileMode.Create);
newFile.Write(myData, 0, myData.Length);
newFile.Close();
binddata();
DataSet ds = gv.DataSource as DataSet;
DataRow dr = ds.Tables[0].NewRow();
// dr[0] = pdf.Text;
dr[0] = sFilename.ToString();
ds.Tables[0].Rows.Add(dr);
ds.AcceptChanges();
string blah = "blah";
Response.Write(ds.Tables.ToString());
// ds.WriteXml(Server.MapPath("testxml.xml"));
String strConnect = GetConnString();
OracleConnection oracleConn = new OracleConnection();
oracleConn.ConnectionString = strConnect;
oracleConn.Open();
OracleCommand cmd = new OracleCommand();
cmd.Connection = oracleConn;
cmd.CommandType = CommandType.Text;
// cmd.CommandText = "SELECT OBJECTID,COMMENTS FROM CampusDev.CU_POLY WHERE OBJECTID = " + Request.QueryString["OBJECTID"];
cmd.CommandText = "UPDATE CampusDev.CU_POLY SET PDF_Storage = :PDF_Storage WHERE OBJECTID = " + Request.QueryString["OBJECTID"];
StringWriter SW = new StringWriter();
ds.WriteXml(SW);
cmd.Parameters.Add(":PDF_Storage", SW.ToString());
cmd.ExecuteNonQuery();
oracleConn.Close();
binddata();
}
protected void Deletedata(object s, GridViewDeleteEventArgs e)
{
binddata();
DataSet ds = gv.DataSource as DataSet;
ds.Tables[0].Rows[gv.Rows[e.RowIndex].DataItemIndex].Delete();
// ds.WriteXml(Server.MapPath("testxml.xml"));//Disabled now. Do database. Irfan. 07/09/10
String strConnect = GetConnString();
OracleConnection oracleConn = new OracleConnection();
oracleConn.ConnectionString = strConnect;
oracleConn.Open();
OracleCommand cmd = new OracleCommand();
cmd.Connection = oracleConn;
cmd.CommandType = CommandType.Text;
cmd.CommandText = "UPDATE CampusDev.CU_POLY SET PDF_Storage = :PDF_Storage WHERE OBJECTID = " + Request.QueryString["OBJECTID"];
StringWriter SW = new StringWriter();
ds.WriteXml(SW,XmlWriteMode.IgnoreSchema);
Regex regex = new Regex(@"(\r\n|\r|\n)+");
string newText = regex.Replace(SW.ToString(), "");
cmd.Parameters.Add(":PDF_Storage", newText);
cmd.ExecuteNonQuery();
oracleConn.Close();
binddata();
string blah = "blah";
}