OleDbConnection Object

RSS

An OleDbConnection object represents an open connection to a data source (such as a database). The OleDbConnection object uses the OLE DB provider specified in the connection string to connect to the data source. This allows for a variety of data sources to be used such as MS Access, Sql Server, Oracle, Excel file, text files and many more. Use the ODBCConnection object to connect to a MySql database.

Table of Contents [Hide/Show]

Connection String
Example Connection Strings
Constructors
   OleDbConnection
Instance Methods
   Close
   Execute
   GetValue ( String )
   GetValue ( String, Object )
   GetDataTable
Text File Example
See Also

Connection String

The connection string is an OLE DB provider connection string that includes the data source name, and other parameters needed to establish the initial connection.

You can find a variety of OLE DB connection string examples at connectionstrings.com.

Example Connection Strings

MS Access Standard Security

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\mydatabasefile.mdb;User id=myuserid;Password=mypassword;

Sql Server Standard

Driver=SQLOLEDB;Data Source=ServerName;Initial Catalog=DataBaseName;User id=UserName;Password=Secret;

Sql Server Trusted

Driver=SQLOLEDB;Data Source=ServerName;Initial Catalog=DataBaseName;Integrated Security=SSPI;

Constructors

OleDbConnection

Initializes a new OleDbConnection instance with the specified OLE DB connection string.

Syntax:

String( String connectionString )

Parameters:

connectionString: The connection used to open the data source.

Example:


db = new OleDbConnection( "Provider=OraOLEDB.Oracle;Data Source=myoracle;User Id=myuserid;Password=mypassword;" );

Instance Methods

Close

Closes the connection to the data source.

Syntax:

void Close( )

Example:


db.Close();

Execute

Executes a SQL commands (such as SQL INSERT, DELETE, UPDATE, and SET statements) against the connection and returns the number of rows affected.

Syntax:

Number Execute(String sqlStatement)

Parameters:

sqlStatement: The text of the sql statement.

Return Value

The number of rows affected.

Example:


n = db.Execute("delete from mytable where col1=2");

GetValue ( String )

Executes the query and returns the first column of the first row in the result set by the query. Additional columns and rows are ignored.

Syntax:

Object GetValue(String sqlStatement)

Parameters:

sqlStatement: The text of the sql query.

Return Value

The first column of the first row in the result set, or a null reference if the result set is empty.

Example:


n = db.GetValue("select count(col1) from mytable where col1=2");

GetValue ( String, Object )

Executes the query and returns the first column of the first row in the result set by the query if any, otherwise returns the default value. Additional columns and rows are ignored.

Syntax:

Object GetValue(String sqlStatement, Object defaultValue )

Parameters:

sqlStatement: The text of the sql query.

defaultValue: The value to return if the result set is empty.

Return Value

The first column of the first row in the result set, or the default value if the result set is empty.

Example:


n = db.GetValue("select max(col1) from mytable", 0);

GetDataTable

Executes the SQl SELECT query and returns a DataTable instance containing the result set.

Syntax:

DataTable GetDataTable(String sqlStatement)

Parameters:

sqlStatement: A string that is an SQL SELECT statement or stored procedure.

Return Value

The number of rows affected.

Example:


dt = db.GetDataTable("select * from mytable");

Text File Example

Typically the OleDbConnection is used with a database, but it can be used with delimited text files as well.

This example shows how to open a delimited text file by using a OleDbConnection instance (using the Microsoft Jet Provider's Text IIsam driver).

Create a text file called textfile.txt that contains data similar to the following:

"F1","F2","F3"
"one", "two", "three"
"1a","2b", "3c"
1,2,3

Test the following script. (set the path in the data source below)


// Open the connection
db = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" +
     "Data Source=e:\\myDbPath\\;" +
     "Extended Properties=\"text;HDR=YES;FMT=Delimited;\"");

// Retrieve a single value
f1 = db.GetValue("select max(F1) from junk.txt");

// Retrieve a data table
dt = db.GetDataTable("select * from junk.txt");

// Get a value from row 0, column "F2"
ret = dt.GetFieldString(0, "F2");

// Close the connection
db.close();