asked
Entity Framework, SQL Data Reader, eliminate blank spaces
![alt text][1] ![alt text][2] I am using datareader to return a list and because of the way the entity is set up, I had to include all of the members into the result set. However, in my dat grid, I'm only displaying a smaller number of fields. My grid is displaying blank rows for all the other members. How do I make the function not return blank rows? Is there a data reader syntax that will check for blank rows after the items are read in the reader? Using reader As SqlDataReader = dcmd.ExecuteReader() While reader.Read() list.Add(New EmpHistDetails_vw() With { _ .Emp = reader("Emp").ToString(), _ .EmpName = reader("EmpName").ToString(), _ .CourseDescription = reader("CourseDescription").ToString(), _ .EducationLevelDescription = reader("EducationLevelDescription").ToString(), _ .InstituteDescription = reader("InstituteDescription").ToString() _ }) End While If (reader.NextResult()) Then While reader.Read() Dim d As DateTime If reader("LastUsed") Is DBNull.Value Then d = DateTime.MinValue Else d = Convert.ToDateTime(reader("LastUsed")) End If list.Add(New EmpHistDetails_vw() With { _ .LanguageCodeDescription = reader("LanguageCodeDescription").ToString(), _ .LastUsed = d _ }) End While If (reader.NextResult()) Then While reader.Read() list.Add(New EmpHistDetails_vw() With { _ .RegDescription = reader("RegDescription").ToString(), _ .RegStateDescription = reader("RegStateDescription").ToString() _ }) End While If (reader.NextResult()) Then While reader.Read() list.Add(New EmpHistDetails_vw() With { _ .SF254Desc = reader("SF254Desc").ToString(), _ .SkillDescription = reader("SkillDescription").ToString(), _ .PrimaryVsAlternate = reader("PrimaryVsAlternate").ToString() _ }) End While [1]: /upfiles/samgrid1.JPG [2]: /upfiles/samgrid2.JPG
@technette - if you post the code you're using, or at least an overview of it, we may be able to help.
@technette I guess I cannot understand what do the rows have to do with anything because you say that in the grid you are "only displaying a smaller number of **fields**. My grid is displaying blank rows for all the other members". Could you please provide some details? Thank you. Does this guess hold any water? You have the reader returning 4 results, and you populate only some of the properties of your list from each set. For example, you might only have LanguageCodeDescription and LastUsed returned by the first set, so you populate only these 2 properties and add the list item to the list. Your second result returns CourseDescription, EducationLevelDescription, Emp, EmpName and InstituteDescription, so you populate only these 5 properties and still add the list item to the list etc. Then if you only need to display the latter 5 columns in the grid, you also get all blank rows for the list items which actually hold other properties which you don't need. In other words, you have the sparse list design, meaning that it has the whole bunch of properties, but different items of the list have different properties populated and rest are blanks. Is this assumption correct?
Your assumptions are correct. The query takes the employee number from a textbox as a parameter. All of the grids are populated accordingly. I have attached a small sample of how the data is being populated in two grids. The second grid is an example of what is happening. It has blank rows for the educational fields but I only populated that grid with registration information. On the database end, I had to perform many outer joins of tables to make the view display all employee details.
I had to modify my original answer because I believe that your nested if statements should be rearranged somewhat. Your procedure returns 4 sets of data. First set is always populated with some information while others might or might not have records. If they do have records then you can check if the combination of all values in the record is not blank, and if not then add the item to your list representing your business type. In other words, don't add blank items to you list. I don't believe though that you have to nest your if statements. They are pretty much independent, meaning that your proc always returns 4 sets but every set might or might not have any records in it. This means that there is no need to nest your if statements. Here is your code with checks for blanks added so you will not get any items in your list with blank values (leading to empty rows in your grids): Using reader As SqlDataReader = dcmd.ExecuteReader() While reader.Read() list.Add(New EmpHistDetails_vw() With { _ .Emp = reader("Emp").ToString(), _ .EmpName = reader("EmpName").ToString(), _ .CourseDescription = reader("CourseDescription").ToString(), _ .EducationLevelDescription = reader("EducationLevelDescription").ToString(), _ .InstituteDescription = reader("InstituteDescription").ToString() _ }) End While If (reader.NextResult()) Then While reader.Read() Dim d As DateTime If reader("LastUsed") Is DBNull.Value Then d = DateTime.MinValue Else d = Convert.ToDateTime(reader("LastUsed")) End If ' Add the item to the list only if LanguageCodeDescription ' is not blank and the LastUsed is not null (d > DateTime.MinValue) If reader("LanguageCodeDescription").ToString() > String.Empty AndAlso _ d > DateTime.MinValue Then list.Add(New EmpHistDetails_vw() With { _ .LanguageCodeDescription = reader("LanguageCodeDescription").ToString(), _ .LastUsed = d _ }) End If End While End If If (reader.NextResult()) Then While reader.Read() ' Add the item to the list only if registration info is not blank If Not String.IsNullOrEmpty(reader("RegDescription").ToString() & _ reader("RegStateDescription").ToString()) then list.Add(New EmpHistDetails_vw() With { _ .RegDescription = reader("RegDescription").ToString(), _ .RegStateDescription = reader("RegStateDescription").ToString() _ }) End If End While End If If (reader.NextResult()) Then While reader.Read() ' Add the item to the list only if skills info is not blank If Not String.IsNullOrEmpty(reader("SF254Desc").ToString() & _ reader("SkillDescription").ToString() & _ reader("PrimaryVsAlternate").ToString()) then list.Add(New EmpHistDetails_vw() With { _ .SF254Desc = reader("SF254Desc").ToString(), _ .SkillDescription = reader("SkillDescription").ToString(), _ .PrimaryVsAlternate = reader("PrimaryVsAlternate").ToString() _ }) End If End While End If reader.Close() End Using ''' reader is disposed Oleg
+1 - the & had me caught out for a while, but then I remembered it :)
Yes... this is a great idea! I tried to add the "if not string.IsNullOrEmpty" to the service but didn't know the correct syntax: ex: While reader.Read() list.Add(New EmpHistDetails_vw() With { _ if not string.IsNullOrEmpty(.emp = reader("Emp").ToString(), _ Doesn't like my syntax...
@technette I modified my answer somewhat, so it now includes the entire snippet. I removed the nesting of the if statements because I think that it is not needed. If I am wrong then you can add the nesting back. Please let me know if this addresses your problem with blank rows in your grids. Thank you.
Oleg, Thank you for responding... I did try the code but the blank lines are still showing up. I think the problem is that the reader does not read and return separate result sets.. it is somehow reading all the items in then when I try to separate the different sets into grids, it reads like... Emp EmpName EducationLevel Institution Emp EmpName EducationLevel2 Institution2 Next Grid... Blank line for EducationLevel Info Blank line for EducationLevel Info RegDescription RegStateDescription RegDescription2 RegStateDescription I can probably fix the display if I can sort in descending order on specific fields like RegDescription, and EducationLevel... Trying to work with doing this in code... don't know how to accomplish that in the SQL data reader.
