Hi, Please see the code below. What I am trying to do is to store some PDF file names in XML format inside a database 'PDF_Storage' field. The code kind of works but is not polished enough: If there is no data for a row then I am putting a hard-coded xml file and then manipulating (Uploading new PDF; Deleting PDF) using a GridView control. The end result is that, if there is no data in a field I am reading an string variable with some junk file name and then showing that inside the GridView. It would be nice to not have to do that: If no data in the database then don't show the default PDF file name. Note, I will need this XML structure to be read in another application and so I've got to have the XML store the PDF file names in the format as in the code below. I think someone who knows the ASP .NET Dataset well may be able to help me. Thanks!
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;
//Per http://sureshsharmaaspdotnet.wordpress.com/2008/07/04/editupdatedelete-in-gridview-using-xml-file/ // 070810 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 = "SELECTMMM";
OracleDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
if (!reader.IsDBNull(0) && reader[0].ToString().Length > 20)
// if (!reader.IsDBNull(0))
{
// ds.ReadXml(reader[0].ToString(), XmlReadMode.IgnoreSchema);
ds.ReadXml(new StringReader(reader[0].ToString()));
gv.DataSource = ds;
string blah = "blah";
gv.DataBind();
}
else
{
//Insert a Placeholder PDF file to retain the schema
string tempxml = "<DataSet><PDF><pdf>PLACEHOLDER_IGNORE.pdf</pdf></PDF></DataSet>";
OracleCommand cmd2 = new OracleCommand();
cmd2.Connection = oracleConn;
cmd2.CommandType = CommandType.Text;
cmd2.CommandText = "UPDATE ...."];
cmd2.Parameters.Add(":PDF_Storage", tempxml);
cmd2.ExecuteNonQuery();
ds.ReadXml(new StringReader(tempxml));
gv.DataSource = ds;
gv.DataBind();
}
}
// gv.DataSource = ds;
// 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 = new DataSet();
DataSet ds = gv.DataSource as DataSet;
DataRow dr = ds.Tables[0].NewRow();
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 = "UPDATE ...."];
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();
}
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. 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..."];
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";
}
protected void Button1_Click(object sender, EventArgs e)
{
Response.Redirect("index.aspx");
}
protected void showPDFFile(object sender, EventArgs e)
{
// Response.Write(((LinkButton)sender).Text);
Response.Redirect("~/ParcelPDF/" + ((LinkButton)sender).Text);
string blah = "blah";
}
}