ADO.NET

ADO.NET is a set of classes designed to access data sources.
We have two categories of these classes:

1. Data Container classes: these classes are merely used for temporary data storage. Data retrieved from database are stored in these classes. Beyond that data container classes can act like your database by maintaining relationships between tables (offline tables not real database tables). This is sometimes referred as Disconnected architecture. These classes include: DataSet
2. Data Connection classes (Data Providers): these classes are the concrete tools to work with data sources. Data providers help us establish connection with our database, retrieve data, update and many related tasks. These classes include: [provider]Connection.

In ADO.NET we have four Data Providers:

1. Sql Data Provider
2. Oracle Data Provider
3. OleDb Data Provider
4. ODBC Data Provider

The main common classes in all data providers are: Connection, Command, DataAdapter, & DataReader.

Sql Data Provider:

• SqlConnection - to establish a connection to our database. This is the first task when we want to deal with database. If the connection is established successfully you have cut a major part of your journey. As a rule every opened connection must be closed after usage. ConnectionString is a property of this class that needs to be set with a number of parameters passed with their values. After this you call Open() method to establish the connection and to disrupt it you call Close() method.
• SqlCommand - to execute sql statements (commands) against the database.This is second task you should do. Two properties need to be set to use this class. The CommandText is set with an sql statement string. The Connection is set with the connection object we have created.
• SqlDataAdapter- it is a bridge between database and dataset.
• SqlDataReader - to read the data returned by SqlCommand

Connection String
To establish a successful connection we need to understand the different ways to construct connection string based on different DBMSs & their different versions. Basically we need to set and pass at least the following parameters in the connection string:

• Data Source - the name or the address where the database is residing.
• Initial Catalog , Database or AttachDbFilename - the name and the path of the database file
• Integrated Security - the type of authentication to use. We have two types of authentication here. Windows Authentication and SQL Server Authentication. If you set this parameter to true you want to use Windows Authentication which is recommended otherwise you are using SQL Server Authentication which requires you to provide two other parameters namely User ID and Password.
• User Instance - you set this to true if your database is not in the master list of sql server. This means roughly your database is not attached to the sql server.

Let us go down: when building connection string you need to consider the version of sql server you are using and whether or not your database is attached to the sql server.

Option 1: You are using Full version of SQL Server

Criteria:  your database must be attached to the sql server
 Example connection string:

Dim conString As String

conString = "Data Source=(local);Initial Catalog=yourdatabase.mdf;" _ 

& "Integrated Security=true;"

Option 2: You are using SQL Express and Database is attached to server

Example connection string

Dim conString As String

conString = "Data Source=(local)\SQLExpress;Initial Catalog=yourdatabase.mdf;" _ 

& "Integrated Security;"

Option 3: You are using SQL Express and Database is not attached to server

Exmaple connection string

Dim conString As String

conString = "Data Source=(local)\SQLExpress;" _ 

& "AttachDbFilename=|DataDirectory|\yourdatabase.mdf;Integrated Security=true;"_ 

& "User Instance=true;"