Table of contents
Introduction
In this section, we will touch base on one of the important concepts in .NET database access.
Previous parts of my interview questions series for architects:
UML interview questions Part 1: SoftArch5.aspx
Happy job hunting......
(B) What is the namespace in which .NET has the data functionality class?
Following are the namespaces provided by .NET for data management:
System.Data
This contains the basic objects used for accessing and storing relational data, such as DataSet, DataTable, and DataRelation. Each of these is independent of the type of data source and the way we connect to it.
System.Data.OleDB
It contains the objects that we use to connect to a data source via an OLE-DB provider, such as OleDbConnection, OleDbCommand, etc. These objects inherit from the common base classes, and so have the same properties, methods, and events as the SqlClient equivalents.
System.Data.SqlClient
This contains the objects that we use to connect to a data source via the Tabular Data Stream (TDS) interface of Microsoft SQL Server (only). This can generally provide better performance as it removes some of the intermediate layers required by an OLE-DB connection.
System.XML
This contains the basic objects required to create, read, store, write, and manipulate XML documents according to W3C recommendations.
(B) Can you give an overview of the ADO.NET architecture?
The most important concept in ADO.NET architecture is “Data Provider”. Data Provider provides access to data sources (SQL Server, Access, Oracle). In short, it provides an object to achieve functionalities like opening and closing connection, retrieve data, and update data. In the below figure, you can see the four main sections of a data provider:
Figure: 9.1 ADO.NET architecture
- Connection.
- Command object (this is the object for using Stored Procedures).
- Data Adapter (this object acts as a bridge between the data store and the dataset).
- Data Reader (this object reads data from the data store in forward only mode).
- DataSet object represents disconnected and cached data. If you see the diagram, it is not in direct connection with the data store (SQL Server, Oracle, etc.) rather it talks with the data adapter, who is responsible for filling the dataset. The dataset can have one or more datatables and relations.
- The DataView object is used to sort and filter data in the datatable.
Note: This is one of the poular questions in .NET interviews. Just paste the picture in your mind and during the interview try to refer to the image.
(B) What are the two fundamental objects in ADO.NET?
DataReader and DataSet are the two fundamental objects in ADO.NET.
(B) What is the 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 row set) from the same data source as well as the relationships between them.DataSet is a disconnected architecture while DataReader is a connected architecture.DataSet can persist contents while DataReader cannot persist contents, they are forward only.
(I) What are the major difference between classic ADO and ADO.NET?
Following are some major differences:
- In ADO, we have a
Recordset and in ADO.NET we have a DataSet. - In
Recordset, we can only have one table. If we want to accommodate more than one table, we need to do inner join and fill the Recordset. A DataSet can have multiple tables. - All data is persisted in XML as compared to classic ADO where data is persisted in binary format.
(B) What is the use of the Connection object?
They are used to connect data to a Command object.
- An
OleDbConnection object is used with an OLE-DB provider. - A
SqlConnection object uses Tabular Data Services (TDS) with MS SQL Server.
(B) What is the use of Command objects?
They are used to connect a Connection object to a DataReader or DataSet. Following are the methods provided by a Command object:
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.
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 row set within the database, allowing the rows to be retrieved.
Executes the command defined in the CommandText property against the connection defined in the Connection property. Returns only a single value (effectively the first column of the first row of the resulting row set, any other returned columns and rows are discarded). It is fast and efficient when only a "singleton" value is required.
ExecuteNonQueryExecuteReaderExecuteScalar
(B) What is the use of a data adapter?
These objects 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.
(B) What are basic methods of a DataAdapter?
These are the most commonly used methods of a DataAdapter:
Executes the Select command 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 data source if there is a primary key in the table in the DataSet.
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.
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.
(B) What is a 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.
(B) What are the various objects in a DataSet?
DataSet has a collection of DataTable objects within the Tables collection. Each DataTable object contains a collection of DataRow objects and a collection of DataColumn objects. There are also collections for 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.
Note: Look back again to the main diagram for the ADO.NET architecture for visualizing this answer in pictorial form.
(B) How can we connect to Microsoft Access, FoxPro, and Oracle etc.?
Microsoft provides the System.Data.OleDb namespace to communicate with databases like Access, Oracle, etc. In short, any OLE DB-compliant database can be connected using the System.Data.OldDb namespace.
Note: A small sample of OLEDB is provided in WindowsAppOleDb which uses Nwind.mdb in the bin directory to display data in a listbox.
Private Sub loadData()
Dim strPath As String
strPath = AppDomain.CurrentDomain.BaseDirectory
Dim objOLEDBCon As New OleDbConnection("Provider=Microsoft.Jet." & _
"OLEDB.4.0;Data Source =" & strPath & "Nwind.mdb")
Dim objOLEDBCommand As OleDbCommand
Dim objOLEDBReader As OleDbDataReader
Try
objOLEDBCommand = New OleDbCommand("Select FirstName from Employees")
objOLEDBCon.Open()
objOLEDBCommand.Connection = objOLEDBCon
objOLEDBReader = objOLEDBCommand.ExecuteReader()
Do While objOLEDBReader.Read()
lstNorthwinds.Items.Add(objOLEDBReader.GetString(0))
Loop
Catch ex As Exception
Throw ex
Finally
objOLEDBCon.Close()
End Try
End Sub
The main code is in the LoadData method which actually loads the data in the list box.
Note: This source code has the connectionstring hard coded in the program itself which is not a good programming practice. For Windows applications, the best place to store the connectionstring is “App.config”. Also note that the AppDomain.CurrentDomain.BaseDirectory function gives the current path of the running exe which is “BIN” and the MDB file is in that directory. Also note, the Finally block executes irrespective of if there is an error or not. This ensures that all the connections to the datastore are freed. It is a best practice to put all clean up statements in a Finally block thus ensuring that the resources are deallocated properly.
(B) How do we connect to SQL Server, which namespace do we use?
Below is the code required. We will try to understand the code in a more detailed manner. For this sample, we will also need a SQL table setup, which I have imported, using the DTS wizard.
Private Sub LoadData()
Dim strConnectionString As String
Dim objConnection As New SqlConnection
Dim objCommand As New SqlCommand
Dim objReader As SqlDataReader
Try
With objConnection
.ConnectionString = strConnectionString
.Open()
End With
objCommand = New SqlCommand("Select FirstName from Employees")
With objCommand
.Connection = objConnection
objReader = .ExecuteReader()
End With
Do While objReader.Read()
lstData.Items.Add(objReader.Item("FirstName"))
Loop
Catch ex As Exception
Throw ex
Finally
objConnection.Close()
End Try
<appSettings>
<add key="Connectionstring" value="Server=ERMBOM1-IT2;User ID=sa;Database=Employees"/>
</appSettings>
Note: The above code is provided in CD in the folder WindowsAppSqlClient. Comments in the code explain a lot but we will iterate through the code later. LoadData is the main method which loads the data from SQL Server. Before running this code, you have to install SQL Server in your machine. As we are dealing with SQLClient, we need to setup a database in SQL Server. For this sample, I have imported Nwind.mdb in the “SampleAccessDatabase” folder in CD into SQL Server. Depending on the computer you will have to change the connectionstring in the Web.config file.
For setting up the sample SQL table, we can use the DTS import wizard to import the table. See the below figure which uses Microsoft Access as the data source. While importing the database, give the database name as “Employees”.
Figure: 9.2 Loading “Nwind.mdb” in SQL SERVER for the sample
Figure: 9.3 Load only the Employee table.
To make it simple, we will only import the employee table as that is the only thing needed in our sample code.
Figure: 9.4 View of loaded Employee table
Now from interview point of view, definitely you are not going to say the whole source code which is given in the book. The interviewer expects a broader answer of the steps needed to connect to SQL Server. You only have to explain the LoadData method in a broader way. Following are the steps to connect to SQL Server:
With objConnection
.Connection String = strConnectionString
.Open ()
End With
ObjCommand = New SqlCommand ("Select First Name from Employees")
With objCommand
.Connection = objConnection
Breeder = .Execute Reader ()
End With
Do while objReader.Read ()
lstData.Items.Add (objReader.Item ("First Name"))
Loop
Note: In LoadData, you will see that the connectionstring is stored in the Web.config file and is loaded using AppSettings.Item(“ConnectionString”). While running this sample live on your database, do not forget to change this connectionstring according to your machine name and SQL Server or else the source code will not run.
- First imports the namespace “
System.Data.SqlClient”. - Create a connection object as shown in the
LoadData method. - Create the command object with the SQL. Also, assign the created connection object to the command object and execute the reader.
- Finally loop through the reader and fill the list box. If VB programmers are expecting the move next command, it has been replaced by
Read() which returns True if there is any data to be read. If Read() returns False that means that it’s the end of the data reader and there is no more data to be read. - Do not forget to close the connection object.
For further reading do watch the below interview preparation videos and step by step video series.
