473,320 Members | 1,600 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

ADO.NET CONNECTIONS

ADO.Net
Making the Connection: Accessing Your Database using ADO.NET
Data Access Namespaces
Within the .NET framework class library (FCL) version 1.1, there are
five third-level namespaces that provide access to an underlying
database. These are System.Data.SqlClient, System.Data.SqlServerCE,
System.Data.OracleClient, System.Data.OleDb, and System.Data.Odbc. As
their names imply, these are used to connect to MS SQL Server, MS SQL
Server Compact Edition, Oracle, as well as data sources supported by
OLE DB Providers and ODBC (open database connectivity) drivers,
respectively.

While the classes and types in the System.Data.OleDb and
System.Data.Odbc namespaces permit you to connect to almost any data
source, they have one drawback. Specifically, they require that the
associated OLE DB Provider or ODBC driver be installed on each
workstation. For some developers this does not pose much of a problem.
For others, however, the additional installation requirements may make
these alternatives unattractive.

In addition to the namespaces in the FCL, many database vendors provide
their own ADO.NET drivers. For example, Oracle publishes the Oracle
Data Provider for .NET (ODP.NET) in the Oracle.DataAccess.Client
namespace, Advantage Database Server supplies a driver in the
Advantage.Data.Provider namespace, and IBM offers a .NET Data Provider
in the IBM.Data.DB2 namespace. In many cases, the vendor-specific
drivers offer additional features over the FCL native drivers. As a
result, it is often worthwhile to compare the performance of the
vendor-specific driver to the OLE DB or ODBC alternatives, so long as
deployment issues are not an issue.

The IDbConnection Interface
Regardless of which namespace you are using, the concrete classes that
you use to connect to a database implement the IDbConnection interface.
This interface, which is defined in the System.Data namespace, has few
methods and properties, all of which are self-explanatory.

There are five public methods in this interface. The Open and Close
methods are implemented to open and close a connection, respectively.
Unlike other data access options that you might have used before, with
ADO.NET it is important that each call to Open be associated with a
corresponding call to Close. For most ADO.NET connections, failure to
call Close may unnecessarily consume resources on your server.

BeginTransaction is implemented to initiate a transaction, while
ChangeDatabase is implemented to change the database the connection is
associated with. Finally, CreateCommand is implemented to return an
appropriate instance of a class that implements IDbCommand.

In addition, there are four public properties in the IDbConnection
interface. ConnectionString is used to get or set the parameters that
will be used to connect to a database. Most IDbConnection implementing
classes permit the connection string to also be set through a parameter
of the class constructor. ConnectionTimeout is used to set or get the
number of seconds after which an attempt to connect will be aborted,
and Database is used to read the name of the database that the
connection will use.

Finally, State is used to read the status of the connection. State is a
ConnectionState property, which is an enumeration. Valid
ConnectionState values include ConnectionState.Open,
ConnectionState.Closed, ConnectionState.Connecting,
ConnectionState.Executing, ConnectionState.Fetching, and
ConnectionState.Broken.

Connecting to Data
Connecting to a database is actually straightforward. Using an instance
of an IDbConnection-implementing class, you define the connection
string and then call the Open method. As mentioned in the preceding
section, the connection string can be set using the ConnectionString
property, but most developers prefer to pass the connection string as a
parameter to the connection constructor.

The connection string consists of a list of name/value pairs that
define how to connect to a database. When a connection string consists
of more than one name/value pair, and most require more than one, the
parameters are separated by semicolons. At a minimum, the connection
string will define on which server the database is running (for remote
database servers), which database to use, and often a user name and
password with data access privileges.

Each IDbConnection-implementing class defines its own connection string
parameters. If you are using one of the five connection-types defined
in the FCL version 1.1, you can find the connection string parameters
in the .NET Framework SDK Documentation. If you are using some other
namespace, you should refer to the documentation provided by the vendor
who publishes that namespace.

The following sections demonstrate how to connect to a variety of
databases using the IDbConnection-implementing classes in the
System.Data.SQLClient, System.Data.OleDb, and System.Data.Odbc
namespaces. In each of these examples a connection is used to populate
a DataTable in a DataSet, and that data is displayed in a DataGrid.

In addition to the connection object, each of these examples employ a
DataSet and a class that implements IDbDataAdapter. These classes will
be discussed in a later article in this series. Consequently, the
following discussions will not go into detail about what or how these
classes are being used.

Connecting to data using BDP.NET is demonstrated at the end of this
article.

Connecting to SQL Server
You connect to SQL server using a SqlConnection instance. The following
code segment shows the variable declarations of the SqlConnectin
object, as well as the supporting classes in C#.

public SqlConnection connection;
public SqlDataAdapter dataadapter;
public DataSet dataset;When calling the constructor of this class you
can pass the connection string as a parameter. This is demonstrated in
the following code segment.

connection = new SqlConnection("Persist Security Info=False;" +
"Integrated Security=SSPI;database=northwind;" +
"server=localhost;Connect Timeout=30");
connection.Open();
dataadapter = new SqlDataAdapter("select * from customers",
connection);
dataset = new DataSet();
dataadapter.Fill(dataset, "table1");
dataGrid1.DataSource = dataset;
dataGrid1.DataMember = "table1";Regardless of which connection class
you are using, you can typically can set the connection string using
the connection string property. An example of this is shown in the
following code segment.

connection = new SqlConnection();
connection.ConnectionString = "Persist Security Info=False;" +
"Integrated Security=SSPI;database=northwind;" +
"server=localhost;Connect Timeout=30";
connection.Open();
dataadapter = new SqlDataAdapter("select * from customers",
connection);
dataset = new DataSet();
dataadapter.Fill(dataset, "table1");
dataGrid1.DataSource = dataset;
dataGrid1.DataMember = "table1";The following Windows form shows the
contents of the customers table from the Northwind database displayed
in a DataGrid.

As you learned earlier, it is very important to close a connection once
you are done with it. In the preceding C# project, the following call
to close the connection can be found in the Closing event of the
Windows form to which this code is attached.

connection.Close();Using Delphi, the variable declarations look like
the following:

Connection: SqlConnection;
Adapter: SqlDataAdapter;
CustDataSet: DataSet;The following shows you how to establish the
connection, populate the DataTable, and display the data in the
DataGrid using Delphi:

Connection := SqlConnection.Create('Persist Security Info=False;' +
'Integrated Security=SSPI;database=northwind;' +
'server=localhost;Connect Timeout=30');
Connection.Open;
CustDataSet := DataSet.Create;
Adapter := SqlDataAdapter.Create('select * from customers',
Connection);
Adapter.Fill(CustDataSet, 'customers');
DataGrid1.DataSource := CustDataSet;
DataGrid1.DataMember := 'customers'For the remainder of this article,
the examples are shown using C#.

Connecting Using OLE DB Providers
OLE DB providers are COM-based classes that implement a standard set of
COM interfaces in order to connect to a data source. While non-database
data sources are supported by OLE DB providers, the following
discussion will only employ database-related OLE DB providers.

OLE DB providers are part of the Microsoft Data Access Component (MDAC)
framework. The .NET framework version 1.1 supports MDAC 2.6 or later.
However, MDAC 2.7 or later is recommended. And, at the time of this
writing, version 2.8 is the most recent.

The following code segment demonstrates how to create a connect to a
Microsoft Access database using an OleDbConnection.

connection = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; " +
"Data Source=C:\\Program Files\\Common Files\\Borland Shared\\Data" +
"\\dbdemos.mdb;Persist Security Info=False");
connection.Open();
dataadapter = new OleDbDataAdapter("select * from customer",
connection);
dataset = new DataSet();
dataadapter.Fill(dataset, "table1");
dataGrid1.DataSource = dataset;
dataGrid1.DataMember = "table1";In this case, a simple connection
string identifies the driver (Microsoft's Jet engine OLE DB provider)
and the database (a MS Access database that is installed along with
Borland Delphi, Kylix, and C++Builder products). All other parameters
that are associated with this driver will assume their default values.

Instead of using a connection string that identifies the various
parameters to use to connect to an OLE DB provider's data source, you
can instead reference a data link (*.udl) file. Data link files permit
you to maintain database connection information outside your
application, much like the feature provided by BDE configuration files
(idapi32.cfg).

The following example demonstrates how to connect to the same database
as above. However, this time, the dbdemos.udl file located in the
c:\program files\common files\system\ole db\data links folder is used.

connection =
new OleDbConnection("FILE NAME=" +
"C:\\Program Files\\Common Files\\System\\" +
"OLE DB\\Data Links\\dbdemos.udl");
connection.Open();
dataadapter = new OleDbDataAdapter("select * from customer",
connection);
dataset = new DataSet();
dataadapter.Fill(dataset, "table1");
dataGrid1.DataSource = dataset;
dataGrid1.DataMember = "table1";Note that the data link file is parsed
each time a connection is opened. If you application must establish
many different connections, the use of data link files may reduce
application performance.

Connecting Using ODBC
ODBC (open database connectivity) is a broadly supported, SQL-based
standard for accessing databases using the Windows operating systems.
To connect to a database using an ODBC driver you use the
OdbcConnection class, located in the System.Data.Odbc third-level
namespace.

You can make a connection to an ODBC database using either an ODBC
connection string, or a reference to a configured ODBC data source.

The following example demonstrates connecting to an MS Access database
using an ODBC connection string. MS Access is used in this example
because its connection string is simple, considering that the database
does not require a user name or password.

connection =
new OdbcConnection("Driver={Microsoft Access Driver (*.mdb)};"+
"DBQ=c:\\program files\\common files\\"+
"borland shared\\data\\dbdemos.mdb");
connection.Open();
dataadapter = new OdbcDataAdapter("select * from customer",
connection);
dataset = new DataSet();
dataadapter.Fill(dataset, "table1");
dataGrid1.DataSource = dataset;
dataGrid1.DataMember = "table1";As in the OLE DB provider example shown
earlier in this paper, the MS Access database that this connection
connects to is the one installed by recent Borland products, such as
C++Builder or Delphi. This database is pointed to by the DBQ parameter
of the connection string used to connect to the database.

So long as you have a configured user, file, or system data source name
(DSN), you can use the parameters of the data source name instead of a
lengthy connection string to connect to your database using an ODBC
driver. The following OdbcConnection constructor show how can connect
to Paradox and InterBase databases, given that you have the necessary
installed drivers, and have configured data source names 'Paradox
DataSource' and 'EasySoft IB ODBC,' respectively

connection = new OdbcConnection("DSN=Paradox DataSource");and

connection = new OdbcConnection("DSN=EasySoft IB DBC");

Jan 17 '07 #1
0 2541

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: Randell D. | last post by:
Folks, I currently connect to my db with PHP code that uses non-persistent connections. I've read that persistent connections can help performance since a connection to the db will use an...
3
by: Mudge | last post by:
Hi, My hosting provider only allows me to use 50 connections to my MySQL database that my Web site will use. I don't know what this 50 connections means exactly. Does this mean that only 50...
4
by: Angelos | last post by:
I get this error mysql_pconnect Too many connections ... every now and then. Does anyone knows where it comes from ? There are a lot of sites running on the server and all of them use the...
1
by: C Sharp beginner | last post by:
I'm sorry about this verbose posting. This is a follow-up to my yesterday's posting. Thanks William for your reply. I understand it is a good practice to open connections as late as possible and...
2
by: Bob | last post by:
We have a production web site that's data intensive (save user input to DB and query for displaying) with the ASP.NET app part on one W2K server and SQL 2000 DB on another W2K server. I have set...
17
by: Peter Proost | last post by:
Hi Group, I've got an interesting problem, I don't know if this is the right group but I think so because everything I've read about it so far says it's a .net problem. Here's the problem, we're...
4
by: elyob | last post by:
Not really tried going two ways at once, but I have an include_once connection to a mysql_database, now I need to retrieve info from a second mysql_database .. My mysql_connects are getting...
1
by: marcfischman | last post by:
Please help. I have a website running on a linux/apache/mysql/php server. I receive about 8,000-10,000 visitors a day with about 200,000 to 300,000 page views. The server is a RedHat Linux...
13
by: PRP | last post by:
Hi, Our DBA has complained about the large number of connections from the aspnet_wp process. We have multiple web applications deployed in different virtual directories. I read that the way...
5
by: Usman Jamil | last post by:
Hi I've a class that creates a connection to a database, gets and loop on a dataset given a query and then close the connection. When I use netstat viewer to see if there is any connection open...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.