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

How To Bind XML File With Drop Down List In Visual Studio 2010


Hello Friends Today You will learn how to make new web site into visual studio 2010
Step 1: Start to visual studio 2010 click on start then programme-> then click on Visual Studio 2010 and At Start it will give you below give look.

Step 2: Now Make New Web Site INto Visual Studio 2010 and Choose C Sharp Language.

Step 3: Now Make a New Employees.xml File

Step 4: Now Click On Default.aspx Page and Go into Source File and Write Down Below Given Source

Welcome to Visual Studio 2010!

To learn about Bind of Drop Down List With XML

Step 5: Now Into Code File of Default.aspx PAge Write down below given code
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
Bind_DropDownList();
}
}
public void Bind_DropDownList()
{
DataSet ds = new DataSet();
ds.ReadXml(Server.MapPath(“Employees.xml”));
DataView dv = new DataView();
dv = ds.Tables[0].DefaultView;
dv.RowFilter = “Esal>=2000”;
dv.Sort = “Ename ASC”;
ddlEmp.DataSource = dv;
ddlEmp.DataTextField = “Ename”;
ddlEmp.DataValueField = “EmpNo”;
ddlEmp.DataBind();

}
STep 6: Now Go On Design View of Your Page and You Will Get As Given Below Look and Press F5 and enjoy in Programming World.

How to Insert Multiple Records In MSSqlserver 2008


CREATE TABLE TBEMP(ID INT,NAME VARCHAR(20))

DROP TABLE TBEMP

INSERT INTO TBEMP(ID,NAME) SELECT 2,’Project A’ UNION ALL
SELECT 4,’Project B’ UNION ALL SELECT 6,’Project C’ UNION ALL
SELECT 9,’Project D’

INSERT INTO TBEMP(ID,NAME) values(11,’Project B’),(16,’Project C’),(19,’Project D’)

SELECT *FROM TBEMP

How To Drop Tables Together With Query In MSSqlServer


Hello Friends Often you try to do How to drop all table into your database together with a single query
Then I give u solution for this Which I Needed

SELECT name INTO #tables from sys.objects where type = ‘U’
while (SELECT count(1) FROM #tables)>0
begin
declare @sql varchar(max)
declare @tbl varchar(255)
SELECT top 1 @tbl = name FROM #tables
SET @sql = ‘drop table ‘ + @tbl
exec(@sql)
DELETE FROM #tables where name = @tbl
end
DROP TABLE #tables;

SELECT *FROM #tables;

HOW TO CHECK TABLE STRUCTURE OF TABLE INTO MSSQLSERVER


Check Complete Table Structure of Your Table Into SqlServer 2005

EXEC SP_HELP TABLE NAME

Check Particular Column Structure Of Your Table SqlServer 2005

EXEC SP_COLUMNS T2, @COLUMN_NAME = ‘PROJECTID’

EXEC SP_COLUMNS ADDRESSINFO
How to Alter Datatype of Your Table Column

ALTER TABLE T1 ALTER COLUMN RoleID VARCHAR(20)

New FileUpload Control Into New Release of Ajax Or Async Fileupload Control


Ajax Control Toolkit is not novice for asp.net developers. A new version of the AJAX Control Toolkit is now available for download from the CodePlex website. This new version of the AJAX Control Toolkit contains two new controls:

1. SeaDragon Java Script Code (SJC) – The SJC control allows SeaDragon scripts to be used to display an image, and to zoom in and out of that image using mouse button keys without resizing the window. I saw the demo and it’s a really cool control.
2. AsyncFileUpload – Finally, we have a control which uploads file asynchronously. This new control enables you to perform file uploads without doing a postback. The control displays a throbber image during upload and raises client and server events when the upload is complete. Check the live demo here.

In this article, we are going to take a look at the AsyncFileUpload control.
This control will only work with .NET 3.5 or higher version.

Features of Async Fileupload Control
1. It works within the Update Panel
2. It uploads the file without any postback
3. It provides Client Side and Server side events
4. There are different coloring options for showing file upload. As for example, it shows green color if upload is successful, otherwise it shows red if there is unsuccessful upload.
5. You can show the loading image while file uploading is in progress.
Drawbacks or Shortcomings of Async FileUpload Control
1. When I was working with the control, once the file is uploaded there is no way to clear the content of file upload control.
2. I went into the source code of this control and noticed that the control stores the file in Session. It never clears the session, which means every time to navigate back to the page it loads the last file uploaded into the text box.
3. There is no way to cancel the upload.
4. There is no way to monitor the progress (how much % is completed) of uploading.
5. Uploading starts as soon as you select the file. It stores the files in the session.
Some Available Properties
1) CompleteBackColor: This property sets the background color of the control on successful upload. Default Value is “Blue”.
2) ErrorBackColor: This property sets the background color of the control on unsuccessful upload. Default Value is “Red”.
3) UploadingBackColor: This property sets the background color of the control when file uploading is in progress.
4) UploaderStyle: There are two options available for styling of the control. Traditional and modern. Default is “Traditional”.
5) ThrobberID: ID of control that is shown while the file is uploading. It will be used to display the loading/in progress image.
6) HasFile: Returns a bool value which indicates whether control has a file or not.
Available Events
1) OnClientUploadError: This is a client side event. If there is an unsuccessful upload, then the specified JavaScript function will be executed.
2) OnClientUploadStarted: This is also a client side event. The specified JavaScript function will be called when the uploading starts. This event will be called before uploading and once this function is executed, uploading will start.
3) OnClientUploadComplete: This is also a client side event. If there is a successful upload, then the specified JavaScript function will be executed.
4) onuploadedcomplete: This is a server side event which will be executed once the uploading is complete. One thing to notice over here is, as soon as you select the file, uploading starts but it remains in session. It is not stored on any physical location. In this event, we can specify the path and save the file into physical location. Things will be clear once we go through the code.

5) SaveAs(): This method saves the file on a specified path.

Let’s create a new website and add a reference of a newly downloaded AjaxControl ToolKit DLL. On default.aspx page, place a script manager and register the Ajax control toolkit DLL
Step 1 Source of Default.aspx Page is Below Given

Demo : AsyncFileUpload Control

function uploadError(sender,args)
{
document.getElementById(‘lblStatus’).innerText = args.get_fileName(), “” + args.get_errorMessage() + ““;
}

function StartUpload(sender,args)
{
document.getElementById(‘lblStatus’).innerText = ‘Uploading Started.’;
}

function UploadComplete(sender,args)
{
var filename = args.get_fileName();
var contentType = args.get_contentType();
var text = “Size of ” + filename + ” is ” + args.get_length() + ” bytes”;
if (contentType.length > 0)
{
text += ” and content type is ‘” + contentType + “‘.”;
}
document.getElementById(‘lblStatus’).innerText = text;
}

loading

Step 2: Into Code Behind File of Default.apsx page
protected void AsyncFileUpload1_UploadedComplete(object sender, AjaxControlToolkit.AsyncFileUploadEventArgs e)
{
System.Threading.Thread.Sleep(5000);
if (AsyncFileUpload1.HasFile)
{
string strPath = MapPath(“~/Uploads/”) + Path.GetFileName(e.filename);
AsyncFileUpload1.SaveAs(strPath);
}

}
Hope this will be useful for u and In Future this control will be enhanced and i will share with u it’s features.
Enjoy and Happy in programming World!!!