Home

Characteristics of a Database Connection

   

Introduction

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.Data;
using System.Drawing;
using System.Windows.Forms;
using System.Data.SqlClient;
public class Exercise : Form
{
    public Exercise()
    {
        InitializeComponent();
    }

    void InitializeComponent()
    {
    }
}

public class Program
{
    static int Main()
    {
        System.Windows.Forms.Application.Run(new Exercise());
        return 0;
    }
}

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 SqlConnection(string connectionString);

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

public class Exercise : Form
{
    public Exercise()
    {
        InitializeComponent();
    }

    void InitializeComponent()
    {
        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. This would be done as follows:

void InitializeComponent()
{
    string strConnection = "Something";
    SqlConnection connection = new SqlConnection(strConnection);
}

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:

void InitializeComponent()
{
    string strConnection = "Something";
    SqlConnection connection = new SqlConnection();

    connection.ConnectionString = strConnection;
}

The Attributes of a Connection String

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 (even though you are working on a C# application). This whole ensemble is either passed as a string to the second constructor:

void InitializeComponent()
{
    SqlConnection connection =
        new SqlConnection("Key1=Value1;Key2=Value2;Key_n=Value_n");
}

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

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

    connection.ConnectionString = strConnection;
}

How you create these attributes depends on the type of computer you are connecting to, whether you are connecting to a database, what level the 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.

The Source of Data

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

Connect to Server: Selecting a Server

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

Add Connection

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 SQL Server is installed, the computer attribute can be identified as (local). Here is an example:

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

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

Add Connection

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

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

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.

Security

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:

Connect to Server

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 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 Windows XP Home Edition (that, by default, doesn't 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:

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

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.

The Username

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:

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

The Password

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:

Add Connection

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:

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.

The Database

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 don't 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.

Add Connection

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.

Additional Attributes

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.

   

Previous Copyright © 2007-2009 FunctionX, Inc. Next