Owe, thats the cute idea. Thanks a loooot. Sure, it is a working code and it is working perfectly. Thank you very much for your help.

Hi, In my code, I am counting the number of records (sent emails) that are older than one month. In order to do that, I am using following snippet.

int totalSentEmailsOlderThanMonth = emailSendingService.ListSentEmails().ToArray().Count(x => (DateTime.Now - x.SentDateTime).TotalDays > 30);

The reason I am using .ToArray() in my code as (DateTime.Now - x.SentDateTime).TotalDays cannot be translated in LINQ to SQL query so, by calling ToArray(), I am loading all records into the memory and then counting. But there are more than hundreds of records in my database and loading all the records into the memory just for counting is not efficient at all. So, would anyone please give me an alternative more efficient snippet that can be used in this scenario. [[ By the way, emailSendingService.ListSentEmails() returns IQueryable<MyRecordType> ]] Regards.

Hi, Thank you so much for your reply, actually I meant cache not by the Http Cache. I meant, I can keep the data live in the Memory as long as the Application is live (Web.Config is not modified or application is not restarted). So, do you think, my Public static get accessor will return the same data for every time just because it is static ? I know if I used a static field inside the get accessor than, the value is not lost from memory.

Hello, I have 2 closely related questions, would you please help. 1. In my ASP.NET MVC application, I am encapsulating the Logged In User Name within a static property in a class as

public static string LoggedInUserName
{
get
{
return HttpContext.Current.User.Identity.Name;
}
}

Now, I am wondering, as the property is static, will that value be cached so that if a new user visit the website, the last returned user's user name will be returned by the Property ? I am expecting that, every call to the property 'LoggedInUserName' the fresh new Identity value will be returned. Am not I right ? 2. If I want to cache a value within the application life cycle, I know I can use a static field. But, is it required that the static field be public ? Or any private static field is also cached within the application life cycle as well ? Regards.

Hi Shameel, Thank you very much for your suggestions. I got the problem solved by COALESCE function that was suggested by the previous reply. But The Table Variable over temporary variable is a nice thing that learned from your link. Regards.

Hi Piebald, Thank you sooooooooooooo much for the kind help. Yes, the COALESCE function is what solved my entire problem. In fact, the link you provided discussed the exactly same problem what I had to solve, so that page just did my task completely. I highly appreciate your help.

Hi Luc, Thanks for your reply. Yes, thats what I realize, I would never do the same thing in my programming. (I am a C# programmer) But the problem is, I dont know the correct TSQL Syntax / operators that can let me get rid of this loop. My intention to get data from stored procedure in the following structure ------------------------- string product_name, int affectedRows, bool hasError, int[] someIds ----------------------- Now, everything was fine until I reached the need to get int[] someIds. I was lost, how I could do that. If I just needed to return SomeIds (: int[]) then, yes, I could simply return the result set, but as I needed to get the other string, bool, int etc type data so I created a temporary table in my procedure to store the int values (someIDs : int[]) then, I used the specified procedure to read back the int ids from the temporary table and serialize as comma separated string to return. Would you please show me any technique to get such data structure from stored procedure ?

Hello, I just started learning TSQL Stored procedure (SQL Server 2005) and looks like it does not have strong set of functions that I could do in programming languages. Anyway, I wanted to return a string type data which holds comma separated value read from a temporary table. After spending hours, I came to the following code that WORKS. But I am wondering there should be more efficient way to do that. My following code runs at least 2 query to the temp table in each iteration to build the string where one of the query uses Nested SQL statement which can be more expensive in performance. So, Would anyone please tell me is there any better way to do that ? I thought there would be SKIP operation but SKIP operator is not being accepted by my SQL Server. IF there was any SKIP operator, at least I could save a nested query. Anyway, your help would be highly appreciated. Regards.

DECLARE @Iterator INT
SET @Iterator = 0

WHILE (@Iterator < @totalIds)
BEGIN

IF(@Iterator = 0)
 BEGIN
          SELECT TOP 1 @DeletedIDArr = CAST(Id AS varchar(50)) FROM #deletedIds
 END
ELSE
 BEGIN
         SELECT TOP 1 @DeletedIDArr = (@DeletedIDArr + ',' + CAST(Id AS varchar(50)))
         FROM #deletedIds
         WHERE Id NOT IN(SELECT TOP (@Iterator) Id FROM #deletedIds);
 END
SET @Iterator = @Iterator + 1

END

Thank you so much for the ideas. Yes, you are right and thats what I am realizing now. At this moment, I am avoiding the extra BLL layer, but I will keep a sharp eye when duplication of code is apparent, and only then, I will add the BLL with only the refactored common methods. So, some of my UI will call DAL and some of my UI will call BLL and over time, depending on BLL will be increased and DAL dependency will decrease.

Thank you very much. Yes, thats a good question. Actually, I spent many hours just to find out if that given code snippet should be a part of BLL or DAL. Yes, if I place that snippet in BLL, then, the auto generated LINQ to SQL data context can be the DAL. But, in my BLL, I wanted see the object, not the LINQ to SQL Data context. Because that data context is auto generated and I will have to depend on what Microsoft gives. That is much uncomfortable for me. But, I learned in many place that, Projection should not be done in DAL. Query is a Business Logic. So, the dilemma is kind of a headache and brain killing. Finally I decided to encapsulate the DataContext in DAL and the DAL will not do any projection. DAL will return a single row of a entity or a collection of entity. My BLL will use that collection of entity and use LINQ to Object to do any projection as needed. Even according to that decision, yes, you are right, the above snippet should be in BLL. I should have asked a collection of employees in DAL from BLL and then, the BLL would filter out with the email Address for the desired employee object using LINQ to Object. Hmmm Brain got jammed again :)

Thank you very much. Now, I got the direction.

Thank you very much. I liked your answer.

Thanks for your kind reply. I see. So, I got something to do (validating inputs) for BLL. But, I do the validation in Presentation Layer as the validation is highly related to the UI. For example, if the input is coming from a Slider, then, my validation code will use the Slider's property. If my email address is coming from a text box, then, my Validation code will use the text box property to check. Yes, if I use a Regex to validate an email address, I encapsulate that Regex code in a method in my BLL and call that method from my Validation logic in Presentation layer so that the Presentation layer does not look thick. Other than validation, is there anything that I may need to know so that I can involve BLL between DAL and Presentation Layer ?

Hi, I know, a well designed architecture should not allow a presentation layer to talk to Data Access Layer. Rather Presentation layer should talk to Business Logic Layer and Business Logic Layer should talk to Data access layer. In my Data access layer, I have method

    /// <summary>
    /// Gets the employee.
    /// </summary>
    /// <param name="emailAddress">The email address.</param>
    /// <returns></returns>
    public static employee GetEmployee(string emailAddress)
    {
        using (EmployeeDBDataContext myDbContext = new EmployeeDBDataContext())
        {
            employee empRecord = myDbContext.employees.Where(aRec => aRec.emailAddress == emailAddress).ToArray().SingleOrDefault();
            return empRecord;
        }
    }

My Presentation Layer's job is to display the properties of the employee object directly to the UI, where I do not even need to write any code for that. Ok, now, should I create a thin Business Logic layer just to mediate the call of Presentation layer ? Then, it may look very messy as I will have to create a set of methods in BLL duplicating the method signature from DAL. Would you please tell me what could be a better practice ?

modified on Monday, February 28, 2011 5:12 AM

Thank you for your reply. Actually I intended to use a boolean property 'IsDirty' for a dirty row. But, In order to set this 'IsDirty' property, I will need to write code which may not look clean and maintainable. I may need to handle some events of the GridView to set this IsDirty property. It can be more messy if i want to maintain IsDeleted property as well. But, I thought, INotifyPropertyChanged interface was invented for solving this problem. Am I wrong ? When I see some demo videos about Silverlight / WPF, I see the instructor is implementing INotifyPropertyChanged interface on business objects and tracking is being taken care by the framework. Moreover, when working with Silverlight, I am forced to use Business Objects instead of direct database access, as Silverlight is a client side tech. So, the framwork is offering solution for Silverlight ! Am I missing anything about it ?

Thank you for showing the light. I will give a try. I just missed the idea about using LINQ to find the diff. now feeling easy :)

Thanks for your reply. Sounds like a lots of tasks, moreover maintenance can be nightmare if I keep changing my domain model and database table fields. I thought, if I implement INotifyPropertyChanged in my Business Object (Person Class) then, the .NET Framework can notify a data context about dirty rows automatically. I tried and that did not work. :( Looks like, I will have to compromise either Excel / Spreadsheet functionality or Decoupling of Presentation Layer + Data Access Layer (LINQ to SQL DataContext class) :(

I see ! So, if I want to expose an Excel Spreadsheet like facility to my User, I cannot follow n tier pattern :( thats sad. But anyway, thank you for giving me the idea.

Hello, I realize that, DatabaseContext class generated by LINQ to SQL / LINQ to ENTITY should not be a part of the Views and so I want to encapsulate the uses of DatabaseContext classes within my Data Access Layer. So, when I need to display a set of records in a GridView in Windows Form, I can simply let my Data Access Layer return the List<Person> of the records and display that records in GridView without any problem. Great! Here is my Person class

public class Person
{
public string FirstName { get; set; }
public string LastName { get; set; }
}

Ok, now, I face the big problem when I need to save the changes back to the database. I may have 1000 records displaying in the Grid view where the GridView is bound to a BindingSource. My user can change only 2-3 records from the GridView and delete some of the records as well. How can I tell the LINQ to SQL / LINQ to Entity (data context) about the changed records so that when I pass the List<Person> object to my Data Access Layer, Data Access Layer can identify which records needs to be updated and finally the Data Context updates those records ? Can you please show me some pattern for this problem ? Regards.

Hi, I developed a desktop application using SQL Compact, used Entity Framework. Now I see, Entity Framework's connection string is not that simple, it stored many information in the connection string in app.config file. So, I do not touch that file. But what I see, the database folder is user |DataDirectory| which is actually Application.StartUpPath. When I distribute my program to end users, will they be able to run my application where the database is in the program files folder ? I know it was not a problem for XP. But when Vista came, the 'User Account Control' feature prevented accessing any files in Program Files folder. What about now ? Is that problem gone in Windows 7 ? IF NOT, then, how can I make the connection string dynamic (setting programmatically) for Entity framework ? Although I extremely like to use Application.StartUpPath to be my data store as it is easy for maintainability.