Monday, July 22, 2013

Is Reflection breaks Encapsulation

"Today, I learn a big lesion  that what ever we know isnt surely only truth. We may know what is true but there may be other true that are different from what we know. And sometime no one tells us that what is truth either because they dont know them selves or we might be not a level to understand that."

Whatever i write down above is just what i felt about knowing that we can call or access private members of class regardless of that can break the concept of encapsulation.

So as i told you reflection can used to access private members.Let assume we have a class Cat.
For eg.

 public class Cat
    {

        private int NoOfLegs = 4;
    }

now we will use to reset the value of NoOfLegs using reflection.

   Cat c = new Cat();
            Type  t= typeof(Cat);

            t.GetField("NoOfLegs", BindingFlags.Instance | BindingFlags.NonPublic)
         .SetValue(c,567);
            Console.Write(t.GetField("NoOfLegs", BindingFlags.Instance | BindingFlags.NonPublic).GetValue(c));
            Console.ReadLine();

So myth creaked  that reflection cant access private members.

"So If some is saying that private members aren't accessible by outside you can prove him/her wrong".

As explained above encapsulation is a design technique to make design clean and structured. If some wants to take full responsibility by changing private members he is on risk of getting exception.
For eg someone can set value to null using reflection so our job as developer is to make sure we are handling null and throwing exception. As we cannt trust on other developers or user of providing expected inputs.
This solution is provided because we may need to test private member of our class.

Dynamic:

Somehow Dynamic datatype is popular as alternative of reflection but it isnt. Dynamic datatype allows as to check property and fields are runtime. Some rules remain unbreakable like object of a class can access only public members of class.
So if someone is thinking to do something similar we did with reflection then he must be ready to get unhandled exception like RuntimeBinderException With msg"'RefdynEncApp.Cat.NoOfLegs' is inaccessible due to its protection level ".


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





Friday, July 19, 2013

Create date using two dates

Here is the logic to create a date using two dates:

For eg:

We have 2 dates Tdate1 and Tdate2. And we want to create a new datetime using these 2 dates, in which we will take date part from Tdate1 and time part from Tdate2. So we can do this using  below query:

 Convert(datetime, CONVERT(VARCHAR(500), Tdate1, 110)
                                + ' '
                                + CONVERT(VARCHAR(50), Tdate2, 108) )

Compare dates :
if we have to check interval(day,month,year,min,hour) between 2 dates than we can use logic defined below:

MONTH:

Tdate1: 7 June 2013 00:00:00
Tdate2: 5 July 2013 00:00:00

select 'Difference',DATEDIFF(month, convert(datetime, Tdate1,Tdate2)
Result:  Difference,1

 MONTH:(-ve)
Tdate1: 7 June 2013 00:00:00
Tdate2: 5 May 2013 00:00:00

select 'Difference',DATEDIFF(month, convert(datetime, Tdate1,Tdate2)
Result:  Difference,-1

 YEAR:

Tdate1: 7 June 2013 00:00:00
Tdate2: 5 July 2013 00:00:00

select 'Difference',DATEDIFF(Year, convert(datetime, Tdate1,Tdate2)
Result:  Difference,0

 HOUR:
Tdate1: 7 June 2013 00:00:00
Tdate2: 7 June 2013 05:06:07

select 'Difference',DATEDIFF(Hour, convert(datetime, Tdate1,Tdate2)
Result:  Difference,5

 MINUTE:
Tdate1: 7 June 2013 00:00:00
Tdate2: 7 June 2013 05:06:07

select 'Difference',DATEDIFF(Minute, convert(datetime, Tdate1,Tdate2)
Result:  Difference,6

 SECOND:
Tdate1: 7 June 2013 00:00:00
Tdate2: 7 June 2013 05:06:07

select 'Difference',DATEDIFF(Second, convert(datetime, Tdate1,Tdate2)
Result:  Difference,7

Let me know if i am missing anything or we have to make any change in this Post.

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


Tuesday, July 16, 2013

Scripts for Self updated Table using multiple Join

Here we have described how to update a table using multiple joins on table

1. Create a Table Variable:

Declare @tblR as Table
(
    RID int,
    SID uniqueidentifier,
    BID uniqueidentifier
)

2. Insert demo data into New Created Table Variable





insert into @tblR values (1, null, null)
insert into @tblR values (2, null, null)
insert into @tblR values (3, null, null)
insert into @tblR values (4, null, null)
insert into @tblR values (5, null, null)
insert into @tblR values (1, null, null)
insert into @tblR values (2, null, null)
insert into @tblR values (6, null, null)
insert into @tblR values (7, null, null)
insert into @tblR values (8, null, null)

3.  Update Table using alias name and Using multiple INNER JOIN





update t set t.BID = b.BID, t.ID = s.ID
from @tblR t
inner join R r on r.ID = t.RID
inner join B b on b.BID = r.BID
inner join S s on s.SID = b.SID

4. Select data from table Variable to check updated data






select * from @tblR

This is just an example for a simple update using inner join.

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




Wednesday, July 3, 2013

Get Id of latest inserted Record in Table

Dear Reader,

One of issue we always worried about was how to get id of latest inserted record.
1. One option was to perform select on that table after inserting latest record.
2. Use or ScopeIdentity
3. Get Inserted id using OUTPUT INSERTED

assuming we have a table tblDemoTable (IDField, Field1,Field2,Field3)
Details on different ways to get ids

1.
 After insert new record we can get if it integer identifier by fetching maximum id values

Declare  @InsertedId int
select top 1 @InsertedId  =Max(IDField)
from tblDemoTable

Note: It doesnt work for Uniqueidentifier column.

And we can also select values based on any date sorting or something if we have any identifier to sort it and take latest record.

2. SCOPE_IDENTITY(),@@IDENTITY :

 SCOPE_IDENTITY() will provide us the last identity value created in a particular session, but it will also limit it to your current scope as well.
 Link for better understanding-1


3. OUTPUT INSERTED :

This is very useful to get list of Id's inserted in current session inside a loop or in batch.
Limitation: It will work in current session only.

Declare @InsertedID as table
                            (
                                Id int
                            )
                            Declare @ID int
                            Insert into tblDemoTable( Field1,Field2,Field3)
                                                        OUTPUT INSERTED.[IDField] INTO @InsertedID
                                                        Values
                                                        ( @Field1,@Field2,@Field3)

Thank you Rohit Rao for providing solution to me.


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

Monday, May 20, 2013

Way to search string in all tables in database

 

Some time we get requirement to find a particulare sting across tables in a data base.
One of my friend was trapped in same situation this the first solution we found on net


Search string in all tables-Ist Solution

but not tried yet we'll continue work on this and let you know once we get something new.
Here is a way to search string in all tables in database this is one of the solution tried by my friend Rohit Rao and it is working solution all credit goes to him.


DECLARE @SearchStr NVARCHAR(100) 

SET @SearchStr = 'F500P' 

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', 
                                                         'int', 'decimal' ) 
                                      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 


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

 

Tuesday, April 16, 2013

Refresh or Reload showModalDialog

The process of Refreshing or Reloading showModalDialog is simple if know how to do that. But if you are looking for a solution it make be pain full task. so this blog is to help you how can you refresh or reload popup window with minimal code.

First of all showModalDialog doesn't feel that way.
steps:

1. Add
<html>
<head>
< base target="_self" />
.....
.....
</head>
By default target is set "_bank".
2.
add link to page as hidden
<a href=”" id=”PageLink” style=”display:none;”>
3.
add url to this page
document.getElementById('PageLink').href = 'test.html';
document.getElementById('PageLink').click();
 
 
Happy Living....
Happy Concepts....
Happy Programming ..... 

Friday, January 25, 2013

Align Sql script in Any Database

Scenario:
Today , while i was working with a large Stored Procedure i felt need of a way to auto align in sql server.

Approach:
 I searched for same on internet and got to know there isn't any short-key provided by Microsoft Sql server. While searching for an add-in there wasnt anything suggested by microsoft site of community provided. At the and i decided to use one of online formater and i got to know http://www.dpriver.com/pp/sqlformat.htm . It is good enough to handle most of the things.

Steps to Use:
1. Visit Link http://www.dpriver.com/pp/sqlformat.htm
2. Select Database from dropdown MSSQL
3. Copy text of a valid T-SQL script (SP or Table) needed to be formated and paste on First Text Area.
4. Check default settings at right side of page (you can leave everything as it is ) . There is one thing defiantly required to change is value in last Textbox (80 to 500 in my case). otherwise it will break one statement into multiple lines.
5. Click on Format button (First button below Text Area).
6. There is output will be displayed on second (destination) TextArea.
7. Now you can Copy Text of this TextArea and paste on you SSMS querywindow and run.

 Thanks dpriver.com for providing us this tool.

Updated: 25/10/2013
http://poorsql.com/ is also a good formater of sql script and we can use it in both ways either online or through adding Add-in to Sql server (SSMS ) and it works with only Ctrl+K,Ctrl+F. we found that is a fast way to format sql scripts.

www.ssmstoolspack.com/‎‎  , SSMS tools pack provide us a large number of functionality including formatting, running
script on different database at once, to store script change history so we can retrieve previous version of our scripts. It also include Insert script generator tool,find data in database/Table/Views and so on.
But after doing all that it makes us to compromise with speed of SSMS while i was trying it my sqlserver hanged 3 times so preferred to go with "Poorsql" instead of "SSMS tool pack".



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


Sunday, January 20, 2013

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