ADO.NET

Anonymous Methods in C# 2.0

2009-02-23T15:34:00.000-08:00

Introduction

C# 2.0 (and .NET in general) introduces the anonymous method. An anonymous method can be used anywhere a delegate is used and is defined inline, without a method name, with optional parameters and a method body.To use anonymous methods, you need to know what a delegate is.
Anonymous methods are a condensed way to declare and use delegates.delegates are the next evolution of raw function pointers. A delegate is a class that encapsulates the pointer; implicitly, delegates in .NET are multicast delegates. To be a multicast delegate simply means that the "one function to one pointer" limitation is gone, because the multicast delegate class contains a list of pointers

Anonymous Methods Are Inline Delegates

Generally, when we're using delegates, we have a method. That method's signature matches the signature prescribed by a delegate and can be used to initialize a delegate instance. Anonymous methods are used to condense the method and initialization of the delegate into a single location.



private void Form1_Load(object sender, EventArgs e)
{
button1.Click += delegate
{
Debug.WriteLine("button1 clicked");
}

Anonymous methods can be used wherever delegates are expected. Anonymous methods can use ref and out arguments, but cannot reference ref or out parameters of an outer scope. Anonymous methods can't use unsafe code, and anonymous methods can't use goto, break, or continue in such a way that the branch behavior causes a branch outside of the anonymous method's code block

Are anonymous methods a good thing? The marketing material says that anonymous methods are good because they reduce code overhead caused by instantiating delegates and reducing separate methods. But the marketing material also says that anonymous methods increase usability and maintainability.

EXAMPLE:
Does this code look easily maintainable?

private void Form1_Load(object sender, EventArgs e)
{
BindClick(delegate { Debug.WriteLine("button1 click"); });
}

private void BindClick(EventHandler handler)
{
button1.Click += handler;
}

In this sample, we're passing a delegate to a method by passing the delegate as an anonymous method. Just keeping the order and number of the parentheses, semicolons, and brackets straight is a pain in the neck.

The cited classic example is that anonymous methods can reduce the overhead of creating delegates and methods just for kicking off threads (which use delegates). This is true, but threads are used infrequently and are already difficult enough to use correctly; I wonder how prudent it is to make the code more esoteric rather than less.

Summary

Anonymous methods are examples of methods without names that can be defined and used anywhere a delegate can be used. Delegates are wrappers for event handlers. How practical and generally useful anonymous methods are remains to be seen.

SQL-syntax

2007-12-26T03:00:00.000-08:00

CREATING A DATABASE

CREATE DATABASECREATE DATABASE database_nameCREATE DATABASECREATE DATABASE database_name
[ON { [PRIMARY](NAME = logical_name,FILENAME ='physical_name'[,SIZE = size][,MAXSIZE = max_size UNLIMITED][,FILEGROWTH = growth_increment])}[,...n]][LOG ON{(NAME = logical _name,FILENAME = 'physical_name'[,SIZE=size UNLIMITED][,MAXSIZE = max_size UNLIMITED][,FILEGROWTH = growth_increment])}[,...n]][FOR LOAD FOR ATTACH]

MODIFYING DATABASE

MODIFYING DATABASE ALTER DATABASE database
{ ADD FILE [,...n] [TO FILEGROUP filegroup_name] ADD LOG FILE [,...n] REMOVE FILE logical_name ADD FILEGROUP filegroup_name REMOVE FILEGROUP filegroup_name MODIFY FILE MODIFY FILEGROUP filegroup_name, filegroup_property}(NAME = logical_name,FILENAME ='physical_name'[,SIZE = size][,MAXSIZE = max_size UNLIMITED][,FILEGROWTH = growth_increment])

CREATING TABLES

CREATE TABLE table_name
(
column_name datatype [Null / Not Null]
[IDENTITY (SEED, INCREMENT)
[DEFAULT constraint_expression]
)

Constraints:Constraints:

Primary Key Constraint
Foreign Key Constraint
Unique Key Constraint
Check Constraint
Default Constraint

MODIFYING A TABLE

ALTER TABLE table_name
[WITH CHECK WITH NO CHECK]
ADD column_name data_type * Adding a Column
[DROP COLUMN column_name] * Dropping a Column

CREATING TABLES WITH CONSTRAINTS

CREATE TABLE table_name
(
column_name data_type CONSTRAINT constraint_name constraint_type [,…N]
)

ALTER TABLE table_name
*Adding constraint
ADD CONSTRAINT constraint_name constraint_type(column_name)
*Dropping constraint
Drop CONSTRAINT constraint_name
DROPPING A TABLE
DROP TABLE table_name [,…n]

SELECT STATEMENT

SELECT [ ALL DISTINCT ][ TOP n [PERCENT] [ WITH TIES] ]

[ INTO new_table ]
[ FROM ]
[ WHERE ]
[ GROUP BY [ALL] group_by_expression [,...n]
[ WITH { CUBE ROLLUP } ]]
[ HAVING ]
[ ORDER BY { column_name [ ASC DESC ] } [,...n] ]
[ COMPUTE { { AVG COUNT MAX MIN SUM }(expression)}[,.n]
[ BY expression [,...n] ]
[ FOR BROWSE ] [ OPTION ( [,...n) ] QUERYING AND REPORTING
Selecting Columns
SELECT column_name1[,column_name2]
FROM table_name

Selecting All Columns
SELECT * FROM table_name

Manipulating Column Names
SELECT column_name ‘column_alias’
[, column_name ‘column_alias’]
FROM table_name
Or
SELECT column_alias = column_name
[, column_alias = column_name]
FROM table_name
STORED PROCEDURE

Creating Stored Procedures
CREATE PROC[EDURE] procedure_name [ ; number ] [ @parameter data_type [,@parameter data_type] [ = default [ OUTPUT ] ][ WITH { RECOMPILE ENCRYPTION
RECOMPILE , ENCRYPTION } ] AS sql_statement [ ...n ]

Modifying a Stored Procedures
ALTER PROC[EDURE] procedure_name [ ; number ] [ @parameter data_type [,@parameter data_type] [ = default [ OUTPUT ] ][ WITH { RECOMPILE ENCRYPTION
RECOMPILE , ENCRYPTION } ] AS sql_statement [ ...n ]

Viewing a Stored Procedure
Sp_help – listed the various procedures created in a DB
Sp_helptext – displays the definition of the procedure
Deleting a Stored Procedure
DROP PROCEDURE procedure_name [,..n]TRIGGERS

Creating Triggers
CREATE TRIGGER trigger_name
ON table_name
FOR {INSERT DELETE UPDATE }
[WITH ENCRYPTION]
AS
sql_statements

Modifying Triggers
ALTER TRIGGER trigger_name
ON table_name
FOR {INSERT DELETE UPDATE }
[WITH ENCRYPTION]
AS
sql_statements

Viewing a Triggers
Sp_help – listed the various triggers created in a DB
Sp_helptext – displays the definition of the triggers
Deleting a Triggers
DROP TRIGGER trigger_name [,..n] FUNCTIONS

Creating a Function
CREATE FUNCTION [ owner_name. ] function_name ( [ { @parameter_name [AS] scalar_parameter_data_type [ = default ] } [ ,...n ] ] )
RETURNS scalar_return_data_type
[ WITH <> [ [,] ...n] ]
[ AS ]
BEGIN function_body RETURN scalar_expressionEND

Altering a Function
ALTER FUNCTION [ owner_name. ] function_name ( [ { @parameter_name scalar_parameter_data_type [ = default ] } [ ,...n ] ] )
RETURNS scalar_return_data_type
[ WITH <> [,...n] ]
[ AS ]
BEGIN function_body RETURN scalar_expressionEND

Drop Function
DROP FUNCTION { [ owner_name . ] function_name } [ ,...n ]
VIEWS

Creating Views
CREATE VIEW owner.view_name [(column [,n])]
[WITH {ENCRYPTION
SCHEMABINDING
VIEW_METADATA} [,N]]
AS
select_statement
[WITH CHECK OPTION] Altering Views
ALTER VIEW owner.view_name [(column [,n])]
[WITH {ENCRYPTION
SCHEMABINDING
VIEW_METADATA} [,N]]
AS
select_statement
[WITH CHECK OPTION]

Renaming a View
Sp_rename old_view_name new_view_name
Prints the text of rule, a default, or an unencrypted stored procedure, user-defined function, trigger, or view
Sp_helptext object_name
Getting Information on a View
Sysobjects or information_schema.tables : View names
Sysdepends or information_schema.view_table_usage : Base object names
Syscomments or information_schema.views : View definition
Syscolumns or information_schema.view_column_usage : columns that are defined in a view
MANIPULATION DATA

INSERTING ROWS

INSERT [INTO] {table_name} [(column_list)]
VALUES {DEFAULT VALUES values_list Select_statement

DELETING ROWS

DELETE [FROM] {table_name}
[WHERE condition]
UPDATING ROWS

UPDATE {table_name}
SET column_name = value
FROM table_name
WHERE condition

Truncating a Table

TRUNCATE TABLE table_name INDEXES
Create an Index
CREATE [UNIQUE][CLUSTERED NONCLUSTERED] INDEX index_name
ON table_name (column_name[, column_name]…)

SQL SERVER ARCHITECTURE

2007-12-26T02:10:00.000-08:00

SQL SERVER ARCHITECTURE

68. What is a transaction and what are ACID properties?
A transaction is a logical unit of work in which, all the steps must be performed or none. ACID stands for Atomicity, Consistency, Isolation, Durability. These are the properties of a transaction. For more information and explanation of these properties, see SQL Server books online or any RDBMS fundamentals text book.

69. Explain different isolation levels
An isolation level determines the degree of isolation of data between concurrent transactions. The default SQL Server isolation level is Read Committed. Here are the other isolation levels (in the ascending order of isolation): Read Uncommitted, Read Committed, Repeatable Read, Serializable. See SQL Server books online for an explanation of the isolation levels. Be sure to read about SET TRANSACTION ISOLATION LEVEL, which lets you customize the isolation level at the connection level.

CREATE INDEX myIndex ON myTable(myColumn)70. What type of Index will get created after executing the above statement?
Non-clustered index. Important thing to note: By default a clustered index gets created on the primary key, unless specified otherwise.

71. What's the maximum size of a row?
8060 bytes. Don't be surprised with questions like 'what is the maximum number of columns per table'. Check out SQL Server books online for the page titled: "Maximum Capacity Specifications".

72. Explain Active/Active and Active/Passive cluster configurations
Hopefully you have experience setting up cluster servers. But if you don't, at least be familiar with the way clustering works and the two clusterning configurations Active/Active and Active/Passive. SQL Server books online has enough information on this topic and there is a good white paper available on Microsoft site.

73. Explain the architecture of SQL Server?
This is a very important question and you better be able to answer it if consider yourself a DBA. SQL Server books online is the best place to read about SQL Server architecture. Read up the chapter dedicated to SQL Server Architecture.

74. What is lock escalation?
Lock escalation is the process of converting a lot of low level locks (like row locks, page locks) into higher level locks (like table locks). Every lock is a memory structure too many locks would mean, more memory being occupied by locks. To prevent this from happening, SQL Server escalates the many fine-grain locks to fewer coarse-grain locks. Lock escalation threshold was definable in SQL Server 6.5, but from SQL Server 7.0 onwards it's dynamically managed by SQL Server.
75. What's the difference between DELETE TABLE and TRUNCATE TABLE commands?
DELETE TABLE is a logged operation, so the deletion of each row gets logged in the transaction log, which makes it slow. TRUNCATE TABLE also deletes all the rows in a table, but it won't log the deletion of each row, instead it logs the deallocation of the data pages of the table, which makes it faster. Of course, TRUNCATE TABLE can be rolled back.
76. Explain the storage models of OLAP?
Check out MOLAP, ROLAP and HOLAP in SQL Server books online for more infomation.

77. What are the new features introduced in SQL Server 2000 (or the latest release of SQL
Server at the time of your interview)? What changed between the previous version of SQL Server and the current version?
This question is generally asked to see how current is your knowledge. Generally there is a section in the beginning of the books online titled "What's New", which has all such information. Of course, reading just that is not enough, you should have tried those things to better answer the questions. Also check out the section titled "Backward Compatibility" in books online which talks about the changes that have taken place in the new version.

78. What are constraints? Explain different types of constraints.
Constraints enable the RDBMS enforce the integrity of the database automatically, without needing you to create triggers, rule or defaults. Types of constraints: NOT NULL, CHECK, UNIQUE, PRIMARY KEY, FOREIGN KEYFor an explanation of these constraints see books online for the pages titled: "Constraints" and "CREATE TABLE", "ALTER TABLE"

79. What is an index? What are the types of indexes? How many clustered indexes can be
created on a table? I create a separate index on each column of a table. What are the advantages and disadvantages of this approach?

Indexes in SQL Server are similar to the indexes in books. They help SQL Server retrieve the data quicker.Indexes are of two types. Clustered indexes and non-clustered indexes. When you craete a clustered index on a table, all the rows in the table are stored in the order of the clustered index key. So, there can be only one clustered index per table. Non-clustered indexes have their own storage separate from the table data storage. Non-clustered indexes are stored as B-tree structures (so do clustered indexes), with the leaf level nodes having the index key and it's row locater. The row located could be the RID or the Clustered index key, depending up on the absence or presence of clustered index on the table.If you create an index on each column of a table, it improves the query performance, as the query optimizer can choose from all the existing indexes to come up with an efficient execution plan. At the same t ime, data modification operations (such as INSERT, UPDATE, DELETE) will become slow, as every time data changes in the table, all the indexes need to be updated. Another disadvantage is that, indexes need disk space, the more indexes you have, more disk space is used.

sql-faq-6

2007-12-26T02:05:00.000-08:00

57. What is DTS?
Microsoft® SQL Server™ 2000 Data Transformation Services (DTS) is a set of graphical tools and programmable objects that lets you extract, transform, and consolidate data from disparate sources into single or multiple destinations.

58. What are defaults? Is there a column to which a default can't be bound?
A default is a value that will be used by a column, if no value is supplied to that column while inserting data. IDENTITY columns and timestamp columns can't have defaults bound to them.

59. What are the constraints?
Table Constraints define rules regarding the values allowed in columns and are the standard mechanism for enforcing integrity. SQL Server 2000 supports five classes of constraints. NOT NULL, CHECK, UNIQUE, PRIMARY KEY, FOREIGN KEY.

60. What is Transaction?
A transaction is a sequence of operations performed as a single logical unit of work. A logical unit of work must exhibit four properties, called the ACID (Atomicity, Consistency, Isolation, and Durability) properties, to qualify as a transaction.

61. What is Isolation Level?
An isolation level determines the degree of isolation of data between concurrent transactions. The default SQL Server isolation level is Read Committed. A lower isolation level increases concurrency, but at the expense of data correctness. Conversely, a higher isolation level ensures that data is correct, but can affect concurrency negatively. The isolation level required by an application determines the locking behavior SQL Server uses. SQL-92 defines the following isolation levels, all of which are supported by SQL Server:
1. Read uncommitted (the lowest level where transactions are isolated only enough to ensure that physically corrupt data is not read).
2. Read committed (SQL Server default level).
3. Repeatable read.
4. Serializable (the highest level, where transactions are completely isolated from one another).

sql-faq-5

2007-12-26T02:03:00.000-08:00

transaction across all the servers enlisted in the transaction.
49. What are the different ways of moving data/databases between servers and databases in
SQL Server?
There are lots of options available, you have to choose your option depending upon your requirements. Some of the options you have are: BACKUP/RESTORE, detaching and attaching databases, replication, DTS, BCP, logshipping, INSERT...SELECT, SELECT...INTO, creating INSERT scripts to generate data.

50. Do you transfer data from text file to database (other than DTS)?
Using the BCP (Bulk Copy Program) utility

51. What's the difference between DELETE TABLE and TRUNCATE TABLE commands?
DELETE TABLE is a logged operation, so the deletion of each row gets logged in the transaction log, which makes it slow. TRUNCATE TABLE also deletes all the rows in a table, but it won't log the deletion of each row, instead it logs the deallocation of the data pages of the table, which makes it faster. Of course, TRUNCATE TABLE can be rolled back.

52. What is a deadlock?
Deadlock is a situation when two processes, each having a lock on one piece of data, attempt to acquire a lock on the other's piece. Each process would wait indefinitely for the other to release the lock, unless one of the user processes is terminated. SQL Server detects deadlocks and terminates one user's process.

53. What is a LiveLock?
A livelock is one, where a request for an exclusive lock is repeatedly denied because a series of overlapping shared locks keeps interfering. SQL Server detects the situation after four denials and refuses further shared locks. A livelock also occurs when read transactions monopolize a table or page, forcing a write transaction to wait indefinitely.

54. How to restart SQL Server in single user mode?
From Startup Options :- Go to SQL Server Properties by right-clicking on the Server name in the Enterprise manager. Under the 'General' tab, click on 'Startup Parameters'. Enter a value of -m in the Parameter.

55. Does SQL Server 2000 clustering support load balancing?
SQL Server 2000 clustering does not provide load balancing; it provides failover support. To achieve load balancing, you need software that balances the load between clusters, not between servers within a cluster.

56. What is DTC?
The Microsoft Distributed Transaction Coordinator (MS DTC) is a transaction manager that allows client applications to include several different sources of data in one transaction. MS DTC coordinates committing the distributed

sql-faq-4

2007-12-26T02:02:00.000-08:00

41. How can I enforce to use particular index?
You can use index hint (index=index_name) after the table name.
SELECT au_lname
FROM authors (index=aunmind)

42. What is sorting and what is the difference between sorting and clustered indexes?
The ORDER BY clause sorts query results by one or more columns up to 8,060 bytes. This will happen by the time when we retrieve data from database. Clustered indexes physically sorting data, while inserting/updating the table.

43. What are the differences between UNION and JOINS?
A JOIN selects columns from 2 or more tables. A UNION selects rows.
The UNION operator allows you to combine the results of two or more SELECT statements into a single result set.

44. What is the Referential Integrity?
Referential integrity refers to the consistency that must be maintained between primary and foreign keys, i.e. every foreign key value must have a corresponding primary key value.

45. What is the row size in SQL Server 2000?
8060 bytes.

46. How to determine the service pack currently installed on SQL Server?
The global variable @@Version stores the build number of the sqlservr.exe, which is used to determine the service pack installed. eg: Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05 Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on Windows NT 5.0 (Build 2195: Service Pack 3)

47. What is the purpose of UPDATE STATISTICS?
Updates information about the distribution of key values for one or more statistics groups (collections) in the specified table or indexed view.

48. What is the use of SCOPE_IDENTITY() function?
Returns the most recently created identity value for the tables in the current execution scope.

sql-faq-3

2007-12-26T02:01:00.002-08:00

29. Can you explain the role of each service?
SQL SERVER - is for running the databases SQL AGENT - is for automation such as Jobs, DB Maintanance, Backups DTC - Is for linking and connecting to other SQL Servers.

30. How do you troubleshoot SQL Server if its running very slow?
First check the processor and memory usage to see that processor is not above 80% utilization and memory not above 40-45% utilization then check the disk utilization using Performance Monitor, Secondly, use SQL Profiler to check for the users and current SQL activities and jobs running which might be a problem. Third would be to run UPDATE_STATISTICS command to update the indexes.

31. Lets say due to N/W or Security issues client is not able to connect to server or vice versa.
How do you troubleshoot?
First I will look to ensure that port settings are proper on server and client Network utility for connections. ODBC is properly configured at client end for connection ——Makepipe & readpipe are utilities to check for connection. Makepipe is run on Server and readpipe on client to check for any connection issues.

32. What are the authentication modes in SQL Server?
Windows mode and mixed mode (SQL & Windows).

33. Where do you think the users names and passwords will be stored in sql server?
They get stored in master db in the sysxlogins table.

34. What is log shipping?
Can we do logshipping with SQL Server 7.0 - Logshipping is a new feature of SQL Server 2000. We should have two SQL Server - Enterprise Editions. From Enterprise Manager we can configure the logshipping. In logshipping the transactional log file from one server is automatically updated into the backup database on the other server. If one server fails, the other server will have the same db and we can use this as the DR (disaster recovery) plan.

35. Let us say the SQL Server crashed and you are rebuilding the databases including the
master database what procedure to you follow?
For restoring the master db we have to stop the SQL Server first and then from command line we can type SQLSERVER .m which will basically bring it into the maintenance mode after which we can restore the master db.

36. Let us say master db itself has no backup. Now you have to rebuild the db so what kind of
action do you take?
(I am not sure- but I think we have a command to do it).

37. What’s the difference between a primary key and a unique key?
Both primary key and unique enforce uniqueness of the column on which they are defined. But by default primary key creates a clustered index on the column, where are unique creates a nonclustered index by default. Another major difference is that, primary key doesn’t allow NULLs, but unique key allows one NULL only.

38. Write a SQL Query to find first Week Day of month?
SELECT
DATENAME(dw, DATEADD(dd, - DATEPART(dd, GETDATE()) + 1, GETDATE())) AS FirstDay

39. How to find 6th highest salary from Employee table
SELECT TOP 1 salary
FROM (SELECT DISTINCT TOP 6 salary
FROM employee
ORDER BY salary DESC)
ORDER BY salary

40. What is a join and List different types of joins?
Joins are used in queries to explain how different tables are related. Joins also let you select data from a table depending upon data from another table. Types of joins: INNER JOINs, OUTER JOINs, CROSS JOINs. OUTER JOINs are further classified as LEFT OUTER JOINS, RIGHT OUTER JOINS and FULL OUTER JOINS.

sql-faq-2

2007-12-26T02:01:00.001-08:00

16. Can you give me some DBCC command options?
(Database consistency check) - DBCC CHECKDB - Ensures that tables in the db and the indexes are correctly linked.and DBCC CHECKALLOC - To check that all pages in a db are correctly allocated. DBCC SQLPERF - It gives report on current usage of transaction log in percentage. DBCC CHECKFILEGROUP - Checks all tables file group for any damage.

17. What command do we use to rename a db?
sp_renamedb ‘oldname’ , ‘newname’

18. Well sometimes sp_reanmedb may not work you know because if some one is using the db
it will not accept this command so what do you think you can do in such cases?
In such cases we can first bring to db to single user using sp_dboptions and then we can rename that db and then we can rerun the sp_dboptions command to remove the single user mode.

19. What is the difference between a HAVING CLAUSE and a WHERE CLAUSE?
Having Clause is basically used only with the GROUP BY function in a query. WHERE Clause is applied to each row before they are part of the GROUP BY function in a query.

20. What do you mean by COLLATION?
Collation is basically the sort order. There are three types of sort order Dictionary case sensitive, Dictonary - case insensitive and Binary.

21. What is a Join in SQL Server?
Join actually puts data from two or more tables into a single result set.

22. Can you explain the types of Joins that we can have with Sql Server?
There are three types of joins: Inner Join, Outer Join, Cross Join

23. When do you use SQL Profiler?
SQL Profiler utility allows us to basically track connections to the SQL Server and also determine activities such as which SQL Scripts are running, failed jobs etc..

24. What is a Linked Server?
Linked Servers is a concept in SQL Server by which we can add other SQL Server to a Group and query both the SQL Server dbs using T-SQL Statements.

26. Which stored procedure will you be running to add a linked server?
sp_addlinkedserver, sp_addlinkedsrvlogin

27. Can you link only other SQL Servers or any database servers such as Oracle?
We can link any server provided we have the OLE-DB provider from Microsoft to allow a link. For Oracle we have a OLE-DB provider for oracle that microsoft provides to add it as a linked server to the sql server group.

28. What are the OS services that the SQL Server installation adds?
MS SQL SERVER SERVICE, SQL AGENT SERVICE, DTC (Distribution transac co-ordinator)
29. Can you explain the role of each service?
SQL SERVER - is for running the databases SQL AGENT - is for automation such as Jobs, DB Maintanance, Backups DTC - Is for linking and connecting to other SQL Servers.

sql-faq-1

2007-12-26T01:59:00.000-08:00

1. What is normalization?
Well a relational database is basically composed of tables that contain related data. So the Process of organizing this data into tables is actually referred to as normalization.

2. What is a Stored Procedure?
Its nothing but a set of T-SQL statements combined to perform a single task of several tasks. Its basically like a Macro so when you invoke the Stored procedure, you actually run a set of statements.

3. Can you give an example of Stored Procedure?
sp_helpdb , sp_who2, sp_renamedb are a set of system defined stored procedures. We can also have user defined stored procedures which can be called in similar way.

4. What is a trigger?
Triggers are basically used to implement business rules. Triggers is also similar to stored procedures. The difference is that it can be activated when data is added or edited or deleted from a table in a database.

5. What is a view?
If we have several tables in a db and we want to view only specific columns from specific tables we can go for views. It would also suffice the needs of security some times allowing specfic users to see only specific columns based on the permission that we can configure on the view. Views also reduce the effort that is required for writing queries to access specific columns every time.

6. What is an Index?
When queries are run against a db, an index on that db basically helps in the way the data is sorted to process the query for faster and data retrievals are much faster when we have an index.

7. What are the types of indexes available with SQL Server?
There are basically two types of indexes that we use with the SQL Server. Clustered and the Non-Clustered.

8. What is the basic difference between clustered and a non-clustered index?
The difference is that, Clustered index is unique for any given table and we can have only one clustered index on a table. The leaf level of a clustered index is the actual data and the data is resorted in case of clustered index. Whereas in case of non-clustered index the leaf level is actually a pointer to the data in rows so we can have as many non-clustered indexes as we can on the db.

9. What are cursors?
Well cursors help us to do an operation on a set of data that we retreive by commands such as Select columns from table. For example : If we have duplicate records in a table we can remove it by declaring a cursor which would check the records during retreival one by one and remove rows which have duplicate values.

10. When do we use the UPDATE_STATISTICS command?
This command is basically used when we do a large processing of data. If we do a large amount of deletions any modification or Bulk Copy into the tables, we need to basically update the indexes to take these changes into account. UPDATE_STATISTICS updates the indexes on these tables accordingly.

11. Which TCP/IP port does SQL Server run on?
SQL Server runs on port 1433 but we can also change it for better security.

12. From where can you change the default port?
From the Network Utility TCP/IP properties –> Port number.both on client and the server.
13. Can you tell me the difference between DELETE & TRUNCATE commands?
Delete command removes the rows from a table based on the condition that we provide with a WHERE clause. Truncate will actually remove all the rows from a table and there will be no data in the table after we run the truncate command.

14. Can we use Truncate command on a table which is referenced by FOREIGN KEY?
No. We cannot use Truncate command on a table with Foreign Key because of referential integrity.

15. What is the use of DBCC commands?
DBCC stands for database consistency checker. We use these commands to check the consistency of the databases, i.e., maintenance, validation task and status checks.

CONSTRAINT-12

2007-12-26T01:56:00.000-08:00

A CONSTRAINT clause is an optional part of a CREATE TABLE statement or ALTER TABLE. A constraint is a rule to which data must conform. Constraint names are o ptional.
A CONSTRAINT can be one of the following:
· a column-level constraint
Column-level constraints refer to a single column in the table and do not specify a column name (except check constraints). They refer to the column that they follow.
· a table-level constraint
Table-level constraints refer to one or more columns in the table. Table-level constraints specify the names of the columns to which they apply. Table-level CHECK constraints can refer to 0 or more columns in the table.
Column constraints include:
· NOT NULL
Specifies that this column cannot hold NULL values (constraints of this type are not nameable).
· NULL
The opposite of NOT NULL (not really a constraint), it specifies that the column can hold NULL values. Specifying NULL is the same as saying nothing at all, except when the column is included in a PRIMARY KEY constraint.
· PRIMARY KEY
Specifies the column that uniquely identifies a row in the table. The identified columns must be defined as NOT NULL.
Note:
If you attempt to add a primary key using ALTER TABLE and any of the columns included in the primary key contain null values, an error will be generated and the primary key will not be added. See ALTER TABLE for more information.
· UNIQUE
Specifies that values in the column must be unique. NULL values are not allowed.
· FOREIGN KEY
Specifies that the values in the column must correspond to values in a referenced primary key or unique key column or that they are NULL.
· CHECK
Specifies rules for values in the column.
Table constraints include:
· PRIMARY KEY
Specifies the column or columns that uniquely identify a row in the table. NULL values are not allowed.
· UNIQUE
Specifies that values in the columns must be unique. The identified columns must be defined as NOT NULL.
· FOREIGN KEY
Specifies that the values in the columns must correspond to values in referenced primary key or unique columns or that they are NULL.
Note:
If the foreign key consists of multiple columns, and any column is NULL, the whole key is considered NULL. The insert is permitted no matter what is on the non-null columns.
· CHECK
Specifies a wide range of rules for values in the table.
Column constraints and table constraints have the same function; the difference is in where you specify them. Table constraints allow you to specify more than one column in a PRIMARY KEY, UNIQUE, CHECK, or FOREIGN KEY constraint definition. Column-level constraints (except for check constraints) refer to only one column

indexes

2007-12-26T01:54:00.000-08:00

Data access: SQL server uses following two methods to access data

1). Table scan
2). By using index

Table scan: it has to start from the beginning of the table and scan each and every page for the required data.

By using index: it will traverse through the index tree structure to find the required data and extract the data that satisfy the query criteria.

Indexes: indexes in sql server are similar to index in a book. Indexes are used to improve the performance of quires.

Indexes are generally created for following columns.
1). Primary key column
2). Foreign key column: Frequently used in join conditions
3). Column which are frequently used in where clause
4). Columns which are used to retrieve the data in sorting order

Indexes are cannot be created for following columns
The columns which are not frequently used in where clause.
Columns containing the duplicate and NULL values.
Columns containing images, binary information, and text information.

Types of indexes
1). Clustered index
2). Non-clustered index
3). Unique index
4). Composite index

1). Clustered index: Only one clustered index is allowed for a table. The order of values in a table. Order of values in index is also same. When cluster index is created on table data is arranged in ascending order cluster index will occupy 5% of the table.

Syntax:
Create clustered index an (columns)
Create clustered indew emp_clindex an emp (empno)

2). Non-clustered index:
It is the default index created by the server the physical order of the data in the table is different from the order of the values in index.

Max no of non – clustered indexes allowed for table is 249.

Syntax:
Create non-clustered index on table_name
Create non-clustered index emp_sal on emp (deptno, sal)

3). Unique index:
An index with unique constraint. It will not allow duplicate values.

Syntax:
Create unique index on (column)
Create unique index dept_index on dept (dname)

Transactions:
1). Auto commit Transaction
2). Explicit Transaction
3). Implicit Transaction

1). Auto commit Transaction: Every individual transaction is an Auto commit transaction

Example: Update emp set sal = sal + 100 where deptno.

2). Explicit transaction: Transaction begins with Begin Tran and ends explicitly with either commit or rollback
Begin Tran
insert into dept values (10, ‘ACCOUNTING’, ‘NEWYORK’)
rollback
insert operation is cancelled

3). Implicit Transaction:
 Set Implicit_transactions on/off
 update emp set sal = sal + 1000 where job = ‘MANAGER’
 delete from emp where deptno = 10
 commit

Update and delete operations are cancelled

Save point: Save points are like paper markers used to marker the transactions.

Syntax: save begin from
insert into emp (empno, ename) values (100, ‘RAM’)
Save point from A
Update emp set sal = sal + 100 where empno = 7839
Save from B
Insert into emp (empno, ename) values (200, ‘***’)
Delete from emp where empno = 7369
Rollback from B
delete and INSERT will be cancelled
Rollback from A
update will be cancelled.
Commit/rollback
Insert will be saved/int will be cancelled

normalization and denormalization

2007-12-26T01:51:00.000-08:00

Normalization

Normalization theory is built the concept of normal forms. Normalizations reduces redundancy. Redundancy is unnecessary repetition of data. It can cause problems with storage and retrieval of data. During the process of normalization, dependencies can be identified, which can cause problems during deletion and updation. Normalization theory is based on the fundamental notation of functional dependency. Normalization helps in simplifying the structure of tables.

Suppose for an entity customer if attributes like cunno, name, address are required, then it can be perceived that for a particular cusno, only one name and address is possible. Hence the attribute name, address are said to be functionally dependent on the attribute cusno. There are four levels of normalization.

Denormalization

Normalization helps in reducing redundancy. But at times when information is required from more than one table, at a faster rate, it is wiser to introduce some amount of redundancy in the table. This intentional introduction of redundancy in to the table is called Denormalization.

For example, if the query were used to display the names of students and their marks obtained in each subject, then it would be advisable to maintain information about the students. Otherwise, every time such a query is made, a join will have to be carried out on the two tables. For the sake of better performance some amount of redundancy has to be introduced.

The language widely used in relational database is Structured Query Language, more popularly known as SQL. It is the standard relational database access language. They can be roughly divided in to three categories based on their functions namely Data Definition Language, Data Manipulation Language and Data Control Language.

sql server-11

2007-12-25T01:00:00.002-08:00

What is de-normalization? When do you do it and how?
De-normalization is the process of attempting to optimize the performance of a database by adding redundant data. It’s used To introduce redundancy into a table in order to incorporate data from a related table. The related table can then be eliminated. De-normalization can improve efficiency and performance by reducing complexity in a data warehouse schema.

Explain features of SQL Server like Scalibility, Availability, Integration with Internet.
Scalibility - The same Microsoft SQL Server 2000 database engine operates on Microsoft Windows 2000 Professional, Microsoft Windows 2000 Server, Microsoft Windows 2000 Advanced Server, Windows 98, and Windows Millennium Edition. It also runs on all editions of Microsoft Windows NT version 4.0. The database engine is a robust server that can manage terabyte-sized databases accessed by thousands of users. Availability - SQL Server 2000 can maintain the extremely high levels of availability required by large Web sites and enterprise systems. Integration -The SQL Server 2000 TCP/IP Sockets communications support can be integrated with Microsoft Proxy Server to implement secure Internet and intranet communications.

What is DataWarehousing?
A data warehouse is a collection of data gathered and organized so that it can easily by analyzed, extracted, synthesized, and otherwise be used for the purposes of further understanding the data.

What is OLAP?
OLAP is an acronym for On Line Analytical Processing. It is an approach to quickly provide the answer to analytical queries that are dimensional in nature.

How do we upgrade SQL Server 7.0 to 2000?
Run the installation of the SQL Server 2000
In the Existing Installation dialog box, click Upgrade your existing installation, and then click Next.
In the Upgrade dialog box, you are prompted as to whether you want to proceed with the requested upgrade. Click Yes, upgrade my to start the upgrade process, and then click Next. The upgrade runs until finished.
In the Connect to Server dialog box, select an authentication mode, and then click Next.
If you are not sure which mode to use, accept the default: The Windows account information I use to log on to my computer with (Windows). In Start Copying Files dialog box, click Next.
Now your Sql Server would be upgraded.

What is job?
It can be defined as a task performed by a computer system. For example, printing a file is a job. Jobs can be performed by a single program or by a collection of programs.

What is Task?
Whenever you execute a program, the operating system creates a new task for it. The task is like an envelope for the program: it identifies the program with a task number and attaches other bookkeeping information to it.

How do you find the error, how can you know the number of rows affected by last SQL Statement?
Answer1
@@errors->give the last error occured in the current DB.
Ans. select @@rowcount

Answer2.
Use @@ERROR which returns the error number for the last Transact-SQL statement executed fro knowing the error.
Use @@ROWCOUNT which returns the number of rows affected by the last statement for finding the no of rows affected.

sql server-10

2007-12-25T01:00:00.001-08:00

What does it mean to have quoted_identifier on? What are the implications of having it off?
SET QUOTED_IDENTIFIER ON- Causes SQL Server to follow the SQL-92 rules regarding quotation mark delimiting identifiers and literal strings. Identifiers delimited by double quotation marks can be either Transact-SQL reserved keywords or can contain characters not usually allowed by the Transact-SQL syntax rules for identifiers.

What is the difference between a Local temporary table and a Global temporary table? How is each one used?
Answer1:
Local templrary table will have a single # (#tablename) appended with the table name.Global templrary table will have Double # (##tablename) appended with the table name.
Ex:create table #table1
local temp. table will be available until the session who created it logs out, but global temp. table is available till the last session gets close in SQLServer.

Answer2:
Local temporary tables are visible only in the current session; global temporary tables are visible to all sessions.Prefix local temporary table names with single number sign (#table_name), and prefix global temporary table names with a double number sign (##table_name).

What are cursors? Name four type of cursors and when each one would be applied?
Opening a cursor on a result set allows processing the result set one row at a time.
The four API server cursor types supported by SQL Server are:
a) Static cursors
b) Dynamic cursors
c) Forward-only cursors
d) Keyset-driven cursors

What is the purpose of UPDATE STATISTICS?
UPDATE STATISTICS- it updates information about the distribution of key values for one or more statistics groups (collections) in the specified table or indexed view.

How do you use DBCC statements to monitor various ASPects of a SQL Server installation?
Database Consistency Checker (DBCC) - Is a statement used to check the logical and physical consistency of a database, check memory usage, decrease the size of a database, check performance statistics, and so on. Database consistency checker (DBCC) ensures the physical and logical consistency of a database, but is not corrective. DBCC can help in repairing or checking the installation in case of any failure.

What is referential integrity and how can we achieve it?
Referential integrity preserves the defined relationships between tables when records are entered or deleted. In SQL Server, referential integrity is based on relationships between foreign keys and primary keys or between foreign keys and unique keys. Referential integrity ensures that key values are consistent across tables. Such consistency requires that there be no references to nonexistent values and that if a key value changes, all references to it change consistently throughout the database.
We can achieve this by using foreign key.

What is indexing?
If we give proper indexes on a table so that any queries written against this table can run efficiently. As your data sets grow over time, SQL Server will continue to rebuild indexes and move data around as efficiently as possible. This property is known as Indexing.

sql server-12

2007-12-25T00:55:00.000-08:00

Which command using Query Analyzer will give you the version of SQL Server and Operating System?
@@VERSION
Returns version, processor architecture, build date, and operating system for the current installation of SQL Server.

How to find the SQL server version from Query Analyser
Answer1
To determine which version of Microsoft SQL Server 2005 is running, connect to SQL Server 2005 by using SQL Server Management Studio, and then run the following Transact-SQL statement:
SELECT SERVERPROPERTY(’productversion’), SERVERPROPERTY (’productlevel’), SERVERPROPERTY (’edition’)
The results are:
• The product version (for example, “9.00.1399.06?)
. • The product level (for example, “RTM”).
• The edition (for example, “Enterprise Edition”).
For example, the result looks similar to:
9.00.1399.06 RTM Enterprise Edition

How to determine which version of SQL Server 2000 is running
To determine which version of SQL Server 2000 is running, connect to SQL Server 2000 by using Query Analyzer, and then run the following code:
SELECT SERVERPROPERTY(’productversion’), SERVERPROPERTY (’productlevel’), SERVERPROPERTY (’edition’)
The results are:
• The product version (for example, 8.00.534).
• The product level (for example, “RTM” or “SP2?).
• The edition (for example, “Standard Edition”). For example, the result looks similar to
:
8.00.534 RTM Standard Edition

Answer2
One can also use SELECT @@Version where the result would look like
Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86)
Oct 14 2005 00:33:37
Copyright (c) 1988-2005 Microsoft Corporation
Express Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

Using query analyzer, name 3 ways you can get an accurate count of the number of records in a table.
Answer1.
a. Select count(*) from table1
b. SELECT object_name(id) ,rowcnt FROM sysindexes WHERE indid IN (1,0) AND OBJECTPROPERTY(id, ‘IsUserTable’) = 1
c. exec sp_table_validation @table = ‘authors’

Answer2.
SELECT count( * ) as totalrecords FROM employee
This will display total records under the name totalrecords in the table employee
use COUNT_BIG
Returns the number of items in a group.
@@ROWCOUNT
Returns the number of rows affected by the last statement.
Use this statement after an SQL select * statement, to retrieve the total number of rows in the table

What is the purpose of using COLLATE in a query?
Answer1.
Collation refers to a set of rules that determine how data is sorted and compared. Character data is sorted using rules that define the correct character sequence, with options for specifying case-sensitivity, accent marks, kana character types and character width.

Answer2.
COLLATE is a clause that can be applied to a database definition or a column definition to define the collation, or to a character string expression to apply a collation cast.

What is one of the first things you would do to increase performance of a query? For example, a boss tells you that “a query that ran yesterday took 30 seconds, but today it takes 6 minutes”?
Answer1.
Use Storedprocedure for any optimized result, because it is an compiled code.

Answer2.
One of the best ways to increase query performance is to use indexes.

What is an execution plan? When would you use it? How would you view the execution plan?
The Query Analyzer has a feature called Show Execution Plan. This option allows you to view the execution plan used by SQL Server’s Query Optimizer to actually execute the query. This option is available from the Query menu on the main menu of Query Analyzer, and must be turned on before the query is executed. Once the query is executed, the results of the execution plan are displayed in graphical format in a separate window, available from a tab that appears below the query results window on the screen.

What is the STUFF function and how does it differ from the REPLACE function?
Answer1:
stuff-> inserts into it without removing any thing. Replace->replace the given text with the new one.

Answer2:
STUFF - it deletes a specified length of characters and inserts another set of characters at a specified starting point. REPLACE -Replaces all occurrences of a specified string value with another string value.

Stored Procedures

2007-12-25T00:50:00.000-08:00

To test a Web Service you must create a windows application or web application to consume this service? It is True/False?
FALSE

How many classes can a single.NET DLL contain?
Answer1:
As many

Answer2:
One or more

What are good ADO.NET object(s) to replace the ADO Recordset object?
The differences includes
In ADO, the in-memory representation of data is the recordset.
In ADO.net, it is the dataset
A recordset looks like a single table in ADO
In contrast, a dataset is a collection of one or more tables in ADO.net
ADO is designed primarily for connected access
ADO.net the disconnected access to the database is used
In ADO you communicate with the database by making calls to an OLE DB provider.
In ADO.NET you communicate with the database through a data adapter (an OleDbDataAdapter, SqlDataAdapter, OdbcDataAdapter, or OracleDataAdapter object), which makes calls to an OLE DB provider or the APIs provided by the underlying data source.
In ADO you cant update the database from the recordset. ADO.NET the data adapter allows you to control how the changes to the dataset are transmitted to the database.

On order to get assembly info which namespace we should import?
System.Reflection Namespace

How do you declare a static variable and what is its lifetime? Give an example.
Answer1
static int Myint–The life time is during the entire application.
br> Answer2
The static modifier is used to declare a static member, which belongs to the type itself rather than to a specific object. The static modifier can be used with fields, methods, properties, operators, events and constructors, but cannot be used with indexers, destructors, or types. In C#, the static keyword indicates a class variable. In VB, the equivalent keyword is Shared. Its scoped to the class in which it occurs.

Example
a. Static int var //in c#.net
b. static void Time( ) //in c#.net

How do you get records number from 5 to 15 in a dataset of 100 records? Write code.
Answer1
DataSet ds1=new DataSet(); String strCon=”data source=IBM-6BC8A0DACEF;initial catalog=pubs;integrated security=SSPI;persist” +” security info=False;user
id=sa;workstation id=IBM-6BC8A0DACEF;packet size=4096?;
String strCom1=”SELECT * FROM employee”;
SqlDataAdapter sqlDa1=new SqlDataAdapter(strCom1,strCon);
ds1.Tables.Add(”employee”);
sqlDa1.Fill(ds1,40,50,ds1.Tables[”employee”].TableName);
DataGrid dg1.DataSource=ds1.Tables[”employee”].DefaultView;
dg1.DataBind();

Answer2
OleDbConnection1.Open()
OleDbDataAdapter1.Fill(DataSet21, 5, 15, “tab”)
This will fill the dataset with the records starting at 5 to 15
.NET Database interview questions

How do you call and execute a Stored Procedure in.NET? Give an example.
Answer1
ds1=new DataSet();
sqlCon1=new SqlConnection(connectionstring);
String strCom1=”byroyalty”;
sqlCom1=new SqlCommand(strCom1,sqlCon1);
sqlCom1.CommandType=CommandType.StoredProcedure;
sqlDa1=new SqlDataAdapter(sqlCom1);
SqlParameter myPar=new SqlParameter(”@percentage”,SqlDbType.Int);
sqlCom1.Parameters.Add (myPar);
myPar.Value=40;
sqlDa1.Fill(ds1);
dg1.DataSource=ds1;
dg1.DataBind();

Answer2
Yes
Dim cn as new OleDbConnection ( “Provider=Microsoft.Jet.OLEDB.4.0;”+ _
“Data Source=C:\Documents and Settings\User\My Documents\Visual Studio Projects\1209\db1.mdb”+ _
“User ID=Admin;”+ _
“Password=;”);
Dim cmd As New OleDbCommand(”Products”, cn)
cmd.CommandType = CommandType.StoredProcedure
Dim da As New OleDataAdapter(cmd)
Dim ds As New DataSet()
da.Fill(ds, “Products”)
DataGrid1.DataSource = ds.Tables(”Products”)

What is the maximum length of a varchar in SQL Server?
Answer1
VARCHAR[(n)]
Null-terminated Unicode character string of length n,
with a maximum of 255 characters. If n is not supplied, then 1 is assumed.

Answer2
8000

Answer3
The business logic is the aspx.cs or the aspx.vb where the code is being written. The presentation logic is done with .aspx extention.

faqs-88

2007-12-24T15:05:00.000-08:00

Can you explain the difference between an ADO.NET Dataset and an ADO Recordset?

Let’s take a look at the differences between ADO Recordset and ADO.Net DataSet:

1. Table Collection: ADO Recordset provides the ability to navigate through a single table of information. That table would have been formed with a join of multiple tables and returning columns from multiple tables. ADO.NET DataSet is capable of holding instances of multiple tables. It has got a Table Collection, which holds multiple tables in it. If the tables are having a relation, then it can be manipulated on a Parent-Child relationship. It has the ability to support multiple tables with keys, constraints and interconnected relationships. With this ability the DataSet can be considered as a small, in-memory relational database cache.

2. Navigation: Navigation in ADO Recordset is based on the cursor mode. Even though it is specified to be a client-side Recordset, still the navigation pointer will move from one location to another on cursor model only. ADO.NET DataSet is an entirely offline, in-memory, and cache of data. All of its data is available all the time. At any time, we can retrieve any row or column, constraints or relation simply by accessing it either ordinarily or by retrieving it from a name-based collection.

3. Connectivity Model: The ADO Recordset was originally designed without the ability to operate in a disconnected environment. ADO.NET DataSet is specifically designed to be a disconnected in-memory database. ADO.NET DataSet follows a pure disconnected connectivity model and this gives it much more scalability and versatility in the amount of things it can do and how easily it can do that.

4. Marshalling and Serialization: In COM, through Marshalling, we can pass data from 1 COM component to another component at any time. Marshalling involves copying and processing data so that a complex type can appear to the receiving component the same as it appeared to the sending component. Marshalling is an expensive operation. ADO.NET Dataset and DataTable components support Remoting in the form of XML serialization. Rather than doing expensive Marshalling, it uses XML and sent data across boundaries.

5. Firewalls and DCOM and Remoting: Those who have worked with DCOM know that how difficult it is to marshal a DCOM component across a router. People generally came up with workarounds to solve this issue. ADO.NET DataSet uses Remoting, through which a DataSet / DataTable component can be serialized into XML, sent across the wire to a new AppDomain, and then Desterilized back to a fully functional DataSet. As the DataSet is completely disconnected, and it has no dependency, we lose absolutely nothing by serializing and transferring it through Remoting.

How do you handle data concurrency in .NET ?

One of the key features of the ADO.NET DataSet is that it can be a self-contained and disconnected data store. It can contain the schema and data from several rowsets in DataTable objects as well as information about how to relate the DataTable objects-all in memory. The DataSet neither knows nor cares where the data came from, nor does it need a link to an underlying data source. Because it is data source agnostic you can pass the DataSet around networks or even serialize it to XML and pass it across the Internet without losing any of its features. However, in a disconnected model, concurrency obviously becomes a much bigger problem than it is in a connected model.
In this column, I'll explore how ADO.NET is equipped to detect and handle concurrency violations. I'll begin by discussing scenarios in which concurrency violations can occur using the ADO.NET disconnected model. Then I will walk through an ASP.NET application that handles concurrency violations by giving the user the choice to overwrite the changes or to refresh the out-of-sync data and begin editing again. Because part of managing an optimistic concurrency model can involve keeping a timestamp (rowversion) or another type of flag that indicates when a row was last updated, I will show how to implement this type of flag and how to maintain its value after each database update.

Is Your Glass Half Full?

There are three common techniques for managing what happens when users try to modify the same data at the same time: pessimistic, optimistic, and last-in wins. They each handle concurrency issues differently.

The pessimistic approach says: "Nobody can cause a concurrency violation with my data if I do not let them get at the data while I have it." This tactic prevents concurrency in the first place but it limits scalability because it prevents all concurrent access. Pessimistic concurrency generally locks a row from the time it is retrieved until the time updates are flushed to the database. Since this requires a connection to remain open during the entire process, pessimistic concurrency cannot successfully be implemented in a disconnected model like the ADO.NET DataSet, which opens a connection only long enough to populate the DataSet then releases and closes, so a database lock cannot be held.

Another technique for dealing with concurrency is the last-in wins approach. This model is pretty straightforward and easy to implement-whatever data modification was made last is what gets written to the database. To implement this technique you only need to put the primary key fields of the row in the UPDATE statement's WHERE clause. No matter what is changed, the UPDATE statement will overwrite the changes with its own changes since all it is looking for is the row that matches the primary key values. Unlike the pessimistic model, the last-in wins approach allows users to read the data while it is being edited on screen. However, problems can occur when users try to modify the same data at the same time because users can overwrite each other's changes without being notified of the collision. The last-in wins approach does not detect or notify the user of violations because it does not care. However the optimistic technique does detect violations.

In optimistic concurrency models, a row is only locked during the update to the database. Therefore the data can be retrieved and updated by other users at any time other than during the actual row update operation. Optimistic concurrency allows the data to be read simultaneously by multiple users and blocks other users less often than its pessimistic counterpart, making it a good choice for ADO.NET. In optimistic models, it is important to implement some type of concurrency violation detection that will catch any additional attempt to modify records that have already been modified but not committed. You can write your code to handle the violation by always rejecting and canceling the change request or by overwriting the request based on some business rules. Another way to handle the concurrency violation is to let the user decide what to do. The sample application that is shown in Figure 1 illustrates some of the options that can be presented to the user in the event of a concurrency violation.

connection objcts

2007-12-24T14:55:00.000-08:00

Explain what a diffgram is and its usage ?

A DiffGram is an XML format that is used to identify current and original versions of data elements. The DataSet uses the DiffGram format to load and persist its contents, and to serialize its contents for transport across a network connection. When a DataSet is written as a DiffGram, it populates the DiffGram with all the necessary information to accurately recreate the contents, though not the schema, of the DataSet, including column values from both the Original and Current row versions, row error information, and row order.
When sending and retrieving a DataSet from an XML Web service, the DiffGram format is implicitly used. Additionally, when loading the contents of a DataSet from XML using the ReadXml method, or when writing the contents of a DataSet in XML using the WriteXml method, you can select that the contents be read or written as a DiffGram.
The DiffGram format is divided into three sections: the current data, the original (or "before") data, and an errors section, as shown in the following example.

The DiffGram format consists of the following blocks of data:

The name of this element, DataInstance, is used for explanation purposes in this documentation. A DataInstance element represents a DataSet or a row of a DataTable. Instead of DataInstance, the element would contain the name of the DataSet or DataTable. This block of the DiffGram format contains the current data, whether it has been modified or not. An element, or row, that has been modified is identified with the diffgr:hasChanges annotation.

This block of the DiffGram format contains the original version of a row. Elements in this block are matched to elements in the DataInstance block using the diffgr:id annotation.

This block of the DiffGram format contains error information for a particular row in the DataInstance block. Elements in this block are matched to elements in the DataInstance block using the diffgr:id annotation.

Which method do you invoke on the DataAdapter control to load your generated dataset with data?
You have to use the Fill method of the DataAdapter control and pass the dataset object as an argument to load the generated data.
Can you edit data in the Repeater control?
NO.

Which are the different IsolationLevels ?

Following are the various IsolationLevels:

• Serialized Data read by a current transaction cannot be changed by another transaction until the current transaction finishes. No new data can be inserted that would affect the current transaction. This is the safest isolation level and is the default.

• Repeatable Read Data read by a current transaction cannot be changed by another transaction until the current transaction finishes. Any type of new data can be inserted during a transaction.

• Read Committed A transaction cannot read data that is being modified by another transaction that has not committed. This is the default isolation level in Microsoft® SQL Server.

• Read Uncommitted A transaction can read any data, even if it is being modified by another transaction. This is the least safe isolation level but allows the highest concurrency.

• Any Any isolation level is supported. This setting is most commonly used by downstream components to avoid conflicts. This setting is useful because any downstream component must be configured with an isolation level that is equal to or less than the isolation level of its immediate upstream component. Therefore, a downstream component that has its isolation level configured as Any always uses the same isolation level that its immediate upstream component uses. If the root object in a transaction has its isolation level configured to Any, its isolation level becomes Serialized.

How xml files and be read and write using dataset?.
DataSet exposes method like ReadXml and WriteXml to read and write xml

What are the different rowversions available?

There are four types of Rowversions.
Current:
The current values for the row. This row version does not exist for rows with a RowState of Deleted.
Default :
The row the default version for the current DataRowState. For a DataRowState value of Added, Modified or Current, the default version is Current. For a DataRowState of Deleted, the version is Original. For a DataRowState value of Detached, the version is Proposed.
Original:
The row contains its original values.
Proposed:
The proposed values for the row. This row version exists during an edit operation on a row, or for a row that is not part of a DataRowCollection

What are different types of Commands available with DataAdapter ?
The SqlDataAdapter has SelectCommand, InsertCommand, DeleteCommand and UpdateCommand
What is a Dataset?
Datasets are the result of bringing together ADO and XML. A dataset contains one or more data of tabular XML, known as DataTables, these data can be treated separately, or can have relationships defined between them. Indeed these relationships give you ADO data SHAPING without needing to master the SHAPE language, which many people are not comfortable with.
The dataset is a disconnected in-memory cache database. The dataset object model looks like this:

Dataset
DataTableCollection
DataTable
DataView
DataRowCollection
DataRow
DataColumnCollection
DataColumn
ChildRelations
ParentRelations
Constraints
PrimaryKey
DataRelationCollection

Let’s take a look at each of these:

DataTableCollection: As we say that a DataSet is an in-memory database. So it has this collection, which holds data from multiple tables in a single DataSet object.

DataTable: In the DataTableCollection, we have DataTable objects, which represents the individual tables of the dataset.

DataView: The way we have views in database, same way we can have DataViews. We can use these DataViews to do Sort, filter data.
DataRowCollection: Similar to DataTableCollection, to represent each row in each Table we have DataRowCollection.

DataRow: To represent each and every row of the DataRowCollection, we have DataRows.
DataColumnCollection: Similar to DataTableCollection, to represent each column in each Table we have DataColumnCollection.

DataColumn: To represent each and every Column of the DataColumnCollection, we have DataColumn.

PrimaryKey: Dataset defines Primary key for the table and the primary key validation will take place without going to the database.
Constraints: We can define various constraints on the Tables, and can use Dataset.Tables(0).enforceConstraints. This will execute all the constraints, whenever we enter data in DataTable.

DataRelationCollection: as we know that we can have more than 1 table in the dataset, we can also define relationship between these tables using this collection and maintain a parent-child relationship.

Explain the ADO . Net Architecture ( .Net Data Provider)

ADO.Net is the data access model for .Net –based applications. It can be used to access relational database systems such as SQL SERVER 2000, Oracle, and many other data sources for which there is an OLD DB or ODBC provider. To a certain extent, ADO.NET represents the latest evolution of ADO technology. However, ADO.NET introduces some major changes and innovations that are aimed at the loosely coupled and inherently disconnected – nature of web applications.

A .Net Framework data provider is used to connecting to a database, executing commands, and retrieving results. Those results are either processed directly, or placed in an ADO.NET DataSet in order to be exposed to the user in an ad-hoc manner, combined with data from multiple sources, or remoted between tiers. The .NET Framework data provider is designed to be lightweight, creating a minimal layer between the data source and your code, increasing performance without sacrificing functionality.
Following are the 4 core objects of .Net Framework Data provider:

• Connection: Establishes a connection to a specific data source

• Command: Executes a command against a data source. Exposes Parameters and can execute within the scope of a Transaction from a Connection.

• DataReader: Reads a forward-only, read-only stream of data from a data source.

• DataAdapter: Populates a DataSet and resolves updates with the data source.
The .NET Framework includes the .NET Framework Data Provider for SQL Server (for Microsoft SQL Server version 7.0 or later), the .NET Framework Data Provider for OLE DB, and the .NET Framework Data Provider for ODBC.
The .NET Framework Data Provider for SQL Server: The .NET Framework Data Provider for SQL Server uses its own protocol to communicate with SQL Server. It is lightweight and performs well because it is optimized to access a SQL Server directly without adding an OLE DB or Open Database Connectivity (ODBC) layer. The following illustration contrasts the .NET Framework Data Provider for SQL Server with the .NET Framework Data Provider for OLE DB. The .NET Framework Data Provider for OLE DB communicates to an OLE DB data source through both the OLE DB Service component, which provides connection pooling and transaction services, and the OLE DB Provider for the data source
The .NET Framework Data Provider for OLE DB: The .NET Framework Data Provider for OLE DB uses native OLE DB through COM interoperability to enable data access. The .NET Framework Data Provider for OLE DB supports both local and distributed transactions. For distributed transactions, the .NET Framework Data Provider for OLE DB, by default, automatically enlists in a transaction and obtains transaction details from Windows 2000 Component Services.
The .NET Framework Data Provider for ODBC: The .NET Framework Data Provider for ODBC uses native ODBC Driver Manager (DM) through COM interoperability to enable data access. The ODBC data provider supports both local and distributed transactions. For distributed transactions, the ODBC data provider, by default, automatically enlists in a transaction and obtains transaction details from Windows 2000 Component Services.
The .NET Framework Data Provider for Oracle: The .NET Framework Data Provider for Oracle enables data access to Oracle data sources through Oracle client connectivity software. The data provider supports Oracle client software version 8.1.7 and later. The data provider supports both local and distributed transactions (the data provider automatically enlists in existing distributed transactions, but does not currently support the EnlistDistributedTransaction method).
The .NET Framework Data Provider for Oracle requires that Oracle client software (version 8.1.7 or later) be installed on the system before you can use it to connect to an Oracle data source.
.NET Framework Data Provider for Oracle classes are located in the System.Data.OracleClient namespace and are contained in the System.Data.OracleClient.dll assembly. You will need to reference both the System.Data.dll and the System.Data.OracleClient.dll when compiling an application that uses the data provider.
Choosing a .NET Framework Data Provider
.NET Framework Data Provider for SQL Server: Recommended for middle-tier applications using Microsoft SQL Server 7.0 or later. Recommended for single-tier applications using Microsoft Data Engine (MSDE) or Microsoft SQL Server 7.0 or later.
Recommended over use of the OLE DB Provider for SQL Server (SQLOLEDB) with the .NET Framework Data Provider for OLE DB. For Microsoft SQL Server version 6.5 and earlier, you must use the OLE DB Provider for SQL Server with the .NET Framework Data Provider for OLE DB.
.NET Framework Data Provider for OLE DB: Recommended for middle-tier applications using Microsoft SQL Server 6.5 or earlier, or any OLE DB provider. For Microsoft SQL Server 7.0 or later, the .NET Framework Data Provider for SQL Server is recommended. Recommended for single-tier applications using Microsoft Access databases. Use of a Microsoft Access database for a middle-tier application is not recommended.
.NET Framework Data Provider for ODBC: Recommended for middle-tier applications using ODBC data sources. Recommended for single-tier applications using ODBC data sources.
.NET Framework Data Provider for Oracle: Recommended for middle-tier applications using Oracle data sources. Recommended for single-tier applications using Oracle data sources. Supports Oracle client software version 8.1.7 and later. The .NET Framework Data Provider for Oracle classes are located in the System.Data.OracleClient namespace and are contained in the System.Data.OracleClient.dll assembly. You need to reference both the System.Data.dll and the System.Data.OracleClient.dll when compiling an application that uses the data provider