Component classes

The Connection object creates the connection to the database. Microsoft Visual Studio .NET provides two types of Connection classes first is  the SqlConnection object, which is designed specifically to connect to Microsoft SQL Server 7.0 or later, and the other is  OleDbConnection object, it can provide connections to a wide range of database types like Microsoft Access and Oracle. The Connection object contains all of the information required to open a connection to the database.

It is represented by two corresponding classes: SqlCommand and OleDbCommand. Command objects are used to execute commands to a database across a data connection. Command objects can be used to execute stored procedures on the database, SQL commands, and  return complete tables directly. It provide three methods which are used to execute commands on the database:

(i)-ExecuteNonQuery: Executes commands that have no return values such as INSERT, UPDATE or DELETE
(ii)ExecuteScalar: Returns a single value from a database query
(iii)ExecuteReader: Returns a result set by way of a DataReader object

The DataReader object provides a forward-only, read-only, connected stream recordset from a database. Unlike other components of the Data Provider, DataReader objects cannot be directly instantiated.The DataReader is returned as the result of the Command object's ExecuteReader method. The SqlCommand.ExecuteReader method returns a SqlDataReader object, and the OleDbCommand.ExecuteReader method returns an OleDbDataReader object. The DataReader can provide rows of data directly to application logic when we do not need to keep the data cached in memory because only one row is in memory at a time, the DataReader provides the lowest overhead in terms of system performance but requires the exclusive use of an open Connection object for the lifetime of the DataReader.

The DataAdapter is the class at the core of ADO .NET's disconnected data access. It is used  to fill a DataTable or DataSet with data from the database with it's Fill method. After the memory-resident data has been manipulated.Tthe DataAdapter can commit the changes to the database by calling the Update method. The DataAdapter provides four properties that represent database commands:

When the Update method is called, changes in the DataSet are copied back to the database and the appropriate InsertCommand, DeleteCommand, or UpdateCommand is executed. 

Define connected and disconnected data access in ADO.NET

connected data access in ADO.NET :-Data reader is based on the connected architecture for data access. Does not allow data manipulation

Disconnected data access in ADO.NET:-Dataset supports disconnected data access architecture. This gives better performance results.

Describe CommandType property of a SQLCommand in ADO.NET.

  CommandType property:-CommandType property  is a property of Command object that can be set to Text, Storedprocedure. If it is Text, the command executes the database query. When it is StoredProcedure, the command runs the stored procedure. A SqlCommand is an object that allows specifying what is to be performed in the database.

Access database at runtime using ADO.NET

Access database at runtime using Sqlconnection in ADO.NET

Using System.Data.SqlClient;
SqlConnection con = new SqlConnection(connectionString)
con.Open();
string stringQuery = "select EmployeeName from emp";
SqlCommand cmd = new SqlCommand(stringQuery, con);
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
        Console.WriteLine(dr [0]);
}
dr.Close(); 
con.Close();

<connectionStrings>
<add name="DragDropWinApp.Settings.TestConnectionString"
connectionString=
"Data Source=(local);Initial Catalog=emp;Integrated Security=True"
providerName="System.Data.SqlClient" />