INFORMATION_SCHEMA View

Many seasoned database developers tuck away all the commonly-used INFORMATION_SCHEMA queries as templates. They're an indispensable supplement to sp_help and sp_helpText  to get handy information about your database objects, and, even if you use SQL Prompt, they're  usually the best standard way to access such information programmatically within a routine. They are ISO standard SQL and are here to stay.  Rob Sheldon goes through the basics in a timely refresher course.

Information schema views return information about the metadata in a SQL Server database. You can write code that uses these views to retrieve metadata, without worrying about changes to the system tables. For example, you can retrieve such metadata as the tables created in a database, the privileges granted on those tables, or the constraints defined on the tables.

SQL Server 2005 and 2008 automatically create 20 information schema views in every database. The views comply with the SQL-92 standard and are created in the schema INFORMATION_SCHEMA. Note, however, that in SQL Server an information schema view returns information only about those objects that the current user has permission to access. For example, if a user does not have the privileges necessary to access a particular table in a database, that user will not be able to view the columns from that table in the COLUMNS view.

When you call an information schema view, you must qualify the view name with the schema INFORMATION_SCHEMA. In addition, if you’re using the views to retrieve data from a database other than the current database, you must also qualify the name by including the database name as well as the schema name.

SQL Server uses SQL-92 metadata names for the information schema views and their columns. That means a database is referred to as a catalog, and a user-defined data type as a domain. However, most other metadata names are consistent between SQL Server and SQL-92.

The rest of the article describes each information schema view available in SQL Server 2005 and 2008 and provides examples that demonstrate how to use them. Note that these examples are based on the AdventureWorks2008 sample database in SQL Server 2008, although in many cases the statements are not specific to any one database.

CHECK_CONSTRAINTS

The CHECK_CONSTRAINTS information schema view displays the check constraints that exist in the current or specified database. The data includes the check expression that is part of the Transact-SQL constraint definition. In the following SELECT statement, I retrieve the schema, constraint, and constraint expression for each check constraint in the AdventureWorks2008 database:

SELECT CONSTRAINT_SCHEMA,

   CONSTRAINT_NAME,

   CHECK_CLAUSE

FROM INFORMATION_SCHEMA.CHECK_CONSTRAINTS

ORDER BY CONSTRAINT_SCHEMA,

   CONSTRAINT_NAME

The CHECK_CONSTRAINTS view does not include the table name. However, if the table name is part of the constraint name, as is the case in the AdventureWorks2008 database, you can order the query according to constraint name. In the example above, I order the results first by schema name and then by constraint name so that tables are grouped together. (Ed: if you use the CONSTRAINT_SCHEMA, BOL  for the 2008 version states 'The only reliable way to find the schema of a object is to query the sys.objects catalog view'. A bug?)

COLUMN_DOMAIN_USAGE

The COLUMN_DOMAIN_USAGE information schema view displays the columns that are configured with user-defined data types. For example, if your database contains a user-defined data type called IndName, the view will return a row for each column in a table or view defined with the IndName data type.

In the following SELECT statement, I retrieve the schema, table, column, and data type for each column configured with a user-defined data type in the AdventureWorks2008 database:

SELECT TABLE_SCHEMA,

   TABLE_NAME,

   COLUMN_NAME,

   DOMAIN_NAME

FROM INFORMATION_SCHEMA.COLUMN_DOMAIN_USAGE

ORDER BY TABLE_SCHEMA,

   TABLE_NAME,

   COLUMN_NAME,

   DOMAIN_NAME

COLUMN_PRIVILEGES

The COLUMN_PRIVILEGES information schema view displays information about each column-level privilege that has been granted to or granted by the current user. The view returns such details as who granted the privilege, who has been granted the privilege, the column on which the privilege is granted, and the type of privilege.

The following example retrieves the column-level privileges granted in the HumanResources schema of the AdvenureWorks2008 database:

SELECT GRANTOR,

   GRANTEE,

   TABLE_NAME,

   COLUMN_NAME,

   PRIVILEGE_TYPE

FROM INFORMATION_SCHEMA.COLUMN_PRIVILEGES

WHERE TABLE_SCHEMA = 'HumanResources'

As you can see, the statement will return the grantor, grantee, table name, column names, and privilege type for each column-level privilege.

COLUMNS

The COLUMNS information schema view displays a list of columns in the specified database. As I indicated above, this data includes only columns that can be accessed by the current user (which is true for all information schema views). The COLUMNS view returns not only the object names that qualify the column (database, schema, table, and column names), but also information such as the ordinal position, default values, nullability, and data type.

In the following example, I retrieve the columns, their data types, nullability, and default values (if any) for the columns in the vEmployee view in the AdventureWorks2008 database:

SELECT COLUMN_NAME,

   DATA_TYPE,

   IS_NULLABLE,

   COLUMN_DEFAULT

FROM INFORMATION_SCHEMA.COLUMNS

WHERE TABLE_NAME = 'vEmployee'

CONSTRAINT_COLUMN_USAGE

The CONSTRAINT_COLUMN_USAGE information schema view displays a list of columns in the current or specified database on which constraints are defined. The view returns the object names that qualify the columns (database, schema, table, and column names) as well as the object names that qualify the constraints (database, schema, and constraint names).

The following example uses the CONSTRAINT_COLUMN_USAGE view to retrieve the constraints defined on columns in the HumanResources schema of the AdventureWorks2008 database:

SELECT TABLE_NAME,

   COLUMN_NAME,

   CONSTRAINT_NAME

FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE

WHERE TABLE_SCHEMA = 'HumanResources'

ORDER BY TABLE_NAME,

   COLUMN_NAME,

   CONSTRAINT_NAME

CONSTRAINT_TABLE_USAGE

The CONSTRAINT_TABLE_USAGE information schema view displays a list of tables in the current or specified database on which constraints are defined. The view returns the object names that qualify the tables (database, schema, and table names) as well as the object names that qualify the constraints (database, schema, and constraint names).

The following example uses the CONSTRAINT_TABLE_USAGE view to retrieve the constraints defined on tables in the HumanResources schema of the AdventureWorks2008 database:

SELECT TABLE_NAME,

   CONSTRAINT_NAME

FROM INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE

WHERE TABLE_SCHEMA = 'HumanResources'

ORDER BY TABLE_NAME,

   CONSTRAINT_NAME

DOMAIN_CONSTRAINTS

The DOMAIN_CONSTRAINTS information schema view displays a row for each user-defined data type in the current or specified database that has a constraint bound to it. The information includes the object names that qualify the constraints (database, schema, and constraint names) as well as the object names that qualify the user-defined data types (database, schema, and type names). The view also returns information about constraint deferability.

In the following example, I retrieve the constraint names and user-defined data type names for each data type in the HumanResources schema that has a constraint bound to it:

SELECT CONSTRAINT_NAME,

   DOMAIN_NAME

FROM INFORMATION_SCHEMA.DOMAIN_CONSTRAINTS

WHERE CONSTRAINT_SCHEMA = 'HumanResources'

ORDER BY CONSTRAINT_NAME

DOMAINS

The DOMAINS information schema view displays a list of user-defined data types in the current or specified database. In the following example, I retrieve the name of the user-defined data types, the built-in data types on which they’re based, and the character length of the data types:

SELECT DOMAIN_NAME,

   DATA_TYPE,

   CHARACTER_MAXIMUM_LENGTH

FROM INFORMATION_SCHEMA.DOMAINS

KEY_COLUMN_USAGE

The KEY_COLUMN_USAGE information schema view displays each column that is configured as a key constraint. The information includes the object names that qualify the constraints (database, schema, and constraint names) as well as the object names that qualify the columns (database, schema, table, and column names). The view also returns the columns’ ordinal positions.

The following SELECT statement uses the KEY_COLUMN_USAGE view to retrieve the columns in the Employee table that are constrained by a key:

SELECT COLUMN_NAME,

   CONSTRAINT_NAME

FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE

WHERE TABLE_NAME = 'Employee'

PARAMETERS

The PARAMETERS information schema view displays a list of parameters for user-defined functions and stored procedures in the current or specified database. For functions, the view displays additional rows for the return values. The PARAMETERS view is one of the meatier information schema views in terms of the amount of information it returns. There are of course the details you would expect—the name of the parameter, associated routine, schema, and database. But the view also returns such details as the parameter data type, its ordinal position, and collation and character information.

In the following SELECT statement, I retrieve the routine name (stored procedure or function), the parameter name, the data type, and the mode (IN or OUT) for all routine parameters in the HumanResources schema of the AdventureWorks2008 database:

SELECT SPECIFIC_NAME,

   PARAMETER_NAME,

   DATA_TYPE,

   PARAMETER_MODE

FROM INFORMATION_SCHEMA.PARAMETERS

WHERE SPECIFIC_SCHEMA = 'HumanResources'

ORDER BY SPECIFIC_NAME,

   PARAMETER_NAME

REFERENTIAL_CONSTRAINTS

The REFERENTIAL_CONSTRAINTS information schema view displays a row for each FOREIGN KEY constraint in the current or specified database. The information includes the object names that qualify the constraints (database, schema, and constraint names) as well as details about matching conditions, update rules, and delete rules. The view also returns details specific to UNIQUE constraints.

The following example uses the REFERENTIAL_CONSTRAINTS view to retrieve the FOREIGN KEY constraints in the Production schema of the AdventureWorks2008 database:

SELECT CONSTRAINT_NAME,

   MATCH_OPTION,

   UPDATE_RULE,

   DELETE_RULE

FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS

WHERE CONSTRAINT_SCHEMA = 'Production'

ORDER BY CONSTRAINT_NAME

As you can see, I retrieve the constraint name, match option, and details about update and delete rules.

ROUTINE_COLUMNS

The ROUTINE_COLUMNS information schema view displays details about each column returned by the table-valued functions in the current or specified database. The information includes the object names that qualify the columns (database, schema, function, and column names) as well as such details as ordinal position, column default, data type, and character and collation information.

In the following example, I retrieve the columns, their data types, and their nullability for the columns returned by the ufnGetContactInformation function in the AdventureWorks2008 database.

SELECT COLUMN_NAME,

  DATA_TYPE,

  IS_NULLABLE

FROM INFORMATION_SCHEMA..ROUTINE_COLUMNS

WHERE TABLE_NAME = 'ufnGetContactInformation'

ROUTINES

The ROUTINES information schema view displays information about the stored procedures and functions in the current or specified database. The information includes the object names that qualify the routines (database, schema, and routine names), the routine definition (Transact-SQL), and character and collation information. The view also includes several columns that return only null values. These columns are reserved for future use.

The following SELECT statement uses the ROUTINES view to return the routine names and their schemas, as well as the routine definitions:

SELECT ROUTINE_SCHEMA,

   ROUTINE_NAME,

   ROUTINE_DEFINITION

FROM INFORMATION_SCHEMA.ROUTINES

SCHEMATA

The SCHEMATA information schema view displays each schema in the current or specified database. The information includes the database and schema names, the schema owner, and details about the character set. The following example retrieves the name and owner of each schema in the AdventureWorks2008 database:

SELECT SCHEMA_NAME,

   SCHEMA_OWNER

FROM AdventureWorks2008.INFORMATION_SCHEMA.SCHEMATA

TABLE_CONSTRAINTS

The TABLE_CONSTRAINTS information schema view displays a list of table constraints in the current or specified database. The view returns the object names that qualify the table (database, schema, and table names) as well as the object names that qualify the constraints (database, schema, and constraint names). The view also returns the constraint type (CHECK, UNIQUE, PRIMARY KEY, or FOREIGN KEY) and provides information about whether constraint checking is deferrable and whether it is at first deferred.

The following example uses the TABLE_CONSTRAINTS view to retrieve the constraints defined the Production schema of the AdventureWorks2008 database:

SELECT TABLE_NAME,

   CONSTRAINT_NAME,

   CONSTRAINT_TYPE

FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS

WHERE TABLE_SCHEMA = 'Production'

Notice that the SELECT statement retrieves the table names, constraint names, and constraint type for each table constraint.

TABLE_PRIVILEGES

The TABLE_PRIVILEGES information schema view displays information about each table-level privilege that has been granted to or granted by the current user. The view returns such details as who granted the privilege, who has been granted the privilege, the table on which the privilege is granted, and the type of privilege.

The following example retrieves the table-level privileges granted in the Production schema of the AdvenureWorks2008 database:

SELECT GRANTOR,

   GRANTEE,

   TABLE_NAME,

   PRIVILEGE_TYPE

FROM INFORMATION_SCHEMA.TABLE_PRIVILEGES

WHERE TABLE_SCHEMA = 'Production'

As you can see, the SELECT statement returns the names of the grantors and grantees, as well as the table names and privilege types.

TABLES

The TABLES information schema view displays a list of tables in the current or specified database. The information includes the object names that qualify the table (database, schema, and table names) as well as the table type (BASE TABLE or VIEW). In the following SELECT statement, I retrieve the tables, their associated schemas, and the table type for the AdventureWorks2008 database:

SELECT TABLE_SCHEMA,

  TABLE_NAME,

  TABLE_TYPE

FROM INFORMATION_SCHEMA.TABLES

ORDER BY TABLE_TYPE,

   TABLE_SCHEMA,

   TABLE_NAME

VIEW_COLUMN_USAGE

The VIEW_COLUMN_USAGE information schema view displays the columns defined in the views in the current or specified database. The information includes the object names that qualify the views (database, schema, and view names) as well as the objects names that qualify the source columns (database, schema, table, and column names). The following example returns a list of views along with their base tables, the tables’ schemas, and the source columns:

SELECT VIEW_NAME,

  TABLE_SCHEMA,

  TABLE_NAME,

  COLUMN_NAME

FROM INFORMATION_SCHEMA.VIEW_COLUMN_USAGE

WHERE TABLE_SCHEMA = 'Person'

ORDER BY VIEW_NAME,

   TABLE_SCHEMA,

   TABLE_NAME,

   COLUMN_NAME

VIEW_TABLE_USAGE

The VIEW_TABLE_USAGE information schema view displays the tables that are used in the views in the current or specified database. The information includes the object names that qualify the views (database, schema, and view names) as well as the objects names that qualify the base tables (database, schema, and table names). The following example returns a list of views along with their base tables and the tables’ schemas:

SELECT VIEW_NAME,

  TABLE_SCHEMA,

  TABLE_NAME

FROM INFORMATION_SCHEMA.VIEW_TABLE_USAGE

WHERE TABLE_SCHEMA = 'Person'

ORDER BY VIEW_NAME,

   TABLE_SCHEMA,

   TABLE_NAME

VIEWS

The VIEWS information schema view displays a list of views in the current or specified database. The information includes the object names that qualify the views (database, schema, and view names) as well as the view definitions (Transact-SQL). The view also returns the WITH CHECK OPTION setting and specifies whether the view is updateable.

In the following SELECT statement, I retrieve the views, their associated schemas, and the view definitions for the AdventureWorks2008 database:

SELECT TABLE_SCHEMA,

   TABLE_NAME,

   VIEW_DEFINITION

FROM INFORMATION_SCHEMA.VIEWS

ORDER BY TABLE_SCHEMA,

   TABLE_NAME

Information Schema Views

This article should have given you a good overview of information schema views in SQL Server 2005 and 2008. For more information about each view, see the view’s topic in SQL Server Books Online. (e.g. CHECK_CONSTRAINTS). There you will find a description of all the columns returned by each view. You can also find additional information in the topic “Information Schema Views (Transact-SQL).”

How to drop all tables, all views, and all stored procedures from a SQL Server 2005 Database?

It may not be a hardcore requirement on day-to-day basis to drop all tables, views and stored procedures from a SQL Server database within your environment, but it will be handy to have such a code at your end when such task is required.

There are 2 ways to accomplish this, first using undocumented stored procedure such as 'sp_MSforeachtable' as follows:

exec sp_MSforeachtable "DROP TABLE ? PRINT '? to be dropped' "

Where the results will have all of the tables to be dropped, ok how about for views & stored procedure then. Here it goes:

Create procedure Usp_DropAllSPViews

as

declare @name varchar(100)

declare @xtype char(1)

declare @sqlstring nvarchar(1000)

declare AllSPViews_cursor cursor for

SELECT sysobjects.name, sysobjects.xtype

FROM sysobjects

open AllSPViews_cursor

fetch next from AllSPViews_cursor into @name, @xtype

while @@fetch_status = 0

begin

-- obtain object type if it is a stored procedure or view

-- obtain object type if it is a view or stored procedure

if @xtype = 'PK'

begin

set @sqlstring = 'drop Constraint ' + @name

exec sp_executesql @sqlstring

set @sqlstring = ' '

end

if @xtype = 'FK'

begin

set @sqlstring = 'drop Constraint ' + @name

exec sp_executesql @sqlstring

set @sqlstring = ' '

end

if @xtype = 'V'

begin

set @sqlstring = 'drop view ' + @name

exec sp_executesql @sqlstring

set @sqlstring = ' '

end

if (@xtype = 'FN' or @xtype='TF')

begin

set @sqlstring = 'drop Function ' + @name

exec sp_executesql @sqlstring

set @sqlstring = ' '

end

if @xtype = 'U'

begin

set @sqlstring = 'drop table ' + @name

exec sp_executesql @sqlstring

set @sqlstring = ' '

end

if @xtype = 'P'

begin

set @sqlstring = 'drop procedure ' + @name

exec sp_executesql @sqlstring

set @sqlstring = ' '

end

fetch next from AllSPViews_cursor into @name, @xtype

end

close AllSPViews_cursor

deallocate AllSPViews_cursor

T/SQL Code to remove SQL Injection Values from your tables

With SQL Injection Attacks being all the rage these days, I’ve been asked a couple of times for T/SQL code to clean up the database.

So I threw this code together to clean up the data. This code will clean all the character and uni-code columns in all the user defined tables in the system. You’ll need to be dbo or sysadmin to run this without error. If you have TEXT or NTEXT columns it will through an error for those columns. Cleaning TEXT and NTEXT columns is a little more complex as you can’t use the REPLACE function on a TEXT or NTEXT datatype.

DECLARE @sql NVARCHAR(4000) DECLARE @InsertedValue NVARCHAR(1000) SET @InsertedValue = 'The Script tags which were inserted' DECLARE cur CURSOR FOR    select 'update [' + sysusers.name + '].[' + sysobjects.name + ']     set [' + syscolumns.name + '] = replace([' + syscolumns.name + '], ''' + @InsertedValue + ''', '''')'    from syscolumns    join sysobjects on syscolumns.id = sysobjects.id     and sysobjects.xtype = 'U'    join sysusers on sysobjects.uid = sysusers.uid    where syscolumns.xtype in (35, 98, 99, 167, 175, 231, 239, 241, 231)   OPEN cur   FETCH NEXT FROM cur INTO @sql   WHILE @@FETCH_STATUS = 0   BEGIN    exec (@sql)    FETCH NEXT FROM cur INTO @sql   END   CLOSE cur   DEALLOCATE cur

Remove All Tables and Constraints from a Database Using T-SQL

You've had this problem before: You don't want to drop a database completely, but you do want to drop all the tables. Try to drop your tables in the wrong order and you're slapped with an error regarding referential constraints. I've created this script to ease the burden. It first drops all the constraints, and then it drops all the tables. Let me know if this doesn't work on your SQL Server database. Here's a warning for those who didn't bother to read this paragraph:

WARNING: The following script will delete all the tables in your database.

On to the script:

DECLARE @TableName NVARCHAR(MAX)

DECLARE @ConstraintName NVARCHAR(MAX)

DECLARE Constraints CURSOR FOR

 SELECT TABLE_NAME, CONSTRAINT_NAME FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE

 

OPEN Constraints

FETCH NEXT FROM Constraints INTO @TableName, @ConstraintName

 

WHILE @@FETCH_STATUS = 0

BEGIN

 EXEC('ALTER TABLE [' + @TableName + '] DROP CONSTRAINT [' + @ConstraintName + ']')

 FETCH NEXT FROM Constraints INTO @TableName, @ConstraintName

END

 

CLOSE Constraints

DEALLOCATE Constraints

 

DECLARE Tables CURSOR FOR

 SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES

 

OPEN Tables

FETCH NEXT FROM Tables INTO @TableName

 

WHILE @@FETCH_STATUS = 0

BEGIN

 EXEC('DROP TABLE [' + @TableName + ']')

 FETCH NEXT FROM Tables INTO @TableName

END

 

CLOSE Tables

DEALLOCATE Tables

SQL SERVER – Example of DDL, DML, DCL and TCL Commands

DML

DML is abbreviation of Data Manipulation Language. It is used to retrieve, store, modify, delete, insert and update data in database.

SELECT – Retrieves data from a table
INSERT -  Inserts data into a table
UPDATE – Updates existing data into a table
DELETE – Deletes all records from a table

DDL

DDL is abbreviation of Data Definition Language. It is used to create and modify the structure of database objects in database.

CREATE – Creates objects in the database
ALTER – Alters objects of the database
DROP – Deletes objects of the database
TRUNCATE – Deletes all records from a table and resets table identity to initial value.

DCL

DCL is abbreviation of Data Control Language. It is used to create roles, permissions, and referential integrity as well it is used to control access to database by securing it.

GRANT – Gives user’s access privileges to database
REVOKE – Withdraws user’s access privileges to database given with the GRANT command

TCL

TCL is abbreviation of Transactional Control Language. It is used to manage different transactions occurring within a database.

COMMIT – Saves work done in transactions
ROLLBACK – Restores database to original state since the last COMMIT command in transactions
SAVE TRANSACTION – Sets a savepoint within a transaction

Validate Request

If you are using a freetextbox or other editor which takes all html tag then it is necessary to use validate request property in page directive or in web config and it will be set like this validate request="false" . Using this property you can set layout of your data when it will be display.

How to Generating Unique Identifire in SQL Server

We often find ourselves in the need of generating unique numbers in our database applications.

Let us quickly take a tour of how to do that using Sql server 2005.

SQL Server contains the NEWID() function. This function creates a unique value of type uniqueidentifier. We can use this function in several ways to generate unique numbers to suit our requirements :

Generate GUID's :

UniqueIndentifiers are also knows as GUID's. To generate a GUID use :

SELECT NEWID() as Guid

generates e00ab1d2-7484-410b-a55a-bcfa131a727e on my machine

Generate only digits :

SELECT ABS(CAST(CAST(NEWID() AS VARBINARY(5)) AS Bigint)) as UniqueNo

generates 427357674589

Generate fixed digit unique numbers :

At times, we may also need to generate fixed digit numbers. You can do that in the following manner :

SELECT REPLACE(STR(CAST(CAST(NEWID() AS binary(5)) AS bigint),12),0,0)

Exporting Data From AJAX GridView with Image to Excel Sheet

Introduction



Generally, exporting GridView data to Excel is a widely used concept in Asp.Net web application development. So this article glow some light on it by combining various real-time situations listed below.

• Export complete GridView data to Excel


• Export GridView data from Selected Page to Excel


• Export GridView Randomly Selected Rows To Excel


• Export GridView with Image column to Excel


• Export GridView with Decimals and DateTime columns with a pre-defined custom format to Excel

For this purpose, we place a GridView control in the Aspx Page and display some Customer information with their favorite images in a column. The GridView control will have columns such as Customer Name [Cus_Name], Customer Age [Cus_Age], Customer Email [Cus_Email], Some Random Decimals numbers [SomeDecimals], Date [Today] and Favorite Image [ImgUrl]. Specify the GridView control’s AllowPaging as true, AutoGenerateColumns as false and the DataKeyNames as “Cus_Code”. Then place three buttons, below the GridView control; change the Text of the first button as “Export All Rows”, second button as “Export Selected Rows” and third button as “Export Current Page”.

Design GridView Control

The GridView’s first column will be TemplateField column, which contains a Checkbox in the ItemTemplate section. Change the Id of the Checkbox as “chkSelect”. This is to select any GridView row and to import to the Excel Sheet. From second to fifth column will be a normal BoundField column to display Customer Name, Age, Email, decimals and date. The last column will be another TemplateField column with an Image control placed in it and the ImageUrl property will be bind to display the Customer’s favorite Image.

Include AJAX

Drag and drop a ScriptManager and an UpdatePanel into the Aspx page and move the GridView and the three buttons inside it. For exporting data into excel sheet, we need an explicit post back. Since we need to achieve this together with AJAX, we have to add PostBackTrigger into the UpdatePanel’s Triggers tag. So add three PostBackTrigger and specify the ControlID to Button1, Button2 and Button3 respectively. The UpdatePanelTrigger Collection Editor is shown below.
The complete Html source of the GridView control inside UpdatePanel will look like below.

<asp:UpdatePanel id="UpdatePanel1" runat="server">

<contenttemplate>

<TABLE width="100%"><TR><TD>

<asp:GridView id="GridView1" runat="server" Width="100%"

OnPageIndexChanging="GridView1_PageIndexChanging"

PageSize="10" AllowPaging="True"
AutoGenerateColumns="False"

OnRowDataBound="GridView1_RowDataBound"

DataKeyNames="Cus_Code">

<Columns>

<asp:TemplateField HeaderText="Select">

<ItemTemplate>

<asp:CheckBox id="chkSelect" runat="server"></asp:CheckBox>

</ItemTemplate>

</asp:TemplateField>

<asp:BoundField DataField="Cus_Name" HeaderText="Name"></asp:BoundField>

<asp:BoundField DataField="Cus_Age" HeaderText="Age"></asp:BoundField>

<asp:BoundField DataField="Cus_Email" HeaderText="Email"></asp:BoundField>

<asp:BoundField DataField="SomeDecimals" HeaderText="Decimals"></asp:BoundField>
<asp:BoundField DataField="Today" HeaderText="Date"></asp:BoundField>

<asp:TemplateField HeaderText="Favorites">

<ItemTemplate>

<asp:Image id="Image1" runat="server" ImageUrl='<%# Bind("ImgUrl")
%>' ></asp:Image>

</ItemTemplate>

</asp:TemplateField>

</Columns>

</asp:GridView>

</TD></TR>

<TR><TD>

<TABLE width="100%"><TR> <TD width="25%">

<asp:Button id="Button1" onclick="Button1_Click" runat="server"
Text="Export All Rows"></asp:Button></TD>

<TD width="25%">

<asp:Button id="Button2" onclick="Button2_Click" runat="server"
Text="Export Selected Rows"></asp:Button>

</TD>

<TD width="25%">

<asp:Button id="Button3" onclick="Button3_Click" runat="server"
Text="Export Current Page"></asp:Button>

</TD></TR>

</TABLE>

</TD></TR>

</TABLE>

</contenttemplate>

<triggers>

<asp:PostBackTrigger ControlID="Button1"></asp:PostBackTrigger>

<asp:PostBackTrigger ControlID="Button2"></asp:PostBackTrigger>

<asp:PostBackTrigger ControlID="Button3"></asp:PostBackTrigger>

</triggers>

</asp:UpdatePanel>

Before making Export to Excel functionality, we have to set up the GridView control,


1. Bind GridView control to Customer Table

2. Allow GridView paging

3. Preserve the selected checkbox values when user navigates to other pages.

1. Bind GridView control to Customer Table

private void BindGrid()

{

  string sql = "Select Cus_Code, Cus_Name, Cus_Age, Cus_Email, SomeDecimals,
ImgUrl, Today 

    from Customer Order By Cus_Name"; 

  SqlDataAdapter da = new SqlDataAdapter(sql, “YourConnectionString”); 

  DataTable dt = new DataTable(); 

  da.Fill(dt); 

  GridView1.DataSource = dt; 

  GridView1.DataBind();

}

Write a private method BindGrid to retrieve data from the Customer table, and bind it with the GridView control. This is a common method that can be used throughout the page to bind the GridView control. Call this method in the Page Load event as follows.

protected void Page_Load(object sender, EventArgs e)

{

  if (!IsPostBack)

  {

     BindGrid(); 

  }

}

2. Allow Paging by adding PageIndexChanging event

For the purpose of this article, we have collaborated both paging and sorting for a single GridView control. So every section of the source code explained below will cover both custom paging and sorting concept. Just concentrate bit more from here. For the purpose of custom paging, we are creating an instance of PagedDataSource class as follows

protected void GridView1_PageIndexChanging(object sender, GridViewPageEventArgs e)

{

  GetCheckedList(); 

  GridView1.PageIndex = e.NewPageIndex; 

  BindGrid();

}

We have already set the AllowPaging property of GridView control as “true”. So the PageIndexChanging event will have normal paging code, but it call GetCheckedList method before setting the PageIndex. The purpose GetCheckedList method is to store the “selected” Checkboxes values in a temporary ArrayList variable. So when the user navigates to other pages, we store the values of the “selected” checkbox in the ArrayList and to retain it throughout the page, we store it in a ViewState. If the user again comes to the visited page, we keep the Checkbox as “selected”, which they previously selects. This is explained clearly in the next point.

3. Preserve the “selected” Checkbox value when navigates to other pages

This is achieved by an ArrayList and a ViewState. ArrayList will have the collection of “selected” Checkboxes and it is stored in a ViewState for retrival purpose. So Declare an ArrayList variable in page scope as follows.

using System.IO;

ArrayList arrlist = new ArrayList();

In the GetCheckedList method, we initialize the ArrayList variable with the ViewState. Then we loop through the GridView rows and check if any Checkbox is selected. If a Checkbox is selected, we again check the ArrayList to know if the particular checkbox is alreay contain in it. If unavailable, then we add it to the ArrayList collection. If the Checkbox is unselected, we do the reverse function. Finally, we store the ArrayList in the ViewState.

private void GetCheckedList()

{

  if (this.ViewState["ArrList"] != null) 

  { 

      arrlist = (ArrayList)this.ViewState["ArrList"]; 

  } 



  foreach (GridViewRow gvr in GridView1.Rows) 

  { 

    CheckBox chkSelect = (CheckBox)gvr.FindControl("chkSelect"); 

    if (chkSelect.Checked) 

    { 

        if (!arrlist.Contains(GridView1.DataKeys[gvr.RowIndex].Value.ToString())) 

          arrlist.Add(GridView1.DataKeys[gvr.RowIndex].Value.ToString()); 

    } 

    else 

    { 

        if (arrlist.Contains(GridView1.DataKeys[gvr.RowIndex].Value.ToString())) 

          arrlist.Remove(GridView1.DataKeys[gvr.RowIndex].Value.ToString()); 

    } 

   } 



  if (arrlist.Count > 0) 

  { 

    this.ViewState["ArrList"] = arrlist; 

  }

}

GridView RowDataBound Event

To retain the “selected” Checkboxes to keep selected, in the RowDataBound event of the GridView control, we check whether the ArrayList contains any values to be selected. If it contains the value, then we set the Checked property of the CheckBox to true;

{

  if (e.Row.RowType == DataControlRowType.DataRow) 

  { 

    if (arrlist.Contains(GridView1.DataKeys[e.Row.RowIndex].Value.ToString())) 

    { 

      CheckBox chkSelect = (CheckBox)e.Row.FindControl("chkSelect"); 

      chkSelect.Checked = true; 

    } 

  }

}

Now if you run this page in browser, you can see the GridView control populated with data and you can navigate through the pages. Select any checkbox in any page, navigate to other pages, then select some checkbox in that page, navigate as you like, you can see the checkbox you selected previously will be kept selected.

Export GridView To Excel

This is the main objective of this article. Exporting the data in the GridView control to an Excel sheet. To achieve this, we are going to create an instance (object) of GridView class. The GridView instance will have same column as the GridView control which displayed data in the page. With the help of RenderControl method of the GridView class, we will render the html of the GridView into a HtmlTextWriter object which in turn pass it to the StringWriter object. Then by using the FileStream, we create an Excel File, write the content in the StringWriter object into it. We define some MSO Number format for the decimal and date column in the Excel, and finally, we write a small piece of code to emit it in the client browser for Download. Let us go step by step to achieve this.


Step 1: Create GridView instance and bind it with a DataTable

BoundField CusCode = new BoundField();

BoundField CusName = new BoundField();

BoundField CusAge = new BoundField();

BoundField CusEmail = new BoundField();

BoundField SomeDecimals = new BoundField();

BoundField TodayDate = new BoundField();

ImageField ImgField = new ImageField();



CusCode.HeaderText = "Customer Code";

CusName.HeaderText = "Customer Name";

CusAge.HeaderText = "Age";

CusEmail.HeaderText = "Email Address";

SomeDecimals.HeaderText = "Decimals";

TodayDate.HeaderText = "Date";

ImgField.HeaderText = "Favorites";



CusCode.DataField = "Cus_Code";

CusName.DataField = "Cus_Name";

CusAge.DataField = "Cus_Age";

CusEmail.DataField = "Cus_Email";

SomeDecimals.DataField = "SomeDecimals";

TodayDate.DataField = "Today";

ImgField.DataImageUrlField = "ImgUrl";

ImgField.DataImageUrlFormatString = “YourSiteCompleteUrl” + "{0}";

ImgField.ItemStyle.Height = Unit.Pixel(100);

ImgField.ItemStyle.Width = Unit.Pixel(140);



GridView g = new GridView();

g.AutoGenerateColumns = false;



g.RowDataBound += new GridViewRowEventHandler(g_RowDataBound);



g.Columns.Insert(0, CusCode);

g.Columns.Insert(1, CusName);

g.Columns.Insert(2, CusAge);

g.Columns.Insert(3, CusEmail);

g.Columns.Insert(4, SomeDecimals);

g.Columns.Insert(5, TodayDate);

g.Columns.Insert(6, ImgField);



g.DataSource = dtData;

g.DataBind();

In the above code, we create six BoundField and one ImageField columns, set its HeaderText property. Then we specify the DataField for each column. Note that the last column is ImageField column, so we specify the DataImageUrlField and DataImageUrlFormatString.For DataImageUrlFormatString property, you have to specify the full URL of the Image location in the above specified format. Set the ImageField Height and Width. Then create the instance of GridView class, make its AutoGenerateColumns as false, then add the BoundField and ImageField columns in the GridView instance. Next we have to the bind the GridView instance with a DataTable object ‘dtData’ that contains the which has to be exported to Excel Sheet.
And the GridView instance is initialized with a RowDataBound event. The purpose of this event is to format some columns of the GridView control while rendering it. The RowDataBound event will be as follows.

protected void g_RowDataBound(object sender, GridViewRowEventArgs e)

{

  if (e.Row.RowType == DataControlRowType.DataRow) 

  { 

    e.Row.Cells[4].Attributes.Add("class", "text"); 

    e.Row.Cells[5].Attributes.Add("class", "dateformat"); 

  }

}

It is adding some class attributes to Cell[4] – Decimals and Cell[5] – Date columns. These classes will be defined in the following sections.

Step 2: Create StringWriter instance

StringWriter strwriter = new StringWriter();

HtmlTextWriter htmlwriter = new HtmlTextWriter(strwriter);

g.RenderControl(htmlwriter);

string htmlmarkup = strwriter.ToString();

The above code is easy to understand. We create a StringWriter and HtmlTextWriter instances and render the content of GridView instance as html into HtmlTextWriter instance which can be retrieved by the StringWriter instance. Now the complete Html source of the GridView instance will be in the variable called ‘htmlmarkup’.

Step 3: Create FileStream object

Then create an Excel file named as ‘File1.xls’, with FileStream object by using the string content in the htmlmarkup. By using the Write method of the FileStream, we write the htmlmarkup values into the Excel file. The code is given below.

FileStream fs = new FileStream(Server.MapPath("File1.xls"), FileMode.Create);

try

{

  Byte[] bContent = System.Text.Encoding.GetEncoding("utf-8").GetBytes(htmlmarkup);

  fs.Write(bContent, 0, bContent.Length);

}

catch (Exception ex)

{

  Response.Write(ex.Message);

}

finally

{

  fs.Close();

  fs.Dispose();

}

Step 4: Format Decimals and Date Column in Excel Sheet


We create the Cascading Style Sheet (CSS), in mso-number-format for the Decimal and DateTime column in the Excel Sheet. We frame the style classes as a string, then Response.Write it to the current page. Previously, in the GridView instances RowDataBound event, we have added these styles to the GridView instance cells 4 and 5, which is Decimal and Date respectively.

string style = @"<style> .text { mso-number-format:0\.00; } ";

style += ".dateformat {mso-number-format:\"Short Date\"}";

style += " </script>";



Response.Write(style);

Step 5: Popup Excel Sheet Download Dialog Box

The below code is reponsible to promt a download dialog in the client browser. Note that the attachment attribute of the AppendHeader is fixed with File1.xls. If you need to pass dynamic excel file name, you have pass your dynamic excel file name here.

Response.AppendHeader("content-disposition", "attachment;filename=File1.xls");


Response.WriteFile("File1.xls");

Response.End();

Complete Code for Export GridView To Excel:

By combining all the above block of codes, we create a method called ExportGridToExcel, which takes a DataTable argument. So by passing a DataTable object as parameter to this method, you can use this method anywhere and anytime in your page.

private void ExportGridToExcel(DataTable dtData)

{ 

  
StringWriter strwriter = new StringWriter(); 

  HtmlTextWriter htmlwriter = new HtmlTextWriter(strwriter); 

  BoundField CusCode = new BoundField(); 

  BoundField CusName = new BoundField(); 

  BoundField CusAge = new BoundField(); 

  BoundField CusEmail = new BoundField(); 

  BoundField SomeDecimals = new BoundField(); 

  BoundField TodayDate = new BoundField(); 

  ImageField ImgField = new ImageField(); 



  CusCode.HeaderText = "Customer Code"; 

  CusName.HeaderText = "Customer Name"; 

  CusAge.HeaderText = "Age"; 

  CusEmail.HeaderText = "Email Address"; 

  SomeDecimals.HeaderText = "Decimals"; 

  TodayDate.HeaderText = "Date"; 

  ImgField.HeaderText = "Favorites"; 

  ImgField.ItemStyle.Height = Unit.Pixel(100); 

  ImgField.ItemStyle.Width = Unit.Pixel(140); 



  CusCode.DataField = "Cus_Code"; 

  CusName.DataField = "Cus_Name"; 

  CusAge.DataField = "Cus_Age"; 

  CusEmail.DataField = "Cus_Email"; 

  SomeDecimals.DataField = "SomeDecimals"; 

  TodayDate.DataField = "Today"; 

  ImgField.DataImageUrlField = "ImgUrl"; 

  ImgField.DataImageUrlFormatString = “YourSiteUrl” + "{0}"; 



  GridView g = new GridView(); 

  g.AutoGenerateColumns = false; 

  g.RowDataBound += new GridViewRowEventHandler(g_RowDataBound); 



  g.Columns.Insert(0, CusCode); 

  g.Columns.Insert(1, CusName); 

  g.Columns.Insert(2, CusAge); 

  g.Columns.Insert(3, CusEmail); 

  g.Columns.Insert(4, SomeDecimals); 

  g.Columns.Insert(5, TodayDate); 

  g.Columns.Insert(6, ImgField); 

  g.DataSource = dtData; 

  g.DataBind(); 

  g.RenderControl(htmlwriter); 



  string htmlmarkup = strwriter.ToString(); 

  FileStream fs = new FileStream(Server.MapPath("File1.xls"), FileMode.Create); 



  try { 

    Byte[] bContent = System.Text.Encoding.GetEncoding("utf-8").GetBytes(htmlmarkup); 

    fs.Write(bContent, 0, bContent.Length); 

  } 



  catch (Exception ex) 

  { 

    Response.Write(ex.Message); 

  } 

  finally 

  { 

    fs.Close(); 

    fs.Dispose(); 

  } 



  string style = @"<style> .text { mso-number-format:0\.00; } "; 

  style += ".dateformat {mso-number-format:\"Short Date\"}"; 

  style += " </script>"; 

  Response.Write(style); 



  Response.AppendHeader("content-disposition", "attachment;filename=File1.xls"); 

  Response.WriteFile("File1.xls"); 

  Response.End();

}  

Export Complete GridView Data To Excel:

The purpose of first button is to Export All Rows in the GridView control to the Excel Sheet. In the click event of the Button1, write the following code.

protected void Button1_Click(object sender, EventArgs e)

{

  string sql = "Select Cus_Code, Cus_Name, Cus_Age, Cus_Email, SomeDecimals,
ImgUrl, Today

   from Customer Order By Cus_Name"; 

  SqlDataAdapter da = new SqlDataAdapter(sql, “YourConnectionString”); 

  DataTable dt = new DataTable(); 

  da.Fill(dt);

  ExportGridToExcel(dt);

}

The complete Customer records are in a DataTable object and by passing it to the ExportGridToExcel method, we can export the complete GridView rows to Excel.

Export ONLY Randomly Selected GridView Rows To Excel: 

The purpose of second button is to Export any rows that is selected randomly from any page of the GridView control to an Excel Sheet. In the click event of the Button2, write the following code.

protected void Button2_Click(object sender, EventArgs e)

{

  GetCheckedList(); 

  if (this.ViewState["ArrList"] != null) 

  { 

    arrlist = (ArrayList)this.ViewState["ArrList"]; 

  } 



  string sql = "Select Cus_Code, Cus_Name, Cus_Age, Cus_Email, SomeDecimals,
ImgUrl, Today from 

  Customer Order By Cus_Name"; 



  SqlDataAdapter da = new SqlDataAdapter(sql, “YourConnectionString”); 

  DataTable dt = new DataTable(); 

  da.Fill(dt); 

  DataView dv = dt.DefaultView; 

  DataTable dtTemp = dv.ToTable().Clone(); 



  for (int i = 0; i < arrlist.Count; i++) 

  { 

    dv.RowFilter = "Cus_Code=" + arrlist[i].ToString(); 

    dtTemp.ImportRow(dv.ToTable().Rows[0]); 

  } 



  ExportGridToExcel(dtTemp);



}

Call GetCheckedList method, to include “selected” checkbox values in the current GridView page with the previously selected values. The Customer information will be in a DataTable object dt. We define a DataView and create another Temp DataTable ‘dtTemp’ which is cloned from the DataTable dt. We loop through the ArrayList, filter the selected Customer and import it to the Temp DataTabe. Finally, we pass the Temp DataTable to the ExportGridToExcel method.

Export GridView Data From Selected Page To Excel:

The purpose of third button is to Export the selected Page of the GridView control to an Excel Sheet. In the Click event of Button3

protected void Button3_Click(object sender, EventArgs e)

{

  string sql = "Select Cus_Code, Cus_Name, Cus_Age, Cus_Email, SomeDecimals,
ImgUrl, Today   

  from Customer Order By Cus_Name"; 

  SqlDataAdapter da = new SqlDataAdapter(sql, “YourConnectionString”); 

  DataTable dt = new DataTable(); 

  da.Fill(dt); 



  DataView dv = dt.DefaultView; 

  DataTable dtTemp = dv.ToTable().Clone(); 



  foreach (GridViewRow gvr in GridView1.Rows) 

  { 

    dv.RowFilter = "Cus_Code=" + GridView1.DataKeys[gvr.RowIndex].Value.ToString(); 

    dtTemp.ImportRow(dv.ToTable().Rows[0]); 

  } 



  ExportGridToExcel(dtTemp);

}

The logic to export a selected GridView page to excel is same as exporting randomly selected rows to excel. But the main difference is at the looping. Here we loop through the GridView rows, not with the ArrayList. Looping through the GridView rows, with the DataKeys value, we filter the DataView, import row by row to the Temp DataTable and pass the Temp DataTable to ExportGridToExcel method.
There is another way to achieve this, by making the AllowPaging Property of the GridView instance to true and pass the selected PageIndex value of the GridView control to the ExportGridToExcel method as second parameter, and assign it PageIndex of the GridView instance. Bit of more logic required, which we don’t want to mess you too much. But you can always try it.

Bind DropdownList with Data From SQL

Try these Examples to Bind DropdownList with Data from SQL Server with Two ways

First Example :

Step1 : Create new ASPX page and Drag DropdownList Control
Step2 : In Code behind of your Page create new method to Bind your Dropdownlist control and then in Page_Loag call your method

this is my method to Bind Dropdownlist control

public void Bind()
{
// Open the Connection
SQLConnection Con = new SqlConnection(ConfigurationManager.ConnectionStrings["Bag_ConnStr"].ConnectionString);
Con.Open();
// SQL Statement
string strSQL = null;
strSQL = "SELECT State_Name, State_Code FROM TableState ORDER BY State_Name";
// Command, Data Reader
SQLCommand Com = new SQLCommand(strSQL, Con);
SQLDataReader rdr = Com.ExecuteReader();
// Populate the Control
while (rdr.Read()) {
ListItem newListItem = new ListItem();
newListItem.Text = rdr.GetString(0);
newListItem.Value = rdr.GetString(1);
DropDownList1.Items.Add(newListItem);
}
}

protected void Page_Load(object sender, EventArgs e)
{
if(! IsPostBack)
{
Bind();
}
}

and in web.config file

<connectionStrings>
<remove name="Bag_ConnStr"/>
<add name="Bag_ConnStr" connectionString="Data Source=.;Initial Catalog=DataBase;Integrated Security=True" providerName="System.Data.SqlClient"/>
</connectionStrings>

Second Example:

Step1 : Create new ASPX page and Drag DropdownList Control
Step2 : In Code behind of your Page create new method to Bind your Dropdownlist control and then in Page_Loag call your method

this is my method to Bind Dropdownlist control

public void Bind()
{
SqlDataReader ddDR = null;
SqlConnection ddSqlConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["Bag_ConnStr"].ConnectionString);
SqlCommand ddSqlCommand = new SqlCommand("SELECT Id,Name FROM Education_Level ", ddSqlConnection);
ddSqlCommand.CommandType = CommandType.Text;
ddSqlConnection.Open();
ddDR = ddSqlCommand.ExecuteReader(CommandBehavior.CloseConnection);
ddl_Education_Level.DataSource = ddDR;
ddl_Education_Level.DataTextField = "Name";
ddl_Education_Level.DataValueField = "Id";
ddl_Education_Level.DataBind();
}

protected void Page_Load(object sender, EventArgs e)
{
if(! IsPostBack)
{
Bind();
}
}

and in web.config file

<connectionStrings>
<remove name="Bag_ConnStr"/>
<add name="Bag_ConnStr" connectionString="Data Source=.;Initial Catalog=DataBase;Integrated Security=True" providerName="System.Data.SqlClient"/>
</connectionStrings>

Note : Don't forget to Import System.Data.SqlClient

Exception in .Net Framework

Exception Types

There are a number of exception classes provided by C#, all of which inherit
from the System.Exception class. Following are some common exception classes.

SystemException

A failed run-time check;used as a base class for other.

AccessException

Failure to access a type member, such as a method or field.

ArgumentException

An argument to a method was invalid.

ArgumentNullException

A null argument was passed to a method that doesn't accept it.

ArgumentOutOfRangeException

Argument value is out of range.

ArithmeticException

Arithmetic over - or underflow has occurred.

ArrayTypeMismatchException

Attempt to store the wrong type of object in an array.

BadImageFormatException

Image is in the wrong format.

CoreException

Base class for exceptions thrown by the runtime.

DivideByZeroException

An attempt was made to divide by zero.

FormatException

The format of an argument is wrong.

IndexOutOfRangeException

An array index is out of bounds.

InvalidCastExpression

An attempt was made to cast to an invalid class.

InvalidOperationException

A method was called at an invalid time.

MissingMemberException

An invalid version of a DLL was accessed.

NotFiniteNumberException

A number is not valid.

NotSupportedException

Indicates that a method is not implemented by a class.

NullReferenceException

Attempt to use an unassigned reference.

OutOfMemoryException

Not enough memory to continue execution.

StackOverflowException

A stack has overflown.

The finally block is used to do all the clean up code. It does not support
the error message, but all the code contained in the finally block is executed
after the exception is raised. We can use this block along with try-catch and
only with catch too.

The finally block is executed even if the error is raised. Control is always
passed to the finally block regardless of how the try blocks exits.

This is shown in the following example:

int a, b = 0 ;
Console.WriteLine( "My program starts" ) ;
try
{
a = 10 / b;
}
catch ( InvalidOperationException e )
{
Console.WriteLine ( e ) ;
}
catch ( DivideByZeroException e)
{
Console.WriteLine ( e ) ;
}
finally
{
Console.WriteLine ( "finally" ) ;
}
Console.WriteLine ( "Remaining program" ) ;

The output here is:

My program starts

System.DivideByZeroException: Attempted to divide by zero.

at ConsoleApplication4.Class1.Main(String[] args) in d:\programs\consoleapplication4\class1.cs:line
51

finally

Remaining program

But then what's the difference? We could have written

Console.WriteLine ("finally");

after the catch block, and not write the finally block at all. Writing finally
did not make much of a difference. Anyway the code written after catch gets executed.
The answer to this is not clear in this program. It will be clear when we see
the try-finally and the throw statement.