ASP.Net Nova

Left out Join implementation in Linq.. hmm first I have no idea how to proceed.

Almost in all the attempt the syntax errors used to appear... and there is no 

relevant information available online from google search.

After giving a long try and error finally i was able to use Linq for

 Left outer join and implemented Page indexing too.

The screen shot explains of "Sales" object has many relations with other tables and

 the function given below will explain the how is implement left join.

public static List<Object> GetSaleList(int pageIndex, int pageSize)
{   
GeneralDataContext db = new GeneralDataContext();
var ObjSale = (from S in db.Sales
   join Prop in db.Properties on S.PropertyId equals Prop.PropertyId 

   into tempProp from PROP in tempProp.DefaultIfEmpty()

       join ImgD in db.ImageDetails on S.ShowImageID equals ImgD.Id 

   into tempImg from IMGD in tempImg.DefaultIfEmpty()
   join Countries in db.Countries on S.TargetCountry equals Countries.ID 

   into tempCountry from COUNTRIES in tempCountry.DefaultIfEmpty()
   join Curr in db.Currencies on S.Currency equals Curr.Id    

   into tempCurrency from CURRENCIES in tempCurrency.DefaultIfEmpty()                       
select new { S.Id,S.PropertyId,S.Link,S.ImageId,S.TargetCountry,S.Currency,

     S.SellingPrice,S.Sell,S.Rent,S.Lease,
     PROP.PropertyType,IMGD.SmallImagePath, COUNTRIES.COUNTRY1,

       CURRENCIES.CurrencySign
     });

 ObjSale = ObjSale.Skip((pageIndex - 1) * pageSize).Take(pageSize);
 return ObjSale.ToList();


}

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


ALTER PROCEDURE [dbo].[proc_KeySearch] 
(
 
    @keyword                    varchar(256)
)
AS

 


DECLARE @SearchStr nvarchar(100)   
   SET @SearchStr = @keyword      
   CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))    
   SET NOCOUNT ON  
   DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)    
   SET  @TableName = ''  
   SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')    
   WHILE @TableName IS NOT NULL  
   BEGIN  
         SET @ColumnName = ''    
         SET @TableName =   
         (  
               SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))  
               FROM INFORMATION_SCHEMA.TABLES  
               WHERE       TABLE_TYPE = 'BASE TABLE'  
                     AND   QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName  
                     AND   OBJECTPROPERTY(  
                                 OBJECT_ID(  
                                       QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)  
                                        ), 'IsMSShipped'  
                                        ) = 0  
         )    
         WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)  
         BEGIN  
               SET @ColumnName =  
               (  
                     SELECT MIN(QUOTENAME(COLUMN_NAME))  
                     FROM INFORMATION_SCHEMA.COLUMNS  
                     WHERE       TABLE_SCHEMA      = PARSENAME(@TableName, 2)  
                           AND   TABLE_NAME  = PARSENAME(@TableName, 1)  
                           AND   DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar',  
                           'numeric','decimal', 'double', 'money')  
                           AND   QUOTENAME(COLUMN_NAME) > @ColumnName  
               )  
  
               IF @ColumnName IS NOT NULL  
               BEGIN  
                     INSERT INTO #Results  
                     EXEC  
                     (  
                           'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630)   
                           FROM ' + @TableName + ' (NOLOCK) ' +  
                           ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2  
                     )  
               END  
         END     
   END    
   SELECT ColumnName, ColumnValue FROM #Results    
DROP TABLE #Results    

Resharper for VS 2010 is about to come soon. Like me many of programmers are not using VS2010 because of Resharper.

More about Resharper pulgin

Resharper from wikipedia
From JetBrain Blog
For Resharper benefits go through these demo
Demos

JavaScript

function resize_iframe(frameid) {
       if (frameid != null && frameid != 'undefined') {
           var height = window.innerWidth; //Firefox
           if (document.body.clientHeight) {
               height = document.body.clientHeight; //IE
           }           
           document.getElementById(frameid).style.height = parseInt(height - document.getElementById(frameid).offsetTop - 8) + "px";         
       }
   }

   // this will resize the iframe every
   // time you change the size of the window.
   window.onresize = resize_iframe;
 

 HTML :



                                                     
                                                    
 



 

Stunning charts on the web page makes the user to stare on the screen for long time and use it more often. Yes charts really convey many messages in one shot.

To implement charts in Asp.net we have 2 best options.
1. Microsoft Charts
2. Google Chart API.

Microsoft Charts :
* Advantages:
1. Easy to use.
2. No third party components.
3. Attractive and pleasing.
4. Quality of charts are superb.
5. Offcourse it is free. :-) and no lic prob for this chart controls.
6. Supports complex charting and drill down charts and reports.

* Disadvantages
1. For more complex charts Server side code is complex and difficult.
2. Comparatively slow but the slowness is worth for the information and UI.

You can have more info from here on MS Charts Installing MS chart adding into vs 2008 toolbox and using MS chart with sample code

Google Charts :
Reference links : Google Chart Reference

*Advantages:
1. Very simple to design for simple charts
2. Light weighted and faster
3. Code is easy to understand and attributes are less to customize.
4. No installation setup is required works on javascript.
5. It is free.
* Disadvantages:
1. Since api works on online javascript so browser should be javascript enabled.
2. Presence of internet is a must.

Both has its own advantages and shortcomings but as a developer we must know what is required for us is the best.



Before generating a chart and displaying it was a nightmare and those who like to choose to develop or spend time on charts was used to called a "Hero of Developers" no matter whether it is a 3rd party component or develop by using HTML, tr, td, images, div and css combinations.

Google Chart Api like Google map api has a made a chart development tremendously easier and faster which almost a fresh student also can make it.

Here I am showing a simple Area graph using Google Chart API. It is so easy that anybody will feel no need of explanation on the html. It requires no serverside code only javascript.

Happy coding "Hero of Developers".

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">



<html>

  <head>

    <script type="text/javascript" src="http://www.google.com/jsapi"></script>

    <script type="text/javascript">

      google.load("visualization", "1", {packages:["areachart"]});

      google.setOnLoadCallback(drawChart);

      function drawChart() {

        var data = new google.visualization.DataTable();

        data.addColumn('string', 'Year');

        data.addColumn('number', 'Actual Targets achieved');

        data.addColumn('number', 'Projected Targets');



        data.addRows(9);

        data.setValue(0, 0, '2000');

        data.setValue(0, 1, 2);

        data.setValue(0, 2, 0);

       

        data.setValue(1, 0, '2001');

        data.setValue(1, 1, 30);

        data.setValue(1, 2, 25);



        data.setValue(2, 0, '2002');

        data.setValue(2, 1, 45);

        data.setValue(2, 2, 70);





        data.setValue(3, 0, '2003');

        data.setValue(3, 1, 85);

        data.setValue(3, 2, 80);



        data.setValue(4, 0, '2004');

        data.setValue(4, 1, 100);

        data.setValue(4, 2, 0);



        data.setValue(5, 0, '2005');

        data.setValue(5, 1, 20);

        data.setValue(5, 2, 0);

        

        data.setValue(6, 0, '');

        data.setValue(6, 1, 20);

        data.setValue(6, 2, 0);

        

        data.setValue(7, 0, '');

        data.setValue(7, 1, 20);

        data.setValue(7, 2, 0);

        

        data.setValue(8, 0, '');

        data.setValue(8, 1, 20);

        data.setValue(8, 2, 0);

         

        

        var chart = new google.visualization.AreaChart(document.getElementById('chart_div'));

        chart.draw(data, { width: 600, height: 340, legend: 'bottom', title: ' Performance ' });

      }

    </script>

  </head>



  <body style="font-family:Calibri;">

    <div id="chart_div"></div>

  </body>

</html>

Here comes another approach to build the tree structure - hierarchial flow of information. This is an simple and raw approach but effective to show the structure of hierarchy.

Let us create the database now....

GO
/****** Object:  Table [dbo].[Comments]    Script Date: 10/12/2009 14:06:05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Comments](
   [ID] [int] IDENTITY(1,1) NOT NULL,
   [Type] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
   [Comment] [varchar](800) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
   [Title] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
   [CreatedBy] [uniqueidentifier] NULL,
   [CreateTime] [datetime] NULL,
   [ModifiedBy] [uniqueidentifier] NULL,
   [Modifiedtime] [datetime] NULL,
   [IPAddress] [varchar](18) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
   [RefComment] [int] NULL,
   [Blocked] [bit] NULL,
   [Rating] [int] NULL,
   [ImageURL] [varchar](200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_Comments_ImageURL]  DEFAULT ('a'),
CONSTRAINT [PK_Comments] PRIMARY KEY CLUSTERED
(
   [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROC [dbo].[ShowComments]
(
   @Root int
)
AS
BEGIN
   SET NOCOUNT ON
   DECLARE @ID int, @Comments varchar(2800)   
   --SET @Comments = (SELECT id+'*Comments*'+Comment+'*CreatedBy*'+CreatedBy+'*CreateTime*'+CreateTime+'*IPAddress*'+IPAddress+'*Rating*'+Rating+'*ImageURL*'+ImageURL FROM comments WHERE ID = @Root)
  
  --SET @Comments = (SELECT '<root><id>'+CAST(id as varchar(10))+'</id><comment>'+Comment+'</comment><createdby>'+CAST(CreatedBy as varchar(36))+'</createdby><createdtime>'+CAST(CreateTime as varchar(15))+'</createdtime><ip>'+CAST(IPAddress as varchar(15)) +'<ip><image>'+ ImageURL +'</image><rating>'+CAST(Rating as varchar(15))+'</rating></root>' FROM comments  WHERE ID = @Root)
SET @Comments = (SELECT '<root><id>'+CAST(id as varchar(10))+'</id><comment>'+Comment+'</comment><createdby>'+CAST(CreatedBy as varchar(36))+'</createdby><createdtime>'+Convert(nvarchar,CreateTime,103)+' '+Convert(nvarchar,CreateTime,114) +'</createdtime><ip>'+CAST(IPAddress as varchar(15)) +'<ip><image>'+ ImageURL +'</image><rating>'+CAST(Rating as varchar(15))+'</rating></root>' FROM comments  WHERE ID = @Root)
PRINT REPLICATE('-', @@NESTLEVEL * 4) + @Comments
   SET @ID = (SELECT MIN(ID) FROM comments WHERE RefComment = @Root )
   WHILE @ID IS NOT NULL
   BEGIN   
       EXEC ShowComments @ID
   SET @ID = (SELECT MIN(ID) FROM comments WHERE RefComment = @Root AND ID > @ID )
   END
END

Approach 1:

USE [StudentInfo]
GO
/****** Object:  Table [dbo].[Testcomments]    Script Date: 10/12/2009 13:51:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Testcomments](
[Id] [int] IDENTITY(1,1) NOT NULL,
[DeviceType] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ParentId] [int] NULL,
CONSTRAINT [PK_Testcomments] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

INSERT [dbo].[Testcomments] ([Id], [DeviceType], [ParentId]) VALUES (1, N'Object ', NULL)
INSERT [dbo].[Testcomments] ([Id], [DeviceType], [ParentId]) VALUES (2, N'WebServer', 4)
INSERT [dbo].[Testcomments] ([Id], [DeviceType], [ParentId]) VALUES (3, N'Database Server', 4)
INSERT [dbo].[Testcomments] ([Id], [DeviceType], [ParentId]) VALUES (4, N'Server', 1)
INSERT [dbo].[Testcomments] ([Id], [DeviceType], [ParentId]) VALUES (5, N'File Server', 4)
INSERT [dbo].[Testcomments] ([Id], [DeviceType], [ParentId]) VALUES (6, N'Mail Server ', 4)
INSERT [dbo].[Testcomments] ([Id], [DeviceType], [ParentId]) VALUES (7, N'IIS web server', 2)
INSERT [dbo].[Testcomments] ([Id], [DeviceType], [ParentId]) VALUES (8, N'Virtual servers', 4)
INSERT [dbo].[Testcomments] ([Id], [DeviceType], [ParentId]) VALUES (9, N'V S Guest OS', 8)
INSERT [dbo].[Testcomments] ([Id], [DeviceType], [ParentId]) VALUES (10, N'VS Hosts', 8)
INSERT [dbo].[Testcomments] ([Id], [DeviceType], [ParentId]) VALUES (11, N'New master', NULL)
INSERT [dbo].[Testcomments] ([Id], [DeviceType], [ParentId]) VALUES (12, N'new master child', 11)
INSERT [dbo].[Testcomments] ([Id], [DeviceType], [ParentId]) VALUES (13, N'new child1', 12)
INSERT [dbo].[Testcomments] ([Id], [DeviceType], [ParentId]) VALUES (14, N'new child2', 13)
INSERT [dbo].[Testcomments] ([Id], [DeviceType], [ParentId]) VALUES (15, N'new child4', 11)
INSERT [dbo].[Testcomments] ([Id], [DeviceType], [ParentId]) VALUES (16, N'new child5', 12)
INSERT [dbo].[Testcomments] ([Id], [DeviceType], [ParentId]) VALUES (17, N'new child6', 12)
INSERT [dbo].[Testcomments] ([Id], [DeviceType], [ParentId]) VALUES (19, N'new child7', 13)

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

-- this can be used when we know the last child node of the tree

alter PROC [dbo].[TestHierar]
AS
BEGIN
 declare @ID int
 set @ID = 19;
--SET @ID = (SELECT max(ID) FROM dbo.TestComments )
with Hierarchy(ParentID,ID,level,Devicetype,Sort) As
 (

     Select ParentId,Id,0,DeviceType,cast(DeviceType as nvarchar(1024))
     from testcomments
where  Id= @ID
     union all
    select dt.ParentId, dt.ID, level+1,dt.DeviceType , cast(Sort +'|'+dt.Devicetype as nvarchar(1024))
    from TestComments dt inner join hierarchy h on dt.Id = h.parentId
)
,HierarchyReverse(ParentID,ID, level, DeviceType, Sort) as

(
select parentId, Id, 0, DeviceType, Cast(DeviceType as nvarchar(1024))
from hierarchy
where parentId is null
union all
Select h.parentId,h.id,hr.LEVEL+1, h.deviceType, cast(hr.Sort + '|' +h.sort as nvarchar(1024))
from Hierarchy h inner join HierarchyReverse hr on h.ParentId = hr.ID
)
select Id, ParentId, DeviceType, Level,Sort
from HierarchyReverse
order by sort


END