| 
  • If you are citizen of an European Union member nation, you may not use this service unless you are at least 16 years old.

View
 

Topic - Creating a Single Table Database Object Storage

Page history last edited by Dr. Ron Eaglin 6 years, 7 months ago

 Topic - Creating a Single Table Database Object Storage

 

 


 

In this series of lectures we create a database table that can store objects created in C#. All storage of all objects is done with a single table, denoted in the code. This series of lectures uses reflection, inheritance, XML, and database classes to achieve the final results. Interface code capable of allowing the user to edit, retrieve, and store these objects is presented. The creation of the XML representation of the class is very similar to XML serialization, which is a built-in capability of C#.

 

The objects created in this series are the XmlPropertyObject and the DatabaseXmlStore (which I convert to the ObjectDatabase in later lectures)

 

Reading

 

Required Reading

 

How this works - Here is the straight scoop. I have created a class called XmlPropertyObject. Objects that you create that inherit from this class have the ability to save themselves to a single database table. A class called  DatabaseXmlStore manages this saving automatically. You do have to have a database to store to - and it does have to have the table (in the comments of DatabaseXmlStore) created in it. The lectures take you through the details of how this all works.

 

Reference Reading

 

Reflection - http://msdn.microsoft.com/en-us/library/ms173183%28v=vs.80%29.aspx 

XmlDocument Class - http://msdn.microsoft.com/en-us/library/system.xml.xmldocument.aspx 

Xml Serilization - http://support.microsoft.com/kb/815813 

Database Class - http://msdn.microsoft.com/en-us/library/system.data.datatable.aspx 

 

Step by Step Setting up Hosting and Deploying a Project in Azure (we do this in these lectures, so setup is important) - http://msdn.microsoft.com/en-us/library/windowsazure/hh694045%28v=vs.103%29.aspx

 

Supporting Lectures

 

Please note that you should watch the entire series of lectures before creating and using these object classes. A solid understanding of the structure is important to the ability to use the classes effectively.

 

Lecture - XML-Reflection-Inheritance

 

Lecture - XML-Reflection-Inheritance Part 2

 

Lecture - XML-Reflection-Inheritance Part 3

 

Lecture - XML-Refelction-Inheritance Part 4

 

These classes are used in Lecture - Use of a Dictionary for Property Labels

 

A step by step guide to using this object is at Getting Started - Using the XmlPropertyObject for COP4834

 

A video to help you get started is at - Step by Step - Using the XmlPropertyObject

 

A second part that shows how to create a form and use the XmlPropertyObject - Lecture - Creating a Form Using XmlPropertyObject

 

The code of version 2 of the object is here - XmlPropertyObject(2).cs

 

Associated Assignments

 

COP4834 Summer 2013 Assignment 2

COP4834 Spring 2014 Assignment 2

 

Support Materials

 

The Code below are the complete classes to reproduce the code in the video segments with all code items completed and refactored.

 

Using Statements

 

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;
using System.Configuration; 

 

Interface IXmlPropertyObject -

 

An interface defines what properties and methods must be in a class. It is not the class itself. Interfaces are incredibly useful as they allow you to create multiple classes that can be used by other classes. For example suppose you want to create a separate class for a truck and a car. Now say you create a 3rd class called DrivingManager - that controls the driving of the car or truck. Without interfaces you would have to create a separate set of methods in DrivingManager for cars and trucks. With interfaces you can create one set of methods for IVehicle that accept classes that meet the requirements of IVehicle. As long as both cars and trucks implement IVehicle - they will both be usable by DrivingManager. 

 

    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);
    } 

 

Class XmlPropertyObject-

 

This class is a class that allows you to create classes that inherit from it and then they are automatically saved when you use the Insert, command. You will want to watch the video to understand how this class works. It is very useful. 

 

    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 string Insert()
        {
            return DatabaseXmlStore.Insert(this);
        }
        public string Update()
        {
            if (id == 0)
                return Insert();
            else
                return DatabaseXmlStore.Update(this);
        }
    }
 

 

 

Class DatabaseXmlStore -

 

This is the class that manages putting XmlPropertyObject in the database. It relies on an ConnectionString named ObjectDatabase to be in the web.config file. This database must be of the structure noted in the comments of this class. I keep the database structure in the comments so that if I make changes to the structure (which I do for some projects) the database and the class managing it are always in synch 

 

    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 = System.Configuration.ConfigurationManager.ConnectionStrings["ObjectDatabase"].ConnectionString;
             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;
                }
            }
        } // End Delete
        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;
                }
            }
        } // End Update
    } // End Class 

 

 

Pages Using XmlPropertyObject

 

These are Pages that us XmlPropertyObject these pages are demonstrated in the video segments of this class.

 

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:LinkButton ID="lbNewObject" runat="server" OnClick="lbNewObject_Click">Add A New Object</asp:LinkButton>
    <br /><br />
        <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");
            }
        }
        protected void lbNewObject_Click(object sender, EventArgs e)
        {
            Session.Add("SamplePropertiesInheritedID", null);
            Response.Redirect("ClassAsXML.aspx");
        }
    }
} 

 

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="Display XML From Properties" 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 as New to Database" OnClick="btnSave_Click" />
    <asp:Button ID="btnUpdate" runat="server" Text="Update Current Entry" OnClick="btnUpdate_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)
        {
            if (!IsPostBack)
            {
                SamplePropertiesInherited s = new SamplePropertiesInherited();
                if (Session["SamplePropertiesInheritedID"] != null)
                {
                    s.setID(Convert.ToInt32(Session["SamplePropertiesInheritedID"]));
                    tbName.Text = s.Name;
                    tbDescription.Text = s.Description;
                    tbContent.Text = s.Content;
                    tbResults.Text = s.asXML();
                    btnUpdate.Visible = true;
                }
                else
                {
                    btnUpdate.Visible = false;
                }
            }
        }
        protected void btnGo2_Click(object sender, EventArgs e)
        {
            // Fills in the XML Text Box
            SamplePropertiesInherited s = getSamplePropertiesInherited();
            tbResults.Text = s.asXML();
        }
        protected void btnSave_Click(object sender, EventArgs e)
        {
            SamplePropertiesInherited s = getSamplePropertiesInherited();
            lblValue.Text = s.Insert();
        }
        protected void btnUpdate_Click(object sender, EventArgs e)
        {
            SamplePropertiesInherited s = getSamplePropertiesInherited();
            lblValue.Text = s.Update();
        }
        private SamplePropertiesInherited getSamplePropertiesInherited()
        {
            return new SamplePropertiesInherited
            {
                id = Session["SamplePropertiesInheritedID"] == null ? 0 : Convert.ToInt32(Session["SamplePropertiesInheritedID"]),
                Name = tbName.Text,
                Description = tbDescription.Text,
                Content = tbContent.Text,
                Owner = Context.User.Identity.Name
            };
        } // end newSamplePropertiesInherited
    } 
} 

 

Back to List of All COP4834 Topics

 

Comments (0)

You don't have permission to comment on this page.