In Telerik Report get search button to grab new data from database using code .

How to get search button to grab new data from database in Telerik Report using code .

HTML Section 

@using Telerik.Reporting.Examples.CSharp.ZipCodeReport;

@{

    ViewBag.Title = "Zip Code Report";

    Layout = "~/Views/Shared/_LayoutAllReport.cshtml";

}

@section styles

{

    <link href="http://netdna.bootstrapcdn.com/font-awesome/3.2.1/css/font-awesome.css" rel="stylesheet" />

    <link href="http://cdn.kendostatic.com/2013.2.918/styles/kendo.common.min.css" rel="stylesheet" />

    @*<link href="http://cdn.kendostatic.com/2013.2.918/styles/kendo.blueopal.min.css" rel="stylesheet" />*@

    <style>

        #reportViewer1 {

            position: relative;

            left: 0;

            right: 0;

            top: 0;

            bottom: 0;

            overflow: hidden;

            font-family: Verdana, Arial;

            height: 1150px;

        }

         #reportViewer1 .trv-parameters-area {

            display:none;

        }

    </style>

    <link href="@Url.Content("~/ReportViewer/styles/telerikReportViewer-9.0.15.324.css")" rel="stylesheet" />

}

<div style="background:#fff; border-bottom: 3px solid #396087;">

    <div class="container">

        <div class="row">

            <div class="col-md-9">

                <div class="clearfix">

                    <div class="col-md-2 form-group clearfix">

                        <label>Date From</label>

                        @(Html.Kendo().DatePicker().Name("DateFrom")

                              .Value(Utility.Common.common.ConvertUTCtoLocalTime(DateTime.UtcNow)).Format("MM/dd/yyyy")

                              .Max(DateTime.Today)

                              .Events(e =>

                              {

                                  e.Change("dateChanged");

                              })

                        )

                    </div>

                    <div class="col-md-2 form-group clearfix">

                        <label>Date To</label>

                        @(Html.Kendo().DatePicker().Name("DateTo")

                          .Value(Utility.Common.common.ConvertUTCtoLocalTime(DateTime.UtcNow)).Format("MM/dd/yyyy")

                          .Max(DateTime.Today)

                          .Events(e =>

                          {

                              e.Change("dateChanged");

                          })

                        )

                    </div>

                    <div class="col-md-2 form-group clearfix">

                        <label>&nbsp;</label>

                        @(Html.Kendo().Button()

                                .Name("btnSearch")

                                .HtmlAttributes(new { type = "button", @class = "small-button" })

                                .Content("Search"))

                    </div>

                </div>

            </div>

            <div class="col-md-3">

                <div class="top-btn floatR" style="margin-top:20px;">

                    <a id="iconTextButton2" href="/Patient/List" class="k-button" data-role="button" role="button" aria-disabled="false" tabindex="0">Find Patient</a>

                    <script>

                        jQuery(function () { jQuery("#iconTextButton2").kendoButton({}); });

                    </script>

                </div>

            </div>

        </div>

    </div>

</div>

<div class="container">

    <div class="row">

        @{

            var typeReportSource = new TypeReportSource() { TypeName = typeof(ZipCodeReport).AssemblyQualifiedName };

            typeReportSource.Parameters.Add("DisplayDateFrom", Utility.Common.common.ConvertUTCtoLocalTime(DateTime.UtcNow).ToString("MM/dd/yyyy"));

            typeReportSource.Parameters.Add("DisplayDateTo", Utility.Common.common.ConvertUTCtoLocalTime(DateTime.UtcNow).ToString("MM/dd/yyyy"));

        }

        @(Html.TelerikReporting().ReportViewer()

                                        // Each report viewer must have an id - it will be used by the initialization script

                                        // to find the element and initialize the report viewer.

                            .Id("reportViewer1")

                                        // The URL of the service which will serve reports.

                                        // The URL corresponds to the name of the controller class (ReportsController).

                                        // For more information on how to configure the service please check http://www.telerik.com/help/reporting/telerik-reporting-rest-conception.html.

                            .ServiceUrl(Url.Content("~/api/reports/"))

                                        // The URL for the report viewer template. The template can be edited -

                                        // new functionalities can be added and unneeded ones can be removed.

                                        // For more information please check http://www.telerik.com/help/reporting/html5-report-viewer-templates.html.

                            .TemplateUrl(Url.Content("~/ReportViewer/templates/telerikReportViewerTemplate-9.0.15.324.html"))

                                        // Strongly typed ReportSource - TypeReportSource or UriReportSource.

                                                            .ReportSource(typeReportSource)

                                        // Specifies whether the viewer is in interactive or print preview mode.

                                        // PRINT_PREVIEW - Displays the paginated report as if it is printed on paper. Interactivity is not enabled.

                                        // INTERACTIVE - Displays the report in its original width and height with no paging. Additionally interactivity is enabled.

                            .ViewMode(ViewModes.PRINT_PREVIEW)

                                        // Sets the scale mode of the viewer.

                                        // Three modes exist currently:

                                        // FIT_PAGE - The whole report will fit on the page (will zoom in or out), regardless of its width and height.

                                        // FIT_PAGE_WIDTH - The report will be zoomed in or out so that the width of the screen and the width of the report match.

                                        // SPECIFIC - Uses the scale to zoom in and out the report.

                            .ScaleMode(ScaleModes.SPECIFIC)

                                        // Zoom in and out the report using the scale

                                        // 1.0 is equal to 100%, i.e. the original size of the report

                            .Scale(1.0)

                                        // Sets whether the viewer’s client session to be persisted between the page’s refreshes(ex. postback).

                                        // The session is stored in the browser’s sessionStorage and is available for the duration of the page session.

                            .PersistSession(false)

                                        // Sets the print mode of the viewer.

                            .PrintMode(PrintMode.AutoSelect)

                                        // Defers the script initialization statement. Check the scripts section below -

                                        // each deferred script will be rendered at the place of TelerikReporting().DeferredScripts().

                            .Deferred()

                           

                            

        )

    </div>

</div>

Script Section

@section scripts

{

    <!--kendo.all.min.js can be used as well instead of kendo.web.min.js and kendo.mobile.min.js-->

    <script src="@Url.Content("~/ReportViewer/js/telerikReportViewer-9.0.15.324.js")"></script>

    <script src="http://cdn.kendostatic.com/2013.2.918/js/kendo.web.min.js"></script>

    <!--kendo.mobile.min.js - optional, if gestures/touch support is required-->

    <script src="http://cdn.kendostatic.com/2013.2.918/js/kendo.mobile.min.js"></script>

    @(

        // All deferred initialization statements will be rendered here

        Html.TelerikReporting().DeferredScripts()

    )

    <script type="text/javascript">

        function ReportViwerLoad()

        {

            var reportUrl = '@Url.Action("GetConvertUTCtoLocalTime", "Report")';

            reportUrl = reportUrl + '?DateFrom=' + $("#DateFrom").val() + ' ' + get12HourFormatTime(new Date()) + '&DateTo=' + $("#DateTo").val() + ' ' + get12HourFormatTime(new Date());

            $.ajax({

                type: "POST",

                url: reportUrl,

                dataType: 'json',

                success: function (data) {

                    var viewer = $("#reportViewer1").data("telerik_ReportViewer");

                    viewer.reportSource({

                        report: viewer.reportSource().report,

                        parameters: { DateFrom: data.returnVal.DateFrom, DateTo: data.returnVal.DateTo, DisplayDateFrom: $("#DateFrom").val(), DisplayDateTo: $("#DateTo").val() }

                    });

                   

                    viewer.refreshReport();

                },

                error: function () {  }

            });

        }

        $("#btnSearch").click(function () {

            ReportViwerLoad();

        });

        function dateChanged()

        {

            ReportViwerLoad();

        }

    </script>

}

Sql Procedure to display audit log details in customize way

Sql Procedure to display audit log details in customize way 


CREATE PROCEDURE dbo.sp_AuditLogDetailsList --'9','','', '1','','','Times','desc',''

@UserNameId varchar(max) = NULL,
@FromDate nvarchar(max),
@ToDate nvarchar(max),
@StartRecNo int,
@ItemCount int,
@SearchKeyWord nvarchar(max),
@sortColumnName varchar(30),
@sortDirection varchar(10),
@allcountresult int OUTPUT
AS
BEGIN
    DECLARE @SQL nvarchar(max);
    SET NOCOUNT ON;
    SET FMTONLY OFF;

    SELECT DISTINCT b.*,
0 AS RN INTO #TMP_SEARCH
FROM(
SELECT DISTINCT UserName,
 UpdateDate AS Times,
 '<font size="2" color="#0088DD">' + UserName + '</font>' + ' @' + '<b><font size="2" color="#2788DD">' + ISNULL(admFunctionalityTableMapping.admFunctionalityTableMapping_ModuleName,'') + '</font></b>' + ' ' + CASE Audit.TYPE
WHEN 'I' THEN 'inserted'
WHEN 'U' THEN 'updated'
ELSE 'Deleted'
END + ' a record with #' + (
SELECT REPLACE(items,'>','')
  FROM dbo.fn_SplitAuditID(Audit.PK,'=')AS ID) + CASE
WHEN Audit.CaseId IS NULL THEN ''
   ELSE ' Reffernce Case Number:'
END + ' <a href=/investigation/SelectCase/' + ISNULL(Audit.CaseId,'') + '>' + ISNULL(invCase.invCase_CaseNum,'') + '</a> ' + CASE Audit.TYPE
WHEN 'U' THEN '</br>' + (
SELECT *
  FROM dbo.fn_SplitAuditID(ISNULL(Audit.FieldName,''),'_')AS FieldName) + ' changed from ' + '#' + ISNULL(Audit.OldValue,0) + ' to ' + '#' + ISNULL(Audit.NewValue,0)
   ELSE ''
END AS Activity
  FROM
  Audit
  LEFT OUTER JOIN admFunctionalityTableMapping
ON Audit.TableName=admFunctionalityTableMapping.admFunctionalityTableMapping_TableName
  LEFT OUTER JOIN invCase
ON Audit.CaseId=invCase.invCase_CaseID)b;
    CREATE TABLE #TMP_SEARCH1(RN int,
UserName varchar(max),
Times datetime,
Activity varchar(max));

    SET @SQL = 'INSERT INTO #TMP_SEARCH1
          SELECT ROW_NUMBER() over (order by ' + @sortColumnName + ' ' + @sortDirection + ') AS RN,UserName,Times,Activity
          FROM #TMP_SEARCH
        where
        (UserName LIKE ''%' + ISNULL(RTRIM(LTRIM(@SearchKeyWord)),'') + '%''
        OR Times LIKE ''%' + ISNULL(RTRIM(LTRIM(@SearchKeyWord)),'') + '%''
        OR Activity  LIKE ''%' + ISNULL(RTRIM(LTRIM(@SearchKeyWord)),'') + '%'')
   
     
        ORDER BY ' + @sortColumnName + ' ' + @sortDirection;
    -- select @SQL
    EXECUTE sp_executesql @SQL;
    --SELECT  @allcountresult=COUNT(*) FROM #TMP_SEARCH1
    CREATE TABLE #TMP_SEARCH2(RN int,
UserName varchar(max),
Times datetime,
Activity varchar(max));

    IF @UserNameId='NULL'
   AND @FromDate=''
   AND @ToDate=''
  BEGIN
 IF @ItemCount=0
BEGIN
INSERT INTO #TMP_SEARCH2(RN,
UserName,
Times,
Activity)
SELECT RN,
 UserName,
 Times,
 Activity
 FROM #TMP_SEARCH1;

END;
 ELSE
BEGIN
INSERT INTO #TMP_SEARCH2(RN,
UserName,
Times,
Activity)
SELECT RN,
 UserName,
 Times,
 Activity
 FROM #TMP_SEARCH1
 WHERE RN>=@StartRecNo
   AND RN<@StartRecNo + @ItemCount;
END;
  END;
    ELSE
  BEGIN
 IF @UserNameId<>'NULL'
AND @FromDate=''
AND @ToDate=''
BEGIN
INSERT INTO #TMP_SEARCH2(RN,
UserName,
Times,
Activity)
SELECT RN,
 UserName,
 Times,
 Activity
 FROM
 #TMP_SEARCH1
 LEFT OUTER JOIN appUsers
ON #TMP_SEARCH1.UserName=appUsers.appUsers_UserName
 WHERE appUsers.appUsers_ID IN(
 SELECT Items
   FROM fn_Split(@UserNameId,','));

END;
 ELSE
BEGIN
IF @UserNameId='NULL'
   AND @FromDate<>'NULL'
   AND @ToDate<>'NULL'
   BEGIN
  INSERT INTO #TMP_SEARCH2(RN,
  UserName,
  Times,
  Activity)
  SELECT RN,
UserName,
Times,
Activity
FROM #TMP_SEARCH1
WHERE CAST(CONVERT(varchar(max),Times,101)AS datetime)BETWEEN @FromDate AND @ToDate;
   END;
ELSE
   BEGIN
  IF @UserNameId<>'NULL'
 AND @FromDate<>'NULL'
 AND @ToDate<>'NULL'
 BEGIN
INSERT INTO #TMP_SEARCH2(RN,
UserName,
Times,
Activity)
SELECT RN,
  UserName,
  Times,
  Activity
  FROM
  #TMP_SEARCH1
  LEFT OUTER JOIN appUsers
ON #TMP_SEARCH1.UserName=appUsers.appUsers_UserName
  WHERE appUsers.appUsers_ID IN(
  SELECT Items
FROM fn_Split(@UserNameId,','))
AND CAST(CONVERT(varchar(max),Times,101)AS datetime)BETWEEN @FromDate AND @ToDate;
 END;
   END;
END;
  END;
    SELECT @allcountresult = COUNT(*)
FROM #TMP_SEARCH2;
    SELECT RN,
UserName,
Times,
Activity
FROM #TMP_SEARCH2
WHERE RN>=@StartRecNo
  AND RN<@StartRecNo + @ItemCount;
END;

List all triggers in SQL Server database with table name and table's schema

SELECT 
     sysobjects.name AS trigger_name 
    ,USER_NAME(sysobjects.uid) AS trigger_owner 
    ,s.name AS table_schema 
    ,OBJECT_NAME(parent_obj) AS table_name 
    ,OBJECTPROPERTY( id, 'ExecIsUpdateTrigger') AS isupdate 
    ,OBJECTPROPERTY( id, 'ExecIsDeleteTrigger') AS isdelete 
    ,OBJECTPROPERTY( id, 'ExecIsInsertTrigger') AS isinsert 
    ,OBJECTPROPERTY( id, 'ExecIsAfterTrigger') AS isafter 
    ,OBJECTPROPERTY( id, 'ExecIsInsteadOfTrigger') AS isinsteadof 
    ,OBJECTPROPERTY(id, 'ExecIsTriggerDisabled') AS [disabled] 
FROM sysobjects 

INNER JOIN sysusers 
    ON sysobjects.uid = sysusers.uid 

INNER JOIN sys.tables t 
    ON sysobjects.parent_obj = t.object_id 

INNER JOIN sys.schemas s 
    ON t.schema_id = s.schema_id 

WHERE sysobjects.type = 'TR' 

Cool Clock Using Java Script

Have a clock with date  near your mouse cursor and have it follow your mouse pointer around using this script. Easy to customize the font and colour.


 <html>
<body bgcolor=blue>

<style type="text/css">
<!--
/*Do not Alter these. Set for alignment*/
.css1{
position:absolute;top:0px;left:0px;
width:16px;height:16px;
font-family:Arial,sans-serif;
font-size:16px;
text-align:center;
font-weight:bold;
}
.css2{
position:absolute;top:0px;left:0px;
width:10px;height:10px;
font-family:Arial,sans-serif;
font-size:10px;
text-align:center;
}
//-->
</style>


<SCRIPT LANGUAGE="JavaScript">

if (document.getElementById&&!document.layers){

//Clock colours
dCol='#00ff00';//date colour.
fCol='#ffffff';//face colour.
sCol='#ffffff';//seconds colour.
mCol='#00ff00';//minutes colour.
hCol='#00ff00';//hours colour.

//Controls
del=0.6;  //Follow mouse speed.
ref=40;   //Run speed (timeout).

//Alter nothing below! Alignments will be lost!
var ieType=(typeof window.innerWidth != 'number');
var docComp=(document.compatMode);
var docMod=(docComp && docComp.indexOf("CSS") != -1);
var ieRef=(ieType && docMod)
?document.documentElement:document.body;
theDays=new Array("SUNDAY","MONDAY","TUESDAY","WEDNESDAY","THURSDAY","FRIDAY","SATURDAY");
theMonths=new Array("JANUARY","FEBRUARY","MARCH","APRIL","MAY","JUNE","JULY","AUGUST","SEPTEMBER","OCTOBER","NOVEMBER","DECEMBER");
date=new Date();
day=date.getDate();
year=date.getYear();
if (year < 2000) year=year+1900;
tmpdate=" "+theDays[date.getDay()]+" "+day+" "+theMonths[date.getMonth()]+" "+year;
D=tmpdate.split("");
N='3 4 5 6 7 8 9 10 11 12 1 2';
N=N.split(" ");
F=N.length;
H='...';
H=H.split("");
M='....';
M=M.split("");
S='.....';
S=S.split("");
siz=40;
eqf=360/F;
eqd=360/D.length;
han=siz/5.5;
ofy=-7;
ofx=-3;
ofst=70;
tmr=null;
vis=true;
mouseY=0;
mouseX=0;
dy=new Array();
dx=new Array();
zy=new Array();
zx=new Array();
tmps=new Array();
tmpm=new Array();
tmph=new Array();
tmpf=new Array();
tmpd=new Array();
var sum=parseInt(D.length+F+H.length+M.length+S.length)+1;
for (i=0; i < sum; i++){
dy[i]=0;
dx[i]=0;
zy[i]=0;
zx[i]=0;
}

algn=new Array();
for (i=0; i < D.length; i++){
algn[i]=(parseInt(D[i]) || D[i]==0)?10:9;
document.write('<div id="_date'+i+'" class="css2" style="font-size:'+algn[i]+'px;color:'+dCol+'">'+D[i]+'<\/div>');
tmpd[i]=document.getElementById("_date"+i).style;
}
for (i=0; i < F; i++){
document.write('<div id="_face'+i+'" class="css2" style="color:'+fCol+'">'+N[i]+'<\/div>');
tmpf[i]=document.getElementById("_face"+i).style;
}
for (i=0; i < H.length; i++){
document.write('<div id="_hours'+i+'" class="css1" style="color:'+hCol+'">'+H[i]+'<\/div>');
tmph[i]=document.getElementById("_hours"+i).style;
}
for (i=0; i < M.length; i++){
document.write('<div id="_minutes'+i+'" class="css1" style="color:'+mCol+'">'+M[i]+'<\/div>');
tmpm[i]=document.getElementById("_minutes"+i).style;
}
for (i=0; i < S.length; i++){
document.write('<div id="_seconds'+i+'" class="css1" style="color:'+sCol+'">'+S[i]+'<\/div>');
tmps[i]=document.getElementById("_seconds"+i).style;       
}

function onoff(){
if (vis){
 vis=false;
 document.getElementById("control").value="Clock On";
 }
else{
 vis=true;
 document.getElementById("control").value="Clock Off";
 Delay();
 }
kill();
}

function kill(){
if (vis)
 document.onmousemove=mouse;
else
 document.onmousemove=null;
}

function mouse(e){
var msy = (!ieType)?window.pageYOffset:0;
if (!e) e = window.event;  
 if (typeof e.pageY == 'number'){
  mouseY = e.pageY + ofst - msy;
  mouseX = e.pageX + ofst;
 }
 else{
  mouseY = e.clientY + ofst - msy;
  mouseX = e.clientX + ofst;
 }
if (!vis) kill();
}
document.onmousemove=mouse;

function winDims(){
winH=(ieType)?ieRef.clientHeight:window.innerHeight;
winW=(ieType)?ieRef.clientWidth:window.innerWidth;
}
winDims();
window.onresize=new Function("winDims()");

function ClockAndAssign(){
time = new Date();
secs = time.getSeconds();
sec = Math.PI * (secs-15) / 30;
mins = time.getMinutes();
min = Math.PI * (mins-15) / 30;
hrs = time.getHours();
hr = Math.PI * (hrs-3) / 6 + Math.PI * parseInt(time.getMinutes()) / 360;

for (i=0; i < S.length; i++){
 tmps[i].top=dy[D.length+F+H.length+M.length+i]+ofy+(i*han)*Math.sin(sec)+scrollY+"px";
 tmps[i].left=dx[D.length+F+H.length+M.length+i]+ofx+(i*han)*Math.cos(sec)+"px";
 }
for (i=0; i < M.length; i++){
 tmpm[i].top=dy[D.length+F+H.length+i]+ofy+(i*han)*Math.sin(min)+scrollY+"px";
 tmpm[i].left=dx[D.length+F+H.length+i]+ofx+(i*han)*Math.cos(min)+"px";
 }
for (i=0; i < H.length; i++){
 tmph[i].top=dy[D.length+F+i]+ofy+(i*han)*Math.sin(hr)+scrollY+"px";
 tmph[i].left=dx[D.length+F+i]+ofx+(i*han)*Math.cos(hr)+"px";
 }
for (i=0; i < F; i++){
 tmpf[i].top=dy[D.length+i]+siz*Math.sin(i*eqf*Math.PI/180)+scrollY+"px";
 tmpf[i].left=dx[D.length+i]+siz*Math.cos(i*eqf*Math.PI/180)+"px";
 }
for (i=0; i < D.length; i++){
 tmpd[i].top=dy[i]+siz*1.5*Math.sin(-sec+i*eqd*Math.PI/180)+scrollY+"px";
 tmpd[i].left=dx[i]+siz*1.5*Math.cos(-sec+i*eqd*Math.PI/180)+"px";
 }
if (!vis)clearTimeout(tmr);
}

buffW=(ieType)?80:90;
function Delay(){
scrollY=(ieType)?ieRef.scrollTop:window.pageYOffset;
if (!vis){
 dy[0]=-100;
 dx[0]=-100;
}
else{
 zy[0]=Math.round(dy[0]+=((mouseY)-dy[0])*del);
 zx[0]=Math.round(dx[0]+=((mouseX)-dx[0])*del);
}
for (i=1; i < sum; i++){
 if (!vis){
  dy[i]=-100;
  dx[i]=-100;
 }
 else{
  zy[i]=Math.round(dy[i]+=(zy[i-1]-dy[i])*del);
  zx[i]=Math.round(dx[i]+=(zx[i-1]-dx[i])*del);
 }
if (dy[i-1] >= winH-80) dy[i-1]=winH-80;
if (dx[i-1] >= winW-buffW) dx[i-1]=winW-buffW;
}

tmr=setTimeout('Delay()',ref);
ClockAndAssign();
}
window.onload=Delay;
}
//-->
</script>
</body>

</html>

SQL SERVER – Restore Database Backup using SQL Script (T-SQL)

Step 1: Retrive the Logical file name of the database from backup.

RESTORE FILELISTONLY
FROM DISK = 'D:BackUpYourBaackUpFile.bak'
GO

Step 2: Use the values in the LogicalName Column in following Step.

----Make Database to single user Mode


ALTER DATABASE YourDB
SET SINGLE_USER WITH
ROLLBACK IMMEDIATE




----Restore Database


RESTORE DATABASE YourDB
FROM DISK = 'D:BackUpYourBaackUpFile.bak'
WITH MOVE 'YourMDFLogicalName' TO 'D:DataYourMDFFile.mdf',
MOVE 'YourLDFLogicalName' TO 'D:DataYourLDFFile.ldf'

/*If there is no error in statement before database will be in multiuser
mode.
If error occurs please execute following command it will convert
database in multi user.*/

ALTER DATABASE YourDB SET MULTI_USER
GO