Microsoft SQL Server and Visual Studio 2005

Introduction

ADO.NET is a group of libraries used to create powerful databases using various sources that include Microsoft SQL Server, Microsoft Access, Oracle, Paradox, 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, as we have introduced it so far, serves as an intermediary between the database engine and the user interface, namely the Windows controls that the user uses to interact with the computer. Still, remember that a DataSet object is used to manage lists, any lists, not just those created using database environments such as Microsoft SQL Server or Microsoft Access.

Besides using features of a database in an ADO.NET application, you can also fully take advantage of XML as it is completely and inherently supported by the DataSet class. To fully support XML in your application, we saw in previous lessons that the .NET Framework is equipped with the System.Xml.dll library. You may have noticed that, in previously lessons, to use XML, we never had to import any library. This was possible because, if you create a Windows Application using the New Project dialog box, the System.Xml.dll namespace is directly included in your application.

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 create a Windows Application from the New Project dialog box, Microsoft Visual Studio would automatically include the System.Data.dll library and add the System.Data namespace to your project, even if you are not creating a database application. This makes it convenient.

If you are creating the application from scratch, in order to use ADO.NET, you must 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 do this, you have to log on to the server. You have various options. If you are working directly on the server:

• You can start Microsoft SQL Server Management Studio and a Connect To Server dialog box would come up
• If you had clicked Cancel on the Connect To Server dialog box, to establish a connection, in the Object Explorer, you can click Connect -> Database Engine...
• If you had already established a connection to one server but you want to create a connection to another server, in the Object Explorer, you can click Connect -> Database Engine...

The Connect To Server dialog box allows you to identify yourself (or the account you will use) to establish a connection with a computer that is running Microsoft SQL Server. You must provide the necessary credentials. You can use Windows Authentication. Here is an example:

Alternatively, you can use an existing SQL Server account. To do this, select SQL Server Authentication from the Authentication combo box. Enter a username in the Login combo box and enter a password in the corresponding text box:

Once you are ready, click Connect.

To be as flexible as possible, ADO.NET allows you to work with various types of databases. This also means that when you create an application, you must certainly first specify the type of database system you would be using.

If you are creating a graphical database application using Microsoft Visual Studio, you do not have to first explicitly log on to a Microsoft SQL Server. You can establish a connection from Microsoft Visual Studio. To do this, first display the Server Explorer window. To display the Server Explorer, on the main menu, you can click View -> Server Explorer.

To create a new connection, you can right-click the Data Connections node and click Add Connection...

The Add Connection dialog box would display:

As another technique, to create a connection, 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, you can click New Connection to open the Add Connection dialog box.

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:

Imports System.Data.SqlClient

Public Class Exercise

End Class

To connect to a database, you can first declare a variable of type SqlConnection using one of its two constructors. The default constructor allows you to declare the variable without specifying how the connection would be carried. The second constructor takes as argument a string value. Its syntax is:

Public Sub New(connectionString As String)

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

Imports System.Data.SqlClient

Public Class Exercise

    Private Sub Exercise_Load(ByVal sender As Object, _
                              ByVal e As System.EventArgs) _
                              Handles Me.Load
        Dim connection As SqlConnection = New SqlConnection("Something")
    End Sub
End Class

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

Private Sub Exercise_Load(ByVal sender As Object, _
                              ByVal e As System.EventArgs) _
                              Handles Me.Load
    Dim strConnection As String = "Something"
    Dim connection As SqlConnection = New SqlConnection(strConnection)
End Sub

To support the connection as an object, the SqlConnection class is equipped with a property called ConnectionString that is a String. If you use the default constructor to prepare the connection, you can first define a String value, then assign it to this property. This would be done as follows:

Private Sub Exercise_Load(ByVal sender As Object, _
                              ByVal e As System.EventArgs) _
                              Handles Me.Load
    Dim strConnection As String = "Something"
    Dim connection As SqlConnection = New SqlConnection()

    connection.ConnectionString = strConnection
End Sub

To use a SqlConnection object, you must provide various pieces of information, packaged as one and made available to the variable. These pieces are joined into a string but are separated from each other with a semi-colon ";". Each piece appears as a Key=Value format. In our lesson, we will refer to each of these pieces (Key=Value) as an attribute of the connection string. When joined, these attributes appear as follows:

Key1=Value1;Key2=Value2;Key_n=Value_n

Anything that is part of this string is not case-sensitive. This whole ensemble is either passed as a string to the second constructor:

Private Sub Exercise_Load(ByVal sender As Object, _
                          ByVal e As System.EventArgs) _
                          Handles Me.Load
    Dim connection As SqlConnection = _
        New SqlConnection("Key1=Value1;Key2=Value2;Key_n=Value_n")
End Sub

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

Private Sub Exercise_Load(ByVal sender As Object, _
                          ByVal e As System.EventArgs) _
                          Handles Me.Load
    Dim strConnection As String = "Key1=Value1;Key2=Value2;Key_n=Value_n"
    Dim connection As SqlConnection = New SqlConnection()

    connection.ConnectionString = strConnection
End Sub

How you create these attributes depends on the type of computer you are connecting to, whether you are connecting to a database, what level of security you would use (or need), etc. There are various of these attributes, some of them are always required, some of them are usually optional, and some others depend on the circumstances.

To establish a connection, you must specify the computer you are connecting to, that has Microsoft SQL Server installed. We saw that you could specify this from the Connect to Server dialog box where you would select the machine object from the Server Name combo box:

If you are working from the Add Connection dialog box, to see the list of servers and select one, you can click the arrow of the Server Name combo box:

If you are programmatically connecting to a computer using the SqlConnection class, the connection string includes an attribute named Server, or Data Source, or Address, or Addr, or Network Address. For the rest of our lessons, this attribute will be referred to as the computer attribute.

If you are creating your application on the same computer on which Microsoft SQL Server is installed, the computer attribute can be identified as (local). Here is an example:

Private Sub Exercise_Load(ByVal sender As Object, _
                          ByVal e As System.EventArgs) _
                          Handles Me.Load
    dim connection as SqlConnection  = new SqlConnection("Server=(local); ")
End Sub

If you are working from the Add Connection dialog box, you can type (local) in the Server Name combo box and press Enter:

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

Private Sub Exercise_Load(ByVal sender As Object, _
                              ByVal e As System.EventArgs) _
                              Handles Me.Load
    Dim connection As SqlConnection = New SqlConnection("Server=central; ")
End Sub

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

Private Sub Exercise_Load(ByVal sender As Object, _
                              ByVal e As System.EventArgs) _
                              Handles Me.Load
    Dim connection As SqlConnection = New SqlConnection("Data Source=central; ")
End Sub

As an option, you can include the name of the computer in single-quotes.

If you are working from the Add Connection dialog box, you can type the name of the server without clicking the arrow of the combo box and press Enter.

Remember that the computer attribute is a requirement regardless of the (type of) application, even if it is local.

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 using the Connect To Server dialog box, after specifying the server, you can use the Windows Authentication or you can use SQL Server Authentication which you would select from the Authentication combo box:

If you are working from the Add Connection dialog box, you can click the Use Windows Authentication radio button. If you want to specify the username and the password, you should click the Use SQL Server Authentication radio button.

To support security, 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 establishing a trusted or simple connection that does not need to be verified, you can assign a value of true or SSPI.  Here is an example:

Private Sub Exercise_Load(ByVal sender As Object, _
                              ByVal e As System.EventArgs) _
                              Handles Me.Load
    Dim connection As SqlConnection = _
        New SqlConnection("Server=(local);Trusted_Connection=SSPI")
End Sub

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 Windows XP Home Edition (that, by default, does not require authentication), Windows 2000 Professional that has a default user name and password, or Windows XP Professional 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. If you do this, then you must (this becomes a requirement) specify the user name and the password. For example, the following code will produce an error if you execute it:

Private Sub Exercise_Load(ByVal sender As Object, _
                              ByVal e As System.EventArgs) _
                              Handles Me.Load
    Dim connection As SqlConnection = _
        New SqlConnection("Server=(local);Integrated Security=no")
End Sub

So, if you set the security attribute to False or No, then you must provide login credentials. In some cases, you can provide empty credentials or a blank password.

If you are using the Connect to Server dialog box and you want to apply authentication, after selecting SQL Server Authentication, you must enter a username (the default sa username may be automatically selected for you).

If you are using the Add Connection dialog box, after selecting the Use SQL Server Authentication radio box, you must enter a username in the indicated text box.

If you are programmatically creating the connection, 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:

Private Sub Exercise_Load(ByVal sender As Object, _
                              ByVal e As System.EventArgs) _
                              Handles Me.Load
    Dim strConnection As String = "Server=(local);" & _
                           "Integrated Security=no;" & _
                           "User ID=wmessmann"
    Dim connection As SqlConnection = New SqlConnection(strConnection)
End Sub

If you are "physically" connecting to the server using the Connect to Server dialog box, after selecting SQL Server Authentication, besides the username, you must provide a password to complete the authentication:

If you are using the Add Connection dialog box, after selecting the Use SQL Server Authentication radio button, after typing a username, you can also enter a password in the indicated text box:

If you are programmatically establishing the connection, 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 (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:

Private Sub Exercise_Load(ByVal sender As Object, _
                              ByVal e As System.EventArgs) _
                              Handles Me.Load
    Dim strConnection As String = "Server=(local);" & _
                           "Integrated Security=no;" & _
                           "User ID=wmessmann;PWD=$outh~@kotA"
    Dim connection As SqlConnection = New SqlConnection(strConnection)
End Sub

In some circumstances, you can use an empty password in which case you would assign an empty string to the password attribute.

Microsoft SQL Server ships with a few default databases. In Microsoft SQL Server Management Studio, the available databases and those you will create are listed in a node called Databases. To display the list of databases, you can expand the name of the server and expand the Databases node. If you are not trying to connect to a database, you do not need to locate and click any. If you are attempting to connect to a specific database, in Microsoft SQL Server Management Studio, you can simply click the desired database.

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:

Private Sub Exercise_Load(ByVal sender As Object, _
                              ByVal e As System.EventArgs) _
                              Handles Me.Load
    Dim connection As SqlConnection = _
	New SqlConnection("Server=(local);Database=;")
End Sub

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

Private Sub Exercise_Load(ByVal sender As Object, _
                              ByVal e As System.EventArgs) _
                              Handles Me.Load
    Dim strConnection As String = "Server=(local);Initial Catalog='exercise1';"
    Dim connection As SqlConnection = New SqlConnection(strConnection)
End Sub

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.

If you are "physically" connecting to a server or a database using the Microsoft SQL Server Management Studio, the steps we have described so far allow you to open the connection.

If you are creating the connection using the Add Connection dialog box, after selecting the options, you can click OK.

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

Public Sub Open

Here is an example of calling it:

Private Sub Exercise_Load(ByVal sender As Object, _
                              ByVal e As System.EventArgs) _
                              Handles Me.Load
    Dim connection As SqlConnection = _
        New SqlConnection("Server=(local);Initial Catalog='exercise1';")
    connection.Open()
End Sub

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 in Microsoft SQL Server Management Studio, to close the connection, you can simply close the window as an application.

If you are working in Microsoft Visual Studio, to close a connection, you can right-click it in the Server Explorer and click Close Connection:

In this case, the Close Connection menu item would become disabled, indicating that the connection is not available. To re-establish it, you can simply right-click that connection and click Modify Connection.

If you are working from a SqlConnection object, to close a connection, you can call the Close() method of the SqlConnection class. Its syntax is:

Public Sub Close

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

Private Sub Exercise_Load(ByVal sender As Object, _
                              ByVal e As System.EventArgs) _
                              Handles Me.Load
        Dim connection As SqlConnection = _
        New SqlConnection("Server=(local);Initial Catalog='exercise1';")
        connection.Open()

        ' Blah Blah Blah

        connection.Close()
End Sub

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. Fortunately, the Visual Basic language provides an alternative that can close the connection for it. To assist you with this, you can use the Using operator as follows:

Private Sub Exercise_Load(ByVal sender As Object, _
                              ByVal e As System.EventArgs) _
                              Handles Me.Load
    Using connection As SqlConnection = _
        New SqlConnection("Data Source=(local);Integrated Security=yes")
            connection.Open()
    End Using
End Sub

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.

We have learned different ways of connecting to a server. 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:

Private Sub Exercise_Load(ByVal sender As Object, _
                              ByVal e As System.EventArgs) _
                              Handles Me.Load
    Dim CommandToExecute As SqlCommand = New SqlCommand()
    Dim strCommandToExecute As String = "Blah Blah Blah"

    CommandToExecute.CommandText = strCommandToExecute
End Sub

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:

Private Sub Exercise_Load(ByVal sender As Object, _
                              ByVal e As System.EventArgs) _
                              Handles Me.Load
        Dim strConnection As String = "Server=(local);" & _
                           "Integrated Security=no;" & _
                           "User ID=sa;PWD=$outh~@kotA"
        Dim connection As SqlConnection = New SqlConnection(strConnection)

        Dim CommandToExecute As SqlCommand = New SqlCommand()
        Dim strCommandToExecute As String = "Blah Blah Blah"

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

        connection.Close()
End Sub

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 Sub New(cmdText As String)

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:

Private Sub Exercise_Load(ByVal sender As Object, _
                              ByVal e As System.EventArgs) _
                              Handles Me.Load
        Dim strConnection As String = "Server=(local);" & _
                           "Integrated Security=no;" & _
                           "User ID=sa;PWD=$outh~@kotA"
        Dim connection As SqlConnection = New SqlConnection(strConnection)

        Dim CommandToExecute As SqlCommand = New SqlCommand("Blah Blah Blah")

        connection.Open()
        CommandToExecute.Connection = connection
        connection.Close()
End Sub

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 Sub New(cmdText As String, connection As SqlConnection)

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

Private Sub Exercise_Load(ByVal sender As Object, _
                              ByVal e As System.EventArgs) _
                              Handles Me.Load
    Dim connection As SqlConnection = New SqlConnection("Server=(local);" & _
                                        "Integrated Security=True;")

    Dim CommandToExecute As SqlCommand = New SqlCommand("Blah Blah Blah", connection)

    connection.Open()
    CommandToExecute.Connection = connection
    connection.Close()
End Sub

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 Overrides Function ExecuteNonQuery As Integer

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 will perform are 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, 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 Function ExecuteReader As SqlDataReader

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 Overrides Function Read As Boolean

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