ASP.NET: June 2009

Monday, June 29, 2009

Gridiview Child Controls Event Firing Sample

<div>
<asp:GridView ID="GridView1" runat="server" AllowPaging="True"
AllowSorting="True" AutoGenerateColumns="False" BackColor="White"
BorderColor="#CCCCCC" BorderStyle="None" BorderWidth="1px" CellPadding="3"
DataKeyNames="CompanyID" DataSourceID="SqlDataSource1" onrowdatabound="GridView1_RowDataBound1"
>
<RowStyle ForeColor="#000066" />
<Columns>
<asp:TemplateField ShowHeader="False">
<ItemTemplate>
<asp:CheckBox ID="chkchoose" runat="server" AutoPostBack="true" OnCheckedChanged="chkchoose_CheckedChanged" />
</ItemTemplate>
</asp:TemplateField>

<asp:TemplateField ShowHeader="False">
<ItemTemplate>
<asp:LinkButton ID="LinkButton1" runat="server" CausesValidation="False"
CommandName="Select" Text="Select"></asp:LinkButton>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="CompanyID" InsertVisible="False"
SortExpression="CompanyID">
<EditItemTemplate>
<asp:Label ID="Label1" runat="server" Text='<%# Eval("CompanyID") %>'></asp:Label>
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID="Label1" runat="server" Text='<%# Bind("CompanyID") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="TargetID" SortExpression="TargetID">
<EditItemTemplate>
<asp:TextBox ID="TextBox1" runat="server" Text='<%# Bind("TargetID") %>'></asp:TextBox>
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID="Label2" runat="server" Text='<%# Bind("TargetID") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="CompanyName" SortExpression="CompanyName">
<EditItemTemplate>
<asp:TextBox ID="TextBox2" runat="server" Text='<%# Bind("CompanyName") %>'></asp:TextBox>
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID="Label3" runat="server" Text='<%# Bind("CompanyName") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Email" SortExpression="Email">
<EditItemTemplate>
<asp:TextBox ID="TextBox3" runat="server" Text='<%# Bind("Email") %>'></asp:TextBox>
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID="Label4" runat="server" Text='<%# Bind("Email") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="URL" SortExpression="URL">
<EditItemTemplate>
<asp:TextBox ID="TextBox4" runat="server" Text='<%# Bind("URL") %>'></asp:TextBox>
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID="Label5" runat="server" Text='<%# Bind("URL") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Country" SortExpression="Country">
<EditItemTemplate>
<asp:Label ID="Label6" runat="server" Text='<%# Bind("Country") %>'></asp:Label>
</EditItemTemplate>
<ItemTemplate>
<asp:DropDownList ID="ddlCountry" runat="server" AutoPostBack="True"
OnSelectedIndexChanged="ddlCountry_SelectedIndexChanged" ></asp:DropDownList>

</ItemTemplate>
</asp:TemplateField>
</Columns>
<FooterStyle BackColor="White" ForeColor="#000066" />
<PagerStyle BackColor="White" ForeColor="#000066" HorizontalAlign="Left" />
<SelectedRowStyle BackColor="#669999" Font-Bold="True" ForeColor="White" />
<HeaderStyle BackColor="#006699" Font-Bold="True" ForeColor="White" />
</asp:GridView>
</div>
<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:MCCMSConnectionString4 %>"
SelectCommand="SELECT [CompanyID], [TargetID], [CompanyName], [Email], [URL], [Country] FROM [CMStbCompanies]"></asp:SqlDataSource>
<asp:SqlDataSource ID="SqlDataSource2" runat="server"
ConnectionString="<%$ ConnectionStrings:MCCMSConnectionString %>"
SelectCommand="SELECT [Acronym], [AcronymID] FROM [CMStbAcronyms] WHERE ([AcrCat] = @AcrCat)">
<SelectParameters>
<asp:Parameter DefaultValue="Countries" Name="AcrCat" Type="String" />
</SelectParameters>
</asp:SqlDataSource>

In Code Behind Page inorder to fire the events

protected void chkchoose_CheckedChanged(object sender, EventArgs e)
{
CheckBox chk = (CheckBox)sender;
TableCell cell = chk.Parent as TableCell;
GridViewRow item = cell.Parent as GridViewRow;

int index = item.RowIndex;
string content = item.Cells[0].Text;

if (chk.Checked)
{
Response.Write(String.Format("Checked Row {0} contains {1}", index, content));
}

}

protected void ddlCountry_SelectedIndexChanged(object sender, EventArgs e)
{
DropDownList list = (DropDownList)sender;

TableCell cell = list.Parent as TableCell;
GridViewRow item = cell.Parent as GridViewRow;

int index = item.RowIndex;
string content = item.Cells[0].Text;

Response.Write(String.Format("Row {0} contains {1}", index, content));

}

Also You bind the DropDown in Code Behind instead at the Item Template Level

protected void GridView1_RowDataBound1(object sender, GridViewRowEventArgs e)
{
if (e.Row.RowType == DataControlRowType.DataRow)
{
DropDownList ddl = (DropDownList)e.Row.FindControl("ddlCountry");

if (ddl != null)
{
ddl.DataTextField = "Acronym";
ddl.DataValueField = "AcronymID";
ddl.DataSource = SqlDataSource2;
ddl.DataBind();
}
}
}

DropDownList Controls Events In an ASP.Net DataGrid

In this article we will demonstrate how to add a data bound, event raising control into a column of an ASP.Net DataGrid. We will also see how to place a control into the header row of a DataGrid. We will demonstrate this using the DropDownList control.

The following screen shot shows a web form in design mode. We are going to query the authors table in SQL Server’s pubs table. You can see we have one column (au_fname) to display the au_fname column from the table. In the second column we have added DropDownList controls to both the header line, and in each row with a data record.

Let’s first take a look at the ASPX markup to create the grid.

<asp:DataGrid
id="DataGrid1" runat="server"
AutoGenerateColumns="False"
OnItemDataBound="DataGrid1_ItemDataBound">
<Columns>
<asp:BoundColumn DataField="au_fname" HeaderText="au_fname" />
<asp:TemplateColumn>
<HeaderTemplate>
<asp:DropDownList
ID="HeaderDropDown" Runat="server"
AutoPostBack="True"
OnSelectedIndexChanged="DropDown_SelectedIndexChanged" />
</HeaderTemplate>
<ItemTemplate>
<asp:DropDownList
ID="ItemDropDown" Runat="server"
AutoPostBack="True"
OnSelectedIndexChanged="DropDown_SelectedIndexChanged" />
</ItemTemplate>
</asp:TemplateColumn>
</asp:DataGrid>

We have a DropDownList in the header declared as HeaderDropDown, and a DropDownList in the Item template declared as ItemDropDown. Notice we set the AutoPostBack property to true. Setting AutoPostBack to true allows the form to post back to the server and raise an event each time the user changes a selection in the DropDownList control. We also assign an event handler for the SelectedIndexChanged event. Notice we are sharing the same event handler (DropDown_SelectedIndexChanged) for all DropDownList controls. We will see later in the code how we can still identity the exact control firing the event.

Before we look at the SelectedIndexChanged event handlers, let’s think about populating the drop down controls with data for the user to select. When the DataGrid binds to a data source it will create a DropDownList control to place in the header row, and also a DropDownList for each row of data rendered. We need to catch when ASP.NET creates these controls so we can populate them with data for the user to select from. The best place to do this is during the ItemDataBound event. You can see in the ASPX above we are handling this event using the DataGrid1_ItemDataBound method, shown below.

protected void DataGrid1_ItemDataBound(object sender, DataGridItemEventArgs e)
{
if(e.Item.ItemType == ListItemType.AlternatingItem ||
e.Item.ItemType == ListItemType.Item)
{
string[] options = { "Option1", "Option2", "Option3" };

DropDownList list = (DropDownList)e.Item.FindControl("ItemDropDown");
list.DataSource = options;
list.DataBind();
}
else if(e.Item.ItemType == ListItemType.Header)
{
string[] options = { "OptionA", "OptionB", "OptionC" };

DropDownList list = (DropDownList)e.Item.FindControl("HeaderDropDown");
list.DataSource = options;
list.DataBind();
}
}

ItemDataBound occurs after a DataGridItem is bound to the grid. The event fires for each row, including the header and the footer rows. We know if the item belongs to the header, the footer, or one of the items by checking the Item.ItemType property of the event. Rows of data will always have a ListItemType of Item or AlternatingItem.

We use the FindControl method to obtain a reference to the DropDownList control for each row. To learn more about using FindControl in these scenarios, see ‘In Search Of ASP.NET Controls’. We specify the control to find by name. For the header remember we specified a name of “HeaderDropDown”, while rows with data will have a name of “ItemDropDown”.

We have an array of strings to represent data for the DropDownList control to bind against. Note that this event will fire for each row of the grid, so you’ll want to make sure this method performs well. You would not want to perform a database query each time the event fires. In this example, the DropDownList control in the header binds against a different set of strings than the DropDownList controls in the item rows.

Whenever the user modifies any of the DropDownList selections in our grid, the DropDown_SelectedIndexChanged event will fire. Remember, we assigned the same event handler for all of our lists, and we set the AutoPostBack property to true so a new selection should post back to the server and raise the event immediately. The event handler is shown below.

protected void DropDown_SelectedIndexChanged(object sender, EventArgs e)
{
DropDownList list = (DropDownList)sender;

TableCell cell = list.Parent as TableCell;
DataGridItem item = cell.Parent as DataGridItem;

int index = item.ItemIndex;
string content = item.Cells[0].Text;

Response.Write(
String.Format("Row {0} contains {1}", index, content)
);

}

First, notice the sender parameter will be the DropDownList control modified by the user. By casting the object reference to a DropDownList we get a reference to the modified control and can see what the user has selected.

Secondly, notice the DropDownList control’s parent will be a TableCell of the grid. The parent of the TableCell will be a DataGridItem. Once we have a reference to the DataGridItem, we can see which row was selected by using the ItemIndex property. (Note: ItemIndex will be -1 for the header row). With the DataGridItem we can also inspect the values of other cells in the row.

The following image shows the form in action just after we made a new selection to the list in Cheryl's row.

Once you know the control invoking the event you can find almost any other piece of information you need about the grid or the underlying data source by getting to the DataGridItem object and inspecting the ItemIndex property or the collection of TableCells. With this information in hand, adding controls to the rows of your DataGrid should become a straightforward process. You can download the code for this article. Add this code to an empty web project as an existing item.

Original POST at

How to Maintain Checkbox state in a Gridview for an Header

Monday, June 22, 2009

How to find Nth Highest Salary of Employee

This question is quite a popular question and it is interesting that I have been receiving this question every other day. I have already answer this question here. “How to find Nth Highest Salary of Employee”.

Please read my article here to find Nth Highest Salary of Employee table : SQL SERVER – Query to Retrieve the Nth Maximum value

I have re-wrote the same article here with example of SQL Server 2005 Database AdventureWorks : SQL SERVER – 2005 – Find Nth Highest Record from Database Table

Just a day ago, I have received another script to get the same result from one of the blog reader Pravin Phatangare, let us see his simple method here.

For particular example of employee :

How to get 1st, 2nd, 3rd, 4th, nth topmost salary from an Employee table

The following solution is for getting 6th highest salary from Employee table ,
SELECT TOP 1 salary
FROM (
SELECT DISTINCT TOP 6 salary
FROM employee
ORDER BY salary DESC) a
ORDER BY salary
You can change and use it for getting nth highest salary from Employee table as follows
SELECT TOP 1 salary
FROM (
SELECT DISTINCT TOP n salary
FROM employee
ORDER BY salary DESC) a
ORDER BY salary
where n > 1 (n is always greater than one)

Same example converted in SQL Server 2005 to work with Database AdventureWorks.
USE AdventureWorks;
GO
SELECT TOP 1 Rate
FROM (
SELECT DISTINCT TOP 4 Rate
FROM HumanResources.EmployeePayHistory
ORDER BY Rate DESC) A
ORDER BY Rate
GO

How do you find the Second highest Salary?

This is the most common question asked in Interviews.

EMPLOYEE table has fields EMP_ID and SALARY how do you find the second highest salary?

Answer:

We can write a sub-query to achieve the result

SELECT MAX(SALARY) FROM EMPLOYEE WHERE SALARY NOT IN (SELECT MAX(SALARY) FROM EMPLOYEE)

The first sub-query in the WHERE clause will return the MAX SALARY in the table, the main query SELECT’s the MAX SALARY from the results which doesn’t have the highest SALARY.

--------------------------------------------------------------------------------

SQL SYNTAX

SQL Syntax Introduction

CREATE TABLE syntax

ALTER TABLE syntax

DROP TABLE syntax

CREATE VIEW syntax

DROP VIEW syntax

CREATE SEQUENCE syntax

DROP SEQUENCE syntax

COMPACT TABLE syntax

CREATE SCHEMA syntax

DROP SCHEMA syntax

INSERT syntax

DELETE syntax

UPDATE syntax

SELECT syntax

COMMIT and ROLLBACK syntax

CREATE USER, ALTER USER and DROP USER syntax

GRANT/REVOKE syntax

SET syntax

DESCRIBE syntax

SHOW syntax

SHUTDOWN syntax

--------------------------------------------------------------------------------

1. SQL Syntax Introduction
This section is a reference for the SQL grammar that Mckoi SQL Database supports. Mckoi SQL Database supports a subset of entry level ANSI SQL-92. This section is not intended to be a tutorial for learning SQL. For SQL books and online tutorials see the links section on the home page.

2. CREATE TABLE syntax

CREATE TABLE [ IF NOT EXISTS ] table_name
( column_declare1, column_declare2, constraint_declare1, ... )

column_declare ::= column_name type [ DEFAULT expression ]
[ NULL | NOT NULL ] [ INDEX_BLIST | INDEX_NONE ]

type ::= BIT | REAL | CHAR | TEXT | DATE | TIME |
FLOAT | BIGINT | DOUBLE | STRING | BINARY | NUMERIC |
DECIMAL | BOOLEAN | TINYINT | INTEGER | VARCHAR |
SMALLINT | VARBINARY | TIMESTAMP | LONGVARCHAR |
JAVA_OBJECT | LONGVARBINARY

constraint_declare :: = [ CONSTRAINT constraint_name ]
PRIMARY KEY ( col1, col2, ... ) |
FOREIGN KEY ( col1, col2, ... ) REFERENCES f_table [ ( col1, col2, ... ) ]
[ ON UPDATE triggered_action ] [ ON DELETE triggered_action ] |
UNIQUE ( col1, col2, ... ) |
CHECK ( expression )
[ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
[ NOT DEFERRABLE | DEFERRABLE ]

triggered_action :: =
NO ACTION | SET NULL | SET DEFAULT | CASCADE

When declaring string or binary column types the maximum size must be specified. The following example declares a string column that can grow to a maximum of 100 characters,

CREATE TABLE Table ( str_col VARCHAR(100) )
When handling strings the database will only allocate as much storage space as the string uses up. If a 10 character string is stored in str_col then only space for 10 characters will be allocated in the database. So if you need a column that can store a string of any size, use an arbitrarily large number when declaring the column. Mckoi SQL Database does not use a fixed size storage mechanism when storing variable length column data.

JAVA_OBJECT is a column type that can contain serializable Java objects. The JAVA_OBJECT type has an optional Java class definition that is used for runtime class constraint checking. The following example demonstrates creating a JAVA_OBJECT column.

CREATE TABLE ObjectTable (
obj_id NUMERIC, obj JAVA_OBJECT(java.awt.Point))
If the Java class is not specified the column defaults to java.lang.Object which effectively means any type of serializable Java object can be kept in the column.

String types may have a COLLATE clause that changes the collation ordering of the string based on a language. For example, the folling statement creates a string that can store and order Japanese text;

CREATE TABLE InternationalTable (
japanese_text VARCHAR(4000) COLLATE 'jaJP')
The 'jaJP' is an ISO localization code for the Japanese language in Japan. Other locale codes can be found in the documentation to java.text.Collate.

Unique, primary/foreign key and check integrity constraints can be defined in the CREATE TABLE statement. The following is an example of defining a table with integrity constraints.

CREATE TABLE Customer (
number VARCHAR(40) NOT NULL,
name VARCHAR(100) NOT NULL,
ssn VARCHAR(50) NOT NULL,
age INTEGER NOT NULL,

CONSTRAINT cust_pk PRIMARY KEY (number),
UNIQUE ( ssn ), // (An anonymous constraint)
CONSTRAINT age_check CHECK (age >= 0 AND age < 200)
)

3. ALTER TABLE syntax

ALTER TABLE table_name ADD [COLUMN] column_declare
ALTER TABLE table_name ADD constraint_declare
ALTER TABLE table_name DROP [COLUMN] column_name
ALTER TABLE table_name DROP CONSTRAINT constraint_name
ALTER TABLE table_name DROP PRIMARY KEY
ALTER TABLE table_name ALTER [COLUMN] column_name SET default_expr
ALTER TABLE table_name ALTER [COLUMN] column_name DROP DEFAULT

ALTER CREATE TABLE ....
ALTER is used to add / remove / modify the columns and integrity constraints of a table. The ADD [COLUMN] form adds a new column definition to the table (using the same column declaration syntax in the CREATE command). The DROP [COLUMN] form drops the column with the name from the table. ALTER [COLUMN] column_name SET default_expr alters the default value for the column. ALTER [COLUMN] column_name DROP DEFAULT removes the default value set for the column.

The following example adds a new column to a table;

ALTER TABLE Order ADD notes VARCHAR(60000) DEFAULT 'n/a'

ADD constraint_declare is used to define a new integrity constraint on a table (using the same constraint declaration syntax in the CREATE command). DROP CONSTRAINT is used to drop a named constraint from a table.

The other form of this statement is ALTER CREATE TABLE .... This alters the table to the specification of the given CREATE statement. Any columns that are in the original table are not lost provided the column name is in the new table specification. Any columns that were not in the original table are set to the default value.

The following example demonstrates this form of ALTER statement;

ALTER CREATE TABLE table
( col1 INTEGER NOT NULL UNIQUE,
col2 NUMERIC,
col3 VARCHAR(90000) )
The ALTER CREATE TABLE ... syntax is an extension to the SQL-92 standard.

4. DROP TABLE syntax

DROP TABLE [ IF EXISTS ] table_name1, table_name2, ....
Removes the table(s) from the database. The IF EXISTS clause will drop the table only if it exists. If this clause is not present an error is generated if the table does not exist. Any data that was in a dropped table is lost so use with care.

5. CREATE VIEW syntax

CREATE VIEW table_name [ ( column_name1, column_name2, ... ) ]
AS SELECT ...
Creates a new view. A view is a virtual table based on the result of a SELECT query. The content of a view may reference any number of other tables and views.

A simple example of a view follows;

CREATE VIEW ViewOfTableA AS SELECT col1 FROM TableA
A view acts like a regular table and can be queried as you would a table made with the CREATE TABLE statement. Views are read-only.

6. DROP VIEW syntax

DROP VIEW table_name
Removes a view from the database. A view can be changed by dropping and recreating it.

7. CREATE SEQUENCE syntax

CREATE SEQUENCE name
[ INCREMENT increment_value ]
[ MINVALUE minimum_value ]
[ MAXVALUE maximum_value ]
[ START start_value ]
[ CACHE cache_value ]
[ CYCLE ]
Creates a new sequence generator that can be used to generate an iterative sequence of values. Sequence generators have a number of uses including the creation of primary keys for a table. The INCREMENT, MINVALUE, MAXVALUE, START, and CACHE values are all optional.

The INCREMENT value specifies how the sequence increments each iteration. By default a sequence generator increments by 1. The MINVALUE and MAXVALUE values specify the bounds of the sequence generator. By default MINVALUE and MAXVALUE are 0 and Long.MAX_VALUE respectively. The START value specifies the first key (exclusive) of the generator. The CACHE value specifies how many keys should be cached ahead of time.

Below is an example that creates a new sequence generator called 'seq_key_1' that starts at 10 and increments by 2 each iteration;

CREATE SEQUENCE seq_key_1 INCREMENT 2 START 10
A sequence generator is accessed by a call to the NEXTVAL function. The NEXTVAL function iterates the generator and returns the next value from the sequence. The NEXTVAL function is an atomic operation and guarantees that no two identical values will be returned regardless of the frequency or concurrency of calls to the function. Below is a simple example;

SELECT NEXTVAL('seq_key_1')

8. DROP SEQUENCE syntax

DROP SEQUENCE name
Drops a sequence generator previously created with the CREATE SEQUENCE statement. A sequence generator may be changed by dropping the sequence and then recreating it.

9. COMPACT TABLE syntax

COMPACT TABLE table_name
Compacts the table data file in the file system. This removes all unused space from the table file and may rearrange the structure of the table to a form that better fits the characteristics of the data being stored.

10. CREATE SCHEMA syntax

CREATE SCHEMA schema_name
Creates a schema with the given name. By default a database has three schema initially defined, SYS_INFO, SYS_JDBC and APP. The SYS_INFO and SYS_JDBC schema contain a number of important system tables and the APP schema is the default user schema.

The following is an example of creating a new schema and changing to it;

CREATE SCHEMA my_schema;
SET SCHEMA my_schema;

11. DROP SCHEMA syntax

DROP SCHEMA schema_name
Drops the schema with the given name. A schema may only be dropped if it contains no tables. The SYS_INFO and APP schema may not be dropped.

12. INSERT syntax

INSERT INTO table_name [ ( col_name1, col_name2, .... ) ]
VALUES ( expression1_1, expression1_2, .... ),
( expression2_1, expression2_2, .... ), ....

INSERT INTO table_name [ ( col_name1, col_name2, .... ) ]
SELECT ...

INSERT INTO table_name
SET col_name1 = expression1, col_name2 = expression2, ....
This is the SQL command to insert records into a table in the database. This statement comes in three forms. The first inserts data from a VALUES clause;

INSERT INTO table ( col1, col2, col3 )
VALUES ( 10, 4 + 3, CONCAT('1', '1', 'c') ),
( 11, (28 / 2) - 7, CONCAT(col1, 'c') )
The second form is used to copy information from a SELECT query into the table specified in the INSERT statement. For example;

INSERT INTO table ( col1, col2, col3 )
SELECT id, num, description
FROM table2
WHERE description LIKE '11%'
The third form uses a list of column SET assignments. For example;

INSERT INTO table
SET col1 = 10, col2 = 4 + 3, col3 = CONCAT(col1, 'c')
If a column of the table is not specified in an INSERT the default value declared for the column is used. If no default value was declared a NULL value is inserted in the column. If the column is declared as NOT NULL the insert operation fails.

13. DELETE syntax

DELETE FROM table_name
[ WHERE expression ]
[ LIMIT limit_amount ]
Deletes all the rows from the table that match the WHERE clause. An optional LIMIT clause specifies the maximum number of matched rows to be removed. An example of using the DELETE statement;

DELETE FROM table
WHERE col3 LIKE '11%' AND col1 < 1000 LIMIT 200

14. UPDATE syntax

UPDATE table_name
SET col_name1 = expression1, col_name2 = expression2, ....
[ WHERE expression ]
[ LIMIT limit_amount ]
Updates information in a table. The SET clause is a list of assignments that describe how the columns of the data matched by the WHERE clause are to be updated. Any columns not assigned in the SET clause are left unchanged. Examples of using UPDATE;

UPDATE Employee
SET salary = salary * 1.25
WHERE name = 'Bob'

UPDATE Order
SET id = id + 3, part = CONCAT(part, '-00')
WHERE part LIKE 'PO-%'
LIMIT 10

15. SELECT syntax

SELECT [ DISTINCT | ALL ]
column_expression1, column_expression2, ....
[ FROM from_clause ]
[ WHERE where_expression ]
[ GROUP BY expression1, expression2, .... ]
[ HAVING having_expression ]
[ ORDER BY order_column_expr1, order_column_expr2, .... ]

column_expression ::= expression [ AS ] [ column_alias ]

from_clause ::= select_table1, select_table2, ...
from_clause ::= select_table1 LEFT [OUTER] JOIN select_table2 ON expr ...
from_clause ::= select_table1 RIGHT [OUTER] JOIN select_table2 ON expr ...
from_clause ::= select_table1 [INNER] JOIN select_table2 ...

select_table ::= table_name [ AS ] [ table_alias ]
select_table ::= ( sub_select_statement ) [ AS ] [ table_alias ]

order_column_expr ::= expression [ ASC | DESC ]
The SELECT statement is used to form queries for extracting information out of the database. The following example query will return the number, quantity and price of all orders for more than 5 items sorted in descending order by order number. In addition it rounds the order price to two decimal places and applies a dollar ($) sign to the output.

SELECT number, quantity, CONCAT('$', ROUND(price, 2))
FROM Order
WHERE quantity > 5
ORDER BY number DESC
The ORDER BY and GROUP BY clause may refer to a column, a column alias, or an expression. The HAVING clause is evaluated after the grouping and aggregate columns have been resolved.

For examples of using SELECT with aggregate functions see the 'Internal SQL Functions' section.

16. COMMIT and ROLLBACK syntax

COMMIT
ROLLBACK
Transactional operations for closing a transaction and either committing all the changes made or rolling back and disposing all changes. COMMIT may cause a concurrent transaction conflict exception to be thrown. If a conflict is detected the transaction is automatically rolled back. See the 'Transactions' section of the documentation for further details of how Mckoi handles transactions.

17. CREATE USER, ALTER USER and DROP USER syntax

CREATE USER username SET PASSWORD 'password'
[ SET GROUPS groups_list ]
[ SET ACCOUNT ( LOCK | UNLOCK ) ]

ALTER USER username SET PASSWORD 'password'
[ SET GROUPS groups_list ]
[ SET ACCOUNT ( LOCK | UNLOCK ) ]

DROP USER username
These are user management commands for creating/altering and dropping users in the system. Only members of the 'secure access' group are permitted to perform these operations, which includes the administrator user that is setup when the Mckoi database is created.

The following example creates a user called 'harry' with the password 'cat';

CREATE USER harry SET PASSWORD 'cat'
See the 'JDBC Driver' section for documentation on connecting to a database using a username and password to connect to a database.

18. GRANT/REVOKE syntax

GRANT privileges ON database_object TO ( PUBLIC | user_list )
[ WITH GRANT OPTION ]

REVOKE [ GRANT OPTION FOR ] privileges ON database_object
FROM ( PUBLIC | user_list )

privileges ::= priv_item1, priv_item2, ...

priv_item ::= ALL [ PRIVILEGES ] | SELECT | INSERT | UPDATE |
DELETE | REFERENCES | USAGE

database_object ::= [ TABLE ] table_name | SCHEMA schema_name

user_list ::= PUBLIC | username1, username2, ...
Grants or revokes types of access on a table or view to a user. When a table or view is created the system gives full grant options to the user that created the object. The user is given the option to grant other users selective access to the object through the GRANT and REVOKE syntax. For example, the follow statement shows how a user would grant user 'toby' permission to SELECT from a table called MyTable;

GRANT SELECT ON TABLE MyTable TO toby
The GRANT command allows granting all users access to an object. The following statement makes MyTable globally readable;

GRANT SELECT ON TABLE MyTable TO PUBLIC
If you wish to give a user the option of granting a privilege to another user, add WITH GRANT OPTION to the GRANT statement.

19. SET syntax

SET variable = expression
SET AUTO COMMIT ( ON | OFF )
SET TRANSACTION ISOLATION LEVEL ( SERIALIZABLE )
SET SCHEMA schema_name

Makes a change to the state of the connection. SET AUTO COMMIT is used to switch transaction 'auto commit mode' on or off. When auto commit mode is on the engine commits after every statement. By default, a connection starts with auto commit mode switched on. SET TRANSACTION ISOLATION LEVEL currently only supports the SERIALIZABLE isolation level. See the 'Transactions' section of the documentation for details of how Mckoi handles transactions.

SET SCHEMA is used to change the default schema of a connection.

20. DESCRIBE syntax

DESCRIBE table_name
This command provides information about the columns of the table. It shows the column names, the type / size and scale (if applicable) and other useful information.

21. SHOW syntax

SHOW engine_variable

engine_variable ::= TABLES | SCHEMA | STATUS | CONNECTIONS
Shows internal information about the database system. SHOW TABLES returns a list of tables in the database. SHOW STATUS returns debugging and statistical information about the internal state of the database engine. SHOW CONNECTIONS returns a snapshot of the current connections on the database. SHOW SCHEMA lists all the schema defined.

22. SHUTDOWN syntax

SHUTDOWN
Shuts down the database. If the database is running as a server the database shuts down cleanly and the process is stopped. If the database is embedded in a Java application it is cleanly put into a shut down state.

Only a user with the correct grants may successfully execute this command.

Thursday, June 4, 2009

Navigate to another URL by pressing enter key

Problem : When I press enter , I want to navigate to another web page. I do not want to use form onsubmit event, as I am having the form tag on the master page.

Solution:
I have a form which has a search text box. The user enters the search keywords and presses enter.
Below is the code (HTML rendering in this blog is awful, sorry about the illegible code) which does that

<script language="javascript" type="text/javascript">
function submitOnEnter(event)
{
var keycode;
if (window.event)
keycode = window.event.keyCode;
else if (e) keycode = e.which;
else return true;
if (keycode ==13)
{
location.href ="default.aspx";
location.reload;
// this always get me!! I forget to call the reload}

}
</script>

Search
onclick="default.aspx">

A much more simpler implementation can be done using JQuery.

Thanks to jackson pushpanathan for this piece of work.You can find the original code here

http://technosia.blogspot.com/2008/10/navigate-to-another-url-by-pressing.html

Monday, June 1, 2009

c # Question and Answers

1) Explain about C#?
C # is also known as c sharp. It is a programming language introduced by Microsoft.
C# contains features similar to Java and C++. It is specially designed to work with Microsoft .NET platform.

2) Explain about the rules for naming classes in C#?
These are the rules for naming classes in c sharp.
• Must begin with a letter. This letter may be followed by a sequence of letters, digits (0-9), or ‘_’. The first character in a class name cannot be a digit.
• Must not contain any embedded space or symbol like ? - + ! @ # % & * ( ) { } [ ] , : ; ‘ “ \ and/. However an underscore _ can be used wherever a space is required.
• Must not use a keyword for a class name.

3) What are the rules to be followed while naming variables in C#.
The following rules are used for naming variables in C#.
* Must begin with a letter or an underscore _ which may be followed by a sequence of letters, digits (0-9), or ‘_’. The first character in a variable name cannot be a digit.
* Must not contain any embedded space or symbol like ? - + ! @ # % & * ( ) { } [ ] , : ; ‘ “ \ and/. However an underscore _ can be used wherever a space is required.
• Must be unique
• Can have any number of characters
• Keywords cannot be used as variable names.

4) What are the different types of Data?
There are two different types of data supported by C#. They are
1) Value types: -They directly contain data. When you declare an int variable, the system allocates memory to store the value.
2) Reference type: -The reference types do not maintain data but they contain a reference to the variables, which are stored in memory. This means that if the value in the memory location is modified by one of the variables, the other variables automatically reflect the changes value

5) Explain about member functions?
A function is a set of statements that perform a specific task in response to a message. The functions of a class are called member functions in Csharp. Member functions are declared inside the class. The function declaration introduces the function in the class and the function definition contains the function code.

6) Explain about comment entry?
Comments are a part of the program and are used to explain the code. Compilers ignore comment entries. If a comment entry spans more than one line, it has to be enclosed within ‘/*’ and ‘*/’. The symbol ‘//’ treats the rest of code within the same line as a comment.

7) What are operators?
Applications use operators to process the data entered by a user. Operators like + and – are used to process variables and return a value. An operator is a set of one or more characters that is used for computations or comparisons. Operators can transform one or more data values, called operands into a new data value.

8) Explain about the break statement?
A break statement is used to exit the switch statement. This prevents the execution of the remaining case structures by ending the execution of the switch case construct. Each break statement terminates the enclosing switch statement and the flow of control. If none of the cases match the default case is invoked.

9) Define encapsulation?
Encapsulation literally means to enclose in or as if in a capsule. Encapsulation is defined as the process of enclosing one or more items within a physical or logical package. It involves preventing access to nonessential details.

10) Define access specifier with reference to class?
An access specifier defines the scope of a class member. A class member refers to the variables and functions in a class. A program can have one or more classes. You may want some members of a class to be accessible to other classes. But, you may not want some other members of the class to be accessible outside the class.

11) Describe about private access specifier?

The private access specifier allows a class to hide its member variables and member functions from other class objects and functions. Therefore, the private member of a class is not visible outside a class. If a member is declared private, only the functions of that class can access the member. Even the instance of the class cannot access its members

12) Explain about protected internal access specifier?
This specifier allows a class to hide its member variables and member functions to be accessed from other class objects and functions, except the child class, within the application. The protected internal access specifier becomes important while implementing inheritance.

13) Define parameter by value?
Pass by value is the default mechanism for passing parameters to a method. The simplest definition of a value parameter is a data type name followed by a variable name. When a method is called, a new storage location is created for each value parameter. The values of the corresponding expressions are copied into them. The expression supplied for each value parameter must be similar to the declaration of the value parameter.

14) State the methods through which parameters can be passed?
Parameters can be passed by using any one of the following mechanism.
Value: -They are sometimes called in or out parameters; therefore, the data can be transferred into the method but cannot be transferred out.
Reference: -Are sometimes called in or out parameters, therefore, the data can be transferred into the method and out again.
Output: -Are sometimes called out parameters, data can be transferred out of the method.

15) Explain about reference parameter?
A reference parameter is a reference to a memory location of a data member. Unlike a value parameter, a reference parameter does not create a new storage location. Instead a reference parameter represents the same location in memory as the variable that is supplied in the method call.

16) How do you use a structure?
A structure is a value type data type. When you want a single variable to hold related data of various data types, you can create a structure. To create a structure you use the struct keyword.

17) What is an enumerator?
Enumeration is a value data type, which means that enumeration contains its own values and cannot inherit or pass inheritance. Enumerator allows you to assign symbolic names or integral constants.

.NET Assembly

What is a .NET assembly?
An assembly is the primary building block of a .NET application and can take the form of a dynamic link library (DLL) or executable file (EXE). An assembly is a collection of functionality that is built, versioned, and deployed as a single implementation unit.

What does an assembly contain?
A .NET assembly may contain the following elements:

Assembly Manifest - Metadata that describes the assembly and its contents (see below)
Source Code - Compiled into Microsoft intermediate language (MSIL)
Type Metadata - Defines all types, their properties and methods, and most importantly, public types exported from this assembly
Resources - Icons, images, text strings and other resources
The assembly manifest is required; the other elements are optional.

What is an assembly manifest?
An assembly manifest is metadata inside an assembly that describes everything there is to know about the assembly and its contents. The manifest contains:

Strong Name - The assembly's name, version, culture, optional processor architecture, and public key (for shared assemblies)
File Contents - Name and hash of all files in the assembly
Type List - Types defined in the assembly, including public types that are exported from the assembly
Resource List - Icons, images, text strings and other resources contained in the assembly
Dependencies - Compile-time dependencies on other assemblies
Security - Permissions required for the assembly to run properly

What is a multi-file assembly?
An assembly can consist of one or more files called modules. Exactly one of these modules contains the assembly manifest. Note that the files in a multi-file assembly can reside in separate locations and are linked together with the assembly manifest.

Multi-file assemblies are rare, and Visual Studio doesn't directly support their creation. The most common reason for multi-file assemblies is when a single assembly combines code from multiple programming languages. (more)

What is the difference between a private and shared assembly?
A private assembly is used only by a single application and is stored in that application's installation folder (or subfolder therein). The name of a private assembly name must be unique within the application that uses it.

A shared assembly is used by multiple applications and is typically stored in a global folder known as the Global Assembly Cache (GAC). When building an assembly, a developer must specifically choose to build it as a shared assembly by giving it a cryptographically strong name. For example, the .NET Framework is a collection of shared assemblies.

What is the difference between an assembly and a namespace?
Namespaces are logical, whereas assemblies are physical.

A namespace is a logical naming scheme to group related types. Namespaces can contain other namespaces to form a hierarchy. The "fully qualified name" of a type is its namespace followed by its type name, separated by a period (for example, System.Windows.Forms.Button). Type names must be unique within a namespace, but the same type name can be used in different namespaces.

An assembly is a physical deployment scheme to group related types. An assembly can contain one or many namespaces. A namespace can exist in one or many assemblies.

What are assembly attributes?
Assembly attributes are values (typically set by the developer) that provide additional information about a .NET assembly. Assembly attributes are grouped as follows:

Identity Attributes - Determine the identity of an assembly: name, version, culture and flags.
Informational Attributes - Provide additional information about an assembly: company name, product name, copyright, trademark, and file version.
Manifest Attributes - Provide information in the assembly manifest: assembly title, description, alias, and configuration (such as debug or release).
Strong Name Attributes - Used to help set an assembly's strong name, including key file, key name, and whether delay signing is used.
How do I set assembly attributes?
There are two ways to set the attributes for an assembly in your development project. Using Visual Studio 2005:

Option 1: AssemblyInfo File
In the Visual Studio Solution Explorer, navigate to the Properties folder, then open the AssemblyInfo.cs file. You can directly edit the attributes in the AssemblyInfo file:

// General information about an assembly is controlled through the following
// set of attributes. Change these attribute values to modify the information
// associated with an assembly.
[assembly: AssemblyTitle( "MyAssembly" )]
[assembly: AssemblyDescription( "Description of MyAssembly" )]
[assembly: AssemblyConfiguration( "" )]
[assembly: AssemblyCompany( "MyCompany" )]
[assembly: AssemblyProduct( "MyProduct" )]
[assembly: AssemblyCopyright( "Copyright (c) 2007 MyCompany Inc" )]
[assembly: AssemblyTrademark( "MyProduct is a trademark of MyCompany Inc." )]
[assembly: AssemblyCulture( "" )]

// Setting ComVisible to false makes the types in this assembly not visible
// to COM components. If you need to access a type in this assembly from
// COM, set the ComVisible attribute to true on that type.
[assembly: ComVisible( false )]

// The following GUID is for the ID of the typelib if this project is exposed to COM
[assembly: Guid( "b7bfd909-f048-4d5b-9f33-1d0642398e4f" )]

// Version information for an assembly consists of the following four values:
// Major Version
// Minor Version
// Build Number
// Revision
[assembly: AssemblyVersion( "1.0.0.*" )]
[assembly: AssemblyFileVersion( "1.0.0.0" )]

Option 2: Project Properties
Select your assembly project in the Visual Studio Solution Explorer. Click the Properties button. Select the Application tab. Then click the Assembly Information button. The following dialog will appear, allowing you to edit the assembly attributes:

How do I access a loaded assembly?
When working with assemblies, be sure to include the Reflection namespace:

using System.Reflection;
To access your application's main assembly (i.e., the executable file):

Assembly asm = Assembly.GetExecutingAssembly();
To access an external assembly loaded by your application, call GetAssembly with a type defined in the external assembly:

Assembly asm = Assembly.GetAssembly( typeof( MyType ) );
How do I retrieve assembly attributes programmatically?
Once you have the Assembly object as shown above, you can obtain its identity attributes from its associated AssemblyName object:

AssemblyName asmName = asm.GetName();
Console.WriteLine( "Name={0}, Version={1}, Culture={2}, ProcessorArchitecture={3}",
asmName.Name, asmName.Version, asmName.CultureInfo, asmName.ProcessorArchitecture );For all other assembly attributes, you can load the attribute directly with the GetCustomAttributes method:

// assembly description attribute
string asmDesc = ((AssemblyDescriptionAttribute)asm.GetCustomAttributes(
typeof( AssemblyDescriptionAttribute ), false )[0]).Description;
// assembly title attribute
string asmTitle = ((AssemblyTitleAttribute)asm.GetCustomAttributes(
typeof( AssemblyTitleAttribute ), false )[0]).Title;
// etc.

To view all attributes for an assembly:

object[] attributes = asm.GetCustomAttributes( true );
foreach (object obj in attributes)
{
Console.WriteLine( obj.ToString() );
}

What is a strong name?
A strong name is a .NET assembly name combined with its version number and other information to uniquely identify the assembly. This allows multiple versions of the same assembly to peacefully co-exist in the global assembly cache, where shared assemblies are typically stored.

A strong name consists of five parts:

Simple Name - Usually the name of the file (without the extension) that contains the assembly
Public Key - RSA cryptographic public key that helps verify the assembly's authenticity
Version - Four-part version number, in the form of Major.Minor.Build.Revision
Culture - Target audience for the assembly, such as "neutral" (default audience), "en-us" (English - United States) or "fr" (France) etc.
Processor Architecture - Defines the assembly's format, such as MSIL (intermediate language) or x86 (binary for Intel x86 processors)
An example strong name is "Mini-Launcher, Version=0.3.612.24542, Culture=neutral, PublicKeyToken=ffa52ed9739048b4, ProcessorArchitecture=MSIL".

Why use strong names?
Strong names are required to store shared assemblies in the global assembly cache (GAC). This is because the GAC allows multiple versions of the same assembly to reside on your system simultaneously, so that each application can find and use its own version of your assembly. This helps avoid DLL Hell, where applications that may be compiled to different versions of your assembly could potentially break because they are all forced to use the same version of your assembly.

Another reason to use strong names is to make it difficult for hackers to spoof your assembly, in other words, replace or inject your assembly with a virus or malicious code.

What is a strong name key file?
A strong name key file has a .snk extension and contains a unique public-private key pair. You use the strong name key file to digitally sign your assembly (see below). Note that this type of file is not secure, as the private key in a .snk file can be easily compromised.

For added protection, Visual Studio can encrypt a strong name key file, which produces a file with the .pfx (Personal Information eXchange) extension. The .pfx file is more secure because whenever someone attempts to use the encrypted key, she will be prompted for the password.

How do I create a strong name key file for a .NET assembly?
Visual Studio 2005 makes it easy to create a strong name key file:

Select your assembly project in the Visual Studio Solution Explorer.
Click the Properties button. The project properties will appear in the main window.
Select the Signing tab:
Check the Sign the assembly checkbox.
In the Choose a strong name key file drop-down, select New. The "Create Strong Name Key" dialog appears:
In the Key file name text box, type the desired key name. Typically this is the name of your assembly but can be anything. Visual Studio will automatically append the proper file extension.
If desired, you can protect the strong name key file with a password. To do so, check the Protect my key file with a password checkbox, then enter and confirm the password.
Click the OK button.
Now when you compile your project, Visual Studio will automatically sign your assembly with the new strong name key you have just created.

Or if you prefer to use the command-line, you can create a key pair file with the strong name utility sn.exe in the .NET SDK, for example:

sn -k MyKey.snk

Then you reference that key file to when compiling your code with the C# compiler csc.exe:

csc /keyfile:MyKey.snk MyCodeFile.cs

What does it mean to sign an assembly?
.NET uses digital signatures to verify the integrity of an assembly. The signatures are generated and verified using public key cryptography, specifically the RSA public key algorithm and SHA-1 hash algorithm. The developer uses a pair of cryptographic keys: a public key, which everyone can see, and a private key, which the developer must keep secret.

To create a strong-named assembly, the developer signs the assembly with his private key when building the assembly. When the system later loads the assembly, it verifies the assembly with the corresponding public key.

How do I sign an assembly?
When you compile your assembly with a strong name key file, the compiler digitally signs the assembly:

The compiler calculates the cryptographic digest (a hash) of your assembly contents. This is known as the compile-time digest. Modifying just a single byte of your assembly will change this hash value.
The compiler encrypts the digest using the 1024-bit private key from your public-private key pair file.
The compiler then stores the encrypted digest and public key into the assembly.
How does the system verify a signed assembly?
Sometime later, when an application attempts to load your signed assembly:

The .NET assembly loader calculates the cryptographic digest of the current assembly contents. This is known as the run-time digest.
The loader extracts the stored compile-time digest and public key from the assembly.
The loader uses the public key to decrypt the compile-time digest.
The loader then compares the run-time digest with the decrypted compile-time digest to ensure they match. If not, then the assembly has been modified since you compiled it, and the assembly load fails.
This process is different when loading shared assemblies from the GAC. Because assemblies are verified when they are first installed into the GAC–and they cannot be modified while in the GAC–the .NET assembly loader does not verify an assembly when loading it from the GAC. This can improve the startup speed of your application if you load many shared assemblies.

What is delay signing?
Delay signing is signing an assembly with its strong name public key, which is freely distributable, instead of using the private key as usual. This allows developers to use and test a strong-named assembly without access to the private key. Then at a later stage (typically just before shipping the assembly), a manager or trusted keyholder must sign the assembly with the corresponding private key. (more)

How do I protect my private keys?
Private keys must remain secret. A hacker with your private key could spoof your signed assemblies by replacing or injecting them with a virus or other malicious code. There are a few strategies you can use to protect your private keys:

Password Protection. As shown above, Visual Studio will allow you to protect your strong name key file with a password.
Delay Signing. As mentioned above, delay signing enables your development team to build and test your assembly without access to the private key.
Cryptographic Container. One of the most secure ways to protect your strong name key is to store it in a secure cryptographic container (see sidebar "Protecting Your Keys" in this article).
How many private keys should I have?
There are three main strategies for how many private keys a developer should use:

One private key for all your applications and assemblies
One private key for each application (an application may have multiple assemblies)
One private key for each assembly
Which option to use depends on your security situation and risk tolerance. With option 1, it's easier to keep a single key secure, but if your one private key is compromised, then all of your assemblies are compromised. With option 3, there are more keys to manage and hence lose, but if one key is compromised, then only one of your many assemblies is compromised. I recommend option 2 or 3 to reduce your overall exposure.

Are there problems with using strong names?
Strong names are not perfect. There are some issues to consider when using strong names:

Requires Exact Match. If you use strong names, your application or library must load the assembly with the exact strong name that you specify, including version and culture. Note that you can bypass this requirement with a publisher policy (to be discussed in a future article).
Cannot Lose Private Key. If your private key is lost or stolen, the security of your assembly is compromised. You will be forced to re-issue a new assembly signed with a new public-private key pair.
Cannot Stop Full Replacement. Strong names cannot prevent a hacker from removing the strong name signature, maliciously modifying your assembly, re-signing it with his own key, and then passing off his assembly as yours. The user must have some way to ensure the public key they have from your assembly is valid and truly came from you. Note that you can use more sophisticated signing schemes (such as Authenticode) to help with this issue.

Where are shared assemblies stored?
A shared assembly is used by multiple applications. You can store shared assemblies pretty much anywhere. However, the challenge is to ensure that all dependent applications can find the shared assembly. The recommended way to ensure this is to store shared assemblies in the Global Assembly Cache.

What is the Global Assembly Cache (GAC)?
The Global Assembly Cache is a system folder (typically C:\Windows\assembly) that contains .NET shared assemblies. Companies that wish to share assemblies with others or even just among their own applications typically store these shared assemblies in the GAC. All of the .NET framework libraries are stored in the GAC.

Why should I store my shared assemblies in the GAC?
You should install assemblies in the GAC only when necessary. As a general guideline, assemblies should be kept private and stored in the application's folder unless you explicitly need to share them. There are some benefits to storing shared assemblies in the GAC:

Global Location

The GAC is the known standard location for .NET shared assemblies. When an application attempts to load an assembly, the GAC is one of the first places it looks. If there's any chance that an application outside your control may someday require access to your shared assembly, you should install your assembly in the GAC so the application is sure to find it.

Security
The GAC is a system folder typically protected by administrator rights. Once an assembly is installed in the GAC, it cannot be easily modified. Also, assemblies stored in the GAC must be signed with a cryptographic key. These protections make it difficult to spoof your assembly, in other words, replace or inject your assembly with a virus or malicious code.

Version Management
.NET allows multiple versions of the same assembly to reside in the GAC so that each application can find and use the version of your assembly to which it was compiled. This helps avoid DLL Hell, where applications that may be compiled to different versions of your assembly could potentially break because they are all forced to use a single version of your assembly.

Faster Loading
The system verifies assemblies when they are first installed in the GAC, eliminating the need to verify an assembly each time it is loaded from the GAC. This can improve the startup speed of your application if you load many shared assemblies.

Why would I avoid the GAC?
The GAC should contain "global" shared assemblies only, so there are many instances when you would NOT install an assembly in the GAC:

The assembly is private to your application and not to be shared with other applications.
You want to use XCOPY or FTP copy to install a .NET application to a single folder. This eliminates the need to access the Registry and GAC and does not require administrator rights.
The assembly is not strong-named or you do not want tight version control.
COM interop and unmanaged code do not require the GAC.
How do I see assemblies installed in the GAC?
The .NET Framework includes an Assembly Cache Viewer. Open Windows Explorer, enter %windir%\assembly in the address bar, and all global assemblies will appear in a special view that shows the assembly name, version, culture, public key token, and processor architecture.

Can I install multiple versions of the same assembly in the GAC?
Yes. Normally you would not be able to have two files with the same name in a Windows folder, but the GAC is a special folder that stores its contents by strong name. Hence, two assemblies with the same name but different versions or cultures may coexist in the GAC.

How do I add/remove assemblies from the GAC?
Assemblies added to the GAC must be signed with a strong name. There are multiple ways to add/remove assemblies from the GAC:

Windows Installer
The preferred way to add/remove assemblies from the GAC is with Microsoft Windows Installer 2.0. Visual Studio includes a limited version of Windows Installer, and most major setup programs such as InstallShield also use Windows Installer. There are benefits to using Windows Installer:

Windows Installer provides a simple interface for developers to add/remove shared assemblies in the GAC and can handle private assemblies as well.
Installer provides a familiar interface and setup experience for the user.
Installer can also install application shortcuts and supporting files such as ReadMe and license agreements and can run other installation programs and scripts.
Installer registers and tracks references to assemblies installed in the GAC to determine which assemblies are still required.
Installer can repair and patch assemblies and rollback unsuccessful installations.
Installer can install assemblies on-demand as they are needed by applications.
GAC Utility
The .NET developer's kit includes a command line utility GACutil.exe to interact with the GAC. This utility is intended for use in a development environment only and should not be used to install assemblies on a client PC because:

The GACutil license agreement states that it is not freely distributable.
GACutil is part of the .NET SDK, which may not be installed on many target PCs.
GACutil lacks many important features found in Windows Installer such as assembly repair and rollback.
Assembly Cache Viewer
Using the Assembly Cache Viewer shown above, you can drag & drop assemblies from any folder into the GAC and also delete assemblies installed in the GAC.

.NET Framework Configuration Administrative Tool
To access the .NET Framework Configuration Tool:

Click Start > Control Panel.
Double-click on Administrative Tools.
Double-click on Microsoft .NET Framework 2.0 Configuration.
Ensure My Computer is selected in the tree.
In the Tasks group, click the Manage the Assembly Cache link.
Two links appear, enabling you to view and add assemblies in the GAC.
How do I access the GAC programmatically?
You can access the GAC from code with the fusion.dll library. Here is an excellent C# wrapper for the GAC.

You are strongly advised NOT to access the GAC from code unless you are creating an administrative or setup tool. The Fusion APIs expose your application to the inner workings of assembly binding and may cause your application to fail on future .NET versions.

How do I add my shared assembly to the Visual Studio "Add Reference" dialog?
If you add an assembly to the GAC, it will NOT automatically appear in the Visual Studio "Add Reference" dialog; instead you must add your assembly manually.

How do I move the GAC?
When installing .NET, you cannot configure the GAC location, however you can move the GAC after it is installed. See this article and scroll down to "Relocating the GAC."

Constraints

CONSTRAINTS

A constraint is a property assigned to a column or the set of columns in
a table that prevents certain types of inconsistent data values from being placed
in the column(s). Constraints are used to enforce the data integrity.
This ensures the accuracy and reliability of the data in the database.

The following categories of the data integrity exist:

Entity Integrity : Entity Integrity ensures that there are no duplicate rows
in a table.

Domain Integrity : Domain Integrity enforces valid entries for a given column
by restricting the type, the format, or the range of possible values.

Referential integrity : Referential integrity ensures that rows cannot be
deleted, which are used by other records (for example,corresponding data values
between tables will be vital).

User-Defined Integrity : User-Defined Integrity enforces some specific business
rules that do not fall into entity, domain, or referential integrity categories.
Each of these categories of the data integrity can be enforced by the appropriate
constraints.

Microsoft SQL Server supports the following constraints:

PRIMARY KEY

UNIQUE

FOREIGN KEY

CHECK

NOT NULL

A PRIMARY KEY constraint is a unique identifier for a row within
a database table. Every table should have a primary key constraint to uniquely identify
each row and only one primary key constraint can be created for each table. The
primary key constraints are used to enforce entity integrity.

A UNIQUE constraint enforces the uniqueness of the values in a
set of columns, so no duplicate values are entered. The unique key constraints are
used to enforce entity integrity as the primary key constraints.

Each table can have only one primary key. If there are multiple UNIQUE identifiers
for a multiple columns, such column pairs are often referred to as alternate keys
or candidate keys (these terms are not used by SQL Server). In practice, one of
two columns is logically promoted to primary key using the PRIMARY KEY constraint,
and the other is usually declared by a UNIQUE constraint. Internally, PRIMARY KEY
and UNIQUE constraints are handled almost identically.

Here two ways to create a table:

Create table customer(cust_id int IDENTITY
not null PRIMARY KEY, cust_name varchar(30) not null)

Create table customer(cust_id int IDENTITY
not null, cust_name varchar(30) not null, PRIMARY KEY(cust_id))

A FOREIGN KEY constraint prevents any actions that would destroy
link between tables with the corresponding data values. A foreign key in one table
points to a primary key in another table. Foreign keys prevent actions that would
leave rows with foreign key values when there are no primary keys with that value.
The foreign key constraints are used to enforce referential integrity.

A FOREIGN KEY is a column whose values are derived from the PRIMARY KEY or UNIQUE
KEY of some other table.

By using ON DELETE CASCADE option and if a user deletes a record in
the master table, all corresponding recording in the detail table along with the
record in the master table will be deleted.

Create table orders(order_id
int not null PRIMARY KEY, cust_id int not null REFERENCES customer(cust_id) ON DELETE
CASCADE)

A table can have a maximum of 253 FOREIGN KEY references. This limit is derived
from the internal limit of 256 tables in a single query. If you’re dropping tables,
you must drop all the referencing tables or drop the referencing FOREIGN KEY constraint
before dropping the referenced table.

DROP TABLE orders DROP TABLE
customer

A CHECK constraint is used to limit the values that can be
placed in a column. The check constraints are used to enforce domain integrity.

Check constraints allow us to define an expression for a table that must not evaluate
to FALSE for a data modification statement to succeed.

Check constraints deal only with some logical expression for the specific row already
being operated on, so no additional I/O required.

Create table employee(emp_id int not null PRIMARY KEY

CHECK(emp_id between
0 and 1000), emp_name varchar(30) not null constraint no_nums

CHECK(emp_name
not like ‘%[0-9]%’), entered_date datetime null

CHECK(entered_date>=CURRENT_TIMESTAMP),
dept_no int CHECK(dept_no < 0 and dept_no > 100))

A NOT NULL constraint enforces that the column will not accept
null values. The not null constraints are used to enforce domain integrity, as the
check constraints.

A DEFAULT Constraints: A default allows you to
specify a constant value, NULL or the run-time value of a system function if no
known value exists or if the column is missing in an INSERT statement.

Create table employee(emp_id int not null PRIMARY KEY DEFAULT 1000 CHECK(emp_id
between 0 and 1000), emp_name varchar(20) DEFAULT 'Radha Krishna')

Insert into employee values(DEFAULT, DEFAULT)

The order of Integrity checks is as follows:

1. Defaults are applied as appropriate.

2. NOT NULL violations are raised.

3. CHECK constraints are evaluated.

4. FOREIGN KEY checks of referencing tables are applied.

5. FOREIGN KEY checks of referenced tables are applied.

6. UNIQUE/PRIMARY KEY is checked for correctness.

7. Triggers fire.

You can add constraints to an existing table by using the ALTER TABLE statement.

The following example adds a pk_employee primary key constraint on an employee table:

ALTER TABLE employee

ADD CONSTRAINT pk_employee PRIMARY KEY
(EmployeeId)

You can add the primary or unique key constraint into an existing table only when
there are no duplicate rows in the table. You can drop constraints in an existing
table by using the ALTER TABLE statement.

The following example drops the pk_employee primary key constraint in the employee
table:

ALTER TABLE employee
DROP CONSTRAINT pk_employee

Sometimes you need to perform some actions that require the FOREIGN KEY or CHECK
constraints be disabled, for example, your company do not hire foreign employees,
you made the appropriate constraint, but the situation was changed and your boss
need to hire the foreign employee, but only this one.

In this case, you need to disable the constraint by using the ALTER TABLE statement.

After these actions will be performed, you can re-enable the FOREIGN KEY and CHECK
constraints by using the ALTER TABLE statement.

The following example disables the check_sale constraint in the employee table and
enables this constraint later:

-- disable the check_sale constraint in the employee table

ALTER TABLE employee NOCHECK CONSTRAINT check_sale

-- enable the check_sale constraint in the employee table

ALTER TABLE employee CHECK CONSTRAINT check_sale

ALTERING A TABLE: Using the ALTER table command, we can make following types
of changes to an existing table.

Change the datatype or NULL property of a single column.

Add one or more new columns, with or without defining constraints for those columns.

Add one or more constraints.

Drop one or more constraints.

Drop one or more columns.

Enable or disable one or more constraints(only applies to CHECK or FOREIGN KEY constraints).

Enable or disable one or more triggers.

We can add only one column for each ALTER TABLE statement.

ALTER TABLE employee
ALTER COLUMN emp_name varchar(50)

ALTER TABLE orders
ADD FOREIGN KEY(cust_id) REFERENCES customer(cust_id)

ALTER TABLE customer
DROP COLUMN cust_name

We cannot drop the columns such as:

A replicated column.
A column used in an index.
A column used in a CHECK, FOREIGN KEY, UNIQUE, or PRIMARY KEY constraint.
A column associated with a default defined with the DEFAULT keyword or bound to
a default object.
A column to which a rule is bound.

The following example is used to create the Books and the Authors
tables and create a foreign key constraint which will perform the cascade delete
action, therefore, when a row in the Authors table is deleted, the corresponding
rows in the Books are also deleted:

CREATE TABLE Books ( BookID INT NOT NULL PRIMARY KEY, AuthorID INT NOT NULL, BookName
VARCHAR(100) NOT NULL, Price MONEY NOT NULL ) GO

CREATE TABLE Authors ( AuthorID INT NOT NULL PRIMARY KEY, Name VARCHAR(100) NOT
NULL ) GO

ALTER TABLE Books ADD CONSTRAINT fk_author FOREIGN KEY (AuthorID) REFERENCES Authors
(AuthorID) ON DELETE CASCADE GO

Conclusion
Constraints are the built-in mechanism for enforcing data integrity. Using constraints
is preferred to using triggers, rules, and defaults because built-in integrity features
use much less overhead and perform faster than the ones you can create. When you
write your own code to realize the same actions the constraints can make you can
make some errors, so the constraints are not only faster, but also are more consistent
and reliable. So, you should use triggers and rules only when the constraints do
not provide all the needed functionality.

Identifiers

IDENTIFIERS:

It must consist of a combination of 1 through 128 letters, digits or the symbols
#, $ @ or _.

If we set QUOTED IDENTIFIER ON, we can use keywords as objects names (ex: columns
names).

A column of type rowversion holds an internal sequence number that SQL Server automatically
updates every time the row is modified.

Identifiers(Topic Covered from MSDN): The database object name
is referred to as its identifier.

Everything in Microsoft SQL Server can have an identifier. Servers, databases, and
database objects, such as tables, views, columns, indexes, triggers, procedures,
constraints, and rules, can have identifiers. Identifiers are required for most
objects, but are optional for some objects such as constraints. An object identifier
is created when the object is defined. The identifier is then used to reference
the object.

For example, the following statement creates a table with the identifier TableX,
and two columns with the identifiers KeyCol and Description:

CREATE TABLE TableX (KeyCol INT PRIMARY KEY, Description nvarchar(80))

This table also has an unnamed constraint. The PRIMARY KEY constraint has no identifier.
The collation of an identifier depends on the level at which it is defined. Identifiers
of instance-level objects, such as logins and database names, are assigned the default
collation of the instance. Identifiers of objects in a database, such as tables,
views, and column names, are assigned the default collation of the database.

For example, two tables with names that differ only in case can be created in a
database that has case-sensitive collation, but cannot be created in a database
that has case-insensitive collation.

Classes of Identifiers

There are two classes of identifiers:

Regular identifiers

Comply with the rules for the format of identifiers. Regular identifiers are not
delimited when they are used in Transact-SQL statements.

SELECT * FROM TableX WHERE KeyCol = 124

Delimited identifiers

Are enclosed in double quotation marks (") or brackets ([ ]). Identifiers that comply
with the rules for the format of identifiers might not be delimited. For example:

SELECT * FROM [TableX] --Delimiter is optional.

WHERE [KeyCol] = 124 --Delimiter is optional.

Identifiers that do not comply with all the rules for identifiers must be delimited
in a Transact-SQL statement.

For example:

SELECT * FROM [My Table] --Identifier contains a space and uses a reserved keyword.

WHERE [order] = 10 --Identifier is a reserved keyword.

Both regular and delimited identifiers must contain from 1 through 128 characters.
For local temporary tables, the identifier can have a maximum of 116 characters.

ASP.NET