How To Filter Data with Data View IN .Net

Step 1: Make Database and a Table With Given Script Into SqlServer2005
USE [master]
GO
IF NOT EXISTS (SELECT name FROM sys.databases WHERE name = N’SampleDb’)
BEGIN
CREATE DATABASE [SampleDb] ON PRIMARY
( NAME = N’SampleDb’, FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\SampleDb.mdf’ , SIZE = 2240KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N’SampleDb_log’, FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\SampleDb_log.LDF’ , SIZE = 504KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
END

GO
EXEC dbo.sp_dbcmptlevel @dbname=N’SampleDb’, @new_cmptlevel=90
GO
IF (1 = FULLTEXTSERVICEPROPERTY(‘IsFullTextInstalled’))
begin
EXEC [SampleDb].[dbo].[sp_fulltext_database] @action = ‘enable’
end
GO
ALTER DATABASE [SampleDb] SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [SampleDb] SET ANSI_NULLS OFF
GO
ALTER DATABASE [SampleDb] SET ANSI_PADDING OFF
GO
ALTER DATABASE [SampleDb] SET ANSI_WARNINGS OFF
GO
ALTER DATABASE [SampleDb] SET ARITHABORT OFF
GO
ALTER DATABASE [SampleDb] SET AUTO_CLOSE OFF
GO
ALTER DATABASE [SampleDb] SET AUTO_CREATE_STATISTICS ON
GO
ALTER DATABASE [SampleDb] SET AUTO_SHRINK OFF
GO
ALTER DATABASE [SampleDb] SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE [SampleDb] SET CURSOR_CLOSE_ON_COMMIT OFF
GO
ALTER DATABASE [SampleDb] SET CURSOR_DEFAULT GLOBAL
GO
ALTER DATABASE [SampleDb] SET CONCAT_NULL_YIELDS_NULL OFF
GO
ALTER DATABASE [SampleDb] SET NUMERIC_ROUNDABORT OFF
GO
ALTER DATABASE [SampleDb] SET QUOTED_IDENTIFIER OFF
GO
ALTER DATABASE [SampleDb] SET RECURSIVE_TRIGGERS OFF
GO
ALTER DATABASE [SampleDb] SET ENABLE_BROKER
GO
ALTER DATABASE [SampleDb] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO
ALTER DATABASE [SampleDb] SET DATE_CORRELATION_OPTIMIZATION OFF
GO
ALTER DATABASE [SampleDb] SET TRUSTWORTHY OFF
GO
ALTER DATABASE [SampleDb] SET ALLOW_SNAPSHOT_ISOLATION OFF
GO
ALTER DATABASE [SampleDb] SET PARAMETERIZATION SIMPLE
GO
ALTER DATABASE [SampleDb] SET READ_WRITE
GO
ALTER DATABASE [SampleDb] SET RECOVERY FULL
GO
ALTER DATABASE [SampleDb] SET MULTI_USER
GO
ALTER DATABASE [SampleDb] SET PAGE_VERIFY CHECKSUM
GO
ALTER DATABASE [SampleDb] SET DB_CHAINING OFF
USE [SampleDb]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tblSample]’) AND type in (N’U’))
BEGIN
CREATE TABLE [dbo].[tblSample](
[IntId] [int] NOT NULL,
[strName] [varchar](50) NULL,
[str#Address] [varchar](50) NULL,
[strDOB] [datetime] NULL,
[strPrice] [float] NULL,
CONSTRAINT [PK_tblSample] PRIMARY KEY CLUSTERED
(
[IntId] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END

Step 2: Make Method Into Code File As Given Below And Test All Filte Methods By Removing Comments
/*Data Filter Ways Of Data View*/
public void DataFilterSamples()
{
SqlDataAdapter adp = new SqlDataAdapter(“Select *From tblSample”, con);
DataSet ds = new DataSet();
adp.Fill(ds);
DataView dv = new DataView();
dv = ds.Tables[0].DefaultView;

/*Names Start With A aplhabet*/
//dv.RowFilter = “strName LIKE ‘A*'”;

// values that don’t start with ‘A’
//dv.RowFilter = “strName NOT LIKE ‘A*'”;

// values that contain ‘Ar’
//dv.RowFilter = “strName LIKE ‘%Ar%'”;

//dv.RowFilter = “strPrice > AVG(strPrice)”;

//dv.RowFilter = “strPrice > AVG(strPrice)”;

// select orders which have more than 5 Price
// dv.RowFilter = “COUNT(strPrice) > 5.3”;

dv.RowFilter = “SUM(strPrice) >= 500”;

// values that starts with ‘*’
//dv.RowFilter = “Name LIKE ‘[*]*'”;

// values that starts with ‘[‘
//dv.RowFilter = “Name LIKE ‘[[]*'”;

//dv.RowFilter = “strName LIKE ‘A*'”;

/*
/*Filteration of DateTime Type Column Values*/
//dv.RowFilter = “strDOB = #5/5/2010#”;

/*Filteration Of Float Values*/
//dv.RowFilter = “strPrice = 52.36”;

/*Filteration Of Float Values*/
//dv.RowFilter = String.Format(CultureInfo.InvariantCulture.NumberFormat,
// “strPrice = {0}”,52.36);

/*Filteration Of Integers Values*/

//dv.RowFilter = “intID=6”;
/*Filteration Of String Values*/
//dv.RowFilter = “strName=’Jaspreet'”;
//String.Format(“strName = ‘{0}'”, “Aman ‘A'”.Replace(“‘”, “””));

/*Filteration Of Special Characters Column name and His Value*/
//dv.RowFilter = “[str#Address]=’Patiala'”;

dv.Sort = “[str#Address] ASC”;
GridView1.DataSource = dv;
GridView1.DataBind();

}
Enjoy in the programming World!!!!

Advertisements
This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s