Viva-ADO.NET

https://www.youtube.com/watch?v=aoFDyt8oG0k&list=PL6n9fhu94yhX5dzHunAI2t4kE0kOuv4D7
ADO.NET

 ADO.NET CLASS METHODS

ADO.NET (Active Data Objects for .NET) is a framework for data access in .NET applications. It provides classes and methods to interact with databases, execute SQL queries, and work with data. Here are some commonly used ADO.NET classes and their methods:

Connection Classes:

SqlConnection: Represents a connection to a SQL Server database.

Open(): Opens a database connection.

Close(): Closes the database connection.

Dispose(): Releases resources associated with the connection.

OleDbConnection, OracleConnection, DbConnection (abstract base class for all connections): Similar to SqlConnection but for different database providers.

Command Classes:

SqlCommand: Represents a SQL command to be executed against a SQL Server database.

ExecuteNonQuery(): Executes a command that doesn't return data (e.g., INSERT, UPDATE).

ExecuteReader(): Executes a command and returns a data reader for reading results.

ExecuteScalar(): Executes a command and returns the first column of the first row as an object.

OleDbCommand, OracleCommand, DbCommand (abstract base class for all commands): Similar to SqlCommand but for different database providers.

DataReader Classes:

SqlDataReader: Provides a forward-only, read-only stream of data from a SQL Server database.

Read(): Advances the reader to the next record.

GetXXX(): Methods for retrieving data by column type (e.g., GetInt32, GetString).

Close(): Closes the data reader.

DataAdapter Classes:

SqlDataAdapter: Represents a set of data commands and a database connection used to fill a DataSet and update the database.

Fill(): Populates a DataSet or DataTable with data from the database.

Update(): Updates changes in a DataSet back to the database.

DataSet and DataTable:

DataSet: Represents an in-memory cache of data retrieved from a database.

DataTable: Represents a single table within a DataSet.

Transaction Classes:

SqlTransaction: Represents a SQL Server transaction.

Commit(): Commits the transaction.

Rollback(): Rolls back the transaction.

Parameter Classes:

SqlParameter: Represents a parameter for a SQL command.

Used to add parameters to SqlCommand to prevent SQL injection.

Connection Pooling:

SqlConnection and other connection classes automatically handle connection pooling, so you typically don't need explicit pool management methods.

Error Handling:

SqlException, OleDbException, OracleException: Exception classes for database-related errors.

Number: Gets the error number associated with the exception.

Message: Gets a description of the error.

Asynchronous Methods:

Many ADO.NET methods have asynchronous counterparts with the Async suffix (e.g., OpenAsync, ExecuteNonQueryAsync). These allow you to perform database operations asynchronously, which is important for responsive applications.

These are some of the essential ADO.NET classes and their methods. The specific classes and methods you use will depend on the database provider you are working with and your application's requirements.

Beginner:

1.       What is the namespace in which .NET has the data functionality class?

Answer: -
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 Data Relation. 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.

2.       Can you give an overview of ADO.NET architecture?

Answer: -
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. 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 (Data Reader provides forward-only and read-only access to dataThis 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.

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

Data reader and Dataset are the two fundamental objects in ADO.NET.

4.       What is difference between dataset and data reader?

Following are some major differences between dataset and data reader:-
. Data Reader 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 data reader is connected architecture.
. Dataset can persist contents while data reader cannot persist contents, they are forward only.

5.       What are major difference between classic ADO and ADO.NET?

Following are some major differences between both :-
. 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.
. All data persist in XML as compared to classic ADO where data persisted 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.       What is the use of command objects?

They are used to connect connection object to Data reader or dataset. Following are the methods provided by command object:-

. ExecuteNonQuery: -

Executes the command defined in the Command Text 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 Command Text 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.

. ExecuteScalar: -

Executes the command defined in the Command Text property against the connection defined in the Connection property. Returns only 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

8.       What is the use of 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.

9.       What are basic methods of Data adapter?

There are three most commonly used methods of Data adapter:-

Fill: -

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.

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.

Intermediate:

11.   What are the various objects in Dataset?

Dataset has a collection of Data Table object within the Tables collection. Each Data Table object contains a collection of Data Row objects and a collection of Data Column 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 Default View object for each table. This is used to create a Data View 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 ADO.NET architecture for visualizing this answer in pictorial form

12.   How can we connect to Microsoft Access, FoxPro, and Oracle etc?

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

Note :- Small sample of OLEDB is provided in "WindowsAppOleDb" which uses "Nwind.mdb" in bin directory to display data in Listbox.

private void LoadData()

{

    try

    {

        string ConString = "Data Source=XE;User Id=system;Password=*****;";

        using (OracleConnection con = new OracleConnection(ConString))

        {

            OracleCommand cmd = new OracleCommand("SELECT * FROM HR.Employees", con);

            OracleDataAdapter oda = new OracleDataAdapter(cmd);

            DataSet ds = new DataSet();

            oda.Fill(ds);

            if (ds.Tables.Count > 0)

            {

                dataGridView1.DataSource = ds.Tables[0].DefaultView;

            }

        }

    }

    catch (Exception ex)

    {

        MessageBox.Show(ex.ToString());

    }

13.   How do we connect to SQL SERVER, which namespace do we use?

using System.Data.SqlClient; 

 private void button1_Click(object sender, EventArgs e)
        {
            string connetionString = null;
            SqlConnection cnn  ;
            SqlCommand cmd ;
            string sql = null;
            SqlDataReader reader ;

            connetionString = "Data Source=ServerName;Initial Catalog=DatabaseName;User ID=UserName;Password=Password";
            sql = "Your SQL Statement Here , like Select * from product";

            cnn = new SqlConnection(connetionString);
            try
            {
                cnn.Open();
                cmd = new SqlCommand(sql, cnn);
                reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    MessageBox.Show(reader.GetValue(0) + " - " + reader.GetValue(1) + " - " + reader.GetValue(2));
                }
                reader.Close();
                cmd.Dispose();
                cnn.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show("Can not open connection ! ");
            }
        }

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

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

Ans:b.) By using CommandBehavior.CloseConnection enumeration
Exp: SqlCommand.ExecuteReader method takes a parameter called as CommandBehaviour. When CommandBehavior.CloseConnection enumeration is used, the corresponding connection is closed when the reader on which it is opened is closed.
DataReader dr = SqlCommand.ExecuteReader(CommandBehaviour.CloseConnection);

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

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

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

Config files are the best place to store connection strings.If it’s a web-based application
“Web.config” file will be used and if it’s a windows application “App.config” files will be
used.

19.   What are the steps involved to fill a dataset?

a. Create a connection object.
b. Create an adapter by passing the string query and the connection object as parameters.
c. Create a new object of dataset.
d. Call the Fill method of the adapter and pass the dataset objec

Example:

using System.Data;
using System.Data.SqlClient;


SqlConnection conn = new SqlConnection("server=localhost;Database=MyDB;userid=sa;pwd=sa");

conn.Open();
SqlDataAdapter da = new SqlDataAdapter("select * from table1", con);
DataSet ds = new DataSet();

da.Fill(ds);

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

DataSet object provides following methods to generate XML: 1) ReadXML Reads a XML schema and data in to Dataset. 2) WriteXML Writes data, and optionally the schema, from the DataSet into an XML file and saves it to disk. 3) GetXML It returns an XML string that is a representation of the data stored in the DataSet. GetXml returns XML as a string, so it requires more overhead than WriteXml to write XML to a file.

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

Note: - This book does not have any sample of Acceptchanges. We leave that to readers as homework sample. But yes from interview aspect that will be enough.

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

Advance:

23.   How can we add/remove row is in “Data Table” object of “Dataset”?

24.   What is basic use of “Data View”?

“Data View” represents a complete table or can be small section of rows depending on some criteria. It is best used for sorting and finding data with in “data table”.

Data view has the following methods:-

Find
It takes an array of values and returns the index of the row.

Find Row
This also takes array of values but returns a collection of “Data Row”.

If we want to manipulate data of “Data Table” object create “Data View” (Using the “Default View” we can create “Data View” object) of the “Data Table” object and use the following functionalities:-

Add New
Adds a new row to the “Data View” object.

Delete
Deletes the specified row from “Data View” object.

25.   What is the difference between “Dataset” and “Data Reader”?

Answer:

DataReader
===========
DataReader is like a forward only recordset. It fetches one row at a time so very less network cost compare to DataSet(Fethces all the rows at a time). DataReader is readonly so we can't do any transaction on them. DataReader will be the best choice where we need to show the data to the user which requires no transaction. As DataReader is forward only so we can't fetch data randomly. .NET Data Providers optimizes the datareader to handle huge amount of data.

DataSet
=======
DataSet is an in memory representation of a collection of Database objects including tables of a relational database schemas.
DataSet is always a bulky object that requires a lot of memory space compare to DataReader. We can say that the DataSet is a small database because it stores the schema and data in the application memory area. DataSet fetches all data from the datasource at a time to its memory area. So we can traverse through the object to get the required data like querying database.

26.   How can we load multiple tables in a Dataset?

                  Ans1:

SqlDataAdapter da = new SqlDataAdapter("Select * from Employee; Select * from Department", mycon);
da.Fill(ds);
ds.Tables[0].TableName = "Employee";
ds.Tables[1].TableName = "Department";

 Ans2:

CREATE PROCEDURE testSP
AS
BEGIN
 SELECT CategoryID,CategoryName,Description FROM Categories 
 SELECT SupplierID,CompanyName,ContactName FROM Suppliers
 SELECT ProductID,ProductName,CategoryID,SupplierID FROM Products
END
GO

C#

using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["NorthwindNewConnectionString"].ToString()))
{
 SqlDataAdapter da = new SqlDataAdapter("testSP",conn);
 da.SelectCommand.CommandType = CommandType.StoredProcedure;

 DataSet ds = new DataSet();

 da.Fill(ds);

 DataTable dtCategories = ds.Tables[0];
 DataTable dtSuppliers = ds.Tables[1];
 DataTable dtProducts = ds.Tables[2];
}

27.   How can we add relation between tables in a Dataset?

DataSet objDS = new DataSet();

            objDS.Tables.Add(Employee);

            objDS.Tables.Add(Department);

            DataRelation objRelation = new DataRelation("objRelation",

            Employee.Columns["EmpID"], Department.Columns["EmpID"]);

            objDS.Relations.Add(objRelation);    

28.   What is the use of Command Builder?

Answer:

Command Builder builds “Parameter” objects automatically. Below is a simple code, which uses command builder to load its parameter objects.

Dim pobjCommandBuilder As New OleDbCommandBuilder(pobjDataAdapter)
pobjCommandBuilder.DeriveParameters(pobjCommand)

Be careful while using “Derive Parameters” method as it needs an extra trip to the Data store, which can be very inefficient

29.   What’s difference between “Optimistic” and “Pessimistic” locking?

In pessimistic locking when user wants to update data it locks the record and till then no one can update data. Other user’s can only view the data when there is pessimistic locking.

In optimistic locking multiple users can open the same record for updating, thus increase maximum concurrency. Record is only locked when updating the record. This is the most preferred way of locking practically. Now a days in browser based application it is very common and having pessimistic locking is not a practical solution.

30.   How many ways are there to implement locking in ADO.NET?

31.  Answer:

Following are the ways to implement locking using ADO.NET: -

• When we call “Update” method of Data Adapter it handles locking internally. If the Dataset values are not matching with current data in Database, it raises concurrency exception error. We can easily trap this error using Try. Catch block and raise appropriate error message to the user.

• Define a Date time stamp field in the table. When actually you are firing the UPDATE SQL statements, compare the current timestamp with one existing in the database. Below is a sample SQL which checks for timestamp before updating and any mismatch in timestamp it will not update the records. This I the best practice used by industries for locking.

Update table1 set field1=@test where Last Timestamp=@Current Timestamp

• Check for original values stored in SQL SERVER and actual changed values. In stored procedure check before updating that the old data is same as the current Example in the below shown SQL before updating field1 we check that is the old field1 value same. If not then some one else has updated and necessary action has to be taken.

Update table1 set field1=@test where field1 = @oldfield1value
Locking can be handled at ADO.NET side or at SQL SERVER side i.e. in stored procedures. For more details of how to implementing locking in SQL SERVER read “What are different locks in SQL SERVER?” in SQL SERVER chapter.

32.   How can we perform transactions in .NET?

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

DataSet.Clone method copies the structure of the DataSet, including all DataTable schemas, relations, and constraints. But it does not copy any data.

Whereas, DataSet.Copy method copies both the structure and data.

34.   Can you explain the difference between an ADO.NET Dataset and an ADO Record set?

35.   Explain in detail the fundamental of connection pooling?

36.   What is Maximum Pool Size in ADO.NET Connection String?

Answer:

Maximum pool size decides the maximum number of connection objects to be pooled. If the maximum pool size is reached and there is no usable connection available the request is queued until connections are released back in to pool.
So it is always a good habit to either call the close or dispose method of the connection as soon as you have finished work with the connection object.

37.   How to enable and disable connection pooling?

For .NET it is enabled by default but if you want to just make sure set Pooling=true in the connection string. To disable connection pooling set Pooling=false in connection string if it is an ADO.NET Connection. If it is an OLEDBConnection object set OLE DB Services=-4 in the connection string.

38.   What extra features does ADO.Net 2.0 have?

Answer:

Bulk Copy Operation
Bulk copying of data from a data source to another data source is a newly added feature in ADO.NET 2.0. ADO.NET inrtoduces bulk copy classes which provide fastest way to transfer\ data from once source to the other. Each ADO.NET data provider has bulk copy classes. For example, in SQL .NET data provider, the bulk copy operation is handled by SqlBulkCopy class, which can read a DataSet, DataTable, DataReader, or XML objects.

Data Paging
A new method is introduced ExecutePageReader which takes three parameters - CommandBehavior, startIndex, and pageSize. So if you want to get rows ony from 10 - 20, you can simply call this method with start index as 10 and page size as 10.

Batch Update
If you want to update large number of data on set ADO.NET 2.0 provides UpdateBatchSize property, which allows you to set number of rows to be updated in a batch. This increases the performance dramatically as round trip to the server is minimized.

Load and Save Methods
In previous version of ADO.NET, only DataSet had Load and Save methods. The Load method can load data from objects such as XML into a DataSet object and Save method saves the data to a persistent media. Now DataTable also supports these two methods. You can also load a DataReader object into a DataTable by using the Load method.

New Data Controls
In toolbox you can see three new controls - DataGridView, DataConnector, and DataNavigator.

DataReader's New Execute Methods
Some new execute methods introduced are ExecutePageReader, ExecuteResultSet, and ExecuteRow.