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