Paging Sorting Searching in MySQL Procedure

Hi All,

Lets do some mySQL programming in the last post i have shown how to do custom pagination from jQuery Data table and today i will share the procedure that i called at the back end to do the pagination stuff this one is in mySQL

USE `Database_Name`;

DROP procedure IF EXISTS `up_GlobalSearch`;



DELIMITER $$

USE `Database_Name`$$

CREATE PROCEDURE `scientestdb`.`up_GlobalSearch` (

IN pFreeText varchar(200), 

IN pStartRecord int, 

IN pSize int, 

IN pSortColumn varchar(20),

IN pSortDirection varchar(20)

)

BEGIN





SET @q := concat('select * from Table_Name where MATCH (Column_Name1,Column_Name2) AGAINST (\'', pFreeText ,'\'  IN BOOLEAN MODE)  ORDER By ', pSortColumn, ' ' , pSortDirection, ' limit ?,?');

PREPARE stmt1 FROM @q;

SET @recStrt = pStartRecord;

SET @recCount = pSize;

EXECUTE stmt1 USING @recStrt, @recCount;





END

$$



DELIMITER ;

Hope it helps.

Happy Coding.

Regards,
U

Extending jQuery Datatable with server side processing and pagination.

Hi All,

Once again i came up with a blog for jQuery Datatable as i really liked this control and love to extend its functionality for .NET, in my previous blog i have bound jQuery Data table with dataset at once which might not be very useful then data is so large as fetching complete data in one hit is not a good job.

So Today we will do some bServerSide processing and make this data table more useful in fetching records from database with pagination and sorting enabled i am still not able to coupe with free text search in returned results but ill share that soon as well.

first step would be to create a handler / web service which we would be passing in the argument sAjaxSource of datatable.

Remember that the response should be a JSON formated text as datatable only reads JSON in adata parameter.

Lets start with the Handler.

<%@ WebHandler Language="C#" Class="Search" %>

using System;
using System.Web;
using System.Collections.Generic;
using System.Web.Script.Serialization;
using System.Linq;
using System.Data;
using DAL.Modules;
using DAL;

public class Search : IHttpHandler
{
    public void ProcessRequest(HttpContext context)
    {
        // Those parameters are sent by the plugin
        var iDisplayLength = int.Parse(context.Request["iDisplayLength"]);
        var iDisplayStart = int.Parse(context.Request["iDisplayStart"]);
        var iSortCol = int.Parse(context.Request["iSortCol_0"]);
        var iSortDir = context.Request["sSortDir_0"];


        IEnumerable<SearchResults> myResults = SearchResults.GetResults(iDisplayStart, iDisplayLength);

        // Define an order function based on the iSortCol parameter
        Func<SearchResults, object> order = rslt =>
        {
            if (iSortCol == 0)
                return rslt.Project_Name;
            else if (iSortCol == 1)
                return rslt.Project_Goal;
            else if (iSortCol == 2)
                return rslt.Proposed_Budget;
            else if (iSortCol == 3)
                return rslt.Status;
            else
                return rslt.Creation_Date;
        };

        // Define the order direction based on the iSortDir parameter
        if ("desc" == iSortDir)
        {
            myResults = myResults.OrderByDescending(order);
        }
        else
        {
            myResults = myResults.OrderBy(order);
        }


        // Remove Skip and Take when doing Custom Pagination from Database. 
        var result = new
        {
            iTotalRecords = myResults.Count(),
            iTotalDisplayRecords = myResults.Count(),
            aaData = myResults
                .Select(p => new[] { p.Project_Name, p.Project_Goal, p.Proposed_Budget, p.Status, p.Creation_Date, p.View_Details })
                .Skip(iDisplayStart)
                .Take(iDisplayLength)

        };

        var serializer = new JavaScriptSerializer();
        var json = serializer.Serialize(result);
        context.Response.ContentType = "application/json";
        context.Response.Write(json);
    }

    public bool IsReusable
    {
        get
        {
            return false;
        }
    }
}

So parsing JSON would require a class structure.

public class SearchResults
{
    public string Project_Name { get; set; }
    public string Project_Goal { get; set; }
    public string Status { get; set; }
    public string Proposed_Budget { get; set; }
    public string Creation_Date { get; set; }
    public string View_Details { get; set; }

    public static IEnumerable<SearchResults> GetResults(int RecordId, int count)
    {
        DataSet ds = null;// Get you Dataset from Database by passing the two parameters for pagination. 

        if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                yield return new SearchResults
                {
                    Project_Name = Helper.ParseString(dr["Project_Name"]),
                    Project_Goal = Helper.ParseString(dr["Project_Goal"]),
                    Status = Helper.ParseString(dr["Status"]),
                    Proposed_Budget = Helper.ParseString(dr["Proposed_Budget"]),
                    Creation_Date = Helper.ParseString(DateTime.Now),
                    View_Details = "<a href='#'>View</a>"
                };
            }
    }
}

Now its time for the final step calling it in ASPX page.

the javascript for table would be

<script type="text/javascript">
        $(function () {
            $('#.datatable').dataTable({
                'bServerSide': true,
                "iDisplayLength": 2,
                "sPaginationType": "full_numbers",
                'sAjaxSource': '/Handlers/Search.ashx',
                "bFilter": false
            });
        });
    </script>

now finally we come to the last step which all of you must be familiar of is the HTML table which will turn into the jQuery Datatable.

<table cellpadding="0" cellspacing="0" border="0" class="datatable">
                    <thead>
                        <tr>
                            <th>
                                Project Name
                            </th>
                            <th>
                                Project Goal
                            </th>
                            <th>
                                Proposed Budget
                            </th>
                            <th>
                                Status
                            </th>
                            <th>
                                Creation Date
                            </th>
                            <th>
                            </th>
                        </tr>
                    </thead>
                    <tbody>
                        <tr>
                            <td colspan="5" class="dataTables_empty">
                                <img alt="Please Wait..." src="/images/loader.png" />
                            </td>
                        </tr>
                    </tbody>
                </table>

That's All if you find any query please leave a comment.

Happy Coding.

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.