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.
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.
|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.
SqlConnection conn = new SqlConnection("Initial Catalog=Northwind;Server=(local);UID=sa;PWD="); conn.Open(); conn.Close();
Dim conn as SqlConnection = New _ SqlConnection("Initial Catalog=Northwind;Server=(local);UID=sa;PWD=") conn.Open() conn.Close()
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.
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.
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.
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.
OleDbConnection conn = new OleDbConnection( "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=c:\nwind.mdb;" + "User Id=admin;" + "Password=;" ); conn.Open(); conn.Close();
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()
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.
OleDbConnection conn = new OleDbConnection( _ "Provider=OraOLEDB.Oracle" + _ "Data Source= DataBasename " + _ "User Id= username; " + _ "Password= password; " ); conn.Open(); conn.Close();
Dim conn as OleDbConnection = New OleDbConnection( _ "Provider=OraOLEDB.Oracle" + _ "Data Source= DataBasename " + _ "User Id= username; " + _ "Password= password; " ) conn.Open() conn.Close()