Database is restoring for a long time no idea what is going on

 Database is restoring for a long time no idea what is going on

Try this

use master

RESTORE DATABASE [you database name] WITH RECOVERY;

Cannot drop database because it is currently in use

 Try find the lock or SPId using the database  using the following command

SP_WHO 

Then run

 kill <SPID>;

example kill 60; to kill the process.

Or the folling script also generate the kill script for the database 

DECLARE @DatabaseName nvarchar(50)

SET @DatabaseName = N'Your database name'

DECLARE @SQL varchar(max)

SELECT @SQL = COALESCE(@SQL,'') + 'Kill ' + Convert(varchar, SPId) + ';'

FROM MASTER..SysProcesses

WHERE DBId = DB_ID(@DatabaseName) AND SPId <> @@SPId

SELECT @SQL ;

The database could not be exclusively locked to perform the operation. While renaming database

 

The database could not be exclusively locked to perform the operation. (Microsoft SQL Server, Error: 5030)

While renaming database :( 

/*You might have seen "The database could not be exclusively locked to perform the operation. (Microsoft SQL Server, Error: 5030)" error when you try to rename SQL server database.  This error normally occurs when your database is in Multi User mode where users are accessing your database or some objects are referring to your database.

 Nothing much to do to resolve the issue. First set the database to single user mode and then try to rename the database and then set it back to Multi user mode./*

 --We will go through step by step.

--First we will see how to set the database to single user mode,

ALTER DATABASE OlddbName 
SET SINGLE_USER WITH ROLLBACK IMMEDIATE

--Now we will try to rename the database

ALTER DATABASE OlddbName MODIFY NAME = NewdbName


--Finally we will set the database to Multiuser mode


ALTER DATABASE NewdbName
SET MULTI_USER WITH ROLLBACK IMMEDIATE
 

--Hope you are able to rename your database without any issues now!!!

 

Kendo Serverside filters with ajax and mvc

Kendo Serverside filters with ajax and mvc 

why? when we don't want to load millions on records on in grid at once, so we need paging , 

but for filters/sorting we need to filter among the million records and show only pageSize records.

Kendo says to use the DataSourceRequest as below

public ActionResult Paging_Orders([DataSourceRequest] DataSourceRequest request)
{
    return Json(GetOrders().ToDataSourceResult(request));
}

but for multiple filters you may get the following errors

Solution

create a class dataSourceHelper.cs

  public class DataSourceHelper
    {
        public static DataSourceRequest GetDataSourceRequest(string filter, string sort,  string page, string pageSize)
        {
            DataSourceRequest request = new DataSourceRequest()
            {
                Page = Convert.ToInt32(page),
                PageSize = Convert.ToInt32(pageSize)
            };
            if (page == null)
                page = "1";
            if (pageSize == null)
                pageSize = "25";
            if (request.Filters == null && !string.IsNullOrEmpty(filter))
            {
                request.Filters = new List<Kendo.Mvc.IFilterDescriptor>();
                request.Filters.AddRange(getFilter(filter));
            }
            if (request.Sorts == null && !string.IsNullOrEmpty(sort))
            {
                request.Sorts = new List<Kendo.Mvc.SortDescriptor>();
                request.Sorts.AddRange(getSorts(sort));
            }
            return request;
        }
        private static IEnumerable<SortDescriptor> getSorts(string sort)
        {
            List<SortDescriptor> list = new List<SortDescriptor>();
            string[] sorts = sort.Split(new[] { "-and-" }, StringSplitOptions.None);
            foreach (string s in sorts)
            {
                string[] x = s.Split('-');
                if (x.Length == 2)
                    list.Add(new SortDescriptor() { Member = x[0], SortDirection = getDirection(x[1]) });
            }
            return list;
        }
        private static ListSortDirection getDirection(string dir)
        {
            switch (dir)
            {
                case "asc":
                    return ListSortDirection.Ascending;
                default:
                    return ListSortDirection.Descending;
            }
        }
        private static IEnumerable<IFilterDescriptor> getFilter(string filter)
        {
            List<IFilterDescriptor> list = new List<IFilterDescriptor>();
            string[] filters = filter.Split(new[] { "~and~" }, StringSplitOptions.None);
            foreach (string f in filters)
            {
                string[] x = f.Split('~');
                list.Add(new FilterDescriptor() { Member = x[0], MemberType = typeof(string), Operator = getFilterOperator(x[1]), Value = x[2].Trim(new char[] { (char)39 }) });
            }
            return list;
        }
        private static FilterOperator getFilterOperator(string str)
        {
            switch (str)
            {
                case "startswith":
                    return FilterOperator.StartsWith;
                case "eq":
                    return FilterOperator.IsEqualTo;
                case "neq":
                    return FilterOperator.IsNotEqualTo;
                default:
                    return FilterOperator.Contains;
            }
        }
    }

Also do not forget the reference dll using tags

using Kendo.Mvc;
using Kendo.Mvc.Extensions;
using Kendo.Mvc.UI;
using System;
using System.Collections.Generic;
using System.ComponentModel;

Usages

you can call the below datasource and bind it to your grid.

    var dataSource= new kendo.data.DataSource({

        type: "webapi",
        dataType: "json",        
        serverPaging: true,
        serverSorting: true,
        serverFiltering: true,
        pageSize: 500,
        transport: {
            read: {
                url: home + "/Action/GetDataMethod"
            }
        },
        schema: {
            total: "Total",
            data: "Data"
            
        }
        
    });

Controller

 public JsonResult GetDataMethod( string filter,string sort, string page, string pageSize)
        {
            DataSourceRequest request = new DataSourceRequest();
            request = DataSourceHelper.GetDataSourceRequest(filter, sort, page, pageSize);
           						
            var result = BusinessLogic.GetData();//your logic to get millions of data
            if (result != null)
            {
                var filterResult = result.Data.ToDataSourceResult(request);
                request.Page = 1;
                request.PageSize = result.Total;
                result.Total= (int)result.WorkQueueItems.ToDataSourceResult(request).Total;
                result.Data= (IEnumerable<DataView>)filterResult.Data;
            }
            return  Json(result, Constants.ResultTypes.ApplicationJson, System.Text.Encoding.Unicode, JsonRequestBehavior.AllowGet);
        } 

Note: Total is required to construct the paging section.

PL SQL Constraints with conditions

TABLE_NAME.category_Type ='ABC,DEF,GEF'

ANOTHER_TABLE.Value have ABC,DEF,GEF in different rows

I created a trigger to check each comma separated values should belong to ANOTHER_TABLE.

CREATE OR REPLACE  TRIGGER TRIGGER_NAME
 BEFORE  INSERT or UPDATE ON  TABLE_NAME
 REFERENCING OLD AS old    NEW AS new
FOR EACH ROW
BEGIN
DECLARE
  num_rows integer;
  dup_rows integer;
  temptype nvarchar2(100);
BEGIN
--  Check each comma separated values are from the ANOTHER_TABLE table where category ='Type'     
--  else throw error
select TO_CHAR( regexp_replace(TO_CHAR(:NEW.category_Type), '[[:space:]]*','')) into temptype from dual;

FOR i IN
   (SELECT trim(regexp_substr(temptype , '[^,]+', 1, LEVEL)) l
     FROM dual
       CONNECT BY LEVEL <= regexp_count(temptype , ',')+1
     )
   LOOP
 
SELECT
     count(1) into num_rows       
FROM   ANOTHER_TABLE where category ='Type' and value=i.l;

if(num_rows=0) then
      RAISE_APPLICATION_ERROR(-20000,'type did not match with expected ANOTHER_TABLE.value');
      end if;

    END LOOP;

  END;
END;

Kendo UI Multiselect with Boolean Checkbox

Kendo UI Multiselect 

 <select id="ddlid" name="ddlname" required data-required-msg="Select ddlname"></select>

DataSource

My datasource returns a selectList
  var selectList = new List<SelectListItem>();
with Text,Value and Selected

Boolean checkbox

 var multi = ddlContainer.kendoMultiSelect({
                            dataSource: dataSource,
                            dataTextField: "Text",
                            dataValueField: "Value",
                            placeholder: "Select items",
                            tagMode: "single",
                            autoBind: "false",
                            headerTemplate: '<div class="dropdown-header k-widget k-header">' +
                                '<span>Is Active </span>' +
                                '<span> -  Items</span>' +
                                '</div>',
                            footerTemplate: 'Total #: instance.dataSource.total() # items found',
                            itemTemplate: '<span class="k-state-default"> <input type="checkbox" #= Selected ? "checked=checked" : "" # disabled="disabled" ></input> </span> ' +                         
                                '<span class="k-state-default">#: data.Text #</span>',                         
                            autoClose: false                         
                        });


                        // Get reference to the validator
                        var validator = $("#ddlid").kendoValidator().data("kendoValidator");

                        // Bind validation to blur
                        $("input", multi.wrapper).on("blur", function () {
                            if (validator != undefined)
                                validator.validate();
                        });

Output

How to install Oracle 12 C and set up oracle provider for dot net

steps 1: 

1. First step Install/Update latest Visual studio, upto VS 2017
2. Delete all old oracle clients
3. Delete oracle providers for dotnet
4. Remove regedit 
   
5. restart system.

Step2: 

download the ODT client from following link

https://www.oracle.com/database/technologies/dotnet-utilsoft-downloads.html

ODAC 12.2c Release 1 and Oracle Developer Tools for Visual Studio (12.2.0.1.1)

ODTwithODAC122011.zip411 MB (431,571,252 bytes)

Extract the zip in "c/temp" folder and hit Setup.exe, follow every thing as default, you can select the base serectory as short as possible in c-drive Mine is c:/Oracle

The installation will add oracle base path and bin path to system variables .

But check for TNS_ADMIN variable in system variables, and the value should be %oracle base%/Network\Admin\

you can change as you like 

Mine TNS_ADMIN  is c:/Orcle/TNS

Where I have my sqlnet.ora and tnsnames.ora files

STEP-3
Set up Windows Features

Step 4
Set up IIS,
Create both web and webservice ,

Common issue after oracle client installation

Network Naming: No LDAP server detected or configured.

Add the following file to TNS_ADMIN path 

ldap.ora

--not for OLDP---

Unable to Access to the path 'C:\Oracle\TNS\sqlnet.ora' is denied.

well i fixed it by creating all file under my C:\Oracle\TNS accessable to everyone 😛