Transcript ADO.NET connections

ADO.NET connections
Connecting to SQL Server and
Oracle
ADO.NET connections
1
Objectives
• The objectives of this slide show are
– to show different connection strings using very
simple examples
• This slide show uses the connected
architecture
– no disconnected DataSets
• All examples are simple console programs
– no GUI, no ASP.NET
– focus on the important aspect: connections
ADO.NET connections
2
ADO.NET in brief
• Connection object
– created using a connection string
– models a physical connection to the database
• Command object
– created using a connection object
– models a SQL statement
• Reader object
– created using a command object
– models the result of an SQL statement
• select or the like
ADO.NET connections
3
Connection strings
• Connection string holds information on
– data source (server)
– database name
– user name and password
• The structure of the connection string depends
on
– the database
– the ADO.NET package used
• Further information
– www.connectionstrings.com is a good source
ADO.NET connections
4
Trusted connections
• Some DBMS' can use the operating
system username and password to
connect to a database.
• Most common in Microsoft technologies
– Windows + SQL Server
• … integrated security=SSPI; …
ADO.NET connections
5
SQL Server connection
• Package System.Data.SqlClient
• Classes
– SqlConnection
– SqlCommand
– SqlDataReader
• Example
– SqlServerTest.cs
ADO.NET connections
6
Oracle connection
• Package System.Data.OracleClient
– in .NET framework 1.1
– must be activated during compile
• csc /r:System.Data.OracleClient.dll filename.cs
• example: compileOracle.bat
• Classes
– OracleConnection
– OracleCommand
– OracleDataReader
• Example
– OracleTest.cs
ADO.NET connections
7
OLE DB connections
• OLE
– Object Linking and Embedding
– General Microsoft component technology
• OLE DB
– General way of using databases in ADO.NET
• Not specific to any DBMS
– Tries to replace ODBC
– Depending on the connection string a program can
connect to any database using OLE DD
– Examples
• SqlServerOleDbTest.cs
• OracleOleDbTest.cs
ADO.NET connections
8
ADO.NET interfaces
• The program can be made a bit more flexible
(i.e. easier to change DBMS) using interfaces
instead of classes
• Package System.Data
• Interfaces
– IDbConnection
– IDbCommand
– IDataReader
• The connection object must be created using a
real class (i.e. not an interface)
• Example
– SqlServerTestInterface
ADO.NET connections
9
Handling exceptions
• A database connection is a scarce resource
– a database connection must be closed (handed back
to the connection pool) after use - not matter what if
we have an exception or not
• Code pattern
SomeConnection con = null;
try {
con = new SomeConnection(…);
use the connection
}
finally {
if (con != null) { con.close; }
}
ADO.NET connections
10