Re: Advanced row selections using Parent Child relationships
From: Sahil Malik (contactmethrumyblog_at_nospam.com)
Date: 12/27/04
- Next message: Sahil Malik: "Re: About MSDTC,The same ContextUtil.TransactionId,but the state of Database A,B is different."
- Previous message: Sahil Malik: "Re: Distinct rows from a dataset?"
- In reply to: Xedecimal: "Re: Advanced row selections using Parent Child relationships"
- Messages sorted by: [ date ] [ thread ]
Date: Mon, 27 Dec 2004 10:12:30 -0500
Xe,
Also look at Frans Bouma's comment on my blog -
http://dotnetjunkies.com/WebLog/sahilmalik/archive/2004/12/26/39040.aspx
That's a really good point, the logic I posted assumes the table to be
sorted, but that is something that can be easily gotten around using a
hashtable.
- Sahil Malik
http://dotnetjunkies.com/weblog/sahilmalik
"Xedecimal" <Xedecimal@discussions.microsoft.com> wrote in message
news:6E7F07AB-39F4-450B-BC91-FA8750478B2D@microsoft.com...
> I was reading all of this and one tiny little part caught my eye...
>
> object LastValue = null;
> foreach (DataRow dr in SourceTable.Select("", FieldName))
> {
> if (LastValue == null || !(ColumnEqual(LastValue,
> dr[FieldName])))
> {
> LastValue = dr[FieldName];
> dt.Rows.Add(new object[] { LastValue });
> }
> }
>
> I realized after reading this part that every row is actually ordered by
the
> user, so I could store the last object and check if it matches and only do
> what I want it to when it's on a unique one, thus a feux distinct select,
and
> exactly what I needed, a few little lines of code changed and kapow!
> Everything works perfect now, not to mention this is good information for
> other people that want to create whole new sets of distinct data... Thanks
a
> ton, a good christmas present, lol.
>
> "Sahil Malik" wrote:
>
> > Okay let me confirm my understanding of your issue first.
> >
> > InputData: You have 3 tables - User/Container and a many to many
> > relationship map of UserContainer .. right?
> > Expected OutputResults: And given a userID, you wish to find out all
user
> > ids that share atleast one container with this user .. and this should
be
> > without duplications .. right??
> >
> > Okay .. if that is the situation, here is the recommended solution ---
> >
> > Have one Dataset with the following 3
tables -User/Container/UserContainer.
> > Create relationships between User-UserContainer and
Container-UserContainer.
> >
> > Okay now lets assume that ur writing a function for this purpose called
..
> >
> > static Array GetContainerSharingUserTableRows(DataRow UserRow)
> > {
> > // Code goes here.
> > }
> >
> > The pseudo logic for that should be as follows ---- (Code files
attached)
> >
> > 1. Given the user Row, lets go left to right, find all the relvant
> > UserContainerRows
> > 2. Going left to right, lets get relevantContainerRows
> > ... Now starts the fun, lets start the reverse trip, start goign right
to
> > left, but relations will give duplicates so do this ..
> > 3. Create a select query that looks like "ContainerID in ('A','B'...)
> > 4. Then use the function I have written in the copy pasted code below
that
> > does a SelectDistinct instead of Select (SelectDistinct is something
that is
> > not provided as a standard part of ADO.NET ur gonna have to write it
urself,
> > or copy it from the copy paste I have given below) ----------- Very
> > important, this in order to function adds the table and a relation into
the
> > original dataset, and if you wish to call the same function repeatedly
on
> > the same dataset over and over again, then you might need to clean the
> > dataset of Table[3] and Relation[2] after this operation is done. (See
> > comment in code // You might need to clean this later)
> > 5. Fine .. now that we have the relevant DISTINCT rows from the middle
> > table, find parent rows for all such distinct rows and add those to an
> > arraylist.
> > 6. Do a ArrayList.ToArray and return the results - those are your
distinct
> > results.
> >
> >
> > The relevant code is both copy pasted and attached as a .CS for your
> > convinience :)
> >
> >
............................................................................
.......
> > .................. Copy paste of code start..........................
> >
............................................................................
.......
> > #region Using directives
> >
> > using System;
> > using System.Collections;
> > using System.Text;
> > using System.Data ;
> > using System.Data.SqlClient;
> > using System.Data.Common ;
> > using System.Configuration ;
> > using System.IO ;
> > using System.Xml ;
> > #endregion
> >
> > namespace ConsoleApplication1
> > {
> > class Program
> > {
> > // private const string CONN_STR =
> > "Server=(local);Database=Adventureworks;Integrated Security=SSPI";
> > static DataSet ds;
> > static void Main(string[] args)
> > {
> > ds = GetDataSet();
> > Array userRows =
> > GetContainerSharingUserTableRows(ds.Tables[0].Rows[0]);
> > Console.WriteLine(userRows.Length);
> > Console.Read();
> > }
> >
> > static Array GetContainerSharingUserTableRows(DataRow UserRow)
> > {
> > DataRelation FirstRel = ds.Relations["FirstRel"] ;
> > DataRelation SecondRel = ds.Relations["SecondRel"] ;
> >
> > // Left to Right Trip
> > DataRow[] UserContainerRows =
UserRow.GetChildRows(FirstRel);
> > ArrayList al = new ArrayList() ;
> > foreach (DataRow usercontainerrow in UserContainerRows)
> > {
> > al.Add(usercontainerrow.GetParentRow(SecondRel));
> > }
> >
> > Array containerrows = al.ToArray();
> > // Right to left trip
> > // formulate the where clause for the select first.
> > StringBuilder sb = new StringBuilder();
> > sb.Append("ContainerID in (");
> > foreach (DataRow containerrow in containerrows)
> > {
> > sb.Append("'");
> > sb.Append(containerrow[0]);
> > sb.Append("'");
> > sb.Append(",");
> > }
> > sb.Remove(sb.Length - 1, 1);
> > sb.Append(")");
> > DataTable ReverseUserContainerRows =
> > SelectDistinct("UserContainerDistinct", ds.Tables[2], "UserID",
> > sb.ToString());
> > // Now setup a new relation
> > ds.Relations.Add(new DataRelation("ThirdRel",
> > ds.Tables[0].Columns[0], ds.Tables[3].Columns[0])) ;
> > DataRelation thirdRel = ds.Relations[2];
> > ArrayList al2 = new ArrayList();
> > foreach (DataRow usercontainerrow in
> > ReverseUserContainerRows.Rows)
> > {
> > al2.Add(usercontainerrow.GetParentRow(thirdRel));
> > }
> > Array userrows = al2.ToArray();
> > return userrows;
> > }
> >
> > static DataTable SelectDistinct(string TableName, DataTable
> > SourceTable, string FieldName, string FilterExpression)
> > {
> > DataTable dt = new DataTable(TableName);
> > dt.Columns.Add(FieldName,
> > SourceTable.Columns[FieldName].DataType);
> >
> > object LastValue = null;
> > foreach (DataRow dr in SourceTable.Select("", FieldName))
> > {
> > if (LastValue == null || !(ColumnEqual(LastValue,
> > dr[FieldName])))
> > {
> > LastValue = dr[FieldName];
> > dt.Rows.Add(new object[] { LastValue });
> > }
> > }
> > // You might need to clean this later
> > if (ds != null)
> > ds.Tables.Add(dt);
> > return dt;
> > }
> >
> > static bool ColumnEqual(object A, object B)
> > {
> >
> > // Compares two values to see if they are equal. Also
compares
> > DBNULL.Value.
> > // Note: If your DataTable contains object fields, then you
must
> > extend this
> > // function to handle them in a meaningful way if you intend
to
> > group on them.
> >
> > if (A == DBNull.Value && B == DBNull.Value) // both are
> > DBNull.Value
> > return true;
> > if (A == DBNull.Value || B == DBNull.Value) // only one is
> > DBNull.Value
> > return false;
> > return (A.Equals(B)); // value type standard comparison
> > }
> > static DataSet GetDataSet()
> > {
> > DataSet ds = new DataSet();
> > DataTable dt = new DataTable("Users");
> > dt.Columns.Add(new DataColumn("UserID")) ;
> > DataRow dr = dt.NewRow();
> > dr[0] = "1";
> > dt.Rows.Add(dr);
> >
> > dr = dt.NewRow();
> > dr[0] = "2";
> > dt.Rows.Add(dr);
> >
> > ds.Tables.Add(dt);
> >
> > dt = new DataTable("Container");
> > dt.Columns.Add(new DataColumn("ContainerID"));
> >
> > dr = dt.NewRow();
> > dr[0] = "A";
> > dt.Rows.Add(dr);
> >
> > dr = dt.NewRow();
> > dr[0] = "B";
> > dt.Rows.Add(dr);
> >
> > ds.Tables.Add(dt);
> >
> > dt = new DataTable("UserContainer");
> > dt.Columns.Add(new DataColumn("UserID"));
> > dt.Columns.Add(new DataColumn("ContainerID"));
> >
> > dr = dt.NewRow();
> > dr[0] = "1";
> > dr[1] = "A";
> > dt.Rows.Add(dr);
> >
> > dr = dt.NewRow();
> > dr[0] = "1";
> > dr[1] = "B";
> > dt.Rows.Add(dr);
> >
> > dr = dt.NewRow();
> > dr[0] = "2";
> > dr[1] = "A";
> > dt.Rows.Add(dr);
> >
> > dr = dt.NewRow();
> > dr[0] = "2";
> > dr[1] = "B";
> > dt.Rows.Add(dr);
> >
> > ds.Tables.Add(dt);
> >
> > ds.Relations.Add(new DataRelation("FirstRel",
> > ds.Tables[0].Columns[0], ds.Tables[2].Columns[0]));
> > ds.Relations.Add(new DataRelation("SecondRel",
> > ds.Tables[1].Columns[0], ds.Tables[2].Columns[1]));
> >
> > return ds;
> > }
> > }
> > }
> >
............................................................................
......................
> > ............................ Copy paste of code end
> > ................................
> >
............................................................................
......................
> > - Sahil Malik
> > http://dotnetjunkies.com/weblog/sahilmalik
> >
> >
> >
> >
> >
> > "Xedecimal" <Xedecimal@discussions.microsoft.com> wrote in message
> > news:B93D6578-B77D-4A05-8DAF-F3D9E5CE4971@microsoft.com...
> > > orry this is so confusing, and no need to appologize, thank you very
much
> > > for
> > > being responsive to my issue, I haven't found anyone really that has a
> > > good
> > > solution for me.
> > >
> > > Lets just say I have three entities. User, Container and
UserContainer.
> > > UserContainer links User to Container and I'm using this third
> > > (UserContainer) entity instead of just adding an attribute called
> > > "Container"
> > > to user, because a single user can exist in multiple containers.
> > >
> > > So since a single User can exist in multiple Containers, then if I
wanted
> > > to
> > > take a single User, and get a list of User rows that share at least
one
> > > container as this User, I will end up with multiple User rows that are
> > > duplicates. For example...
> > >
> > > UserA is in ContainerA and ContainerB.
> > > UserB is in ContainerA and ContainerB.
> > >
> > > This means there are four (4) UserContainer rows, two for each user
for a
> > > query that looks like this...
> > >
> > > mydata.UserContainer.Select("Container = ContainerA OR Container =
> > > ContainerB");
> > >
> > > UserA->ContainerA
> > > UserA->ContainerB
> > > UserB->ContainerA
> > > UserB->ContainerB
> > >
> > > So I was thinking that in using relations, I could get the actual User
> > > rows
> > > that have any container that is related to any container that the
specific
> > > user that we're looking for is inside.
> > >
> > > The relations go a little something like, User.ID <->
UserContainer.UserID
> > > and Container.ID <-> UserContainer.ContainerID, where UserContainer is
the
> > > child of both User and Container in relation. Since UserContainer is
the
> > > child, I would use a query something like...
> > >
> > > User.Select("Child(relUserContainer).ContainerID = ContainerA OR
> > > Child(relUserContainer).ContainerID = ContainerB");
> > >
> > > With this I would actually be selecting from the User entity instead
of
> > > the
> > > UserContainer entity and this would return only one single unique user
per
> > > row (no duplicates), which is what I want but I always get syntax
errors
> > > in
> > > the Select() when I try to use Child() anything like that. I also
tried
> > > Child.ContainerID = ContainerA, etc.
> > >
> > > Note, None of these names are specific or types specified, to
hopefully
> > > try
> > > and clear up my chicken scratch clarity. This still looks pretty
confusing
> > > to
> > > me but I'm at a blank as to how I can make it much clearer. If you
have
> > > any
> > > specific questions or anything about it, please let me know and I'll
> > > elaborate as much as I can
- Next message: Sahil Malik: "Re: About MSDTC,The same ContextUtil.TransactionId,but the state of Database A,B is different."
- Previous message: Sahil Malik: "Re: Distinct rows from a dataset?"
- In reply to: Xedecimal: "Re: Advanced row selections using Parent Child relationships"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
