In this blog, I have shown the process of calling web services through a stored procedure in SQL Server database. Also, I have explained how to call a stored procedure with a SOAP Envelope.

Step 1
Create a stored procedure in your SQL Server.
  CREATE proc [dbo].[spHTTPRequest]    
        @URI varchar(2000) = 'http://localhost:55253/',         
        @methodName varchar(50) = 'Get',    
        @requestBody varchar(8000) = '',    
        @SoapAction varchar(255),    
        @UserName nvarchar(100), -- Domain\UserName or UserName    
        @Password nvarchar(100),    
        @responseText varchar(8000) output   
  as   
  SET NOCOUNT ON   
  IF    @methodName = ''   
  BEGIN   
        select FailPoint = 'Method Name must be set'   
        return   
  END   
  set   @responseText = 'FAILED'   
  DECLARE @objectID int   
  DECLARE @hResult int   
  DECLARE @source varchar(255), @desc varchar(255)    
  EXEC @hResult = sp_OACreate 'MSXML2.ServerXMLHTTP', @objectID OUT   
  IF @hResult <> 0    
  BEGIN   
        EXEC sp_OAGetErrorInfo @objectID, @source OUT, @desc OUT   
        SELECT      hResult = convert(varbinary(4), @hResult),    
                    source = @source,    
                    description = @desc,    
                    FailPoint = 'Create failed',    
                    MedthodName = @methodName    
        goto destroy    
        return   
  END   
  -- open the destination URI with Specified method    
  EXEC @hResult = sp_OAMethod @objectID, 'open', null, @methodName, @URI, 'false', @UserName, @Password   
  IF @hResult <> 0    
  BEGIN   
        EXEC sp_OAGetErrorInfo @objectID, @source OUT, @desc OUT   
        SELECT      hResult = convert(varbinary(4), @hResult),    
              source = @source,    
              description = @desc,    
              FailPoint = 'Open failed',    
              MedthodName = @methodName    
        goto destroy    
        return   
  END   
  -- set request headers    
  EXEC @hResult = sp_OAMethod @objectID, 'setRequestHeader', null, 'Content-Type', 'text/xml;charset=UTF-8'   
  IF @hResult <> 0    
  BEGIN   
        EXEC sp_OAGetErrorInfo @objectID, @source OUT, @desc OUT   
        SELECT      hResult = convert(varbinary(4), @hResult),    
              source = @source,    
              description = @desc,    
              FailPoint = 'SetRequestHeader failed',    
              MedthodName = @methodName    
        goto destroy    
        return   
  END   
  -- set soap action    
  EXEC @hResult = sp_OAMethod @objectID, 'setRequestHeader', null, 'SOAPAction', @SoapAction    
  IF @hResult <> 0    
  BEGIN   
        EXEC sp_OAGetErrorInfo @objectID, @source OUT, @desc OUT   
        SELECT      hResult = convert(varbinary(4), @hResult),    
              source = @source,    
              description = @desc,    
              FailPoint = 'SetRequestHeader failed',    
              MedthodName = @methodName    
        goto destroy    
        return   
  END   
  declare @len int   
  set @len = len(@requestBody)    
  EXEC @hResult = sp_OAMethod @objectID, 'setRequestHeader', null, 'Content-Length', @len    
  IF @hResult <> 0    
  BEGIN   
        EXEC sp_OAGetErrorInfo @objectID, @source OUT, @desc OUT   
        SELECT      hResult = convert(varbinary(4), @hResult),    
              source = @source,    
              description = @desc,    
              FailPoint = 'SetRequestHeader failed',    
              MedthodName = @methodName    
        goto destroy    
        return   
  END   
  /*   
  -- if you have headers in a table called RequestHeader you can go through them with this   
  DECLARE @HeaderKey varchar(500), @HeaderValue varchar(500)   
  DECLARE RequestHeader CURSOR  
  LOCAL FAST_FORWARD   
  FOR  
        SELECT      HeaderKey, HeaderValue   
        FROM RequestHeaders   
        WHERE       Method = @methodName   
  OPEN RequestHeader   
  FETCH NEXT FROM RequestHeader   
  INTO @HeaderKey, @HeaderValue   
  WHILE @@FETCH_STATUS = 0   
  BEGIN  
        --select @HeaderKey, @HeaderValue, @methodName   
        EXEC @hResult = sp_OAMethod @objectID, 'setRequestHeader', null, @HeaderKey, @HeaderValue   
        IF @hResult <> 0   
        BEGIN  
              EXEC sp_OAGetErrorInfo @objectID, @source OUT, @desc OUT  
              SELECT      hResult = convert(varbinary(4), @hResult),   
                    source = @source,   
                    description = @desc,   
                    FailPoint = 'SetRequestHeader failed',   
                    MedthodName = @methodName   
              goto destroy   
              return  
        END  
        FETCH NEXT FROM RequestHeader   
        INTO @HeaderKey, @HeaderValue   
  END  
  CLOSE RequestHeader   
  DEALLOCATE RequestHeader   
  */    
  -- send the request    
  EXEC @hResult = sp_OAMethod @objectID, 'send', null, @requestBody    
  IF    @hResult <> 0    
  BEGIN   
        EXEC sp_OAGetErrorInfo @objectID, @source OUT, @desc OUT   
        SELECT      hResult = convert(varbinary(4), @hResult),    
              source = @source,    
              description = @desc,    
              FailPoint = 'Send failed',    
              MedthodName = @methodName    
        goto destroy    
        return   
  END   
  declare @statusText varchar(1000), @status varchar(1000)    
  -- Get status text    
  exec sp_OAGetProperty @objectID, 'StatusText', @statusText out   
  exec sp_OAGetProperty @objectID, 'Status', @status out   
  select @status, @statusText, @methodName    
  -- Get response text    
  exec sp_OAGetProperty @objectID, 'responseText', @responseText out   
  IF @hResult <> 0    
  BEGIN   
        EXEC sp_OAGetErrorInfo @objectID, @source OUT, @desc OUT   
        SELECT      hResult = convert(varbinary(4), @hResult),    
              source = @source,    
              description = @desc,    
              FailPoint = 'ResponseText failed',    
              MedthodName = @methodName    
        goto destroy    
        return   
  END   
  destroy:    
        exec sp_OADestroy @objectID    
  SET NOCOUNT OFF   
      
  GO   

The Stored Procedure takes the following parameters.
  @URI: the URI of the web service
  @MethodName: this would be ‘GET’ or ‘POST’
  @RequestBody: this is the SOAP xml that you want to send
  @SoapAction: this the operation that you want to call on your service
  @UserName: NT UserName if your web service requires authentication
  @Password: the password if using NT Authentication on the web service
  @ResponseText: this is an out parameter that contains the response from the web service

Step 2
Make the setting in SQL for it.
  Use master 
  sp_configure 'show advanced options', 1  
   
  GO  
  RECONFIGURE;  
  GO  
  sp_configure 'Ole Automation Procedures', 1  
  GO  
  RECONFIGURE;  
  GO  
  sp_configure 'show advanced options', 1  
  GO  
  RECONFIGURE; 

Step 3
Call the stored procedure (Here is a sample call to my service).
  declare @xmlOut varchar(8000) 
  Declare @RequestText as varchar(8000); 
  set @RequestText= 
  '<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:tem="http://tempuri.org/"> 
     <soapenv:Header/> 
     <soapenv:Body> 
        <tem:CreateOrder> 
           <!--Optional:--> 
           <tem:OrderRequest> 
              <tem:OrderId>200</tem:OrderId> 
              <!--Optional:--> 
              <tem:OrderName>something</tem:OrderName> 
           </tem:OrderRequest> 
        </tem:CreateOrder> 
     </soapenv:Body> 
  </soapenv:Envelope>' 
  exec spHTTPRequest 
  'http://localhost/testwebservices/helloworldservice.asmx', 
  'POST', 
  @RequestText, 
  'http://tempuri.org/CreateOrderForMe',   -- this is your SOAPAction: 
  '', '', @xmlOut out 
  select @xmlOut  

Make sure your SOAP action is correct. Copy this action from your services. It will show up when your service is RUN.

In this post we will see how to create ASP.NET Registration form and save its data in MS SQL Database. It will be quite simple form with values like Employee Id, Employee name, Date of Birth, Country, State, City names and more. We will be also adding Profile Image of the user and saving it to ASP.NET Project’s folder to complete our Registration form data. So let’s see.

I’m also using AJAX to create this registration form in ASP.NET. You can add it to your project by using Visual Studio’s Nuget Manager Console.

First create a new ASP.NET web project in Visual studio. I’m using Visual studio 2017 to create this registration form for ASP.NET.

Now add a new ASPX form with name Registration.aspx and edit it as below:

Registration.aspx:

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Registration.aspx.cs"
Inherits="RegistrationForms.Registration" ValidateRequest="true"%>

<%@ Register Assembly="AjaxControlToolkit" Namespace="AjaxControlToolkit" TagPrefix="ajax" %>
<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<link href="css/bootstrap.min.css" rel="stylesheet" />
<style>
.borderless td, .borderless th {
border: none;
border-color: Red;
}

.table-condensed > thead > tr > th, .table-condensed > tbody > tr > th, .table-condensed > tfoot > tr > th, .table-condensed > thead > tr > td, .table-condensed > tbody > tr > td, .table-condensed > tfoot > tr > td {
padding: 3px;
}

input, select {
border-radius: 3px;
padding: 1px;
border: 1px solid darkgray;
}

.btnCoral {
background-color: crimson;
color: #fff;
}

body {
/* Permalink - use to edit and share this gradient: http://colorzilla.com/gradient-editor/#1e5799+0,2989d8+50,207cca+51,7db9e8+100;Blue+Gloss+Default */
background: #1e5799; /* Old browsers */
background: -moz-linear-gradient(top, #1e5799 0%, #2989d8 50%, #207cca 51%, #7db9e8 100%); /* FF3.6-15 */
background: -webkit-linear-gradient(top, #1e5799 0%,#2989d8 50%,#207cca 51%,#7db9e8 100%); /* Chrome10-25,Safari5.1-6 */
background: linear-gradient(to bottom, #1e5799 0%,#2989d8 50%,#207cca 51%,#7db9e8 100%); /* W3C, IE10+, FF16+, Chrome26+, Opera12+, Safari7+ */
filter: progid:DXImageTransform.Microsoft.gradient( startColorstr='#1e5799', endColorstr='#7db9e8',GradientType=0 ); /* IE6-9 */
}

.parent-container {
background-color: black;
width:70%;
}

.container {
background-color: white;
margin:2px;
width:auto;
}

</style>
<title>Registration Form</title>
</head>
<body>
<form id="form1" runat="server">
<asp:ScriptManager runat="server" />
<div class="parent-container">
<div class="container">
<table class="table-condensed borderless">
<tr>
<td align="center" colspan="2">
<h2 style="background-color: black; color: White; padding: 5px;">REGISTRATION FORM</h2>
</td>
</tr>
<tr>
<td align="right">EMP Id:</td>
<td align="left">
<asp:TextBox runat="server" ID="txtEmpId" />
<asp:RequiredFieldValidator runat="server" ControlToValidate="txtEmpId" ErrorMessage="*" ForeColor="Red"
ValidateRequestMode="Enabled"/>
</td>
</tr>
<tr>
<td align="right">Name:</td>
<td align="left">
<asp:TextBox runat="server" ID="txtName" />
<asp:RequiredFieldValidator runat="server" ControlToValidate="txtName" ErrorMessage="*" ForeColor="Red" />
</td>
</tr>
<tr>
<td align="right">Date of Birth:</td>
<td align="left">
<asp:TextBox runat="server" ID="txtDOB" />
<ajax:CalendarExtender runat="server" Format="yyyy-MM-dd" TargetControlID="txtDOB" />

<asp:RequiredFieldValidator runat="server" ControlToValidate="txtDOB" ErrorMessage="*" ForeColor="Red" />
</td>
</tr>
<tr>
<td align="right">Address:</td>
<td align="left">
<asp:TextBox runat="server" ID="txtAddress" />
<asp:RequiredFieldValidator runat="server" ControlToValidate="txtAddress" ErrorMessage="*" ForeColor="Red" />
</td>
</tr>
<tr>
<td align="right">Country:</td>
<td align="left">
<asp:DropDownList runat="server" ID="ddCountry"
AutoPostBack="true" OnSelectedIndexChanged="ddCountry_SelectedIndexChanged" /></td>
</tr>
<tr>
<td align="right">State:</td>
<td align="left">
<asp:DropDownList runat="server" ID="ddState"
AutoPostBack="true" OnSelectedIndexChanged="ddState_SelectedIndexChanged" /></td>
</tr>
<tr>
<td align="right">City:</td>
<td align="left">
<asp:DropDownList runat="server" ID="ddCity" /></td>
</tr>
<tr>
<td align="right">Pincode:</td>
<td align="left">
<asp:TextBox runat="server" ID="txtPincode" />
<asp:RequiredFieldValidator runat="server" ControlToValidate="txtPincode" ErrorMessage="*" ForeColor="Red" />
</td>
</tr>
<tr>
<td align="right">Date of Joining:</td>
<td align="left">
<asp:TextBox runat="server" ID="txtDOJ" />
<ajax:CalendarExtender runat="server" Format="yyyy-MM-dd" TargetControlID="txtDOJ" />
<asp:RequiredFieldValidator runat="server" ControlToValidate="txtDOJ" ErrorMessage="*" ForeColor="Red" />
</td>
</tr>
<tr>
<td align="right">Department:</td>
<td align="left">
<asp:DropDownList runat="server" ID="ddDepartment" /></td>
</tr>
<tr>
<td align="right">Role:</td>
<td align="left">
<asp:DropDownList runat="server" ID="ddRole">

<asp:ListItem Text="USER" Value="USER" />
<asp:ListItem Text="ROLE" Value="ROLE" />

</asp:DropDownList></td>
</tr>
<tr>
<td align="right">Profile Pic:</td>
<td align="left" valign="middle">
<asp:FileUpload runat="server" ID="fileUpload" />
<asp:Button runat="server" Text="Upload" ID="btnUpload" OnClick="btnUpload_Click" />

<asp:Image ID="imgProfile" runat="server" Width="150px" Height="150px" />
</td>
</tr>

<tr>
<td align="right">Email ID:</td>
<td align="left">
<asp:TextBox runat="server" ID="txtEmail" />
<asp:RequiredFieldValidator runat="server" ControlToValidate="txtEmail" ErrorMessage="*" ForeColor="Red" />
<asp:RegularExpressionValidator runat="server" ValidationExpression="\w+([-+.]\w+)*@\w+([-.]\w+)*\.\w+([-.]\w+)*" ControlToValidate="txtEmail"
ErrorMessage="Email address invalid" />
</td>
</tr>
<tr>
<td align="right">Password:</td>
<td align="left">
<asp:TextBox runat="server" ID="txtpassword" TextMode="Password"/>
<asp:RequiredFieldValidator runat="server" ControlToValidate="txtpassword" ErrorMessage="*" ForeColor="Red" />
</td>
</tr>
<tr>
<td align="right">Confirm Password:</td>
<td align="left">
<asp:TextBox runat="server" ID="txtConfirmPassword" TextMode="Password"/></td>
</tr>
<tr>
<td colspan="20">
<asp:CompareValidator ID="comparePasswords"
runat="server"
ControlToCompare="txtpassword"
ControlToValidate="txtConfirmPassword"
ErrorMessage="Passwords do not match up."
ForeColor="Red" />
</td>
</tr>
<tr>
<td colspan="2" align="center">
<br />
<asp:Button runat="server" ID="btnSubmit" Text="Submit" OnClick="btnSubmit_Click"
CssClass="btn btnCoral" />
</td>
</tr>
<tr>
<td colspan="2">
<asp:Label runat="server" ID="lblInfo" />
</td>
</tr>
</table>
</div>

</div>
</form>
</body>
</html>

Ajax’s Data picker is used to set Date of Birth and Date of Joining information of the employee to complete the registration process.

Profile picture will be saved inside “ProfileImages” folder.

And below is my code for the registration page:

using System;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;
using System.IO;
using System.Web.UI;

namespace RegistrationForms
{
public partial class Registration : System.Web.UI.Page
{
SqlConnection con;
SqlCommand cmd;
SqlDataAdapter adapter;
SqlDataReader reader;
DataSet ds;
DataTable dt;

protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
LoadCountries();
LoadDepartment();
}
}

public void LoadDepartment()
{
try
{
con = new SqlConnection(ConfigurationManager.ConnectionStrings["connectionstring"].ConnectionString);
cmd = new SqlCommand("Select * from tblDepartments", con);
con.Open();
adapter = new SqlDataAdapter(cmd);
dt = new DataTable();
adapter.Fill(dt);

if (dt.Rows.Count > 0)
{
DataRow dr = dt.NewRow();
dr["DeptId"] = 0;
dr["Department"] = "Please select Department";
dt.Rows.InsertAt(dr, 0);

ddDepartment.DataSource = dt;
ddDepartment.DataTextField = "Department";
ddDepartment.DataValueField = "DeptId";
ddDepartment.DataBind();
}
adapter.Dispose();
cmd.Dispose();
con.Close();
}
catch (Exception ex)
{
lblInfo.Text = ex.Message.ToString();
}
}
public void LoadCountries()
{
try
{
con = new SqlConnection(ConfigurationManager.ConnectionStrings["connectionstring"].ConnectionString);
cmd = new SqlCommand("Select * from tblCountries", con);
con.Open();
adapter = new SqlDataAdapter(cmd);
dt = new DataTable();
adapter.Fill(dt);

if (dt.Rows.Count > 0)
{
DataRow dr = dt.NewRow();
dr["CountryId"] = 0;
dr["Country"] = "Please select Country";
dt.Rows.InsertAt(dr, 0);

ddCountry.DataSource = dt;
ddCountry.DataTextField = "Country";
ddCountry.DataValueField = "CountryId";
ddCountry.SelectedIndex = 0;
ddCountry.DataBind();
}
adapter.Dispose();
cmd.Dispose();
con.Close();
}
catch (Exception ex)
{
lblInfo.Text = ex.Message.ToString();
}

}
protected void btnSubmit_Click(object sender, EventArgs e)
{
try
{
if (Page.IsValid)
{

con = new SqlConnection(ConfigurationManager.ConnectionStrings["connectionString"].ConnectionString);
cmd = new SqlCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "sp_AddUsers";
cmd.Connection = con;
cmd.Parameters.AddWithValue("@UserId", txtEmpId.Text.ToString());
cmd.Parameters.AddWithValue("@Name", txtName.Text.ToString());
cmd.Parameters.AddWithValue("@DOB", txtDOB.Text.ToString());
cmd.Parameters.AddWithValue("@Address", txtAddress.Text.ToString());
cmd.Parameters.AddWithValue("@City", ddCity.SelectedValue.ToString());
cmd.Parameters.AddWithValue("@State", ddState.SelectedValue.ToString());
cmd.Parameters.AddWithValue("@Country", ddCountry.SelectedValue.ToString());
cmd.Parameters.AddWithValue("@Pincode", txtPincode.Text.ToString());
cmd.Parameters.AddWithValue("@Department", ddDepartment.SelectedValue.ToString());
cmd.Parameters.AddWithValue("@UserRole", ddRole.SelectedItem.Text.ToString());
cmd.Parameters.AddWithValue("@Passcode", txtpassword.Text.ToString());
cmd.Parameters.AddWithValue("@DOJ", txtDOJ.Text.ToString());
cmd.Parameters.AddWithValue("@ProfilePic", imgProfile.ImageUrl.ToString());
cmd.Parameters.AddWithValue("@EmailId", txtEmail.Text.ToString());
con.Open();
cmd.ExecuteNonQuery();
cmd.Dispose();
con.Close();
ShowAlert("Success", "Saved Successfully");
ClearFeilds();
}
else
ShowAlert("Error", "Please enter all fields");
}
catch (Exception ex)
{

}
}

public void ShowAlert(String header, String message)
{
ScriptManager.RegisterStartupScript(this, GetType(), header, "alert('" + message + "');", true);
}

public void ClearFeilds()
{
txtAddress.Text = "";
txtConfirmPassword.Text = "";
txtDOB.Text = "";
txtDOJ.Text = "";
txtEmail.Text = "";
txtEmpId.Text = "";
txtName.Text = "";
txtpassword.Text = "";
txtPincode.Text = "";
LoadCountries();
}
protected void ddCountry_SelectedIndexChanged(object sender, EventArgs e)
{
LoadState();
}

public void LoadState()
{
try
{
con = new SqlConnection(ConfigurationManager.ConnectionStrings["connectionstring"].ConnectionString);
cmd = new SqlCommand("Select * from tblStates where CountryId=@id", con);
cmd.Parameters.AddWithValue("@id", ddCountry.SelectedValue.ToString());
con.Open();
adapter = new SqlDataAdapter(cmd);
dt = new DataTable();
adapter.Fill(dt);

if (dt.Rows.Count > 0)
{
DataRow dr = dt.NewRow();
dr["StateId"] = 0;
dr["State"] = "Please select State";
dr["CountryId"] = "0";
dt.Rows.InsertAt(dr, 0);
ddState.Items.Clear();
ddState.ClearSelection();
ddState.DataSource = dt;
ddState.DataTextField = "State";
ddState.DataValueField = "StateId";
ddState.DataBind();
ddState.SelectedValue = null;
ddState.SelectedIndex = 0;
ddCity.Items.Clear();
ddCity.SelectedValue = null;
}
adapter.Dispose();
cmd.Dispose();
con.Close();
}
catch (Exception ex)
{
}
}

protected void ddState_SelectedIndexChanged(object sender, EventArgs e)
{
try
{
con = new SqlConnection(ConfigurationManager.ConnectionStrings["connectionstring"].ConnectionString);
cmd = new SqlCommand("Select * from tblCities where Stateid=@id", con);
cmd.Parameters.AddWithValue("@id", ddState.SelectedValue.ToString());
con.Open();
adapter = new SqlDataAdapter(cmd);
dt = new DataTable();
adapter.Fill(dt);

if (dt.Rows.Count > 0)
{
DataRow dr = dt.NewRow();
dr["CityId"] = 0;
dr["City"] = "Please select City";
dr["StateId"] = "0";
dt.Rows.InsertAt(dr, 0);
ddCity.DataSource = null;
ddCity.DataSource = dt;
ddCity.DataTextField = "City";
ddCity.DataValueField = "CityId";
ddCity.DataBind();
ddCity.SelectedValue = null;
ddCity.SelectedIndex = 0;
}
adapter.Dispose();
cmd.Dispose();
con.Close();
}
catch (Exception ex)
{
}
}

protected void btnUpload_Click(object sender, EventArgs e)
{
try
{
if (fileUpload.HasFile)
{
string fileName = Path.GetFileName(fileUpload.PostedFile.FileName);
fileUpload.PostedFile.SaveAs(Server.MapPath("~/ProfileImages/") + fileName);
imgProfile.ImageUrl = "~/ProfileImages/" + fileName;
}
}
catch (Exception ex)
{
lblInfo.Text = "Image upload: " + ex.Message.ToString();
}
}
}
}

Below is the Database script (MS SQL Database):

--Create database [ESource]
USE [ESource]

CREATE TABLE [dbo].[tblStates](
[StateId] [int] IDENTITY(1,1) NOT NULL,
[State] [nvarchar](50) NOT NULL,
[CountryId] [int] NOT NULL
)

CREATE TABLE [dbo].[tblCities](
[CityId] [int] IDENTITY(1,1) NOT NULL,
[City] [nvarchar](50) NOT NULL,
[StateId] [int] NOT NULL
)

CREATE TABLE [dbo].[tblCountries](
[CountryId] [int] IDENTITY(1,1) NOT NULL,
[Country] [nvarchar](50) NOT NULL
)

CREATE TABLE [dbo].[tblDepartments](
[DeptId] [int] IDENTITY(1,1) NOT NULL,
[Department] [nvarchar](50) NOT NULL
)

CREATE TABLE [dbo].[tblUserDetails](
[EId] [int] IDENTITY(1,1) NOT NULL,
[EmailId] [nvarchar](50) NOT NULL,
[UserId] [nvarchar](50) NULL,
[Passcode] [nvarchar](50) NOT NULL,
[Name] [nvarchar](50) NOT NULL,
[DOB] [datetime] NOT NULL,
[Address] [nvarchar](max) NOT NULL,
[City] [nvarchar](50) NOT NULL,
[Pincode] [int] NOT NULL,
[State] [nvarchar](50) NOT NULL,
[Country] [nvarchar](50) NOT NULL,
[Department] [nvarchar](50) NOT NULL,
[DOJ] [datetime] NOT NULL,
[ProfilePic] [nvarchar](max) NOT NULL,
[UserRole] [nvarchar](50)
)

CREATE procedure [dbo].[sp_AddUsers]
(
@UserId nvarchar(50),
@Passcode nvarchar(50),
@Name nvarchar(50),
@DOB datetime,
@Address nvarchar(max),
@City int,
@Pincode nvarchar(50),
@State int,
@Country int,
@Department int,
@DOJ datetime,
@ProfilePic nvarchar(max),
@UserRole nvarchar(50),
@EmailId nvarchar(50)
)
as begin
Insert into tblUserDetails
(UserId,Passcode,Name,DOB,Address,City,Pincode,State,Country,Department,DOJ,ProfilePic,UserRole,EmailId)
values
(@UserId,@Passcode,@Name,@DOB,@Address,@City,@Pincode,@State,@Country,@Department,@DOJ,@ProfilePic,@UserRole,@EmailId);
end
GO

I am pretty sure that all of us read or even participated in quite a few heated discussions about Common Language Runtime (CLR) code in Microsoft SQL Server. Some people state that CLR code works faster than T-SQL, others oppose them. Although, as with the other SQL Server technologies, there is no simple answer to that question. Both technologies are different in nature and should be used for the different tasks. T-SQL is the interpreted language, which is optimized for set-based logic and data access. CLR, on the other hand, produces compiled code that works the best for imperative procedural-style code.

Even with imperative code, we need to decide if we want to implement it in CLR or as the client-side code, perhaps running on the application servers. CLR works within SQL Server process. While, on one hand, it eliminates network traffic and can provide us the best performance due to the “closeness” to the data, CLR adds the load to the SQL Server. It is usually easier and cheaper to scale out application servers rather than upgrading SQL Server box.

There are some cases when we must use CLR code though. For example, let’s think about the queries that performing RegEx evaluations as part of the where clause. It would be inefficient to move such evaluations to the client code and there is no regular expressions support in SQL Server. So CLR is the only choice we have. Although, in the other cases, when procedural-style logic can be moved to the application servers, we should consider such option. Especially when application servers are residing closely to SQL Server and network latency and throughput are not an issue.

Today we will compare performance of the few different areas of CLR and T-SQL. I am not trying to answer the question – “what technology is better”. As usual it fits into “It depends” category. What I want to do is looking how technologies behave in the similar tasks when they can be interchanged.

Before we begin, let’s create the table and populate it with some data.


As the first step, let’s compare the user-defined functions invocation cost. We will use the simple function that accepts the integer value as the parameter and returns 1 in case if that value is even. We can see CLR C# implementation below.

As we can see, there are the attributes specified for each function. Those attributes describes different aspects of UDF behavior and can help Query Optimizer to generate more efficient execution plans. I would recommend specifying them explicitly rather than relying on default values.

One of the attributes – DataAccess – indicates if function performs any data access. When this is the case, SQL Server calls the function in the different context that will allow access to the data. Setting up such context introduces additional overhead during the functional call, which we will see in a few minutes.

T-SQL implementation of those functions would look like that:


Let’s measure average execution time for the statements shown below. Obviously, different hardware leads to the different execution time although trends would be the same.

Each statement performs clustered index scan of dbo.Numbers table and checks if Num column is even for every row from the table. For CLR and T-SQL scalar user-defined functions, that introduces the actual function call. Inline multi-statement function, on the other hand, performed the calculation inline without function call overhead.

As we can see, CLR UDF without data access context performs about four times faster comparing to T-SQL scalar function. Even if establishing data-access context introduces additional overhead and increases execution time, it is still faster than T-SQL scalar UDF implementation.

The key point here though is than in such particular example the best performance could be achieved if we stop using the functions at all rather than converting T-SQL implementation to CLR UDF. Even with CLR UDF, the overhead of the function call is much higher than inline calculations.

Unfortunately, this is not always the case. While we should always think about code refactoring as the option, there are the cases when CLR implementation can outperform inline calculations even with all overhead it introduced. We are talking about mathematical calculations, string manipulations, XML parsing and serialization – to name just a few. Let’s test the performance of the functions that calculate the distance between two points defined by latitude and longitude.

We can see that CLR UDF runs almost two times faster comparing to inline table-valued functions and more than five times faster comparing to T-SQL scalar UDF. Even with all calling overhead involved.

Now let’s look at the data access performance. The first test compares performance of the separate DML statements from T-SQL and CLR stored procedures. In that test we will create the procedures that calculate the number of the rows in dbo.Numbers table for specific Num interval provided as the parameters. We can see the implementation below

Finally, let’s look at the performance of CLR aggregates. We will use standard implementation of the aggregate that concatenates the values into comma-separated string.


As with user-defined functions, it is extremely important to set the attributes that tell Query Optimizer about CLR Aggregate behavior and implementation. This would help to generate more efficient execution plans and prevent incorrect results due to optimization. It is also important to specify MaxByteSize attribute that defines the maximum size of the aggregate output. In our case, we set it to -1 which means that aggregate could hold up to 2GB of data.

Speaking of T-SQL implementation, let’s look at the approach that uses SQL variable to hold intermediate results. That approach implements imperative row-by-row processing under the hood.

As another option let’s use FOR XML PATH technique. It is worth to mention that this technique could introduce different results by replacing XML special characters with character entities. For example, if our values contain < character, it would be replaced with &lt; string.

Our test code would look like that:


When we compare the performance on the different row set sizes, we would see results below

As we can see, CLR aggregate has slightly higher startup cost comparing to T-SQL variable approach although it quickly disappears on the larger rowsets. Performance of both: CLR aggregate and FOR XML PATH methods linearly depend on the number of the rows to aggregate while performance of SQL Variable approach degrade exponentially. SQL Server needs to initiate the new instance of the string every time it concatenates the new value and it does not work efficiently especially when it needs to be populated with the large values.

The key point I would like to make with that example is that we always need to look at the options to replace imperative code with declarative set-based logic. While CLR usually outperforms procedural-style T-SQL code, set-based logic could outperform both of them.

While there are some cases when choice between technologies is obvious, there are the cases when it is not clear. Let us think about scalar UDF that needs to perform some data access. Lower invocation cost of CLR function can be mitigated by higher data access cost from there. Similarly, inline mathematical calculations in T-SQL could be slower than in CLR even with all invocation overhead involved. In those cases, we must test different approaches and find the best one which works in that particular case.

Memory is one of the most-used resources in SQL Server. Generally, the more you have, the better query performance you’ll get. How can you track your server’s memory usage? One way is to use the Performance Monitor (Perfmon) counters exposed through the sys.dm_os_performance_counters DMV. One indicator of memory performance is Page Life Expectancy (PLE). You can capture basic memory usage over time by setting up a SQL Server Agent job to query this DMV, inserting the results into a table, and reporting on the table results.

COLLECTING THE DATA

I have a “DBAInfo” database on my instance that I use to track metrics and other information. I create a new table, MemoryHistory.

USE DBAInfo;
CREATE TABLE MemoryHistory
(ID INT IDENTITY NOT NULL,
CollectionDateTime DATETIME,
PerfmonObjectName NCHAR(128),
CounterName NCHAR(128),
CounterValue BIGINT)

Then, I create a new SQL Server Agent job that runs every 5 minutes.

The only step in this job is the below query, which queries the DMV and inserts the results into the table I created.

INSERT INTO MemoryHistory
SELECT CURRENT_TIMESTAMP,
object_name,
counter_name,
cntr_value
FROM sys.dm_os_performance_counters
WHERE object_name = 'SQLServer:Buffer Manager';

I schedule the job to run every five minutes.

VIEWING THE DATA

Now, this data isn’t going to do me any good unless I view it, and make a decision or perform an action based on what I learn.

To view the data I’ve collected, I run the following query:

SELECT ID,
CollectionDateTime,
CounterName,
CounterValue
FROM MemoryHistory;

That’s a lot of junk to sort through when all I want to see is PLE, so I narrow down the query a bit.

SELECT ID,
CollectionDateTime,
CounterName,
CounterValue
FROM MemoryHistory
WHERE CounterName = 'Page life expectancy';

But who wants to read through results like that each time there’s a problem to see when PLE rose or fell? Not me. I’d rather see it in a graphical format. How can I do that?

SQL Server Reporting Services

I have SSRS at my disposal. I’m going to create a very simple report that will allow me to enter start and end dates, and will display a line chart for PLE during that time.

REPORTING ON THE DATA

I set up my report to have DBAInfo as my data source. In order to choose dates, I use the following query as my dataset.

SELECT ID,
CollectionDateTime,
CounterName,
CounterValue
FROM MemoryHistory
WHERE CounterName = 'Page life expectancy'
AND CONVERT(DATE, CollectionDateTime) &gt;= @Start
AND CONVERT(DATE, CollectionDateTime) &lt;= @End;

I change my @Start and @End parameters to “Date/Time” so I get a date picker.

I drag a Line Chart onto the design surface and add the CounterValue as my Value and CollectionDateTime as my Category Group.

I can preview the report to view it:

Last but not least, I’ll deploy this report to Report Manager so that I and others can run it, or even schedule a regular subscription.

Today post I will show you the difference between Stored Procedure and Function in SQL Server. Stored Procedures are pre-compile objects which are compiled for first time and its compiled format is saved which executes (compiled code) whenever it is called. But Function is compiled and executed every time when it is called.

Basic Difference

• Function must return a value but in Stored Procedure it is optional( Procedure can return zero or n values).
• Functions can have only input parameters for it whereas Procedures can have input/output parameters .
• Function takes one input parameter it is mandatory but Stored Procedure may take o to n input parameters..
• Functions can be called from Procedure whereas Procedures cannot be called from Function.

Advance Difference

1. Procedure allows SELECT as well as DML(INSERT/UPDATE/DELETE) statement in it whereas Function allows only SELECT statement in it.
2. Procedures can not be utilized in a SELECT statement whereas Function can be embedded in a SELECT statement.
3. Stored Procedures cannot be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section whereas Function can be.
4. Functions that return tables can be treated as another rowset. This can be used in JOINs with other tables.
5. Inline Function can be though of as views that take parameters and can be used in JOINs and other Rowset operations.
6. Exception can be handled by try-catch block in a Procedure whereas try-catch block cannot be used in a Function.
7. We can go for Transaction Management in Procedure whereas we can't go in Function.

Hope it will completely enough to explain the difference about stored procedure and Function.

Here, I have provided an article showing you how to utilize the two new logical functions Choose and IIF in SQL Server. The Choose function works like an array kind of thing and the IIF function is used to check a condition. In this article we will see both functions with examples. These functions are also called new logical functions in SQL Server 2012. So let's take a look at a practical example of how to use the Choose and IIF functions in SQL Server. The example is developed in SQL Server 2012 using the SQL Server Management Studio.

These are the two logical functions:

1. IIF() Function
2. Choose() Function

IIF() Function

The IIF function is used to check a condition. Suppose X>Y. In this condition a is the first expression and b is the second expression. If the first expression evaluates to TRUE then the first value is displayed, if not the second value is displayed.

Syntax

IIF ( boolean_expression, true_value, false_value )

Example

DECLARE @X INT;
SET @X=50;
DECLARE @Y INT;
SET @Y=60;
Select iif(@X>@Y, 50, 60) As IIFResult

In this example X=50 and Y=60; in other words the condition is false.  Select iif(@X>@Y, 50, 60) As IIFResult. It returns false value that is 60.

Output

Choose() Function

This function is used to return the value out of a list based on its index number. You can think of it as an array kind of thing. The Index number here starts from 1.

Syntax

CHOOSE ( index, value1, value2.... [, valueN ] )

CHOOSE() Function excepts two parameters,

Index: Index is an integer expression that represents an index into the list of the items. The list index always starts at 1. 

Value: List of values of any data type.

Now some facts related to the Choose Function

1. Item index starts from 1

DECLARE @ShowIndex INT;
SET @ShowIndex =5;
Select Choose(@ShowIndex, 'M','N','H','P','T','L','S','H') As ChooseResult 

In the preceding example we take index=5. It will start at 1. Choose() returns T as output since T is present at @Index location 5.

Output

2.  When passed a set of types to the function it returns the data type with the highest precedence; see:

DECLARE @ShowIndex INT;
SET @ShowIndex =5;
Select Choose(@ShowIndex ,35,42,12.6,14,15,18.7)  As CooseResult

In this example we use index=5. It will start at 1. Choose() returns 15.0 as output since 15 is present at @ShowIndex location 5 because in the item list, fractional numbers have higher precedence than integers.

3. If an index value exceeds the bound of the array it returns NULL

DECLARE @ShowIndex INT;
SET @ShowIndex =9;
Select Choose(@ShowIndex , 'M','N','H','P','T','L','S','H')  As CooseResult

In this example we take index=9. It will start at 1. Choose() returns Null as output because in the item list the index value exceeds the bounds of the array; the last Index=8.

Output

4. If the index value is negative then that exceeds the bounds of the array therefore it returns NULL; see:

DECLARE @ShowIndex INT;
SET @ShowIndex =-1;
Select Choose(@ShowIndex, 'M','N','H','P','T','L','S','H')  As CooseResult

In this example we take index= -1. It will start at 1. Choose() returns Null as output because in the item list the index value exceeds the bounds of the array.

Output

5. If the provided index value has a float data type other than int, then the value is implicitly converted to an integer; see:

DECLARE @ShowIndex  INT;
SET @ShowIndex =4.5;
Select Choose(@ShowIndex ,35,42,12.6,13,15,20) As CooseResult

In this example we take index= 4.5. It will start at 1.  If the specified index value has a float data type other than int, then the value is implicitly converted to an integer. It returns the 13.0 as output since 15 is present at @ShowIndex=4.5 which means index is 4.

Output

This article talk about what SQL injection is, how can that effect the security of our websites and what steps should be taken to create an ASP.NET application SQL injection proof. SQL injection is the attack in which the user of the website will input some SQL code as input which would result in creating a SQL statement that developers didn't intend to write. These SQL statements could result in unauthorized access, revealing secret user information and sometimes it could even wipe out the entire data lying on the server.

Getting to know SQL Injection

Let us take this discussion a little further by looking into the bad coding practices that will make the application prone to the SQL injection attacks. Let us create a simple table that contains username and password of the user for authentication.

Now I will create a small page that lets the user to enter his login credentials and get them validated against the Users table.

Note: Password should never be stored in plain text. This table contains password in plain text just for the sake of simplicity of this article.

The actual code that I will use to authenticate the user contains dynamic SQL that is being created by concatenating strings. This code will return true if the userid and password are found in the database otherwise false.  

public bool IsUserAuthenticated_Bad(string username, string password)
{
    DataTable result = null;
    try
    {
        using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["SampleDbConnectionString1"].ConnectionString))
        {
            using (SqlCommand cmd = con.CreateCommand())
            {
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = "select userID from Users where userID = '" + username + "' and password = '" + password + "'";               

                using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                {
                    result = new DataTable();
                    da.Fill(result);

                    //check if any match is found
                    if (result.Rows.Count == 1)
                    {
                        // return true to indicate that userID and password are matched.
                        return true;
                    }
                }
            }
        }
    }
    catch (Exception ex)
    {
        //Pokemon exception handling
    }

    //user id not found, lets treat him as a guest       
    return false;
}

For all the normal users this code will work fine. I can even test it using userid as sampleuser and password as samplepwd and this will work fine. For any other data except this it should say that authentication failed(since this is the only record in the table). The query that will get generated to test this input will be:

select userID from Users where userID = 'sampleuser' and password = 'samplepwd'

Now let us try to inject some SQL into this page. Let me give hacker' or 1=1-- as username and anything in the password(even leave it empty). Now the resultant SQL for this will become:

select userID from Users where userID = 'hacker' or 1=1--' and password = ''

Now when we execute this query the 1=1 clause will always return true(and the password check is commented out. Now irrespective of whatever data user has entered this will SQL return a row making this function return true and in turn authenticating the user. So What I have done now is that I gained access to the website even when I didn't knew the valid user credentials.

How can I curb this problem is something we will look into details in some time. But before that let us also look at one more example of SQL injection just to get little more understanding.

In this second example we will assume that the malicious user somehow got hold of the database schema and then he is trying to manipulate the application to find some confidential information. Lets say we have a page that is supposed to show all the products that are assigned to a user in the organization.

Let us start by looking at the Product table.

Let us now look at the code that is retrieving this data:

public DataTable GetProductsAssigner_Bad(string userID)
{
    DataTable result = null;
    try
    {
        using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["SampleDbConnectionString1"].ConnectionString))
        {
            using (SqlCommand cmd = con.CreateCommand())
            {
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = "select * from Products where AssignedTo = '" + userID + "'";

                using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                {
                    result = new DataTable();
                    da.Fill(result);
                }
            }
        }
    }
    catch (Exception ex)
    {
        //Pokemon exception handling
    }

    //user id not found, lets treat him as a guest       
    return result;
}

Now if I call this function with the proper data(as normal users would do) then this will show me the results. i.e. If I call this page for sampleuser the resulting query would be:

select * from Products where AssignedTo = 'sampleuser'

Now let me use this query string with this page: userID=' UNION SELECT 0 AS Expr1, password, userID FROM Users -- . Once this data is used with the current code this will show me all the username and passwords from the database. The reason will be quiet clear once we look into the resulting query of this input.

select * from Products where AssignedTo = '' UNION SELECT 0 AS Expr1, password, userID FROM Users --

Now we saw that how string concatenated dynamic SQL is prone to SQL injection. There are many other problems that could be created by injecting SQL. Imagine a scenario where the injected SQL is dropping tables or truncating all the tables. The problem in such cases would be catastrophic.

How to Prevent SQL Injection

ASP.NET provides us beautiful mechanism for prevention against the SQL injection. There are some thumb rules that should be followed in order to prevent injection attacks on our websites.

• User input should never be trusted. It should always be validated
• Dynamic SQL should never be created using string concatenations.
• Always prefer using Stored Procedures. 
• If dynamic SQL is needed it should be used with parametrized commands.
• All sensitive and confidential information should be stored in encrypted.
• The application should never use/access the DB with Administrator privileges. 

User input should never be trusted. It should always be validated

The basic thumb rule here is that the user input should never be trusted. First of all we should apply filters on all the input fields. If any field is supposed to take numbers then we should never accept alphabets in that. Secondly, All the inputs should be validated against a regular expression so that no SQL characters and SQL command keywords are passed to the database.

Both this filtration and validation should be done at client side using JavaScript. It would suffice for the normal user. Malicious users cans till bypass the client side validations. So to curb that all the validations should be done at server side too.

Dynamic SQL should never be created using string concatenations.

If we have dynamic SQL being created using string concatenations then we are always at the risk of getting some SQL that we are not supposed to use with the application. It is advisable to avoid the string concatenations altogether.

Always prefer using Stored Procedures.

Stored procedures are the best way of performing the DB operations. We can always be sure of that no bad SQL is being generated if we are using stored procedures. Let us create a Stored procedure for the database access required for our login page and see what is the right way of doing the database operation using stored procedure.

CREATE PROCEDURE dbo.CheckUser     
      (
      @userID varchar(20),
      @password varchar(16)
      )
AS
      select userID from Users where userID = @userID and password = @password
      RETURN

And now lets have a good version in our code using this stored procedure.

public bool IsUserAuthenticated_Good(string username, string password)
{
    DataTable result = null;
    try
    {
        using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["SampleDbConnectionString1"].ConnectionString))
        {
            using (SqlCommand cmd = con.CreateCommand())
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "CheckUser";
                cmd.Parameters.Add(new SqlParameter("@userID", username));
                cmd.Parameters.Add(new SqlParameter("@password", password));

                using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                {
                    result = new DataTable();
                    da.Fill(result);

                    //check if any match is found
                    if (result.Rows.Count == 1)
                    {
                        // return true to indicate that userID and password are matched.
                        return true;
                    }
                }
            }
        }
    }
    catch (Exception ex)
    {
        //Pokemon exception handling
    }

    //user id not found, lets treat him as a guest       
    return false;
}

If dynamic SQL is needed it should be used with parametrized commands.

If we still find our self needing the dynamic SQL in code then parametrized commands are the best way of performing such dynamic SQL business. This way we can always be sure of that no bad SQL is being generated. Let us create a parametrized command for the database access required for our Product page and see what is the right way of doing the database operation.

public DataTable GetProductsAssigner_Good(string userID)
{
    DataTable result = null;
    try
    {
        using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["SampleDbConnectionString1"].ConnectionString))
        {
            using (SqlCommand cmd = con.CreateCommand())
            {
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = "select * from Products where AssignedTo = @userID";
                cmd.Parameters.Add(new SqlParameter("@userID", userID));

                using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                {
                    result = new DataTable();
                    da.Fill(result);
                }
            }
        }
    }
    catch (Exception ex)
    {
        //Pokemon exception handling
    }

    //user id not found, lets treat him as a guest       
    return result;
}

All sensitive and confidential information should be stored in encrypted.

All the sensitive information should be stored encrypted in the database. The benefit of having this is that even if somehow the user get hold of the data he will only be able to see the encrypted values which are not easy to use for someone who doesn't know the encryption technique used by the application.

The application should never use/access the DB with Administrator privileges.

This will make sure that even if the bad SQL is being passed to the Database by some injections, the database will not allow any catastrophic actions like dropping table.

Note: Refer the sample application attached to see the working examples SQL injection and how to curb them using parametrized commands and stored procedures.

In this example i am going to describe how to combine multiple columns and records in one column in MS SQL.

Here is the scenario

I have a table having Employees names and their respective Department names,
now i want to show Employees names separated by comma into one column and respective Department name in another column.

My table schema is shown in the image below

And this is Data into table

I want output in following format

                                  Department                               FirstName
                                   IT                                             amiT,Emp1,Emp5
                                  Admin                                       Shobhit, Emp3,Emp7

and so on

To get this desired result we need to write below mentioned query

1SELECT DISTINCT
2Department,
3EmpNames = substring( ( SELECT ', ' + FirstName
4FROM Employees e2
5WHERE e2.Department = e1.Department FOR XML path(''), elements
6),2,500)
7FROM Employees e1

And the output of this SQL Query would be

Here are some important points to consider while restoring a database backup. Let us first create a sample database named test using the following code.

A new database named test will be created in your server and data and log files are created in the d: drive.

Let us assume that you regularly take backup of this database using the below code:

backup database test to disk='d:\test.bak'

Now if you want to create another database or restore this test database to a new database named testing, you can use the following code

restore database testing from disk='d:\test.bak'

However executing the code above will give you the following error

Msg 1834, Level 16, State 1, Line 2
The file 'h:\test_dat.mdf' cannot be overwritten.  It is being used by database 'test'.
Msg 3156, Level 16, State 4, Line 2
File 'test_dat' cannot be restored to 'h:\test_dat.mdf'. Use WITH MOVE to identify a valid location for the file.
Msg 1834, Level 16, State 1, Line 2
The file 'h:\test_log.ldf' cannot be overwritten.  It is being used by database 'test'.
Msg 3156, Level 16, State 4, Line 2
File 'test_log' cannot be restored to 'h:\test_log.ldf'. Use WITH MOVE to identify a valid location for the file.
Msg 3119, Level 16, State 1, Line 2
Problems were identified while planning for the RESTORE statement. Previous messages provide details.
Msg 3013, Level 16, State 1, Line 2
RESTORE DATABASE is terminating abnormally.

This error occurs because the data and log files are currently being used by the database test. So you need to give different names for those files while restoring, as shown below

restore database testing from disk='d:\test.bak'
with
move 'test_dat' to 'd:\testing.mdf',
move 'test_log' to 'd:\testing.ldf'

The above code will work fine and new database will be created with the name testing.

Just be aware of this point while restoring a backup of existing database!

In SQL Server 2012, there is a new data model, called tabular, that is part of the new feature called the Business Intelligence Semantic Model (BISM).  BISM also includes the multidimensional model (formally called the UDM).

The Tabular model is based on concepts like tables and relationships that are familiar to anyone who has a relational database background, making it easier to use than the multidimensional model.  The tabular model is a server mode you choose when installing Analysis Services.

The tabular model is an enhancement of the current PowerPivot data model experience, both of which use the Vertipaq engine.  When opening a PowerPivot for SharePoint workbook, a SSAS cube is created behind the scenes, which is why PowerPivot for SharePoint requires SSAS to be installed.

So if tabular models and PowerPivot models use the same Analysis Services engine, why are tabular models necessary when we already have PowerPivot?

There are four things that tabular models offer that PowerPivot models does not:

1. Scalability - PowerPivot has a 2 GB limit for the size of the Excel file and does not support partitions, but tabular model have no limit and support partitions.  Tabular models also support DirectQuery
2. Manageability – There are a lot of tools you can use with the tabular model that you can’t use with PowerPivot: SSMS, AMO, AMOMD, XMLA, Deployment Wizard, AMO for PowerShell, and Integration Services
3. Securability – Tabular models can use row security and dynamic security, neither of which PowerPivot supports, only Excel workbook file security
4. Professional development toolchain - Tabular models live in the Visual Studio shell.  Thus, they enjoy all the shell services such as integrated source control, msbuild integration, and Team Build integration.  PowerPivot lives in the Excel environment, thus it is limited to the extensibility model provided in Excel (which doesn’t include source control or build configuration).  Also, because tabular models live in the VS environment, build and deployment can be naturally separated

So Analysis Services can now be installed in one of three server modes: Multidimensional and Data Mining (default), PowerPivot for SharePoint, and Tabular.

More info:

When to choose tabular models over PowerPivot models

Comparing Analysis Services and PowerPivot

Feature by Server Mode or Solution Type (SSAS)