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