Ado.net Interview Questions and Answers
Question - 41 : - What is Connection pooling?
Answer - 41 : -
The task of grouping database connections in the cache memory is to make them available whenever there is a requirement of connection. Opening a new database connection every time is a time-consuming process. Connection pooling allows you to reuse existing and active database connections, whenever there is a need, and thus increases the application performance.
By setting the pooling property into true or false in the connection string, we can enable or disable the connection pooling in the application. It is enabled by default in every application.
Question - 42 : - What is DataTable in ADO.NET?
Answer - 42 : -
DataTable in ADO.NET represents a single table in a DataSet that has in-memory relational data. The data within DataTable is local to the .NET framework-based application to which it belongs but can be populated using a DataAdapter from different data sources such as Microsoft SQL Server. The DataTable class belongs to the System.Data namespace within the library of .NET Framework.
DataTable can be represented in .aspx.cs code as given below:
protected void DataTableExample()
{
SqlConnection conn = new SqlConnection("Write the database connection string");
conn.Open();
SqlCommand cd = new SqlCommand("Write the query or procedure", conn);
SqlDataAdapter d = new SqlDataAdapter(cd);
DataTable dt = new DataTable();
d.Fill(dt);
grid.DataSource = dt;
grid.DataBind();
}
The SQL connection and SQL command object will be created. We pass the SQL query to the object of the SQL command class. A new data table object will be created by using the DataTable class and it is filled with data using a data adapter.
Question - 43 : - Name some of the properties and methods provided by the DataReader in ADO.NET?
Answer - 43 : -
Some of the properties provided by the DataReader are as follows:
- Depth: It represents the depth of nesting for a row.
- FieldCount: It gives the total column count in a row.
- Item: It obtains the column value in a native format.
- RecordsAffected: It gives the number of transaction affected rows.
- IsClosed: It represents whether a data reader is closed.
- VisibleFieldCount: It is used to obtain the number of unhidden fields in the SqlDataReader.
Some of the methods provided by the DataReader are as follows:
- Read(): This method reads a record from the SQL Server database.
- Close(): It closes a SqlDataReader object.
- NextResult(): It moves the data reader to the next result during the time of batch transactions.
- Getxxx(): Various types of Getxxx() methods such as GetBoolean(Int32), GetChar(Int32), GetFloat(Int32), GetDouble(Int32), etc., are provided by the DataReader. These methods will read a value of a particular data type from a column. For example, GetFloat() will return a column value as a Float and GetChar as a character.
Question - 44 : - What are the conditions for connection pooling?
Answer - 44 : -
The conditions for connection pooling are:
- There must be several processes with the same parameters and security settings so that they can share the same connection.
- The connection string should be identical.
Question - 45 : - What are the data providers in ADO.NET?
Answer - 45 : -
Data providers are used to transferring the data between the client application and the data store. It encapsulates the database-specific details. Data providers are helpful for database connection, data retrieval, storing the data in a dataset, reading the retrieved data, and updating the database.
The data providers that comes along with the ADO.NET Framework are:
- OLE DB: The OLEDB provider is available under System.Data.OleDb namespace. This provider can be used to access Microsoft Access, DB2/400, SyBase, and SQL Server 6.5 and earlier.
- ODBC: The ODBC provider is available under System.Data.Odbc namespace. This provider is used when there will not be any newer provider is available.
- SQL Server: The Microsoft SQL Server provider is available under System.Data.SqlClient namespace. Classes available under this provider will provide the same functionality as the generic OLEDB provider.
Question - 46 : - Why Stored Procedure is used in ADO.NET?
Answer - 46 : -
The reasons for using Stored Procedures in ADO.NET are given below:
- For improved performance
- For security reasons
- Easier to use and maintain
- Lesser Network Traffic
- Execution time is less
Question - 47 : - What are the different authentication techniques used to connect with MS SQL Server?
Answer - 47 : -
Before performing any task in the database, SQL Server will authenticate. Two types of authentication techniques are:
- Windows Authentication: This default authentication is provided only through Windows domain accounts. This SQL Server security model is strongly integrated with Windows, so it is also referred to as integrated security. Particular Windows users and group accounts are allowed to login into SQL Server. Windows users who are already been authenticated or logged onto Windows do not have to provide additional credentials.
The below-given SqlConnection.ConnectionString specifies Windows authentication without any need of providing a user name or password by the user.
C#
"Server=MSSQL1;Database=Institute;Integrated Security=true;
- SQL Server and Windows Authentication Mode(Mixed-mode): Authentication will be provided with the help of the Windows and SQL Server Authentication combination. User name and password pair will be maintained within SQL Server. In order to use this mixed-mode authentication, you need to create SQL Server logins that are stored in SQL Server. After that, you can supply the user name and password to SQL Server at run time.
The below-given ConnectionString specifies Mixed mode authentication:
C#
"Persist Security Info=False;User ID=Harsh;Password=xyz@123;Initial Catalog=Institute;Server=MySqlServer"
Question - 48 : - Explain about ADO.NET objects.
Answer - 48 : -
There are seven main objects in ADO.NET. They are:
- DataSet: It is available under both System.Data.ADO and the System.Data.SQL namespaces. DataSet is a database cache built-in memory for using it in disconnected operations. It holds the complete collection of tables, constraints, and relationships.
- SQLDataSetCommand: It represents a stored procedure or a database query that can be used to populate the DataSet object. It corresponds to the ADO’s Command object-provided functionalities.
- SQLCommand: It represents a stored procedure or a T-SQL statement that will be executed by SQL Server. It corresponds to another set of functionalities provided by the ADO’s Command object.
- SQLParameter: It can be used to pass parameters to the object of SQLCommand or SQLDataSetCommand class. When you are passing a parameter for SQLCommand using SQLParameter, SQLParameter will represent a parameter that can be used by T-SQL statement or stored procedure. Whenever a parameter has been passed for SQLDataSetCommand using SQLParameter, SQLParameter will represent a column from a result set.
- SQLConnection: It represents an open connection to the data source like SQL Server. This object is similar to the standard Connection object in ADO.
- SQLDataReader: It reads a forward-only stream of data from a SQL Server database. It works with an open database connection.
- SQLError: It collects runtime warnings and error conditions related information that will be encountered by an ADO.NET application. It corresponds to ADO’s Error object.
Question - 49 : - Explain about ExecuteScalar() in ADO.NET.
Answer - 49 : -
- A single value from the first row and first column of the ResultSet will be returned by ExecuteScalar() method on query execution.
- If the ResultSet is having multiple rows or columns, all those rows and columns will be ignored except the first row and first column. If the ResultSet is empty, this function will return NULL.
- The best situation to use ExecuteScalar() method is when we are using functions such as COUNT(), SUM(), etc., as it uses only a few resources compared to the ExecuteReader() method.
Example:
public void ExecuteScalarExample()
{
SqlConnection con = new SqlConnection();
con.ConnectionString = ConfigurationManager.ConnectionStrings["conString"].ConnectionString;
try
{
SqlCommand cd = new SqlCommand();
cd.Connection = con;
cd.CommandText = "SELECT SUM(SALARY) FROM EMPLOYEE";
cd.CommandType = CommandType.Text;
con.Open();
Int32 SalaryTotal = Convert.ToInt32(cd.ExecuteScalar());
MessageBox.Show("Total Salary of the employee is : " + SalaryTotal.ToString());
cd.Dispose();
con.Dispose();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
Here, we create an object of the class SqlConnection and SqlCommand. We pass SQL Statement to the object of SqlCommand class, which returns a single value. When ExecuteScalar() function gets executed, a single value will be returned, i.e, the total salary of employees. This value will be displayed using a message box.
Question - 50 : - What is Response.Expires and Response.ExpiresAbsolute property?
Answer - 50 : -
- Response.Expires property is specific to the minutes that a particular page stays in the cache for the specific time from the time it has been requested. For example, if Response.Expires value is set to 5 minutes, then the page is instructed to be in cache for 5 minutes from the time it has been requested.
- Response.ExpiresAbsolute property helps to provide the proper time at which a specific page cache has been expired. For example, Response.ExpiresAbsolute provides information like 14 March 15:40:15. This time tells about when the page was in cache.