Connecting to SQL Server Express

To connect to SQL Server Express in an ASP.NET application, you can use the SqlConnection class to create a connection to the database. Here’s an example of how to do this:

Add a reference to the System.Data.SqlClient namespace at the top of your code file:

using System.Data.SqlClient;

Create a connection string that specifies the server name, database name, and any other necessary connection parameters. For example:

string connectionString = “Data Source=SERVERNAME\\SQLEXPRESS;Initial Catalog=DATABASENAME;Integrated Security=True;”;

Replace SERVERNAME and DATABASENAME with the appropriate values for your SQL Server Express instance.

Create a new instance of the SqlConnection class and pass in the connection string:

SqlConnection connection = new SqlConnection(connectionString);

Open the connection:

connection.Open();

Use the connection object to execute SQL commands against the database, such as queries or updates:

SqlCommand command = new SqlCommand(“SELECT * FROM TableName”, connection);

SqlDataReader reader = command.ExecuteReader();

while (reader.Read())

{

// do something with the data

}

Close the connection when you’re finished:

connection.Close();

Note that in this example, I’ve used Integrated Security=True in the connection string, which means that the connection will use Windows Authentication to log in to the database. If you need to use SQL Server authentication instead, you’ll need to include a User ID and Password in the connection string.

Checking whether SQLExpress is running

This section assumes that you installed SQL Server 2005 Express (SQLExpress) on your workstation. Installation is covered in Chapter 3.

Before you try connecting to SQL Server Express it helps to know whether the SQL software is running. Follow these steps to use a command line utility to check your system for a running instance of SQL Express:

Open a command prompt:
If you’re using Windows XP, choose Start Run; enter cmd and press Enter.
If you’re using Windows Vista, choose Start, enter cmd in the search box, and press Ctrl+Shift+Enter.
At the command prompt, type the following command:
sqlcmd -S(local)\SQLExpress
Press Enter.
If SQLExpress is running, the program responds with a prompt that looks like 1>
If SQLExpress isn’t running, the program reports a connection error and quits.
Type exit and press Enter to exit the sqlcmd utility and then type exit and press Enter again to close the command line utility.