Question 1-
What provider ADO.NET use by default?
Answer - There's no default provider as such but there are generic providers (OLE DB and ODBC) which are not limited to a specific database such as SQL Server or Oracle.
Question 2- How to select last N records from a Table ?
Answer - SELECT TOP N * FROM EMP ORDER BY EMPNO DESC
Question 3- How will you copy the structure of a table without copying the data in SQL?
Answer - SELECT TOP 0 * INTO newtable FROM oldtable
OR
SELECT * INTO newTable FROM oldTable WHERE 0 = 1
Question 3- What is fastest way of accessing a row in a table?
Answer - You can use ROWID.CONSTRAINTS
Question 4- What is fastest way of accessing a row in a table?
Answer - You can use ROWID.CONSTRAINTS
Question 5- What is difference in Drop, Delete and Truncate commands in SQL.
Answer - 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.
TRUNCATE
TRUNCATE is faster and uses fewer system and transaction log resources than DELETE.
TRUNCATE removes the data by deallocating the data pages used to store the table's data, and only the page deallocations are recorded in the transaction log.
TRUNCATE removes all rows from a table, but the table structure and its columns, constraints, indexes and so on remain. The counter used by an identity for new rows is reset to the seed for the column.You cannot use TRUNCATE TABLE on a table referenced by a FOREIGN KEY constraint.Because TRUNCATE TABLE is not logged, it cannot activate a trigger.
TRUNCATE can not be Rolled back.
TRUNCATE is DDL Command.
TRUNCATE Resets identity of the table.
DELETE
DELETE removes rows one at a time and records an entry in the transaction log for each deleted row.If you want to retain the identity counter, use DELETE instead. If you want to remove table definition and its data, use the DROP TABLE statement.
DELETE Can be used with or without a WHERE clause
DELETE Activates Triggers.
DELETE Can be Rolled back.
DELETE is DML Command.
DELETE does not reset identity of the table.
DROP
Removes a table definition and all data, indexes, triggers, constraints, and permission specifications for that table. Any view or stored procedure that references the dropped table must be explicitly dropped by using the DROP VIEW or DROP PROCEDURE statement.
Question 6- Explain the architecture of SQL Server?
Answer - http://msdn.microsoft.com/en-us/library/aa933154(v=sql.80).aspx
Question 7- Explain the basic use of DataView?
Answer - Data View is contains a table or small table part with some rows. It is used to sort and filter data with in a data table.
DataView dv1 = ds.Tables[0].DefaultView;//CREATE DATA VIEW
dv1.Sort = "Title"; //SORT ROWS
dv1.RowFilter = "Title like a%"; // FILTER ROWS
DataRowView[] drv = dv1.FindRows("my movie"); //FIND ROWS WHICH TITLE
Question 8- What is Linked Server ?
Answer - Linked server is the concept in sql server by using which you can add (link) another server to your existing server. Means your existing server make link with another server such as oracle or any so you can make a operation on database of both sql server on a single sql server opening screen.
To see goto Server object---Linked Server--its displyas the list of linked server,then right click on existing linked server-and clik on New linked server
Question 9- What is DataRowCollection in Ado.Net?
Answer - http://msdn.microsoft.com/en-us/library/system.data.datarowcollection.aspx
Question 10- What is DataViewManager in ado.net?
Answer - "DataViewManager is used to manage view settings of the tables in a DataSet. A DataViewManager is best suited for views that consist of a combination of multiple tables. The properties like ApplyDefaultSort, Sort, RowFilter, and RowStateFilter are referenced using DataViewSetting."
Question 11- What is DataViewManager in ado.net?
Answer - "DataViewManager is used to manage view settings of the tables in a DataSet. A DataViewManager is best suited for views that consist of a combination of multiple tables. The properties like ApplyDefaultSort, Sort, RowFilter, and RowStateFilter are referenced using DataViewSetting."
Question 12- How to copy one database table to another database(with all things like procedure,Trigger etc).
Answer - You can do through import /export wizard .Right click on database go to task there u can find this option
OR
http://csharpdotnetfreak.blogspot.com/2012/07/copy-database-table-to-another-sqlserver.html
Question 13- What are the different layers in ADO.Net?
Answer - http://www.beansoftware.com/ASP.NET-Tutorials/ADO.NET-DAL.aspx
Question 14- What are the different layers in ADO.Net?
Answer - http://www.beansoftware.com/ASP.NET-Tutorials/ADO.NET-DAL.aspx
Question 15- What is maximum Pool Size in ADO.NET Connection String?
Answer - http://www.c-sharpcorner.com/uploadfile/4d56e1/connection-pooling-ado-net/
Question 16- What is typed dataset in Ado.net?
Answer - Typed DataSet has a Xml schema and an Untyped DataSet donot have Xml schema.
We can insert additional columns in the type dataset but in untype we can't insert additional columns here.A typed DataSet is a class that derives from a DataSet. As such, it inherits all the methods, events, and properties of a DataSet. Additionally, a typed DataSet provides strongly typed methods, events, and properties. This means you can access tables and columns by name, instead of using collection-based methods.
Or you can follow the following link
http://msdn.microsoft.com/en-us/library/8bw9ksd6%28v=vs.71%29.aspx
http://www.c-sharpcorner.com/UploadFile/rupadhyaya/TypedDataSets12032005021013AM/TypedDataSets.aspx
http://www.wrox.com/WileyCDA/Section/Strongly-Typed-DataSets-in-ASP-NET-2-0-Pages.id-302833.html
http://www.codeguru.com/csharp/.net/net_data/article.php/c19561/Introducing-ADONET-and-the-Typed-DataSet.html
Question 17- How to Fetch the last inserted record in a particular table?
Answer - If a table have identity column then you can fetch the last inserted record as below
select * from <tablename> where id=(select max(id) from <table name>)
If you accessing inside Trigger? Magic tables will help you in this case.
Question 18- What are main difference between classic ADO and ADO.NET?
Answer - Following are some major differences between both :-
In ADO we have recordset and in ADO.NET we have dataset.
In recordset we can only have one table. If we want to accommodate more than one tables we need to do inner join and fill the recordset. Dataset can have multiple tables.
All data persist in XML as compared to classic ADO where data persisted in Binary format also.
For another diffrences related to ADO Refer:
http://onlydifferencefaqs.blogspot.in/2012/07/adonet-difference-faqs-1.html
Question 19- What is the Purpose of connection pooling in ADO.NET?
Answer - It's a technique to allow multiple clinets to make use of a cached set of shared and reusable connection objects providing access to a database.
Opening/Closing database connections is an expensive process and hence connection pools improve the performance of execution of commands on a database for which we maintain connection objects in the pool. It facilitates reuse of the same connection object to serve a number of client requests. Every time a client request is received, the pool is searched for an available connection object and it's highly likely that it gets a free connection object.
Otherwise, either the incoming requests are queued or a new connection object is created and added to the pool (depending upon how many connections are already there in the pool and how many the particular implementation and configuration can support).
As soon as a request finishes using a connection object, the object is given back to the pool from where it's assigned to one of the queued requests (based on what scheduling algorithm the particular connection pool implementation follows for serving queued requests). Since most of the requests are served using existing connection objects only so the connection pooling approach brings down the average time required for the users to wait for establishing the connection to the database.
Or You can refer the below links,
http://www.c-sharpcorner.com/uploadfile/mahesh/understanding-connection-pooling-in-ado-net/default.aspx
http://www.c-sharpcorner.com/uploadfile/4d56e1/connection-pooling-ado-net/default.aspx
http://www.c-sharpcorner.com/UploadFile/dsdaf/ConnPooling07262006093645AM/ConnPooling.aspx
http://www.c-sharpcorner.com/UploadFile/munnamax/connectionpooling08112007062332AM/connectionpooling.aspx
Question 20- What is the Difference between OLEDB Provider and SqlClient?
Answer - Dot NET Framework Data Provider for OLE DB is universal for accessing other sources, like Oracle, DB2, Microsoft Access and Informix, but it's a .NET layer on top of OLEDB layer, Performance is not so faster.
.NET Framework Data Provider for SQL Server is used only for sql server database is high-speed and robust Performance is faster.
.NET Framework Data Provider for Oracle is used only for Oracle database is high-speed and robust Performance is faster.
Question 21- What is enable and disable connection pooling in Ado.Net?
Answer - Dot NET it is enabled by default but if you want to just make sure set Pooling=true in the connection string. To disable connection pooling set Pooling=false in connection string if it is an ADO.NET Connection.
Question 22- How to create a strong name command in dot net?
Answer - A name that consists of an assembly's identity.Its simple text name, version number, and culture information (if provided)—strengthened by a public key and a digital signature generated over the assembly. Because the assembly manifest contains file hashes for all the files that constitute the assembly implementation, it is sufficient to generate the digital signature over just the one file in the assembly that contains the assembly manifest. Assemblies with the same strong name are expected to be identical
or you refer the below links
http://www.visualbuilder.com/dotnet/tutorial/strong-name-in-net-assembly
http://www.codeguru.com/csharp/.net/net_general/article.php/c4643/Giving-a-NET-Assembly-a-Strong-Name.htm
http://www.c-sharpcorner.com/UploadFile/hemantpatil/111222007032935AM/1.aspx
Question 23- What are the fundamental object in Ado.net.
Answer -
http://www.csharp-station.com/Tutorial/AdoDotNet/lesson01
http://www.functionx.com/adonet/index.htmQuestion 24-What is the maximum size of row in SQL?
Answer -It will depend on which RDBMS you're using.
In SQL Server 2000 there was a maximum row size of 8094 bytes which is the size of a memory page (8K) less some overhead.
However, in SQL Server 2005 or later, there isn't really a maximum size because pointers can be added to the first page to point to other pages, if the limit would otherwise be exceeded.
There's a link here which explains how all this works behind the scenes:
http://codebetter.com/raymondlewallen/2005/12/30/how-sql-server-2005-bypasses-the-8kb-row-size-limitation/
Answer - There's no default provider as such but there are generic providers (OLE DB and ODBC) which are not limited to a specific database such as SQL Server or Oracle.
Question 2- How to select last N records from a Table ?
Answer - SELECT TOP N * FROM EMP ORDER BY EMPNO DESC
Question 3- How will you copy the structure of a table without copying the data in SQL?
Answer - SELECT TOP 0 * INTO newtable FROM oldtable
OR
SELECT * INTO newTable FROM oldTable WHERE 0 = 1
Question 3- What is fastest way of accessing a row in a table?
Answer - You can use ROWID.CONSTRAINTS
Question 4- What is fastest way of accessing a row in a table?
Answer - You can use ROWID.CONSTRAINTS
Question 5- What is difference in Drop, Delete and Truncate commands in SQL.
Answer - 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.
TRUNCATE
TRUNCATE is faster and uses fewer system and transaction log resources than DELETE.
TRUNCATE removes the data by deallocating the data pages used to store the table's data, and only the page deallocations are recorded in the transaction log.
TRUNCATE removes all rows from a table, but the table structure and its columns, constraints, indexes and so on remain. The counter used by an identity for new rows is reset to the seed for the column.You cannot use TRUNCATE TABLE on a table referenced by a FOREIGN KEY constraint.Because TRUNCATE TABLE is not logged, it cannot activate a trigger.
TRUNCATE can not be Rolled back.
TRUNCATE is DDL Command.
TRUNCATE Resets identity of the table.
DELETE
DELETE removes rows one at a time and records an entry in the transaction log for each deleted row.If you want to retain the identity counter, use DELETE instead. If you want to remove table definition and its data, use the DROP TABLE statement.
DELETE Can be used with or without a WHERE clause
DELETE Activates Triggers.
DELETE Can be Rolled back.
DELETE is DML Command.
DELETE does not reset identity of the table.
DROP
Removes a table definition and all data, indexes, triggers, constraints, and permission specifications for that table. Any view or stored procedure that references the dropped table must be explicitly dropped by using the DROP VIEW or DROP PROCEDURE statement.
Question 6- Explain the architecture of SQL Server?
Answer - http://msdn.microsoft.com/en-us/library/aa933154(v=sql.80).aspx
Question 7- Explain the basic use of DataView?
Answer - Data View is contains a table or small table part with some rows. It is used to sort and filter data with in a data table.
DataView dv1 = ds.Tables[0].DefaultView;//CREATE DATA VIEW
dv1.Sort = "Title"; //SORT ROWS
dv1.RowFilter = "Title like a%"; // FILTER ROWS
DataRowView[] drv = dv1.FindRows("my movie"); //FIND ROWS WHICH TITLE
Question 8- What is Linked Server ?
Answer - Linked server is the concept in sql server by using which you can add (link) another server to your existing server. Means your existing server make link with another server such as oracle or any so you can make a operation on database of both sql server on a single sql server opening screen.
To see goto Server object---Linked Server--its displyas the list of linked server,then right click on existing linked server-and clik on New linked server
Question 9- What is DataRowCollection in Ado.Net?
Answer - http://msdn.microsoft.com/en-us/library/system.data.datarowcollection.aspx
Question 10- What is DataViewManager in ado.net?
Answer - "DataViewManager is used to manage view settings of the tables in a DataSet. A DataViewManager is best suited for views that consist of a combination of multiple tables. The properties like ApplyDefaultSort, Sort, RowFilter, and RowStateFilter are referenced using DataViewSetting."
Question 11- What is DataViewManager in ado.net?
Answer - "DataViewManager is used to manage view settings of the tables in a DataSet. A DataViewManager is best suited for views that consist of a combination of multiple tables. The properties like ApplyDefaultSort, Sort, RowFilter, and RowStateFilter are referenced using DataViewSetting."
Question 12- How to copy one database table to another database(with all things like procedure,Trigger etc).
Answer - You can do through import /export wizard .Right click on database go to task there u can find this option
OR
http://csharpdotnetfreak.blogspot.com/2012/07/copy-database-table-to-another-sqlserver.html
Question 13- What are the different layers in ADO.Net?
Answer - http://www.beansoftware.com/ASP.NET-Tutorials/ADO.NET-DAL.aspx
Question 14- What are the different layers in ADO.Net?
Answer - http://www.beansoftware.com/ASP.NET-Tutorials/ADO.NET-DAL.aspx
Question 15- What is maximum Pool Size in ADO.NET Connection String?
Answer - http://www.c-sharpcorner.com/uploadfile/4d56e1/connection-pooling-ado-net/
Question 16- What is typed dataset in Ado.net?
Answer - Typed DataSet has a Xml schema and an Untyped DataSet donot have Xml schema.
We can insert additional columns in the type dataset but in untype we can't insert additional columns here.A typed DataSet is a class that derives from a DataSet. As such, it inherits all the methods, events, and properties of a DataSet. Additionally, a typed DataSet provides strongly typed methods, events, and properties. This means you can access tables and columns by name, instead of using collection-based methods.
Or you can follow the following link
http://msdn.microsoft.com/en-us/library/8bw9ksd6%28v=vs.71%29.aspx
http://www.c-sharpcorner.com/UploadFile/rupadhyaya/TypedDataSets12032005021013AM/TypedDataSets.aspx
http://www.wrox.com/WileyCDA/Section/Strongly-Typed-DataSets-in-ASP-NET-2-0-Pages.id-302833.html
http://www.codeguru.com/csharp/.net/net_data/article.php/c19561/Introducing-ADONET-and-the-Typed-DataSet.html
Question 17- How to Fetch the last inserted record in a particular table?
Answer - If a table have identity column then you can fetch the last inserted record as below
select * from <tablename> where id=(select max(id) from <table name>)
If you accessing inside Trigger? Magic tables will help you in this case.
Question 18- What are main difference between classic ADO and ADO.NET?
Answer - Following are some major differences between both :-
In ADO we have recordset and in ADO.NET we have dataset.
In recordset we can only have one table. If we want to accommodate more than one tables we need to do inner join and fill the recordset. Dataset can have multiple tables.
All data persist in XML as compared to classic ADO where data persisted in Binary format also.
For another diffrences related to ADO Refer:
http://onlydifferencefaqs.blogspot.in/2012/07/adonet-difference-faqs-1.html
Question 19- What is the Purpose of connection pooling in ADO.NET?
Answer - It's a technique to allow multiple clinets to make use of a cached set of shared and reusable connection objects providing access to a database.
Opening/Closing database connections is an expensive process and hence connection pools improve the performance of execution of commands on a database for which we maintain connection objects in the pool. It facilitates reuse of the same connection object to serve a number of client requests. Every time a client request is received, the pool is searched for an available connection object and it's highly likely that it gets a free connection object.
Otherwise, either the incoming requests are queued or a new connection object is created and added to the pool (depending upon how many connections are already there in the pool and how many the particular implementation and configuration can support).
As soon as a request finishes using a connection object, the object is given back to the pool from where it's assigned to one of the queued requests (based on what scheduling algorithm the particular connection pool implementation follows for serving queued requests). Since most of the requests are served using existing connection objects only so the connection pooling approach brings down the average time required for the users to wait for establishing the connection to the database.
Or You can refer the below links,
http://www.c-sharpcorner.com/uploadfile/mahesh/understanding-connection-pooling-in-ado-net/default.aspx
http://www.c-sharpcorner.com/uploadfile/4d56e1/connection-pooling-ado-net/default.aspx
http://www.c-sharpcorner.com/UploadFile/dsdaf/ConnPooling07262006093645AM/ConnPooling.aspx
http://www.c-sharpcorner.com/UploadFile/munnamax/connectionpooling08112007062332AM/connectionpooling.aspx
Question 20- What is the Difference between OLEDB Provider and SqlClient?
Answer - Dot NET Framework Data Provider for OLE DB is universal for accessing other sources, like Oracle, DB2, Microsoft Access and Informix, but it's a .NET layer on top of OLEDB layer, Performance is not so faster.
.NET Framework Data Provider for SQL Server is used only for sql server database is high-speed and robust Performance is faster.
.NET Framework Data Provider for Oracle is used only for Oracle database is high-speed and robust Performance is faster.
Question 21- What is enable and disable connection pooling in Ado.Net?
Answer - Dot NET it is enabled by default but if you want to just make sure set Pooling=true in the connection string. To disable connection pooling set Pooling=false in connection string if it is an ADO.NET Connection.
Question 22- How to create a strong name command in dot net?
Answer - A name that consists of an assembly's identity.Its simple text name, version number, and culture information (if provided)—strengthened by a public key and a digital signature generated over the assembly. Because the assembly manifest contains file hashes for all the files that constitute the assembly implementation, it is sufficient to generate the digital signature over just the one file in the assembly that contains the assembly manifest. Assemblies with the same strong name are expected to be identical
or you refer the below links
http://www.visualbuilder.com/dotnet/tutorial/strong-name-in-net-assembly
http://www.codeguru.com/csharp/.net/net_general/article.php/c4643/Giving-a-NET-Assembly-a-Strong-Name.htm
http://www.c-sharpcorner.com/UploadFile/hemantpatil/111222007032935AM/1.aspx
Question 23- What are the fundamental object in Ado.net.
Answer -
DataReader - It is
basically used in the Connected Environment. For example... If you want to
select some data from the database, then you can just run your procedure or
your query through the command object and can use the DataReader oBJECT to
read the values fetched through the query and display or use it in the
further process.... For this the Connection with the Data Source is
compulsary. You cannot use this object in the disconnected environment.
Dataset -
It is basically used in the disconnected environment. You just fetch the
data from the Datasource using a DataAdapted and fill the Dataset using the
Fill() method of the Adapter Object. The Dataset can store multiple content
in tabular format. Once you fetch the values from the Datasource in the
Adapter and fill the Dataset, you can use the Dataset even if the connection
with the DataSource is closed.
In ADO.NET, some other Objects like Connection Object, Command Object, Data Adapters, Data Tables, etc also are the most vital objects. Atleast you always need to use the Connection Object for connecting with the database. Or you see the following links
http://www.codeproject.com/Articles/8477/Using-ADO-NET-for-beginnersIn ADO.NET, some other Objects like Connection Object, Command Object, Data Adapters, Data Tables, etc also are the most vital objects. Atleast you always need to use the Connection Object for connecting with the database. Or you see the following links
http://www.csharp-station.com/Tutorial/AdoDotNet/lesson01
http://www.functionx.com/adonet/index.htmQuestion 24-What is the maximum size of row in SQL?
Answer -It will depend on which RDBMS you're using.
In SQL Server 2000 there was a maximum row size of 8094 bytes which is the size of a memory page (8K) less some overhead.
However, in SQL Server 2005 or later, there isn't really a maximum size because pointers can be added to the first page to point to other pages, if the limit would otherwise be exceeded.
There's a link here which explains how all this works behind the scenes:
http://codebetter.com/raymondlewallen/2005/12/30/how-sql-server-2005-bypasses-the-8kb-row-size-limitation/
