Connecting to Various Data Sources

for RuBoard

It would be impossible to include code to connect to all the possible kinds of databases. The next section shows you how to connect to Microsoft SQL Server versions 7.0 and up using the managed SQL provider in the System.Data.SqlClient namespace and also how to connect to various OLE DB data sources using the System.Data.OleDb namespace.

SQL Server

When using the managed SQL provider, do not specify the Provider keyword in your connection string. In fact, the only connection string properties you're likely to need are the ones in Table 5.1.

Table 5.1. SQL Connection String Options
Connection String Property Description
Initial Catalog Specifies the database you're connecting to
Server Specifies the server you're connecting to
User ID Specifies the user ID you're using to make the connection
Password Specifies the password for the user you are using to connect to the database

Listings 5.1 and 5.2 show how to connect to a Microsoft SQL database and open and close a connection.

Listing 5.1 Connecting to SQL Server in C#
 SqlConnection conn = new          SqlConnection("Initial Catalog=Northwind;Server=(local);UID=sa;PWD="); conn.Open(); conn.Close(); 
Listing 5.2 Connecting to SQL Server in Visual Basic .NET
 Dim conn as SqlConnection = New _           SqlConnection("Initial Catalog=Northwind;Server=(local);UID=sa;PWD=") conn.Open() conn.Close() 
graphics/alarm.gif

Don't forget to include or import the System.Data.SqlClient namespace into your project or Web form before attempting to use its objects, including the Connection object.


OLE DB Data Sources

The key to connecting to an OLE DB data source is using a properly formed connection string. OLE DB providers exist for almost every type of data imaginable. The Internet provides a wealth of connection string information for various OLE DB providers.

The following page contains a wealth of OLE DB connection strings: http://www.able-consulting.com/ADO_Conn.htm. However, if you do not find one for your data source, there are also hundreds of other well-written lists of connection strings on the Internet; a quick Internet search should enable you to find one that will allow you to connect to your database.

The following sections show you how to use ADO.NET to connect to some common OLE DB providers: a Microsoft Access database, as well as an Oracle database.

Microsoft Access Database

The code in Listings 5.3 and 5.4 shows how to connect to a Microsoft Access database using the Microsoft Jet OLE DB provider.

graphics/alarm.gif

The backslash character (" \ ") is a special character when used in a string in C#. Therefore, if you need to enter a single backslash, as in the path in Listing 5.3, you must use two backslashes together, which C# interprets as a single backslash. This is called "escaping" the special character.


Listing 5.3 Connecting to a Microsoft Access Database in C#
 OleDbConnection conn = new OleDbConnection(                          "Provider=Microsoft.Jet.OLEDB.4.0;" +                          "Data Source=c:\nwind.mdb;" +                          "User Id=admin;" +                          "Password=;" ); conn.Open(); conn.Close(); 
Listing 5.4 Connecting to a Microsoft Access Database in Visual Basic .NET
 Dim conn as OleDbConnection = New OleDbConnection( _                          "Provider=Microsoft.Jet.OLEDB.4.0;" + _                          "Data Source=c:\nwind.mdb;" + _                          "User Id=admin;" + _                          "Password=;" ) conn.Open() conn.Close() 
Oracle Database

Connecting to an Oracle database is straightforward. The code in Listings 5.5 and 5.6 will connect to an Oracle database using the OLE DB provider provided by Microsoft.

Listing 5.5 Connecting to an Oracle Database in C#
 OleDbConnection conn = new OleDbConnection( _                          "Provider=OraOLEDB.Oracle" + _                          "Data Source=  DataBasename  " + _                          "User Id=  username;  " + _                          "Password=  password;  " ); conn.Open(); conn.Close(); 
Listing 5.6 Connecting to an Oracle Database in Visual Basic .NET
 Dim conn as OleDbConnection = New OleDbConnection( _                          "Provider=OraOLEDB.Oracle" + _                          "Data Source=  DataBasename  " + _                          "User Id=  username;  " + _                          "Password=  password;  " ) conn.Open() conn.Close() 
for RuBoard


Sams Teach Yourself ADO. NET in 24 Hours
Sams Teach Yourself ADO.NET in 24 Hours
ISBN: 0672323834
EAN: 2147483647
Year: 2002
Pages: 237

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net