Basics of ADO.NET

ADO.NET

• ASP.NET Web Applications
• Console Applications
• Windows Applications.

Various Connection Architectures

1. Connected architecture: the application remains connected with the database throughout the processing.
   
   
2. Disconnected architecture: the application automatically connects/disconnects during the processing. The application uses temporary data on the application side called a DataSet.

Understanding ADO.NET and it's class library

Important Classes in ADO.NET

1. Connection Class
2. Command Class
3. DataReader Class
4. DataAdaptor Class
5. DataSet.Class

1. Connection Class

2. Command Class

• ExecuteReader: Returns data to the client as rows. This would typically be an SQL select statement or a Stored Procedure that contains one or more select statements. This method returns a DataReader object that can be used to fill a DataTable object or used directly for printing reports and so forth.
  
• ExecuteNonQuery: Executes a command that changes the data in the database, such as an update, delete, or insert statement, or a Stored Procedure that contains one or more of these statements. This method returns an integer that is the number of rows affected by the query.
  
• ExecuteScalar: This method only returns a single value. This kind of query returns a count of rows or a calculated value.
  
• ExecuteXMLReader: (SqlClient classes only) Obtains data from an SQL Server 2000 database using an XML stream. Returns an XML Reader object.

3. DataReader Class

4. DataAdapter Class

5. DataSet Class

How to Connect to a Database using ADO.NET

Server="nameof the server or IP Address of the server"

Database="name of the database"

userid="user name who has permission to work with database"

word="the word of userid"

SQL Authentication

String constr="server=.;database=institute;user id=rakesh;word=abc@123";

Or:

String constr="data source=.;initial catalog=institute;uid=rakesh;pwd=abc@213";

Windows Authentication

String constr="server=.;database=institute;trusted_connection=true"

Or:

String constr="server=.;initial catalog=institute;integrated security=true"

1. Create a SqlConnection object using a connection string.
   
   
2. Handle exceptions.
   
   
3. Open the connection.
   
   
4. Create a SQLCommand. To represent a SQLCommand like (select * from studentdetails) and attach the existing connection to it. Specify the type of SQLCommand (text/storedprocedure).
   
   
5. Execute the command (use executereader).
   
   
6. Get the Result (use SqlDataReader). This is a forwardonly/readonly dataobject.
   
   
7. Close the connection
8. Process the result
9. Display the Result