How to use substring and len function in sql server 2005 or 2008

I am giving one example from that you will understand How to use Substring and Len Funcation.
Suppose I have one table employee with 1000 of Record. and Having one Column Name is stImage.
This column contain link of Image. stImage contain data like http://www.example.com/images/sqlserversubstring.jpg now I want to change only domain name only replace with http://aspdotnet-example.com/images/sqlserversubstring.jpg for that we will do

update employee set stImage= 'http://aspdotnet-example.com'+
SUBSTRING(stImage,len('http://www.example.com'),len(stImage))
from employee

How to Set Identity in Sql Server 2005 or 2008 ?

You have Sql Table Which Having for Example 20 Records. And Identity also 20. Now you want insert new Record with Identity 100 then you pass 99 in below function as Parameter and Table Name and Reseed .

DBCC CHECKIDENT (TableName, reseed, 99)

what is view in sql server ?

view is one type of virtual table so view does not contain physical memory.

view is just some columns and rows combination which is selected from one or

more table.just it contain query.

 view benefits :- 

view is provide abstract layer over database tables. it's provide security.

only some non sensitive columns select in query so confidential data can not

appear that particular user.

for example:-

create view view_name select name from tbladmin.    

Using Store Procedure customize Paging in GridView Data Control

On web 1000 of records are in Database.We need only small amount 

of data i.e 10,15 etc. default paging all records are load and

it take more time. Performance of our Application decreasing. 

we create custom Optimize Paging.

In this paging we retrieve only our page size data only from database 

for that we create store procedure.

Customize or Manual Paging Store procedure     

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:  <Author,,ASPdotNet-Example.blogspot.com>
-- Create date: <Create Date,,May 20 2011>
-- Description: <Description,,>
-- =============================================

CREATE PROCEDURE [dbo].[SP_Paging]

@PageIndex int,
@PageSize int,

@TotalRecord int output,
AS
BEGIN
 
 SET NOCOUNT ON;
select @TotalRecord=count(id) from tblemp
 

select * from 

(

select row_number() over (order by [id] asc )as RowNumber,

Name,depid,Salary from tblemp

)as a 

where a.rownumber between (@PageIndex-1)*@PageSize+1 AND 

(((@PageIndex-1)*@PageSize+1)+@PageSize)-1
END

Save Successfully confirm message using store procedure(sp) Input OutPut

Insert,Update,Delete Store Procedure(SP) execution Successfully done or any Error that Can using output variable in sp.
Here I have Given Example For Insert Sp in Three Tier Architecture.

Insert Store Procedure
ALTER PROCEDURE dbo.sp_InserttblEmp
@Name varchar(50),
@depid int,
@Salary int,
@status int output
AS
insert into tblemp VALUES(@Name,@depid,@Salary);

select @status=@@ERROR

RETURN @status

LogicLayer.cs

public Boolean insert_tblemp(LogicLayer objLogicLayer)
    {
        int status;
        string Sp = "sp_InserttblEmp";
        objCmd = new SqlCommand(Sp,objCn);
        objCmd.CommandType = CommandType.StoredProcedure;
        objCmd.Parameters.Add("@Name",objLogicLayer.Name);
        objCmd.Parameters.Add("@depid", objLogicLayer.Sid);
        objCmd.Parameters.Add("@Salary", objLogicLayer.Salary);
        objCmd.Parameters.Add("@status", SqlDbType.Int).Value=1;
        objCmd.Parameters["@status"].Direction = ParameterDirection.InputOutput;
        objCn.Open();
        objCmd.ExecuteNonQuery();
        status = (int)objCmd.Parameters["@status"].Value;
        objCn.Close();
        if (status == 0)
        {
            return true;
        }
        else
        {
            return false;
        }
    }

DataLayer.cs

public class public class LogicLayer
{
{
DataLayer objLogicLayer=new LogicLayer ();
public int Sid { set; get; }
public string Name { set; get; }
public int Salary { set; get; }
public Boolean insert_tblemp(DataLayer objDataLayer)
{
return objLogicLayer.insert_tblemp(objDataLayer);
}
}

InsertForm.ASPX

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="insert.aspx.cs" Inherits="insert" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    
</head>
<body>
    <form id="form1" runat="server">
    <div>
    <table>
    <tr>
    <td>
    Name
    </td>
    <td>
        <asp:TextBox ID="txtName" runat="server"></asp:TextBox>
    </td>
    </tr>
    <tr>
    <td>
    depid
    </td>
    <td>
        <asp:TextBox ID="txtdepid" runat="server"></asp:TextBox>
    </td>
    </tr>
    <tr>
    <td>
    Salary
    </td>
    <td>
        <asp:TextBox ID="txtSalary" runat="server"></asp:TextBox>
    </td>
    </tr>
    <tr>
    <td>
   &nbsp;
        <asp:Label ID="lblMsg" runat="server" ForeColor="Red" Text="Label"></asp:Label>
    </td>
    <td>
        <asp:Button ID="btnSave" runat="server" Text="Save" onclick="btnSave_Click" />
    </td>
    </tr>
    </table>
    </div>
    </form>
</body>
</html>

InsertForm.ASPX.cs

using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;

public partial class insert : System.Web.UI.Page
{
    LogicLayer objLogicLayer = new LogicLayer();
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        { 
        
        }
    }
    protected void btnSave_Click(object sender, EventArgs e)
    {
        bool status;
        objLogicLayer.Sid = Convert.ToInt32(txtdepid.Text);
        objLogicLayer.Name = txtName.Text;
        objLogicLayer.Salary = Convert.ToInt32(txtSalary.Text);
        status = objLogicLayer.insert_tblemp(objLogicLayer);
        if (status == true)
        {
            lblMsg.Text = "Save Successfully";
        }
        else
        {
            lblMsg.Text = "Error";
        }
    }
}

SQL Update Query With inner Join and case

tblEMp

EmpID	Name	Salary	DepID
1	Sachin	1000	1
2	Mahora	2000	2
3	Afridi	3000	1

tblDep

DepID	DepName
1	D1
2	D2

Now

Now Question is Update Salary with 10% where DepName is D1.and 20% where DepName is D2. Use Join and Single Query Only.
Ans:-  update tblemp set Salary = Case tbldep.lname
           when 'D1' then Salary +(Salary*0.10)
          when 'D2' then Salary +(Salary*0.20)
          end
           from tblemp join tbldep on tblemp.depid=tbldep.depid

Why use Microsoft Sql server with Asp.net in Back end ?

There are many Database System Available e.g Microsoft Access, Oracle,Mysql,Microsoft SqlServer. The Main Point is that Microsoft SqlServer is Server So it can Handle multiple request at time. Oracle is also server but Microsoft SqlServer is in build install with .Net so with Oracle we need more Space that's why We Choice Microsoft SqlServer with ASP.NET Back End.