Query samples on a self referenced table (Part-3)

In previous post we queried tables.

In this post we will join the tables. This will show Top Menu title and Sub Menu title in a row.

Linq

var menus = from t in Menus
  where t.MenuType == 1
  join s in Menus on t.MenuID equals s.MenuParent
  select new {
   TopMenu = t.Title,
   SubMenu = s.Title
  };

Lambda

Menus
   .Where (t => (t.MenuType == 1))
   .Join (
      Menus, 
      t => (Int32?)(t.MenuID), 
      s => s.MenuParent, 
      (t, s) => 
         new  
         {
            TopMenu = t.Title, 
            SubMenu = s.Title
         }
   )

SQL

DECLARE @p0 Int = 1

SELECT [t0].[Title] AS [TopMenu], [t1].[Title] AS [SubMenu]
FROM [Menu] AS [t0]
INNER JOIN [Menu] AS [t1] ON ([t0].[MenuID]) = [t1].[MenuParent]
WHERE [t0].[MenuType] = @p0

Read More

Query samples on a self referenced table (Part-2)

In previous post we created a database table, filled it with sample data and saw some basic queries.

In this post we will query the table in { Menu.Title, IEnumerable<menu> } form. This will show Top menu title and sub menu items in a row.

Linq

var menus = from t in Menus 
 where t.MenuType==1 
 select new {
  t.Title,
  SubMenus = from s in Menus
  where t.MenuType==1 && t.MenuID==s.MenuParent
  select s
 };

Lambda

Menus
   .Where (t => (t.MenuType == 1))
   .Select (
      t => 
         new  
         {
            Title = t.Title, 
            SubMenus = Menus
               .Where (s => ((t.MenuType == 1) && ((Int32?)(t.MenuID) == s.MenuParent)))
         }
   )

SQL

DECLARE @p0 Int = 1
DECLARE @p1 Int = 1

SELECT [t0].[Title], [t1].[MenuID], [t1].[Title] AS [Title2], [t1].[Url], [t1].[MenuParent], [t1].[MenuType], (
    SELECT COUNT(*)
    FROM [Menu] AS [t2]
    WHERE ([t0].[MenuType] = @p0) AND (([t0].[MenuID]) = [t2].[MenuParent])
    ) AS [value]
FROM [Menu] AS [t0]
LEFT OUTER JOIN [Menu] AS [t1] ON ([t0].[MenuType] = @p0) AND (([t0].[MenuID]) = [t1].[MenuParent])
WHERE [t0].[MenuType] = @p1

Read More

Query samples on a self referenced table (Part-1)

I will give you some query examples in Linq, Lambda Expression and SQL syntax.
Examples are build on one table that includes self referenced data. We will create a menu table on the database and fill it with some sample data. Some menu items are top menus and the others are subs. Sub menu items has a parent id which is referenced another menu item on the same table.

Step 1: Create menu table on SQL Server.

CREATE TABLE [dbo].Menu(
 [MenuID] [int] IDENTITY(1,1) NOT NULL,
 [Title] [nvarchar](50) NULL,
 [Url] [nvarchar](100) NULL,
 [MenuParent] [int] NULL, --If has parent fill this column with MenuID 
 [MenuType] [int] NULL --1: Top menu, 2:Sub menu
)

Step 2: Insert some data

INSERT INTO [dbo].[Menu] ([Title], [Url], [MenuParent], [MenuType]) VALUES ('Accounts', '.aspx', 0, 1) --ID=1

INSERT INTO [dbo].[Menu] ([Title], [Url], [MenuParent], [MenuType]) VALUES ('Users', '.aspx', 1, 2) --ID=2

INSERT INTO [dbo].[Menu] ([Title], [Url], [MenuParent], [MenuType]) VALUES ('Customers', '.aspx', 1, 2) --ID=3

INSERT INTO [dbo].[Menu] ([Title], [Url], [MenuParent], [MenuType]) VALUES ('Settings', '.aspx', 0, 1) --ID=4

INSERT INTO [dbo].[Menu] ([Title], [Url], [MenuParent], [MenuType]) VALUES ('App', '.aspx', 4, 2) --ID=5

INSERT INTO [dbo].[Menu] ([Title], [Url], [MenuParent], [MenuType]) VALUES ('Web', '.aspx', 4, 2) --ID=6

INSERT INTO [dbo].[Menu] ([Title], [Url], [MenuParent], [MenuType]) VALUES ('Service', '.aspx', 4, 2) --ID=7

INSERT INTO [dbo].[Menu] ([Title], [Url], [MenuParent], [MenuType]) VALUES ('Parameters', '.aspx', 0, 1) --ID=8

INSERT INTO [dbo].[Menu] ([Title], [Url], [MenuParent], [MenuType]) VALUES ('Account Type', '.aspx', 8, 2) --ID=9

INSERT INTO [dbo].[Menu] ([Title], [Url], [MenuParent], [MenuType]) VALUES ('Help', '.aspx', 0, 1) --ID=10

Step 3: Query table. In this example we will see some basic queries.

Basic Queries

Queries below selects top menus and sub menus.

Linq

var topMenus = from f in Menus where f.MenuType==1 select f;
var subMenus = from f in Menus where f.MenuType==2 select f;

Lambda

Menus
   .Where (f => (f.MenuType == 1))

Menus
   .Where (f => (f.MenuType == 2))

SQL

DECLARE @p0 Int = 1
SELECT [t0].[MenuID], [t0].[Title], [t0].[Url], [t0].[MenuParent], [t0].[MenuType]
FROM [Menu] AS [t0]
WHERE [t0].[MenuType] = @p0
GO

DECLARE @p1 Int = 2
SELECT [t0].[MenuID], [t0].[Title], [t0].[Url], [t0].[MenuParent], [t0].[MenuType]
FROM [Menu] AS [t0]
WHERE [t0].[MenuType] = @p1
GO

See Part-2 for the other queries.

Read More

[Tips] Create numbers 0 to 23 query

Query below creates numbers 0 to 23.

WITH Hours(h) AS
(
    SELECT 0
    UNION ALL
    SELECT h+1 FROM Hours WHERE h < 23
)
SELECT h FROM Hours

Read More

[Tips] Database convert to date

Oracle	SELECT to_date('01-JAN-2014', 'dd.MM.yyyy') FROM dual
SQL Server	SELECT CAST('01-JAN-2014' AS DATE)
SQL Server	SELECT CONVERT(DATE,'01/JAN/2014')

Read More

[Tips] IE Mode

How to force IE mode to the latest version?

<meta http-equiv="X-UA-Compatible" content="IE=edge">

More info

Read More

Working at home

Broadband networks, VPN, 3G, mobility, cloud... All these technologies provide people to communicate each other in a fast way. Video conferencing, online meetings can be easily done. Most of the people have computers, internet access, smart phones etc. But why we are still dependent to work at offices? 

Although technology adaptation is high we are still working traditionally. Face to face communication is preferred. Managers want to see their employees. Employees want to feel to be inside a community, use office facilities. People talk about flexible working conditions but most of the businesses are not ready to work at home.

Technology provides all the tools to work from the out of office. Microsoft has online meeting tools. But they don't say not to come to the office. All tools are just supplementary. If you are on vacation, traveling somewhere else etc. you can use technology. But you have to go to the office. Think Google. They have incredible office building. Everyone wants to work there.

Working at home may be a good idea but not ideal in the real world. So, companies should improve working place conditions to make people willing to be in the office.

Yahoo banned work at home and their reason is very clear. Yahoo's head of human resources, said:

To become the absolute best place to work, communication and collaboration will be important, so we need to be working side-by-side. That is why it is critical that we are all present in our offices. Some of the best decisions and insights come from hallway and cafeteria discussions, meeting new people, and impromptu team meetings. Speed and quality are often sacrificed when we work from home. We need to be one Yahoo!, and that starts with physically being together.

Read More

Rosyln

We saw many improvements, new libraries, new user interfaces in Visual Studio in years. But it is the first time that i heard about a compiler development. Microsoft is working on dynamic compiling technology - called Rosyln.

Current compiler depends on C++ infrastructure. New compiler will allow us to compile the code on .Net framework. I couldn't see any information about the compile time issue. It may be faster or not. But this is not the question yet. There are questions about this technology in my mind. What can we do with Roslyn?

• First thing is that bringing an idea to me about Visual Studio. Since the new compiler seems to be a service, a Visual Studio version may be just an editor on our computers. We may write code on different editors then check and build our code on the server. This will allow us to write code on the web interface. Even on MAC we will be able to write code. Because the compiler doesn't need to be on our client. Such a this scenario is mostly applicable on the web applications i think.

• Second thing is the security concerns. When we say dynamic compiling, it includes an input can create an assembly. How will we know someone write some code to run dynamically when we run an applicaiton?  The application can run a code that is saved in a file like this: scriptEngine.ExecuteFile("script.csx");

Microsoft's new technology will bring new and useful features. As far as i know it is published as a CTP release. In the future we will see the real life examples about it. 

Read More

Quick reference for regular expressions

Syntax:

Regex regex = new Regex(@"
^                   # anchor at the start
(?=.*\d)        # must contain at least one numeric character
(?=.*[a-z])    # must contain one lowercase character
(?=.*[A-Z])  # must contain one uppercase character
.{8,10}         # From 8 to 10 characters in length
\s                  # allows a space
$                  # anchor at the end",
RegexOptions.IgnorePatternWhitespace);

Code example:

   1:    public static string emailPattern = 

   2:            @"\w+([-+.']\w+)*@\w+([-.]\w+)*\.\w+([-.]\w+)*"; 

   3:    public static bool IsEmail(string email)

   4:    {

   5:       if (email != null) return Regex.IsMatch(email, emailPattern);

   6:       else return false;

   7:    }

Reference:

URL

Field	Expression	Format Samples
Validates a name. Allows up to 40 uppercase and lowercase characters and a few special characters that are common to some names. You can modify this list.
Name	^[a-zA-Z''-'\s]{1,40}$	John Doe O'Dell
Validates the format, type, and length of the supplied input field. The input must consist of 3 numeric characters followed by a dash, then 2 numeric characters followed by a dash, and then 4 numeric characters.
Social Security Number	^\d{3}-\d{2}-\d{4}$	111-11-1111
Validates a U.S. phone number. It must consist of 3 numeric characters, optionally enclosed in parentheses, followed by a set of 3 numeric characters and then a set of 4 numeric characters.
Phone Number	^[01]?[- .]?(\([2-9]\d{2}\)|[2-9]\d{2})[- .]?\d{3}[- .]?\d{4}$	(425) 555-0123 425-555-0123 425 555 0123 1-425-555-0123
Validates an e-mail address.
E-mail	^([0-9a-zA-Z]([-\.\w]*[0-9a-zA-Z])*@([0-9a-zA-Z][-\w]*[0-9a-zA-Z]\.)+[a-zA-Z]{2,9})$	me@example.com
Validates a URL
^(ht|f)tp(s?)\:\/\/[0-9a-zA-Z]([-.\w]*[0-9a-zA-Z])*(:(0-9)*)*(\/?)([a-zA-Z0-9\-\.\?\,\'\/\\\+&%\$#_]*)?$	http://www.msn.com	Validates a URL
Validates a U.S. ZIP Code. The code must consist of 5 or 9 numeric characters.
ZIP Code	^(\d{5}-\d{4}|\d{5}|\d{9})$|^([a-zA-Z]\d[a-zA-Z] \d[a-zA-Z]\d)$	12345
Validates a strong password. It must be between 8 and 10 characters, contain at least one digit and one alphabetic character, and must not contain special characters.
Password	(?!^[0-9]*$)(?!^[a-zA-Z]*$)^([a-zA-Z0-9]{8,10})$
Validates that the field contains an integer greater than zero.
Non- negative integer	^\d+$	0 986
Validates a positive currency amount. If there is a decimal point, it requires 2 numeric characters after the decimal point. For example, 3.00 is valid but 3.1 is not.
Currency (non- negative)	^\d+(\.\d\d)?$	1.00
Validates for a positive or negative currency amount. If there is a decimal point, it requires 2 numeric characters after the decimal point.
Currency (positive or negative)	^(-)?\d+(\.\d\d)?$	1.20

Read More

Obsolete types and members in .Net framework 4

Microsoft did it again! They changed many type and member definitions in framework 4. Same thing had happened when framework 3.5 published. For example in 3.5 version mail class was transferred to System.Net from System.Web and Page.RegisterStartupScript was changed to ClientScriptManager.RegisterStartupScript etc.

And now the most interesting change is about System.Data.OracleClient. It is not actually a change, they will not support OracleClient class in next versions. It seems they give the responsibility to Oracle to create new .net classes to work with new frameworks.

(In framework 4) The types in System.Data.OracleClient are deprecated. The types are supported in version 4 of the .NET Framework but will be removed in a future release. Microsoft recommends that you use a third-party Oracle provider.

To see full list click here.

Read More