SQL Query: find the specific number from the number series stored as record in a Column from the DB Table

//User Defined Function to Split the number series into temp results data

Create FUNCTION [dbo].[Split] (@sep char(1), @s varchar(5000))

RETURNS table

AS

RETURN (

    WITH Pieces(pn, start, stop) AS (

      SELECT 1, 1, CHARINDEX(@sep, @s)

      UNION ALL

      SELECT pn + 1, stop + 1, CHARINDEX(@sep, @s, stop + 1)

      FROM Pieces

      WHERE stop > 0

    )

    SELECT pn AS SerialNumber,

      SUBSTRING(@s, start, CASE WHEN stop > 0 THEN stop-start ELSE 5000 END) AS IDValue

    FROM Pieces

  )

Declare @NumberToFind int= 14

Select T.Id From Table T Where ISNULL(T.ConcatStringColumn ,'') != ''

And Exists (Select IDValue From dbo.Split(',',T.ConcatStringColumn ) Where IDValue =@NumberToFind)

Output:

'Some ID Here If specific number finds from that string'

Convert String to Title Case (Proper Case) in .Net C#

C# Code (by default, English Culture):

 

CultureInfo culinfo = Thread.CurrentThread.CurrentCulture;

TextInfo txtinfo = culinfo.TextInfo;

lblResult.Text = txtinfo.ToTitleCase("AMIT JAIN");


OutPut: 
Amit Jain

Update incremental value in one colum with one update statement in SQL

Declare @CorporateId int = 12, @SortOrder int
Set @SortOrder = 0
Update DashboardIndicators SET @SortOrder = SortOrder = @SortOrder + 1 Where CorporateId = @CorporateId

Update all of the null values to default values in SQL table

 SELECT  'update '+ so.name+' set '+sc.name+'= '''' where '+sc.name+' is null '  
   
            FROM sysobjects so
            JOIN syscolumns sc ON so.id = sc.id
            JOIN systypes st ON sc.xtype=st.xtype 
            where so.type = 'U'
            and st.name in('nvarchar','int')
            and so.name = 'TableName'

Case in where clause in SQL Server

Old Approach (will be messy in case of multiple columns in where clause)

If @Id = 0
    Select * From tbl
Else
    Select * From tbl Where Id = @Id




Good Approches:

-- Do the comparison, OR'd with a check on the @Country=0 case
Select * From tbl WHERE (Id = @Id OR @Id = 0)

-- compare the Country field to itself
Select * From tbl WHERE Id = CASE WHEN @Id > 0 THEN @Id ELSE Id END

Get the scripted Stored Procedures As Result of DB SQL Query in SQL Server.

select Definition
from
(
SELECT SM.Object_ID o, 1 ord, SM.Definition
FROM SYS.SQL_Modules As SM INNER JOIN SYS.Objects As Obj
ON SM.Object_ID = Obj.Object_ID WHERE Obj.Type = 'P'
union all
SELECT SM.Object_ID o, 2 ord, 'GO'
FROM SYS.SQL_Modules As SM INNER JOIN SYS.Objects As Obj
ON SM.Object_ID = Obj.Object_ID WHERE Obj.Type = 'P'
) a
order by o,ord

Back Button Issue after LogOff in .Net mvc

If User donot want to remove the cache at each web page  in the entire application and don't want to go back after logoff the system, then the following code will be good enough:

public ActionResult LogOff()
    {

        FormsAuthentication.SignOut();
        Session.Abandon();
        Session.Clear();

        // clear authentication cookie
        HttpCookie cookie1 = new HttpCookie(FormsAuthentication.FormsCookieName, "");
        cookie1.Expires = DateTime.Now.AddYears(-1);
        Response.Cookies.Add(cookie1);

        // clear session cookie (not necessary for your current problem but i would recommend you do it anyway)
        HttpCookie cookie2 = new HttpCookie("ASP.NET_SessionId", "");
        cookie2.Expires = DateTime.Now.AddYears(-1);
        Response.Cookies.Add(cookie2);

        // Invalidate the Cache on the Client Side
        Response.Cache.SetCacheability(HttpCacheability.NoCache);
        Response.Cache.SetExpires(DateTime.UtcNow.AddMinutes(-1));
        Response.Cache.SetNoStore();

        Response.AppendHeader("Pragma", "no-cache");

        // send an expired cookie back to the browser
        var ticketExpiration = DateTime.Now.AddDays(-7);
        var ticket = new FormsAuthenticationTicket(
            1,
            // replace with username if this is the wrong cookie name
            FormsAuthentication.FormsCookieName,
            DateTime.Now,
            ticketExpiration,
            false,
            String.Empty);
        var cookie = new System.Web.HttpCookie("user")
        {
            Expires = ticketExpiration,
            Value = FormsAuthentication.Encrypt(ticket),
            HttpOnly = true
        };

        Response.Cookies.Add(cookie);

        return RedirectToAction("Login", "Account");
    }


If User wants to remove caching the entire application, then the following code will be applied in the Global.asax.cs file:
protected void Application_BeginRequest()
{
    Response.Cache.SetCacheability(HttpCacheability.NoCache);
    Response.Cache.SetExpires(DateTime.UtcNow.AddHours(-1));
    Response.Cache.SetNoStore();
}

Add-On in VS 2012 for Multi-Line Text Replace

https://visualstudiogallery.msdn.microsoft.com/699CE302-B0D4-4083-BE0E-1682E873CEBF

Rename all files of folder with extension .cs using Powershell

dir | Rename-Item -NewName {[System.IO.Path]::GetFileNameWithoutExtension($_.Name) + "Ext.cs"}

Adding Columns dynamically in Razor MVC

@{
    var gridColumns = new List<WebGridColumn>();
    gridColumns.Add(grid.Column(format: (item) => Html.ActionLink("Select", "Details", 

 new { contractId = item.ContractId })));
    if (Model.ShowEdit)
    {
        gridColumns.Add(grid.Column(format: (item) => Html.ActionLink("Edit", "Edit",  

new { contractId = item.ContractId })));
    }
    gridColumns.Add(grid.Column("SignOffDate", "Sign Off Date", 

format: @<text> <span>@item.SignOffDate.ToString("d/M/yyyy")</span></text>));
    gridColumns.Add(grid.Column("FullContractNumber", "Contract Number"));
    gridColumns.Add(grid.Column("ContractTitle", "Title"));
}

@grid.GetHtml(columns: grid.Columns(gridColumns.ToArray()));

SQL Server: Link two Databases from two different SQL Servers

How to link Two SQL Servers

Execute the following system Stored Procedure to create new SQL Server that you want to link to: 

sp_addlinkedserver 'TestServer1','','SQLNCLI','192.199.121.12\SQLEXPRESS',null,NULL,NULL 

Now, by executing the first command, your linked server is created. But still you need to create the user to login to that linked server. To do this, execute the following system Stored Proc: 

exec sp_addlinkedsrvlogin ' TestServer1','false',null,'username','password' 

 Now, try to run the any query of the database connected to that linked server

E.g: Select * From TestServer1.DatabaseName.[dbo].tableName

Autocomplete Location Search using Google API in .Net Jquery

The following code is implemented in Razor MVC:

1. _TestView.cshtml
<script src="https://maps.google.com/maps/api/js?sensor=false&libraries=places" type="text/javascript"></script>
/*
Your JQuery Files
*/
<div>
/* Your View Code */
.
.
.
.
<input id="txtLocation" type="text" />
</div>



2. Jquery Code:
google.maps.event.addDomListener(window, 'load', initialize);
function initialize() {
    var input = document.getElementById('textbox_Location');
    var autocomplete = new google.maps.places.Autocomplete(input);


    google.maps.event.addListener(autocomplete, 'place_changed', function () {

        var place = autocomplete.getPlace();
        if (typeof (place.address_components) !== 'undefined') {
            //Saved location
            var placeName = place.name;
            var completeAddress = place.formatted_address;
            var latValue = place.geometry.location.lat();
            var lonValue = place.geometry.location.lng();
        }      
        return false;
    });
}