Use Variable with Top in Select In Sql

 Use Variable with Top in Select In Sql:

This is something i knew but forget to logged in my blog. But when my some one asked me for same I googled and found answer this time i am going to log it for myself and who ever comes to check my blog.

declare @i int =10

select  top (@i) * from dbo.tblviewsource


It will return top 10 records from table dbo.tblviewsource.

Happy Living....
Happy Concepts....
Happy Programming....

Speed Up Sql Query

Speed Up Sql Query

1. If there is a large Table going to participate in Query, it is better to filter out records based on some filters and reduce the no of records going to participate in Query.

2. If in a query ,joins include few inner joins and left joins than it is better approach to first apply all inner joins and then on the result of inner join performed query, apply left join.

3. Temp table if something that is very large query  and we want to take some portion of query out side and wants to use table variable or temp table then tere are 2 cases:

   for small data set:
                            if the result of queries are smaller than we should use table variable it doesn't has overhead of dropping it in the end of query.

   for large data-set:
                            In the case of large result set it is better to use temp table because there isn't over head of writing it to table(by default in the case of temp table simply result sets are referenced by some pointer all temp table). One more advantage we have here is of Indexes we can apply indexes on temp table and it get dropped when this temp table dropped out.

4. Fields of table should not be used in function, in where clause or select clause If we do this it will be slower, so good will be not to use them in function. We can use constant variable or parameter in function it wont affect query very much.

5. Make sure we have proper indexes on table going to participate specially on large table.

6. Drop temp tables just after the if not usable any longer. In the case of single time use i prefer to use CTE tables.


Happy Living....
Happy Concepts....
Happy Programming....

How to remove Special Character in database output

A few months back i was caught in situation where we had special characters in data base like shift+enter, tab , space , enter etc.

That time we spend 3 hours to detect issue and next 3 hours to resolve it. But a few days back one of my colleague get caught in same situation we helped her to detected the issue and suggested the same solution we put at that time, but somehow she managed to get more easy approach to resolve the issue.

so if a string has special characters in it, then it is better approach to change value to string and than perform a trim on the charcter we want to remove.

for eg:

string s= dr.GetString("column1");

assuming s contains a tab and enter character in it.

s=s.ToString().Trim("\t"); // it will remove tab character form string
s=s.ToString().Trim("\n"); // it will remove enter character from string

so it is better to use Trim instead of performing looping on character and using ASCII to remove character.

Note: But remember, you need to use ToString method before using Trim method. If we wont do that there wont be any affect on string.

Happy Living....
Happy Concepts....
Happy Programming....

Allow Null values In Inner join

Recently i got stuck in a scenario where i had to allow nulls in inner join and also sequece needed to be the same as inner join.

For eg:
We have a table with following schema

PersonDetails:
       -----------------------------------------------------
       Id         Name            ProjectId           TechGroupId
       ------------------------------------------------------
       1           m1                  null                   30
       2            m2                  p1                     null 


Projects :
      -------------------------------------------------------
       Id                            Name
      -------------------------------------------------------
       p1                           Project1
       p2                           Project2   

TechGroups :
      -------------------------------------------------------
       Id                            Name
      -------------------------------------------------------
       1                           dotnet
       2                           java   
       30                         Salesforce 

And user expecting following result:

     Id         Name            Project                  TechGroup
       --------------------------------------------------------------
       1           m1                  null                    SalesForce
       2            m2                  project1                     null 

Solution:

we can create to temptables as following
Select * into #Projects from Projects
select * into #TechGroups from TechGroups

Now add nulls in temp tables.

Insert #Projects
values (null , null)

Insert #TechGroups
Values(null,null)


and To get expected result query should be:

Select * from PersonalDetails pd
inner join #Projects p on  (pd.Projectid is null and p.Id is null ) or pd.Projectid=p.Id
inner join #TechGroups tg on  (pd.TechGroupid is null and tg.Id is null ) or pd.TechGroupid=tg.Id

Reset Identity column to new id

Problem:

I have an enum database table with name DataTypes

Id             Name

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

1              short

2               Int

3               long

and in c-sharp Classes i have 

enum string DataTypes{short=1,Int=2,long=3}

 and we used these references in other tables and classes.

Now we got a scenario where we have to add more datatypes

Id               Name

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

1                short

2                Int

3                long

4                Chara

5                string

6                float

If we delete chara from table and try to add again chara on 4 it is not possible we will 

get 7 Id for new chara element.

But we have kind of scenario where we want it to add with 4 Id value.

Solution:

Here we have three approaches we can use whatever is suites best to us:

1. Use Truncate:

 First what we can do if we are allowed to Truncate tables than Truncate it.

        Truncate table tablename

 After truncating table id will get reset to 0 but it will work if we dont have child table.

 If we have child parent relationship then first we need to delete referenced record from 

 child table and then only we can truncate table.

2. Reset Identity Value:

 Second way is to reset it directly using following command: 

   DBCC CHECKIDENT('TableName', RESEED, [NewValue])
 

 If for new insert value we want Id [new_Id] then :

 [NewValue]=[new_Id]-1 

 for eg.:

   In above case we want new value to 4 then reset command will be: 

   -- for example
   DBCC CHECKIDENT('DataTypes', RESEED, 3)

3. Set Identity OFF(before Insert Statement) and ON (after Insert Statement):

  In this solution we do it in three phase 

• Set Identity OFF for Table
• Run Insert data script but we need to specify Value of Id field explicitily
• Set Identity ON for Table

 For e.g:  

             Set identity Off

              Insert into DataTypes (Id, Name)

                               values (4, 'Char')

              Insert into DataTypes (Id, Name)

                               values (5, 'String')

              Set Identity ON

This is my findings through R&D on Identity Column.

Happy Living...

Happy Cocepts...

Happy Programming..

Factory Pattern

Factory Pattern



Visualization of Factory Pattern:

   

Happy Living...
Happy Concepts....
Happy Programming..

Set Compatability mode in Sql server

Set compatibility Mode in different versions of Sql Server

values use to identify these different versions are:
60 = SQL Server 6.0
65 = SQL Server 6.5
70 = SQL Server 7.0
80 = SQL Server 2000
90 = SQL Server 2005
100 = SQL Server 2008

 Use Following Statement to set Compatibility mode in sql Server:


ALTER DATABASE database_name SET COMPATIBILITY_LEVEL = { 80 | 90 | 100 }


Happy Living.... 
Happy Concepts...
Happy Programming...

Summary Row in Sql Result set

Summary Row in Sql Result set

If are performing some kind of aggregation function on a set of fields in sql query and we also want an additional record for summary for eg. Grand Total, Total Avg etc.

In this case we have an extension available for Group by Clause in sql server

                                       "With Rollup"

so if we are calculating avg of order per month than we can also be able to get Total avg.

Select datename(month,orderdate) as monthOfOrder

, avg(price) as avgPriceOfMonth

from orderPrice

group by datename(month,orderdate)

order by datename(month,orderdate)

it will produce simple result avg of each month.

Select case when (Grouping( datename(month,orderdate))=1) Then 'Total Avg'

Else  datename(month,orderdate) 

End as monthOfOrder

, avg(price) as avgPriceOfMonth

from orderPrice

group by datename(month,orderdate) with Rollup

order by datename(month,orderdate)

In this case we will get one additional Row with summary of all Months.


Happy Living.....
Happy Concepts.....
Happy Programming....

Playing With Dates in Sql Server

1. Convert a number into Month Name:
     Declare  @monthInt int
     set @monthInt =1
     datename(month,dateadd(month, @monthInt - 1, 0)) as MonthName


Happy Living.........
Happy Concepts..........
Happy Programming...........

Save ViewTemplate In Database

Purpose :

In this article we will identify what is the best way to save different kind of views in database without making any changes in database and base classes of view.This article includes 2 parts , 

In First part we will identify the criteria and will design Tables for it. Part1

Second part contains the C# code for classes required to implement in a generic way.

Recap what we did in Part1

In part1 we had designed database tables to store data into tables.

 

Part2 

In this article, we have to create classes to save and fetch views in/from database. 

as we know we are going to save data in XML form so there will be some utility(Serialization) that creates XML for us.

Serialization: It is process to convert an Object into XML/Binary.

Deserialization: It is a way convert XML data into Object.

Assuming we have to save different information for e.g.  Grouping(includes to save information of column, and order), Filtering (includes columnName, Filterexpression),

Sorting(ColumnName,OrderType:Ascending/descending). And this information needed to be save as xml. So we have to create somthing like below as XML.

<XMLViewSettings xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                 xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <lstSorting>
    <Sorting>
      <ColumnName>STN</ColumnName>
      <SortingOrder>Ascending</SortingOrder>
      <OrderNo>0</OrderNo>
    </Sorting>
  </lstSorting>
  <lstFiltering>
    <Filtering>
      <ColumnName />
      <FilterExpression />
      <OrderNo>0</OrderNo>
    </Filtering>
  </lstFiltering>
  <lstGrouping />
  <lstPaging />
  <lstFieldChooser />
</XMLViewSettings>

   Sorting,Paging,FieldChooser,Filtering,Grouping are the classes required to serialize. So set Classes as serilize and properties as XMLElement.

Fig 1

 

 

 

Now we can see in fig 1 there is a class diagram available for the classes required to store data and convert it to XML .

SerializationUtil<T>: Is a generic classes, if we want to create a classs with serialization support we can extend that class with SerializationUtil<T> and T is the type of Class needed to support.

Saving of different kind of UI views in database

In this article we will identify what is the best way to save differnt kind of views in database without making any changes in database and base classes of view.This article includes 2 parts ,
In First part we will identify the criteria and will design Tables for it.
Second part contains the C# code for classes required to implement in a generic way.

Part 1

Assuming we are working on a application where we need to save different views for eg there are 3 scenario explained below.

1. We have a grid on page where we have some data to display on it. The functianlity of grid allowing user to grouping . filtering ,hiding columns ,sorting and ordering, etc.

Requirement : To allow user to make some change in grid on grouping,filtering, etc. and save it as view and this way user will be able to save different views in database. User allowed to make one of them as default. later on when user come again on this page she will be able to view grid based on the default view selected by her.

2. In this scenario, user have a checklist and chart on page. User can see different kind of charts using drop-down list of chart type, and also able to check the fields want to draw on chart.

Requirement : To allow user to make some change in chart type, checkbox list and save it as view and this way user will be able to save different views in database. User allowed to make one of them as default. later on when user come again on this page she will be able to view charttype and checkbox checked based on the default view selected by her.

3. If we have a Search page and we have several textboxes, dropdown and checkbox etc. Here we can set different search criteria and search on bases of these search criteria.

Requirement : To allow user to make some change in criteria by change textbox text, checkbox, dropdowns  and save it as view and this way user will be able to save different views in database. User allowed to make one of them as default. later on when user come again on this page she will be able to fill criteria based on the default view selected by her and later on she will be able to select different criteria by selecting a different view.

I am thinking now the basic requirement is clear to us that we have to do to save different kind of views in db using same tables and if it possible then write same reusable code.

lets take case One by one and design a database that can help us to identify database tables.

So first we have to save different kind of views for different forms, so we need a table that can identify views

ViewTypes
--------------------------------------
Id                   uniqueidentifier newid()
Code              varchar(100)
Description    varchar(100)

for e.g:
Id                                        Code                                      Description
---------------------------------------------------------------------------------------
asd-bd-dda-d123-fsdsd       PageName_GridView            Gird view for PageName1 Page  
123-bd-dda-d123-fsdsd       PageName_ChartView          Chart view for PageName3 Page
a56-bd-dda-d123-fsdsd       PageName_SearchView        Search view for PageName2 Page


In this way we can save a overall view for page or multiple kind of views for different section of same page.
If we have a scenario where our domain contains different application for different purpose for e.g Charting is done on a different project and Grid on different and might be mixture of both kind of thing if this is the case. We need a different table to store type of Application.

AppTypes
--------------------------------------

Id                   uniqueidentifier newid()
Code              varchar(100)
Description    varchar(100)


for e.g:
Id                                        Code                                      Description
---------------------------------------------------------------------------------------
678-bd-dda-d123-fsdsd       AppType1                 Application to show  Gird Views 
qwe-bd-dda-d123-fsdsd      AppType2                 Application to show  Chart Views
ui2-bd-dda-d123-fsdsd      AppType3                 Application to show  Search Views


      Now we have ViewType, AppType and we have to save data of view, Because we don't know what kind of data, we need to save in future so it is a better approach not to save different parameters in different columns instead of that we should save it as XML data filled.
        So idea is that we create  XML data from the data need to save as view and save it as data in table. My proposed solution is to create a table :


ViewData
-------------------------------------------------------------------
Id                              uniqueidentifier newid()
AppTypeId               uniqueidentifier newid()
ViewTypeId             uniqueidentifier newid()
Data                         XMLdata
Isdefault                   bit
IsActive                    bit



Now we have all 3 tables to save data into database and  identify to which view and application it is associated, Whether it is default/Active or not.


To Complete this article also read Part 2.




Happy Living..........
Happy Concepts............
Happy Programming..........

Search DBO object's(table,Sp,function,etc.) dependencies in Other Databases

We can use 3 different approaches to find dependencies of  a data base object (e.g table,Sp,function , etc.).

 1. In this way , we use GUI to find an object in same database and this the limitation of this approach
that we are only find the dependencies in same database.
One of the most basic way to identify if a particular data base object has dependencies on other objects in same database.

2. If we arent able to find dependencies in same database we might have a scenario where need to check other database of same application. So if this is the case, we can use following script to find it in other db:

SELECT DISTINCT so.name, so.xtype
FROM syscomments sc
INNER JOIN sysobjects so ON sc.id=so.id
WHERE sc.TEXT LIKE '%Sp_NeedToSearch%'

3. One the other way is to use Sp_depends sp to identify dependencies of a database object into Database.

exec sp_depends 'Sp_NeedToSearch'

name                                         type             
-------------------------------------------- ---------------- 
dbo.sp_depn1                                stored procedure
dbo.sp_depn2                                stored procedure
dbo.viewOfTb                                view

Source of Information :
 Finding-Database-Object-Dependencies
 msdn help for finding dependencies

Above links and article helps you to find dependencies in same data base if our object is to look across database or across server we have another good article on msdn site on link Check Dependencies across database or across server . I am hoping this will help you on finding solution for your specific problem.


Happy Living……..
Happy Coding……….
Happy Concepts……….

Regions in Javascript files and other addons for VS2010

Dear Readers,

I was looking for a free tool to create regions in Javascript files so that we could be able to make it more structured and readable. I have finding that can help us.

1. JavaScript Editor
2. Code Maid (Great Tool to help Devlopers)

 JavaScript Editor:
 JavaScript Editor by default adds regions to method

but if we have aim to

You can see the JavaScript Editor's auto implementation adds regions with every block based on

braces used to open and close a block of code.

Now if we have to add region based on set of method use following way:

 // abc-xyzblock

{

function abc(){

// to do code

}

function abc2(){

// to do code

} 

function xyz(){

// to do code

}

}

 

 

 

 

 

 

after collapsing block 

 

 

 

Now as we can see it very easy to identify our javascript code in blocks

 

CodeMaid:

CodeMaid is an open source Visual Studio extension to cleanup, dig through and simplify our C#, C++, XAML, XML, ASP, HTML, CSS and JavaScript coding.

For this add-on everything is available on above provided link so i am explaining it here. Please use link to get detailed information about this add-on I am truly say you are going to love it.

Happy Living....

Happy Concepts....

Happy Programming....

Thanks 

Yashpal Sharma

 

Intellisense in Visual Studio 2010

This is very simple to get jQuery Intellisense in visual studio JS file. Steps include very easy instruction.
1. Drag jQuery-1.4.1.js from SolutionExplorer of project to top of the JS file.

When you will start using $(document).ready( ) you will be able to get function parameter and other things.



Happy Living……..
Happy Coding……….
Happy Concepts……….

Access denied by caller on call of cally

Access denied by caller on call of Cally

I got this issue while i was trying to access parent pages tags into child.

scenario : Both pages exist in different folder. Folder may be readonly.

so if we will try to get properties of parent page that exists in differnt folder then we might need to check readonly access of file , some its on page level or folder level.
JUST by removing the readonly access of file/folder we will be able to remove the error.

Happy Coding...
Happy Concepts...
Happy Programming ...

Working with HTML Tags

Textbox:

To set width use , Style="width:100px;"

submit large amount of data with jquery.post() or $.post()

There is  a limit assoicated with form key collection, and we can increase this limit by setting configuration values in web.config in our application

<appSettings>
        <add key="aspnet:MaxHttpCollectionKeys" value="50000"/>
</appSettings>

We can use this settings to reset data upload restriction implemented by IIS.
This is one of reason when we fail to submit data to server if there is large amount of data is associated with our Page.
 
 

Happy Living...
Happy Coding....
Happy Concepts....

Export HTML to Image or PDF

Export HTML to Image or PDF

A few days back one of my friend asked to me How can we export HTML page into Pdf.
We searched a lot there are a large number of thired party tools are available to do this but these
all tools are paid. So we cant buy a tool and because it also include Html reandring engine code so its
not easy to create a tool for project its time consuming task.

As i told you above we are not able to export HTML to Pdf we used a differnt approach to resolve it.
We planned to convert HTML to Image and then write down this image into Pdf and it working for us.

Now i am going to explain it Step by Step:

1. Get reference of System.UI.Form , System.Drawing
2. Create object of WebBrowser
3. On Completed event

    a) Create Bitmap object and specify size of Bitmap
    b) Convert WebBrowser to Control and then use DrawToImage()
    c) Save Bitmap to jpg file.
   
4. Add Reference of iTextSharp
5. Create a Object of Document
6. Create a Object of PdfWriter
7. Add Image to PdfWriter using PdfWriter.Add();
8. Close PdfWriter and Document Object.

Happy Living...
Happy Coding...
Happy Concepts...

Merge to Update / Insert using a single statement

My best guess is that you can use Merge to Update / Insert using a single statement

-- Merge Statement
MERGE ProjectDetailsTransit  AS spdt1
    USING (select d.ProjectDetailsTransitID,d.Startdate ,d.enddate from DeletedDayInclude d) AS ddi
    ON spdt1.ProjectDetailsTransitID = ddi.ProjectDetailsTransitID
    WHEN MATCHED THEN UPDATE SET spdt1.startDatetime =ddi.startDate, spdt1.FinishDateTime = ddi.enddate
    WHEN NOT MATCHED THEN INSERT(startDatetime ,FinishDateTime )    VALUES(ddi.startDate,ddi.enddate );
GO

Note : In insert statement of  Merge we can not use alias name with fields for ex:
INSERT(stm.User_Id ,stm.Favorite_question )
VALUES(sd.User_Id ,(cast(@questionid as varchar(50)))

if we will write it in the way it defined into blue marked code it will give us following error :
Error 1:  The insert column list used in the MERGE statement cannot contain multi-part identifiers. Use single part identifiers instead.

Because Query parse looks for column name in target table and it densest expect any other table name. So if we will write the alias table name with column name then it will try to find "stm.User_Id" name in columns of target table,but it wont find it there we will get above error message.

 Error 2:   Sometime we get following error if we try to execute newly build sp in which we have used Merge statement:
Msg 325, Level 15, State 1, Line 7
Incorrect syntax near . You may need to set the compatibility level of the current database to a higher value to enable this feature. See help for the stored procedure sp_dbcmptlevel.

 Reason :  

Mostly with each new version of sql server we upgrade our data base and this is very true for application build several year back. In this case, the new functionality get issues to execute because still we are running database on compatibility mode of old version and it is not Compatible with new version of sqlserver for eg.
Compatibility mode is 90 for sqlserver 2005 
and Compatibility mode is 100 for sqlserver 2008

Change the database compatibility level using following command.

For SQL Server 2005:
EXEC sp_dbcmptlevel 'DatabaseName', 90

For SQL Server 2008:
EXEC sp_dbcmptlevel 'DatabaseName', 100

Happy Living...
Happy Coding...
Happy Concepts...

get Comma separated string for Column in Sql

Assuming i have table

Create Table shifts
(
RequestNo int, ShiftId int
) 

and for each request number we want to get a comma separated string of shift ids associated with a particulare request



select SubString( (SELECT ', ' + convert(varchar(1),p2.ShiftID )FROM shifts p2
FOR XML PATH('')),2,200000)


Happy Living ...
Happy Coding...
Happy Concepts