ADO.Net Questions - Part 1

  1. What are the .Net  namespaces with the data functionality classes?

    System.Data This namespace 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 type of the connection.

    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.

  2. Give an overview of ADO.Net architecture.

    The most important thing to mention in ADO.NET architecture is "Data Provider". Data Provider provides access to datasource (Access, SQL Server, Oracle etc.).

    In short it provides 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 :

    • Connection object.
    • Command object (This object is responsible for using stored procedures, queries etc).
    • Data Adapter (This object acts as a bridge between datastore and DataSet).
    • Datareader (This object reads data from data store in forward only mode).
    • Dataset object (This objects represents disconnected and cached data).

    Dataset object represents disconnected and cached data. If you see the diagram it is not in direct connection with the data store, it communicate with Data Adapter, who is responsible for filling the Dataset. Dataset can have one or more Datatable and relations.

    ado_net_arch.jpg

  3. What are the two fundamental objects in ADO.NET ?

    Datareader and Dataset.

  4. Describe main differences 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 providesrandom access and 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.

  5. Describe main differences between ADO.NET and classic ADO.

    • 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 are stored in XML in ADO.Net as compared to classic ADO where data can be stored in binary format also.

  6. What is the use of connection object ?

    They are used to connect a 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.

  7. Why do we need a Command object ?

    It is 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 single value is required

  8. What is the DataAdapter ?

    It is an object 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.

  9. 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.

  10. 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.

  11. 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.

  12. How can we connect to the databse, other than SQL Server (Microsoft Access , Foxpro , Oracle etc ?

    Microsoft provides System.Data.OleDb namespace to communicate with databases like scess , Oracle etc. In short any OLE DB-Compliant database can be connected using System.Data.OldDb namespace.


    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

    “Loaddata()” method actually loads the data in listbox.

    Note: This source code has the ConnectionString hard coded in the program itself which is not a good programming practice. For windows application the best place to store connectionstring is “App.config”. Also note that the final block which executes irrespective that there is error or not. Thus ensuring that all the connection to the datastore is freed. Its best practice to put all clean up statements in finally block thus ensuring that the resources are deallocated properly.

  13. How do we use stored procedure in ADO.NET and how do we provide parameters to the stored procedures?

    ADO.NET provides the SqlCommand object which provides the functionality of executing stored procedures.

    Note :

    Sample code is provided in folder “WindowsSqlClientCommand”. There are two stored procedures created in same database “Employees” which was created for the previous question.

    CREATE PROCEDURE SelectByCustomer @FirstName nvarchar(200) AS
    Select FirstName from Customers where FirstName like @FirstName + ‘%’
    CREATE PROCEDURE SelectCustomer AS
    Select FirstName from Customers


    If txtCustomerName.Text.Length = 0 Then
    objCommand = New SqlCommand(“SelectCustomer”)
    Else
    objCommand = New SqlCommand(“SelectByCustomer”)

    objCommand.Parameters.Add(“@FirstName”, Data.SqlDbType.NVarChar, 200)
    objCommand.Parameters.Item(“@FirstName”).Value = txtEmployeeName.Text.Trim()
    End If

    In the above sample not much has been changed only that the SQL is moved to the stored procedures. There are two stored procedures one is “SelectCustomer” which selects all the customers and the other is “SelectByCustomer” which returns customer name starting with a specific character. As you can see to provide parameters to the stored procedures we are using the parameter object of the command object.

  14. How to force the connection object to close after 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 closed.


    pobjDataReader = pobjCommand.ExecuteReader(CommandBehavior.CloseConnection)

  15. How to force the Datareader to return only schema of the datastore rather than data ?

    reader = cmd.ExecuteReader(CommandBehavior.SchemaOnly)

  16. How can we fine tune the command object when we are expecting a single row?

    CommandBehaviour enumeration provides two values - SingleResult and SingleRow. If you expect a single value pass “CommandBehavior.SingleResult” and the query is optimized accordingly, if you expect single row then pass
    “CommandBehavior.SingleRow”.

  17. Which is the best place to store connection string in .NET projects?

    Config files are the best places to store connectionstrings. If it is a web-based application “Web.config” file will be used and if it is a windows application “App.config” files will be used.

  18. What are the various methods provided by the dataset object to generate XML?

    Note: XML is one of the most important leap between classic ADO and ADO.NET. So this question is normally asked more generally how can we convert any data to XML format. Best answer is convert in to dataset and use the below methods.

    • ReadXML

      Read’s a XML document in to Dataset.

    • GetXML

      This is a function which returns the string containing XML document.

    • WriteXML

      This writes a XML data to disk.

January 14, 2008 | Posted Under ADO.Net Questions 

Comments

Leave a Reply

You must be logged in to post a comment.