developernetwork
asked on
SQL backed by Classes.
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(Configuratio
}
// ==========================
// 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(Comman
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
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.Crypto
namespace System.Web.Authentication
{
/// <summary>
/// The Employee class represents a member of the employees.
/// </summary>
[ SqlTable ( "Employees" ) ]
public partial class Employee : System.Web.Data.DataProvid
{
// ==========================
// 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.SqlC
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.AddWithVa
("@firstname", UserName[0]);
Query.Parameters.AddWithVa
("@lastname", UserName[1]);
// Encrypt The Password so that it is compared to the one
// in the database properly, never sending plaintext values
Query.Parameters.AddWithVa
("@password", WebEncryption.Encode(passw
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; }
}
}
}
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(Configuratio
}
// ==========================
// 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(Comman
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
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.Crypto
namespace System.Web.Authentication
{
/// <summary>
/// The Employee class represents a member of the employees.
/// </summary>
[ SqlTable ( "Employees" ) ]
public partial class Employee : System.Web.Data.DataProvid
{
// ==========================
// 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.SqlC
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.AddWithVa
("@firstname", UserName[0]);
Query.Parameters.AddWithVa
("@lastname", UserName[1]);
// Encrypt The Password so that it is compared to the one
// in the database properly, never sending plaintext values
Query.Parameters.AddWithVa
("@password", WebEncryption.Encode(passw
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; }
}
}
}
ASKER
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?
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"].
ds.Tables["start_menu_setu
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"].
foreach (DataRow childItem in parentItem.GetChildRows("C
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"].
ds.Tables["start_menu_setu
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"].
foreach (DataRow childItem in parentItem.GetChildRows("C
ASKER
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?
ASKER
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.Conne
string[] UserName = txtName.Text.Split(' ');
da.SelectCommand.Parameter
("@firstname", UserName[0]);
da.SelectCommand.Parameter
("@lastname", UserName[1]);
// Encrypt The Password so that it is compared to the one
// in the database properly, never sending plaintext values
da.SelectCommand.Parameter
("@password", WebEncryption.Encode(txtPa
da.Fill(LoginDataSet, "Employees");
if (LoginDataSet.Tables["Empl
{ // 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
Response.Write("window.top
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..
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.Conne
string[] UserName = txtName.Text.Split(' ');
da.SelectCommand.Parameter
("@firstname", UserName[0]);
da.SelectCommand.Parameter
("@lastname", UserName[1]);
// Encrypt The Password so that it is compared to the one
// in the database properly, never sending plaintext values
da.SelectCommand.Parameter
("@password", WebEncryption.Encode(txtPa
da.Fill(LoginDataSet, "Employees");
if (LoginDataSet.Tables["Empl
{ // 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
Response.Write("window.top
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..
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(MyData
MyDataRow[i] = i.ToString();
}
MyTable.Rows.Add(MyDataRow
MyDataSet.Tables.Add(MyTab
DataRow MyRetrieval = MyDataSet.Tables[0].Rows[0
if (MyRetrieval[1].ToString()
{
Response.Write("YES");
}
else
{
Response.Write("NO");
}
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(MyData
MyDataRow[i] = i.ToString();
}
MyTable.Rows.Add(MyDataRow
MyDataSet.Tables.Add(MyTab
DataRow MyRetrieval = MyDataSet.Tables[0].Rows[0
if (MyRetrieval[1].ToString()
{
Response.Write("YES");
}
else
{
Response.Write("NO");
}
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
ASKER
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...
ASKER
I see. the first time this page loaded, your other post didn't show up...
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"].
{
if (Convert.ToInt32(parentIte
{
MenuItem categoryItem = new MenuItem((string)parentIte
categoryItem.NavigateUrl = "productdetail.aspx?prod_i
Menu1.FindItem("Products")
}
else
{
MenuItem categoryItem = new MenuItem((string)parentIte
Menu1.FindItem("Products")
foreach (DataRow childItem in parentItem.GetChildRows("C
{
MenuItem childrenItem = new MenuItem((string)childItem
childrenItem.NavigateUrl = "productdetail.aspx?prod_i
categoryItem.ChildItems.Ad
}
}
}
}
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"].
ds.Tables["start_menu_setu
return ds;
}