Interacting with SQL Server in Code

Communicating with a database yourself is definitely more work than dragging-and-dropping data sources, but whoever said programming was a cake walk?

Building the Connection String

The first step on the road to the data-controlling lifestyle is to connect to the database using a connection string. If you have used ADO, then you are already familiar with the connection strings used in ADO.NET, for they are generally the same. You probably also know that it is through connection strings that Microsoft keeps a tight rein on Windows developers. It's not that they are complex; they are nothing more than strings of semicolon-separated parameters. But the parameters to include, and their exact format, are the stuff of legend. The MSDN documentation included with Visual Studio does provide some examples of connection strings, but not much detail. A third-party resource, http://www.connectionstrings.com, also provides numerous examples of valid connection string formats.

The connection string we will use to connect to the Library database, fortunately, isn't overly complex. If you use your Microsoft Windows login to connect to the database, the following string will meet your needs (as one unbroken line):

Data Source=instance_name;Initial Catalog=Library; Integrated Security=true 

where instance_name is replaced by the name of your SQL Server instance or data source. The connection string we built visually earlier used "MYSYSTEM\SQLEXPRESS" for its data source name.

To use SQL Server user IDs and passwords, try this format.

Data Source=instance_name;Initial Catalog=Library; User ID=sa;Password=xyz 

Of course, replace the user ID (sa) and password (xyz) with your own settings. If you want to include MARS support in your connection, add another semicolon-delimited component:

MultipleActiveResultSets=true 

Note

There are other connection string options that let you connect to a SQL Server Express (SSE) database file directly, alter the "user instancing" method (often used with ClickOnce-deployed databases), and make other adjustments. Although it is somewhat scattered about, you can find these options documented in the MSDN documentation that comes with Visual Studio.

Establishing the Connection

Use the connection string to create a SqlConnection object, and then open the connection.

' ----- Assumes: '         Imports System.Data Dim libraryDB As New SqlClient.SqlConnection( _    "Data Source=MYSYSTEM\SQLEXPRESS;" & _    "Initial Catalog=Library;Integrated Security=true") libraryDB.Open() 

Make sure you replace "MYSYSTEM" with your own system's name. This entire block of code sure seems a lot easier to me than those 10 or 15 steps you had to follow earlier when setting up the connection through Visual Studio.

Using SQL Statements

Once the connection is open, you can issue SELECT, INSERT, UPDATE, DELETE, or any other DML or DDL statement accepted by the database. A SqlCommand object prepares your SQL statement for use by the open connection. Here's a statement that returns the description for entry number 1 in the Activity table.

SELECT FullName FROM Activity WHERE ID = 1 

Creating a SqlCommand object that wraps around this statement is easy. The constructor for the SqlCommand object takes a SQL statement, plus a SqlConnection object.

Dim sqlStatement As New SqlClient.SqlCommand( _    "SELECT FullName FROM Activity WHERE ID = 1", libraryDB) 

Processing the Results

The only thing left to do is to pass the SQL statement to the database, via the connection, and retrieve the results as a SqlDataReader object. Once we get the data, process each record using that object's Read method. You access individual fields by name through the default Item collection.

Dim sqlResults As SqlClient.SqlDataReader = _    sqlStatement.ExecuteReader() sqlResults.Read() MsgBox(CStr(sqlResults.Item("FullName"))) ' ----- Since Item is the default property, this works too... MsgBox(CStr(sqlResults("FullName"))) ' ----- This shortened syntax also works... MsgBox(CStr(sqlResults!FullName)) 

Taking all these blocks of code together displays the message shown in Figure 10-8.

Figure 10-8. Basic data retrieved from a database

When you're finished, make sure that you close all of the connections you opened.

sqlResults.Close() libraryDB.Close() 

Modifying Data

Making changes to database tables is coded just like data retrieval, but no SqlDataReader is needed. Instead of using the ExecuteReader method, use the ExecuteNonQuery method, which returns no results.

Dim sqlStatement As New SqlClient.SqlCommand( _    "UPDATE Activity SET FullName = 'Sleeps all day'" & _    " WHERE ID = 1", libraryDB) sqlStatement.ExecuteNonQuery() 

SQL Server 2005 has a convenient new feature that will return a single field from a new record created via an INSERT statement. If you look back at the Library Project's database design, you will see that the ID fields in many of the tables are generated automatically. Traditionally, if you wanted to immediately retrieve the ID field for a new record, you first had to INSERT the record, and then perform a separate SELECT statement, returning the new record's ID field.

INSERT INTO CodeSeries (FullName)    VALUES ('Children''s Books') SELECT ID FROM CodeSeries   WHERE FullName = 'Children''s Books' 

A new OUTPUT INSERTED clause in SQL Server 2005 combines both of these statements into a single action.

INSERT INTO CodeSeries (FullName)    OUTPUT INSERTED.ID    VALUES ('Children''s Books') 

When the INSERT is complete, SQL Server returns the ID field as a result set, just as if you had issued a separate SELECT statement. The SqlCommand's ExecuteScalar method is a simple way to retrieve a single value from a SQL statement.

sqlStatement = New SqlClient.SqlCommand( _    "INSERT INTO CodeSeries (FullName) " & _    "OUTPUT INSERTED.ID VALUES ('Children''s Books')", _    libraryDB) Dim newID As Integer = CInt(sqlStatement.ExecuteScalar())