SQL SERVER 2005 SYNONYMS - Usage And Limitations

SYNONYMS:

Problem:

At work we face situations like ,

1. where tables created in the production database are required to move to another DB as a minimal time period, Moving the table with data's is a tedious job

2. And also we use various SPs, TVF Functions in some DB say DB1. When we are working in some other DB2 and we have situation to use the same SPs and Functions in DB1 for a test process, here we need to run the whole scripts in DB2 and we have to drop it if it is no more useful which comsume time

Solution:

The feature is SYNONYMs in SQL Server 2005. SYNONYMs is new to SQL Server 2005. It is a way to give an alias to an already existing or potential new object(May be a Table,SP,Functions,Views etc). It is just a pointer or reference, so it is not considered to be an object.

Required Permissions:

In order to create a synonym, you need to have CREATE SYNONYM permissions. If you are a sysadmin or db_owner you will have these privileges or you can grant this permission to other users. Also, you create a synonym that crosses databases you will need permissions for the other database as well..

CREATING SYNONYMs:

A SYNONYM can be created within a DB or Between DBs and also Between DBs in Different Servers.

Example 1:

Here is an Example for Creating a Synonyms within a DB

Syntax:

CREATE SYNONYM [SynName] FOR [ObjectName]

USE AdventureWorks

GO

CREATE SYNONYM MySyn FOR Production.Location

GO
To check that this works you can issue a query like

SELECT * FROM MySyn

This returns the values of the table Production.Location . This any modifications to this Synonyms will reflect in Production.Location table.

Say for example

UPDATE MySyn

SET Name = 'MukundsIdeas'

WHERE LocationID = 1

Executing this query will affect the values in the
Production.Location table of AdventureWorks DB.

Example 2:

Here is an example to create the SYNONYM in one database that references an object in another database.

USE master

GO

CREATE SYNONYM dbo.MySyn FOR AdventureWorks.Production.Location

GO

This creates a Synonym that can be accessible form any DB inside the Same Server.

Note: Make note on what Schema your DB use. For eg : if ur DB uses schema dbo then u should mention it as

CREATE SYNONYM dbo.MySyn FOR AdventureWorks.dbo.Production.Location

Example 3:

USE master
GO
CREATE SYNONYM dbo.MySyn FOR [UrServerName].[DBName].[TableName or SPname or Functionname or viewname etc]
GO

To get the meta data for all synonyms use the following command

SELECT * FROM sysobjects WHERE xtype = 'SN' ORDER BY NAME

And to drop the synonym use the following command

USE AdventureWorks;

GO

DROP SYNONYM MySyn

GO

SYNONYM's can be very useful and can be created for

1. Tables
2. Views
3. Assembly Stored Procedures,

4. Table Valued Functions,

5. Aggregations
6. SQL Scalar Functions
7. SQL Stored Procedures
8. SQL Table Valued Functions
9. SQL Inline-Table-Valued Functions
10. Local and Global Temporary Tables
11. Replication-filter-procedures
12. Extended Stored Procedures

Benefits :

SYNONYMs provide a layer of abstraction over the referenced object
Allow changes to complicated (multi part) and lengthy names with a simplified alias as a same server resident object.

Provides flexibility for changing the location of objects without changing existing code.

SYNONYMs can be created in the same database to provide backward compatibility for older applications in case of drop or rename of objects.

SYNONYMs can be useful if you give the front-end query tools like spreadsheets and Access linked tables direct links in to the tables.

Limitations:

SYNONYMs are loosely bound to the referenced objects. So you can delete a SYNONYM without getting any warning that it is being referenced by any other database object.

Chaining is not allowed. It means that you can not create SYNONYM of a SYNONYM.

Obviously consumes possible object names, as you can not create a table with the same name of a synonym

The object for which the SYNONYM is being created is checked at run time. It is not checked at creation time. So this means that if you make any related error e.g. spelling error, the synonym will created, but you will get an error while accessing the object.

SYNONYM can not be referenced in a DDL statement

User Defined SPLIT Functions in SQL SERVER 2005

User Defined Function for Splitting the string value using the delimiter:
This is a function that requires the string value and the delimiter as input and returns a column of splitted values

CREATE FUNCTION [dbo].[Split]
(
@String nvarchar (4000),
@Delimiter nvarchar (10)
)
returns @ValueTable table ([ID] BIGINT identity(1,1), [Value] nvarchar(4000))

BEGIN
DECLARE @NextString nvarchar(4000)
DECLARE @Pos int
DECLARE @NextPos int
DECLARE @CommaCheck nvarchar(1)

--Initialize
SET @NextString = ''
SET @CommaCheck = right(@String,1)

--Check for trailing Delimiter, if not exists, INSERT
--if (@CommaCheck <> @Delimiter )
SET @String = @String + @Delimiter

--Get position of first Delimiter
SET @Pos = charindex(@Delimiter,@String) SET @NextPos = 1

--Loop while there is still a Delimiter in the String of levels
WHILE (@pos <> 0)
BEGIN
SET @NextString = substring(@String,1,@Pos - 1)
INSERT INTO @ValueTable ( [Value]) Values (@NextString)
SET @String = substring(@String,@pos +1,len(@String))
SET @NextPos = @Pos
SET @pos = charindex(@Delimiter,@String)
END
RETURN
END

For eg Executing the Query
SELECT [Value] FROM [dbo].[split]('1,2,3',',') will result in
table
Value
1
2
3
This function is useful when we pass a string list to a SP.

Mathematical Calculations in SQL server

Calculating Mathematical Values in SQL Server :

Problem:

In our application we have the need to perform mathematical calculations.Right now we are doing so in our front end application. Unfortunately we are starting to experience performance problems with large data sets and differences in calculations due to developers using different logic. We are seeking some other options to perform the calculations.

Does SQL Server perform basic mathematical calculations?
Solution: Yes - SQL Server can perform basic addition, subtraction, multiplication and division. So if you need some of those basic building blocks those are available and we will walk through some examples in this tip.

In addition, SQL Server can calculate SUM, COUNT, AVG, etc. For these type of calculations, check out SQL Server T-SQL Aggregate Functions.

To address the multiple code issues, I would recommend researching stored procedures. This tip Getting started with SQL Server stored procedures is probably a good place to start.In this tip, let's focus on some basic building blocks to perform your calculations with the T-SQL language.

Here are the examples we will cover:
• Calculations on Values

• Calculations on Parameters

• Calculations on Columns

• Calculations in Computed Columns

Calculations on Values:
As a starting point, values themselves can have mathematical operations performed on them. So in the code below we are performing addition (+), subtraction (-), multiplication (*), division (/) and a combination of operations.

In these examples, both positive and negative whole numbers and decimals are used. The value calculated by the statement is below the code to serve as a point of reference. Review and copy the code into a SQL Server 2005 Management Studio window to get a feel for these calculations. Certainly this list is not comprehensive, so feel free to test some operations in your applications or from your experiences.

Calculations on Values:
-- Example 1 - Addition (+) :

SELECT 1 + 1

-- = 2

SELECT 1 + 2 + 3 + 4 + 99 + 704

-- = 813

SELECT 1.5 + 1.5 -- = 3.0

SELECT .25678 + .00096356

-- = 0.25774356

SELECT 1.75 + -2.25

-- = -0.50

Example 2 - Subtraction (-):

SELECT 1 - 1-- = 0

SELECT 918 - 704-- = 214

SELECT 3.2 - 1.9-- = 1.3

SELECT 1.9 - 3.2-- = -1.3

SELECT 9 - 3 - 3 -- = 3

SELECT .75 - .68-- = 0.07--

Example 3 - Multiplication (*):

SELECT 1 * 1-- = 1

SELECT 2 * -4-- = -8

SELECT 2 * 5 * 10-- = 100

SELECT 1.25 * 3-- = 3.75

SELECT .4 * .5-- = .20--

Example 4 - Division (/):

SELECT 1/2-- = 0

SELECT 1.0/2.0-- = 0.500000

SELECT 0/5-- = 0

SELECT 100/12-- = 8

SELECT 100.0/12.0-- = 8.333333

SELECT -75.0/4.5-- = -16.666666

SELECT .5/.1-- = 5.000000-- Example 5 - Combination

SELECT ((100 + 100) * .05)-- = 10.00

SELECT (10 - 5)/2-- = 2

SELECT (10.0 - 5.0)/2.0-- = 2.500000

SELECT ((100 + 100) - (50 + 50))-- = 100

Calculations on Parameters:
Since we have covered the bulk of the calculations in the section above let's just focus on a few different operations to show how parameters with specific data types play an important role. Since this set of examples are not comprehensive, feel free to copy the code below into a SQL Server 2005 Management Studio window and test the code with some of your own logic.
Calculations on Parameters
-- Variable declaration

DECLARE @i1 int

DECLARE @i2 int

DECLARE @i3 int

DECLARE @d1 decimal(10,2)

DECLARE @d2 decimal(10,2)

DECLARE @d3 decimal(10,2)

-- Initialize variables

SET @i1 = 100

SET @i2 = 75

SET @i3 = 50

SET @d1 = 1.5

SET @d2 = 5.5

SET @d3 = .575--

Example 1 - Addition (+)

SELECT @i1 + @i2 + @i3 + @d1 + @d2 + @d3 -- = 232.58

SELECT @d2 +

-@d3-- = 4.92

-- Example 2 - Subtraction (-):

SELECT @i2 - @i3

-- = 25

SELECT @d2 - @d3

-- = 4.92

-- Example 3 - Multiplication (*):

SELECT @i2 * @i3

-- = 3750

SELECT @d2 * @d3

-- = 3.1900

-- Example 4 - Division (/):

SELECT @i2 / @i3

-- = 1

SELECT @d2 / @d3

-- = 9.4827586206896

-- Example 5 - Combination:

SELECT ((@i1 + @i2) * @d2)

-- = 962.50

SELECT ((@i1 + @i2) - (@d1 + @d2))

-- = 168.00

Calculations on Columns:

Another option is to calculate the values based on a SELECT statement as the example below shows. If the data is in 1 or more columns, this approach eliminates the need to calculate the values based on additional parameters or logic.
Calculations on Columns
-- Sample Table

CREATE TABLE dbo.CalculationExample

(

ProductID int NOT NULL,

Cost decimal(10,2) NOT NULL

)

GO
-- Populate Table

INSERT INTO dbo.CalculationExample (ProductID, Cost)

SELECT 1, 100.00

UNION

SELECT 2, 50.00UNION SELECT 3, 25.00

GO
-- Verify Insertion

SELECT * FROM dbo.CalculationExample

GO
-- Declare Variables

DECLARE @MarginPercent decimal(10, 2)

DECLARE @TaxPercent decimal(10, 2)

-- Initialize Variables

SET @MarginPercent = .20

SET @TaxPercent = .05

-- Calculate Values

SELECT ProductID, Cost, Cost * @MarginPercent AS 'Margin',

Cost * @TaxPercent AS 'Tax',

Cost + (Cost * @MarginPercent) + (Cost * @TaxPercent) AS 'FinalCost'

FROM dbo.CalculationExample

GO

Calculations in Computed Columns:

Let's take those same calculations and now move them to computed columns in the table. Here is that example:

Calculations in Computed Columns

CREATE TABLE [dbo].[CalculationExample_ComputedColumns]

([ProductID] [int] NOT NULL,

[Cost] [decimal](18, 0) NOT NULL,

[Tax] AS ([Cost]*(0.05)),

[Margin] AS ([Cost]*(0.20)),

[FinalCost] AS (([Cost]+[Cost]*(0.20))+[Cost]*(0.05)),

CONSTRAINT [PK_CalculationExample_ComputedColumns] PRIMARY KEY CLUSTERED ([ProductID] ASC)

WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]) ON [PRIMARY]

-- Populate Table

INSERT INTO dbo.CalculationExample_ComputedColumns

(ProductID, Cost)

SELECT 1, 100.00

UNION

SELECT 2, 50.00

UNION

SELECT 3, 25.00

GO
-- Review the values

SELECT * FROM dbo.CalculationExample_ComputedColumnsGO

Ref: http://www.mssqltips.com/tip.asp?tip=1547

Useful - Date Fucntions in SQL SERVER 2005

Here are some of the Most useful DateTime Functions that used to reduce many lines of code.

—-Today

SELECT GETDATE() ‘Today’

—-Yesterday

SELECT DATEADD(d,-1,GETDATE()) ‘Yesterday’

—-First Day of Current Week

SELECT DATEADD(wk,DATEDIFF(wk,0,GETDATE()),0) ‘First Day of Current Week’

—-Last Day of Current Week

SELECT DATEADD(wk,DATEDIFF(wk,0,GETDATE()),6) ‘Last Day of Current Week’

—-First Day of Last Week

SELECT DATEADD(wk,DATEDIFF(wk,7,GETDATE()),0) ‘First Day of Last Week’

—-Last Day of Last Week

SELECT DATEADD(wk,DATEDIFF(wk,7,GETDATE()),6) ‘Last Day of Last Week’

—-First Day of Current Month

SELECT DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0) ‘First Day of Current Month’

—-Last Day of Current Month

SELECT DATEADD(ms,- 3,DATEADD(mm,0,DATEADD(mm,DATEDIFF(mm,0,GETDATE())+1,0))) ‘Last Day of Current Month’

—-First Day of Last Month

SELECT DATEADD(mm,-1,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0)) ‘First Day of Last Month’

—-Last Day of Last Month

SELECT DATEADD(ms,-3,DATEADD(mm,0,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0)))

‘Last Day of Last Month’

—-First Day of Current Year

SELECT DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0) ‘First Day of Current Year’

—-Last Day of Current Year

SELECT DATEADD(ms,-3,DATEADD(yy,0,DATEADD(yy,DATEDIFF(yy,0,GETDATE())+1,0)))

'Last Day of Current Year’

—-First Day of Last Year

SELECT DATEADD(yy,-1,DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0)) ‘First Day of Last Year’

—-Last Day of Last Year

SELECT DATEADD(ms,-3,DATEADD(yy,0,DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0)))

‘Last Day of Last Year’

REF:http://blog.sqlauthority.com/2008/08/29/sql-server-few-useful-datetime-functions-to-find-specific-dates/

Searching a column name using Sounds in SQL SERVER 2005

SQL SERVER 2005 Even adds a Advanced search option like Searching using Sounds.
For eg:
We can search using the sounds of the word say, Column name having the sounds like rich..using the SOUNDEX Function May contain
Name:
Rex
Rich
Relay
Ross
Raj

The Query looks like

SELECT COLUMN_NAME FORM [dbo].[TABLE_NAMEWHERE]
WHERE SOUNDEX(COLUMN_NAME) = SOUNDEX('Rich')

This will return the column values which sound like rich.. We cant do this using 'like' Operator

How to find the the Primary Key columns in a DB in SQL SERVER 2005

How to List Primary Key columns in a DB:
This the Query that ll list all the Primary Key columns with its table name and column name in a DataBase,

SELECT p.TABLE_NAME,c.CONSTRAINT_NAME,c.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS p ,
INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
WHERE CONSTRAINT_TYPE = 'PRIMARY KEY'
AND c.TABLE_NAME = p.TABLE_NAMEAND
AND c.CONSTRAINT_NAME = p.CONSTRAINT_NAME
ORDER by c.TABLE_NAME

Automatically Running Stored Procedures at SQL Server Startup

Here in the below link u can find a very simple implementation of Automatically running SPs at SQL SERVER startup.
http://www.mssqltips.com/tip.asp?tip=1574

SQL SERVER 2000 VS 2005

DIFFERENCES BETWEEN SQL 2000 & SQL 2005:
I am a C# Asp.Net Developer with little knowledge in SQL. I myself had a question what is the difference between SQL server 2000 & SQL 2005. Not only me , more Developer have the same Question . So i analysed surffing in the net mostly from SQLServerCentral an Excellant site for SQL, i have provided a major differnces in tabular form.

First we can discuss the Adminsitrative Differences one by one

The Development differences are more in SQL 2005 which make the major differnces & advantages than SQL Server 2000.

Now we can easily answer the questions regarding the differences in SQL 2000 & SQL 2005

SQL Server Connection Strings - An overview

SQL Server Connection Strings :
Some common and not so common connection strings for the .NET SqlConnection object.
The article includes .NET sample code and some tricks to increase the supportability of your application.

Trusted Authentication:
Data Source=ServerName; Initial Catalog=DatabaseName; Integrated Security=SSPI;

Trusted authentication uses the security credentials of the current user to make the connection to SQL Server. SQL Server uses Windows (or Active Directory) to validate the current user. ServerName can be the name of a server or the name of a SQL Server instance such as Server1\Instance2.
ServerName can also be expressed as an IP address.
SSPI stands for Security Support Provider Interface (in you were curious).

SQL Server Security Authentication:
Data Source=ServerName; Initial Catalog=DatabaseName; User Id=UserName; Password=UserPassword;
In SQL Server authentication SQL Server stores the username and password.
ServerName can be the name of a server or the name of a SQL Server instance such as Server1\Instance2. ServerName can also be expressed as an IP address.

Setting the Application Name:
Data Source=ServerName; Initial Catalog=DatabaseName; Integrated Security=SSPI; Application Name=MyAppName;
I often set the Application Name when I construct connections strings. Whatever text you assign to Application Name will appear in a couple of different places:

• It will be displayed in Profiler under the Application Name column.
• It will be shown in the output of sp_who2 in the Program Name column.
• It will be shown in the Activity Monitor in the Application column.

You can get to the Activity Monitor in SQL Server Management Studio by
Management -> Activity Monitor.

• It will appear in the program_name column if you select from master.dbo.sysprocesses (for SQL Server 2000)
• It will appear int he program_name column if you select from sys.dm_exec_sessions (for SQL Server 2005 and later).

Setting the application name makes it very easy to find out what applications are issuing particular SQL statements against my database.
Setting the application name can also lead to an increase in the number of connections to your SQL Server. Each client that uses connection pooling will create one pool inside each application per unique connection string.
If you use multiple application names you have the possibility to increase the number of pools and thus the number of connections to SQL Server.

I've always found it more beneficial to have the application name than to have a few less connections to my database servers.

Using MARS (Multiple Active Result Sets):
Data Source=ServerName; Initial Catalog=DatabaseName; Integrated Security=SSPI; MultipleActiveResultSets=True;
If you want to use MARS you'll need to enable it in the connection string.

Sample .NET code:
There are two common ways to create a connection string in .NET.
The first is to use an explicit connection string.
SqlConnection conn = new SqlConnection();
conn.ConnectionString = "Data Source=L40; Initial Catalog=master; Integrated Security=SSPI;";

The second is to use the Connection String Builder object in .NET to construct a connection string.
SqlConnectionStringBuilder csb = new SqlConnectionStringBuilder();
csb.DataSource = "L40";
csb.InitialCatalog = "master";
csb.IntegratedSecurity = true;
SqlConnection conn = new SqlConnection();
conn.ConnectionString = csb.ToString();

Ref: http://www.sqlteam.com/article/sql-server-connection-string

C# Generics with examples

Introduction:
Parametric Polymorphism is a well-established programming language feature. Generics offers this feature to C#.
The best way to understand generics is to study some C# code that would benefit from generics. The code stated below is about a simple Stack class with two methods:
Push () and Pop ().
First, without using generics example you can get a clear idea about two issues:
a) Boxing and unboxing overhead and
b) No strong type information at compile type.
After that the same Stack class with the use of generics explains how these two issues are solved.
Example Code
Code without using generics:
public class Stack
{
object[] store;
int size;
public void Push(object x)
{...}
public object Pop()
{...}
}
Boxing and unboxing overhead:
You can push a value of any type onto a stack. To retrieve, the result of the Pop method must be explicitly cast back. For example if an integer passed to the Push method, it is automatically boxed. While retrieving, it must be unboxed with an explicit type cast.

Stack stack = new Stack();
stack.Push(3);
int i = (int)stack.Pop(); //unboxing with explicit int casting

Such boxing and unboxing operations add performance overhead since they involve dynamic memory allocations and run-time type checks.
No strong Type information at Compile Time

Another issue with the Stack class:
It is not possible to enforce the kind of data placed on a stack.
For example, a string can be pushed on a stack and then accidentally cast to the wrong type like integer after it is retrieved:

Stack stack = new Stack();
stack.Push("SomeName");
//pushing the string
int i = (int)stack.Pop();
//run-time exception will be thrown at this point
The above code is technically correct and you will not get any compile time error. The problem does not become visible until the code is executed; at that point an InvalidCastException is thrown.

Code with generics
In C# with generics, you declare class Stack {...}, where T is the type parameter.
Within class Stack you can use T as if it were a type. You can create a Stack as Integer by declaring
Stack or Stack as Customer object by declaring
Stack.
Simply your type arguments get substituted for the type parameter. All of the Ts become ints or Customers, you don't have to downcast, and there is strong type checking everywhere.
public class Stack OpenTag(T)CloseTag
{
// items are of type T, which is kown when you create the object
T[] items;
int count;
public void Push(T item) {...}
//type of method pop will be decided when you creat the object
public T Pop() {...}
}
In the following example, int is given as the type argument for T:

Stack OpenTag Int CloseTag stack = new Stack OpenTag Int CloseTag ();
stack.Push(3);
int i = stack.Pop();

The Stack type is called a constructed type. In the Stack type, every occurrence of T is replaced with the type argument int. The Push and Pop methods of a Stack operate on int values, making it a compile-time error to push values of other types onto the stack, and eliminating the need to explicitly cast values back to their original type when they are retrieved.
You can use parameterization not only for classes but also for interfaces, structs, methods and delegates.
//For Interfaces interface IComparable OpenTag T CloseTag
//for structs struct HashBucket OpenTag T CloseTag
//for methods static void Reverse OpenTag T CloseTag (T[] arr)
//for delegates delegate void Action OpenTag T CloseTag (T arg)

Inside the CLR
When you compile StackOpenTag T CloseTag , or any other generic type, it compiles down to IL and metadata just like any normal type. The IL and metadata contains additional information that knows there's a type parameter. This means you have the type information at compile time.
Implementation of parametric polymorphism can be done in two ways
1. Code Specialization: Specializing the code for each instantiation
2. Code sharing: Generating common code for all instantiations.

The C# implementation of generics uses both code specialization and code sharing as explained below.
At runtime, when your application makes its first reference to StackOpenTag T CloseTag , the system looks to see if anyone already asked for Stack . If not, it feeds into the JIT the IL and metadata for Stack and the type argument int.

The .NET Common Language Runtime creates a specialized copy of the native code for each generic type instantiation with a value type, but shares a single copy of the native code for all reference types (since, at the native code level, references are just pointers with the same representation).

In other words, for instantiations those are value types: such as Stack OpenTag int CloseTag , Stack OpenTag Long CloseTag , Stack OpenTag Double CloseTag , Stack OpenTag Float CloseTag, CLR creates a unique copy of the executable native code.

So StackOpenTag int CloseTag gets its own code. Stack OpenTag Long CloseTag gets its own code. Stack OpenTag Float CloseTag gets its own code. Stack OpenTag int CloseTag uses 32 bits and Stack OpenTag Long CloseTag uses 64 bits. While reference types, Stack OpenTag Dog CloseTag is different from Stack OpenTag Cat CloseTag , but they actually share all the same method code and both are 32-bit pointers.

This code sharing avoids code bloat and gives better performance.
To support generics, Microsoft did some changes to CLR, metadata, type-loader,language compilers, IL instructions and so on for the next release of Visual Studio.NET(code named Whidbey).

What you can get with Generics
Generics can make the C# code more efficient, type-safe and maintainable.
Efficiency: Following points states that how performance is boosted.
Instantiations of parameterized classes are loaded dynamically and the code for their methods is generated on demand [Just in Time].
Where ever possible, compiled code and data representations are shared between different instantiations.
Due to type specialization, the implementation never needs to box values of primitive types.

Safety: Strong type checking at compile time, hence more bugs caught at compile time itself.
Maintainability: Maintainability is achieved with fewer explicit conversions between data types and code with generics improves clarity and expressively.

Conclusion
Generics gives better performance, type safety and clarity to the C# programs. Generics will increase program reliability by adding strong type checking. Learning how to use generics is straightforward, hopefully this article has inspired you to look deeper into how you can use them.

How to write property for CheckBox control in Asp.Net 2.0

Say chActive is the checkBox ,

private string Active
{
get
{
return chkActive.Checked ? "Y" : "N";
}

set
{
chkActive.Checked = (string.Compare(value, "Y", true) == 0 ? true : false);
}
}
Thus a check box property has been written

SQL server Count Aggregate

Count Aggregate:

Just declare a table, say

DECLARE @t table

(

id int identity(1,1),

value varchar(50)

)

Insert some values,

INSERT INTO @t values(NULL)

INSERT INTO @t values(NULL)

INSERT INTO @t values(NULL)

INSERT INTO @t values('1')

INSERT INTO @t values('2')

INSERT INTO @t values('3')

What is the output?

SELECT count(id), count(value) FROM @t

It's 6,3 actually because, The Count aggregate does not include NULL values, so the count will be 3 rows for the 2nd column

SQL DateTime Functions

SQL - Timestamp
A timestamp servers as the catch all for dates and times. Retrieving a timestamp is very simple and the result can be converted or manipulated in nearly every way imaginable.

SQL Code:
SELECT CURRENT_TIMESTAMP;
Return a Timestamp:2004-06-22 10:33:11.840
Keep in mind that each platform of SQL (DB2, Oracle, SQL Server, etc...) may return dates and times which are formatted differently.
SQL - Date FunctionsAs we just mentioned, it is possible to breakdown timestamps into their individual pieces using any of the following date functions.

SQL Code:
SELECT MONTH(CURRENT_TIMESTAMP);
Return a Month:6 SQL Code:
SELECT DAY(CURRENT_TIMESTAMP);
Return a Day:22

There are many more functions available, including functions to extract milliseconds, names of the months, names of each week day, etc.
Each SQL platform varies in the actual naming of date functions. Here's a few c\The following is a list of other date functions available to most platforms of SQL with the exception of MS's SQL Server.

SQL Function Code:
SELECT DATE(CURRENT_TIMESTAMP); - returns a date (2004-06-22)
SELECT TIME(CURRENT_TIMESTAMP); - returns the time (10:33:11.840)
SELECT DAYOFWEEK(CURRENT_TIMESTAMP); - returns a numeric value (1-7)
SELECT DAYOFMONTH(CURRENT_TIMESTAMP); - returns a day of month (1-31)
SELECT DAYOFYEAR(CURRENT_TIMESTAMP); - returns the day of the year (1-365)
SELECT MONTHNAME(CURRENT_TIMESTAMP); - returns the month name (January - December
SELECT DAYNAME(CURRENT_TIMESTAMP); - returns the name of the day (Sunday - Saturday)
SELECT WEEK(CURRENT_TIMESTAMP); - returns number of the week (1-53)

Timestamps are often the easiest to work with, but we certainly are not limited to using only the current_timestamp as our parameter. We can send any date, time, or timestamp to the function which then returns our result.

SQL Code:
SELECT MONTHNAME('2004-11-27');
Return a Month Name: MONTHNAME('2004-11-27') November
Date functions can also be performed on table columns similarly to numeric and mathematical functions such as SUM() or AVG().

SQL Code:
SELECT DAYOFYEAR(column_name) FROM table_name WHERE name = 'Joe';
SQL will return a numeric result from 1 - 365 representing the day of the year that Joe's record was created/inserted.We can expand this concept one step further with the use of a subquery. Say we have a table with a column named timestamp. In this table column are timestamps of when each record was entered and we would like to know what was the numeric day of the year that a record was entered.

SQL Code:
SELECT DAYOFYEAR((SELECT DATE(timestamp) FROM employees WHERE name = 'James Bond'));
Above you can see how it is possible to combine several date functions as well as a subquery to return very specific information about Mr. James Bond.
SQL - Inserting Date DataDate data exists as numbers, strings, and timestamps. Built into most platforms are several date column types such as DATE or TIMESTAMP. By setting the default value to the current date or timestamp, the table column will automatically be filled with a current date/timestamp as each record is inserted.
Here's the code to add a timestamp column to an existing table.
SQL Code:
ALTER TABLE `orders` ADD `order_date` TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL;
Now each time an order is placed in our make believe business, a timestamp of that order is also recorded.A date or timestamp table column will only allow date data types to be inserted as values so be sure to convert any strings or numbers to dates and timestamps before trying to insert them.

SQL - Datepart()
Microsoft's SQL Server takes a little different approach to working with dates. It is still possible to extract individual parts of a timestamp and several other functions also work as outlined in SQL - Date. The difference is that SQL Server uses one main function as oppose to several different functions ie the DATEPART() function.
Datepart() requires two parameters, a part argument and a date argument. By part we mean year, day of the year, day of the week, etc. Let's look at an example.

SQL Code:
SELECT DATEPART(week, '2005-12-31');Return the Week Number:53

Here we have successfully pulled the "week number" from existing date.
SQL's CURRENT_DATE function could be also be substituted or a string value representing the year ('Dec 31, 2005').

Handy Keyboard Shortcuts for c# 2005

Handy Keyboard Shortcuts for c# 2005

The following keyboard shortcuts I find invaluable. It's amazing how many people still use the mouse to do everything.
Document navigation :

Ctrl+Tab Switch documents

Ctrl+Shift+Tab Reverse switch documents

Ctrl+kk Drop a bookmark

Ctrl+kn Itterate through bookmarks

F7 Switch from HTML to Codebehind view

Ctrl+- Navigate backward through last cursor locations

Code Navigation :

F12 Goto Definition

Ctrl+] Jump to matching brace

Editing :

Ctrl+c Copy a whole line

Ctrl+v When a whole line in the clipboard (as above) this will instet a whole copied line.. handy for quick duplication

Ctrl+u Change to lower case

Ctrl+Shift+U Change to UPPER case

Macros :

Ctrl+Shift+R Record a quick Macro

Ctrl+Shift+P Run the quick Macro you just recorded
Comments

Ctrl+kc Comment out selected lines

Ctrl+ku Uncomment selected lines

Formatting

Ctrl+kd Autoformat selected lines

Hope this is more useful to attract others!Happy Programming

Add,Edit, Delete and View in GridView in Asp.net 2.0

Description
DataGrid plays very important component in ASP.Net Application. It covers most of the basic reports for which you do not need any reporting component like Crystal Report, Report Viewer etc.
Major concern with any data entry form is Add, Edit, Delete and View.

Assumptions-
We are using SQL Server Express editions as Database.
Table Name- Employees
Fields- EmpId, EmpName, Designation
Take GridView control on page and set the ID property to dgEmployees.
Go to property builder by right click on

GridView -> Show Smart Tag -> Edit Columns.

Uncheck Auto-Generate Field from the Property window.
Add three TemplateField Column for Employee Name, Designation, & Delete Button. Add Edit button from CommandField Group from Property Window.
TemplateField Colums have ItemTemplate,

Alternating Item Template,

Edit Template, Header Template, Footer Template.

Each template columns Item Template field contains Label Control & Edit Template contains TextBox control for Editing item. Set the Binding Field name to the Text Property of both controls for each template field to the respective Database Column Name

i.e Eval("EmpName").

Set the DataKeyNames property of GridView to Primary Key Column of DataBase i.e. EmpId.

This property binds the database column field value to each row of gridview as a Unique Identifier.
Set the data bindings for Delete Button for CommandArgument Eval("EmpId"); for saving the ID column value from database for fetching the ID field value while Deleting the Record.

Set the CommandName property to Delete Button to CMDDelete. The CommandName property can contain any string name which can be used to recognize the type of command invoked from gridview. Because when any of the event generated in GridVeiw it fires RowCommand Event. In this event we have to handle the Delete Button Code. Instead if you are using default Delete Button of GridView then register for RowDeleting event of GridView and for accessing Unique ID columnvalue from database you need to fetch the id from DataKeys collection of GridView.

For e.g.
int EmpId = Convert.ToInt32(dgEmployees.DataKeys[e.RowIndex].Value);

Place the Textbox control in the grids Footer template for Adding new record. Set the CommandName to CMDAdd for Add button.
Register events for Edit, Update, Cancel button of gridview RowEditing, RowUpdating, RowCancelEditing.

View in GridView
To view data in gridview is very simple. Just create a DataSet using SqlDataAdapter’s Fill method and set the GridViews DataSource Property to DataSet.
Create a Method to Bind the GridView to DataSource named BindGrid. This method fetches data from the GetEmployees method which returns DataSet from Employees table.
Call the BindGrid on Page_Load in !IsPostBack block to fill the grid by default.
private void BindGrid()
{
dgEmployees.DataSource = GetEmployees();
dgEmployees.DataBind();
}
private DataSet GetEmployees()
{
DataSet ds = new DataSet();
SqlConnection conn = new SqlConnection();
conn.ConnectionString =ConfigurationManager.ConnectionStrings["Conn"].ConnectionString;
SqlDataAdapter da = new SqlDataAdapter("Select * From Employees", conn);
try
{
da.Fill(ds);
return ds;
}
catch { }
finally
{
conn.Close();
conn.Dispose();
}
return null;
}

Edit in GridView
For Editing Register RowEditing event of GridView. To switch the normal mode to Edit mode of gridview EditIndex property plays important role. EditIndex specifies which row is in edit mode by setting RowIndex to it. By default EditIndex of gridview is -1 (Normal mode).

If you want to edit 3rd Row then set the EditIndex to 2 (Row index starts from 0,1,2..).
After setting editindex refresh the grid by calling BinGrid. GridViewEditEventArgs object knows the current row index so getting row index of the selected row in gridveiw is not big deal; just

e.NewEditIndex (e object of GridViewEditEventArgs).

protected void dgEmployees_RowEditing(object sender, GridViewEditEventArgs e)
{
dgEmployees.EditIndex = e.NewEditIndex;
BindGrid();
}

Cancel in GridView
For Cancel just reset the GridView editindex to default i.e. -1 and refresh the grid.
protected void dgEmployees_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
{
dgEmployees.EditIndex = -1;
BindGrid();
}

Update in GridView
For Update register RowUpdating event of the gridview. Find the Unique id for updating the row from DataKeys collection of gridview.
int EmpId = Convert.ToInt32(dgEmployees.DataKeys[e.RowIndex].Value);
Find the controls in the selected row by using FindControl method of gridviews rows collection and collect data from the text boxes.

TextBox txtname = dgEmployees.Rows[e.RowIndex].FindControl("txtEmpName") as TextBox;

TextBox txtdesign = dgEmployees.Rows[e.RowIndex].FindControl("txtDesignation") as TextBox;

Finally update the row and refresh the grid.

if(txtname!=null && txtdesign!=null)
UpdateEmployee(empId, txtname.Text.Trim(), txtdesign.Text.Trim());
dgEmployees.EditIndex = -1;
BindGrid();

Complete code-
protected void dgEmployees_RowUpdating(object sender, GridViewUpdateEventArgs e)
{
int empId = Convert.ToInt32(dgEmployees.DataKeys[e.RowIndex].Value);
//Find Text boxex
TextBox txtname = dgEmployees.Rows[e.RowIndex].FindControl("txtEmpName") as TextBox;
TextBoxtxtdesign=dgEmployees.Rows[e.RowIndex].FindControl("txtDesignation") as TextBox;
if(txtname!=null && txtdesign!=null)
UpdateEmployee(empId, txtname.Text.Trim(), txtdesign.Text.Trim());
dgEmployees.EditIndex = -1;
BindGrid();
}

Custom Delete in GridView
For Delete register RowCommand event of the gridview. Find the Unique id for deleting the row from DataKeys collection of gridview. Check for CommanName and invoke delete method for the selected row.
protected void dgEmployees_RowCommand(object sender, GridViewCommandEventArgs e)
{
if (e.CommandName.Equals("CMDDelete"))
{
int EmpId = Convert.ToInt32(e.CommandArgument);
DeleteEmployee(EmpId);
//Refresh Grid
BindGrid();
}
}
Add in GridView
Adding from GridView is just some trick with Footer Template. I added textboxes and a add button in the footer row of the gridview. When u are in Normal mode it is visible else it is invisible to synchronize between edit and add.
Like Updating find the Textbox and pass the values to Addemployee method like
else if (e.CommandName.Equals("CMDAdd"))
{
TextBox txtname = dgEmployees.FooterRow.FindControl("txtEmpName") asTextBox;
TextBox txtdesign = dgEmployees.FooterRow.FindControl("txtDesignation") as TextBox;
if (txtname != null && txtdesign != null)
{
AddEmployee(txtname.Text.Trim(), txtdesign.Text.Trim());
BindGrid();
}
}
The Complete code for EditEmployee, AddEmployee, UpdateEmployee, DeleteEmployee is in Source File.

The code should be in RowCommand event only. Due to this we use CommandName for different button control to differentiate between the type of code to be handled by gridview.

How to provide ASP.NetWebadminfiles (WSAT) like user management for your hosted or online site

How to provide ASP.NetWebadminfiles (WSAT) like user management for your hosted or online site:
Bulk User ModificationActive Directory Display Name, Logon Name Modification, AD Reportswww.admanagerplus.com
I recently was working on a ASP.net 2.0 website. I used the ActiveDirectoryMembershipProvider and used the membership API along with Login controls to provide a nice experience to the user with features like Sign up as new user, change password, password reset, login and all related functionality which any website offers you.When my code was in development, I had the built in WSAT (ASP.Net website administration tool), which I could launch from Visual Studio.Net and I could easily administer my website.
You can launch this tool using the Website–>ASP.Net configuration menu. This tool is really cool and without writing a single line of code you can easily manage all the security and settings for your website.But the problem arises when you move your code to production. The WSAT tool only works locally (i.e via localhost). By default, it prohibits remote access.In this post, I will explore two ways of managing your website security remotely.

Option 1 :Make changes to the WSAT tool to make it work remotelyThe WSAT tool with source code is located in your C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\ASP.NETWebAdminFiles folder. To make it accessible on the network, all you have to do is go to IIS–>Create new virtual directory–>Point to the above folder and remove anonymous access from directory settings page.

Then you need to access it the same way your local ASP.Net configuration tool is accessed i.e via a URL which resembles something like :http://SERVER/AdminTool/default.aspx?applicationPhysicalPath=C:\Inetpub\wwwrooot\testsite\&applicationUrl=/testsite
But you will notice, as soon as you try to access it, it will spit out an ugly error “This tool cannot be remotely accessed.“. This is because by default the tool is locked down for local access only. To fix this, all you need to do is open
C:\WINDOWS\Microsoft.NET\Framework64\v2.0.50727\ASP.NETWebAdminFiles\App_Code\WebAdminPage.cs file
in a text editor and change line#488 FROM >>
if (!application.Context.Request.IsLocal)
{ <<>
if(false){
Once you save your file, the tool will allow remote access.

Option 2:
Some people may not allow you to mess with the production webserver like above, becasue it involves changing a .net framework file and it can be a security risk. 4guysfromrolla.com has done a nice thing, they have written a generic user management piece which works just like WSAT and you can easily include it as part of your website. Just package it with your website, since it comes with source code (although the source code is in C#). You just have to follow a few steps to make it work for you. You can find the article which talks about the custom tool here : http://aspnet.4guysfromrolla.com/articles/052307-1.aspx and download the source code here http://aspnet.4guysfromrolla.com/code/ezdeploy.zip

Here are few things I had to do to make it work for my website:
Copy the source code to a subfolder in my site Delete the web.config from the root level which comes with the source code Move the 4guys.master file to the root of my website (this is mentioned in the article) Move images from the i folder to the images folder of my website and change links which point to these images (this is mentioned in the article) Change the stylesheet link in 4guys.master file to point to the correct location. Move _controls folder to the root of my website Delete all subfolders except admin from the source code. We dont need these. Changed the 4guys.master to remove menu links to pages which are irrelevant for the security piece.

NOTE: If you are using ActiveDirectoryMembershipProvider, you will get bunch of errors like The property 'LastLoginDate' is not supported by the Active Directory membership provider.]
System.Web.Security.ActiveDirectoryMembershipUser.get_LastLoginDate()To solve this all you have to do is remove following lines in all the .aspx pages.
(asp:BoundField DataField=”lastlogindate” HeaderText=”Last Login Date” /)
(asp:BoundField DataField=”lastactivitydate” HeaderText=”Last Activity Date” /)(asp:BoundField DataField=”isonline” HeaderText=”Is Online” /)

ASP.NET Basics

ASP.NET Basics: Foundation of ASP.NET
This has been pooled together from a number of resources:

What is ASP.NET?
Microsoft ASP.NET is a server side technology that enables programmers to build dynamic Web sites, web applications, and XML Web services. It is a part of the .NET based environment and is built on the Common Language Runtime (CLR) . So programmers can write ASP.NET code using any .NET compatible language.
What are the differences between ASP.NET 1.1 and ASP.NET 2.0?
A comparison chart containing the differences between ASP.NET 1.1 and ASP.NET 2.0 can be found over here.

Which is the latest version of ASP.NET? What were the previous versions released?
The latest version of ASP.NET is 2.0. There have been 3 versions of ASP.NET released as of date. They are as follows :
ASP.NET 1.0 – Released on January 16, 2002.
ASP.NET 1.1 – Released on April 24, 2003.
ASP.NET 2.0 – Released on November 7, 2005.
Additionally, ASP.NET 3.5 is tentatively to be released by the end of the 2007.

Explain the Event Life cycle of ASP.NET 2.0?
The events occur in the following sequence. Its best to turn on tracing(% @Page Trace=”true”%) and track the flow of events :
PreInit – This event represents the entry point of the page life cycle. If you need to change the Master page or theme programmatically, then this would be the event to do so. Dynamic controls are created in this event.
Init – Each control in the control collection is initialized.
Init Complete* - Page is initialized and the process is completed.
PreLoad* - This event is called before the loading of the page is completed.
Load – This event is raised for the Page and then all child controls. The controls properties and view state can be accessed at this stage. This event indicates that the controls have been fully loaded.
LoadComplete* - This event signals indicates that the page has been loaded in the memory. It also marks the beginning of the rendering stage.
PreRender – If you need to make any final updates to the contents of the controls or the page, then use this event. It first fires for the page and then for all the controls.
PreRenderComplete* - Is called to explicitly state that the PreRender phase is completed.
SaveStateComplete* - In this event, the current state of the control is completely saved to the ViewState.
Unload – This event is typically used for closing files and database connections. At times, it is also used for logging some wrap-up tasks.
The events marked with * have been introduced in ASP.NET 2.0.
You have created an ASP.NET Application. How will you run it?
With ASP.NET 2.0, Visual Studio comes with an inbuilt ASP.NET Development Server to test your pages. It functions as a local Web server. The only limitation is that remote machines cannot access pages running on this local server. The second option is to deploy a Web application to a computer running IIS version 5 or 6 or 7.

Explain the AutoPostBack feature in ASP.NET?
AutoPostBack allows a control to automatically postback when an event is fired. For eg: If we have a Button control and want the event to be posted to the server for processing, we can set AutoPostBack = True on the button.

How do you disable AutoPostBack?
Hence the AutoPostBack can be disabled on an ASP.NET page by disabling AutoPostBack on all the controls of a page. AutoPostBack is caused by a control on the page.

What are the different code models available in ASP.NET 2.0?
There are 2 code models available in ASP.NET 2.0. One is the single-file page and the other one is the code behind page.
Which base class does the web form inherit from?
Page class in the System.Web.UI namespace.

Which are the new special folders that are introduced in ASP.NET 2.0?
There are seven new folders introduced in ASP.NET 2.0 :
\App_Browsers folder – Holds browser definitions(.brower) files which identify the browser and their capabilities.
\App_Code folder – Contains source code (.cs, .vb) files which are automatically compiled when placed in this folder. Additionally placing web service files generates a proxy class(out of .wsdl) and a typed dataset (out of .xsd).
\App_Data folder – Contains data store files like .mdf (Sql Express files), .mdb, XML files etc. This folder also stores the local db to maintain membership and role information.
\App_GlobalResources folder – Contains assembly resource files (.resx) which when placed in this folder are compiled automatically. In earlier versions, we were required to manually use the resgen.exe tool to compile resource files. These files can be accessed globally in the application.
\App_LocalResources folder – Contains assembly resource files (.resx) which can be used by a specific page or control.
\App_Themes folder – This folder contains .css and .skin files that define the appearance of web pages and controls.
\App_WebReferences folder – Replaces the previously used Web References folder. This folder contains the .disco, .wsdl, .xsd files that get generated when accessing remote web services.

Explain the ViewState in ASP.NET?
Http is a stateless protocol. Hence the state of controls is not saved between postbacks. Viewstate is the means of storing the state of server side controls between postbacks. The information is stored in HTML hidden fields. In other words, it is a snapshot of the contents of a page.
You can disable viewstate by a control by setting the EnableViewState property to false.

What does the EnableViewState property signify?
EnableViewState saves the state of an object in a page between postbacks. Objects are saved in a Base64 encoded string. If you do not need to store the page, turn it off as it adds to the page size.
There is an excellent article by Peter Bromberg to understand Viewstate in depth.

Explain the ASP.NET Page Directives?
Page directives configure the runtime environment that will execute the page. The complete list of directives is as follows:
@ Assembly - Links an assembly to the current page or user control declaratively.
@ Control - Defines control-specific attributes used by the ASP.NET page parser and compiler and can be included only in .ascx files (user controls).
@ Implements - Indicates that a page or user control implements a specified .NET Framework interface declaratively.
@ Import - Imports a namespace into a page or user control explicitly.
@ Master - Identifies a page as a master page and defines attributes used by the ASP.NET page parser and compiler and can be included only in .master files.
@ MasterType - Defines the class or virtual path used to type the Master property of a page.
@ OutputCache - Controls the output caching policies of a page or user control declaratively.
@ Page - Defines page-specific attributes used by the ASP.NET page parser and compiler and can be included only in .aspx files.
@ PreviousPageType - Creates a strongly typed reference to the source page from the target of a cross-page posting.
@ Reference - Links a page, user control, or COM control to the current page or user control declaratively.
@ Register - Associates aliases with namespaces and classes, which allow user controls and custom server controls to be rendered when included in a requested page or user control.
This list has been taken from here.

Explain the Validation Controls used in ASP.NET 2.0?
Validation controls allows you to validate a control against a set of rules. There are 6 different validation controls used in ASP.NET 2.0.
RequiredFieldValidator – Checks if the control is not empty when the form is submitted.
CompareValidator – Compares the value of one control to another using a comparison operator (equal, less than, greater than etc).
RangeValidator – Checks whether a value falls within a given range of number, date or string.
RegularExpressionValidator – Confirms that the value of a control matches a pattern defined by a regular expression. Eg: Email validation.
CustomValidator – Calls your own custom validation logic to perform validations that cannot be handled by the built in validators.
ValidationSummary – Show a summary of errors raised by each control on the page on a specific spot or in a message box.
How do you indentify that the page is post back?
By checking the IsPostBack property. If IsPostBack is True, the page has been posted back.

What are Master Pages?
Master pages is a template that is used to create web pages with a consistent layout throughout your application. Master Pages contains content placeholders to hold page specific content. When a page is requested, the contents of a Master page are merged with the content page, thereby giving a consistent layout.

How is a Master Page different from an ASP.NET page?
The MasterPage has a @Master top directive and contains ContentPlaceHolder server controls. It is quiet similar to an ASP.NET page.

How do you attach an exisiting page to a Master page?
By using the MasterPageFile attribute in the @Page directive and removing some markup.

How do you set the title of an ASP.NET page that is attached to a Master Page?
By using the Title property of the @Page directive in the content page. Eg:
OpenTag @Page MasterPageFile="Sample.master" Title="I hold content" % CloseTag

What is a nested master page? How do you create them?
A Nested master page is a master page associated with another master page. To create a nested master page, set the MasterPageFile attribute of the @Master directive to the name of the .master file of the base master page.

What are Themes?
Themes are a collection of CSS files, .skin files, and images. They are text based style definitions and are very similar to CSS, in that they provide a common look and feel throughout the website.

What are skins?
A theme contains one or more skin files. A skin is simply a text file with a .skin extension and contains definition of styles applied to server controls in an ASP.NET page. For eg:

(asp:button runat="server" BackColor="blue" BorderColor="Gray" Font-Bold ="true" ForeColor="white"/ )

Defines a skin that will be applied to all buttons throughout to give it a consistent look and feel.

What is the difference between Skins and Css files?
Css is applied to HTML controls whereas
skins are applied to server controls.

What is a User Control?
User controls are reusable controls, similar to web pages. They cannot be accessed directly.

Explain briefly the steps in creating a user control?
· Create a file with .ascx extension and place the @Control directive at top of the page.
· Included the user control in a Web Forms page using a @Register directive

What is a Custom Control?
Custom controls are compiled components that run on the server and that encapsulate user-interface and other related functionality into reusable packages. They can include all the design-time features of standard ASP.NET server controls, including full support for Visual Studio design features such as the Properties window, the visual designer, and the Toolbox.

What are the differences between user and custom controls?
User controls are easier to create in comparison to custom controls, however user controls can be less convenient to use in advanced scenarios.
User controls have limited support for consumers who use a visual design tool whereas custom controls have full visual design tool support for consumers.
A separate copy of the user control is required in each application that uses it whereas only a single copy of the custom control is required, in the global assembly cache, which makes maintenance easier.
A user control cannot be added to the Toolbox in Visual Studio whereas custom controls can be added to the Toolbox in Visual Studio.
User controls are good for static layout whereas custom controls are good for dynamic layout.

Where do you store your connection string information?
The connection string can be stored in configuration files (web.config).

What is the difference between ‘Web.config’ and ‘Machine.config’?
Web.config files are used to apply configuration settings to a particular web application whereas machine.config file is used to apply configuration settings for all the websites on a web server.
Web.config files are located in the application's root directory or inside a folder situated in a lower hierarchy. The machine.config is located in the Windows directory Microsoft.Net\Framework\Version\CONFIG.
There can be multiple web.config files in an application nested at different hierarchies. However there can be only one machine.config file on a web server.

What is the difference between Server.Transfer and Response.Redirect?
Response.Redirect involves a roundtrip to the server whereas Server.Transfer conserves server resources by avoiding the roundtrip. It just changes the focus of the webserver to a different page and transfers the page processing to a different page.
Response.Redirect can be used for both .aspx and html pages whereas Server.Transfer can be used only for .aspx pages.
Response.Redirect can be used to redirect a user to an external websites.
Server.Transfer can be used only on sites running on the same server. You cannot use Server.Transfer to redirect the user to a page running on a different server.
Response.Redirect changes the url in the browser. So they can be bookmarked. Whereas Server.Transfer retains the original url in the browser. It just replaces the contents of the previous page with the new one.

What method do you use to explicitly kill a users session?
Session.Abandon().

What is a webservice?
Web Services are applications delivered as a service on the Web. Web services allow for programmatic access of business logic over the Web. Web services typically rely on XML-based protocols, messages, and interface descriptions for communication and access. Web services are designed to be used by other programs or applications rather than directly by end user. Programs invoking a Web service are called clients. SOAP over HTTP is the most commonly used protocol for invoking Web services.

How to check whether the SP exist and DROP the SP

-- Checks the sys objects for the existence of SP and drops it.
IF EXISTS (SELECT 1 FROM sys.objects
WHERE object_id = OBJECT_ID (N'[dbo].[SP_Name]')
AND
type in (N'P', N'PC'))
BEGIN
DROP PROCEDURE [dbo].[SP_Name]
END