How Do You Ensure That The Database Connections Are Always Closed?
To ensure that the database connections are always closed, open the connection inside of a using block, as shown in the following code fragment. Doing so ensures that the connection is automatically closed when the code exits the block.
using (SqlConnection ConnectionObject = new SqlConnection())
{
ConnectionObject.Open();
//The database connection will be closed when the control exits the using code block
}
How Do You Read An Xml File Into A Dataset?
Using the DataSet object’s ReadXML method.
When Do You Use Executereader, Executenonquery, Executescalar Methods?
If the command or stored procedure that is being executed returns a set of rows, then we use ExecuteReader method.
If the command or stored procedure that is being executed returns a single value then we use ExecuteScalar method.
If the command or stored procedure performs INSERT, DELETE or UPDATE operations, then we use ExecuteNonQuery method.
ExecuteNon Query method returns an integer specifying the number of rows inserted, deleted or updated.
Can Your Class Inherit From Sqlcommand Class?
No,
you cannot inheirt from SqlCommand Class. SqlCommand Class is a sealed class. It is a compile time error.
Give An Example That Shows How To Execute A Stored Procedure In Ado.net?
using (SqlConnection ConnectionObject = new SqlConnection())
{
//Specify the name of the stored procedure to execute and the
Connection Object to use.
SqlCommand CommandObject = new
SqlCommand("StoredProcedureName", ConnectionObject);
//Specify the SQL Command type is a stored procedure
CommandObject.CommandType = CommandType.StoredProcedure;
//Open the connection
ConnectionObject.Open();
//Execute the Stored Procedure
int RecordsAffected = CommandObject.ExecuteNonQuery();
}
Can You Reuse A Sqlcommand Object?
Yes,
you can reset the CommandText property and reuse the SqlCommand object.
What Are The Methods That Can Ensure Asynchronous Execution Of The Transact-sql Statement Or Stored Procedure?
BeginExecuteNonQuery,
BeginExecuteReader.
What Is Sqlcommand.commandtimeout Property Used For?
CommandTimeout Property is used to Get or set the wait time before terminating the attempt to execute a command and generating an error.
//Specify the CommandTimeout property value
SqlCommand CommandObject = new
SqlCommand("StoredProcedureName", ConnectionObject);
//Wait for 10 seconds to execute the Stored procedure
CommandObject.CommandTimeout = 10;
The time is in seconds. The default is 30 seconds.
How Do You Create An Instance Of Sqldatareader Class?
To create an instance of SqlDataReader class, you must call the ExecuteReader method of the SqlCommand object, instead of directly using a constructor.
//Error! Cannot use SqlDataReader() constructor to create
an instance of SqlDataReader class.
SqlDataReader ReaderObject = new SqlDataReader();
//Call the ExecuteReader method of the SqlCommand object
SqlCommand CommandObject = new SqlCommand();
SqlDataReader ReaderObject = CommandObject.ExecuteReader();
Creating an instance of SqlDataReader class using SqlData Reader() constructor generates a compile time error - The type 'System.Data.SqlClient.SqlDataReader' has no constructors defined.
How Do You Programatically Check If A Specified Sqldatareader Instance Has Been Closed?
Use the IsClosed property of SqlDataReader to check if a specified SqlDataReader instance has been closed. If IsClosed property returns true, the SqlDataReader instance has been closed else not closed.
How Do You Get The Total Number Of Columns In The Current Row Of A Sqldatareader Instance?
FieldCount property can be used to get the total number of columns in the current row of a SqlData Reader instance.
What Is The Use Of Sqlparameter.direction Property?
SqlParameter.Direction Property is used to specify the Sql Parameter type - input-only, output-only, bidirectional, or a stored procedure return value parameter. The default is Input.
What Are The Advantages Of Using Sql Stored Procedures Instead Of Adhoc Sql Queries In An Asp.net Web Application?
Better Performance : As stored procedures are precompiled objects they execute faster than SQL queries. Every time we run a SQL query, the query has to be first compiled and then executed where as a stored procedure is already compiled. Hence executing stored procedures is much faster than executing SQL queries.
Better Security: For a given stored procedure you can specify who has the rights to execute. You cannot do the same for an SQL query. Writing the SQL statements inside our code is usually not a good idea. In this way you expose your database schema (design) in the code which may be changed. Hence most of the time programmers use stored procedures instead of plain SQL statements.
Reduced Network Traffic : Stored Procedures reside on the database server. If you have to execute a Stored Procedure from your ASP.NET web application you just specify the name of the Stored Procedure. So over the network you just send the name of the Stored Procedure. With an SQL query you have to send all the SQL statements over the network to the database server which could lead to increased network traffic.
Can You Update The Database Using Datareader Object?
No,
You cannot update the database using DataReader object. DataReader is read-only, foward only. It reads one record at atime. After DataReader finishes reading the current record, it moves to the next record. There is no way you can go back to the previous record.
What Is The Difference Between A Datareader And A Dataset?
DataReader
DatReader works on a Connection oriented architecture.
DataReader is read only, forward only. It reads one record at atime. After DataReader finishes reading the current record, it moves to the next record. There is no way you can go back to the previous record. So using a DataReader you read in forward direction only.
Updations are not possible with DataReader.
As DataReader is read only, forward only it is much faster than a DataSet.
DataSet
DataSet works on disconnected architecture.
Using a DataSet you can move in both directions. DataSet is bi directional.
Database can be updated from a DataSet.
DataSet is slower than DataReader.
Give An Example Scenario Of Using A Dataset And A Datareader?
If you want to just read and display the data(No updates, deletes, or inserts) then use a DataReader.
If you want to do a batch inserts, updates and deletes then use a DataSet.
What Are The Two Fundamental Objects In Ado.net ?
Datareader and Dataset are the two fundamental objects in ADO.NET.
What Is Difference Between Dataset And Datareader ?
Following are some major differences between dataset and datareader :- DataReader provides forward-only and read-only access to data, while the DataSet object can hold more than one table (in other words more than one rowset) from the same data source as well as the relationships between them. Dataset is a disconnected architecture while datareader is connected architecture. Dataset can persist contents while datareader can not persist contents, they are forward only.
What Are Major Difference Between Classic Ado And Ado.net ?
Following are some major differences between classic ADO and ADO.NET: Both As in classic ADO we had client and server side cursors they are no more present in ADO.NET. Note it's a disconnected model so they are no more applicable. Locking is not supported due to disconnected model. All data persist in XML as compared to classic ADO where data persisted in Binary format also.
What Is The Use Of Connection Object ?
They are used to connect a data to a Command object. An OleDb Connection object is used with an OLE-DB provider255 A SqlConnection object uses Tabular Data Services (TDS) with MS SQL Server.
What Is The Use Of Command Objects ?
They are used to connect connection object to Datareader or dataset. Following are the methods provided by command object :-
ExecuteNonQuery :- Executes the command defined in the CommandText property against the connection defined in the Connection property for a query that does not return any row (an UPDATE, DELETE or INSERT). Returns an Integer indicating the number of rows affected by the query.
ExecuteReader :- Executes the command defined in the CommandText property against the connection defined in the Connection property. Returns a "reader" object that is connected to the resulting rowset within the database, allowing the rows to be retrieved.
ExecuteScalar :- Executes the command defined in the CommandText property against the connection defined in the Connection property. Returns only single value (effectively the first column of the first row of the resulting rowset) any other returned columns and rows are discarded. It is fast and efficient when only a "singleton" value is required.
What Is The Use Of Dataadapter ?
These are objects that connect one or more Command objects to a Dataset object. They provide logic that would get data from the data store and populates the tables in the DataSet, or pushes the changes in the DataSet back into the data store.An OleDbDataAdapter object is used with an OLE-DB provider A SqlDataAdapter object uses Tabular Data Services with MS SQL Server.
What Are Basic Methods Of Dataadapter ?
There are three most commonly used methods of Dataadapter :-
Fill :- Executes the SelectCommand to fill the DataSet object with data from the data source. It an also be used to update (refresh) an existing table in a DataSet with changes made to the data in the original datasource if there is a primary key in the table in the DataSet.
FillSchema :- Uses the SelectCommand to extract just the schema for a table from the data source, and creates an empty table in the DataSet object with all the corresponding constraints.
Update:- Calls the respective InsertCommand, UpdateCommand, or DeleteCommand for each inserted, updated,or deleted row in the DataSet so as to update the original data source with the changes made to the content of the DataSet. This is a little like the UpdateBatch method provided by the ADO Recordset object, but in the DataSet it can be used to update more than one table.
What Is Dataset Object?
The DataSet provides the basis for disconnected storage and manipulation of relational data. We fill it from a data store, work with it while disconnected from that data store, then reconnect and flush changes back to the data store if required.
What Are The Various Objects In Dataset ?
Dataset has a collection of DataTable object within the Tables collection. Each DataTable object contains a collection of DataRow objects and a collection of DataColumn objects. There are also collections for the primary keys, constraints, and default values used in this table which is called as constraint collection, and the parent and child relationships between the tables. Finally, there is a DefaultView object for each table. This is used to create a DataView object based on the table, so that the data can be searched, filtered or otherwise manipulated while displaying the data.
How Can We Force The Connection Object To Close After My Datareader Is Closed ?
Command method Executereader takes a parameter called as CommandBehavior where in we can specify saying close connection automatically after the Datareader is close.
pobjDataReader = pobj Command .Execute Reader (CommandBehavior.CloseConnection)
I Want To Force The Datareader To Return Only Schema Of The Datastore Rather Than Data ?
pobjDataReader = pobjCommand.ExecuteReader(CommandBehavior.SchemaOnly).
How Can We Fine Tune The Command Object When We Are Expecting A Single Row ?
Again CommandBehaviour enumeration provides two values SingleResult and SingleRow. If you are expecting a single value then pass “CommandBehaviour.SingleResult” and the query is optimized accordingly, if you are expecting single row then pass “CommandBehaviour.SingleRow” and query is optimized according to single row.
To ensure that the database connections are always closed, open the connection inside of a using block, as shown in the following code fragment. Doing so ensures that the connection is automatically closed when the code exits the block.
using (SqlConnection ConnectionObject = new SqlConnection())
{
ConnectionObject.Open();
//The database connection will be closed when the control exits the using code block
}
How Do You Read An Xml File Into A Dataset?
Using the DataSet object’s ReadXML method.
When Do You Use Executereader, Executenonquery, Executescalar Methods?
If the command or stored procedure that is being executed returns a set of rows, then we use ExecuteReader method.
If the command or stored procedure that is being executed returns a single value then we use ExecuteScalar method.
If the command or stored procedure performs INSERT, DELETE or UPDATE operations, then we use ExecuteNonQuery method.
ExecuteNon Query method returns an integer specifying the number of rows inserted, deleted or updated.
Can Your Class Inherit From Sqlcommand Class?
No,
you cannot inheirt from SqlCommand Class. SqlCommand Class is a sealed class. It is a compile time error.
 |
| Red Hat Most Frequently Asked Latest ASP.NET Interview Questions Answers |
Give An Example That Shows How To Execute A Stored Procedure In Ado.net?
using (SqlConnection ConnectionObject = new SqlConnection())
{
//Specify the name of the stored procedure to execute and the
Connection Object to use.
SqlCommand CommandObject = new
SqlCommand("StoredProcedureName", ConnectionObject);
//Specify the SQL Command type is a stored procedure
CommandObject.CommandType = CommandType.StoredProcedure;
//Open the connection
ConnectionObject.Open();
//Execute the Stored Procedure
int RecordsAffected = CommandObject.ExecuteNonQuery();
}
Can You Reuse A Sqlcommand Object?
Yes,
you can reset the CommandText property and reuse the SqlCommand object.
What Are The Methods That Can Ensure Asynchronous Execution Of The Transact-sql Statement Or Stored Procedure?
BeginExecuteNonQuery,
BeginExecuteReader.
What Is Sqlcommand.commandtimeout Property Used For?
CommandTimeout Property is used to Get or set the wait time before terminating the attempt to execute a command and generating an error.
//Specify the CommandTimeout property value
SqlCommand CommandObject = new
SqlCommand("StoredProcedureName", ConnectionObject);
//Wait for 10 seconds to execute the Stored procedure
CommandObject.CommandTimeout = 10;
The time is in seconds. The default is 30 seconds.
How Do You Create An Instance Of Sqldatareader Class?
To create an instance of SqlDataReader class, you must call the ExecuteReader method of the SqlCommand object, instead of directly using a constructor.
//Error! Cannot use SqlDataReader() constructor to create
an instance of SqlDataReader class.
SqlDataReader ReaderObject = new SqlDataReader();
//Call the ExecuteReader method of the SqlCommand object
SqlCommand CommandObject = new SqlCommand();
SqlDataReader ReaderObject = CommandObject.ExecuteReader();
Creating an instance of SqlDataReader class using SqlData Reader() constructor generates a compile time error - The type 'System.Data.SqlClient.SqlDataReader' has no constructors defined.
How Do You Programatically Check If A Specified Sqldatareader Instance Has Been Closed?
Use the IsClosed property of SqlDataReader to check if a specified SqlDataReader instance has been closed. If IsClosed property returns true, the SqlDataReader instance has been closed else not closed.
How Do You Get The Total Number Of Columns In The Current Row Of A Sqldatareader Instance?
FieldCount property can be used to get the total number of columns in the current row of a SqlData Reader instance.
What Is The Use Of Sqlparameter.direction Property?
SqlParameter.Direction Property is used to specify the Sql Parameter type - input-only, output-only, bidirectional, or a stored procedure return value parameter. The default is Input.
What Are The Advantages Of Using Sql Stored Procedures Instead Of Adhoc Sql Queries In An Asp.net Web Application?
Better Performance : As stored procedures are precompiled objects they execute faster than SQL queries. Every time we run a SQL query, the query has to be first compiled and then executed where as a stored procedure is already compiled. Hence executing stored procedures is much faster than executing SQL queries.
Better Security: For a given stored procedure you can specify who has the rights to execute. You cannot do the same for an SQL query. Writing the SQL statements inside our code is usually not a good idea. In this way you expose your database schema (design) in the code which may be changed. Hence most of the time programmers use stored procedures instead of plain SQL statements.
Reduced Network Traffic : Stored Procedures reside on the database server. If you have to execute a Stored Procedure from your ASP.NET web application you just specify the name of the Stored Procedure. So over the network you just send the name of the Stored Procedure. With an SQL query you have to send all the SQL statements over the network to the database server which could lead to increased network traffic.
Can You Update The Database Using Datareader Object?
No,
You cannot update the database using DataReader object. DataReader is read-only, foward only. It reads one record at atime. After DataReader finishes reading the current record, it moves to the next record. There is no way you can go back to the previous record.
What Is The Difference Between A Datareader And A Dataset?
DataReader
DatReader works on a Connection oriented architecture.
DataReader is read only, forward only. It reads one record at atime. After DataReader finishes reading the current record, it moves to the next record. There is no way you can go back to the previous record. So using a DataReader you read in forward direction only.
Updations are not possible with DataReader.
As DataReader is read only, forward only it is much faster than a DataSet.
DataSet
DataSet works on disconnected architecture.
Using a DataSet you can move in both directions. DataSet is bi directional.
Database can be updated from a DataSet.
DataSet is slower than DataReader.
Give An Example Scenario Of Using A Dataset And A Datareader?
If you want to just read and display the data(No updates, deletes, or inserts) then use a DataReader.
If you want to do a batch inserts, updates and deletes then use a DataSet.
What Are The Two Fundamental Objects In Ado.net ?
Datareader and Dataset are the two fundamental objects in ADO.NET.
What Is Difference Between Dataset And Datareader ?
Following are some major differences between dataset and datareader :- DataReader provides forward-only and read-only access to data, while the DataSet object can hold more than one table (in other words more than one rowset) from the same data source as well as the relationships between them. Dataset is a disconnected architecture while datareader is connected architecture. Dataset can persist contents while datareader can not persist contents, they are forward only.
What Are Major Difference Between Classic Ado And Ado.net ?
Following are some major differences between classic ADO and ADO.NET: Both As in classic ADO we had client and server side cursors they are no more present in ADO.NET. Note it's a disconnected model so they are no more applicable. Locking is not supported due to disconnected model. All data persist in XML as compared to classic ADO where data persisted in Binary format also.
What Is The Use Of Connection Object ?
They are used to connect a data to a Command object. An OleDb Connection object is used with an OLE-DB provider255 A SqlConnection object uses Tabular Data Services (TDS) with MS SQL Server.
What Is The Use Of Command Objects ?
They are used to connect connection object to Datareader or dataset. Following are the methods provided by command object :-
ExecuteNonQuery :- Executes the command defined in the CommandText property against the connection defined in the Connection property for a query that does not return any row (an UPDATE, DELETE or INSERT). Returns an Integer indicating the number of rows affected by the query.
ExecuteReader :- Executes the command defined in the CommandText property against the connection defined in the Connection property. Returns a "reader" object that is connected to the resulting rowset within the database, allowing the rows to be retrieved.
ExecuteScalar :- Executes the command defined in the CommandText property against the connection defined in the Connection property. Returns only single value (effectively the first column of the first row of the resulting rowset) any other returned columns and rows are discarded. It is fast and efficient when only a "singleton" value is required.
What Is The Use Of Dataadapter ?
These are objects that connect one or more Command objects to a Dataset object. They provide logic that would get data from the data store and populates the tables in the DataSet, or pushes the changes in the DataSet back into the data store.An OleDbDataAdapter object is used with an OLE-DB provider A SqlDataAdapter object uses Tabular Data Services with MS SQL Server.
What Are Basic Methods Of Dataadapter ?
There are three most commonly used methods of Dataadapter :-
Fill :- Executes the SelectCommand to fill the DataSet object with data from the data source. It an also be used to update (refresh) an existing table in a DataSet with changes made to the data in the original datasource if there is a primary key in the table in the DataSet.
FillSchema :- Uses the SelectCommand to extract just the schema for a table from the data source, and creates an empty table in the DataSet object with all the corresponding constraints.
Update:- Calls the respective InsertCommand, UpdateCommand, or DeleteCommand for each inserted, updated,or deleted row in the DataSet so as to update the original data source with the changes made to the content of the DataSet. This is a little like the UpdateBatch method provided by the ADO Recordset object, but in the DataSet it can be used to update more than one table.
What Is Dataset Object?
The DataSet provides the basis for disconnected storage and manipulation of relational data. We fill it from a data store, work with it while disconnected from that data store, then reconnect and flush changes back to the data store if required.
What Are The Various Objects In Dataset ?
Dataset has a collection of DataTable object within the Tables collection. Each DataTable object contains a collection of DataRow objects and a collection of DataColumn objects. There are also collections for the primary keys, constraints, and default values used in this table which is called as constraint collection, and the parent and child relationships between the tables. Finally, there is a DefaultView object for each table. This is used to create a DataView object based on the table, so that the data can be searched, filtered or otherwise manipulated while displaying the data.
How Can We Force The Connection Object To Close After My Datareader Is Closed ?
Command method Executereader takes a parameter called as CommandBehavior where in we can specify saying close connection automatically after the Datareader is close.
pobjDataReader = pobj Command .Execute Reader (CommandBehavior.CloseConnection)
I Want To Force The Datareader To Return Only Schema Of The Datastore Rather Than Data ?
pobjDataReader = pobjCommand.ExecuteReader(CommandBehavior.SchemaOnly).
How Can We Fine Tune The Command Object When We Are Expecting A Single Row ?
Again CommandBehaviour enumeration provides two values SingleResult and SingleRow. If you are expecting a single value then pass “CommandBehaviour.SingleResult” and the query is optimized accordingly, if you are expecting single row then pass “CommandBehaviour.SingleRow” and query is optimized according to single row.
Post a Comment