SQL SERVER – 2008 – Creating Full Text Catalog and Full Text Search

Create table content 
(
Pagename varchar(20) not null primary key,
URL varchar(30) not null,
Description text null,
Keywords varchar(4000) null
)
INSERT content values ('home.asp','home.asp','This is the home page','home,SQL')
GO
INSERT content values ('pagetwo.asp','/page2/pagetwo.asp','NT Magazine is great','second')
GO
INSERT content values ('pagethree.asp','/page3/pagethree.asp','SQL Magazine is the greatest','third')
GO

 

To create an Index, follow the steps:

1. Create a Full-Text Catalog

2. Create a Full-Text Index

3. Populate the Index

1) Create a Full-Text Catalog

Following Screenshot shown as AdventureWorks2008 -  follow the same in ur Db.

 

 

 

Full – Text can also be created while creating a Full-Text Index in its Wizard.

2) Create a Full-Text Index

3) Populate the Index

 

SELECT Description, Keywords
FROM content
WHERE FREETEXT(*, 'second');
SELECT Description, Keywords
FROM content
WHERE FREETEXT((Description,Keywords), 'SQL');
SELECT Description, Keywords
FROM content
WHERE CONTAINS(*, 'SQL');
SELECT Description, Keywords
FROM content
WHERE CONTAINS(*, 'SQL');
(Note : If you dont have a primary key,Full text Index cannot be created).

SQL CASE Function

DECLARE @Type varchar(20)
SET @Type = 'Programming'
SELECT 
   CASE @Type 
      WHEN 'Sql' THEN 'sqltutorials.blogspot.com' 
      WHEN 'Programming' THEN 'programmingschools.blogspot.com' 
      WHEN 'Travel' THEN 'travelyourself.blogspot.com' 
      ELSE 'Not yet categorized' 
      END

Value = programmingschools.blogspot.com





If SET @Type = 'Picture', then Return value = Not yet categorized

link : http://sqltutorials.blogspot.in/2007/06/sql-case-function.html

Dynamic Query in sql server

Create table #info (id int,Sname varchar(50))
Create table #Post (Post_id int, id int,post varchar(50))
Create table #city (city_id int,id int,city varchar(100))

   insert into #info(id,Sname) values(1,'sandip.')
   insert into #info(id,Sname) values(2,'sandip_SP')
   insert into #info(id,Sname) values(3,'sandip_Ak')
   insert into #info(id,Sname) values(4,'sandip_Sk')
   insert into #info(id,Sname) values(5,'sandipG')
   insert into #Post(Post_id ,id,post) values(1,1,'Software Dev')
   insert into #Post(Post_id ,id,post) values(2,2,'Database admin')
   insert into #Post(Post_id ,id,post) values(3,3,'tester')
   insert into #Post(Post_id ,id,post) values(4,4,'HR')
   insert into #Post(Post_id ,id,post) values(5,5,'Team Lead')
   insert into #city (city_id,id,city) values (1,1,'kolhapur')
   insert into #city (city_id,id,city) values (1,1,'sangli')
   insert into #city (city_id,id,city) values (1,1,'satara')
   insert into #city (city_id,id,city) values (1,1,'karad')
   insert into #city (city_id,id,city) values (1,1,'maharastra')

     declare @main_str varchar(max)
     declare @query_str varchar(max)
     declare @InnerJoin_str varchar(max)
     declare @Where_str varchar(max)
     set @query_str =' select * from #temp a ' 
     set @InnerJoin_str =' inner join #post b on a.id=b.id '
     set @Where_str= 'Where a.id=4 '
     set @main_str=''
     set @main_str=@query_str+@InnerJoin_str+@Where_str
     exec(@main_str)
    --Add Dynamacally where clause in sql query
    set @Where_str=@Where_str+ ' or b.post=''Software Dev'''
    set @main_str=''
    set @main_str=@query_str+@InnerJoin_str+@Where_str
    exec(@main_str)
 --Add Dynamacally joins in sql Query
   set @InnerJoin_str=@InnerJoin_str+ ' inner join #city c on a.id=c.id '
   set @main_str=''
   set @main_str=@query_str+@InnerJoin_str+@Where_str
   exec(@main_str)

  link:http://sandipgsql.blogspot.in/2013/05/dynamic-query-in-sql-server.html

select Group_by Comma Separated

Hi ,
         In this article we will see how to get a group by comma separated values sql server.

Check Below example ::

step1:: Create table and fill some data in that table.

Create table #Group_by_CommaSeprated(id int ,Hosp_name varchar(50),City varchar(50))
insert into #Group_by_CommaSeprated (id,Hosp_name,City )values(1,'sandip','mumbai')
insert into #Group_by_CommaSeprated (id,Hosp_name,City )values(1,'raj','pune')
insert into #Group_by_CommaSeprated (id,Hosp_name,City )values(1,'ajit','mumbai')
insert into #Group_by_CommaSeprated (id,Hosp_name,City )values(1,'aditya','nashik')
insert into #Group_by_CommaSeprated (id,Hosp_name,City )values(2,'rachit','bhopal')
insert into #Group_by_CommaSeprated (id,Hosp_name,City )values(2,'rakesh','bihar')
insert into #Group_by_CommaSeprated (id,Hosp_name,City )values(2,'shrikant','MP')
insert into #Group_by_CommaSeprated (id,Hosp_name,City )values(2,'chandan','MP')
insert into #Group_by_CommaSeprated (id,Hosp_name,City )values(3,'dishal','MP')
insert into #Group_by_CommaSeprated (id,Hosp_name,City )values(3,'saket','UP')
insert into #Group_by_CommaSeprated (id,Hosp_name,City )values(3,'pranav','sangli')
insert into #Group_by_CommaSeprated (id,Hosp_name,City )values(3,'aditya','kolhapur')

step 2:: Check your data .

select * from #Group_by_CommaSeprated

Step 3:: Select Query for get a group by comma separated values sql server.

SELECT id, Hosp_name = 
STUFF((SELECT ', ' + Hosp_name
FROM #Group_by_CommaSeprated b
WHERE b.id = a.id 
FOR XML PATH('')), 1, 2, ''), City = 
STUFF((SELECT ', ' + City
FROM #Group_by_CommaSeprated b 
WHERE b.id = a.id 
FOR XML PATH('')), 1, 2, '')
FROM #Group_by_CommaSeprated a
GROUP BY id

Step 4:: OUTPUT

 

link: http://sandipgsql.blogspot.in/search/label/data%20in%20comma%20separated%20string

Sql - Comma separated string into table

CREATE TABLE tblEmployee(
EmployeeId          INT,
Name                VARCHAR(30),
Gender              VARCHAR(30),
City                VARCHAR(4000))
GO
DELETE FROM tblEmployee
GO
INSERT INTO tblEmployee(EmployeeId, Name, Gender, City) VALUES(1, 'Mark', 'Male', 'London')
GO
INSERT INTO tblEmployee(EmployeeId, Name, Gender, City) VALUES(2, 'John', 'Male', 'Chennai')
GO
INSERT INTO tblEmployee(EmployeeId, Name, Gender, City) VALUES(3, 'Mary', 'FeMale', 'New york')
GO
INSERT INTO tblEmployee(EmployeeId, Name, Gender, City) VALUES(4, 'Mike', 'Male', 'Sydney')
GO

Function:
------------
CREATE FUNCTION [dbo].[CsvToIntTable] ( @StringInput VARCHAR(8000) )
RETURNS @OutputTable TABLE ( [Id] int )
AS
BEGIN
    DECLARE @String    VARCHAR(20)
    WHILE LEN(@StringInput) > 0
    BEGIN
        SET @String      = LEFT(@StringInput, 
                                ISNULL(NULLIF(CHARINDEX(',', @StringInput) - 1, -1),
                                LEN(@StringInput)))
        SET @StringInput = SUBSTRING(@StringInput,
                                     ISNULL(NULLIF(CHARINDEX(',', @StringInput), 0),
                                     LEN(@StringInput)) + 1, LEN(@StringInput))
        INSERT INTO @OutputTable ( [Id] )
        VALUES ( cast(@String as int) )
    END
    
    RETURN
END

Declare @EmpId nvarchar(100);
Set @EmpId = '1,2'
Select * 
from dbo.tblEmployee
where EmployeeId IN (SELECT * FROM [dbo].[CsvToIntTable](@EmpId))

DropDownlist with Image

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="DropDownlist_Default" %>
<!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 id="Head1" runat="server">
<title>List box</title>
<style type="text/css">
    
    #Menu1 a.popout {
background-image: url("../Images/Arrowdown.gif") !important;
    border-style :solid;
    width:80px;
    }
    
    .level2 .dynamic
    {
     border-style :solid !important;
     background-color: white;
     
     border-width:thin;
    
     margin-bottom:0px;
     width:100px;
    }
</style>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:Menu ID="Menu1" runat="server" Orientation="Horizontal">
<Items>
<asp:MenuItem Text="Home"></asp:MenuItem>
<asp:MenuItem Text="Products">
<asp:MenuItem Text="Master" ImageUrl="~/Images/Master.jpg"></asp:MenuItem>
<asp:MenuItem Text="Visa" ImageUrl="~/Images/Visa.png"></asp:MenuItem>
</asp:MenuItem>
</Items>
</asp:Menu>
</div>
</form>
</body>
</html>

 

How to handle double click event of a list box in ASP.NET?

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="DropDownlist_Default" %>
<!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 id="Head1" runat="server">
<title>List box</title>
<script language="javascript">
    function ListBox1_DoubleClick() {
        document.forms[0].ListBox1Hidden.value = "doubleclicked";
        document.forms[0].submit();
    }
</script>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:ListBox ID="lstBox" runat="server" Width="16%" CssClass="inputtext" SelectionMode="Multiple" ondblclick="ListBox1_DoubleClick()">
<asp:ListItem>1</asp:ListItem>
<asp:ListItem>2</asp:ListItem>
<asp:ListItem>3</asp:ListItem>
<asp:ListItem>4</asp:ListItem>
<asp:ListItem></asp:ListItem>
</asp:ListBox>
<asp:ListBox ID="ListBox2" runat="server" Width="16%" CssClass="inputtext" SelectionMode="Multiple" ondblclick="ListBox1_DoubleClick()">
</asp:ListBox>
<input type="hidden" name="ListBox1Hidden" />
</div>
</form>
</body>
</html>

using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
public partial class DropDownlist_Default : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (Request.Params["ListBox1Hidden"] != null && (string)Request.Params["ListBox1Hidden"] == "doubleclicked")
        {
            //This means It was double click
            Response.Write("Double Click was fired selected item is "
            + lstBox.SelectedItem.Text);
            ListBox2.Items.Add(lstBox.SelectedItem.Text);
        }
    }
}

How to avoid multiple button clicks while post back in ASP.NET

 
Many time we face the issue how to prevent user from clicking the same button before completing the post back.

A simple java script can be added for this. this will change the text of button and verify. A Dot Net 2.0 example given below

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="DropDownlist_Default" %>
<!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 id="Head1" runat="server">
    <title>CheckBoxTest</title>
</head>
<body>
    <form id="Form1" runat="server">
    <div>
        <asp:Button ID="btnAction" runat="server" 
OnCommand="ActionEvent" 
 OnClientClick="
{
var res=this.value=='Processing...'?false:true;
 this.value='Processing...';
 return res;
}"
 Text="Do Action" Width="125px" />
    </div>
    </form>
</body>
</html>

using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
public partial class DropDownlist_Default : System.Web.UI.Page
{
   
    protected void ActionEvent(object sender, CommandEventArgs e)
    { 
      //System.Threading.Thread.Sleep(10000);        
    }
}

 
The text of button is changed to 'Processing...' so that the user even knoWS something is going on and even if he clicks it will not post back as our client script returns false if the caption is 'Processing...'.