Get All Days/Months/Years/Quarters with in Two dates.

Hi All,

Today i am feeling soo great as i finally got what i needed the function that i am going to show now will return all the Dates with in two dates but wait its not this simple, i can have periods defined like difference would be in weeks / Months / Years / Quarters and so on....

What it will return in end is Minimum Date of reporting period Maximum date of reporting period and name of that Period.

CREATE FUNCTION [dbo].[GetReportingPeriods] (
  @StartDate DATETIME,
  @EndDate   DATETIME,
  @type varchar(20)
)
RETURNS @A TABLE (
  stdate datetime,eddate datetime, PartName varchar(30)
)
AS
BEGIN
/******************************************************************************
 * Author:      USMAN SHABBIR 
 * Create Date: 2010-11-20
 * Description: Create a table of dates between @StartDate and @EndDate
 *****************************************************************************/
  if @type = 'daily'
 begin 
  ;with cte as 
  ( select CONVERT(DATETIME,@startDate) AS DATE 
  UNION ALL 
  select Dateadd(dd,1,DATE) as date 
  from cte   
  where date <= @endDate - 1 
  )  
 
  insert into @a
  SELECT DATE, DATE + 1 - 1, 'Day ' + CAST(DatePart(dd, DATE) as varchar(10))  as Week_NO_Of_Year
  FROM CTE
  --Group by 'Day ' + CAST(DatePart(dd, DATE) as varchar(10))
  order by (DATE) ASC
  
  OPTION (MAXRECURSION 0)
 end

 else if @type = 'Weekly'
 begin 
  ;with cte as 
 ( select CONVERT(DATETIME,@startDate) AS DATE 
 UNION ALL 
 select Dateadd(dd,1,DATE) as date 
 from cte   
 where date <= @endDate - 1 
 )  
 
  insert into @a
  SELECT MIN(DATE), MAX(DATE), 'Week ' + CAST(DatePart(ww, DATE) as varchar(10))  as Week_NO_Of_Year
  FROM CTE
  Group by 'Week ' + CAST(DatePart(ww, DATE) as varchar(10))
  order by MIN(DATE) ASC
  
  OPTION (MAXRECURSION 0)
 end
 else if @type = 'Monthly'
  begin 
  
   ;with cte as 
 ( select CONVERT(DATETIME,@startDate) AS DATE 
 UNION ALL 
 select Dateadd(dd,1,DATE) as date 
 from cte   
 where date <= @endDate - 1 
 )  
  insert into @a
  SELECT MIN(DATE), MAX(DATE), CAST(DATENAME(month, DATE) as varchar(10))  as Week_NO_Of_Year
  FROM CTE
  Group by CAST(DATENAME(month, DATE) as varchar(10))
  order by MIN(DATE) ASC
  
  OPTION (MAXRECURSION 0)
 end
 else if @type = 'quarterly'
  begin 
  
 ;with cte as 
 ( select CONVERT(DATETIME,@startDate) AS DATE 
 UNION ALL 
 select Dateadd(dd,1,DATE) as date 
 from cte   
 where date <= @endDate - 1 
 )  
  insert into @a
  SELECT MIN(DATE), MAX(DATE), 'Quarter ' + CAST(DatePart(QQ, DATE) as varchar(10))  as Week_NO_Of_Year
  FROM CTE
  Group by 'Quarter ' + CAST(DatePart(QQ, DATE) as varchar(10))
  order by MIN(DATE) ASC
  
  OPTION (MAXRECURSION 0)
 end
 else if @type = 'yearly'
  begin 
  
 ;with cte as 
 ( select CONVERT(DATETIME,@startDate) AS DATE 
 UNION ALL 
 select Dateadd(dd,1,DATE) as date 
 from cte   
 where date <= @endDate - 1 
 )  
  insert into @a
  SELECT MIN(DATE), MAX(DATE), 'year ' + CAST(DatePart(yy, DATE) as varchar(10))  as Week_NO_Of_Year
  FROM CTE
  Group by 'year ' + CAST(DatePart(yy, DATE) as varchar(10))
  order by MIN(DATE) ASC
  
  OPTION (MAXRECURSION 0)
 end

  RETURN;
END

Hope it will help some one the same way as it did to me :)

Happy coding.

Kill all connections of a Database SQL server 2008

Hi All,

I was just trying to restore a DB and it was giving me error of Database in use so can not be restored. I googled a bit and found a nice solution so decided to share with you guys.

SET NOCOUNT ON
DECLARE @DBName varchar(50)
DECLARE @spidstr varchar(8000)
DECLARE @ConnKilled smallint
SET @ConnKilled=0
SET @spidstr = ''

Set @DBName = 'DATABASE_NAME'
IF db_id(@DBName) < 4
BEGIN
PRINT 'Connections to system databases cannot be killed'
RETURN
END
SELECT @spidstr=coalesce(@spidstr,',' )+'kill '+convert(varchar, spid)+ '; '
FROM master..sysprocesses WHERE dbid=db_id(@DBName)

IF LEN(@spidstr) > 0
BEGIN
EXEC(@spidstr)
SELECT @ConnKilled = COUNT(1)
FROM master..sysprocesses WHERE dbid=db_id(@DBName)
END

It worked like charm hope you enjoy it as well.

Happy Coding.

Simplest way to use JQuery DataTable in ASP.net (C#)

Hi All,

Today i will be using JQuery dataTable in ASP.net c# code without any 'bProcessing' , 'bServerSide' and 'sAjaxSource'.

First of All what is Jquery Datatable, just Click and find out :).

After getting the basic structure of JQuery datatable you may find that all the server side examples on site is in PHP and the stuff done for ASP.net seems so difficult nor such newbies like me. so lets talk about a simpler solution.

First we create a simple HTML table. and the place where data will get populated just add an ASP Literal control. :)

<table class="datatable">
                        <thead>
                            <tr>
                                <th class="bSortable">
                                    <input type="checkbox" class="checkbox select-all" />
                                </th>
                                <th>
                                    First Name
                                </th>
                                <th>
                                    Middle Name
                                </th>
                                <th>
                                    Last Name
                                </th>
                               </tr>
                        </thead>
                        <tbody>
                            <asp:Literal runat="server" ID="ltData"></asp:Literal>
                        </tbody>
                    </table>

Now on the server side write a simple function to populate the literal with required data.

private void loadGrid()
    {
        StringBuilder sb = new StringBuilder();
        DataTable dt = MyData.GetAllUserData();
        if (dt != null)
            if (dt.Rows.Count > 0)
                foreach (DataRow dr in dt.Rows)
                {

                    sb.Append("<tr>");
                    sb.Append("<td>");
                    sb.Append("<input type='checkbox' class='checkbox' />");
                    sb.Append("</td>");
                    sb.Append("<td>");
                    sb.Append((dr["vchFirstName"]));
                    sb.Append("</td>");
                    sb.Append("<td>");
                    sb.Append((dr["vchMiddleName"]));
                    sb.Append("</td>");
                    sb.Append("<td>");
                    sb.Append((dr["vchLastName"]));
                    sb.Append("</td>");
                   
                    sb.Append("</tr>");

                }

        ltData.Text = sb.ToString();

    }

For initializing the data table you can refer to the site or use the following code and write it in document.ready() function of jQuery.

function LoadTable() {
 $('.datatable').dataTable({
  'bLengthChange': true,
  'bPaginate': true,
  'sPaginationType': 'full_numbers',
  'iDisplayLength': 5,
  'bInfo': false,
  'oLanguage': 
  {
   'sSearch': 'Search all columns:',
   'oPaginate': 
   {
    'sNext': '>',
    'sLast': '>>',
    'sFirst': '<<',
    'sPrevious': '<'
   }
  }
 });
}

At this point we are done with the basic grid view with just text data but what if we need to perform some functions like delete a particular row, Just don't worry about it create a webservice and write the deletion function in that service then use the following code to call it using jQuery.

function DeleteMe(string IdToBeDeleted){
var webMethod = 'DeleteRecord.asmx/DeleteUser'
var parameters = "{'ID':'" + IdToBeDeleted + "'}"

$.ajax({
        type: "POST",
        url: webMethod,
        data: parameters,
        contentType: "application/json; charset=utf-8",
        dataType: "json",
        success: function(msg) {    
                alert('Success Deletion');
        },
        error: function(e){
                 alert('Failure Deletion');                         
        }
});

and for Datatable just create an other column and call this DeleteMe function with RowId as parameter to it and a nice deletion will occur.

Hope it helps.

Happy coding....

JQuery menu anchor tag click problem

Hi All,

Once again a new problem with another solution. If you have ever tried to create a JQuery menu whose click pops out another sub menu and you have to place an anchor tag with in that submenu, you wont find the anchor tag click to be working.

Suppose i have the following structure.

<div class="menu">
    <ul>
        <li><a href="#">My Parent Menu</a>
         <ul>
            <li><a href="myPage.aspx">My Title</a></li>
            <li><a href="#">another Title</a></li>
         </ul>
       </li>
    </ul>
</div>

and you need the internal UL to pop out on clicking external LI. The JQuery sample is.

$('.menu ul li').click(function () {
  submenu = $(this).find('ul');
  if (submenu.is(':visible'))
   submenu.slideUp(150);     
  else
                submenu.slideDown(200);         
  return false;
 });

So what you need to do is to stop propagation of external li item to the anchor tag by

$('.menu li ul li a').click(function(e) {
        e.stopPropagation();
    });

An now you can do your click and pop Ins/pop outs at the same time.

Happy Coding.

Click Server button problem in jquery Model popup ASP.NET

Hi All,

If you have ever used JQuery model popup with server side controls on it you might face problem when clicking a server button on it, That the server event is not fired when and hence you can not open perform any server activity inside that popup.

The reason is that JQuery models are designed in a way that they should contain a form tag as an ASP.net server page but when the popup appears div gets out from document object model and gets attached to the end of page and hence it never contained a form tag which stops "runat=server" to work so server events didn't gets fired.

The solution is very simple just add the model popup to the parent form and you are done. i have also attached the sample code.

jQuery(function() {
   var myDialog= jQuery("#DialogName").dialog({ 
                        // SET Properties here 
          });
  // Adding Dialog to Parent form will do the Job
  myDialog.parent().appendTo(jQuery("form:first"));
});

Also Take care of one thing that the addition of model dialog to page must b done inside the page it this is done in external javascript file it wont work.

Happy Coding.

Split view Controller, Calling Detail view controller from Navigation view controller.

You might find lots of Blogs and Books to use Split view controller in a normal way with one root view controller and a detail view controller like a really nice tutorial can be seen here. You might also find a tutorial that will tell you how to call different detail view controllers from root view controller here.

But when it comes to call detail view controller from a controller other then Root view controller help just diminish. As we can use navigation controller in root view controller that will navigate to a controller which is not defined in app delegate for split view controller then comes the real deal of calling it from last controller in navigation.

The solution that i proposed here is to use NSnotifications to broad cast a message with proper name and then use that message in detail view controller to call a function in detail view controller and for the detail item that is to be passed use the Object: parameter on NSnotification to send an object to it which can be utilized in detailview controller.

Lets check some code snippits for better elaboration.

Here is a root view controller didSelectRowatindexPath function from which mostly detail view controller is called but we need some navigation so i am calling a TestViewController by using navigation of root view controller.

- (void)tableView:(UITableView *)aTableView didSelectRowAtIndexPath:(NSIndexPath *)indexPath {
 int index = [indexPath row];
 
 Category *category = [categories objectAtIndex:index];
 
 
 TestViewController *shopList = [[TestViewController alloc] initWithNibName:@"TestViewController " bundle:[NSBundle mainBundle]];
 shopList.categoryId = category.categoryId;
 [[self navigationController]  pushViewController:shopList animated:YES];
 [shopList release];

}

Now to Reach to a test view controller from which a detail view controller needs to be called in the didSelectRowatindexPath function of this viewcontroller we will be broadcasting an NSTotification

- (void)tableView:(UITableView *)tableView didSelectRowAtIndexPath:(NSIndexPath *)indexPath {
 int index = [indexPath row];
 
 [[NSNotificationCenter defaultCenter] postNotificationName:@"dealNotification" object:[article;

}

That all for a new controller in navigation controller. now we move to detail view controller where in ViewdidLoad an observer is added and the function that needs to be called is passed to selector.

- (void)viewDidLoad {

 [[NSNotificationCenter defaultCenter] addObserver: self selector: @selector(iamcallingyou:) name:@"dealNotification" object: nil];
    [super viewDidLoad];
}

Now finally the function whose name is given in selector.

- (void) iamcallingyou:(NSNotification *)pNotification
{
 
 NSLog(@"hi");
// This is a Class Object that i have passed in to object parameter when broadcasting notifications.
 article = (Article *)[pNotification object];
  
 if (popoverController != nil) {
        [popoverController dismissPopoverAnimated:YES];
    } 
 

}

And we are done with it... do some memory management in final touches and we are good to go

Happy Coding.

Memory Exception EXC_BAD_ACCESS, Tracking with NSZombie

Hi All,

I am just a beginner to Objective C and I phone but Apple takes your brain out once you start coding for them and the biggest brainstorming occurs when its about the memory and its allocation / de allocation.

So i was stuck in same kind of exception and wasted many hours for that but then NSzombie helped me a lot actually when NSZombie is enabled the GDB is interreped when ever we try to access an object that is deallocated from memory.

Its just simple to Enable NsZombie.

1. Goto Executables in project tree





2. Add a Name value pair NSZombieEnable and value equals to YES.






Hope it will also help you,

Happy Coding.