Would appreciate some help. I need to fill the session collection with the results from TWO tables as a datarelation and this is where I am stuck. Filling the results from one table is easy but how do i do this with the two tables.
I tried modifying the code in the For statment as follwos but it would not work.
foreach(DataRow r in dr)
public static SessionCollection GetEmployeeGridEntries( string SelDate)
{
DataSet ds = SqlHelper.ExecuteDataset(ConfigurationSettings.AppSettings[Web.Global.CfgKeyConnString],"SP_GetEmployeeWorkedGrid",
Convert.ToDateTime(SelDate));
ds.Tables[0].TableName = "Employee";
ds.Tables[1].TableName ="EmployeeRecords";
DataColumn Parent;
DataColumn Child;
Parent=ds.Tables[0].Columns["Employee_Number"];
Child =ds.Tables[1].Columns["Stat_Employee"];
DataRelation dr = new DataRelation("EmployeeGroup", Parent,Child,false);ds.Relations.Add(dr);
SessionCollection mysession = new SessionCollection();
foreach(DataRow r in ds.Tables[0].Rows) //??Stuck at this point
{
MySession prj = new MySession();
prj.EmployeeName =r["EmployeeName"].ToString();
prj.Hours = Convert.ToDecimal(r["Stat_Hours"]);
mysession.Add(dr);
}
return mysession;
}Hi Bryan,
You cud try something with the GetChildRows Method of the parent table
foreach (DataRow r in ds.Tables[0].Rows)
{
MySession prj = new MySession();
prj.EmployeeName =r["EmployeeName"].ToString();
prj.Hours = Convert.ToDecimal(r["Stat_Hours"]);
DataRow[] childr;
childr = r.GetChildRows("EmployeeGroup");
foreach (DataRow childRow in childr)
{
prj.ChildTblField1 = childRow["FieldName1"].ToString();
.
.
}
}
The above dots in the inner for loop represent other fields you may need to extract from the table.
Hope that helps.
Regards,
Hello -
Just wondering if the datarelation couldn't be accomplished through
a query with an aggregate?
Please advise on what you are really trying to accomplish.
thanks
tony
Tony:
I am trying to utilize the Hierargrid control in a heavily midifed timetracker application. The web page calls a class to fill a datagrid. Here is the unmodified code from the timetracker application. In my application i want to modify the GetProjects() so that a data relation is used to fill the collection.
public class Project
{
private SessionCollection _categories;
private string _description;
private DateTime _estCompletionDate;
private decimal _estDuration;
private int _managerUserID;
private string _managerUserName;
private UsersCollection _members;
private string _name;
private int _projectID;
public Project()
{
}
public Project(int projectID)
{
_projectID = projectID;
}
public Project(
int projectID,
string name,
string description,
int managerUserID,
DateTime estCompletionDate,
decimal estDuration)
{
_projectID = projectID;
_name = name;
_description = description;
_managerUserID = managerUserID;
_estCompletionDate = estCompletionDate;
_estDuration = estDuration;
}
public SessionCollection Categories
{
get{ return _categories; }
set{ _categories = value; }
}
public string Description
{
get{ return _description; }
set{ _description = value; }
}
public DateTime EstCompletionDate
{
get{ return _estCompletionDate; }
set{ _estCompletionDate = value; }
}
public decimal EstDuration
{
get{ return _estDuration; }
set{ _estDuration = value; }
}
public int ManagerUserID
{
get{ return _managerUserID; }
set{ _managerUserID = value; }
}
public string ManagerUserName
{
get{ return _managerUserName; }
set{ _managerUserName = value; }
}
public UsersCollection Members
{
get{ return _members; }
set{ _members = value; }
}
public string Name
{
get{ return _name; }
set{ _name = value; }
}
public int ProjectID
{
get{ return _projectID; }
set{ _projectID = value; }
}
//*********************************************************************
//
// Retrieves a list of projects based on the user's role
//
//*********************************************************************
public static ProjectsCollection GetProjects(int userID, string role)
{
string firstName = string.Empty;
string lastName = string.Empty;
DataSet ds = SqlHelper.ExecuteDataset(
ConfigurationSettings.AppSettings[Web.Global.CfgKeyConnString],
"TT_ListProjects", userID, Convert.ToInt32(role));
ProjectsCollection projects = new ProjectsCollection();
foreach(DataRow r in ds.Tables[0].Rows)
{
Project prj = new Project();
prj.ProjectID = Convert.ToInt32(r["ProjectID"]);
prj.Name = r["ProjectName"].ToString();
prj.Description = r["Description"].ToString();
prj.ManagerUserID = Convert.ToInt32(r["ManagerUserID"]);
prj.ManagerUserName =
TTUser.GetDisplayName(Convert.ToString(r["UserName"]), ref firstName, ref lastName);
prj.EstCompletionDate = Convert.ToDateTime(r["EstCompletionDate"]);
prj.EstDuration = Convert.ToDecimal(r["EstDuration"]);
projects.Add(prj);
}
return projects;
}
Thanks Shravan.
I am a little stuck on this aspect of your code, would you be able to explain. Namely this line
prj.ChildTblField1 = childRow["FieldName1"].ToString();
It returns a error that fieldname1 can not be found in the table Employee. It appears not to be referncing table 1, but still referencing table 0.
DataRow[] childr;
childr = r.GetChildRows("EmployeeGroup");
foreach (DataRow childRow in childr)
{
prj.ChildTblField1 = childRow["FieldName1"].ToString();
.
.
}
}
Bryan,
By naming FieldName1 in this line
prj.ChildTblField1 = childRow["FieldName1"].ToString();
I meant that you would use the field names of the second table and you should be knowing the its field names (of your second table) which I do not have any knowledge about. Also the ChildTblField1 referrs that its a property in your prj whose value is set by the expression or code on the right hand side of the '=' sign in the line above.
Also, How did it refer FieldName1 in table Employee. Its a field of your second table.
If you still have problems, post the part of the code that you are using and possibly it wud make me understand better, if I'm missing something now.
Regards,
Shravan:
Thanks for the help,sorry, I did substitute my field names. Here is the code below.
If I use the name of table (0) Employee or table (1) EmployeeRecords the loop to grab the child record does not happen. if I use the name of the datarelation "EmployeeGroup", the loop executes but returns a error
//Exception Details: System.ArgumentException: Column 'stat_Employee' does not belong to table Employee.
The column stat_employee does not exist in the table Employee, but EmployeeRecords, which it should determine from the data relation, shouldn't it?
Am I missing something ?
//******************************************************************
//
// MySession Class
//
// Used to represent a session along with its members and categories.
//
//******************************************************************
public class MySession
{
private SessionCollection _categories;
private UsersCollection_members;
private string_employeename;
private int_employeenumber;
public MySession()
{
}
public MySession(int RecordNumber)
{
_recordnumber = RecordNumber;
}
public MySession(
string EmployeeName,
int EmployeeNumber)
{
_employeename = EmployeeName;
_employeenumber = EmployeeNumber;
}
public UsersCollection Members
{
get{ return _members; }
set{ _members = value; }
}
public string EmployeeName
{
get{ return _employeename; }
set{ _employeename = value; }
}
public int EmployeeNumber
{
get{ return _employeenumber; }
set{ _employeenumber = value; }
}
//******************************************************************
//
// Retrieves a list of GridEntries
//
//******************************************************************
Public static SessionCollection GetEmployeeGridEntries( string SelDate)
{
DataSet ds = SqlHelper.ExecuteDataset(ConfigurationSettings.AppSettings[Web.Global.CfgKeyConnString],"SP_GetEmployeeWorkedGrid", Convert.ToDateTime(SelDate));
ds.Tables[0].TableName = "Employee";
ds.Tables[1].TableName = "EmployeeRecords";
DataColumn Parent;
DataColumn Child;
Parent =ds.Tables[0].Columns["Employee_Number"];
Child = ds.Tables[1].Columns["Stat_Employee"];
DataRelation dr = new DataRelation("EmployeeGroup", Parent,Child,false);
ds.Relations.Add(dr);
SessionCollection mysession = new SessionCollection();
foreach (DataRow r in ds.Tables[0].Rows)
{
MySession prj = new MySession();
prj.EmployeeName =r["EmployeeName"].ToString();
DataRow[] childr;
childr = r.GetChildRows("EmployeeRecords");
foreach (DataRow ChildRow in childr)
{
prj.EmployeeNumber = Convert.ToInt32(r["stat_Employee"]);
}
}
return mysession;
}
Bryan,
Your inner for loop seems to screw up the whole point here. r represents the rows of your parent table. Make the change as below to your inner loop.
foreach (DataRow ChildRow in childr)
{
prj.EmployeeNumber = Convert.ToInt32(ChildRow["stat_Employee"]);
}
hope that helps and do post what end result you got.
Regards,
Thanks Shravan:
Managed to get it to work (almost). The rows appear to be added to the collection, but the calling procedure
private void BindEmployeeGrid()
{
SessionCollection GridItems = BusinessLogicLayer.MySession.GetEmployeeGridEntries(txtCurrentDate.Text );
EmployeeGrid.DataSource = GridItems;
EmployeeGrid.DataBind();
}
throws an error when the grid is filled
"System.Web.HttpException: A field or property with the name 'EmployeeNumber' was not found on the selected datasource."
The field/property EmployeeNumber or EmployeeName is indeed part off the SessionCollection and appears to be filled when I debug.
Am I missing something in the setting up of the grid. ?
Bryan or Shravan,
Did you get this to work? ...and could you share how you did this with the Projects class in timetracker?
I have created a whole new application using TimeTracker as the framework. I came across Denis Bauer's HierarGrid yesterday and would like to implement it into my app.
I'm just not sure where to add the 'Relations' since the dataset is populated in the Project.vb and returned to the calling function as ProjectsCollection.
Regards
Hello Knute:
I too based my app on the timetracker framework. I could not figure out how to fill the Projects Collection from the dataset using a releation so I just filled it from the dataset (lack of experience I suppose <g>).
To be honest I think it's probably faster filling straight to a dataset as you don't have to loop through the dataset to add to the ProjectCollection. I never bothered using the project collection.
The Hierargrid is a nice tool, but I found it tends to be a bit slow to fill, I need to read up on optimizing my database for performance, I think this is where the bottlework maybe.
//Called from a web pageprivate void BindEmployeeGrid()
{
SessionCollection entryList = BusinessLogicLayer.MySession.GetEmployeeGridEntries(102,txtCurrentDate.Text );DataSet GridItems;
GridItems = BusinessLogicLayer.MySession.GetEmployeeGridEntries(txtCurrentDate.Text );EmployeeGrid.DataSource = GridItems;
EmployeeGrid.DataMember = "Employee";
EmployeeGrid.DataBind();
EmployeeGrid.RowExpanded[0] = true;
}//the class
public static DataSet GetEmployeeGridEntries( string SelDate)
{DataSet ds = SqlHelper.ExecuteDataset(ConfigurationSettings.AppSettings[Web.Global.CfgKeyConnString],"SP_GetEmployeeWorkedGrid", Convert.ToDateTime(SelDate));
ds.Tables[0].TableName = "Employee";
ds.Tables[1].TableName = "EmployeeRecords";DataColumn Parent;
DataColumn Child;//Relation
Parent =ds.Tables[0].Columns["Employee_Number"];
Child = ds.Tables[1].Columns["Stat_Employee"];
DataRelation dr = new DataRelation("EmployeeGroup", Parent,Child,false);
ds.Relations.Add(dr);
return ds;}
Thanks Bryan:
I understand about bypassing the collection but is your stored procedure (SP_GetEmployeeWorkedGrid) basically...two SELECTs within the one stored procedure?
I've looked at Denis Bauer's TfsDemo and from the way the stored procedures are written I will have to change my approach to retrieving the data quite a bit.
About performance: (Just a thought)
We use ActiveDirectory on our network, however, when I set the following line in Webconfig to use "ActiveDirectory" the app slowed to a crawl - especially where Role=1. Try "None".
<add key="UserAccountSource" value="None" />
Knute:
Yes my stored procedure is TWO select statements with parameters for a date and a location (haven't implemented the location yet as this is why the parameter 102 is passed to the stored procedure in my example.
I will give your suggestions a try. i need to also look at caching as I am sure I can speed things up by implementing caching. Let me know how it works out. I struggled with the hierargrid for about 2 weeks brfore I got it working so I have goten pretty good at it<g
Thanks Bryan.
Great article in July 2004 issue of Access-VB-SQL Advisor on Caching:
Faster ASP.NET Applications
Use the ASP.NET Output Cache Engine to increase application performance.
By Stephen Forte, Technical Editor
0 comments:
Post a Comment