CodeSmith Community

Hi,

Actually I have written following store procedure. But I want to make it possible with TList<>. Is that possible.

I have two tables: User & ProjectMember and there is a relationship between these tables. Now I want to retrieve all user info from User table whose ID is not there into ProjectMember table. For that I have written following store proc.

CREATE  PROCEDURE dbo._GetUser_NotAssigned
 @ProjectId int
AS
SELECT dbo.[User].user_id As UserId, dbo.[User].role_id As RoleId, dbo.[User].first_name As FirstName, dbo.[User].last_name As LastName, dbo.[User].email As Email
FROM dbo.[User]
WHERE dbo.[User].user_id NOT IN
(SELECT DISTINCT ProjectMember.user_id
 FROM dbo.ProjectMember
 WHERE ProjectMember.project_id = @ProjectId
)
GO

Is there any possible way to get it done with TList<> without writing store procedure?

Thanks

Actually you could:

    protected void Page_Load(object sender, EventArgs e)
    {
        TList<User> users = DataRepository.UserProvider.GetAll();       
        UserParameterBuilder filter = new UserParameterBuilder(false, false);
       
        foreach (User u in users)
        {
            filter.AppendEquals(UserColumn.UserID, u.UserID);
        }
       
        TList<ProjectMember> members = DataRepository.ProjectMemberProvider.Find(filter.GetParameters());
       
        foreach (User u in users)
        {
            bool b = members.Exists(delegate(ProjectMember m)
            {               
                return m.UserID == u.UserID;
            });

            if (b)
            {
                u.MarkToDelete();
            }
        }

        users.ApplyFilter(delegate(User u) { return u.IsDirty == false; });
        GridView1.DataSource = users;
        GridView1.DataBind();
    }

but stored proc would more efficient, one note though use left join instead of subquery:

CREATE  PROCEDURE dbo._GetUser_NotAssigned
 @ProjectId int
AS
SELECT
 dbo.[User].user_id As UserId,
 dbo.[User].role_id As RoleId,
 dbo.[User].first_name As FirstName,
 dbo.[User].last_name As LastName,
 dbo.[User].email As Email
FROM
 dbo.[User]
 LEFT JOIN ProjectMember ON [User].user_id = ProjectMember.user_id
WHERE
 ProjectMember.user_id IS NULL

GO

prashant_kumar:

Hi,

Actually I have written following store procedure. But I want to make it possible with TList<>. Is that possible.

I have two tables: User & ProjectMember and there is a relationship between these tables. Now I want to retrieve all user info from User table whose ID is not there into ProjectMember table. For that I have written following store proc.

CREATE  PROCEDURE dbo._GetUser_NotAssigned
 @ProjectId int
AS
SELECT dbo.[User].user_id As UserId, dbo.[User].role_id As RoleId, dbo.[User].first_name As FirstName, dbo.[User].last_name As LastName, dbo.[User].email As Email
FROM dbo.[User]
WHERE dbo.[User].user_id NOT IN
(SELECT DISTINCT ProjectMember.user_id
 FROM dbo.ProjectMember
 WHERE ProjectMember.project_id = @ProjectId
)
GO

Is there any possible way to get it done with TList<> without writing store procedure?

Thanks

Hi Mike,

Thanks for your reply, but the store proc that you have provided is not working as per my need.

Actually I want to retrieve all user's info from User table whose user_id is not there into ProjectMember table for the specific project_id. For example suppose I am providing project_id=1 to the store proc then it will compare all the user_id of User table to the user_id of ProjectMember table for the corresponding project_id of ProjectMember table.

For this requirement I have added one more statement to your store proc as following But its not working:

CREATE  PROCEDURE dbo._GetUser_NotAssigned
 @ProjectId int
AS
SELECT
 dbo.[User].user_id As UserId,
 dbo.[User].role_id As RoleId,
 dbo.[User].first_name As FirstName,
 dbo.[User].last_name As LastName,
 dbo.[User].email As Email
FROM
 dbo.[User]
 LEFT JOIN ProjectMember ON [User].user_id = ProjectMember.user_id
WHERE
 ProjectMember.user_id IS NULL AND ProjectMember.project_id = @ProjectId
GO

Should I have to make some more changes into it? 

Thanks 

prashant_kumar,

I see what you say. Then it will be like following (see highlighted)

CREATE  PROCEDURE dbo._GetUser_NotAssigned
 @ProjectId int
AS
SELECT DISTINCT
 dbo.[User].user_id As UserId,
 dbo.[User].role_id As RoleId,
 dbo.[User].first_name As FirstName,
 dbo.[User].last_name As LastName,
 dbo.[User].email As Email
FROM
 dbo.[User]
 LEFT JOIN ProjectMember ON [User].user_id = ProjectMember.user_id
WHERE
  ISNULL(ProjectMember.project_id, 0) != @ProjectId

GO

See if that helps

Try this one...

UserParameterBuilder filter = new UserParameterBuilder();
filter.AppendNotInQuery(UserColumn.UserId, "select user_id from ProjectMember where project_id = 1");

TList<User> users = DataRepository.UserProvider.Find(filter.GetParameters());

Hope that helps!
 

Thanks to all.

But I am not able to find UserParameterBuilder class. In which layer it get defined?

The above exapmle looks good. Would it also be flexible for three tables. Suppose I have tables User, ProjectMember & Role. Both User & Role table has its foreign key into ProjectMember table. I want to find the user_name from ProjectMember table where role_name = Manager & project_id = 1.

Here we have retrieve data from 3 tables. user_name is from User table, role_name is from Role table.

Please suggest me how can I make it possible.

Thanks & Regards

The UserParameterBuilder is defined in [YourProjectNamespace].Data.Bases

To get the user_name from User table by FK from ProjectMember you'd need to either use EntityDataSourceFilter control or deepload ProjectMember class

DataRepository.ProjectMemberProvider.DeepLoad(ProjectMember, false, DeepLoadType.IncludeChildren, typeof(TList<User>));

It will populate UserIDSource property of the ProjectMember object, and in the GridView you would then specify it as following:

<asp:TemplateField HeaderText="User Name">
 <ItemTemplate>
  <%# Eval("UserIDSource.UserName") %>
 </ItemTemplate>
</asp:TemplateField>

prashant_kumar:

Thanks to all.

But I am not able to find UserParameterBuilder class. In which layer it get defined?

The above exapmle looks good. Would it also be flexible for three tables. Suppose I have tables User, ProjectMember & Role. Both User & Role table has its foreign key into ProjectMember table. I want to find the user_name from ProjectMember table where role_name = Manager & project_id = 1.

Here we have retrieve data from 3 tables. user_name is from User table, role_name is from Role table.

Please suggest me how can I make it possible.

Thanks & Regards

Hi,

When I am using following store proc; its not retrieving perfect result:

CREATE  PROCEDURE dbo._GetUser_NotAssigned
 @ProjectId int
AS
SELECT DISTINCT
 dbo.[User].user_id As UserId,
 dbo.[User].role_id As RoleId,
 dbo.[User].first_name As FirstName,
 dbo.[User].last_name As LastName,
 dbo.[User].email As Email
FROM
 dbo.[User]
 LEFT JOIN ProjectMember ON [User].user_id = ProjectMember.user_id
WHERE
  ISNULL(ProjectMember.project_id, 0) != @ProjectId

GO

BUT

When I use store proc containing sub-query instead of left-join; its working. I think query with lefy-join not able to categorize result along with @ProjectId which sub-query doing.

Thank you

Prashant,

 I have a couple of things to note here.  First, if you expect your custom stored proc to return a User entity, you will need to not alias the column names.  I believe that this will cause the template logic to return a dataset/idatareader instead of an entity (I could be wrong, though).  Second,  from a SQL standpoint if I was going to write a stored proc like this I would use a correlated sub-query using NOT EXISTS instead of NOT IN of a LEFT JOIN simply for performance reasons.  If you dataset is small, the performance gains will be negligible, but as the number of records increases the performance gains can be enormous (especially for not exists vs. not in).  I would write the query like so:

SELECT 
 dbo.[User].user_id,
 dbo.[User].role_id,
 dbo.[User].first_name,
 dbo.[User].last_name,
 dbo.[User].email
FROM
 dbo.[User] u
WHERE NOT EXISTS 
    ( 
    SELECT * 
    FROM dbo.[ProjectMember] pm 
    WHERE pm.user_id = u.user_id  
    AND pm.project_id = @ProjectId
    )

You should run the query both ways and see which is more performant (take a look at the execution plan).  Also, if the table in the sub-query contains null values you can get some unexpected results using NOT IN.

Bobby - I see that you added AppendNotInQuery to the ParameterBuilder class.  We may want to think about adding AppendNotExistsQuery and AppendExistsQuery.  The only gotcha is making sure we can specify how to link the inner and outer queries.

Hi bdiaz,

I have same problem in query.
Where can i find the EntityParameterBuilder?
I can not find it in my_namespace.Data.Bases

Is this about the generation configuration in codesmith?
how can I config the codesmith to generate my  EntityParameterBuilder?

md5

bdiaz:

Try this one...

UserParameterBuilder filter = new UserParameterBuilder();
filter.AppendNotInQuery(UserColumn.UserId, "select user_id from ProjectMember where project_id = 1");

TList<User> users = DataRepository.UserProvider.Find(filter.GetParameters());

Hope that helps!
 

The ParameterBuilder is wrapped in an <Entity>Query class (i got v2.2.0.662).  So above it would be UserQuery filter = new UserQuery();  Usage is the same.