Toegang tot databases vanuit programma`s

Toegang tot databases vanuit programma's
Inhoud
•Microsoft en ODBC
•Java en JDBC
•C# (en andere Microsoft-talen) en ADO.NET
Situatie-schets
ODBC
•Open DataBase Connectivity
•een standaard databank toegang ontwikkeld door SQL Access group in 1992.
•doel: toegang mogelijk maken tot gelijk welke data, van gelijk welke toepassing, dus
onafhankelijk van gebruikte Database Management System (DBMS)
•via laag tussen applicatie en DBMS, een database driver, vertaalt de dataqueries van de
toepassing naar commando's die de DBMS verstaat.
•laat programma's toe om SQL requests te gebruiken, zonder de proprietary interfaces naar de
databases te kennen
•ODBC converteert de SQL request naar request dat individuele database systeem verstaat.
•voorwaarde: toepassing en DBMS: ODBC-compliant
–toepassing moet ODBC-commando's kunnen genereren
–DBMS moet deze kunnen beantwoorden
•native interface die native library oproept
–voor Windows-platformen is dit een DLL (eerste ODBC-product was van Microsoft, nu
ook versies voor UNIX, OS/2, Macintosh, …)
nadeel ODBC
•Databank moet geregistreerd zijn (Windows-niveau)
Java en JDBC
•Databank toegang vanuit Java (stand alone, servlet, enterprise, …)
•4 types:
–JDBC-ODBC bridge
–Java en native driver
–Java en middelware
–puur Java
•voordelen JDBC:
–eenvoudig
–zelfs over netwerk (enkel URL nodig)
–toegang tot metadata
JDBC-ODBC
•JDBC die ODBC-driver gebruikt voor connectie met databank
•driver vertaalt JDBC-oproepen naar ODBC-oproepen
•meestal als geen andere JDBC-driver beschikbaar voor specifieke database.
•Sun heeft JDBC-ODBC-brug geïncludeerd in JVM als tijdelijke oplossing, niet voor productie
en Sun raadt af om te gebruiken
Puur Java
•direct van client naar DBMSserver
•puur Java voor database middleware:
–JDBC  middleware protocol
–middleware protocol  DBMS protocol
•native API
–JDBC  DBMS protocol
–vraagt binaire code op elke client
Java: werkwijze
•laad de juiste driver (registreert deze automatisch bij de DriverManager) (via
Class.forName(DriverClassNaam))
•maak een connectie via Connection con =
DriverManager.getConnection(dbURL,naam,paswoord);
•maak een statement-object via Statement st = con.createStatement();
•voer queries uit
•sluit connectie via con.close()
Driver en connectie
Driver class
start van dbURL
Database
sun.jdbc.odbc.JdbcOdbcDriver
jdbc:odbc:
brug naar ODBC (in
JDK)
postgresql.Driver
jdbc:postgres://host/database
PostGreSQL (free,
www.postgreslq.org)
org.gjt.mm.mysql.Driver
jdbc:mysql://host/database
MySql (free,
www.mysql.org)
Queries
ResultSet rs = stmt.executeQuery(
"SELECT * from mijnTabel where naam='an'");
while (rs.next())
{String naam = rs.getString(1);
double waarde = rs.getDouble(3);
…
}
rs.close(); stmt.close(); conn.close();
stmt.executeUpdate("CREATE TABLE …");
stmt.executeUpdate("INSERT …");
Java-SQL type overeenkomst
SQL
Java
SQL
BIGINT
getLong()
LONGVARCHAR getString()
BINARY
getBytes()
NUMERIC
getBigDecimal()
BIT
getBoolean()
OTHER
getObject()
CHAR
getString()
REAL
getFloat()
DATE
getDate()
SMALLINT
getShort()
DECIMAL
getBigDecimal()
TIME
getTime()
DOUBLE
getDouble()
TIMESTAMP
getTimestamp()
FLOAT
getDouble()
TINYINT
getByte()
INTEGER
getInt()
VARBINARY
getBytes()
VARCHAR
getString()
LONGVARBINARY getBytes()
Java
SQLException
try
{…
}
catch (SQLException sqle)
{while (sqle!=null)
{…
sqle = sqle.getNextException();
}
}
ADO.NET en C#
•doelstellingen ADO.NET
–multi-tier: gemakkelijk code op ≠ lagen van toepassing te gebruiken
–disconnected: enkel tijdelijke connecties naar databank
–XML-gebaseerd:
•voor data-transport tussen lagen
•relationele data <-> XML-data
–schaalbaar
–snel: vooral in combinatie met SQLServer
•nadelen:
–niet mogelijk om van databankprovider te wijzigen zonder code te wijzigen
•performantie
•wijzigingen beperkt
•geen wijzigingen als binnen OLEDB of binnen ODBC
–native dataproviders maar voor beperkt aantal databanken beschikbaar (breidt wel uit)
.NET Data Providers
•brug tss toepassing en databron (databank)
•voor Microsoft SQLServer 7.0 en later: SQLServer.Net data provider: using
System.Data.SqlClient
•voor vroegere SQLServer-versies (Access, enkel voor kleine 1-laagstoepassingen): OLEDB.Net:
System.Data.OleDb
•ook System.Data.Odbc en System.Data.OracleClient
Belangrijkste klassen in elke dataprovider
•Connection: voor verbinding met databank
•Command: instructie voor databank, typisch SQL insert, updat, delete, …
•Parameter: parameters bij command
•DataReader: snel ophalen data, enkel voorwaarts doorlopen van een resultaatset
•DataAdapter: verbinding tss dataprovider en DataSet (vlg slide), zorgt dat DataSet
gevuld wordt door query
•CommandBuilder: als wijzigingen in DataSet terug naar databank moeten (hoe moeten
insert, update, delete gebeuren?)
•…
DataSet
•kan meer dan 1 DataTable bevatten
•onafhankelijke datacontainer
–vullen dmv databankquery
–manueel vullen
•wijzigingen aan DataSet worden bijgehouden (wijzigingen doorgeven aan databank via
DataAdapter)
•ondervragen mogelijk zonder databank
•kunnen ook data-integriteit verzorgen
•gemakkelijk converteerbaar naar XML en terug
•DataSet: belangrijkste klasse, vooral container voor andere objecten
•DataTable: 1 tabel of resultaat van 1 query
•DataColumn: informatie over veld: naam, datatype, lengte
•DataRow: elke rij in DataTable, vragen naar waarde in bepaalde kolom
•…
DataAdapter (CommandBuilder)
•OLEDB en SQL
•brug tss DataSet en databron
•SqlDataAdapter (met SqlCommand en SqlConnection) of OleDbDataAdapter (met
OleDbCommand en OleDbConnection)
•gebruikt commando's voor aanpassen databron als DataSet gewijzigd
–gebruik Fill-methode van DataAdapter om DataSet te vullen (select)
–gebruik Update-methode voor insert, update of delete voor elke gewijzigde rij
–gebruik CommandBuilder-object om dit laatste automatisch te doen, maar minder
performant
Vb. Connectie
using System;
using System.Data.SqlClient;
public class adooverview1
{ public static void Main()
{ adooverview1 myadooverview1 = new adooverview1();
myadooverview1.Run();
}
public void Run()
{SqlConnection mySqlConnection = new SqlConnection(
"server=(local)\\NetSDK;Trusted_Connection=yes;
database=northwind");
try
{mySqlConnection.Open();
Console.WriteLine("Opened Connection to {0}", mySqlConnection.ConnectionString);
mySqlConnection.Close();
Console.WriteLine("Closed Connection. It is important to close connections explicitly.");
}
catch
{ Console.WriteLine("Couldn't Open Connection to {0}",
mySqlConnection.ConnectionString);
}
}
}
insert, delete
string Message = null;
SqlConnection myConnection = new …
SqlCommand mySqlCommand = new SqlCommand("INSERT INTO Customers (CustomerId,
CompanyName, ContactName, ContactTitle, Address) Values ('ABC','ABC Company', 'John
Smith', 'Owner','One My Way')", myConnection);
SqlCommand mySqlCleanup = new SqlCommand("DELETE FROM Customers WHERE
CustomerId = 'ABC'", myConnection);
try
{myConnection.Open();
mySqlCleanup.ExecuteNonQuery(); // remove record that may have been entered previously.
mySqlCommand.ExecuteNonQuery();
Message = "New Record inserted into Customers table in northwind.";
}
catch(Exception e)
{ Message= "Couldn't insert record: " + e.ToString();
}
finally
{ myConnection.Close();
}
Console.Write(Message);
DataReader
SqlDataReader myReader = null;
SqlConnection mySqlConnection = new …
SqlCommand mySqlCommand = new SqlCommand("select * from customers",
mySqlConnection);
try
{mySqlConnection.Open();
myReader = mySqlCommand.ExecuteReader();
Console.Write("Customer ID ");
Console.WriteLine("Company Name");
while (myReader.Read())
{Console.Write(myReader["CustomerID"].ToString() + " ");
Console.WriteLine(myReader["CompanyName"].ToString());
}
}
catch(Exception e)
{Console.WriteLine(e.ToString());
}
finally
{if (myReader != null) myReader.Close();
if (mySqlConnection.State == ConnectionState.Open) mySqlConnection.Close();
}
…
DataReader met OleDb
OleDbDataReader myDataReader = null;
OleDbConnection myOleDbConnection = new
OleDbConnection("server=(local)\\NetSDK;Trusted_Connection=yes;database=northwind;provi
der=sqloledb");
OleDbCommand myOleDbCommand = new OleDbCommand("SELECT EmployeeID,
LastName, FirstName, Title, ReportsTo FROM Employees", myOleDbConnection);
try
{myOleDbConnection.Open();
myDataReader = myOleDbCommand.ExecuteReader();
Console.Write("EmployeeID" + "\t");
Console.Write("Name" + "\t");
Console.Write("Title" + "\t");
Console.Write("ReportsTo" + "\n");
// Always call Read before accessing data.
while (myDataReader.Read())
{Console.Write(myDataReader.GetInt32(0) + "\t");
Console.Write(myDataReader.GetString(2) + " " + myDataReader.GetString(1) + "\t");
Console.Write(myDataReader.GetString(3) + "\t");
if (myDataReader.IsDBNull(4)) Console.Write("N/A\n");
else Console.Write(myDataReader.GetInt32(4) + "\n");
}
}
catch(Exception e)
{ Console.Write(e.ToString());
}
finally
{// Always call Close when done reading.
if (myDataReader != null) myDataReader.Close();
// Close the connection when done with it.
if (myOleDbConnection.State == ConnectionState.Open)
myOleDbConnection.Close();
}
…
DataSet
SqlConnection myConnection = new
SqlConnection("server=(local)\\NetSDK;Trusted_Connection=yes;database=northwind");
SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter("select * from customers",
myConnection);
try
{DataSet myDataSet = new DataSet();
mySqlDataAdapter.Fill(myDataSet,"Customers");
foreach (DataRow myDataRow in myDataSet.Tables["Customers"].Rows)
{ Console.WriteLine(myDataRow["CustomerId"].ToString());
}
}
catch(Exception e) { Console.WriteLine(e.ToString()); }
…
DataAdapter
SqlConnection myConnection = new
SqlConnection("server=(local)\\NetSDK;Trusted_Connection=yes;database=northwind");
SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter("select * from customers",
myConnection);
SqlDataAdapter mySqlDataAdapter1 = new SqlDataAdapter("select * from orders",
myConnection);
// Restore database to it's original condition so sample will work correctly.
Cleanup();
try
{DataSet myDataSet = new DataSet();
DataRow myDataRow;
// Create command builder. This line automatically generates the update commands for
// you, so you don't have to provide or create your own.
SqlCommandBuilder mySqlCommandBuilder = new SqlCommandBuilder(mySqlDataAdapter);
// Set the MissingSchemaAction property to AddWithKey because Fill will not cause
//primary key & unique key information to be retrieved unless AddWithKey is specified.
mySqlDataAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
mySqlDataAdapter1.MissingSchemaAction = MissingSchemaAction.AddWithKey;
mySqlDataAdapter.Fill(myDataSet,"Customers");
Console.WriteLine("Loaded data from Customers table into dataset.");
mySqlDataAdapter1.Fill(myDataSet,"Orders");
Console.WriteLine("Loaded data from Orders table into dataset.");
// ADD RELATION
myDataSet.Relations.Add("CustOrders",myDataSet.
Tables["Customers"].Columns["CustomerId"],
myDataSet.Tables["Orders"].Columns["CustomerId"]);
// EDIT
myDataSet.Tables["Customers"].Rows[0]["ContactName"]="Peach";
// ADD
myDataRow = myDataSet.Tables["Customers"].NewRow();
myDataRow["CustomerId"] ="NewID";
myDataRow["ContactName"] = "New Name";
myDataRow["CompanyName"] = "New Company Name";
myDataSet.Tables["Customers"].Rows.Add(myDataRow);
Console.WriteLine("Inserted new row into Customers.");
// Update Database with SqlDataAdapter
mySqlDataAdapter.Update(myDataSet, "Customers");
Console.WriteLine("Sent Update to database.");
Console.WriteLine("DataSet processing has completed successfully!");
}
catch(Exception e)
{ Console.WriteLine(e.ToString());
}
…