Search records in database using dynamic number of parameters

Search records in database using dynamic number of parameters

Scenario :
In a search form if there are multiple fields available as search criteria and user can search on the basics of a single field or without fields or a set of criteria fields.And we are calling same SP by sending entire criteria without taking consideration what criteria user has selected. In this case SP is responsible to use optimize technique to provide result dataset. It becomes extremely important if searching is done on set of tables or large set of data.

Approaches:
So to resolve above issue we have 2 reproaches described below:
1. Create dynamic query based on criteria sent by user
2. Create SP to smart enough to handle Null or empty fields.

// under construction


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

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