Sams Teach Yourself .Net in 21 Days

By Jason Beres

• Table of Contents
  • Copyright
  • About the Author
  • About the Technical Editor
  • Acknowledgments
  • We Want to Hear from You
  • Introduction
  • Week 1: At a Glance
  • Day 1. Introduction to the Microsoft .NET Framework
  • Day 2. Introduction to Visual Studio .NET
  • Day 3. Writing Windows Forms Applications
  • Day 4. Deploying Windows Forms Applications
  • Day 5. Writing ASP.NET Applications
  • Day 6. Deploying ASP.NET Applications
  • Day 7. Exceptions, Debugging, and Tracing
  • Week 1. In Review
  • Week 2: At a Glance
  • Day 8. Core Language Concepts in Visual Basic .NET and C#
  • Day 9. Using Namespaces in .NET
  • Day 10. Accessing Data with ADO.NET
    • Looking Inside ADO.NET
    • Connecting to a Database
    • Using the Command Object and DataReaders
    • Using Windows Controls with DataReaders and Command Objects
    • Using DataAdapters and DataSets
    • Using Databinding with DataSets
    • Binding to a DataGrid Control
    • Summary
    • Q&A
    • Quiz
    • Exercises
  • Day 11. Understanding Visual Database Tools
  • Day 12. Accessing XML in .NET
  • Day 13. XML Web Services in .NET
  • Day 14. Components and .NET
  • Week 2. In Review
  • Week 3: At a Glance
  • Day 15. Writing International Applications
  • Day 16. Using Macros in Visual Studio .NET
  • Day 17. Automating Visual Studio .NET
  • Day 18. Using Crystal Reports
  • Day 19. Understanding Microsoft Application Center Test
  • Day 20. Using Visual SourceSafe
  • Day 21. Object Role Modeling with Visio
  • Week 3. In Review

Connecting to a Database

To work with any database, the first thing you must do is connect to it. In ADO.NET, you use the Connection object to connect to a database. When accessing SQL Server, the SqlConnection class of the System.Data.SqlClient namespace contains the connection object that you use to connect to a SQL Server database. The OleDbConnection class of the System.Data.OleDb namespace contains the connection object for OLE DB data sources.

When connecting to a database, you must specify the server that the database resides on, the database name, and the authentication information for the database. There are several variations of the connection string, so you might see different syntax based on who wrote the connection string code and the type of authentication used if the database is SQL Server.

The following code gives you an example of each of the following types of connection string options:

• Connecting to a Microsoft Access database using the OleDbConnection class
• Connecting to a .NET Framework software development kit (SDK) sample Microsoft data engine (MSDE) database with integrated security
• Connecting to a SQL Server database with integrated security
• Connecting to a SQL Server database passing the user ID and password in the connection string

' Connect to a Microsoft Access database using the
' System.Data.OleDb.OleDbConnection class
Dim strOleDb As String = _
    "Provider=Microsoft.Jet.OLEDB.4.0" _
    & "Data Source=..\Northwind.mdb"
Dim cnOleDb As New OleDbConnection()
cnOleDb.ConnectionString = strOleDb
cnOleDb.Open()

' Connect to the MSDE SDK database using  the
' System.Data.SqlClient.SqlConnection class
' with Integrated Security
Dim strMSDE As String = _
    "Server=(local)\NetSDK;DataBase=Northwind" _
    & "Integrated Security=SSPI"
Dim cnMSDE As New SqlConnection()
cnMSDE.ConnectionString = strMSDE
cnMSDE.Open()

' Connect to a local SQL Server database using  the
' System.Data.SqlClient.SqlConnection class
' with Integrated Security
Dim strSQL As String = _
    "Server=localhost;DataBase=Northwind" & _
    "Integrated Security=SSPI"
Dim cnSQL As New SqlConnection()
cnSQL.ConnectionString = strSQL
cnSQL.Open()

' Connect to a local SQL Server database using  the
' System.Data.SqlClient.SqlConnection class
' passing userid and password in the connection string
Dim strSQL1 As String = _
    "Server=localhost;DataBase=Northwind" & _
    "uid=sa;pwd=password"
Dim cnSQL1 As New SqlConnection()
cnSQL1.ConnectionString = strSQL1
cnSQL1.Open()


// Connect to a Microsoft Access database using the
// System.Data.OleDb.OleDbConnection class
string strOleDb;
strOleDb =
   @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=..\Northwind.mdb";
OleDbConnection cnOleDb = new OleDbConnection();
??? Author added parenthesis after OleDbConnection - Jason
cnOleDb.ConnectionString = strOleDb;
cnOleDb.Open();

// Connect to the MSDE SDK database using  the
// System.Data.SqlClient.SqlConnection class
// with Integrated Security
string strMSDE;
strMSDE =
   @"Server=(local)\NetSDK;DataBase=Northwind;Integrated Security=SSPI";
SqlConnection cnMSDE = new SqlConnection;()
??? Author: added parenthesis after SqlConnection
cnMSDE.ConnectionString = strMSDE;
cnMSDE.Open();

// Connect to a local SQL Server database using  the
// System.Data.SqlClient.SqlConnection class
// with Integrated Security
string strSQL;
strSQL =
   @"Server=localhost;DataBase=Northwind;Integrated Security=SSPI";
sqlConnection cnSQL = new SqlConnection();
??? Author: added parenthesis after SqlConnection
cnSQL.ConnectionString = strSQL;
cnSQL.Open();

// Connect to a local SQL Server database using  the
// System.Data.SqlClient.SqlConnection class
// passing userid and password in the connection string
string strSQL1;
strSQL1 = @"Server=localhost;DataBase=Northwind;uid=sa;pwd=";
SqlConnection cnSQL1 = new SqlConnection();
cnSQL1.ConnectionString = strSQL1;
cnSQL1.Open();

The connection string differences are not that noticeable. You can see the difference between OleDbConnection and SqlConnection is that with SQL Server, you pass the server name (in this case, localhost), the database name, and either the integrated security constant or the actual user ID and password. With the OleDbConnection, you specify the physical path to the database, and most importantly, the OLE DB provider name. Based on the type of OLE DB provider you're using, the connection string might vary. For example, using Microsoft Access, you must specify the physical path to the database. If you're using Oracle or SQL Server 6.5, you must specify a connection string similar to SQL 7.0 or SQL 2000.

Table 10.1 lists the common properties of a SqlConnection object and an OleDbConnection object.

Table 10.1. Properties of the Connection Object

The SqlConnection and OleDbConnection classes are overloaded, so you can also pass the connection string when you create the connection as the following Visual Basic .NET code demonstrates:

Dim cn As New SqlConnection( _
    "Server=localhost;DataBase=Northwind" _
    & "uid=sa;pwd=password")

Now that you can connect to a database, you can start writing some code that retrieves data.

Share This