De SqlHelperParameterCache class wordt door de SqlHelper class gebruikt, maar kan ook gebruikt worden om een SqlParameter array te bevragen, zodat alle parameters voor een stored procedure gevuld kunnen worden. GetSpParameterSet is de enige methode waarmee je kunt vragen welke parameters een stored procedure heeft.
Indien je niet de SqlConnection als parameter meegeeft, zal het data access application block voor elke aanroep een nieuwe connectie aanmaken. Als je er voor zorgt dat elke keer dezelfde connection string wordt meegegeven, wordt er optimaal gebruik gemaakt van connection pooling. Op deze manier is het mogelijk om zeer goed schaalbare databaseoplossingen te ontwikkelen.
// Create & open a SqlConnection,
// and dispose of it after we are done
using (SqlConnection connection =
new SqlConnection(connectionString))
{
connection.Open();
// Call the overload that takes a
// connection in place of the connection string
return ExecuteNonQuery(connection, commandType,
commandText, commandParameters);
}
Om er voor te zorgen dat connection pool niet volloopt, wordt elke connectie gesloten en worden alle resources direct vrijgegeven. Dit wordt gedaan door de CLR, doordat het C# statement ‘using’ gebruikt wordt. Hierbij wordt het gecreëerde object door de garbage collector vrijgegeven, zodra de scope van de using voorbij is.
Hoe gebruik je het?
Dataset
Een dataset ophalen is met een SqlHelper class heel eenvoudig. Eigenlijk is het enige wat je hoeft te doen, weten waar de data staat, de connection string, en welke data je nodig hebt, de query. Vervolgens geef je deze informatie als parameters mee aan ExecuteDataset en je krijgt een keurig gevulde dataset terug.
string connectionString =
"Integrated Security=SSPI;
database=northwind;server=localhost";
DataSet ds =
SqlHelper.ExecuteDataset(
connectionString, CommandType.Text,
"SELECT ProductID, ProductName, UnitPrice
FROM Products ORDER BY ProductName" );
foreach ( DataRow dr in ds.Tables[0].Rows )
{
Console.WriteLine( dr[0].ToString() + "\t" +
dr[1].ToString() + "\t" +
dr[2].ToString() );
}
Typed dataset
Een typed dataset is een class die erft van dataset en een aantal extra ‘typed’ methoden aanbiedt om de data in de dataset te benaderen. Verder wordt de datadefinitie van de datatables in de class vastgelegd. Om volledig ‘typed’ te kunnen werken is het ook noodzakelijk om ook een typed datarow te hebben. Een typed dataset kun je in Visual Studio.NET eenvoudig aanmaken. Het beginpunt van een typed dataset is een XSD schema. Je kunt dit aanmaken door een ‘Dataset’ bestand toe te voegen aan je project. Vervolgens kun je vanuit de server explorer met drag en drop de tabel(len) die je nodig hebt op het XSD plaatsen. Je kunt nu een typed dataset van de XSD maken door het context menu van het XSD bestand te openen en ‘Run custom tool’ te kiezen. Er wordt nu een typed dataset aangemaakt die je niet direct in het solution explorer scherm ziet staan. Om de typed dataset te zien moet je ‘Show all files’ in de solution explorer kiezen. De typed dataset verschijnt nu onder de XSD. De typed dataset bevat al een definitie en hoeft dus alleen nog maar gevuld te worden. Wel moet je aan de SqlHelper vertellen welke tabel(len) met data gevuld moeten worden. Aangezien er ook een typed datarow is aangeleverd, kunnen de velden vervolgens als een property worden uitgelezen.
ProductDataset ds = new ProductDataset();
string[] tables = { "Products" };
SqlHelper.FillDataset(
connectionString, CommandType.Text,
"SELECT ProductID, ProductName, UnitPrice
FROM Products ORDER BY ProductName", ds, tables);
foreach ( ProductDataset.ProductsRow product
in ds.Tables[0].Rows )
{
Console.WriteLine(
product.ProductID.ToString() + "\t" +
product.ProductName + "\t" +
product.UnitPrice.ToString() );
}
Update
Het updaten van een dataset is wat meer werk dan een dataset lezen. Zo moet je allereerst stored procedures aanmaken, zodat het data access building block daar gebruik van kan maken. Voor eenvoudige stored procedures kun je in de Enterprise Manager van SQL Server kiezen voor ‘Create Stored Procedure Wizard’ onder ‘Tools/Wizards’. Hiermee kun je de insert, delete en update stored procedures voor een tabel bij elkaar klikken.
CREATE PROCEDURE UpdateProduct
(@ProductID [int],
@ProductName [nvarchar](40),
@UnitPrice [money])
AS UPDATE [Products]
SET [ProductName] = @ProductName,
[UnitPrice] = @UnitPrice
WHERE ( [ProductID] = @ProductID)
GO
Het updaten van een dataset is wat meer werk
Als je de stored procedures klaar hebt, moet je voor insert, delete en update een SqlCommand aanmaken met de CreateCommand methode van de SqlHelper. Deze methode heeft behalve de connectie en de stored procedure naam de lijst met veldnamen nodig die als parameter worden meegegeven. Deze parameters moeten natuurlijk overeenkomen met de parameters van de stored procedure. Om nu de update uit te voeren geef je de drie SqlCommand objecten mee aan UpdateDataset die samen met de naam van de tabel gebruikt moeten worden om de update mee uit te voeren. Let op: als je geen typed dataset gebruikt is de naam van eerste tabel in de dataset ‘Table’ en heeft deze niet de naam van de databasetabel.
// Get Dataset
DataSet ds =
SqlHelper.ExecuteDataset( connectionString );
// Set Price of the first product
ds.Tables[0].Rows[0]["UnitPrice"] = 35.0;
SqlConnection conn =
new SqlConnection( connectionString );
// Create empty SqlCommand, null is not allowed.
SqlCommand insert =
new SqlCommand();
// Create empty SqlCommand, null is not allowed.
SqlCommand delete =
new SqlCommand();
SqlCommand update =
SqlHelper.CreateCommand(
conn, "UpdateProduct",
"ProductID", "ProductName", "UnitPrice" );
SqlHelper.UpdateDataset(
insert, delete, update, ds, "Table" );
Transacties
Indien je gebruik wilt maken van database transacties, zul je gebruik moeten maken van de overloads waarbij je een SqlTransaction object kunt meegeven. Indien je al gebruik maakt van de enterprises services voor je transacties, hoef je de SqlTransaction natuurlijk niet meer te gebruiken. In het voorbeeld kun je zien hoe alle prijzen verhoogd worden binnen een transactie. De stored procedure UpdateProduct wordt hiervoor rechtstreeks aangeroepen.
SqlConnection conn =
new SqlConnection( connectionString );
conn.Open();
DataSet ds =
SqlHelper.ExecuteDataset(
conn, CommandType.Text,
"SELECT ProductID, ProductName, UnitPrice
FROM Products ORDER BY ProductName");
SqlTransaction trans = conn.BeginTransaction();
try
{
foreach ( DataRow dr in ds.Tables[0].Rows )
{
SqlHelper.ExecuteNonQuery(
trans, "UpdateProduct", dr[0], dr [1],
Convert.ToDouble( dr[2] ) * 1.1 );
}
trans.Commit();
}
catch
{
trans.Rollback();
}
Wat kun je zelf aanpassen?
Het data access application block zoals Microsoft dat uitbrengt is alleen geschikt voor SQL Server. Als je nu niet werkt met SQL Server maar met een andere database, is het nog steeds goed mogelijk om het application block te gebruiken, maar je zult een aantal zaken moeten aanpassen. Je moet SqlConnection, SqlParameter, SqlCommand en SqlCommandBuilder uit de System.Data.SqlClient namespace vervangen door de varianten uit bijvoorbeeld de System.Data.OleDb of System.Data.OracleClient namespace. Sommige functionaliteit zal niet altijd beschikbaar zijn; zo is ExecuteXmlReader alleen beschikbaar voor SqlCommand en niet in OracleCommand en OleDbCommand. De methoden die daar gebruik van maken kun je in dat geval het beste verwijderen.
Hieronder zie je een zoek en vervang tabel om de SqlHelper om te zetten naar een versie voor Oracle. Als je in de SqlHelper.cs alles zoekt en vervangt volgens de tabel, ben je al bijna klaar de SqlHelper geschikt te maken voor een andere database.
|
SqlHelper voor SQL Server |
SqlHelper voor Oracle |
|
using System.Data.SqlClient |
using System.Data.OracleClient |
|
SqlDataAdapter |
OracleDataAdapter |
|
SqlCommand |
OracleCommand |
|
SqlParameter |
OracleParameter |
|
SqlDataReader |
OracleDataReader |
|
SqlConnection |
OracleConnection |
|
SqlTransaction |
OracleTransaction |
Het enige wat je nu nog moet doen om te zorgen dat de SqlHelper voor Oracle ook compileert, is de ExecuteXmlReader en ExecuteXmlReaderTypedParams methoden weghalen.
Een andere handige aanpassing kan zijn om een overload van de static methoden op de SQLHelper class te maken waarin de connectionstring niet meegegeven hoeft te worden.
///
/// Execute a SqlCommand (that returns a resultset and
/// takes no parameters) against the default database.
///
///
/// e.g.:
/// DataSet ds = ExecuteDataset(
/// CommandType.StoredProcedure, "GetOrders");
///
///
/// The CommandType (stored procedure, text, etc.)
///
///
/// The stored procedure name or T-SQL command
///
///
/// A dataset containing the resultset generated by
/// the command
///
public static DataSet ExecuteDataset(
CommandType commandType, string commandText)
{
string connectionString =
System.Configuration.ConfigurationSettings.
AppSettings.Get("ConnectionString");
if ( connectionString == null ||
connectionString.Length == 0 )
throw new Exception(
"ConnectionString not defined on .config file" );
// Pass through the call providing null
// for the set of SqlParameters
return ExecuteDataset( connectionString, commandType,
commandText, (SqlParameter[])null);
}
Hierdoor hoef je niet overal in de applicatie de connection string te kennen of code te schrijven om de connection string uit de .config file te lezen.
De laatste nuttige uitbreiding is voor het updaten van datasets. Met de huidige functionaliteit moet je altijd een aantal stored procedures aanmaken om de updates uit te voeren en te definiëren welke parameters meegegeven moeten worden. Voor standaard insert/update/delete acties is het ook goed mogelijk om een overload te maken die gebruik maakt van de SqlCommandBuilder om de eenvoudige acties automatisch te laten verlopen.
Let op: de SqlCommandBuilder is in het gebruik natuurlijk een stuk trager dan rechtstreeks stored procedures aanroepen. Gebruik het dus niet als performance belangrijk is.
Conclusie
In de praktijk blijkt het Data Access Application Block van Microsoft een waardevolle toevoeging te zijn voor het ontwikkelen van applicaties die gebruik maken van een database. Door het application block te gebruiken zorg je als ontwikkelaar voor een centrale database toegang in je applicatie en voorkom je dat je veel code voor database toegang elke keer opnieuw schrijft. Het Data Access Application Block is gratis te downloaden bij http://www.microsoft.com/downloads en de sourcecode is toegevoegd waardoor je eenvoudig aanpassingen kunt doen of het geschikt kunt maken voor een andere database dan SQL Server.