Most Important Interview Questions And Answers: 2008-09-28

Monday, September 29, 2008

connection strings in .NET

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:
Dot Net Framework data provider for Microsoft SQL Server DBMS
Dot Net Framework data provider for Oracle DBMS (available only in Framework 1.1)
Dot Net Framework data provider for OLEDB supporting DBMS
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:
>>Defining the connection string for the database server
>>Defining the connection (SqlConnection, OleDbConnection, etc) to the database using the connection string
>>Defining the command (SqlCommand, OleDbCommand, etc) or command string that contains the query
>>Defining the data adapter (SqlDataAdapter, OleDbDataAdapter, etc) using the command string and the connection object
>>Creating a new DataSet object
>>If the command is SELECT, filling the dataset object with the result of the query through the data adapter
>>Reading the records from the DataTables in the datasets using the DataRow and DataColumn objects
>>If the command is UPDATE, INSERT or DELETE, then updating the dataset through the data adapter

>>Accepting to save the changes in the dataset to the database

How do I define a connection string for the database server?
For MS SQL Server, used with the SQL Server data provider, we can write the connection string like:
C# Version

// for Sql Serverstring connectionString = "server=P-III; database=programmersheaven;" +_"uid=sa; pwd=;";
VB.Net Version'

/ for Sql ServerDim

connectionString As String = "server=P-III; database=programmersheaven;" + _ "uid=sa; pwd=;"
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)
For Oracle Database Server, used with the Oracle data provider, we can write the connection string like:
C# Version

string connectionString = "Data Source=Oracle8i;User Id=username;" + "Password=pwd; Integrated Security=no;";
VB.Net Version

Dim connectionString As String = "Data Source=Oracle8i;User Id=username;" + _"Password=pwd; Integrated Security=no;"
For MS Access Database, used with the OLE DB data provider, we can write the connection string like:
C# Version

// for MS Accessstring

connectionString = "provider=Microsoft.Jet.OLEDB.4.0;" + "data source = c:\\programmersheaven.mdb";
VB.Net Version

' for MS Access

Dim connectionString As String = "provider=Microsoft.Jet.OLEDB.4.0;" + _ "data source = c:\programmersheaven.mdb"
First we have defined the provider of the access database. Then we have defined the data source which is the address of the target database.
For MS SQL Server, used with the ODBC data provider, we can write the connection string like:
C# Version

string connectionString = "Driver={SQL Server};Server=FARAZ;Database=pubs;Uid=sa;Pwd=;";
VB.Net Version

Dim connectionString As String = "Driver={SQL Server};Server=FARAZ;Database=pubs;Uid=sa;Pwd=;"

Data Base iN ADO.net

What is a dataset?
A dataset is the local repository of the data used to store the tables and disconnected record set. When using disconnected architecture, all the updates are made locally to dataset and then the updates are performed to the database as a batch.
What is a data adapter?
A data adapter is the component that exists between the local repository (dataset) and the physical database. It contains the four different commands (SELECT, INSERT, UPDATE and DELETE). It uses these commands to fetch the data from the DB and fill into the dataset and to perform updates done in the dataset to the physical database. It is the data adapter that is responsible for opening and closing the database connection and communicates with the dataset.
What is a database connection?
A database connection represents a communication channel between you application and database management system (DBMS). The application uses this connection to pass the commands and queries to the database and obtain the results of the operations from the database.
What is a database command?
A database command specifies which particular action you want to perform to the database. The commands are in the form of SQL (Structured Query Language). There are four basic SQL statements that can be passed to the database.
SQL SELECT Statement
This query is used to select certain columns of certain records from a database table.

SELECT * from emp// sql steaments

selects all the fields of all the records from the table name ‘emp’

SELECT empno, ename from emp
selects the fields empno and ename of all records from the table name ‘emp’

SELECT * from emp where empno <>
selects all those records from the table name ‘emp’ that have the value of the field empno less than 100

SELECT * from article, author where article.authorId = author.authorId
selects all those records from the table name ‘article’ and ‘author’ that have same value of the field authorId
SQL INSERT Statement
This query is used to insert a record to a database table.

INSERT INTO emp(empno, ename) values(101, ‘John Guttag’)
inserts a record to emp table and set its empno field to 101 and its ename field to ‘John Guttag’
SQL UPDATE Statement
This query is used to edit an already existing record in a database table.

UPDATE emp SET ename =‘Eric Gamma’ WHERE empno = 101
updates the record whose empno field is 101 by setting its ename field to ‘Eric Gamma’
SQL DELETE Statement
This query is used to delete the existing record(s) from the database table

DELETE FROM emp WHERE empno = 101
deletes the record whose empno field is 101 from the emp table
What is a data reader?
The data reader is a component that reads the data from the database management system and provides it to the application. The data reader works in the connected manner; it reads a record from the DB, pass it to the application, then reads another and so on.
How do different components of ADO.Net interact with each other in disconnected architecture?
The Data Adapter contains in it the Command and Connection object. It uses the connection object to connect to the database, execute the containing command, fetch the result and update the DataSet.

What does it mean by Dot Net Framework Data Provider?
Dot Net Framework Data Provider is a set of classes that establishes the database communication between an application and the database management system based on the standards of ADO.Net framework. Different data providers provide specialized and optimized connectivity to particular database management system or to a particular class of DBMS. For example, the MS SQL Server data provider provides the optimized connectivity between dot net application and MS SQL Server DBMS while the OLEDB data provider provides the uniform connectivity between dot net application and the OLEDB databases.

ADO.NET

What is ADO.Net?
Most of the today’s applications need to interact with database systems to persist, edit or view data. In .Net data access service is provided through ADO.Net (ActiveX Data Object in Dot Net) components. ADO.Net is an object oriented framework that allows you to interact with database systems. We usually interact with database systems through SQL queries or stored procedures. ADO.Net encapsulates our queries and commands to provide a uniform access to various database management systems.
ADO.Net is a successor of ADO (ActiveX Data Object). The prime features of ADO.Net are its disconnected data access architecture and XML integration.
What does it mean by disconnected data access architecture of ADO.Net?
ADO.Net introduces the concept of disconnected data architecture. In traditional data access components, you make a connection to the database system and then interact with it through SQL queries using the connection. The application stays connected to the DB system even when it is not using DB services. This commonly wastes the valuable and expensive database resource as most of the time applications only query and view the persistent data. ADO.Net solves this problem by managing a local buffer of persistent data called data set. Your application automatically connects to the database server when it needs to pass some query and then disconnects immediately after getting the result back and storing it in dataset. This design of ADO.Net is called disconnected data architecture and is very much similar to the connection less services of http over the internet. It should be noted that ADO.Net also provides the connection oriented traditional data access services.
What does it mean by disconnected data access architecture of ADO.Net?
ADO.Net introduces the concept of disconnected data architecture. In traditional data access components, you make a connection to the database system and then interact with it through SQL queries using the connection. The application stays connected to the DB system even when it is not using DB services. This commonly wastes the valuable and expensive database resource as most of the time applications only query and view the persistent data. ADO.Net solves this problem by managing a local buffer of persistent data called data set. Your application automatically connects to the database server when it needs to pass some query and then disconnects immediately after getting the result back and storing it in dataset. This design of ADO.Net is called disconnected data architecture and is very much similar to the connection less services of http over the internet. It should be noted that ADO.Net also provides the connection oriented traditional data access services.
What does it mean by connected data access architecture of ADO.Net?
In the connected environment, it is your responsibility to open and close the database connection. You first establish the database connection, perform the interested operations to the database and when you are done, close the database connection. All the changes are done directly to the database and no local (memory) buffer is maintained.
What's the difference between accessing data with dataset or data reader?
The dataset is generally used when you like to employ the disconnected architecture of the ADO.Net. It reads the data into the local memory buffer and perform the data operations (update, insert, delete) locally to this buffer.
The data reader, on the other hand, is directly connected to the database management system. It passes all the queries to the database management system, which executes them and returns the result back to the application.
Since no memory buffer is maintained by the data reader, it takes up fewer resources and performs more efficiently with small number of data operations. The dataset, on the other hand is more efficient when large number of updates are to be made to the database. All the updates are done in the local memory and are updated to the database in a batch. Since database connection remains open for the short time, the database management system does not get flooded with the incoming requests.
What are the performance considerations when using dataset?
Since no memory buffer is maintained by the data reader, it takes up fewer resources and performs more efficiently with small number of data operations. The dataset, on the other hand is more efficient when large number of updates are to be made to the database. All the updates are done in the local memory and are updated to the database in a batch. Since database connection remains open for the short time, the database management system does not get flooded with the incoming requests.
However, since the dataset stores the records in the local buffer in the hierarchical form, it does take up more resources and may affect the overall performance of the application.
How to select dataset or data reader?
The data reader is more useful when you need to work with large number of tables, database in non-uniform pattern and you need not execute the large no. of queries on few particular table.
When you need to work on fewer no. of tables and most of the time you need to execute queries on these fewer tables, you should go for the dataset.
It also depends on the nature of application. If multiple users are using the database and the database needs to be updated every time, you must not use the dataset. For this, .Net provides the connection oriented architecture. But in the scenarios where instant update of database is not required, dataset provides optimal performance by making the changes locally and connecting to database later to update a whole batch of data. This also reduces the network bandwidth if the database is accessed through network.
Disconnected data access is suited most to read only services. On the down side, disconnected data access architecture is not designed to be used in the networked environment where multiple users are updating data simultaneously and each of them needs to be aware of current state of database at any time (e.g., Airline Reservation System).
How XML is supported in ADO.Net?
The dataset is represented in the memory as an XML document. You can fill the dataset by XML and can also get the result in the form of XML. Since XML is an international and widely accepted standard, you can read the data using the ADO.Net in the XML form and pass it to other applications using Web Service. These data consuming application need not be the essentially Dot Net based. They may be written with Java, C++ or any other programming language and running on any platform.