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 said:
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
