December 20, 2013

In the previous post, I showed how to use the group a ListView by a predefined number of items. Though this can be useful for displaying, it lacks the feature of not being able to group items by column or a range of data. In this post, I will show how to group a ListView by a range of data within a column.  I will the same data as in the previous post and modify it so it is ordered by age as shown below. I have done the ordering manually. I will bind the ListView and display data so it is grouped by age - for examples, it will be grouped by 10-20 years old, 20-30 years old, etc.
In the ListView declaration, I have changed the display to a list rather than the table for convenience. The ListView declaration is below.
ListView declaration
  1.     <asp:ListView ID="lv2" runat="server"ItemPlaceholderID="itemPlaceholder">
  2.  
  3.       <LayoutTemplate>
  4.         <ul>
  5.           <asp:Literal id="itemPlaceholder" runat="server" />
  6.         </ul>
  7.       </LayoutTemplate>
  8.      
  9.  
  10.       <ItemTemplate>
  11.                 <%# AddHeader() %>
  12.           <li><%# Eval("Name") %> : <%# Eval("Age") %></li>
  13.               </ItemTemplate>
  14.  
  15.   </asp:ListView>

I have also added a method AddHeader() as the first item within the Item Template. This method will add a list item that will be marked as the group identifier for the data. Here's the backend code for AddHeader() method. It's quite simple. It's important to note that updates the header property when the range changes and will only outputs the list item when there is a change.
AddHeader()
  1. public string header = string.Empty;
  2. public string AddHeader()
  3. {
  4.     int age = (int)Eval("Age");
  5.     string ans = "";
  6.  
  7.     if (age >= 10 && age < 20)
  8.         ans = "10 - 20";
  9.     else if (age >= 20 && age < 30)
  10.         ans = "20 - 30";
  11.     else if (age >= 30 && age < 40)
  12.         ans = "30 - 40";
  13.     else if (age >= 40 && age < 50)
  14.         ans = "40 - 50";
  15.  
  16.     if (ans == header)
  17.         ans = "";
  18.     else
  19.         header = ans;
  20.  
  21.     if (ans == "")
  22.         return "";
  23.     else
  24.         return "<li class='liheader'>" + ans + "</li>";
  25. }

I have also added css for styling and you can modify it as you see fit. Here's the css and the display when the code is run.
CSS
  1. <style>
  2.     .liheader {
  3.         font-weight:bold; background-color:gray;
  4.     }
  5.     li {
  6.         list-style: none;
  7.         width: 150px;
  8.         border:1px solid gray;
  9.         padding-left:4px;
  10.     }
  11. </style>
image
Here is the full code from code-behind in case you haven’t read my previous post.
Code behind - full page
  1. public partial class GroupList : System.Web.UI.Page
  2. {
  3.     protected void Page_Load(object sender, EventArgs e)
  4.     {
  5.         lv2.DataSource = GetList();
  6.         lv2.DataBind();
  7.     }
  8.  
  9.     private List<PersonClass> GetList()
  10.     {
  11.         List<PersonClass> list = new List<PersonClass>();
  12.  
  13.         list.Add(new PersonClass { Name = "Don", Age = 25 });
  14.         list.Add(new PersonClass { Name = "Andrew", Age = 25 });
  15.         list.Add(new PersonClass { Name = "Janette", Age = 27 });
  16.         list.Add(new PersonClass { Name = "Smith", Age = 30 });
  17.         list.Add(new PersonClass { Name = "Jane", Age = 32 });
  18.         list.Add(new PersonClass { Name = "Glenn", Age = 35 });
  19.         list.Add(new PersonClass { Name = "Jack", Age = 35 });
  20.         list.Add(new PersonClass { Name = "John", Age = 39 });
  21.         list.Add(new PersonClass { Name = "Peter", Age = 39 });
  22.         list.Add(new PersonClass { Name = "Robert", Age = 40 });
  23.         
  24.         return list;
  25.     }
  26.  
  27.     public string header = string.Empty;
  28.     public string AddHeader()
  29.     {
  30.         int age = (int)Eval("Age");
  31.         string ans = "";
  32.  
  33.         if (age >= 10 && age < 20)
  34.             ans = "10 - 20";
  35.         else if (age >= 20 && age < 30)
  36.             ans = "20 - 30";
  37.         else if (age >= 30 && age < 40)
  38.             ans = "30 - 40";
  39.         else if (age >= 40 && age < 50)
  40.             ans = "40 - 50";
  41.  
  42.         if (ans == header)
  43.             ans = "";
  44.         else
  45.             header = ans;
  46.  
  47.         if (ans == "")
  48.             return "";
  49.         else
  50.             return "<li class='liheader'>" + ans + "</li>";
  51.     }
  52. }
  53.  
  54. public class PersonClass
  55. {
  56.     public string Name { get; set; }
  57.     public int Age { get; set; }
  58. }

December 19, 2013

Group Data Item in ListView

In asp.net ListView, there is an option to group item by count - that is group a number items together. In a ListView, usually, the data will appear in the format as set within the Item Template. With the grouping option - the data will appear in groups of x many items where x is set within the GroupItemCount property of the ListView. For example, this would be useful if you choose to diaplay items in a table with 4 columns and rows. In this case, if there 22 rows of data - there will be 6 rows within the table displayed (5 rows of 4 columns and last row with 2 columns of data).

So, to use the grouping - declare the ListView like below.

ListView declaration
  1. <asp:ListView ID="lv1" runat="server" GroupPlaceholderID="groupPlaceholder" ItemPlaceholderID="itemPlaceholder" GroupItemCount="2">
  2.  
  3.     <LayoutTemplate>
  4.       <table id="main" border="1">
  5.         <asp:Literal id="groupPlaceholder" runat="server" />
  6.       </table>
  7.     </LayoutTemplate>
  8.  
  9.     <GroupTemplate>
  10.         <tr><asp:Literal runat="server" id="itemPlaceholder" /></tr>
  11.     </GroupTemplate>
  12.  
  13.               <ItemTemplate>
  14.         <td><%# Eval("Name") %> :  <%# Eval("Age") %></td>
  15.             </ItemTemplate>
  16.  
  17. </asp:ListView>

 

Note that there is a group template and an item template. The group template is where the data items will be placed. The group and item place holder ids are set from the ListView declaration. The place holder controls should be marked with runat="server". Otherwise, it won't run.

In the backend, I have set up a class - PersonClass and created a List of PersonClass objects. Finally, I have binded the List of PersonClass objects to the  ListView. Here's the code for that.

Backend
  1. public partial class GroupList : System.Web.UI.Page
  2. {
  3.     protected void Page_Load(object sender, EventArgs e)
  4.     {
  5.         lv1.DataSource = GetList();
  6.         lv1.DataBind();
  7.     }
  8.  
  9.     private List<PersonClass> GetList()
  10.     {
  11.         List<PersonClass> list = new List<PersonClass>();
  12.  
  13.         list.Add(new PersonClass { Name = "Smith", Age = 30 });
  14.         list.Add(new PersonClass { Name = "John", Age = 39 });
  15.         list.Add(new PersonClass { Name = "Peter", Age = 39 });
  16.         list.Add(new PersonClass { Name = "Jack", Age = 35 });
  17.         list.Add(new PersonClass { Name = "Andrew", Age = 25 });
  18.         list.Add(new PersonClass { Name = "Robert", Age = 40 });
  19.         list.Add(new PersonClass { Name = "Janette", Age = 27 });
  20.         list.Add(new PersonClass { Name = "Jane", Age = 32 });
  21.         list.Add(new PersonClass { Name = "Glenn", Age = 35 });
  22.         list.Add(new PersonClass { Name = "Don", Age = 25 });
  23.         return list;
  24.     }
  25. }
  26.  
  27. public class PersonClass
  28. {
  29.     public string Name { get; set; }
  30.     public int Age { get; set; }
  31. }

Now, if I run the code the following will be displayed. You can see there are 2 columns.

image

If I change the GroupItemCount property to 3, the following screen will be displayed. Now there are 3 columns displayed.

image

While working with Sql Server, errors might occur when a table is altered. The table can be altered by adding a new column, deleting an existing column, or changing the data type of any of the columns. The error message that might come up is "Prevent saving changes that require the table re-creation".
To correct the above error in Sql Server Management Studio, click on Options from top menu.


1
From the Options popup, select the "Designers" option from the left menu and then untick "Prevent saving changes that require table re-creation". Setting this option will allow saving the changes in the database.

December 18, 2013

add primary key using sql

In the previous example, I showed how to add primary key from the designer view of the table in Sql Server Management Studio. In this example, I will show how to add primary key using sql. Since the column is already added to the table, we will use Alter Table and add the primary key constant to the table. Here's the sql to add the primary key.
  1. ALTER TABLE Customer
  2. ADD CONSTRAINT pk_Email PRIMARY KEY (EmailAddress)

December 17, 2013

Using the same database and tables setup in previous post, we will add a primary key for the table. The primary key is a column that uniquely identify a row of data within a table. So, for example, in the Customer table, you can have 2 customers with name - Steve Smith and you can easily differentiate between them. This might also mean duplicated fields - like the same customer might have been added multiple times and we cannot differentiate them. To allow for these scenarios, a unique key is required.

In one of the previous posts, I showed how to create a database from Sql Server Management Studio, in this one I will showed how to create the database from script. To do so, I will use the same database to show the create database script.
Once a database is created, scripts can be generated for it by right clicking the database name and selecting "Script Database as" > "Create To" as shown below.
1
This generates the scripts as per below.
Sql Script
  1. USE [master]
  2. GO
  3.  
  4. /****** Object:  Database [TestDB]    Script Date: 13/12/2013 2:13:54 PM ******/
  5. CREATE DATABASE [TestDB]
  6. CONTAINMENT = NONE
  7. ON  PRIMARY
  8. ( NAME = N'TestDB', FILENAME = N'c:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\TestDB.mdf' , SIZE = 5120KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
  9. LOG ON
  10. ( NAME = N'TestDB_log', FILENAME = N'c:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\TestDB_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
  11. GO
  12.  
  13. ALTER DATABASE [TestDB] SET COMPATIBILITY_LEVEL = 110
  14. GO
  15.  
  16. IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
  17. begin
  18. EXEC [TestDB].[dbo].[sp_fulltext_database] @action = 'enable'
  19. end
  20. GO
  21.  
  22. ALTER DATABASE [TestDB] SET ANSI_NULL_DEFAULT OFF
  23. GO
  24.  
  25. ALTER DATABASE [TestDB] SET ANSI_NULLS OFF
  26. GO
  27.  
  28. ALTER DATABASE [TestDB] SET ANSI_PADDING OFF
  29. GO
  30.  
  31. ALTER DATABASE [TestDB] SET ANSI_WARNINGS OFF
  32. GO
  33.  
  34. ALTER DATABASE [TestDB] SET ARITHABORT OFF
  35. GO
  36.  
  37. ALTER DATABASE [TestDB] SET AUTO_CLOSE OFF
  38. GO
  39.  
  40. ALTER DATABASE [TestDB] SET AUTO_CREATE_STATISTICS ON
  41. GO
  42.  
  43. ALTER DATABASE [TestDB] SET AUTO_SHRINK OFF
  44. GO
  45.  
  46. ALTER DATABASE [TestDB] SET AUTO_UPDATE_STATISTICS ON
  47. GO
  48.  
  49. ALTER DATABASE [TestDB] SET CURSOR_CLOSE_ON_COMMIT OFF
  50. GO
  51.  
  52.   ALTER DATABASE [TestDB] SET CURSOR_DEFAULTGLOBAL
  53. GO
  54.  
  55. ALTER DATABASE [TestDB] SET CONCAT_NULL_YIELDS_NULL OFF
  56. GO
  57.  
  58. ALTER DATABASE [TestDB] SET NUMERIC_ROUNDABORT OFF
  59. GO
  60.  
  61. ALTER DATABASE [TestDB] SET QUOTED_IDENTIFIER OFF
  62. GO
  63.  
  64. ALTER DATABASE [TestDB] SET RECURSIVE_TRIGGERS OFF
  65. GO
  66.  
  67.   ALTER DATABASE [TestDB] SETDISABLE_BROKER
  68. GO
  69.  
  70. ALTER DATABASE [TestDB] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
  71. GO
  72.  
  73. ALTER DATABASE [TestDB] SET DATE_CORRELATION_OPTIMIZATION OFF
  74. GO
  75.  
  76. ALTER DATABASE [TestDB] SET TRUSTWORTHY OFF
  77. GO
  78.  
  79. ALTER DATABASE [TestDB] SET ALLOW_SNAPSHOT_ISOLATION OFF
  80. GO
  81.  
  82. ALTER DATABASE [TestDB] SET PARAMETERIZATION SIMPLE
  83. GO
  84.  
  85. ALTER DATABASE [TestDB] SET READ_COMMITTED_SNAPSHOT OFF
  86. GO
  87.  
  88. ALTER DATABASE [TestDB] SET HONOR_BROKER_PRIORITY OFF
  89. GO
  90.  
  91. ALTER DATABASE [TestDB] SET RECOVERY SIMPLE
  92. GO
  93.  
  94.   ALTER DATABASE [TestDB] SETMULTI_USER
  95. GO
  96.  
  97.   ALTER DATABASE [TestDB] SET PAGE_VERIFY CHECKSUM
  98. GO
  99.  
  100. ALTER DATABASE [TestDB] SET DB_CHAINING OFF
  101. GO
  102.  
  103. ALTER DATABASE [TestDB] SET FILESTREAM( NON_TRANSACTED_ACCESS = OFF )
  104. GO
  105.  
  106. ALTER DATABASE [TestDB] SET TARGET_RECOVERY_TIME = 0 SECONDS
  107. GO
  108.  
  109.   ALTER DATABASE [TestDB] SETREAD_WRITE
  110. GO

If you notice, it has lot of options that we skipped while creating the database. For simplicity, we will remove these options. This options will automatically default to its default value. Here's the shortened script.
Sql Script - Shortened
  1. USE [master]
  2. GO
  3.  
  4. /****** Object:  Database [TestDB]    Script Date: 13/12/2013 2:13:54 PM ******/
  5. CREATE DATABASE [TestDB]
  6. CONTAINMENT = NONE
  7. ON  PRIMARY
  8. ( NAME = N'TestDB', FILENAME = N'c:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\TestDB.mdf' , SIZE = 5120KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
  9. LOG ON
  10. ( NAME = N'TestDB_log', FILENAME = N'c:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\TestDB_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
  11. GO

Once the script is run, the database will be created. Note that, if the database already exists, it won't work. To test the script in that case, just rename the database name and file names and then execute the script.

December 16, 2013

Enable SSL from IIS 7

SSL or Secure Socket Layer is needed for enhanced security on a website. With SSL, websites are viewed on the browser with https. To enable SSL, a certificate must be purchased from authorities. Free or cheap ones are available as well like from  http://www.startssl.com . However, after purchasing and setting up the certificate, SSL needs to be enabled from the web server - in this case, IIS.

To enable SSL on IIS, open IIS and then click on site under "Sites" and then click on Bindings from the "Edit Site" section.

1


If https does not exist, add https by clicking on Add like below. Also, select the certificate to bind https to.

2

After SSL is added, click on SSL settings within IIS subheading for the website.

3

On the Settings panel, Choose Require SSL.

4

That should be it. You should be done now.

December 14, 2013

Once the database has been created, tables need to be added to the database. Without the tables, the database has no data. In SQL Server Management Studio, to add a new table, simply right click on the "Tables" and click "New Table".

12

December 13, 2013

To create a sql server database using management studio, open the management studio and connect to the sql server. A windows login or a sql server login is required. During connection, a prompt like below will appear where the details can be filled in.

December 12, 2013

There is not a lot of differences between window's onload and body onload events except that window's onload event fires at start whereas body's onload event fires once all content is downloaded. So, in case, we need to find an element and update style or content, etc to it, then, body onload is the way to go. Whereas if it does not matter - then we can use either window's onload or body onload event. Below is an example html source code to check when each of the events is fired.

Add Static Row in ListView

ListView is a standard asp.net data control and it allows lot of customisation of the view to represent data. We can display any html within the ListView as shown in an earlier post. In this post, I will show how to add a static row at the start or end of the ListView. Let's say our ListView initially look below.

December 10, 2013

ListView in asp.net is a great control to display data in any customised format. We can also display images, videos, hyperlinks or pretty much anything that ordinary html can. This is unlike other controls like GridView or at least this is much easier to do with ListView than with GridView for example.
In this scenario, we have a ListView that is binded to a collection that has one of the properties "FileName" which can either be an image or a video. There is no field in the collection that tells whether FileName refers to a video or an image but it's either an image or a video for sure. To display the item - video or image - properly in the ListView, a method is written that compares the file extension and decided whether it's an image or a video. Here's the source code for that.

I use Visual Studio 2012 and I have noticed that every now and then the UI crashes. More often than not the issues happen with the Dark theme - sometimes the UI will look strange and not workable with. Other times the UI is workable but it would look funny - like highlighting would not work properly or the line numbers will have the white theme when the UI will have the dark theme. Some of the screen shots for this behaviour is below.

Reference: Shahed Kazi at AspNetify.com