ADO.NET is a group of libraries used to create powerful databases using various sources that include Microsoft SQL Server, Microsoft Access, Oracle, XML, etc. ADO.NET relies on the .NET Framework's various classes to process requests and perform the transition between a database system and the user. The operations are typically handled through the DataSet class.

While ADO.NET is the concept of creating and managing database systems, the DataSet class serves as an intermediary between the database engine and the user interface.

The classes used to create ADO.NET databases are defined in the System.Data namespace and are stored in the System.Data.dll library. If you are creating the application from scratch, remember to reference the System.Data.dll library and add the System.Data namespace in your file(s).

After installing Microsoft SQL Server, you can use it. To perform an operation on a database server, you must establish a connection to it.

To support a connection to a database server, the .NET Framework provides the SqlConnection class that is defined in the System.Data.SqlClient namespace. Before using this class, you can first include this namespace in your file:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;

public partial class _Default : System.Web.UI.Page 
{
    protected void Page_Load(object sender, EventArgs e)
    {

    }
}

To connect to a database, you can first declare a variable of type SqlConnection using one of its two constructors. Besides the default constructor, the second constructor takes as argument a string value. Its syntax is:

public SqlConnection(string connectionString);

You can create the necessary (but appropriate) string in this constructor when declaring the variable. This would be done as follows:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;

public partial class _Default : System.Web.UI.Page 
{
    protected void Page_Load(object sender, EventArgs e)
    {
        SqlConnection connection = new SqlConnection("Something");
    }
}

If you want, you can first create the string that would be used to handle the connection, then pass that string to this construction.

To support the connection as an object, the SqlConnection class is equipped with the ConnectionString property. If you use the default constructor, you can first define a string value, then assign it to this property.

To use a SqlConnection object, you must provide various pieces of information joined into a string but are separated from each other with a semi-colon ";". Each piece appears as a Key=Value:

Key1=Value1;Key2=Value2;Key_n=Value_n

It can be passed as follows:

SqlConnection connection = new SqlConnection("Key1=Value1;Key2=Value2;Key_n=Value_n");

or assigned as a string to the SqlConnection.ConnectionString property:

string strConnection = "Key1=Value1;Key2=Value2;Key_n=Value_n";
SqlConnection connection = new SqlConnection();

connection.ConnectionString = strConnection;

To establish a connection, you must specify the computer you are connecting to, that has Microsoft SQL Server installed. If you are creating your application on the same computer on which SQL Server is installed, you can use (local). Here is an example:

SqlConnection connection = new SqlConnection("Server=(local); ");

If you know the name of the computer, you can assign it to the computer attribute. Here is an example:

SqlConnection connection = new SqlConnection("Server=central; ");

In the same way, if you are connecting to a specific computer, you must provide its name. Here is an example:

SqlConnection connection = new SqlConnection("Data Source=central; ")

An important aspect of establishing a connection to a computer is security. To support security, the connection string of the SqlConnection class includes an attribute called Trusted_Connection or Integrated Security that can have a value of true, false, yes, no, or SSPI with the SSPI having the same indication as true.

If you are establishing a trusted or simple connection that doesn't need to be verified, you can assign a value of true or SSPI.  Here is an example:

SqlConnection connection =
        new SqlConnection("Server=(local);Trusted_Connection=SSPI");

If you are programmatically establishing the connection, to apply authentication, you can assign false or no to the security attribute you selected.

To specify the user name, after assigning false or no to the security attribute, you must use the User ID attribute and assign it a valid username. Here is an example:

string strConnection = "Server=(local);" +
                           "Integrated Security=no;" +
                           "User ID=wmessmann";
    SqlConnection connection = new SqlConnection(strConnection);

Besides the username, to create a secured connection, you must also provide a password. To specify the password, you can user either the PASSWORD or the PWD. Here is an example:

string strConnection = "Server=(local);" +
                           "Integrated Security=no;" +
                           "User ID=wmessmann;PWD=$outh~@kotA";
    SqlConnection connection = new SqlConnection(strConnection);

To specify the database, the connection string includes an attribute named Database. The Database keyword can also be substituted for the Initial Catalog value. Here is an example:

SqlConnection connection = new SqlConnection(Server=(local);Database=;);

Another alternative is to assign an empty, single-quoted, string to this attribute. Here is an example:

void InitializeComponent()
{
    string strConnection = "Server=(local);Initial Catalog='exercise1';";
    SqlConnection connection = new SqlConnection(strConnection);
}

As mentioned above, the Database attribute is optional, especially if you are only connecting to the computer and not to a specific database.

There are various other attributes used in the connection string. They include Network Library (also called Net), Application Name, Workstation ID, Encrypt, Connection Timeout, Data Source, Packet Size, AttachDBFilename, Current Language, Persist Security Info.

After creating the connection string, when the application executes, the compiler would "scan" the string to validate each key=value section. If it finds an unknown Key, an unknown value, or an invalid combination of key=value, it would throw an ArgumentException exception and the connection cannot be established.

When working in Microsoft Visual Studio, if you create a connection using the Server Explorer, a link to the database would display as a sub-node of the Data Connection link. You can re-use one of these connections when necessary. As an alternative, on the main menu, you can click Data -> Add New Data Source... In the Data Source Configuration Wizard, click Database and click Next. In the second page of the wizard, select an existing connection in the combo box.

After creating a connection string, to apply it and actually establish the connection, you must call the SqlConnection.Open(). Its syntax is:

public override void Open();

Here is an example of calling it:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;

public partial class _Default : System.Web.UI.Page 
{
    protected void Page_Load(object sender, EventArgs e)
    {
        SqlConnection connection =
        	new SqlConnection("Server=(local);Initial Catalog='exercise1';");
        connection.Open();
    }
}

As you can see, this method does not take any argument. The SqlConnection object that calls it is responsible to get the connection string ready:

• If the connection fails, the compiler would throw a SqlException exception
• If the connection string does not contain the computer attribute or the connection is already opened, the compiler would throw an InvalidOperationException exception.

After using a connection and getting the necessary information from it, you should terminate it. If you are working from a SqlConnection object, to close a connection, you can call the SqlConnection.Close() method. Its syntax is:

public virtual void Close();

This method is simply called to close the current connection. Here is an example of calling it:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;

public partial class _Default : System.Web.UI.Page 
{
    protected void Page_Load(object sender, EventArgs e)
    {
        SqlConnection connection =
        new SqlConnection("Server=(local);Initial Catalog='exercise1';");
        connection.Open();

        // Blah Blah Blah

        connection.Close();
    }
}

While you should avoid calling the Open() method more than once if a connection is already opened, you can call the Close() method more than once.

You should always remember to close the connection so that the resources that the database application was using can be made available to other applications. To assist you with this, you can use the using keyword as follows:

void InitializeComponent()
{
    using (SqlConnection connection =
        new SqlConnection("Data Source=(local);Integrated Security=yes"))
    {
        conDatabase.Open();
    }
}

When this code executes, it opens the connection. Inside of the curly brackets, you can do whatever you want. When the compiler reaches the closing curly bracket, it calls the SqlConnection.Close() method, which means you do not need to remember to close it.

After establishing a connection, if you are successful, the database system becomes available to you. An action you perform on the database server or on a database is called a command. 

If you want to use the default constructor, you can then create a string that would carry the action to perform. Once the string is ready, you can assign it the CommandText property. This would be done as follow:

SqlCommand CommandToExecute = new SqlCommand();
string strCommandToExecute = "Blah Blah Blah";

CommandToExecute.CommandText = strCommandToExecute;

After creating the action that would be performed, you must specify what connection would carry it. To do this, you can first create a SqlConnection object. To provide it to the command, the SqlCommand class is equipped with a property named Connection that is of type SqlConnection. After creating a SqlConnection object, to provide it to the command, you can assign it to the SqlCommand.Connection property. This would be done as follows:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;

public partial class _Default : System.Web.UI.Page 
{
    protected void Page_Load(object sender, EventArgs e)
    {
        string strConnection = "Server=(local);" +
                           "Integrated Security=no;" +
                           "User ID=sa;PWD=$outh~@kotA";
        SqlConnection connection = new SqlConnection(strConnection);

        SqlCommand CommandToExecute = new SqlCommand();
        string strCommandToExecute = "Blah Blah Blah";

        connection.Open();
        CommandToExecute.Connection = connection;
        CommandToExecute.CommandText = strCommandToExecute;

        connection.Close();
    }
}

Instead of declaring a SqlCommand variable and the command text separately, as an alternative, you can define the command text when declaring the SqlCommand variable. You can use the second constructor of the SqlCommand class. The syntax of this constructor is:

public SqlCommand(string cmdText);

After using this constructor, you must specify what connection would carry the action. To do this, you can assign a SqlConnection object to the Connection property of your SqlCommand. Here is an example:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;

public partial class _Default : System.Web.UI.Page 
{
    protected void Page_Load(object sender, EventArgs e)
    {
        string strConnection = "Server=(local);" +
                           "Integrated Security=no;" +
                           "User ID=sa;PWD=$outh~@kotA";
        SqlConnection connection = new SqlConnection(strConnection);

        SqlCommand CommandToExecute = new SqlCommand("Blah Blah Blah");

        connection.Open();
        CommandToExecute.Connection = connection;
        connection.Close();
    }
}

Instead of assigning a SqlConnection object to the SqlCommand.Connection property, you can specify what connection would carry the action at the same time you are creating the command. To specify the connection when declaring the SqlCommand variable, you can use the third constructor of this class. Its syntax is:

public SqlCommand(string cmdText, SqlConnection connection);

The second argument to this constructor is an established connection you would have defined. 

If you had initiated the action using the default constructor of the SqlCommand class, you can assign a SqlConnection object to the Connection property of the SqlCommand class.

After establishing a connection and specifying what command needs to be carried, you can execute it. This is done using the ExecuteNonQuery() method of the SqlCommand class:

public override int ExecuteNonQuery();

This method does not take any argument. The SqlCommand object that calls it must have prepared a valid command.

The SqlCommand.CommandTimeOut property allows you to specify the time to wait before trying to execute a command. The default value of this property is 30 (seconds). If you want a different value, assign it to your SqlCommand variable.

In this and the next few lessons, all of the commands we perform will be communicated as strings. When we study (stored) procedures, we will see other types of commands. To allow you to specify the type of command you want to perform, the SqlCommand class is equipped with the CommandType property, which is based on the CommandType enumeration.

The CommandType enumeration has three members: StoredProcedure, TableDirect, and Text. For a SqlCommand object, the default value is Text.

A data command is used to initiate an action to perform on a database. To read data of a database, one of the objects you can use is called a data reader. 

To support data readers, the .NET Framework provides, for a Microsoft SQL Server database, a class named SqlDataReader. To get a data reader, you can declare a variable of type SqlDataReader. This class does not have a constructor. This means that, to use it, you must (directly) specify where it would read its data. To provide data to the reader, the SqlCommand class is equipped with the ExecuteReader() method that is overloaded with two versions. The simplest version of this method uses the following syntax:

public SqlDataReader ExecuteReader();

Based on this, before using a data reader, you should first create a command that would specify how data would be acquired. Once the data is read, you can pass it to the data reader by assigning the result of a call to a SqlCommand.ExecuteReader() method to a SqlDataReader object.

Once data is supplied to the reader, you can access it, one value at a time, from top to bottom. To access data that the reader acquired, you can call its Read() method whose syntax is:

public override bool Read();

As mentioned already, the Read() method simply reads a value and moves on. When reading the values, as mentioned already many times, the data reader reads one value at a time and moves to the next.