May 30, 2014

I am trying to add a database diagram for the tables in Sql Server Management Studio and it's failing with the message "Database diagram support objects cannot be installed because this database does not have a valid owner". This is a bit weird as I thought the owner is already set.

Get System Information using C#

In this post, I will quickly show how to get system related information like machine name, user name, domain name, or how long ago the system started, etc. Most of these information can be retrieved from System.Environment class and I will take advantage of this class to display this data.

Below is an example of how most of these information can be obtained from the System.Environment class.

System Information using asp.n
  1. using System;
  2.  
  3. namespace WebApplication3
  4. {
  5.     public partial class Info : System.Web.UI.Page
  6.     {
  7.         protected void Page_Load(object sender, EventArgs e)
  8.         {
  9.             GetSystemInfo();
  10.         }
  11.  
  12.         private void GetSystemInfo()
  13.         {
  14.             string br = "<br/>";
  15.             Response.Write("Machine Name: " + Environment.MachineName + br);
  16.             Response.Write("OS Version: " + Environment.OSVersion + br);
  17.             Response.Write("System Directory: " + Environment.SystemDirectory + br);
  18.             Response.Write("Domain Name: " + Environment.UserDomainName + br);
  19.             Response.Write("User name: " + Environment.UserName + br);
  20.             Response.Write("CLR version: " + Environment.Version + br);
  21.               Response.Write("System Started: " + Environment.TickCount / (1000 * 60 * 60) + "hours ago"+ br);
  22.             Response.Write("No of processors: " + Environment.ProcessorCount + br);
  23.             Response.Write("Current Directory: " + Environment.CurrentDirectory + br);
  24.  
  25.         }
  26.     }
  27. }

May 29, 2014

Tables in a database can many to many relationships. For example, an Order can contain multiple Items and an Item can be in multiple Orders. In this scenario, the tables are linked to each other using a link table that contains the OrderId and ItemId. Here is how the table structure looks like.

Table Structure
  1. USE [Employee]
  2. GO
  3.  
  4. SET ANSI_NULLS ON
  5. GO
  6. SET QUOTED_IDENTIFIER ON
  7. GO
  8. CREATE TABLE [dbo].[Item](
  9.     [ItemId] [int] IDENTITY(1,1) NOT NULL,
  10.     [ItemName] [nvarchar](50) NOT NULL,
  11. CONSTRAINT [PK_Item] PRIMARY KEY CLUSTERED
  12. (
  13.     [ItemId] ASC
  14. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
  15. ) ON [PRIMARY]
  16.  
  17. GO
  18. /****** Object:  Table [dbo].[Order]    Script Date: 29/05/2014 7:04:15 PM ******/
  19. SET ANSI_NULLS ON
  20. GO
  21. SET QUOTED_IDENTIFIER ON
  22. GO
  23. CREATE TABLE [dbo].[Order](
  24.     [OrderId] [int] IDENTITY(1,1) NOT NULL,
  25.     [OrderName] [nvarchar](50) NOT NULL,
  26. CONSTRAINT [PK_Order] PRIMARY KEY CLUSTERED
  27. (
  28.     [OrderId] ASC
  29. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
  30. ) ON [PRIMARY]
  31.  
  32. GO
  33. /****** Object:  Table [dbo].[OrderItem]    Script Date: 29/05/2014 7:04:15 PM ******/
  34. SET ANSI_NULLS ON
  35. GO
  36. SET QUOTED_IDENTIFIER ON
  37. GO
  38. CREATE TABLE [dbo].[OrderItem](
  39.     [OrderId] [int] NOT NULL,
  40.     [ItemId] [int] NOT NULL
  41. ) ON [PRIMARY]
  42.  
  43. GO
  44.   ALTER TABLE [dbo].[OrderItem]  WITH CHECK ADDCONSTRAINT [FK_OrderItem_Item] FOREIGN KEY([ItemId])
  45. REFERENCES [dbo].[Item] ([ItemId])
  46. GO
  47. ALTER TABLE [dbo].[OrderItem] CHECK CONSTRAINT [FK_OrderItem_Item]
  48. GO
  49.   ALTER TABLE [dbo].[OrderItem]  WITH CHECK ADDCONSTRAINT [FK_OrderItem_Order] FOREIGN KEY([OrderId])
  50. REFERENCES [dbo].[Order] ([OrderId])
  51. GO
  52. ALTER TABLE [dbo].[OrderItem] CHECK CONSTRAINT [FK_OrderItem_Order]
  53. GO

 

To import these tables in Entity Framework model, I will create a new edmx file and drag and drop the tables into the designer view. You will notice that the link table does not appear in the designer view and the link is denoted by a line with multiple (*) sign at both ends.

image

Now the model can be queried in the usual way as we would query any other entity framework model. Here is an example on how to get Items by OrderId and to add Items and Orders in the database.

Query Model
  1. using System.Collections.Generic;
  2. using System.Linq;
  3.  
  4. namespace WebApplication3.Helper
  5. {
  6.     public class ItemOrderBll
  7.     {
  8.         private List< Model.Item> GetItemsByOrderId(int orderId)
  9.         {
  10.             using(Model.EmployeeEntities en = new Model.EmployeeEntities())
  11.             {
  12.                 return (from c in en.Items
  13.                         from d in en.Orders
  14.                         where d.OrderId == orderId
  15.                         select c).ToList();
  16.             }
  17.         }
  18.  
  19.         private void AddStuff()
  20.         {
  21.             using (Model.EmployeeEntities en = new Model.EmployeeEntities())
  22.             {
  23.                 Model.Item item1 = new Model.Item() { ItemName = "Item 1" };
  24.                 Model.Item item2 = new Model.Item() { ItemName = "Item 2" };
  25.  
  26.                 Model.Order order1 = new Model.Order() { OrderName = "Order 1" };
  27.                 Model.Order order2 = new Model.Order() { OrderName = "Order 2" };
  28.  
  29.                 order1.Items.Add(item1);
  30.                 item2.Orders.Add(order2);
  31.  
  32.                 en.SaveChanges();
  33.             }
  34.         }
  35.     }
  36. }

Remove Duplicates from a List in C#

A List can contain simple types like int, string or other objects. Other day I was working and had to remove duplicates from a List collection. In this post, I will talk about how I remove the duplicates using minimum code. To start with a clue, I used a HashSet to remove the duplicates.

HashSet has the feature to only contain distinct items within it. Therefore, I could convert the List to HashSet and back to a list and this would remove all the duplicates for me. For HashSets to only contain unique items, the object needs to override the Equals and GetHashCode methods. Below is an example of how this can be done. I have created an Employee class with few members and overridden Equals and GetHashCode methods. Then I have another class EmployeeCollection where I have removed the duplicates.

Remove Duplicates
  1. using System;
  2. using System.Collections.Generic;
  3.  
  4. namespace WebApplication3.Helper
  5. {
  6.     public class EmployeeCollection
  7.     {
  8.         private List<Employee> RemoveDuplicates()
  9.         {
  10.             List<Employee> list = new List<Employee>();
  11.             //load employees to list
  12.             //list.Add(new Employee() { FirstName = "John" });
  13.  
  14.             //HashSet removes duplicates
  15.             HashSet<Employee> emSet = new HashSet<Employee>(list);
  16.             return new List<Employee>(emSet);
  17.         }
  18.     }
  19.  
  20.     public class Employee
  21.     {
  22.         public string FirstName { get; set; }
  23.         public string LastName { get; set; }
  24.         public DateTime DateOfBirth { get; set; }
  25.         public DateTime DateJoined { get; set; }
  26.         public bool IsRetired { get; set; }
  27.  
  28.         public override string ToString()
  29.         {
  30.             return "First Name: " + FirstName + "\nLastName: " + LastName + "\nDate of Birth: " + DateOfBirth
  31.                 + "\nDate Joined: " + DateJoined + "\nIs Retired? " + IsRetired;
  32.         }
  33.  
  34.         public override bool Equals(object obj)
  35.         {
  36.             Employee em = (Employee)obj;
  37.             return this.FirstName == em.FirstName
  38.                 && this.LastName == em.LastName
  39.                 && this.DateOfBirth == em.DateOfBirth
  40.                 && this.DateJoined == em.DateJoined
  41.                 && this.IsRetired == em.IsRetired;
  42.         }
  43.  
  44.         public override int GetHashCode()
  45.         {
  46.             unchecked
  47.             {
  48.                 int hash = 23;
  49.  
  50.                 hash = FirstName == null ? hash * 11 : hash * 17 + FirstName.GetHashCode();
  51.                 hash = LastName == null ? hash * 19 : hash * 17 + LastName.GetHashCode();
  52.                 hash = DateOfBirth == null ? hash * 13 : hash * 17 + DateOfBirth.GetHashCode();
  53.                 hash = DateJoined == null ? hash * 7 : hash * 17 + DateJoined.GetHashCode();
  54.                 hash = IsRetired == null ? hash * 7 : hash * 17 + IsRetired.GetHashCode();
  55.                 return hash;
  56.             }
  57.         }  
  58.     }
  59. }

In the previous post, I showed how to save an image to a database. In this post, I will show how to retrieve an image and display it in an asp.net page. Note that the image is saved in bytes format rather than as a file and displaying it in an asp.net Image control requires more work. Therefore, in this post, I have used the html img element to display the image.

May 26, 2014

Save Image to Sql Server

Sql server allows for the "Image" data type that can be used to save images in databases. In this post, I will show how to save an image to the database. In a nutshell, I will use a File Upload control to upload the image and use entity framework to save the image.

May 24, 2014

This should have been an easy task to create a composite key using the design view in sql server management studio but it took me a while to figure it out.  Basically, creating the composite key is easy and can be done in the following way.

I am importing an excel spread sheet to sql server database but for some reason I am getting the error “The value violated the integrity constraints for the column”. I checked the table data type and it should really just work but could not get it to work.

I am importing an excel spread sheet into a sql server database and is getting the error "DTS_E_INDUCEDTRANSFORMFAILUREONERROR" and it more messages like "The value could not be converted because of a potential loss of data" and "The source column is of nVarchar(272 -length) and the destination column is nVarchar(256 -length)"

So, really there are 2 failure messages here. After some investigation, I realised that with the first message "The value could not be converted because of a potential loss of data", it's because one of the rows had a double value whereas I initially set the sql table to have int data type. As a result, it failed while converting this particular row. Changing the data type to float fixed this issue.

The second error message is because the string value in the excel is lot bigger in length compared to the max length set in the sql table. Changing the max number of characters in sql table fixed this issue.

Overall, importing an excel spread sheet to sql table is quite easy provided correct data types are applied to the sql table.

May 7, 2014

In asp.net, Response.Redirect, PostBackUrl and Server.Transfer are similar but all of these are somewhat different. In this post, I will go through the differences of these three approaches.

Response.Redirect

This will cause the browser to load the specified page. The page may or may not be part of the existing application. This will result in a http get and the new page will not have access to any data on the current page. This can be triggered by any control that performs postback or even on Page_Load event.

Response.Redirect can be set in the following way.

  1. protected void b1_Click(object sender, EventArgs e)
  2. {
  3.     Response.Redirect("some page");
  4. }

 

PostBackUrl

PostBackUrl property can only be set on an asp.net button control. The UseSubmitBehavior property needs to be set to true. This will result in a http post and the new page can retrieve current page's data using Request.Form and Page.PreviousPage. The user will notice the change in the once the button is clicked. PostBackUrl can contain pages from current application or any other pages as well.

PostBackUrl is set in the following way.

  1. <asp:Button ID="b1" runat="server" Text="postback" PostBackUrl="~/PostBack2.aspx" UseSubmitBehavior="true" />

 

To retrieve data,

protected void Page_Load(object sender, EventArgs e)
{
    l1.Text = Request.Form["t1"];
    l1.Text += ((TextBox)this.PreviousPage.FindControl("t1")).Text;
}

Server.Transfer

Server.Transfer performs a http post and data can be obtained similar to PostBackUrl, that is, using both Request.Form and Page.PreviousPage. Server.Transfer can only transfer to a page in the current application. Also the url does not change in the browser. Like Response.Redirect, this can be triggered by any control that performs postback or even on Page_Load event.

Server.Transfer can be used in the following way.

protected void b2_Click(object sender, EventArgs e)
{
    Server.Transfer("Postback2.aspx");
}

To retrieve data,

protected void Page_Load(object sender, EventArgs e)
{
    l1.Text = Request.Form["t1"];
    l1.Text += ((TextBox)this.PreviousPage.FindControl("t1")).Text;
}

May 6, 2014

jQuery not working within Update Panel

I wrote this jQuery validation for a site and it all worked in my test page but for some reason it stopped working in the real page. Since the real page is a currently live, I started creating and testing the validation on the dummy page first. After some debugging for finding the issue, I realised that the jQuery is not running within the actual page since the controls and html elements are within the Update Panel.

May 5, 2014

Browser Link in Visual Studio 2013

Browser Link is a new feature in Visual Studio 2013 and it allows refreshing of multiple browsers at once. This is great for web developers as now we can test the application in multiple browsers and make modifications and see the changes all at once.

May 2, 2014

We may need to take a database offline various reasons, for example, it was a test database and we don’t need it anymore or the application has been decommissioned, etc. Sometimes, I have noticed that Sql Server Management Studio becomes unresponsive and hangs for a long time while taking the database offline. In this post I will talk about how to fix this issue.

Usually, the database can be taken offline by right-clicking on the name of the database and clicking Tasks > Take Offline as shown below.

image

Once I click on “Take Offline”, I expect the database to be taken offline but instead it hangs. To avoid the issue, I can write sql script to take the database offline. Following is the script that will take the database offline and then bring it online.

Take Database Offline / Online
  1. --take offline
  2. USE master
  3. GO
  4. ALTER DATABASE [database_name]
  5. SET OFFLINE WITH ROLLBACK IMMEDIATE
  6. GO
  7.  
  8. --take online
  9. USE master
  10. GO
  11. ALTER DATABASE [database_name]
  12. SET ONLINE
  13. GO

May 1, 2014

Most (if not all) web sites will have a default start page for example, default.aspx, index.aspx, home.aspx, etc. The default start page kicks in when someone goes to a url that does not refer to the page (no file extension) but possibly to a folder - for example, example.com/ or example.com/test . Without the default page, the user will be shown or not shown the directory listing page depending on the IIS configuration. For security reasons, it's better not to show end users the directory listing page. I once saw an application with directory listing that was showing the log folder and files.

Reference: Shahed Kazi at AspNetify.com