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...........