ADO.NET

1. Overview of ADO.NET?

"ADO.NET mainly comprise of Data Provider, Dataset and Dataview. The most important section in ADO.NET architecture is “Data Provider”. Data Provider provides access to data source (SQL SERVER, ACCESS, ORACLE).In short it provides object to achieve functionalities like opening and closing connection, retrieve data, and update data. Two fundamental objects in ADO.NET are DataReader and Dataset. In the below figure, you can see the four main sections of a data provider:-

• Connection

• Command object (This is the responsible object to use stored procedures)

• Data Adapter (This object acts as a bridge between data store and dataset)

• Data reader (This object reads data from 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 Data adapter, who is responsible for filling the dataset. Dataset can have one or more Data table and relations. • Data View” object is used to sort and filter data in Data table. "

2 What is the difference between ADO and ADO.NET?


"a) ADO is a com base library that supports only connected data. This means that when we access the data (view, edit and update) it is affected in real-time,since the connection is being used all the time, whereas ADO.NET is a CLR based library that supports disconnected recordsets. This means that When we access data, ADO.NET makes a copy of the data using XML and holds the connection to pull down the data or to make any requested updates as long as we need.
b)In ADO we have recordset and in ADO.NET we have dataset. In recordset we can only have one table. If we want to accommodate more than one tables we need to do inner join and fill the recordset. Dataset can have multiple tables.

c)ADO allows only client side cursors while ADO.NET allows both client side as well as server side cursors.In ADO.NET, classes actually handle the work of cursors. This allows the developer to decide which is best. For Internet development, this is crucial in creating efficient applications.

d)Whereas ADO allows you to persist records in XML format, ADO.NET allows you to manipulate your data using XML as the primary means. This is nice when you are working with other business applications and also helps when you are working with firewalls because data is passed as HTML and XML."

3 What is difference between dataset and data reader?

  4 What is difference between ADO.NET dataset and ADO.NET recordset?

"a)A DataSet is designed to work without any continuing connection to the original data source while recordset is a connection oriented service.

b)DataSet is represented using XML so can travel safely over http and port 80 without concerns for a firewall etc. A recordset is an instance of a COM object and may not travel through firewalls.

c)For the same reason as stated in point 1 a dataset could be stored in a session variable of a web application without affecting the scalability while storing a recordset in a session variable would affect scalability.

d)A DataSet can represent an entire relational database in memory, complete with tables, relations, and views while recordset stores only the rows returned by the given query.

e)For a dataset XML is the internal representation as well as medium used for output but for a recordset XML is merely an output format.

f) Data in a DataSet is bulk-loaded, rather than being loaded on demand as in recordset. g) Both support batch updates and sorting and filtering of data."

5 What is difference between import System.Data.SqlClient , System.Data.Oledb?

System.Data.OleDB It contains the objects that we use to connect to a data source via an OleDB provider , such as OleDbConnection, OleDBCOmmand. System.Data.SqlClient It contains objects that we use to connect to a data source via Tabular data stream(TDS) interface provided by Microsoft Sql Server. This can be generally used to provide better performance because it removes some of the intermediate layers required by the OleDB provider.

6 What are the various ADO.NET Objects?

a)Connection object

b)Command Object

c)DataReader Object

d)DataSet Object

e) DataAdapter Object"

7 Explain Connection Object?

Connection Object connect to the specified Database and open a connection between the application and the Database . When the connection is established , SQL Commands may be executed, with the help of the Connection Object, to retrieve or manipulate data in the Database. Once the Database activity is over , Connection should be closed and release the resources . connetionString = "Data Source=ServerName;Initial Catalog=DatabaseName;User ID=UserName;Password=Password"

8 Explain Command Object?

The Command Object in ADO.NET executes SQL statements and Stored Procedures against the data source specified in the Connection Object. The Command Object required an instance of a Connection Object for executing the SQL statements. That is, for retrieving data or execute an SQL statement against a Data Source , you have to create a Connection Object and open a connection to the Data Source, and assign the open connection to the connection property of the Command Object. When the Command Object return result set , a Data Reader is used to retrieve the result set. The Command Object has a property called CommandText, which contains a String value that represents the command that will be executed in the Data Source. When the CommandType property is set to StoredProcedure, the CommandText property should be set to the name of the stored procedure.

9 What are important methods of Command Object?

a) Executenonquery():It is one of the most frequently used method in SqlCommand Object and is used for executing statements that do not return result set. ExecuteNonQuery() performs Data Definition tasks as well as Data Manipulation tasks also. The Data Definition tasks like creating Stored Procedures and Views perform by ExecuteNonQuery() . Also Data Manipulation tasks like Insert , Update and Delete perform by ExecuteNonQuery().

b)ExecuteReader() in SqlCommand Object send the SQL statements to Connection Object and populate a SqlDataReader Object based on the SQL statement. When the ExecuteReader method in SqlCommand Object execute , it instantiate a SqlClient.SqlDataReader Object. Slide 5

c)ExecuteScalar() in SqlCommand Object is used for get a single value from Database after its execution. It executes SQL statements or Stored Procedure and returned a scalar value on first column of first row in the Result Set. If the Result Set contains more than one columns or rows , it takes only the first column of first row, all other values will ignore. If the Result Set is empty it will return a Null reference.

It is very useful to use with aggregate functions like Count(*) or Sum() etc. When compare to ExecuteReader() , ExecuteScalar() uses fewer System resources. Slide 4"

10 Explain Dataset?


"The ADO.NET DataSet is a memory-resident representation of data that provides a consistent relational programming model regardless of the source of the data it contains. A DataSet represents a complete set of data including the tables that contain, order, and constrain the data, as well as the relationships between the tables.
The DataSet class and the classes contained in DataSet objects-Data­Table, DataColumn, DataRow, Constraint, and DataRelation-reside in the System.Data namespace."

11 What is the new keyword used for?

New keyword is used to create the new instance of the class. Unlike value types, such as Integer and Double, objects are reference types, and you must explicitly create them before you can use them.

11 Explain DataAdapter?

DataAdapter provides the communication between the Dataset and the Datasource. We can use the DataAdapter in combination with the DataSet Object. That is these two objects combine to enable both data access and data manipulation capabilities. The DataAdapter can perform Select , Insert , Update and Delete SQL operations in the Data Source. The Insert , Update and Delete SQL operations , we are using the continuation of the Select command perform by the DataAdapter. That is the DataAdapter uses the Select statements to fill a DataSet and use the other three SQL commands (Insert, Update, delete) to transmit changes back to the Database. sqlCnn.Open()
sqlCmd = New SqlCommand(sql, sqlCnn)
adapter.SelectCommand = sqlCmd
adapter.Fill(ds)
For i = 0 To ds.Tables(0).Rows.Count - 1
MsgBox(ds.Tables(0).Rows(i).Item(0) & "" -- "" & ds.Tables(0).Rows(i).Item(1))
Next
adapter.Dispose()
sqlCmd.Dispose()
sqlCnn.Close()"

12 What is Dataview?

The DataView provides different views of the data stored in a DataTable. That is we can customize the views of data from a DataTable. DataView can be used to sort, filter, and search the data in a DataTable , additionally we can add new rows and modify the content in a DataTable. The DataView provides different views of the data stored in a DataTable. DataView can be used to sort, filter, and search in a DataTable , additionally we can add new rows and modify the content in a DataTable. DataViews can be created and configured both design time and run time . Changes made to a DataView affect the underlying DataTable automatically, and changes made to the underlying DataTable automatically affect any DataView objects that are viewing the DataTable

13 Explain DataReader?

"DataReader Object in ADO.NET is a stream-based , forward-only, read-only retrieval of query results from the Data Source, which do not update the data. The DataReader cannot be created directly from code, they created only by calling the ExecuteReader method of a Command Object.
DataReader = Command.ExecuteReader()
DataReader Object provides a connection oriented data access to the data Sources. A Connection Object can contain only one DataReader at a time and the connection in the DataReader remains open and cannot be used for any other purpose while data is being accessed. When started to read from a DataReader it should always be open and positioned prior to the first record. The Read() method in the DataReader is used to read the rows from DataReader and it always moves forward to a new valid row, if any row exist .

DataReader.Raed()
There are two types of DataReader in ADO.NET. They are SqlDataReader and the OleDbDataReader. The System.Data.SqlClient and System.Data.OleDb are containing these DataReaders respectively. From the following link you can see in details about these classes.

SqlDataReader
OleDbDataReader
Example: Filling drop down list, Comparing username and password in database. sqlCnn.Open()
sqlCmd = New SqlCommand(sql, sqlCnn)
Dim sqlReader As SqlDataReader = sqlCmd.ExecuteReader()
While sqlReader.Read()
MsgBox(sqlReader.Item(0) & "" - "" & sqlReader.Item(1) & "" - "" & sqlReader.Item(2))
End While
sqlReader.Close()
sqlCmd.Dispose()
sqlCnn.Close()"

14 What is DSN?

A data source name (DSN) is a data structure that contains the information about a specific database that an Open Database Connectivity ( ODBC ) driver needs in order to connect to it. If the DSN (Data Source Name) has to be used with SQL Server, namespace that needs to be imported is system.data.oledb

15 OLEDB Connection Pooling is handled by OLEDB.Net Provider and SQLClient Connection pooling is handled by?

Windows 2000 Component Service

16 Which object in ADO.Net is similar to the Recordset object of ADO ?

DataReader

17 State whether the following statement is true or false.ADO.NET does not have the inherent cursor object but has data classes that include functionality of cursors

TRUE

18 Two users are trying to update the row in a database at the same time.Assuming that optimistic concurrency is not used for concurrency control in this case, what will be the result ?

The Second Update overwrites the first update

19  What is difference between Dataset.Clone and Dataset. Copy?

Clone: - It only copies structure, does not copy data.
Copy: - Copies both structure and data."

20 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.
CREATE PROCEDURE SelectByEmployee @FirstName nvarchar(200) AS
Select FirstName from Employees where FirstName like @FirstName + '%'
CREATE PROCEDURE SelectEmployee AS
Select FirstName from Employees
If txtEmployeeName.Text.Length = 0 Then
objCommand = New SqlCommand(“SelectEmployee”)
Else
objCommand = New SqlCommand(“SelectByEmployee”)
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 “Select Employee” which selects all the employees and the other is “SelectByEmployee” which returns employee 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. In such question interviewer expects two simple answers one is that we use command object to execute stored procedures and the parameter object to provide parameter to the stored procedure."

21 Why should you validate data in the dataset along with the validation in database?

Adding validation rules to your application can improve its performance. If a user fails to enter a credit card number, either by accident or in the hope that the system programmers were extremely lazy, the code for the Web page can easily determine that it can't successfully submit the order without having to contact the database. The other benefits of this approach are a slight reduction of network traffic and a lighter load on your database.

22 What are various validation techniques available in ADO.NET datasets?

The ADO.NET DataSet offers many of the same data validation mechanisms available in database systems. You can separate these validation mechanisms, also called constraints, into two categories-column-level restrictions and table-level restrictions.

The DataColumn object exposes a number of properties that you can use to validate your data.

1. ReadOnly

The simplest way to ensure that your data is valid is to not let users modify it. If you want to make the data in a DataColumn read-only, set the Read­Only property of the DataColumn to True.

2. AllowDBNull

Some database columns require values, while others accept empty, or null, values. The DataColumn object exposes an AllowDBNull property that you can set to control whether the column in your DataSet accepts null values.

3. MaxLength

Many databases place restrictions on the size of a string in a column. In the Customers table, for example, the CustomerID column accepts a string of up to 5 characters and the CompanyName column accepts up to 40 characters. You can place similar restrictions on a DataColumn using the MaxLength property.

4. Unique

The DataColumn lets you specify which values in a column are unique using the Unique property. When you set this property to True on a DataColumn, ADO.NET will examine the value stored in this column of each row in your DataTable. If you add or modify a row in your DataTable to create a duplicate value in a unique column, ADO.NET will throw a ConstraintException.

The DataTable Object's Constraints Collection

You can also validate data in your DataSet by setting properties of the Data­Table object. The ADO.NET object model includes two classes that you can use to define constraints in a DataTable. These classes, UniqueConstraint and Foreign­KeyConstraint, are derived from the Constraint class. The DataTable exposes a Constraints property that you can use to add to, modify, or examine the constraints on the DataTable.

1. UniqueConstraints: If you set the Unique property of a DataColumn to True, you've defined a unique constraint in the DataTable that contains that column. At the same time, you've also added a UniqueConstraint object to the Data­Table object's Constraints collection. Setting the Unique property of a Data­Column is simpler than creating a new UniqueConstraint in a DataTable object's Constraints collection. However, there are times when you'll want to explicitly create a UniqueConstraint, such as when you need to make sure that the combinations of values from multiple columns are unique.

2. PrimaryKey

A primary key is a special type of unique constraint. The ADO.NET DataRowCollection object has a Find method that you can use to locate a row in your DataTable by the value or values in its primary key column, as shown here.

row = MyTable.Rows.Find("RAGS")

A DataTable can have multiple unique constraints but can contain at most one primary key. You can set or examine a DataTable object's primary key using its PrimaryKey property.

3. ForeignKeyConstraint

You can also add foreign constraints to a DataTable. I described an example of a foreign key constraint just a couple pages back. Each order in the Northwind database's Orders table must have a value for its CustomerID column that is used in the Customers table. You can place similar restrictions on the data in your DataSet by creating a ForeignKeyConstraint and adding it to the table whose rows you want to validate.You generally won't need to explicitly create a ForeignKeyConstraint. Creating a DataRelation between two DataTable objects within your DataSet creates a ForeignKeyConstraint in the process. In the next chapter, I'll discuss the DataRelation object and how you can use it to work with relational data.

23 How do you filter and sort data using datasets?

"There are two ways to filter and sort the data using Datasets:
• Data tables support a Select method that you can call to filter and sort. The method does not change the contents or order of records in a table; instead, it presents you with a list of records (an array) representing the criteria you specify.
• You can use a data view (DataView object). A data view is an object that acts as a layer on top of the data table, providing a filtered and sorted view of the table's contents. (You can also use a data view manager, which acts like a collection of data views.) A data view is similar to a view in a database, in that it is not a copy of the data. Instead, it is simply a different way of seeing the data in a table."

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

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

24 How to create and fill the dataset?

Using VB.Net
Dim ds As New DataSet(""DataSetName"")
Console.WriteLine(ds.DataSetName)
Filling the data from database into dataset object is a very easy process. Here we can use either SQL query or a stored procedure. Below is the example of how to fill data using query.
Using VB.Net1.

1. Create a connection to the database using a connection object
2. Invoke a command to create a DataSet object using an adapter object
3. Use the DataSet object in code to display or to change items in the database
4. Invoke a command to update the database from the DataSet object using an adapter object
5. Close the database connection (if you explicitly opened it)
Dim strConn, strSQL As String
strConn = ""Provider=SQLOLEDB;Data Source=(local)\NetSDK;"" & _
""Initial Catalog=Northwind;Trusted_Connection=Yes;""
strSQL = ""SELECT CustomerID, CompanyName, ContactName, Phone "" & _
""FROM Customers""
Dim da As New OleDbDataAdapter(strSQL, strConn)
Dim ds As New DataSet
da.Fill(ds, ""Customers"")"

26 How can we force the connection object to close after my data reader is closed?

Command method Execute reader takes a parameter called as Command Behavior where in we can specify saying close connection automatically after the Data reader is close. PobjDataReader = pobjCommand.ExecuteReader (CommandBehavior.CloseConnection)

27 I want to force the data reader to return only schema of the data store rather than data.

PobjDataReader = pobjCommand.ExecuteReader (CommandBehavior.SchemaOnly)

28 How can we fine-tune the command object when we are expecting a single row?

Again, CommandBehaviour enumeration provides two values Single Result and Single Row. If you are expecting a single value then pass “CommandBehaviour.SingleResult” and the query is optimized accordingly, if you are expecting single row then pass “CommandBehaviour.SingleRow” and query is optimized according to single row.

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

Config files are the best places to store connection strings. 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.

30 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."

31  How can we save all data from dataset?

Dataset has “Accept Changes” method, which commits all the changes since last time “Accept changes” has been executed.

32 How can we check that some changes have been made to dataset since it was loaded?

"Twist: - How can we cancel all changes done in dataset?
Twist: - How do we get values, which are changed, in a dataset?

For tracking down changes, Dataset has two methods, which comes as rescue “Get Changes “and “Has Changes”.
Get Changes
Returns dataset, which are changed since it, was loaded, or since Accept changes was executed.
Has Changes
Or abandon all changes since the dataset was loaded use “Reject Changes This property indicates that has any changes been made since the dataset was loaded or accept changes method was executed.
Note:- One of the most misunderstood things about these properties is that it tracks the changes of actual database. That is a fundamental mistake; actually the changes are related to only changes with dataset and have nothing to with changes happening in actual database. As dataset are disconnected and do not know anything about the changes happening in actual database."