INTERVIEW QUESTOINS ADO.NET PART THREE

What are the standard dot net framework data providers that are shipped with the Dot Net Framework 1.1?

The Dot Net Framework 1.1 is shipped with four different data providers:

1. • Dot Net Framework data provider for Microsoft SQL Server DBMS
2. • Dot Net Framework data provider for Oracle DBMS (available only in Framework 1.1)
3. • Dot Net Framework data provider for OLEDB supporting DBMS
4. • Dot Net Framework data provider for ODBC supporting data sources (available only in Framework 1.1)
   

Why should one use a specialized data provider when the data can be accessed with general data providers?

The specialized data providers (e.g., SQL Server and Oracle) are built specially for a particular kind of DBMS and works much more efficiently than the general data providers (e.g., OLEDB and ODBC). In practice, the specialized data providers are many times efficient than the general data providers.

What is the Dot Net Framework data provider for SQL Server?

The dot net framework data provider for SQL Server is the optimized data provider for Microsoft SQL Server 7 or later. It is recommended to use SQL Server data provider to access the SQL Server DB than general provider like OLEDB. The classes for this provider are present in the System.Data.SqlClient namespace.

What is the Dot Net Framework data provider for Oracle?

The dot net framework data provider for Oracle is the optimized data provider for Oracle DBMS. It is recommended to use Oracle data provider to access the Oracle DB than general provider like OLEDB. It supports the Oracle Client version 8.1.7 and later. The classes for this provider are present in the System.Data.OracleClient namespace. This provider is included in the .Net framework 1.1 and was not available in the Dot Net framework 1.0.

What is the Dot Net Framework data provider for OLEDB?

The dot net framework data provider for OLEDB provides connectivity with the OLEDB supported database management systems. It is the recommended middle tier for the SQL Server 6.5 or earlier and Microsoft Access Database. It is a general data provider. You can also use it to connect with the SQL Server or Oracle Database Management Systems. The classes for this provider are present in the System.Data.OleDBClient namespace.

What is the Dot Net Framework data provider for ODBC?

The dot net framework data provider for ODBC provides connectivity with the ODBC supported database management systems and data sources. It is a general data provider. You can also use it to connect with the SQL Server or Oracle Database Management Systems. The classes for this provider are present in the System.Data.ODBCClient namespace. This provider is included in the .Net framework 1.1 and was not available in the Dot Net framework 1.0.

What are the basic steps involved in data access with ADO.Net in disconnected environment?

Data access using ADO.Net involves the following steps:

1. • Defining the connection string for the database server
2. • Defining the connection (SqlConnection, OleDbConnection, etc) to the database using the connection string
3. • Defining the command (SqlCommand, OleDbCommand, etc) or command string that contains the query
4. • Defining the data adapter (SqlDataAdapter, OleDbDataAdapter, etc) using the command string and the connection object
5. • Creating a new DataSet object
6. • If the command is SELECT, filling the dataset object with the result of the query through the data adapter
7. • Reading the records from the DataTables in the datasets using the DataRow and DataColumn objects
8. • If the command is UPDATE, INSERT or DELETE, then updating the dataset through the data adapter
9. • Accepting to save the changes in the dataset to the database

Which namespaces I need to add to my project for each of the standard data provider?

You need to add following namespaces for the specified data providers:

Data Provider Namespace

1. MS SQL Server System.Data.SqlClient
2. Oracle Database System.Data.OracleClient
3. OLE DB Databases System.Data.OleDBClient
4. ODBC Data Sources System.Data.ODBCClient
   

How do I define a connection string for the database server?

First of all we have defined the instance name of the server, which is P-III on my system. Next we defined the name of the database, user id (uid) and password (pwd). Since my SQL server doesn't have a password for the System Administrator (sa) user, I have left it blank in the connection string. (Yes I know this is very dangerous and is really a bad practice - never, ever use a blank password on a system that is accessible over a network)

How do I define the connection to database?

A connection is defined using the connection string. The Connection object is used by the data adapter or data reader to connect to and disconnect from the database. For SQL Server used with SQL Server data provider.

How do I create a command and supply the SQL query to ADO.Net? (Command object and command string)

First of all, you create a command object (SqlCommand, OracleCommand, OleDbCommand, OdbcCommand) using the connection object (SqlConnection, OracleConnection, OleDbConnection, OdbcConnection) and set its CommandText property to the SQL query to execute.

How do I define a data adapter?

The data adapter stores your command (query) and connection and using these connect to the database when asked, fetch the result of query and store it in the local dataset.

The DataAdapter class (SqlDataAdapter, OracleDataAdapter, OleDbDataAdapter, OdbcDataAdapter) may be instantiated in three ways:

1. by supplying the command string (SQL Select command) and connection string
2. by supplying the command string (SQL Select command) and a connection object
3. by supplying the command object (SqlCommand, OracleCommand, OleDbCommand, OdbcCommand)

How do I get the result of my command and fill it to the dataset?

DataSet is a local and offline container of the data. The DataSet object is created simply like

C# Version

DataSet ds = new DataSet();

VB.Net Version

Dim ds As New DataSet()

Now we need to fill the DataSet with the result of the query. We will use the dataAdapter object for this purpose and call its Fill() method. This is the step where data adapter connects to the physical database and fetch the result of the query.

C# Version

dataAdapter.Fill(ds, "prog");

VB.Net Version

da.Fill(ds, "prog")

Here we have called the Fill() method of dataAdapter object. We have supplied it the dataset to fill and the name of the table (DataTable) in which the result of query is filled.

This is all we needed to connect and fetch the data from the database. Now the result of query is stored in the dataset object in the prog table which is an instance of DataTable. We can get a reference to this table by using the indexer property of dataset object’s Tables collection

C# Version

DataTable dataTable = ds.Tables["prog"];

VB.Net Version

Dim dataTable As DataTable

dt = ds.Tables("prog")

The indexer we have used takes the name of the table in dataset and returns the corresponding DataTable object. Now we can use the tables Rows and Columns collection to access the data in the table.
RELATED POST

ASP.NET INTERVIEW QUESTIONS AND ANSWERS PART ONE

ASP.NET INTERVIEW QUESTIONS AND ANSWERS PART TWO

ASP.NET INTERVIEW QUESTIONS AND ANSWERS PART THREE

ASP.NET INTERVIEW QUESTIONS AND ANSWERS PART FOUR

ASP.NET INTERVIEW QUESTIONS AND ANSWERS PART FIVE

ADO.NET INTERVIEW QUESTIONS AND ANSWERS PART ONE

ADO.NET INTERVIEW QUESTIONS AND ANSWERS PART TWO

You can also learn the concept of frame work concept in detail with questions and answers in the following place.

MICROSOFT DOT NET FRAME WORK QUESTIONS AND ANSWERS PART ONE

MICROSOFT DOT NET FRAME WORK QUESTIONS AND ANSWERS PART TWO

MICROSOFT DOT NET FRAME WORK QUESTIONS AND ANSWERS PART THREE

MICROSOFT DOT NET FRAME WORK QUESTIONS AND ANSWERS PART FOUR

MICROSOFT DOT NET FRAME WORK QUESTIONS AND ANSWERS PART FIVE

MICROSOFT DOT NET FRAME WORK QUESTIONS AND ANSWERS PART SIX

MICROSOFT DOT NET FRAME WORK QUESTIONS AND ANSWERS PART SEVEN

MICROSOFT DOT NET FRAME WORK QUESTIONS AND ANSWERS PART EIGHT

MICROSOFT DOT NET FRAME WORK QUESTIONS AND ANSWERS PART NINE

MICROSOFT DOT NET FRAME WORK QUESTIONS AND ANSWERS PART TEN