XML-Reflection-Inheritance Part 4
Prerequisites
Part of Topic – Creating a Single Table Database Object Storage
Lecture – XML-Reflection-Inheritance Part 3
Summary
Topics Covered in this Video;
-SelectClassAsXml
-ClassAsXml
-ItemTemplate
-s.SelectAll
-e.CommandArgument
-SetPropertiesFromXml
-Returning rows of XML Data
-Saving vs Updating
-Gridview on Page
-Edit Buttons and Session Variables
Video
Reference Materials
Classes XmlPropertyObject, XmlDataStore
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Reflection;
using System.Xml.Linq;
using System.Data;
using System.Data.SqlClient;
namespace DatabaseSample1.App_Code
{
public interface IXmlPropertyObject
{
// Required Properties
int id { get; set; }
string Name { get; set; }
string Description { get; set; }
string Owner { get; set; }
// Required Methods
string asXML();
string className();
string getPropertyFromXml(string property, string xml);
string getPropertyValue(string name);
void setID(int ID);
void setPropertyFromXml(string property, string xml);
void setProperty(string name, string value);
void setPropertiesFromXml(string xml);
}
public class XmlPropertyObject : IXmlPropertyObject
{
public int id {get; set; }
public string Name { get; set; }
public string Description { get; set; }
public string Owner { get; set; }
public XmlPropertyObject(int ID)
{
id = ID;
Select(ID);
}
public XmlPropertyObject() { }
public string asXML()
{
string s = String.Empty;
string fc = className();
while (fc.Contains("."))
{
fc = fc.Substring(fc.IndexOf(".") + 1);
}
s += "<" + fc + ">\n";
foreach (var property in this.GetType().GetProperties())
{
s += " <" + property.Name + ">";
s += Convert.ToString(property.GetValue(this));
s += "</" + property.Name + ">";
s += "\n";
}
s += "</" + fc + ">";
return s;
}
public string className()
{
string fc = this.GetType().ToString();
while (fc.Contains("."))
{
fc = fc.Substring(fc.IndexOf(".") + 1);
}
return fc;
}
public string getPropertyFromXml(string property, string xml)
{
XDocument doc = XDocument.Parse(xml);
// Gets the value fom XML
try {return doc.Root.Element(property).Value; }
catch {return String.Empty; }
}
public void setID(int ID)
{
id = ID;
Select(ID);
}
public void setPropertyFromXml(string property, string xml)
{
string value = getPropertyFromXml(property, xml);
setProperty(property, value);
}
public string getPropertyValue(string name)
{
PropertyInfo pi = this.GetType().GetProperty(name, BindingFlags.Public | BindingFlags.Instance);
return Convert.ToString(pi.GetValue(this));
}
public void setProperty(string name, string value)
{
PropertyInfo pi = this.GetType().GetProperty(name, BindingFlags.Public | BindingFlags.Instance);
if (null != pi && pi.CanWrite)
{
pi.SetValue(this, Convert.ChangeType(value,pi.PropertyType), null);
}
}
public void setPropertiesFromXml(string xml)
{
XDocument doc = XDocument.Parse(xml);
foreach (XElement xe in doc.Root.Elements())
{
setProperty(xe.Name.ToString(), xe.Value);
}
}
public void Select(int ID)
{
DataTable dt = DatabaseXmlStore.Select(ID);
if (dt.Rows.Count == 0) return;
setPropertiesFromXml(dt.Rows[0]["XmlData"].ToString());
id = ID;
}
public DataTable SelectAll(string ownerID)
{
return DatabaseXmlStore.SelectAll(ownerID, className());
}
}
public class DatabaseXmlStore
{
// Allows for the storage and retrieval of objects from the database
// That conform to IXmlPropertyObject
/*
*
CREATE TABLE XmlObject (
id INT PRIMARY KEY IDENTITY(1,1),
Class VARCHAR(255) NOT NULL,
Name VARCHAR(255) NULL,
Description VARCHAR(500) NULL,
XMLData VARCHAR(MAX) NULL,
OwnerUserID VARCHAR(255) NOT NULL,
LastEditedDate DATETIME)
* *
*/
// Properties
public static SqlConnection Connection()
{
// This should be read from the Web.config
string cs = @"Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=COP4834ObjectStorage;Data Source=A30609\EAGLIN";
return new SqlConnection(cs);
}
#region Insert
public static string Insert(IXmlPropertyObject o)
{
string objectName = o.className();
string sql = "INSERT INTO XmlObject ";
sql += "(Class, Name, Description, XMLData, OwnerUserID, LastEditedDate)";
sql += "VALUES";
sql += "(@Class, @Name, @Description, @XMLData, @OwnerUserID, GETDATE())";
SqlConnection connection = DatabaseXmlStore.Connection();
using (connection)
{
SqlCommand command = new SqlCommand(sql, connection);
command.Parameters.Add("@Class", SqlDbType.VarChar);
command.Parameters["@Class"].Value = o.className();
command.Parameters.Add("@Name", SqlDbType.VarChar);
command.Parameters["@Name"].Value = o.Name;
command.Parameters.Add("@Description", SqlDbType.VarChar);
command.Parameters["@Description"].Value = o.Description;
command.Parameters.Add("@XmlData", SqlDbType.VarChar);
command.Parameters["@XmlData"].Value = o.asXML();
command.Parameters.Add("@OwnerUserID", SqlDbType.VarChar);
command.Parameters["@OwnerUserID"].Value = o.Owner;
try
{
connection.Open();
Int32 rowsAffected = command.ExecuteNonQuery();
return "Data Inserted";
}
catch (Exception ex)
{
return ex.Message;
}
}
}
#endregion
#region Select
public static DataTable Select(int id)
{
string sql = "SELECT * FROM XmlObject ";
sql += "WHERE id = @ID ";
SqlConnection connection = DatabaseXmlStore.Connection();
using (connection)
{
SqlCommand command = new SqlCommand(sql, connection);
command.Parameters.Add("@ID", SqlDbType.Int);
command.Parameters["@ID"].Value = id;
try
{
DataTable dt = new DataTable();
connection.Open();
SqlDataAdapter sda = new SqlDataAdapter(command);
sda.Fill(dt);
return dt;
}
catch
{
return new DataTable();
}
}
}
public static DataTable SelectAll(string ownerID)
{
string sql = "SELECT * FROM XmlObject ";
sql += "WHERE OwnerUserId = @OwnerUserID ";
SqlConnection connection = DatabaseXmlStore.Connection();
using (connection)
{
SqlCommand command = new SqlCommand(sql, connection);
command.Parameters.Add("@OwnerUserID", SqlDbType.VarChar);
command.Parameters["@OwnerUserID"].Value = ownerID;
try
{
DataTable dt = new DataTable();
connection.Open();
SqlDataAdapter sda = new SqlDataAdapter(command);
sda.Fill(dt);
return dt;
}
catch
{
return new DataTable();
}
}
}
public static DataTable SelectAll(string ownerID, string objectClass)
{
string sql = "SELECT * FROM XmlObject ";
sql += "WHERE OwnerUserId = @OwnerUserID ";
sql += "AND Class = @Class";
SqlConnection connection = DatabaseXmlStore.Connection();
using (connection)
{
SqlCommand command = new SqlCommand(sql, connection);
command.Parameters.Add("@Class", SqlDbType.VarChar);
command.Parameters["@Class"].Value = objectClass;
command.Parameters.Add("@OwnerUserID", SqlDbType.VarChar);
command.Parameters["@OwnerUserID"].Value = ownerID;
try
{
DataTable dt = new DataTable();
connection.Open();
SqlDataAdapter sda = new SqlDataAdapter(command);
sda.Fill(dt);
return dt;
}
catch
{
return new DataTable();
}
}
}
#endregion
public static string Delete(IXmlPropertyObject o)
{
string sql = "DELETE FROM XmlObject ";
sql += "WHERE id = @id ";
SqlConnection connection = DatabaseXmlStore.Connection();
using (connection)
{
SqlCommand command = new SqlCommand(sql, connection);
command.Parameters.Add("@id", SqlDbType.Int);
command.Parameters["@id"].Value = o.id;
try
{
connection.Open();
command.ExecuteNonQuery();
return "Object Deleted";
}
catch(Exception ex)
{
return ex.Message;
}
}
}
public static string Update(IXmlPropertyObject o)
{
string objectName = o.className();
string sql = "UPDATE XmlObject ";
sql += "SET Class = @Class, ";
sql += "Name = @Name, ";
sql += "Description = @Description, ";
sql += "XMLData = @XmlData, ";
sql += "OwnerUserID = @OwnerUserID, ";
sql += "LastEditedDate = GETDATE()";
sql += "WHERE id = @id";
SqlConnection connection = DatabaseXmlStore.Connection();
using (connection)
{
SqlCommand command = new SqlCommand(sql, connection);
command.Parameters.Add("@Class", SqlDbType.VarChar);
command.Parameters["@Class"].Value = o.className();
command.Parameters.Add("@Name", SqlDbType.VarChar);
command.Parameters["@Name"].Value = o.Name;
command.Parameters.Add("@Description", SqlDbType.VarChar);
command.Parameters["@Description"].Value = o.Description;
command.Parameters.Add("@XmlData", SqlDbType.VarChar);
command.Parameters["@XmlData"].Value = o.asXML();
command.Parameters.Add("@OwnerUserID", SqlDbType.VarChar);
command.Parameters["@OwnerUserID"].Value = o.Owner;
command.Parameters.Add("@id", SqlDbType.Int);
command.Parameters["@id"].Value = o.id;
try
{
connection.Open();
Int32 rowsAffected = command.ExecuteNonQuery();
return "Data Updated";
}
catch (Exception ex)
{
return ex.Message;
}
}
}
}
public class SamplePropertiesInherited : XmlPropertyObject
{
public string Content { get; set;}
}
} ClassAsXml.aspx
<%@ Page Title="" Language="C#" MasterPageFile="~/Site.Master" AutoEventWireup="true" CodeBehind="ClassAsXML.aspx.cs" Inherits="DatabaseSample1.Pages.ClassAsXML" ValidateRequest="false" %>
<asp:Content ID="Content1" ContentPlaceHolderID="HeadContent" runat="server">
</asp:Content>
<asp:Content ID="Content2" ContentPlaceHolderID="FeaturedContent" runat="server">
Name: <asp:TextBox ID="tbName" runat="server"></asp:TextBox><br />
Description: <asp:TextBox ID="tbDescription" runat="server"></asp:TextBox><br />
Content: <asp:TextBox ID="tbContent" runat="server"></asp:TextBox><br />
<asp:Button ID="btnGo2" runat="server" Text="Fill Text Box" OnClick="btnGo2_Click" /><br />
<asp:TextBox ID="tbResults" runat="server" TextMode="MultiLine" Height="200" Width="400"></asp:TextBox>
<br /><asp:Button ID="btnSave" runat="server" Text="Save to Database" OnClick="btnSave_Click" />
<asp:Label ID="lblValue" runat="server"></asp:Label>
<hr />
</asp:Content>
<asp:Content ID="Content3" ContentPlaceHolderID="MainContent" runat="server">
</asp:Content> ClassAsXml.aspx.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using DatabaseSample1.App_Code;
namespace DatabaseSample1.Pages
{
public partial class ClassAsXML : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
SamplePropertiesInherited s = new SamplePropertiesInherited();
if (Session["SamplePropertiesInheritedID"] != null)
{
int ID = Convert.ToInt32(Session["SamplePropertiesInheritedID"]);
s.setID(ID);
tbName.Text = s.Name;
tbDescription.Text = s.Description;
tbContent.Text = s.Content;
tbResults.Text = s.asXML();
}
}
protected void btnGo2_Click(object sender, EventArgs e)
{
SamplePropertiesInherited s = new SamplePropertiesInherited
{
Name = tbName.Text,
Description = tbDescription.Text,
Content = tbContent.Text
};
tbResults.Text = s.asXML();
}
protected void btnSave_Click(object sender, EventArgs e)
{
SamplePropertiesInherited s = new SamplePropertiesInherited();
s.setPropertiesFromXml(tbResults.Text);
s.Owner = Context.User.Identity.Name;
lblValue.Text = DatabaseXmlStore.Insert(s);
}
}
} SelectClassAsXml.aspx
<%@ Page Title="" Language="C#" MasterPageFile="~/Site.Master" AutoEventWireup="true" CodeBehind="SelectClassAsXml.aspx.cs" Inherits="DatabaseSample1.Pages.SelectClassAsXml" %>
<asp:Content ID="Content1" ContentPlaceHolderID="HeadContent" runat="server">
</asp:Content>
<asp:Content ID="Content2" ContentPlaceHolderID="FeaturedContent" runat="server">
<asp:GridView ID="gvData" runat="server"
CellPadding="4" ForeColor="#333333" GridLines="None" OnRowCommand="gvData_RowCommand">
<AlternatingRowStyle BackColor="White" />
<Columns>
<asp:TemplateField HeaderText="Select">
<ItemTemplate>
<asp:LinkButton ID="lbSelect" runat="server" CommandName="lbSelect" Text="Edit" CommandArgument='<%# Eval("id") %>' />
</ItemTemplate>
</asp:TemplateField>
</Columns>
<FooterStyle BackColor="#990000" Font-Bold="True" ForeColor="White" />
<HeaderStyle BackColor="#990000" Font-Bold="True" ForeColor="White" />
<PagerStyle BackColor="#FFCC66" ForeColor="#333333" HorizontalAlign="Center" />
<RowStyle BackColor="#FFFBD6" ForeColor="#333333" />
<SelectedRowStyle BackColor="#FFCC66" Font-Bold="True" ForeColor="Navy" />
<SortedAscendingCellStyle BackColor="#FDF5AC" />
<SortedAscendingHeaderStyle BackColor="#4D0000" />
<SortedDescendingCellStyle BackColor="#FCF6C0" />
<SortedDescendingHeaderStyle BackColor="#820000" />
</asp:GridView>
</asp:Content>
<asp:Content ID="Content3" ContentPlaceHolderID="MainContent" runat="server">
</asp:Content> SelectClassAsXml.aspx.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using DatabaseSample1.App_Code;
namespace DatabaseSample1.Pages
{
public partial class SelectClassAsXml : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
SamplePropertiesInherited s = new SamplePropertiesInherited();
gvData.DataSource = s.SelectAll(Context.User.Identity.Name);
gvData.DataBind();
}
protected void gvData_RowCommand(object sender, GridViewCommandEventArgs e)
{
int ID = Convert.ToInt32(e.CommandArgument);
if (e.CommandName == "lbSelect")
{
Session.Add("SamplePropertiesInheritedID", ID);
Response.Redirect("ClassAsXML.aspx");
}
}
}
}