Sunday, July 7, 2013

How to restart a server from MSSQL SERVER using xp_cmdshell

Sometimes we need to restart a windows PC or server within or after an interval of time. We can do it easily using 'scheduled task' of windows. Here I‘ll discuss how to do this task from MS SQL SERVER. To do this we'll use a stored procedure ‘xp_cmdshell’ and an ms-dos command ‘shutdown’.

“xp_cmdshell” is a stored procedure which allows us to execute ms-dos commands from SQL Server.
xp_cmdshell {'command_string'} [, no_output]

command_string: command_string for operating system command (ms-dos command). The maximum length of this parameter is varchar(8000) or nvarchar(4000).
No_output: No_output Is an optional parameter. After executing the given command_string with No_output it does not return any output to the client.

Execute following script to enable xp_cmdshell:
use master
EXEC sp_configure 'show advanced options', 1
EXEC sp_configure 'xp_cmdshell', 1

Now we will use ‘shutdown -r -f -t 01’ as a command_string. 
Shutdown is a dos-command which is used to shut down, restart, log off or hibernate a computer.
 -r for restart, -f Forces running applications to close without warning, -t 01 for restart within 1 second.
Finally we will execute following sql procedure.
EXEC master..xp_cmdshell 'shutdown -r -f -t 01'

If it is required to restart a PC/server every day then we can use MS SQL SERVER JOB.
Read More

Wednesday, July 3, 2013

Some new structural tags in HTML5

Before HTML5, <div> elements were the only opportunity for defining block-level page structure. HTML5 introduced us with few important structural elements: header, footer, nav, article, section, aside and hgroup.  The new elements simply give us an element that carries more meaning, with which to replace the generic <div>.

structural tags in html5

Header: Header element is designed for the header area of a page or page section. The header element can contain h1-h6 heading tag and other elements as well. it can be nested within each <article> element of a single web page.
    <h1>Anupam Dutta</h1>
    <h2>(Web and Desktop Application Developer)</h2>
Footer: The footer tag of HTML5 usually contains information of a section or document. Basically author information, copyright data, links to related documents, social networking and sharing links are placed within the footer tag.
  <h3> Copyright 2013 Anupam Dutta</h3>
Nav: Nav element contains the navigation functionality of a page such as table of contents and menu. Nav can appear more than once in a web page.
      <li><a href="#HOME">HOME</a></li>
      <li><a href="#MVC">MVC</a></li>  
      <li><a href="#MSSQL">MSSQL</a></li>  
      <li><a href="#HTML5">HTML5</a></li>  
      <li><a href="#ABOUT">ABOUT</a></li>  
Section: The section element is similar to <div> as a general container which represents a standard section of a document, web page or application, such as a chapter, for example.  It has some semantic meaning. Section is a sectional element but <div> is not a sectional element. Section indicates a new section in a document.
<section id="MSSQL">
  <!-- multiple article elements could go in here -->
<section id="MVC">
  <!-- multiple article elements could go in here -->
Article: The article element represents an independent section of a document, page or site. It is appropriate for content like a magazine or newspaper article, blog articles, individual comments or forum posts, an interactive gadget or widget.
  <section id="MVC">
  <section id="MSSQL">
Aside: The aside element is for content that is tangentially related to another content item (such as a section or article), and is typically useful for marking up sidebars.
<h2>Related post</h2>
    <!-- lots of quick facts inside here -->
Hgroup: The hgroup element is used to group any set of h1 - h6 elements when the heading has multiple levels.
    <h1>Anupam Dutta</h1>
    <h2>(Web and Desktop Application Developer)</h2>

Read More

Thursday, June 27, 2013

Audio and Video in HTML5

<audio> and <video> tags of HTML5 allows us to incorporate native audio and video support without Flash.

To include audio and video we need to set src attribute to identify the media source and include a controls attribute so the user can play and pause the media.

The simplest way of embedding a video file in a webpage:

<video src="video.mp4" width="320" height="200" controls preload></video>

<audio src="audio.mp3" controls preload></audio>

We can use <source> tag to indicate media along with media type and many other attributes. A video/Audio element allows various source elements and browser will use the first recognized format:

<video width="618" height="347" controls preload> 
    <source src="video.mp4" media="only screen and (min-device-width: 568px)"></source>
    <source src="video.iphone.mp4" media="only screen and (max-device-width: 568px)"></source>

<audio controls preload> 
    <source src="audio.mp3"></source> 
    <source src="audio.ogg"></source> 

Attribute Specification:
Autoplay: The video/audio will automatically begin to play back as soon as it can do so without stopping to finish loading the data.
Autobuffer: The video/audio will automatically begin buffering even if it's not set to automatically play.
Controls: It will allow the user to control video/audio playback, including volume, seeking, and pause/resume playback.
Loop: It will allow video/audio automatically seek back to the start after reaching at the end.
Preload: This attribute specifies that the video/audio will be loaded at page load, and ready to run. Ignored if autoplay is present.
Src: The URL of the video/audio to embed.
Height: Height of the video's display area in pixels.
Width: Width of the video's display area in pixels.
Poster: URL of an image which will be displayed while the video is loading.

Read More

Friday, June 14, 2013

MSSQL Database Backup

Database backup is a very important thing if you don’t want to drop any of your valuable data that you are storing. Here, I’ll explain how to create MSSQL database backup.

Create a stored procedure from following script.
CREATE procedure prcDBBackup

declare @dbname varchar(100)
select @dbname = CONVERT(VARCHAR(10),GETDATE(),101)+CONVERT(VARCHAR(8),GETDATE(),108)
select @dbname = replace(replace(@dbname,'/',''),':','')
set @dbname = (select'E:\ERP_DB_BACKUP\ERP_DB_' + @dbname + '.BAK')
TO DISK = @dbname
Replace 'E:\ERP_DB_BACKUP\ERP_DB’ with your destination path.

Execute following sql command to create a backup manually.
exec prcDBBackup 
To create an automatic daily backup you can create a MS SQL SERVER JOB.
Read More

Thursday, May 23, 2013

How to access an Existing Database Using Entity Framework 5.0 Code-First

In my previous two articles we see how to use Entity Framework Code First and mvcscaffolding in mvc4. We didn't create any database into above articles. Database was created automatically from model class. But in this article I’ll explain how to use existing database in an EF code-first application. For this purpose we’ll make two small changes in any mvc code-first project.

1. Define connectionstring in web.config for existing database.

    <add name="DonarConnectionString" connectionString="Data Source=MSSQLSERVER;initial catalog=BloodDonar;user id=sa;password=;Integrated Security=SSPI;" providerName="System.Data.SqlClient" />

2. In previous two articles we have created a database context class which is inherited from “DbContext”. Now we need a constructor for this class. Notice, in this constructor, that we call into the base constructor and pass it a string.  This is the connectionstring which is created in previous step. EF will use it during database connections.

        public InventoryContext(): base("name=DonarConnectionString")

Here, we are setting the Initializer for the context to null with SetInitializer<>.  So, EF will not try to change any database initialization logic.
Read More

Monday, April 29, 2013 MVC Scaffolding in code first scenarios

In this article we will walk through the creation of a read/write/update/delete of Recipes and its related (many) Ingredients using MvcScaffolding and MVC 4.

MVC Scaffolding gives you a fast way to get some helpful, effective code, that you can then edit and adapt according to your needs.

In other words, MvcScaffolding  can create automatically:
1. Controllers for creating, viewing, updating and deleting instances of your model,
2. Corresponding Views for creating, viewing, updating, and deleting data,
3. Database contexts from your model,
4. Catalyze the creation of a database model including support for one-to-many relationships and more…

In this article we'll create a blood donar application using MVC Scaffolding and code first.

Let’s create two models for blood group and donar. Add the following classes to our Models folder, then compile the solution :


using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.ComponentModel;

namespace Blood_Donar.Models
 public class bloodgroup
public int bloodgroupid { get; set; }

[DisplayName ("Blood Group")]
public string name { get; set; }



using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.ComponentModel;
using System.ComponentModel.DataAnnotations;

namespace Blood_Donar.Models
public class donar
public int ID { get; set; }

[DisplayName ("Donar Name")]
[Required(ErrorMessage="Name is required")]
public string Name { get; set; }

public string Address { get; set; }

[Required(ErrorMessage="Mobile no is required.")]
public int Mobile { get; set; }

[DisplayName ("Blood Group")]
public int bloodgroupid { get; set; }

public virtual bloodgroup bloodgroups { get; set; }


Execute following command into Package Manager Console:
1. Install-Package EntityFramework
2. Install-Package mvcscaffolding

Next, we will create a complete Create-Read-Update-Delete (CRUD) UI for those models by executing a single command into Package Manager Console:

Scaffold Controller Donar

This command will create a database context,  a controller, and all the views for Create-Read-Update-Delete (CRUD) actions. Right now you can run it (Shift-F5).

If you have SQL Server Express running on your machine, EFCodeFirst will automatically connect to it, create your database schema, and you’ve got a basic working application without writing a single line of procedural code. Also you can add a SQL Server Compact to your project by issuing the following command in the Package Manager Console:

Install-Package EFCodeFirst.SqlServerCompact

Again, execute following command for BloodGroup model

Scaffold Controller BloodGroup

To create repositary class execute following command into package manager console

Scaffold Controller Donar –Repository -Force

Now the scaffolder will produce two additional class, Donar Repository, and the following interface which Donar Repository implements:

public interface IdonarRepository
IQueryable<donar> All { get; }
IQueryable<donar> AllIncluding(params Expression<Func<donar, object>>[] includeProperties);
donar Find(int id);
void InsertOrUpdate(donar donar);
void Delete(int id);
void Save();

Follow same process for Bloodgroup.

Now run the application and see the magic of MVCScaffolding.

Read More

Wednesday, April 24, 2013

Create an application using MVC 4 and EF code first

In this tutorial we will create our first MVC application using visual studio 2012, MVC 4 and EntityFramework Code First.

Step-1: Create Project
1. In Visual Studio 2012, add a new project by selecting the File menu, then New and then Project.
2. Select ASP.NET MVC 4 Web Application and C# as a language of choice.
3. Name the project Inventory and then click OK.
4. In the New ASP.NET MVC 4 Project wizard, choose Internet Application and razor view engine.
5. Leave the default values of the other options and click OK.

Step-2: Create a model class
1. In Solution Explorer, right click the Models folder.
2. Select Add from the menu and then from the bottom of its context menu choose Class.
3. In the Add New Item dialog, change the new class name to Category.
4. A new file, Category will be created.

Add following code into category class.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.ComponentModel;

namespace Inventory.Models
    public class Category
        public int CategoryID { get; set; }

        [DisplayName("Category Name")]
        public string CategoryName { get; set; }

Now, We’ll create a class that inherits from DbContext and knows how to serve up and manage the Category object. The Entity Framework will take care of bridging the classe and a database. Before the use of DbContext, we’ll need to create a reference to the Entity Framework API. This is the assembly that contains the Code First runtime. Visual studio 2012 automatically creates a reference of Entity Frmework. But for VS 2008 or 2010 we need to create a reference of EF by using Nuget. Nuget allows us to easily find and install reference assemblies from the internet.

1. Select the Inventory project in Solution Explorer.
2. From the Tools Menu, choose Library Package Manager which has a sub-menu.
3. From the sub-menu choose Package Manager Console.
4. At the console’s PM prompt type install-package EntityFramework then hit enter.
When the package is installed, you should see the “success message”.

Add following class to the Models folder and compile the application.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.Entity;

namespace Inventory.Models
    public class InventoryContext: DbContext
        public DbSet<Category> Categories { get; set; }

Step-3: Create Controller
1. Build the project by choosing Build from the Visual Studio menu and then Build Inventory from its drop-down menu.
2. In the Solution Explorer, right click the Controllers folder.
3. Click Add from its context menu and then Controller.
4. In the Add Controller window, change the Controller Name to CategoryController and select MVC Controller with read/write and view using EntityFramework.
5. Select Model Class: Category(Inventory.Models).
6. Select DBContext Class: InventoryContext.Models).
7. Views: Razor(cshtml).
Click the Add button.
The CategoryController class will be created with ActionResult methods: Index, Details, Create, Edit and Delete.

Step-4: Change the global.asax
1. Open the global.asax file from Solution Explorer.
2. Modify the MapRoute call to change the value of controller from “Home” to “Category”.
   "Default", // Route name
   "{controller}/{action}/{id}", // URL with parameters
   new { controller = "Category", action = "Index", id = UrlParameter.Optional }

Our first application is created. Run the application. The CategoryController.Index action will be the first method called.

Read More

Monday, April 22, 2013

MS SQL Server Job

A SQL  Job is a collection of steps executed by the database engine by SQL ServerAgent.

The job can perform many different functions within it that can save time and effort on the part of employees.

Let us consider a procedure PrcDailyUpdateForecast which will be executing hourly.

Minimally, it takes two or three steps to set up a scheduled task to execute the code of your choice. First, go to the Management node, SQL Server Agent, and highlight jobs. Right-click, new job, and give it a name like UpdateForecast. Move to the steps tab, add a step called S1.

The first tab (General) of this dialog shows the name of the job, when the job was created, whether or not the job is enabled, the category the job falls into, who owns the job within the database and a description of the job.

To disable a SQL Server job, uncheck the Enabled checkbox and the job will no longer run in the database if it was scheduled to do so.

The Steps tab of this dialog brings up the following screen:

Each step within the job will be identified within this screen.

These steps can include executing data transformation packages, executing queries, executing stored procedures, etc.

Within this interface you can move the order of the steps around so that they occur in a specific order, as well as, add new steps or edit existing steps.

If you select a step and select the Edit button, the following screen appears:

This screen is used to set the command that is to be executed during the current step.

You can also specify the name of the step, the type of command to execute and the exit code of the process if successful.

The tab also has a Schedule tab that can be used to manage the schedule of theSQL Server jobs.

This will allow the job to be set to occur once a daily, multiple times per day, weekly, monthly or just a one time. The job can be set to run at a certain times per day on a recurring basis as well, such as every hour or at a certain time per day.

The following screen shows the scheduling tab:

To work with the schedule, click the New Schedule or the Edit button.

The New Schedule button will allow a new schedule to be input for the job.

The Edit button will allow the edit of the current schedule.

There is also a Notifications tab that can be used to handle notifications from theSQL Server in the event that something unusual occurs with the job while it is running.

The following screen shows the notification dialog which allows the selection of notifications options:

Notifications can be sent in multiple methods. For the notification to work, there must be operators set up within SQL Server to receive them.

The notification can be an email, a page or a net send. Each notification can be used to alert the operator that something has occurred on the server and the actions are controlled by SQL Server Agent.

Once the SQL Server job is configured, the changes can be saved and the job can now be used to perform routine tasks on a regular basis which can free up resources within your organization.

Read More

Saturday, April 20, 2013

SQL Cursor

SQL Server is very good at handling sets of data. For example, you can use a single UPDATE statement to update many rows of data. There are times when you want to loop through a series of rows a perform processing for each row. In this case you can use a cursor.

The basic syntax of a cursor is:

Declare @vStyleID varchar(50)
Declare @Qty numeric

set @Qty = 0

select DISTINCT vStyleID from tmprptstylewiseclosingreport

OPEN CurClosing

INTO @vStyleID

set @Qty = (select sum(iTotalQty) from vwAutogenID where vStyleID = @vStyleID)
update tmprptstylewiseclosingreport
set nOrderQty = @Qty
where vStyleID = @vStyleID
set @Qty = 0

INTO @vStyleID

CLOSE CurClosing

Based on the example above, cursors include these components:

• DECLARE statements – Declare variables used in the code block
• SET\SELECT statements – Initialize the variables to a specific value
• DECLARE CURSOR statement – Populate the cursor with values that will be evaluated
NOTE – There are an equal number of variables in the DECLARE CURSOR FOR statement as there are in the SELECT statement. This could be 1 or many variables and associated columns.
• OPEN statement – Open the cursor to begin data processing
• FETCH NEXT statements – Assign the specific values from the cursor to the variables
NOTE – This logic is used for the initial population before the WHILE statement and then again during each loop in the process as a portion of the WHILE statement
• WHILE statement – Condition to begin and continue data processing
• BEGIN…END statement – Start and end of the code block
NOTE – Based on the data processing multiple BEGIN…END statements can be used
• Data processing – In this example, this logic is to backup a database to a specific path and file name, but this could be just about any DML or administrative logic
• CLOSE statement – Releases the current data and associated locks, but permits the cursor to be re-opened
• DEALLOCATE statement – Destroys the cursor

Read More

Wednesday, April 17, 2013

Introduction to MVC Concepts

The intension of MVC or Model-View-Controller is to build applications in a modular way. In MVC, 3 general parts/layers are responsible for creating the output and allowing the interaction with the user:





In the context of the MVC the model has the data that is essential to render things in the view. So it represents the data that are mandatory for rendering. It also takes over the process related to the domain. The model is used to do something and get the data.


A view takes care of “rendering the pixels on the screen“and should be as dump as possible. Naturally a view class understands one or more model(s) and outputs the information of that model. (A view is not allowed to write in the model.) So the view is conscious of the model and knows how to read the data out of it – but never the model should know something about the view!


It glues together Model and View, it controls the user communication with the model and the view. (It’s a thin layer that connects models with their views)

The diagram below illustrates a common request cycle in an MVC application.

The process begins when a user takes an action on a web page – submitting a form that adds a new blog post, for example. The request is sent to the blog controller, which extracts the data submitted via the HTTP POST request and sends a message to the blog model to save a new post with this data.

The model checks the data against its validation rules. Assuming it passes validation; the model stores the data for this new post in the database and tells the controller it was successful. The controller then sets a variable for the view indicating success.

Finally, the view displays this message to the user back on the web page, and they know their new blog post has been successfully created. If, for some reason, validation of the data failed, the model would alert the controller of any errors, which would set a variable containing these errors for the view. The view would then present the original form along with the error messages for any fields that didn’t validate.

Read More