An important aspect of establishing a connection to a computer is security. Even if you are developing an application that would be used on a standalone computer, you must take care of this issue. The security referred to in this attribute has to do with the connection, not how to protect your database.

If you are working in the Add Connection dialog box, if you wan to use the account that opened the application, you can accept the Windows Authentication radio button. If you want to specify the authentication, click the Use SQL Server Authentication:

If you are programmatically establishing the connection, 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 creating your connection string using the SqlConnectionStringBuilder class, it is equipped with a Boolean property named IntegratedSecurity:

public bool IntegratedSecurity { get; set; }

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:

void InitializeComponent()
{
    SqlConnection connection =
        new SqlConnection("Server=(local);Trusted_Connection=SSPI");
}

When you use the true or SSPI values, the user name (if any) and the password (if any) of the person opening your application would be applied. For example, if the application is being opened on Microsoft Windows 7 Professional, Ultimate, Enterprise, or Windows 8 Pro that has a default user name and password, the application would be opened fine without checking security.

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

void InitializeComponent()
{
    SqlConnection connection =
        new SqlConnection("Server=(local);Integrated Security=no");
}

If you are using the Add connection dialog box and if you had clicked the Use SQL Server Authentication radio button, type the user name in the User Name text box. When you are programmatically connecting to a server and you are using the SqlConnection, use the User ID attribute to provide a username. If you are using the SqlConnectionStringBuilder class, it is equiped with a property named UserID, which is a string:

public string UserID { get; set; }

Here is an example of using it:

void CreateConnectionString()
{
    SqlConnectionStringBuilder csbExercise = new SqlConnectionStringBuilder();

    csbExercise.DataSource = "(local)";
    csbExercise.UserID = "Admin";
}

When you are establishing a connection to a server, besides the username, to specify the password, if you are using the Add connection dialog box and if you had clicked the Use SQL Server Authentication radio button, after typing a user name in the User Name text box, type the corresponding password in the other text box. If yo are using SQL code, use either the PASSWORD or the PWD (remember that the attributes are not case-sensitive but the value of the password is) attribute and assign it the exact password associated with the User ID attribute of the same connection string. Here is an example:

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

In some circumstances, you can use an empty password in which case you would assign an empty string to the password attribute. If you are using the SqlConnectionStringBuilder class, it is equipped with a property named Password:

public string Password { get; set; }

You can then assign a password to this property.

If you are working from the Add Connection dialog box, if you want to establish a connection to a specific database, click the arrow of the Select Or Enter A Database Name combo box and select the desired database. If you are working programmatically, to let you specify the database, the connection string includes an attribute named Database. The Database attribute allows you to specify the name of the database you are connecting to, if any. The Database keyword can also be substituted for the Initial Catalog value. If you are connecting to an existing database, assign its name to this attribute. If you are not connecting to a database, you can omit this attribute. Alternatively, you can assign nothing to this attribute. Here is an example:

void InitializeComponent()
{
    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. If you are using the SqlConnectionStringBuilder class, it is equipped with a property named InitialCatalog:

public string InitialCatalog { get; set; }

You can assign the name of the database to this property.

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.

If you are using the Add Connection dialog box, after specifying the necessayr pieces of information, to open the connection, click OK. If you are writing code, to open the connection, you must call the SqlConnection.Open() method. Its syntax is:

public override void Open();

Here is an example of calling it:

void InitializeComponent()
{
    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.

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:

void InitializeComponent()
{
    SqlConnection connection = 
        new SqlConnection("Server=(local);Initial Catalog='exercise1';");
    connection.Open();

    // Do some things here

    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.

The SqlConnection class is derived from a class named DbConnection:

public abstract class DbConnection : Component, 
                                     IDbConnection, IDisposable

As you can see, the DbConnection class implements the IDisposable interface. This means that, to close the connection and free its resources, you can use the using keyword. This would be done 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 and you can take actions, such as creating a database and/or manipulating data. An action you perform on the database server or on a database is called a command.

To support the various commands you can perform on a Microsoft SQL Server database, the System.Data.SqlClient namespace provides the SqlCommand class. To use it, you can declare a variable of type SqlCommand using one of its constructors.

The SqlCommand class is equipped with four constructors. The default constructor allows you to initiate a command without specifying what action would be taken. The action to perform is created as a string statement. This action is represented by the CommandText property of the SqlCommand class, which is of type string.

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:

void InitializeComponent()
{
    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:

void InitializeComponent()
{
    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. To do this, you can use the second constructor of the SqlCommand class. The syntax of this constructor is:

public SqlCommand(string cmdText);

Once again, 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:

void InitializeComponent()
{
    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. Here is an example:

void InitializeComponent()
{
    SqlConnection connection = new SqlConnection("Server=(local);" +
                                                 "Integrated Security=no;" +
                                                 "User ID=sa;PWD=$outh~@kotA");

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

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

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.

In the next sections and future lessons, we will study the types of commands that can be carried.

After establishing a connection and specifying what command needs to be carried, you can execute it. To support this, the SqlCommand class is equipped with the ExecuteNonQuery() method. Its syntax is:

public override int ExecuteNonQuery();

This method does not take any argument. The SqlCommand object that calls it must have prepared a valid command. In future lessons, we will see that there are other ways a SqlCommand object can execute commands.

In some cases, some actions take longer than others to execute. For this type of command, the compiler would keep trying to execute a command until successful. If there is a problem, this operation can take long or too long. You can specify how long the compiler should wait to try executing the command, again.

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 know how a data reader works, imagine you have a list of values as follows:

If you use a data reader to read these values, the compiler visits the first value to read it. After reading it, the compiler moves to the second value. After visiting the second value, the compiler moves to the third value and so on. One of the particularities of a data reader is that, once it visits a value, reads it, and moves to the next value, the compiler cannot refer to the previous value. This can be illustrated as follows:

To support data readers, the .NET Framework provides, for a Microsoft SQL Server database, a class named SqlDataReader. To get a data reader, 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 from. 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, the data reader reads one value at a time and moves to the next.