Usefull .NET code stuff

Show only the short time in a grid
<%# String.Format("{0:g}", Eval("ShuffleTime")) %>  eg:- 22/11/2010 3:39 PM

There are so many useful formats
<%# String.Format("{0:d}", Eval("ShuffleTime")) %>  eg:- 22/11/2010

<%# String.Format("{0:G}", Eval("ShuffleTime")) %>  eg:- 22/11/2010 3:39:55 PM

<%# String.Format("{0:M}", Eval("ShuffleTime")) %>  eg:- November 22

<%# String.Format("{0:t}", Eval("ShuffleTime")) %>  eg:- 11:05 AM


Bind a DataSet/DataTable directly to a DropDown

            DataSet dsUnitTypes = ((PageBase)(this.Page)).Services.Game.GetCodeValuesForCodeHeaderName("UnitType");
            ddlGameTypes.DataSource = dsUnitTypes.Tables[0];
            ddlGameTypes.DataTextField = "Description";
            ddlGameTypes.DataValueField = "CodeID";
            ddlGameTypes.DataBind();

Stored Procedure with two EXEC commands, row filtration & order with dynamic parameter

 

 

 

ALTER
PROCEDURE [dbo].[ip_ListingSearch]

@State varchar(200),            

@SalesRegionID int    ,        

@DistrictID int        ,        

@SuburbID varchar(200),        

                            

@bedrooms varchar(20),        

@bathrooms varchar(20),        

@carspaces varchar(20),        

@EER decimal            ,        

                            

@PropertyType varchar(200)    ,

@DevelopmentName varchar(200),

@LandSize int                ,

@HouseSize int                ,

@ListingCategoryCodeID int    ,

@ListingTypeCodeID int        ,

                            

@MinPrice int                ,

@MaxPrice int                ,

@ListingStartDate datetime    ,

@ListingEndDate datetime    ,

@OrderBy varchar(50)
,

@StartRow int
,

@EndRow int
,

@AdvanceQuery varchar(max)

AS

 

SET
NOCOUNT
ON

 

DECLARE @rowTotal INT

 

--bedrooms

DECLARE @bedroomsSign varchar(1)

DECLARE @bedroomCount int

SET @bedroomsSign =
'='

SET @bedroomCount = 0

 

IF @bedrooms <>
''

BEGIN

    SET @bedroomsSign =
SUBSTRING(@bedrooms,0,2)

    SET @bedroomCount =
CAST(REPLACE(@bedrooms,'+','')
AS
INT)

END

 

--bathrooms

DECLARE @bathroomsSign varchar(1)

DECLARE @bathroomCount int

SET @bathroomsSign =
'='

SET @bathroomCount = 0

 

IF @bathrooms <>
''

BEGIN

    SET @bathroomsSign =
SUBSTRING(@bathrooms,0,2)

    SET @bathroomCount =
CAST(REPLACE(@bathrooms,'+','')
AS
INT)

END

 

--carspaces

DECLARE @carspacesSign varchar(1)

DECLARE @carspaceCount int

SET @carspacesSign =
'='

SET @carspaceCount = 0

 

IF @carspaces <>''

BEGIN

    SET @carspacesSign =
SUBSTRING(@carspaces,0,2)

    SET @carspaceCount =
CAST(REPLACE(@carspaces,'+','')
AS
INT)

END

 

DECLARE @AddressType int

SELECT @AddressType = tblCode.CodeID FROM tblCodeHeader INNER
JOIN

dbo.tblCode ON dbo.tblCode.CodeHeaderID = dbo.tblCodeHeader.CodeHeaderID

WHERE
(dbo.tblCodeHeader.CodeTable =
'AddressTypeProperty')
AND
(dbo.tblCode.CodeValue =
'Location')

 

 

DECLARE @ListingParticipantTyepe int

SELECT @ListingParticipantTyepe = dbo.tblCode.CodeID

FROM dbo.tblCodeHeader INNER
JOIN

dbo.tblCode ON dbo.tblCode.CodeHeaderID = dbo.tblCodeHeader.CodeHeaderID

WHERE
(dbo.tblCodeHeader.CodeTable =
'ListingParticipantType')
AND
(dbo.tblCode.CodeValue =
'LISTAGENT')

 

 

SELECT
DISTINCT

dbo.tblSLListing.ListingID, dbo.tblProperty.PropertyID, dbo.tblProperty.PropertyState, dbo.tblSLDistrict.SalesRegionID, dbo.tblSLDistrict.DistrictID,

dbo.tblSuburb.SuburbID, dbo.tblSuburb.PostCode, dbo.tblSLListing.Price, dbo.tblSLListing.ListingDate, dbo.tblSLListing.roomCount, dbo.tblSLListing.bathRoomCount,

dbo.tblSLListing.carSpaceCount, dbo.tblProperty.PropertyTypeCodeID AS PropertyType, dbo.tblSLListing.ObjectStateID, dbo.tblProperty.BlockNumber,

dbo.tblProperty.SectionNumber, dbo.tblProperty.UnitNumber,
ISNULL(dbo.tblAddress.StreetName,
'Address not found')
AS
Address, dbo.tblAddress.Suburb,

dbo.tblProperty.BlockSize, dbo.tblSLListing.EER, dbo.tblSLListing.Size AS HouseSize, dbo.tblSLListingLegalentity.LegalEntityID AS PrimarySalesPersonLLE,

dbo.tblPerson.PersonID AS PrimarySalesPersonID,
ISNULL(dbo.tblSLListing.EstablishedFlag, 0)
AS EstablishedFlag, tblListingCategory.CodeValue AS listingCategory,

dbo.tblSLListing.ListingCategoryCodeID,
ISNULL(dbo.tblPerson.FirstName,
'')
+
' '
+
ISNULL(dbo.tblPerson.LastName,
'')
AS ListingAgent,

COALESCE
(SettlementState.Description, SaleState.Description, dbo.tblSLObjectState.Description)
AS CurrentState, dbo.tblSLObjectState.Description AS ListingState,

dbo.tblSLObjectState.ObjectStateID AS ListingObjectStateID, SaleState.Description AS SaleState, SaleState.ObjectStateID AS SaleObjectStateID,

SettlementState.Description AS SettlementState, SettlementState.ObjectStateID AS SettlementObjectStateID, dbo.tblProperty.PropertyState AS
State,

ISNULL(dbo.tblBodyCorporate.Name, dbo.tblDevelopment.DevelopmentName)
AS DevelopmentName, dbo.tblSLListing.PriceRangeFrom,

dbo.tblSLListing.PriceRangeTo, dbo.tblSLListing.AuctionLocation, dbo.tblSLListing.AuctionTime, dbo.tblSLListing.TenderClosingDate,

dbo.tblSLListing.TenderDeliveryLocation, dbo.tblSLListing.ListingTypeCodeID, dbo.tblSLSale.SaleID

                     ,tblSLListingAttribute.AttributeID,tblPropertyLegalEntity.LegalEntityID as vendor_LEID

into #tempListing

                    

FROM dbo.tblSLListing INNER
JOIN

dbo.tblProperty ON dbo.tblProperty.PropertyID = dbo.tblSLListing.PropertyID

                     LEFT
OUTER
JOIN tblPropertyLegalEntity ON tblPropertyLegalEntity.PropertyID = tblSLListing.PropertyID

INNER
JOIN dbo.tblSLObjectState ON dbo.tblSLObjectState.ObjectStateID = dbo.tblSLListing.ObjectStateID INNER
JOIN

dbo.tblCode AS tblListingCategory ON tblListingCategory.CodeID = dbo.tblSLListing.ListingCategoryCodeID LEFT
OUTER
JOIN

dbo.tblDevelopment ON dbo.tblProperty.DevelopmentID = dbo.tblDevelopment.DevelopmentID LEFT
OUTER
JOIN

dbo.tblBodyCorporate ON dbo.tblBodyCorporate.BodyCorporateID = dbo.tblProperty.BodyCorporateID LEFT
OUTER
JOIN

dbo.tblAddress ON dbo.tblAddress.RelatedTable =
'tblProperty'
AND dbo.tblAddress.RelatedID = dbo.tblProperty.PropertyID AND

dbo.tblAddress.AddressTypeCodeID = @AddressType LEFT
OUTER
JOIN

dbo.tblSuburb ON dbo.tblSuburb.SuburbID = dbo.tblAddress.SuburbID LEFT
OUTER
JOIN

dbo.tblSLDistrict ON dbo.tblSLDistrict.DistrictID = dbo.tblSuburb.DistrictID LEFT
OUTER
JOIN

dbo.tblSLListingLegalentity ON dbo.tblSLListingLegalentity.ListingID = dbo.tblSLListing.ListingID AND

dbo.tblSLListingLegalentity.ListingParticipantTypeCodeID = @ListingParticipantTyepe

LEFT
OUTER
JOIN

dbo.tblPerson ON dbo.tblPerson.LegalEntityID = dbo.tblSLListingLegalentity.LegalEntityID LEFT
OUTER
JOIN

dbo.tblSLSale ON dbo.tblSLSale.ListingID = dbo.tblSLListing.ListingID LEFT
OUTER
JOIN

dbo.tblSLObjectState AS SaleState ON SaleState.ObjectStateID = dbo.tblSLSale.objectStateID LEFT
OUTER
JOIN

dbo.tblSLSettlement ON dbo.tblSLSettlement.SaleID = dbo.tblSLSale.SaleID LEFT
OUTER
JOIN

dbo.tblSLObjectState AS SettlementState ON SettlementState.ObjectStateID = dbo.tblSLSettlement.objectStateID

                     LEFT
OUTER
JOIN tblSLListingAttribute ON tblSLListingAttribute.RelatedObjectID = tblsllisting.ListingID

                    

 

WHERE

    tblProperty.PropertyState = @State

    AND
(@SalesRegionID = 0 or tblSLDistrict.SalesRegionID = @SalesRegionID)

    AND
(@districtID = 0 or tblSLDistrict.DistrictID = @DistrictID)

    AND
(@SuburbID =
''
or tblSuburb.SuburbID in
(select orderID from splitCommaIDs(@SuburbID)))

 

    AND
( @bedrooms =
''
OR
(@bedroomsSign ='+'
AND tblSLListing.roomCount >= @bedroomCount)
OR
(@bedroomsSign='='
AND tblSLListing.roomCount = @bedroomCount))

    AND
( @bathrooms =
''
OR
(@bathroomsSign ='+'
AND tblSLListing.bathRoomCount >= @bathroomCount)
OR
(@bathroomsSign ='='
AND tblSLListing.bathRoomCount = @bathroomCount)
)

    AND
( @carspaces =
''
OR
(@carspacesSign ='+'
AND tblSLListing.carSpaceCount >= @carspaceCount)
OR
(@carspacesSign ='='
AND tblSLListing.carSpaceCount = @carspaceCount))

    AND
( @EER = 0 or tblSLListing.EER = @EER )

    

    AND
(@PropertyType = 0 or tblProperty.PropertyTypeCodeID in
(select orderID from splitCommaIDs(@PropertyType)))

    

    AND
(@HouseSize = 0 or tblSLListing.Size >= @HouseSize)

    AND
(@LandSize = 0 or tblProperty.BlockSize >= @LandSize)

 

    AND
(@MinPrice = 0 or tblSLListing.Price >
= @MinPrice)

    AND
(@MaxPrice = 0 or tblSLListing.Price <= @MaxPrice)

 

    AND
(@ListingStartDate =
''
or tblSLListing.ListingDate >= @ListingStartDate)

    AND
(@ListingEndDate =
''
or tblSLListing.ListingDate <= @ListingEndDate)

    AND
(@ListingCategoryCodeID = 0 or tblSLListing.ListingCategoryCodeID = @ListingCategoryCodeID )

    

 

DECLARE @RowCount as
int

select @RowCount =
count(ListingID)
from #tempListing

 

DECLARE @sQuery as
varchar(1000)

DECLARE @RowBeforeFilter as
int

 

 

--hack to find the row count------------------------------------

SET @sQuery =
'select listingid from #tempListing '
+ @AdvanceQuery

Create
table #test

(

listingid int

)

insert #test

exec(@sQuery)

 

select @rowTotal=count(*)
from #test

--------------------------------------------------------

 

 

SET @sQuery =
'select *,Row_Number() over (order by '
+@orderby +') as rowNumber from #tempListing '
+ @AdvanceQuery

 

declare @pagingQuery as
varchar(2000);

set @pagingQuery =
'with tblResult as ('
+ @sQuery +
') select tblResult.*, '+
convert(varchar(10),@rowTotal)+' as TotalRows from tblResult where tblResult.rowNumber > '
+
convert(varchar(10), @StartRow)
+
' and tblResult.rowNumber <= '
+
convert(varchar(10), @EndRow)
+
';'

exec(@pagingQuery)

 

drop
table #tempListing

drop
table #test

 

SQL Update query with inner join, Script to add table columns

update tbladdress

set tbladdress.state=p.propertystate

from tbladdress as a

inner join tblproperty as p on a.relatedid=p.propertyid

where (a.state is null or a.state='')

      and a.relatedtable='tblProperty'

      and (p.propertystate is not null and p.propertystate <> '')

================================================================

ALTER TABLE SPORT
ADD CreatedDate     datetime,
CreateBy        int,
ModifiedDate     datetime,
Modifiedby      int 

================================================================

Sharepoint 2010 New Features / Enhancements

With Sharepoint 2010 we are getting few new features as well as many enhancements.
(1) Development & deployement enhancements
          Creation of Webparts using VS 2010
          Easy deployment of features
    
(2) Administration interface & user experience
          User friendly Central Admin Interface
          Ajax enabled pages
          Modal Popup & less  postbacks
          Ribbon interface 

(3) Performance management
          More templates
          Easy Search
          Easy SSP (Shared Services Providers)
          Database backup & restore
          Performance analyzing

Also we are getting new features in differen layers

Data Modules                 --  Data fetch from External Lists
Server API's                     --  LING
Connected Client API's   --  REST
Pages & UI              --  Silverlight

SQL Server Reporting Services Expressions

Table Row Visibility by value
=iif(Fields!PctQuota.Value>.9, False, True)


Field Visibility when data is null
=iif(Fields!vendorSolicitor.Value Is Nothing, True, False)

Set Data Field Values
=iif(Fields!ExchangeDate.Value Is Nothing,"",FormatDateTime(Fields!ExchangeDate.Value ,2))

SWITCH
=Switch
(
    Fields!PropertyCategory.Value="N" and sum(Fields!newHouseCount.Value) > 0, sum(Fields!newHouseCount.Value),
    Fields!PropertyCategory.Value="E" and sum(Fields!estHouseCount.Value) > 0, sum(Fields!estHouseCount.Value)
)

Format date
=iif(Fields!ExchangeDate.Value Is Nothing,"",FormatDateTime(Fields!ExchangeDate.Value ,2))

Format Decimal values(Drop decimal places than rounding)
=FormatCurrency(Floor(Sum(Fields!ExpectedAgencyCommission.Value)),0)

Site Navigation using Tree View

Drop Tree View Control Form Navigation Tab

Bind Data Source Type to Tree View (Add "Site Map" source)

Add New Item --> Add "SiteMap" from VS templates (extension will be test.sitemap)

Add pages and title of pages to show on Tree View in "test.sitemap"

Sharepoint 2010 Web Analytics

Site settings--> Site Actions--> Site Web Analytics reports

   
Top bar --> Change Settings

    
 
Analyze --> Preceding 7 /30 days

Left panel --> Top Pages

  

Left panel --> Top visitors

  
 
Left panel --> Top Browsers

 

TFS Burndown report does not show data - Solution

Burndown report show empty data

 

Open Report in Excel in Edit Mode, Allow Data populate set as "trust data connection" in excel

Click on the pivot table data area, then see right side column, see that "Row Labels" box is empty

Drag & drop "Last 4 weeks" from Date--> Sets --> "Last 4 weeks" 
Row Labels (This box use to show horizontal bar information in your graph)

 

Then Save the excel sheet. You will get data in your Burndown report when you refresh your page. J

    

Sharepoint backup script

(1) Open notepad

(2) type following command

@echo

echo Back up the farm to "Optimus\Share"

echo ===============================================================

For /F "tokens=1,2,3 delims=/ " %%A in ('Date /t') do (

Set Day=%%A

Set Month=%%B

Set Year=%%C

Set todayDate=%%C%%B%%A

)
set WORKINGFOLDER="C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\BIN"
pushd "%WORKINGFOLDER:"=%"

cd C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\BIN

echo Starting Backup ========================

stsadm -o backup -url http://optimus:3333 -filename \\Optimus\Share\SharepointPortalBackup_%todayDate%.bak -overwrite


pause


(3) Save the file as .bat or .cmd
(4) Run & test (This will create a backup with the date (SharepointPortalBackup_1406Mon.bak) )
(5) Add a "Task Scheduler" in server

:) :) :) Simple

Sharepoint Central Administration WebSite Not Working - Solution

It was a nightmare when we lost central administration website.

All other websites worked fine.
When we click "Central Administration 2010" we got only,


We had to use command to get this up & running
(1) Run Command prompt
(2) cd C:\Program Files\Common Files\Microsoft Shared\W
eb Server Extensions\14\BIN
(3) C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\BIN>
Psconfig -cmd adminvs -provision -port 33633 -windowsauthprovider onlyusentlm
Here 33633 = Port number of central administration website



It takes little time & finally you will get your your central admin site :)



Easy Work now :) :) :)