dotConnect for SQLite can be used in conjunction with Microsoft Enterprise Library Data Access Application Block, which boosts development productivity in many cases. This topic describes what is Enterprise Library, what part of it can be substituted with dotConnect for SQLite, and how to use the bundle in your applications. The topic consists of following sections:
The first three sections contain general information on Enterprise Library and its benefits. You can safely skip it if you are acquainted with the matter. The last section practically demonstrates how to start using dotConnect for SQLite as Data Access Application Block.
This feature is available in Professional Edition only.
Enterprise Library is a set of application blocks - reusable software components designed to help developers with usual enterprise development tasks.
Enterprise Library is designed for:
Application blocks help solve common problems that developers face from one project to another. They have been designed to encapsulate the Microsoft recommended best practices for .NET applications. Application blocks can be added to applications quickly and easily.
The Enterprise Library Data Access Application Block simplifies common data access functionality. Applications can use this application block in a variety of situations, such as reading data for display, passing data through application layers, and submitting changed data back to the database system. Common maintenance tasks, such as managing connections and creating and caching parameters, are encapsulated in the application block's methods. In other words, the Data Access Application Block provides access to the most often used features of ADO.NET in simple-to-use classes. The Block exposes a model that supports encapsulation of database-specific features, which allows applications to be ported from one database type to another without modifying the client code.
The Data Access Application Block provides the following benefits:
The Data Access Application Block includes a small number of methods that simplify the most common methods of accessing a database. Each method encapsulates the logic required to retrieve the data and manage the connection to the database. You should consider using the application block if your application uses standard data access techniques.
The application block supplements the code in ADO.NET that allows you to use the same code with different database types. The GenericDatabase class allows you to use the application block with any configured ADO.NET DbProviderFactory object.
The Data Access Application Block is a complement to ADO.NET; it is not a replacement. The application block provides simplicity and convenience while helping developers use ADO.NET with best practices. If your application needs to retrieve data in specialized way, or if your code needs customization to take advantage of features specific to SQLite, using dotConnect for SQLite might suit you better.
The rest of the article demonstrates configuring and using Enterprise Library. We will consider parts of the DataAccessQuickStart sample found in Program Files\Devart\dotConnect\SQLite\EnterpriseLibrary\ folder. You can study it in conjunction with this article. The sample represents integral project that demonstrates primary techniques, while the article explains most important points.
The first thing to do in using dotConnect for SQLite as Data Access Block is adjusting application settings. All settings are stored in single file, App.config. No other configuration file is needed. This file (App.config) in your sources should look like the following example:
<configuration>
<configSections>
<section
name="dataConfiguration"
type="
Microsoft.Practices.EnterpriseLibrary.Data.Configuration.DatabaseSettings,
Microsoft.Practices.EnterpriseLibrary.Data"/>
</configSections>
<connectionStrings>
<add
name="DataAccessQuickStart"
providerName="Devart.Data.SQLite"
connectionString="Data Source=..\EntLibQuickStarts.db;FailIfMissing=false;" />
</connectionStrings>
<system.data>
<DbProviderFactories>
<remove invariant="Devart.Data.SQLite" />
<add name="dotConnect for SQLite" invariant="Devart.Data.SQLite"
description="Devart dotConnect for SQLite" type="Devart.Data.SQLite.SQLiteProviderFactory, Devart.Data.SQLite, Version=5.1.26.0, Culture=neutral, PublicKeyToken=09af7300eec23701" />
</DbProviderFactories>
</system.data>
<dataConfiguration
defaultDatabase="DataAccessQuickStart">
<providerMappings>
<add
databaseType="Devart.Data.SQLite.EnterpriseLibrary.SQLiteDatabase,
Devart.Data.SQLite.EnterpriseLibrary"
name="dotConnect for SQLite" />
</providerMappings>
</dataConfiguration>
</configuration>
After compilation App.config is renamed to YourAppName.exe.config where YourAppName.exe is name of the executable file, and placed next to the file. Application reads the file when it is loaded. Note that for web applications and web services metaconfiguration file is web.config in the root directory of your web site or virtual directory.
In Data Access Application Block obtaining handle to database is as simple as following statement:
Database db = DatabaseFactory.CreateDatabase();
If an argument was not supplied to the method it searches configuration XML for defaultInstance
attribute of enterpriseLibrary.databaseSettings node. Its value is name of the instance to use.
This instance must be described in instances node. Type of the instance determines what provider
to use (enumerated in databaseTypes) and name of connection string. The connection string itself
is defined in connectionStrings node. Its arguments are self-explanatory.
This is how Data Access Application Block initializes database connections. Single configuration file
can describe multiple data providers, connection strings and databases. You can refer to certain database
in your code directly:
Database db = DatabaseFactory.CreateDatabase("myInstanceName");
Microsoft provides a GUI tool to create and configure visually the files App.config. You can find in Start menu shortcut Enterprise Library Configuration to the file EntLibConfig.exe. This tool also allows you to setup encryption for the configuration file.
The following assemblies need to be referenced by the solution: Microsoft.Practices.EnterpriseLibrary.Data and Microsoft.Practices.EnterpriseLibrary.Common. They are typically located in bin folder of Enterprise Library like \Program Files\Microsoft Enterprise Library\bin\. Add namespace Microsoft.Practices.EnterpriseLibrary.Data to using (Imports in Visual Basic) clause of your sources to have important classes visible.
To use dotConnect for SQLite as Data Access Application Block you have to add reference to Devart.Data.SQLite.EnterpriseLibrary.dll assembly. dotConnect for SQLite is shipped with sources of this assembly, so if you need to alter it or add custom functionality, add Devart.Data.SQLite.EnterpriseLibrary project to your solution.
In this section we will analyze adapted fragments of DataAccessQuickStart sample.
The following routine returns list of customers from corresponding table. It returns the list as string object where individual customers are separated with line breaks.
public string GetCustomerList() {
// Create the Database object, using the default
// database service as described above.
Database db = DatabaseFactory.CreateDatabase();
// Define SQL query to retrieve the data.
string sqlCommand = "Select Name From Customers";
// Create ADO.NET DbCommand object
DbCommand dbCommand = db.GetSqlStringCommand(sqlCommand);
// Create intermediate data holder
StringBuilder readerData = new StringBuilder();
// DataReader that will hold the returned results
// The ExecuteReader call will request the connection to be closed upon
// the closing of the DataReader. The DataReader will be closed
// automatically when it is disposed.
using (IDataReader dataReader = db.ExecuteReader(dbCommand)) {
// Iterate through DataReader
while (dataReader.Read()) {
// Get the value of the 'Name' column in the DataReader
readerData.Append(dataReader["Name"]);
readerData.Append(Environment.NewLine);
}
}
return readerData.ToString();
}
The next example demonstrates usage of parameters. The routine returns DataSet that contains all products within given category.
public DataSet GetProductsInCategory(int Category) {
Database db = DatabaseFactory.CreateDatabase();
string sqlCommand = "Select * From Products Where CategoryID = :CategoryID";
DbCommand dbCommand = db.GetSqlStringCommand(sqlCommand);
// Create and add integer input parameter
db.AddInParameter(dbCommand, "CategoryID", DbType.Int32, Category);
// DataSet that will hold the returned results
DataSet productsDataSet = null;
// Open connection, execute the query and close connection
productsDataSet = db.ExecuteDataSet(dbCommand);
return productsDataSet;
}
The next example shows how to modify a DataSet. The routine retrieves data, inserts new row into table and updates changes. You can alter or delete existing rows in same way.
public int UpdateProducts() {
Database db = DatabaseFactory.CreateDatabase();
// Create DataSet that receives initial data
DataSet productsDataSet = new DataSet();
string sqlCommand = "Select ProductID, ProductName, CategoryID,
UnitPrice, LastUpdate From Products";
DbCommand dbCommand = db.GetSqlStringCommand(sqlCommand);
// Declare table name to refer later
string productsTable = "Products";
// Retrieve the initial data
db.LoadDataSet(dbCommand, productsDataSet, productsTable);
// Get the table that will be modified
DataTable table = productsDataSet.Tables[productsTable];
// Add a new product to existing DataSet
DataRow addedRow = table.Rows.Add(new object[] {
DBNull.Value, "New product", 11, 25 });
// Setup Insert command
DbCommand insertCommand = db.GetSqlStringCommand("Insert Into Products " +
"(ProductName, CategoryID, UnitPrice) Values (:ProductName,
:CategoryID,:UnitPrice)");
db.AddInParameter(insertCommand, "ProductName", DbType.String,
"ProductName", DataRowVersion.Current);
db.AddInParameter(insertCommand, "CategoryID", DbType.Int32,
"CategoryID", DataRowVersion.Current);
db.AddInParameter(insertCommand, "UnitPrice", DbType.Decimal,
"UnitPrice", DataRowVersion.Current);
// Update the DataSet, capturing the number of rows that were affected
int rowsAffected = db.UpdateDataSet(productsDataSet, "Products",
insertCommand, null,
null, UpdateBehavior.Standard);
return rowsAffected;
}
More examples can be found in DataAccessQuickStart demo project. You can open it in your IDE and explore other features of Enterprise Library.