Connecting to a Database via JDBC Architecture

JDBC

• It is an API Specification developed by Sun MicroSystems

• It is a Core part of the standard Java Platform.

• It defines a uniform interface for accessing different relational databases .

• It is included in the standard JDK.

Accessing a Database [ Connecting to a Database via JDBC Architecture ]

1. Register the JDBC driver with the DriverManager

Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

2. Establish a Database Connection using Database URL jdbc::

Connection con = DriverManager.getConnection("jdbc:odbc:dsname");

3. Execute an SQL Statement

i. Create a Statement object using Connection object's createStatement() method.

Statement stmt = con.createStatement();

ii. Query can be executed by using the Statement object's executeQuery()
method. It returns a ResultSet object that encapsulates the retrieved
data.

ResultSet rs = stmt.executeQuery("Query");

4. Process the Results

i. ResultSet object's next()& previous() methods can be used to
iterate through the rows of the result set.

ii. ResultSet object's getString() method is used to extract the value of
specified fields.

while(rs.next()){
System.out.println(rs.getString("fieldName"));
}

5. Close the Database Connection.

Database Connections should be closed after completing transactions.

rs.close();
stmt.close();
con.close();

[Written on: Monday, August 22, 2005 3:02:34 PM]

ADO.NET Database connections: SQLClient, OleDb, Odbc

ADO.NET
ADO.NET is a set of classes that expose data access services to the .NET programmer. ADO.NET provides a rich set of components for creating distributed, data-sharing applications. It is an integral part of the .NET Framework, providing access to relational data, XML, and application data. ADO.NET supports a variety of development needs, including the creation of front-end database clients and middle-tier business objects used by applications, tools, languages, or Internet browsers.

---

Code Samples of ADO.NET application that retrieves data from a database and returns it to the console.

SqlClient

using System;
using System.Data;
using System.Data.SqlClient;

class Sample
{
public static void Main()
{
SqlConnection nwindConn = new SqlConnection("Data Source=localhost;Integrated Security=SSPI;Initial Catalog=northwind");

SqlCommand catCMD = nwindConn.CreateCommand();
catCMD.CommandText = "SELECT CategoryID, CategoryName FROM Categories";

nwindConn.Open();

SqlDataReader myReader = catCMD.ExecuteReader();

while (myReader.Read())
{
Console.WriteLine("\t{0}\t{1}", myReader.GetInt32(0), myReader.GetString(1));
}

myReader.Close();
nwindConn.Close();
}
}

OleDb

using System;
using System.Data;
using System.Data.OleDb;

class Sample
{
public static void Main()
{
OleDbConnection nwindConn = new OleDbConnection("Provider=SQLOLEDB;Data Source=localhost;Integrated Security=SSPI;Initial Catalog=northwind");

OleDbCommand catCMD = nwindConn.CreateCommand();
catCMD.CommandText = "SELECT CategoryID, CategoryName FROM Categories";

nwindConn.Open();

OleDbDataReader myReader = catCMD.ExecuteReader();

while (myReader.Read())
{
Console.WriteLine("\t{0}\t{1}", myReader.GetInt32(0), myReader.GetString(1));
}

myReader.Close();
nwindConn.Close();
}
}

Odbc

using System;
using System.Data;
using System.Data.Odbc;

class Sample
{
public static void Main()
{
OdbcConnection nwindConn = new OdbcConnection("Driver={SQL Server};Server=localhost;" +
"Trusted_Connection=yes;Database=northwind");

OdbcCommand catCMD = new OdbcCommand("SELECT CategoryID, CategoryName FROM Categories", nwindConn);

nwindConn.Open();

OdbcDataReader myReader = catCMD.ExecuteReader();

while (myReader.Read())
{
Console.WriteLine("\t{0}\t{1}", myReader.GetInt32(0), myReader.GetString(1));
}

myReader.Close();
nwindConn.Close();
}
}