Thursday, January 30, 2014

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.



 



wps_clip_image-21464




wps_clip_image-21540



 



wps_clip_image-21647



 



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



2) Create a Full-Text Index



wps_clip_image-21716





wps_clip_image-21765



wps_clip_image-21804



wps_clip_image-21860



wps_clip_image-21912



wps_clip_image-21958



wps_clip_image-22007



wps_clip_image-22049



wps_clip_image-22095



3) Populate the Index



 



wps_clip_image-22147



wps_clip_image-22222



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).

Monday, January 27, 2014

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

Sunday, January 26, 2014

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

grp_by_comma_1

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



 



grp_by_comma_2



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))

Monday, January 13, 2014

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>


 



Menu

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);
        }
    }
}

Sunday, January 12, 2014

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...'.