s GETTING TO KNOW ADO.NET in VB.NET

Encoder Data Matrix ECC200 in VB.NET s GETTING TO KNOW ADO.NET

Summary

In this chapter, you saw why ADO.NET was developed and how it supersedes other data access technologies in .NET. We gave an overview of its architecture and then focused on one of its core components, the data provider. You built three simple examples to practice basic data provider use and experience the uniform way data access code is written, regardless of the data provider. Finally, we offered the opinion that conceptual clarity is the key to understanding and using both data providers and the rest of the ADO.NET API. Next, we ll study the details of ADO.NET, starting with connections.

efore you can do anything useful with a database, you need to establish a session with the database server. You do this with an object called a connection, which is an instance of a class that implements the System.Data.IDbConnection interface for a specific data provider. In this chapter, you ll use various data providers to establish connections and look at problems that may arise and how to solve them. In this chapter, we ll cover the following: Introducing data provider connection classes Connecting to SQL Server Express with SqlConnection Improving your use of connection objects Connecting to SQL Server Express with OleDbConnection

As you saw in 9, each data provider has its own namespace. Each has a connection class that implements the System.Data.IDbConnection interface. Table 10-1 summarizes the data providers supplied by Microsoft. Table 10-1. Data Provider Namespaces and Connection Classes

System.Data.Odbc System.Data.OleDb System.Data.OracleClient System.Data.SqlClient System.Data.SqlServerCe

As you can see, the names follow a convention, using Connection prefixed by an identifier for the data provider. Since all connection classes implement System.Data.IDbConnection, the use of each one is similar. Each has additional members that provide methods specific to a particular database. You used connections in 9. Let s take a closer look at one of them, SqlConnection, in the namespace System.Data.SqlClient.

In this example, you ll again connect to the SQL Server Management Studio Express (SSMSE) Northwind database.

You ll write a very simple program, just to open and check a connection: 1. In Visual Studio 2008, create a new Visual Basic Console Application project named 10. When Solution Explorer opens, save the solution. 2. Rename the 10 project to ConnectionSQL. Rename the Module1.vb file to ConnectionSql.vb, and replace the generated code with the code in Listing 10-1. Listing 10-1. ConnectionSql.vb Imports System Imports System.Data Imports System.Data.SqlClient Module ConnectionSQL Sub Main() 'Set up connection string Dim connstring As String connstring = "Data Source=.\sqlexpress;Integrated Security=True" 'Create connection Dim conn As SqlConnection = New SqlConnection(connstring) Try ' Open connection conn.Open() Console.WriteLine("Connection opened") Catch e As SqlException Console.WriteLine("Error: " &

e.ToString)

Finally ' Close connection conn.Close() Console.WriteLine("Connection closed.") End Try End Sub End Module

3. Run the application by pressing Ctrl+F5. If the connection is successful, you ll see the output in Figure 10-1.

Figure 10-1. Connecting and disconnecting If the connection failed, you ll see an error message, as shown in Figure 10-2. (You can get this by shutting down SSMSE first, entering net stop mssql$sqlexpress at a command prompt. If you try this, remember to restart it by typing net start mssql$sqlexpress.)

Figure 10-2. Error if connection failed while connecting to SQL Server Don t worry about the specifics of this message right now. Connections often fail for reasons that have nothing to do with your code. It may be because a server isn t started, as in this case, or because a password is wrong, or some other configuration problem exists. You ll soon look at common problems in establishing database connections.

Let s examine the code in Listing 10-1 to understand the steps in the connection process. First, you specify the ADO.NET and the SQL Server data provider namespaces, so you can use the simple names of their members: Imports System Imports System.Data Imports System.Data.SqlClient Then you create a connection string. A connection string consists of parameters in other words, key=value pairs separated by semicolons that specify connection information. Although some parameters are valid for all data providers, each data provider has specific parameters it will accept, so it s important to know what parameters are valid in a connection string for the data provider you re using. Dim connstring As String connstring = "Data Source=.\sqlexpress;Integrated Security=True" Let s briefly examine each of the connection string parameters in this example. The data source parameter specifies the SQL Server instance to which you want to connect: Data Source=.\sqlexpress In this statement, . (dot) represents the local server, and the name followed by the \ (backslash) represents the instance name running on the database server. So here you have an instance of SQL Server Express named sqlexpress running on the local server.