Solved

SQL backed by Classes.

Posted on 2006-06-09
10
348 Views
Last Modified: 2008-02-01
I've got the following classes for backing my Database with C# classes in ASP.NET.
My problem is, I cannot figure out what to do with Foreign Keys. Any suggestions?
Listed below is the data backing class, and then a class that uses it to store and retrieve data. I'm not too happy with this model, if anyone has a better alternative, i'd love to know. The DataSet feature in Visual Studio 2005 is totally useless to me because I need to access my data OUTSIDE of a databound object like a GridView or a DetailsView, and that seems impossible with the TableAdapter/etc. Model that .NET 2.0 provides, so I've had to come up with this.

using System;
using System.Reflection;
using System.Configuration;

using System.Data;
using System.Data.SqlClient;

namespace System.Web.Data
{
      public abstract partial class DataProvider
      {
            // ================================================
            // Private Instance Fields
            // ================================================

            // Property Backer for the SQL Connection
            private SqlConnection __connection = null;

            // Property Backer for the SQL Query
            private SqlCommand __sqlcommand = new SqlCommand();

            // ================================================
            // Constructors
            // ================================================

            /// <summary>
            /// Base Constructor
            /// </summary>
            public DataProvider()
            {
                  __connection =
                        new SqlConnection(ConfigurationManager.ConnectionStrings["MSSQL"].ToString());
            }

            // ================================================
            // Internal Methods
            // ================================================

            /// <summary>
            /// Raised by the Load event.
            /// </summary>
            internal void OnLoad()
            {
            }

            /// <summary>
            /// Executes the SqlQuery based on the Query Command.
            /// </summary>
            internal void SqlQuery( Type ObjectType, Object Object )
            {
                  // Open the Database Connection
                  // and prepare it for Querying.
                  Database.Open();

                  // Execute the Query and Retrieve the Results
                  SqlDataReader Results =
                        Query.ExecuteReader(CommandBehavior.CloseConnection);

                  while (Results.Read())
                  {
                        // Retrieve all Public Properties of the Class.
                        PropertyInfo[] PublicProperties = ObjectType.GetProperties();

                        foreach (PropertyInfo Property in PublicProperties)
                        {
                              // Retrieve the Column Name bound to each Property.
                              SqlColumn Column = (SqlColumn)Property.
                                    GetCustomAttributes(typeof(SqlColumn), false)[0];

                              if (Column == null)
                                    break;

                              // If the Property Value has a Column Bound, and it
                              // matches, set the property now. Special action is
                              // going to be taken for Foreign Keys.
                              Property.SetValue(Object, Results[Column.Name], null);
                        }
                  }
            }

            // ================================================
            // Public Properties
            // ================================================

            /// <summary>
            /// Gets the Database Connection.
            /// </summary>
            internal SqlConnection Database
            {
                  get { return __connection; }
            }

            /// <summary>
            /// Gets or Sets the Query
            /// </summary>
            internal SqlCommand Query
            {
                  get { return __sqlcommand; }
                  set { __sqlcommand = value; }
            }
      }
}


using System;
using System.Web;
using System.Configuration;
using System.Web.Security.Cryptography;

namespace System.Web.Authentication
{
      /// <summary>
      /// The Employee class represents a member of the employees.
      /// </summary>
      [ SqlTable ( "Employees" ) ]
      public partial class Employee : System.Web.Data.DataProvider
      {
            // ================================================
            // Private Instance Fields
            // ================================================

            // Property Backer for the ID
            private int _id = 0;

            // Property Backer for the First Name
            private string _firstName = null;

            // Property Backer for the Last Name
            private string _lastName = null;

            // ================================================
            // Constructors
            // ================================================

            /// <summary>
            /// Constructs a new Employee with the given name and password.
            /// </summary>
            /// <param name="name"></param>
            /// <param name="password"></param>
            public Employee(string name, string password):base()
            {
                  Query = new System.Data.SqlClient.SqlCommand(@"
SELECT     Employees.Id, Employees.FirstName, Employees.LastName, Roles.Name AS Role, Roles.Principle, Employees.Password, Employees.IsActive
FROM       Employees INNER JOIN
                        Roles ON Employees.Id = Roles.Id
WHERE     (Employees.FirstName = @firstname) AND (Employees.LastName = @lastname) AND (Employees.Password = @password)", Database);
                  
                  string[] UserName = name.Split(' ');

                  Query.Parameters.AddWithValue
                        ("@firstname", UserName[0]);

                  Query.Parameters.AddWithValue
                        ("@lastname", UserName[1]);

                  // Encrypt The Password so that it is compared to the one
                  // in the database properly, never sending plaintext values
                  Query.Parameters.AddWithValue
                        ("@password", WebEncryption.Encode(password));

                  SqlQuery( this.GetType(), this  );
            }

            // ================================================
            // Internal Methods
            // ================================================

            // ================================================
            // Public Properties
            // ================================================

            [SqlColumn("Id")]
            public Int32 ID
            {
                  get { return _id; }
                  set { _id = value; }
            }

            public string Name
            {
                  get { return FirstName + " " + LastName; }
            }

            [SqlColumn("FirstName")]
            public string FirstName
            {
                  get { return _firstName; }
                  set { _firstName = value; }
            }

            [SqlColumn("LastName")]
            public string LastName
            {
                  get { return _lastName; }
                  set { _lastName = value; }
            }

      }
}
0
Comment
Question by:developernetwork
  • 5
  • 5
10 Comments
 
LVL 20

Expert Comment

by:REA_ANDREW
ID: 16869647
When you say Foreign key, obviously I know what one is, but what I mean is are you looking for a way of having relationships in the actual dataset, because this is possible and quite straight forward.

For Example
I made these two functions in order to populate an ASP.NET Drop DOwn Menu with its sub menus, notice the second function where I define the relationships in the DataSet

DataSet ds = GetDataSetForMenu();

  foreach (DataRow parentItem in ds.Tables["menu_creator"].Rows)
  {
      if (Convert.ToInt32(parentItem["prod_counter"]) == 1)
      {
          MenuItem categoryItem = new MenuItem((string)parentItem["cat_name"]);
          categoryItem.NavigateUrl = "productdetail.aspx?prod_id=" + Convert.ToInt32(parentItem["link_field"]);
          Menu1.FindItem("Products").ChildItems.Add(categoryItem);
      }
      else
      {
          MenuItem categoryItem = new MenuItem((string)parentItem["cat_name"]);
          Menu1.FindItem("Products").ChildItems.Add(categoryItem);

          foreach (DataRow childItem in parentItem.GetChildRows("Children"))
          {
              MenuItem childrenItem = new MenuItem((string)childItem["prod_name"]);
              childrenItem.NavigateUrl = "productdetail.aspx?prod_id=" + Convert.ToInt32(childItem["prod_count"]);
              categoryItem.ChildItems.Add(childrenItem);
          }
      }
  }
}

private DataSet GetDataSetForMenu()
{
  SqlDataAdapter adCat = new SqlDataAdapter("SELECT cat_id, cat_name, link_field, prod_counter FROM menu_creator ORDER BY cat_menu_order", Connection);
  SqlDataAdapter adProd = new SqlDataAdapter("SELECT cat_id, prod_count, prod_name, cat_name FROM start_menu_setup ORDER BY prod_name", Connection);

  DataSet ds = new DataSet();
  adCat.Fill(ds, "menu_creator");
  adProd.Fill(ds, "start_menu_setup");
  ds.Relations.Add("Children",
     ds.Tables["menu_creator"].Columns["cat_id"],
     ds.Tables["start_menu_setup"].Columns["cat_id"]);
  return ds;
}

0
 

Author Comment

by:developernetwork
ID: 16870700
I'm a little confused. I'm not quite sure how a DataSet works. Can you expand on your example any more? How do you access each item as it if it were a deserialized class from the database columns?
0
 
LVL 20

Expert Comment

by:REA_ANDREW
ID: 16870837
OK I nest a foreach loop inside the first foreach loop. When you get the information from the Database and put it into the dataset it preserves the Column Name. Subsequently you can also use there integer position i.e. 0.

Basically we are creating two Tables in one dataset.

To retrieve information, you first have to cast an Object as a DataRow. "DataRow parentItem " <---parentItem is just something I named it.

So the first iteration goes through each row of the first table in the dataSet. while in this Loop, say I wanted to Access the first Column which was called prod_id I would use parentItem["prod_id"].

While in this Loop though I want to find all values in the second table of the dataset, which for example have the same prod_id. NOTE for this example the relationship would be based on the Primary key prod_id, and the Foreign Key in the second table prod_id.

e.g.

  ds.Relations.Add("Children",
     ds.Tables["menu_creator"].Columns["prod_id"],
     ds.Tables["start_menu_setup"].Columns["prod_id"]);

So to find all these related values, we have to add one more function which is GetChildRows().  It says Get the child rows from table 2 where the parent is parentItem. Then because we call this DataRow childItem we can access the Child Elements again simply by using

childItem["prod_id"]

\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/

 foreach (DataRow parentItem in ds.Tables["menu_creator"].Rows)

          foreach (DataRow childItem in parentItem.GetChildRows("Children"))
0
 

Author Comment

by:developernetwork
ID: 16870891
Ok. But I'm not wanting to use a DataSet. I need to be able to access my items individually, outside of binding to a control. That is to say if I want to look up the result of the query in one specific column, I need to be able to. Will this basically create such a structure?
0
 

Author Comment

by:developernetwork
ID: 16870979
Okay, I've been trying to use the DataSet, and it doesn't seem to suit my needs. Here is my code (In an HTML page)

                  DataSet LoginDataSet = new DataSet();
                  SqlDataAdapter da =
                        new SqlDataAdapter("SELECT Employees.Id, Employees.FirstName, Employees.LastName, Roles.Name AS Role, Roles.Principle, Employees.Password, Employees.IsActive FROM Employees INNER JOIN Roles ON Employees.Id = Roles.Id WHERE (Employees.FirstName = @firstname) AND (Employees.LastName = @lastname) AND (Employees.Password = @password)", ConfigurationManager.ConnectionStrings["MSSQL"].ToString());

                  string[] UserName = txtName.Text.Split(' ');

                  da.SelectCommand.Parameters.AddWithValue
                        ("@firstname", UserName[0]);

                  da.SelectCommand.Parameters.AddWithValue
                        ("@lastname", UserName[1]);

                  // Encrypt The Password so that it is compared to the one
                  // in the database properly, never sending plaintext values
                  da.SelectCommand.Parameters.AddWithValue
                        ("@password", WebEncryption.Encode(txtPassword.Text));

                  da.Fill(LoginDataSet, "Employees");

                  if (LoginDataSet.Tables["Employees"].Columns["FirstName"]. == "System")
                  {                        // close the login window and redirect to the mainpage.
                        // the login will only close on a successful login.
                        Response.Write("<script language='javascript'>");
                        Response.Write("window.top.hidePopWin();");
                        Response.Write("window.top.navigatePage('main');");
                        Response.Write("</script>");
                  }

I cannot retrieve the value from the query in a specific column. That is what I need to do, I DO NOT want to bind to a DataGrid or any other source, I want to very specifically retrieve the values of individual columns from the query, and even ontop of that, I'm still uncertain how you're using Foreign Keys..
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 20

Expert Comment

by:REA_ANDREW
ID: 16871174
Here I have created one from scratch, I have done it the long way so you get an idea of the structure, so no database here I am just showing you how to retrieve one value from one column from one row

DataSet MyDataSet = new DataSet();
        DataTable MyTable = new DataTable();
        DataRow MyDataRow = MyTable.NewRow();
        for (int i = 0; i < 4; i++)
        {
            DataColumn MyDataColumn = new DataColumn();
            MyDataColumn.ColumnName = i.ToString();
            MyTable.Columns.Add(MyDataColumn);
            MyDataRow[i] = i.ToString();
        }
        MyTable.Rows.Add(MyDataRow);
        MyDataSet.Tables.Add(MyTable);

        DataRow MyRetrieval = MyDataSet.Tables[0].Rows[0];
        if (MyRetrieval[1].ToString() == "1")
        {
            Response.Write("YES");
        }
        else
        {
            Response.Write("NO");
        }


0
 
LVL 20

Accepted Solution

by:
REA_ANDREW earned 500 total points
ID: 16871258
So going back to your example then, you have to first tell the script what rows to look at for that column.

DataRow MyRetrieval = MyDataSet.Tables[0].Rows[0];

in my example I say look at the first row so in your example, assuming your looking at the first row, or any you change the value like this

DataRow RetrievalDR = LoginDataSet.Tables["Employees"].Rows[0];
               if (RetrievalDR["FirstName"] == "System")
               {                    // close the login window and redirect to the mainpage.
                    // the login will only close on a successful login.
                    Response.Write("<script language='javascript'>");
                    Response.Write("window.top.hidePopWin();");
                    Response.Write("window.top.navigatePage('main');");
                    Response.Write("</script>");
               }
0
 
LVL 20

Expert Comment

by:REA_ANDREW
ID: 16871289
I have implemented this in so many of my .dll's because it is very robust.

Addressing Values in a DataSet is sinilar to Excel

You Needd a Table Reference, A Row Referene and also A Column Reference

this pin points a particular value in there.

Regards,

Andy
0
 

Author Comment

by:developernetwork
ID: 16872035
That doesn't mean much to me, since I don't know how to access them in Excel either. Trying to return the value of a cell just throws an error...
0
 

Author Comment

by:developernetwork
ID: 16872085
I see. the first time this page loaded, your other post didn't show up...
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Suggested Solutions

This article discusses the ASP.NET AJAX ModalPopupExtender control. In this article we will show how to use the ModalPopupExtender control, how to display/show/call the ASP.NET AJAX ModalPopupExtender control from javascript, how to show/display/cal…
I recently went through the process of creating a Calendar Control of events with the basis of using a database to keep track of the dates that are selectable, one requirement was to have the selected date pop-up in a simple lightbox.  At first this…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now