How does Ado Net work?-trickcode

ADO.NET Fundamentals

We will be using SQL Server as our data source for this lesson. To start connecting to a database, first, we need to import the System.Data.SqlClient namespace. It contains the classes that we will be using to connect to a database using code.

using System.Data.SqlClient;

This lesson only shows you ADO.NET’s capability of connecting to an SQL Server data source. Each part will be explained briefly but don’t worry if you don’t get it for the first time as they will be explained in greater detail in later lessons.

Let’s create a form that contains a ComboBox that will list the names of all the employees from the Northwind Database. Also provide 4 labels and TextBoxes that will show the individual employee’s information. Change the Text property of your labels to match the one below. Your form should look like this:

Change the Name property of the ComboBox to comboBoxEmployees. Also, change the Name properties of the TextBoxes to textBoxFirstName, textBoxLastName, textBoxAddress, and textBoxBirthDate. Change the Name property of the Form to FormDatabase and its Text to Database Connection(optional).

Go to the code editor and import the namespace System.Data.SqlClient and System.Data namespaces. The System. Data namespace contains the CommandBehavior enumeration that will be used later.

How does Ado.Net work?

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

After importing the namespaces, declare the needed private fields inside the class.

public partial class FormDatabase : Form
{
    private SqlConnection connection;
    private SqlCommand command;
    private SqlDataReader reader;

    //... some code omitted
}

SqlConnection class represents the connection to our SQL Database.
SqlCommand is the SQL command that will be used to query data or execute commands to the database.

The SqlDataReader reads each row of the table from our database.
Go back to the Designer View and double click the form (not the controls), to add an event handler to the Load event of the form. The Load event of the form will execute once the form has fully loaded when the program is run.

Add the following code inside the event handler.

private void Form1_Load(object sender, EventArgs e)
{
    connection = new SqlConnection();
    command = new SqlCommand();

    connection.ConnectionString = @"Data Source=localhost\SQLEXPRESS;" +
          "Initial Catalog=Northwind;Integrated Security=SSPI";
    command.CommandText = "SELECT EmployeeID, FirstName, LastName FROM Employees";
    command.Connection = connection;

    try
    {
        connection.Open();
        reader = command.ExecuteReader();
        while (reader.Read())
        {
            comboBoxEmployees.Items.Add(reader["FirstName"].ToString()
                + " " + reader["LastName"].ToString());
        }
        reader.Close();
    }
    catch (SqlException)
    {
        MessageBox.Show("An error occured while connecting to the server.", "Error");
    }
    finally
    {
        connection.Close();
    }
}

We first created new instances of the SqlConnection and SqlCommand classes.

connection = new SqlConnection();
command = new SqlCommand();

We then provide the connection string for our connection. A connection string is made up of a semicolon delimited collection of attribute/value pairs that define how to connect a data source.

connection.ConnectionString = @"Data Source=localhost\SQLEXPRESS;" +

"Initial Catalog=Northwind;Integrated Security=SSPI";

The connection string is:

Data Source=localhost\SQLExpress;InitialCatalog=Northwind;Integrated Security=SSPI
Each piece of data is separated by semicolon. Each data contains the name of the attribute and the value. The Data Source indicates the name of the server where the data source is located. localhost simply means your current computer. \SQLExpress means we need to use the server instance named SQLExpress. If you are using the full version of the SQL Server, then you can ommit this part and simply use localhost.

Initial Catalog is the name of the database that the connection will be accessing. Since we are using the Northwind database, we provided Northwind as the value for this attribute. If you wan’t to change the database during run-time, you can use the Connection.ChangeDatabase() method.

Integrated Security indicates you want to connect to SQL Server using the Windows account if you supply SSPI (Security Support Provider Interface). Alternatively, you can supply a user ID and password that’s defined in the database for SQL Server authentication, although this method is less secure and is discouraged.
You can also provide another attribute named ConnectionTimeout which determines how long in seconds do you have to wait before generating an error that a connection cannot be established. The default value is 15 seconds. If you give 0 as the duration, then it will wait indefinitely while connecting to the server.

command.CommandText = "SELECT EmployeeID, FirstName, LastName FROM Employees";

The SqlCommand class provides you with the CommandText property which accepts an SQL command string. Our command in the code above simply means “Get all the EmployeeIDs, FirstNames, and LastNames of all the Employees from the Employees table. A table in a database contains a collection of rows that represents each record. Each row contains one or more columns which represents the fields such as EmployeeID, FirstName, and LastName. We will be needing this command to get the values that we need.

command.Connection = connection;

We also need to indicate the connection that will be used by our command. We used the Connection property of the SqlCommand class and store our previously created SqlConnection instance.

try
{
    // codes omitted
}
catch (SqlException)
{
    MessageBox.Show("An error occured while connecting to the server.", "Error");
}
finally
{
    connection.Close();
}

The next line of codes are enclosed in a try block because we will be attempting to open our database. Errors are common when openning a connection. A common error is if the values inside your connection string is incorrect. When an error is found, the program will throw an SqlException. We provided a catch block that will catch this exception and show an error message to notify the user. We also provided a finally block and inside it is the code to close the connection, because our connections need to be closed.

connection.Open();

This commands opens the connection our connection. We need to open the connection first before accessing it’s data. Another technique is using DIsconnected Connection which will be discussed in a later lesson.

reader = command.ExecuteReader();

Once you successfully openned a connection, we need to use the SqlDataReader to access its data. we use the SqlCommand.ExecuteReader() to create an instance of the reader. The SqlDataReader uses a live connection and should be used quickly and then closed. It supports fast-forward-only read-only access to your results, which is generally all you need when retrieving information.

while (reader.Read())

{

//code omitted

}

We then created a while loop to access each row in our data table. We used the SqlReader.Read() method to get the first row of data from the table. The Read method wil get the next row of data everytime it is executed. If it successfully gets a row of data, it will return true. If no more data can be found, typically because the table is empty or you reach the end of the table, then it will return false stopping the loop.

comboBoxEmployees.Items.Add(reader["FirstName"].ToString()

+ " " + reader["LastName"].ToString());
To access a specific column or field in the current row, you can specify the name of the column as a key for the reader object. We accessed both the FirstName and LastName fields and converted them to string then combined them to form the full name of the employee. We then added the result in the Items property of the ComboBox by using the Add method. The result is each of the names of the employees are added to the list of names of the comboBoxEmployees control. After the loop, we closed the reader, and the finally block is executed closing the connection as well.

Now return to the Design View and double click the ComboBox control to create an event handler for it’s Changed event which triggers when you changed the selected item of the ComboBox. Add the following code.

private void comboBoxEmployees_SelectedIndexChanged(object sender, EventArgs e)
{
    string name = comboBoxEmployees.Text;

    command.CommandText = @"SELECT FirstName, LastName, Address, BirthDate" +
        @" FROM Employees WHERE (FirstName + ' ' + LastName) = '" + name + "'";

    try
    {
        connection.Open();

        reader = command.ExecuteReader(CommandBehavior.SingleResult);
        reader.Read();

        textBoxFirstName.Text = reader["FirstName"].ToString();
        textBoxLastName.Text = reader["LastName"].ToString();
        textBoxAddress.Text = reader["Address"].ToString();
        textBoxBirthDate.Text = reader["BirthDate"].ToString();

        reader.Close();
    }
    catch
    {
        MessageBox.Show("An error occured while connecting to the server.", "Error");
    }
    finally
    {
        connection.Close();
    }
}

We first store the currently selected text of the comboBoxEmployees. We will need this data to query the details associated with the name of the selected employee.

command.CommandText = @"SELECT FirstName, LastName, Address, BirthDate" +

@" FROM Employees WHERE (FirstName + ' ' + LastName) = '" + name + "'";

We now change the command for our SqlCommand instance. The command will be getting the fields FirstName, LastName, Address, and BirthDate from the employees table. Notice the WHERE keyword. This will filter the results and select only the records that math the condition following the WHERE. It’s like an if statement. The condition is if the combined FirstName and LastName or the record is similar to the the selected name of the ComboBox, then get that record.

After that, we again placed the following codes inside a try block as we will be reopenning our connection to the database. Note that we don’t need to set the connection string of our SqlConnection instance since it was set in the Form’s Load event handler. That’s the reason why we made our SqlConnection and SqlCommand as fields of the class so they can be accessed throughout the class.

reader = command.ExecuteReader(CommandBehavior.SingleResult);

reader.Read();
We then used the SqlCommand.ExecuteReader but this time, we used that overloaded version that accepts a System.Data.CommandBehavior enumeration value. Since we imported System.Data, we don’t have to write the full name. The SingleResult value means that the reader will only get the first matching record since we are only looking for a single person. We used the Read method to access the matching record.

textBoxFirstName.Text = reader["FirstName"].ToString();

textBoxLastName.Text = reader["LastName"].ToString();

textBoxAddress.Text = reader["Address"].ToString();

textBoxBirthDate.Text = reader["BirthDate"].ToString();

The codes above assigned each values of the column to their respective textboxes for displaying. We then close the reader and finally closed the connection. Run the program and select a name of an employee using the combo box. If everything went fine, you should be presented with a list of names. Select a name and the textboxes will be fields with the employee’s details.