Script to find Estimated Finish Time of The Backup Database

Here is the Script to find Estimated Finish Time of The Backup Database:

Note: This will work when you had initiated process of taking BackUp.

SELECT
session_id
,percent_complete AS CompletedPercent
,DATEADD(MILLISECOND,estimated_completion_time,CURRENT_TIMESTAMP) AS EstimatedFinishTime
,(total_elapsed_time/1000)/60 AS TotalElapsedTimeInMIN
,DB_NAME(Database_id) AS DatabaseName
,command
,sql_handle
FROM sys.dm_exec_requests
WHERE command LIKE '%BACKUP DATABASE%'


Happy Coding !!!

Generate script of All the indexes in a SQL Server database

Here is the script to Find all indexes in a database

IF NOT EXISTS(SELECT NULL
                FROM INFORMATION_SCHEMA.SCHEMATA
               WHERE SCHEMA_NAME = 'utils'
                 AND SCHEMA_OWNER = 'dbo')
BEGIN
    EXEC('CREATE SCHEMA utils AUTHORIZATION dbo')
END
GO

IF NOT EXISTS(SELECT NULL
                FROM INFORMATION_SCHEMA.ROUTINES
               WHERE ROUTINE_NAME = 'GenerateIndexesScript'
                 AND ROUTINE_TYPE = N'PROCEDURE')
BEGIN
    EXEC ('CREATE PROCEDURE [utils].[GenerateIndexesScript] AS BEGIN SELECT 1 END')
END
GO

/*
Adapted from http://www.sqlservercentral.com/Forums/Topic401784-562-2.aspx (by Jonathan AC Roberts. )

Modifications 10/06/2010 By R.Doering - http://sqlsolace.blogspot.com

        1) Changed Schema of routine to Utils
        2) Changed Name from INFGenerateIndexesScript to GenerateIndexesScript
        3) Added Schemas to script
        4) Reformatted for clarity

--  Usage: EXEC utils.GenerateIndexesScript 1, 0, 0
Sample call utils.GenerateIndexesScript
*/
ALTER PROCEDURE utils.GenerateIndexesScript
(
    @IncludeFileGroup  bit = 1,
    @IncludeDrop       bit = 1,
    @IncludeFillFactor bit = 1
)
AS

BEGIN
    -- Get all existing indexes, but NOT the primary keys
    DECLARE Indexes_cursor CURSOR
        FOR SELECT SC.Name          AS      SchemaName,
                   SO.Name          AS      TableName,
                   SI.OBJECT_ID     AS      TableId,
                   SI.[Name]         AS  IndexName,
                   SI.Index_ID       AS  IndexId,
                   FG.[Name]       AS FileGroupName,
                   CASE WHEN SI.Fill_Factor = 0 THEN 100 ELSE SI.Fill_Factor END  Fill_Factor
              FROM sys.indexes SI
              LEFT JOIN sys.filegroups FG
                     ON SI.data_space_id = FG.data_space_id
              INNER JOIN sys.objects SO
                      ON SI.OBJECT_ID = SO.OBJECT_ID
              INNER JOIN sys.schemas SC
                      ON SC.schema_id = SO.schema_id
             WHERE OBJECTPROPERTY(SI.OBJECT_ID, 'IsUserTable') = 1
               AND SI.[Name] IS NOT NULL
               AND SI.is_primary_key = 0
               AND SI.is_unique_constraint = 0
               AND INDEXPROPERTY(SI.OBJECT_ID, SI.[Name], 'IsStatistics') = 0
             ORDER BY OBJECT_NAME(SI.OBJECT_ID), SI.Index_ID

    DECLARE @SchemaName     sysname
    DECLARE @TableName      sysname
    DECLARE @TableId        int
    DECLARE @IndexName      sysname
    DECLARE @FileGroupName  sysname
    DECLARE @IndexId        int
    DECLARE @FillFactor     int

    DECLARE @NewLine nvarchar(4000)
    SET @NewLine = char(13) + char(10)
    DECLARE @Tab  nvarchar(4000)
    SET @Tab = SPACE(4)

    -- Loop through all indexes
    OPEN Indexes_cursor

    FETCH NEXT
     FROM Indexes_cursor
     INTO @SchemaName, @TableName, @TableId, @IndexName, @IndexId, @FileGroupName, @FillFactor

    WHILE (@@FETCH_STATUS = 0)
        BEGIN

            DECLARE @sIndexDesc nvarchar(4000)
            DECLARE @sCreateSql nvarchar(4000)
            DECLARE @sDropSql           nvarchar(4000)

            SET @sIndexDesc = '-- Index ' + @IndexName + ' on table ' + @TableName
            SET @sDropSql = 'IF EXISTS(SELECT 1' + @NewLine
                          + '            FROM sysindexes si' + @NewLine
                          + '            INNER JOIN sysobjects so' + @NewLine
                          + '                   ON so.id = si.id' + @NewLine
                          + '           WHERE si.[Name] = N''' + @IndexName + ''' -- Index Name' + @NewLine
                          + '             AND so.[Name] = N''' + @TableName + ''')  -- Table Name' + @NewLine
                          + 'BEGIN' + @NewLine
                          + '    DROP INDEX [' + @IndexName + '] ON [' + @SchemaName + '].[' + @TableName + ']' + @NewLine
                          + 'END' + @NewLine

            SET @sCreateSql = 'CREATE '

            -- Check if the index is unique
            IF (IndexProperty(@TableId, @IndexName, 'IsUnique') = 1)
                BEGIN
                    SET @sCreateSql = @sCreateSql + 'UNIQUE '
                END
            --END IF
            -- Check if the index is clustered
            IF (IndexProperty(@TableId, @IndexName, 'IsClustered') = 1)
                BEGIN
                    SET @sCreateSql = @sCreateSql + 'CLUSTERED '
                END
            --END IF

            SET @sCreateSql = @sCreateSql + 'INDEX [' + @IndexName + '] ON [' + @SchemaName + '].[' + @TableName + ']' + @NewLine + '(' + @NewLine

            -- Get all columns of the index
            DECLARE IndexColumns_cursor CURSOR
                FOR SELECT SC.[Name],
                           IC.[is_included_column],
                           IC.is_descending_key
                      FROM sys.index_columns IC
                     INNER JOIN sys.columns SC
                             ON IC.OBJECT_ID = SC.OBJECT_ID
                            AND IC.Column_ID = SC.Column_ID
                     WHERE IC.OBJECT_ID = @TableId
                       AND Index_ID = @IndexId
                     ORDER BY IC.[is_included_column],
                              IC.key_ordinal

            DECLARE @IxColumn      sysname
            DECLARE @IxIncl        bit
            DECLARE @Desc          bit
            DECLARE @IxIsIncl      bit
            SET @IxIsIncl = 0
            DECLARE @IxFirstColumn   bit
            SET @IxFirstColumn = 1

            -- Loop through all columns of the index and append them to the CREATE statement
            OPEN IndexColumns_cursor
            FETCH NEXT
             FROM IndexColumns_cursor
             INTO @IxColumn, @IxIncl, @Desc

            WHILE (@@FETCH_STATUS = 0)
                BEGIN
                    IF (@IxFirstColumn = 1)
                        BEGIN
                            SET @IxFirstColumn = 0
                        END
                    ELSE
                        BEGIN
                            --check to see if it's an included column
                            IF (@IxIsIncl = 0) AND (@IxIncl = 1)
                                BEGIN
                                    SET @IxIsIncl = 1
                                    SET @sCreateSql = @sCreateSql + @NewLine + ')' + @NewLine + 'INCLUDE' + @NewLine + '(' + @NewLine
                                END
                            ELSE
                                BEGIN
                                    SET @sCreateSql = @sCreateSql + ',' + @NewLine
                                END
                            --END IF
                        END
                    --END IF

                    SET @sCreateSql = @sCreateSql + @Tab + '[' + @IxColumn + ']'
                    -- check if ASC or DESC
                    IF @IxIsIncl = 0
                        BEGIN
                            IF @Desc = 1
                                BEGIN
                                    SET @sCreateSql = @sCreateSql + ' DESC'
                                END
                            ELSE
                                BEGIN
                                    SET @sCreateSql = @sCreateSql + ' ASC'
                                END
                            --END IF
                        END
                    --END IF
                    FETCH NEXT
                     FROM IndexColumns_cursor
                     INTO @IxColumn, @IxIncl, @Desc
                END
            --END WHILE
            CLOSE IndexColumns_cursor
            DEALLOCATE IndexColumns_cursor

            SET @sCreateSql = @sCreateSql + @NewLine + ') '

            IF @IncludeFillFactor = 1
                BEGIN
                    SET @sCreateSql = @sCreateSql + @NewLine + 'WITH (FillFactor = ' + CAST(@FillFactor AS varchar(13)) + ')' + @NewLine
                END
            --END IF

            IF @IncludeFileGroup = 1
                BEGIN
                    SET @sCreateSql = @sCreateSql + 'ON ['+ @FileGroupName + ']' + @NewLine
                END
            ELSE
                BEGIN
                    SET @sCreateSql = @sCreateSql + @NewLine
                END
            --END IF

            PRINT '-- **********************************************************************'
            PRINT @sIndexDesc
            PRINT '-- **********************************************************************'

            IF @IncludeDrop = 1
                BEGIN
                    PRINT @sDropSql
                    PRINT 'GO'
                END
            --END IF

            PRINT @sCreateSql
            PRINT 'GO' + @NewLine  + @NewLine

            FETCH NEXT
             FROM Indexes_cursor
             INTO @SchemaName, @TableName, @TableId, @IndexName, @IndexId, @FileGroupName, @FillFactor
        END
    --END WHILE
    CLOSE Indexes_cursor
    DEALLOCATE Indexes_cursor

END
GO


Happy Coding !!!

10 ways to Bind Multiple Models on a View in MVC

we will see below 10 ways to bind multiple models on a single view

1. View Model

2. View Bag

3. View Data

4. Temp Data

5. Session

6. Dynamic

7. Tuples

8. Render Action

9. JSON

10. Navigation Properties

Refer:

https://www.codeproject.com/Articles/1108855/ways-to-Bind-Multiple-Models-on-a-View-in-MVC

ASP.NET Web API facebook authentication

To use Facebook account for authentication, we will have to first register our application with Facebook. Here are the steps to register your application with Facebook. Once we successfully register our application, we will be given a Client ID and Client Secret. We need both of these for using Facebook authentication with our Web API service.

Step 1 : To register your application go to
https://developers.facebook.com/

Step 2 : Login with your Facebook account.
Click "Create App" button
Provide a name for the App in the "Display Name" textbox. I named it TestApp
Provide your email in the "Contact Email" textbox
Select a Category. I selected "Education"
Finally, click "Create App ID" button

Step 3 : Next we need to make the App that we just created public. To make the App, public, Click on "App Review" menu item on the left and then click the Yes|No button.
You also need to give Live URL for Privacy Policy URL.

Step 4 : Next we need to add "Facebook Login" product to our App. To do this click "Add Product" menu item on the left and click "Get Started" button against "Facebook Login" 

Step 5 : In "Valid OAuth redirect URIs" textbox, include the URI of your application. I have my web api application running at http://localhost:61358

Step 6 : Click "Dashboard" menu item on the left and you will see App ID and App Secret.

Enable Facebook OAuth authentication in ASP.NET Web API service

Step 1 : Facebook has made a breaking change to it's API in version 2.4.  The change is, in addition to the access token we also have to send the fields that we want in URI. In version 2.3 and earlier, the URI used to be as shown below. The access token is appended to the URI using a ?
https://graph.facebook.com/v2.3/me?access_token=ABC

With version 2.4, they have changed it to as shown below. Instead of using ? we have to use & to attach the access token to the URI
https://graph.facebook.com/v2.4/me?fields=id,email&access_token=ABC

The defualt implementation for the Facebook Client, that is provided by Microsoft attaches the access token to the URI using a ?, so we have to change it to &. To do that,
Add a folder to the project. Name it "Facebook"
Add a class file to the folder. Name it FacebookBackChannelHandler.cs


using System;
using System.Net.Http;
using System.Threading;
using System.Threading.Tasks;

namespace EmployeeService.Facebook
{
    public class FacebookBackChannelHandler : HttpClientHandler
    {
        protected override async Task<HttpResponseMessage>
            SendAsync(HttpRequestMessage request,
                      CancellationToken cancellationToken)
        {
            if (!request.RequestUri.AbsolutePath.Contains("/oauth"))
            {
                request.RequestUri = new Uri(
                    request.RequestUri.AbsoluteUri.Replace("?access_token", "&access_token"));
            }
            return await base.SendAsync(request, cancellationToken);
        }
    }
}

Step 2 : In Startup.Auth.cs file in App_Start folder include the following code block. Use the AppID and AppSecret that we got after registering our application with Facebook.

var facebookOptions = new FacebookAuthenticationOptions()
{
    AppId = "160811734413146",
    AppSecret = "21f2665e0aed11867fcd8d35e67d6068",
    BackchannelHttpHandler = new FacebookBackChannelHandler(),
    UserInformationEndpoint = "https://graph.facebook.com/v2.4/me?fields=id,email"
};

facebookOptions.Scope.Add("email");
app.UseFacebookAuthentication(facebookOptions);

Step 3 : On Login.html page, include the HTML for "Login with Facebook" button.

<tr>
    <td>
        <input type="button" id="btnFacebookLogin"
                value="Login with Facebook" class="btn btn-success" />
    </td>
</tr>

Step 4 : Wire up the click event handler for "Login with Facebook" button

$('#btnFacebookLogin').click(function () {
    window.location.href = "/api/Account/ExternalLogin?provider=Facebook&response_type=token&client_id=self&redirect_uri=http%3a%2f%2flocalhost%3a61358%2fLogin.html&state=GerGr5JlYx4t_KpsK57GFSxVueteyBunu02xJTak5m01";

});

Step 5 : In GoogleAuthentication.js file, modify isUserRegistered() and signupExternalUser() functions as shown below. I have included comments where the code is modified, so you know what has changed.

function isUserRegistered(accessToken) {
    $.ajax({
        url: '/api/Account/UserInfo',
        method: 'GET',
        headers: {
            'content-type': 'application/json',
            'Authorization': 'Bearer ' + accessToken
        },
        success: function (response) {
            if (response.HasRegistered) {
                localStorage.setItem("accessToken", accessToken);
                localStorage.setItem("userName", response.Email);
                window.location.href = "Data.html";
            }
            else {
                // Pass the login provider (Facebook or Google)
                signupExternalUser(accessToken, response.LoginProvider);
            }
        }
    });
}


// Include provider parameter
function signupExternalUser(accessToken, provider) {
    $.ajax({
        url: '/api/Account/RegisterExternal',
        method: 'POST',
        headers: {
            'content-type': 'application/json',
            'Authorization': 'Bearer ' + accessToken
        },
        success: function () {
            // Use the provider parameter value instead of
            // hardcoding the provider name
            window.location.href = "/api/Account/ExternalLogin?provider=" + provider + "&response_type=token&client_id=self&redirect_uri=http%3a%2f%2flocalhost%3a61358%2fLogin.html&state=GerGr5JlYx4t_KpsK57GFSxVueteyBunu02xJTak5m01";
        }
    });
}

Happy Coding !!!

ASP.NET Web API google authentication

Register our application with Google:

Step 1 : To register your application go to
https://console.developers.google.com

Step 2 : Login with your GMAIL account.Click on Credentials link on the left, and then create a new project, by clicking on "Create Project" button.

Step 3 : Name your project "Test Project" and click "CREATE" button.

Step 4 : The new project will be created. Click on "OAuth consent screen". In the "Product name shown to users" textbox type "Test Project" and click "Save" button

Step 5 : The changes will be saved and you will be redirected to "Credentials" tab. If you are not redirected automatically, click on the "Credentials" tab and you will see "Create Credentials" dropdown button. Click on the button, and select "OAuth client ID" option

Step 6 : On the next screen,

• Select "Web application" radio button. 
• Type "Web client 1" in the "Name" textbox.
• In the "Authorized JavaScript origins" textbox type in the URI of your application. I have my web api application running at http://localhost:61358
• In the "Authorized redirect URIs" textbox type in the redirect URI i.e the path in our application that users are redirected to after they have authenticated with Google. I have set it to http://localhost:61358/signin-google
• Click the "Create" button

Step 7 : Enable Google+ API service. To do this click on "Library" link on the left hand pane.Under "Social APIs" click on "Google+ API" link and click "Enable" button.

Enable Google OAuth authentication in ASP.NET Web API service

Step 1 : In Startup.Auth.cs file in App_Start folder un-comment the following code block, and include ClientId and ClientSecret that we got after registering our application with Google.

app.UseGoogleAuthentication(new GoogleOAuth2AuthenticationOptions()

{

    ClientId = "Your Google Client Id",

    ClientSecret = "Your Google Client Secret"

});

Step 2 : In Login.html page include the following HTML table, just below "Existing User Login" table

<table class="table table-bordered">

    <thead>

        <tr class="success">

            <th>

                Social Logins

            </th>

        </tr>

    </thead>

    <tbody>

        <tr>

            <td>

                <input type="button" id="btnGoogleLogin"

                        value="Login with Google" class="btn btn-success" />

            </td>

        </tr>

    </tbody>

</table>

Step 3 : In the script section, in "Login.html" page, wire up the click event handler for "Login with Google" button. 

$('#btnGoogleLogin').click(function () {

    window.location.href = "/api/Account/ExternalLogin?provider=Google&response_type=token&client_id=self&redirect_uri=http%3a%2f%2flocalhost%3a61358%2fLogin.html&state=GerGr5JlYx4t_KpsK57GFSxVueteyBunu02xJTak5m01";

});

Notice when we click the button we are redirecting the user to /api/Account/ExternalLogin. 

The obvious question that we get at this point is from where do we get this URL. To get this URL, issue a GET request to api/Account/ExternalLogins?returnUrl=%2F&generateState=true. Since in my case the application is running at http://localhost:61358, the complete URL is http://localhost:61358/api/Account/ExternalLogins?returnUrl=%2F&generateState=true. 

Step 4 : Open "ApplicationOAuthProvider.cs" file from "Providers" folder, and modify ValidateClientRedirectUri() method as shown below. The change is to set the Redirect URI to Login.html

public override Task ValidateClientRedirectUri

    (OAuthValidateClientRedirectUriContext context)

{

    if (context.ClientId == _publicClientId)

    {

        Uri expectedRootUri = new Uri(context.Request.Uri, "/Login.html");

        if (expectedRootUri.AbsoluteUri == context.RedirectUri)

        {

            context.Validated();

        }

    }

    return Task.FromResult<object>(null);

}

Step 5 : At this point build the solution and navigate to Login.html. Click on "Login with Google" button. Notice we are redirected to "Google" login page. Once we provide our Google credentials and successfully login, we are redirected to our application Login.html page with access token appended to the URL.

http://localhost:61358/Login.html#access_token=Pwf1kU_LkrdueJbnaDtZohLsUHMDBvrYrdMxL59c4pilUC0&token_type=bearer&expires_in=1209600&state=GerGr5JlYx4t_KpsK57GFSxVueteyBunu02xJTak5m01

Step 6 : Next we need to retrieve the access token from the URL. The following JavaScript function does this. Add a new JavaScript file to the Scripts folder. Name it GoogleAuthentication.js. Reference jQuery. You can find minified jQuery file in the scripts folder. Copy and and paste the following function in it. Notice we named the function getAccessToken(). 

function getAccessToken() {

    if (location.hash) {

        if (location.hash.split('access_token=')) {

            var accessToken = location.hash.split('access_token=')[1].split('&')[0];

            if (accessToken) {

                isUserRegistered(accessToken);

            }

        }

    }

}

Step 7 : Notice the above function calls isUserRegistered() JavaScript function which checks if the user is already registered with our application. isUserRegistered() function is shown below. To check if the user is registered we issue a GET request to /api/Account/UserInfo passing it the access token using Authorization header. If the user is already registered with our application, we store the access token in local storage and redirect the user to our protected page which is Data.html. If the user is not registered, we call a different JavaScript function - signupExternalUser(). We will discuss what signupExternalUser() function does in just a bit. Now copy and paste the following function also in GoogleAuthentication.js file.

function isUserRegistered(accessToken) {

    $.ajax({

        url: '/api/Account/UserInfo',

        method: 'GET',

        headers: {

            'content-type': 'application/JSON',

            'Authorization' : 'Bearer ' + accessToken

        },

        success: function (response) {

            if (response.HasRegistered) {

                localStorage.setItem('accessToken', accessToken);

                localStorage.setItem('userName', response.Email);

                window.location.href = "Data.html";

            }

            else {

                signupExternalUser(accessToken);

            }

        }

    });

}

Step 8 : If the Google authenticated user is not already registered with our application, we need to register him. This is done by signupExternalUser() function show below. To register the user with our application we issue a POST request to /api/Account/RegisterExternal, passing it the access token. Once the user is successfully registered, we redirect him again to the same URL, to which the user is redirected when we clicked the "Login with Google" button. Since the user is already authenticated by Google, the access token will be appended to the URL, which will be parsed by getAccessToken() JavaScript function. getAccessToken() function will again call isUserRegistered() function. Since the user is already registered with our application, we redirect him to the Data.html page and he will be able to see the employees data. Copy and paste the following function also in GoogleAuthentication.js file.

function signupExternalUser(accessToken) {

    $.ajax({

        url: '/api/Account/RegisterExternal',

        method: 'POST',

        headers: {

            'content-type': 'application/json',

            'Authorization': 'Bearer ' + accessToken

        },

        success: function () {

            window.location.href = "/api/Account/ExternalLogin?provider=Google&response_type=token&client_id=self&redirect_uri=http%3a%2f%2flocalhost%3a61358%2fLogin.html&state=GerGr5JlYx4t_KpsK57GFSxVueteyBunu02xJTak5m01";

        }

    });

}

Step 9 : In AccountController.cs, modify RegisterExternal() method as shown below. Notice we removed "RegisterExternalBindingModel" parameter and if (!ModelState.IsValid) code block.

// POST api/Account/RegisterExternal

[OverrideAuthentication]

[HostAuthentication(DefaultAuthenticationTypes.ExternalBearer)]

[Route("RegisterExternal")]

public async Task<IHttpActionResult> RegisterExternal()

{

    var info = await Authentication.GetExternalLoginInfoAsync();

    if (info == null)

    {

        return InternalServerError();

    }

    var user = new ApplicationUser() { UserName = info.Email, Email = info.Email };

    IdentityResult result = await UserManager.CreateAsync(user);

    if (!result.Succeeded)

    {

        return GetErrorResult(result);

    }

    result = await UserManager.AddLoginAsync(user.Id, info.Login);

    if (!result.Succeeded)

    {

        return GetErrorResult(result);

    }

    return Ok();

}

Step 10 : Finally, on Login.html page reference GoogleAuthentication.js file and call get getAccessToken() function

Build the solution and navigate to Login.html page and click on "Login with Google" button. Notice we are redirected to Google Login page. Once we provide our Google credentials and Login, we are redirected to Data.html page. When we click "Load Employees" button we see employees data.

At this point if you query AspNetUsers and AspNetUserLogins tables you will see an entry for your login is made into these 2 tables

Select * from AspNetUsers

Select * from AspNetUserLogins

Happy Coding !!!