CHAPTER 9
Data Access
Since computers are designed to process data, it's a rare program that doesn't require some form of data access, whether it's reading a small configuration file or accessing a full-scale relational database management system. In this chapter, you will investigate the wide range of options that are available for data access in F#.
The System.Configuration Namespace
Whenever you execute any program written in any .NET language, the .NET runtime will automatically check whether a configuration file is available. This is a file with the same name as the executable plus the extension .config that must be placed in the same directory as the executable, meaning the configuration file for MyApp.exe would be MyApp.exe.config. In ASP.NET applications, these files are called web.config files because there is no executable, and they live in the web root. These files are useful for storing settings that you want to be able to change without recompiling the application—a classic example of this is a connection string to a database. You should be careful not to store values that are specific to a user in the configuration file, because any changes to the file will affect all users of the application. The best place to store user-specific settings is in a relational database. I'll cover relational database access in the "ADO.NET" section.
Note You can use configuration files to do much more than store data for your program to access. You can also use them to control various settings with the .NET Framework, such as controlling which version of the .NET runtime should be used or directing a program to automatically look for a new version of a .dll. I don't cover this functionality in this chapter, but you can find more information online at http://strangelights.com/FSharp/Foundations/default.aspx/FSharpFoundations.Config.
The System.Configuration namespace provides an easy way to access configuration values, and the simplest way of accessing configuration data is with ConfigurationManager. The next example shows how to load a simple key-value pair from a configuration file. Imagine you have the following configuration file and want to read "MySetting" from the file:
<configuration>
<appSettings>
<add key="MySetting" value="An important string" />
</appSettings>
</configuration>
The following code loads the setting by using ConfigurationManager's static AppSettings property:
#light
#r "System.Configuration.dll";;
open System.Configuration
let setting = ConfigurationManager.AppSettings.Item("MySetting")
print_string setting
The result is as follows:
An important string
Note The way to access these values in .NET version 1.1 was through the ConfigurationSettings type in System.dll. This type is still available in .NET 2.0 but has been depreciated, so it is best to avoid using it.
Since the most common use for these name-value pairs is to store connection strings, it is customary to use a separate section specifically for this purpose to help separate them from other configuration settings. The providerName property allows you to store information about which database provider the connection string should be used with. The next example shows how to load the connection string "MyConnectionString" from the following configuration file:
<configuration>
<connectionStrings>
<add
name="MyConnectionString"
connectionString=" Data Source=server;
Initial Catalog=pubs;
Integrated Security=SSPI;"
providerName="System.Data.SqlClient" />
</connectionStrings>
</configuration>
The following example loads the connection string via another static property on the ConfigurationManager class, the ConnectionString property. This is a collection that gives access to a type called ConnectionStringSettings, which has a ConnectionString property giving access to the connection string and a ProviderName property giving access to the provider name string.
#light
#r "System.Configuration.dll";;
let connectionStringDetails =
ConfigurationManager.ConnectionStrings.Item("MyConnectionString")
let connectionString = connectionStringDetails.ConnectionString
let providerName = connectionStringDetails.ProviderName
printfn "%s
%s"
connectionString
providerName
The results are as follows:
Data Source=server;
Initial Catalog=pubs;
Integrated Security=SSPI;
System.Data.SqlClientCaution Notice that because I added spaces and newline characters to the configuration file to improve the formatting, these were also added to the connection string, which can be seen when output to the console. Most libraries consuming the connection string will correct for this, but some may not, so be careful when formatting your configuration file.
You'll explore the possibility of choosing between different relational databases at runtime in "The EntLib Data Access Block" section later in this chapter.
It's also possible to load configuration files associated with other programs or web applications and even machine.config, which contains the default settings for .NET on a particular machine. These files can be queried, updated, and then saved. The following sample shows how to open machine.config and enumerate the various sections within it:
#light
#r "System.Configuration.dll";;
let config =
ConfigurationManager.OpenMachineConfiguration()
for x in config.Sections do
print_endline x.SectionInformation.Name
The results, when executed on my machine, are as follows:
system.data
windows
system.webServer
mscorlib
system.data.oledb
system.data.oracleclient
system.data.sqlclient
configProtectedData
satelliteassemblies
system.data.dataset
startup
system.data.odbc
system.diagnostics
runtime
system.codedom
system.runtime.remoting
connectionStrings
assemblyBinding
appSettings
system.windows.forms
This section has shown how to work with configuration files, a particular kind of XML file. The next section will show how to use the System.Xml namespace to work with any kind of XML file.
The System.IO Namespace
The main purpose of the System.IO namespace is to provide types that give easy access to the files and directories of the operating system's file store, although it also provides ways of writing to memory and network streams too.
The namespace offers two main ways to deal with files and directories. FileInfo and DirectoryInfo objects are used to get or alter information about a file or directory. There are also File and Directory classes that offer the same functionality but that are exposed as static members that require the filename to be passed to each method. Generally, you will use the File and Directory classes if you want a single piece of information about a file system object and use the FileInfo and DirectoryInfo classes if you need lots of information about a single file system object. The two techniques are complementary; for example, you might use the Directory type to get information about all the files in a directory and then use the FileInfo object to find out the name and other information about the file. Here's an example of doing this:
#light
open System.IO
let files = Directory.GetFiles(@"c:")
for filepath in files do
let file = new FileInfo(filepath)
printfn "%s %d %O"
file.Name
file.Length
file.CreationTime
The results, when executed on my machine, are as follows:
addadmin.bat 95 01/10/2003 02:08:10
ATTDialer.doc 297472 03/11/2003 20:12:54
AUTOEXEC.BAT 0 12/05/2003 20:21:21
avapower.gif 1056 07/07/2004 01:27:05
boot.ini 211 12/05/2003 12:58:01
CONFIG.SYS 0 12/05/2003 20:21:21
dpst.bat 17 01/10/2003 02:08:10
imagefaq.bat 21 01/10/2003 02:08:10
IO.SYS 0 12/05/2003 20:21:22
MSDOS.SYS 0 12/05/2003 20:21:22
NTDETECT.COM 47564 23/08/2001 14:00:00
Ntldr 250032 23/08/2001 14:00:00
NavCClt.Log 35152 13/05/2003 00:44:02
The namespace also provides an extremely convenient way to work with the contents of files. Files are open and are represented as streams, which provide a way to read or write bytes, characters, or strings from a file. Opening a file and reading text from it could not be simpler—just call the File.OpenText method, and you get access to a StreamReader object that allows you to read the file line by line. The following example demonstrates reading a comma-separated file, containing three columns of data:
#light
open System.IO
//test.csv:
//Apples,12,25
//Oranges,12,25
//Bananas,12,25
using (File.OpenText("test.csv"))
(fun f ->
while not f.EndOfStream do
let line = f.ReadLine()
let items = line.Split([|','|])
printfn "%O %O %O"
items.[0]
items.[1]
items.[2])
The results, when executed with the text file in the comments, are as follows:
Apples 12 25
Oranges 12 25
Bananas 12 25
Note The File.OpenText method assumes your file has a UTF-8 encoding. If your file does not use this text encoding, you should call the OpenRead method and then wrap the resulting FileStream object in a StreamReader, passing in the appropriated encoding object. For example, if your file used the encoding Windows-1252 for Western languages, you should open it via new StreamReader(File.OpenRead("accents.txt"), Encoding.GetEncoding(1252)).
The System.Xml Namespace
XML has become a popular data format for a number of reasons, probably because for most people it is a convenient format to represent their data and because the resulting files tend to be reasonably human readable. Programmers tend to like that you can have both files be unstructured (that is, don't follow a set pattern) or have the files be structured and have the data conform to a contract defined by an XSD schema. Programmers also like the convenience of being able to query the data using XPath, which means that writing custom parsers for new data formats is rarely necessary, and files can quickly be converted between different XML formats using the powerful XSLT language to transform data.
The System.Xml namespace contains classes for working with XML files using all the different technologies I have described and more besides this. You'll look at the most common way to work with XML files—the .NET implementation of the W3C recommendation for the XML Document Object Model (DOM), which is generally represented by the class XmlDocument. The first example in this section will read information from the following short XML file, fruits.xml:
<fruits>
<apples>2</apples>
< oranges >3</oranges>
<bananas>1</bananas>
</fruits>
The following code loads fruits.xml, binds it to the identifier fruitsDoc, and then uses a loop to display the data:
#light
open System.Xml
let fruitsDoc =
let temp = new XmlDocument()
temp.Load("fruits.xml")
temp
let fruits = fruitsDoc.SelectNodes("/fruits/*")
for x in fruits do
printfn "%s = %s " x.Name x.InnerText
The results are as follows:
apples = 2
oranges = 3
bananas = 1
The next example looks at how to build up an XML document and then write it to disk. Say you have a set of data, bound to the identifier animals, and you'd like to write it as XML to the file animals.xml. You start by creating a new XmlDocument object, and then you build the document by creating the root node via a call to the XmlDocument instance member CreateElement method and then append to the document object using its AppendChild method. The rest of the document is built up by enumerating over the animals list and creating and appending nodes.
#light
open System.Xml
let animals = [ "ants", "6"; "spiders", "8"; "cats", "4" ]
let animalsDoc =
let temp = new XmlDocument()
let root = temp.CreateElement("animals")
temp.AppendChild(root) |> ignore
animals
|> List.iter (fun x ->
let element = temp.CreateElement(fst x)
element.InnerText <- (snd x)
root.AppendChild(element) |> ignore )
temp
animalsDoc.Save("animals.xml")
The result of this code is a file, animals.xml, containing the following XML document:
<animals>
<ants>6</ants>
<spiders>8</spiders>
<cats>4</cats>
</animals>
The System.Xml namespace is large, with many interesting classes to help you work with XML data. Table 9-1 describes some of the most useful ones.
Table 9-1. Summary of Useful Classes from the System.XML Namespace
| Class | Description |
System.Xml.XmlDocument |
The Microsoft .NET implementation of the W3C's XML DOM. |
System.Xml.XmlNode |
This class can't be created directly but is often used; it is the result of the XmlDocument's SelectSingle node method. |
System.Xml.XmlNodeList |
This class is a collection of nodes and is the result of the XmlDocument's SelectNode method. |
System.Xml.XmlTextReader |
This provides forward-only, read-only access to an XML document. Although not as easy to use as the XmlDocument class, it does not require the whole document to be loaded into memory. When working with big documents, it can often provide better performance than the XmlDocument. |
System.Xml.XmlTextWriter |
This class provides a forward-only way to write to an XML document. If you must start your XML document from scratch, this is often the easiest way to create it. |
System.Xml.Schema.XmlSchema |
This provides a way of loading an XML schema into memory and then allows the user to validate XML documents with it. |
System.Xml.Serialization.XmlSerializer |
This allows a user to serialize .NET objects directly to and from XML. However, unlike the BinarySerializer available elsewhere in the framework, this class serializes only public fields. |
System.Xml.XPath.XPathDocument |
This class is designed to be the most efficient way to work with XPath expressions. This class is just the wrapper for the XML document; the programmer must use the XPathExpression and XPathNavigator to actually do the work. |
System.Xml.XPath.XPathExpression |
This class represents an XPath expression to be used with an XPathDocument; it can be compiled to make it more efficient when used repeatedly. |
System.Xml.XPath.XPathNavigator |
Once an XPathExpression has been executed against the XPathDocument, this class can be used to navigate the results; the advantage of this class is that it pulls only one node at a time into memory, making it efficient in terms of memory. |
System.Xml.Xsl.XslTransform |
This class can be used to transform XML using XSLT style sheets. |
ADO.NET
Relational database management systems are the most pervasive form of data storage. ADO.NET, in System.Data and associated namespaces, makes it easy to access relational data. In this section, you'll look at various ways you can use F# with ADO.NET.
Note All database providers use a connection string to specify the database to which to connect. You can find a nice summary of the connection strings you need to know at http://www.connectionstrings.com.
All examples in this section use the AdventureWorks sample database and SQL Server 2005 Express Edition, both freely available for download from http://www.microsoft.com. It should be easy to port these samples to other relational databases. To use this database with SQL Server 2005 Express Edition, you can use the following connection settings or an adaptation of them appropriate to your system:
<connectionStrings>
<add
name="MyConnection"
connectionString="
Database=AdventureWorks;
Server=.SQLExpress;
Integrated Security=SSPI;
AttachDbFilename=
C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDataAdventureWorks_Data.mdf"
providerName="System.Data.SqlClient" />
</connectionStrings>
I'll discuss options for accessing other relational databases in the section "ADO.NET Extensions." The following example shows a simple way of accessing a database:
#light
#r "System.Configuration.dll";;
open System.Configuration
open System.Data
open System.Data.SqlClient
let connectionSetting =
ConfigurationManager.ConnectionStrings.Item("MyConnection")
let connectionString =
connectionSetting.ConnectionString
using (new SqlConnection(connectionString))
(fun connection ->
let command =
let temp = connection.CreateCommand()
temp.CommandText <- "select * from Person.Contact"
temp.CommandType <- CommandType.Text
temp
connection.Open()
using (command.ExecuteReader())
(fun reader ->
let title = reader.GetOrdinal("Title")
let firstName = reader.GetOrdinal("FirstName")
let lastName = reader.GetOrdinal("LastName")
let getString (r : #IDataReader) x =
if r.IsDBNull(x) then
""
else
r.GetString(x)
while reader.Read() do
printfn "%s %s %s"
(getString reader title )
(getString reader firstName)
(getString reader lastName)))
The results are as follows:
Mr. Gustavo Achong
Ms. Catherine Abel
Ms. Kim Abercrombie
Sr. Humberto Acevedo
Sra. Pilar Ackerman
Ms. Frances Adams
Ms. Margaret Smith
Ms. Carla Adams
Mr. Jay Adams
Mr. Ronald Adina
Mr. Samuel Agcaoili
Mr. James Aguilar
Mr. Robert Ahlering
Mr. François Ferrier
Ms. Kim Akers
...
In the previous example, first you find the connection string you are going to use; after this, you create the connection:
using (new SqlConnection(connectionString))
You wrap it in the using function to ensure it is closed after you have finished what you're doing. The connection is used to create a SqlCommand class and use its CommandText property to specify which command you want to execute:
temp.CommandText <- "select * from Person.Contact"
Then you execute the command to create a SqlDataReader class that is used to do the work of actually reading from the database:
using (command.ExecuteReader())
This tool is called through the using function to ensure it is closed correctly.
You probably wouldn't write data access code in F# if you had to write this amount of code for every query. One way to simplify things is to create a library function to execute commands for you, allowing you to parameterize which command to run and which connection to use.
The following example shows how to write such a function. You implement the execCommand function via Seq.generate_using, which is a way of generating an IEnumerable sequence collection. The generate_using function takes two arguments. The first is a function to open a connection to the database and is called each time you enumerate the resulting collection. This function is called the opener and could just as well open a connection to a file. The second is a function to generate the items in the collection, called the generator. In this case, this creates a Dictionary object for a row of data.
#light
#r "System.Configuration.dll";;
open System.Configuration
open System.Collections.Generic
open System.Data
open System.Data.SqlClient
open System.Data.Common
open System
/// Create and open an SqlConnection object using the connection string found
/// in the configuration file for the given connection name
let openSQLConnection(connName:string) =
let connSetting = ConfigurationManager.ConnectionStrings.Item(connName)
let connString = connSetting.ConnectionString
let conn = new SqlConnection(connString)
conn.Open();
conn
/// Create and execute a read command for a connection using
/// the connection string found in the configuration file
/// for the given connection name
let openConnectionReader connName cmdString =
let conn = openSQLConnection(connName)
let cmd = conn.CreateCommand(CommandText=cmdString,
CommandType = CommandType.Text)
let reader = cmd.ExecuteReader(CommandBehavior.CloseConnection)
reader
let readOneRow (reader: #DbDataReader) =
if reader.Read() then
let dict = new Dictionary<string, obj>()
for x = 0 to (reader.FieldCount - 1) do
dict.Add(reader.GetName(x), reader.Item(x))
Some(dict)
else
None
let execCommand (connName : string) (cmdString : string) =
Seq.generate_using
// This function gets called to open a connection and create a reader
(fun () -> openConnectionReader connName cmdString)
// This function gets called to read a single item in
// the enumerable for a reader/connection pair
(fun reader -> readOneRow(reader))
After defining a function such as execCommand, accessing a database becomes pretty easy. You call execCommand, passing the chosen connection and command, and then enumerate the results. This is as follows:
let contactsTable =
execCommand
"MyConnection"
"select * from Person.Contact"
for row in contactsTable do
for col in row.Keys do
printfn "%s = %O" col (row.Item(col))
The results are as follows:
...
ContactID = 18
NameStyle = False
Title = Ms.
FirstName = Anna
MiddleName = A.
LastName = Albright
Suffix =
EmailAddress = [email protected]
EmailPromotion = 1
Phone = 197-555-0143
PasswordHash = 6Hwr3vf9bo8CYMDbLuUt78TXCr182Vf8Zf0+uil0ANw=
PasswordSalt = SPfSr+w=
AdditionalContactInfo =
rowguid = b6e43a72-8f5f-4525-b4c0-ee84d764e86f
ModifiedDate = 01/07/2002 00:00:00
...
One thing you should be careful about when dealing with relational databases is ensuring that the connection is closed in a timely manner. Closing the connection quickly makes the connection available to other database users, improving concurrent access. Let's look at how the previous sample creates connections and how they are "cleaned up" automatically. In the previous example, the opener function openConnectionReader is called every time the collection is enumerated using Seq.iter. This uses an IEnumerator object to iterate the data, which in turn uses the generator function to generate individual results. Each call to Seq.iter creates one SqlDataReader and one SqlDataReader object. These must be closed at the end of the iteration or if the iteration terminates abruptly for some reason. Fortunately, the F# library implementation of Seq.iter and Seq.generate_using are careful to invoke the right functions to clean up resources on both complete and partial iterations. They do this by calling IDisposable.Dispose methods on the intermediate IEnumerator objects, which in turn cause the SqlDataReader to be closed. You must also close the corresponding SqlConnection object, which is done by linking the closing of the database connection to the closing of the SqlDataReader:
command.ExecuteReader(CommandBehavior.CloseConnection)
To avoid keeping the connection open for too long, you should avoid complicated or time-consuming operations while iterating the resulting IEnumerable collection, and you should especially avoid any user interaction with the collection. For example, rewriting the previous example so the user can move on to the next record by pressing Enter would be bad for database performance, as shown here:
for row in contactsTable do
for col in row.Keys do
printfn "%s = %O" col (row.Item(col))
printfn "Press <enter> to see next record"
read_line() |> ignore
If you want to use the collection more than once or let the user interact with it, you should generally convert it to a list or an array; an example of this is as follows:
let contactsTable =
execCommand
"select * from Person.Contact"
"MyConnection"
let contactsList = Seq.to_list contactsTable
Although connections will be closed when the cursors are garbage collected, this generally takes too long, especially if a system is under stress. For example, if the code you are writing will run in a server application that will handle lots of concurrent users, then not closing connections will cause errors because the server will run out of database connections.
The EntLib Data Access Block
The Enterprise Library (EntLib) is a library produced by the Microsoft Patterns and Practices department and is available for download at http://www.microsoft.com. This section uses EntLib 2.0. It includes a data access block, which is designed to help programmers conform to best practices when writing data access code.
EntLib includes a configuration console, which allows you to configure connection strings without having to deal directly with the XML .config file. Figure 9-1 shows the configuration console.
Figure 9-1. The enterprise library configuration console
EntLib simplifies data access by allowing you to create an instance of the Database class that is based on the connection string and provider configured in the <connectionStrings> section in the .config file. This Database class then allows you to execute queries against the database with minimal effort. Further, because you don't directly create the ADO.NET objects, you can change which type of provider you want to use just by changing the configuration file. I'll discuss how this works in the section "ADO.NET Extensions" later in the chapter.
The following example shows how to use EntLib to execute a stored procedure, "uspGetBillOfMaterials", against the configured AdventureWorks database:
#light
#r "Microsoft.Practices.EnterpriseLibrary.Data.dll";;
open System
open Microsoft.Practices.EnterpriseLibrary.Data
let database = DatabaseFactory.CreateDatabase()
let reader = database.ExecuteReader(
"uspGetBillOfMaterials",
[| box 316; box (new DateTime(2006,1,1)) |])
while reader.Read() do
for x = 0 to (reader.FieldCount - 1) do
printfn "%s = %O"
(reader.GetName(x))
(reader.Item(x))
The results are as follows:
ProductAssemblyID = 316
ComponentID = 486
ComponentDesc = Metal Sheet 5
TotalQuantity = 1,00
StandardCost = 0,0000
ListPrice = 0,0000
BOMLevel = 4
RecursionLevel = 0
In my experience, EntLib can help you reduce the amount of data access code you need to write and assist you in changing between the types of databases you are using.
Data Binding
Data binding is the process of mapping a value or set of values to a user interface control. The data does not particularly need to be from a relational database, but it is generally from some system external to the program, and the process of accessing this data and transforming it into a state where it can be bound is more complicated than the binding itself, which is straightforward. This is why I cover this topic in this chapter rather than in Chapter 8. The next example shows how to bind data from a database table to a combo box:
#light
#r "Microsoft.Practices.EnterpriseLibrary.Data.dll";;
open System
open System.Collections.Generic
open System.Data
open System.Windows.Forms
open Microsoft.Practices.EnterpriseLibrary.Data
let opener commandString =
let database = DatabaseFactory.CreateDatabase()
database.ExecuteReader(CommandType.Text, commandString)
let generator (reader : IDataReader) =
if reader.Read() then
let dict = new Dictionary<string, obj>()
for x = 0 to (reader.FieldCount - 1) do
dict.Add(reader.GetName(x), reader.Item(x))
Some(dict)
else
None
let execCommand (commandString : string) =
Seq.generate_using
(fun () -> opener commandString)
(fun r -> generator r)
let contactsTable =
execCommand
"select top 10 * from Person.Contact"
let contacts =
[| for row in contactsTable ->
Printf.sprintf "%O %O"
(row.Item("FirstName"))
(row.Item("LastName")) |]
let form =
let temp = new Form()
let combo = new ComboBox(Top=8, Left=8, DataSource=contacts)
temp.Controls.Add(combo)
temp
Application.Run(form)
Figure 9-2 shows the resulting form.
Figure 9-2. A data-bound combo box
If you break the previous example down a bit, first you execute the query:
let contactsTable =
execCommand
"select top 10 * from Person.Contact"
You then need to turn the resulting IEnumerable collection into something suitable to be bound to the combo box; you do this by first grabbing the important members, then mapping them into a string collection, and finally converting it to an array. Then you must bind the collection to the control that will display it; you do this by setting the control's DataSource property:
combo.DataSource <- contacts
Although you've looked only at the ComboBox class, most Windows and web controls can be data bound in a similar way. These include the ListBox and CheckListBox classes. Next, you'll look at binding data to a more complicated control, the DataGridView class.
Data Binding and the DataGridView
The DataGridView control, unlike the controls you saw in the previous section, can display more than one column; the data must be formatted in such a way that the data grid knows which columns to display. You can achieve this in two ways. One is to bind the DataGridView to a DataTable. The other is to bind the grid to a list of objects that have properties; the various properties will become the grid's columns.
Binding to a DataSet is the simpler solution, as in the next example:
#light
#r "Microsoft.Practices.EnterpriseLibrary.Data.dll";;
open System
open System.Collections.Generic
open System.Data
open System.Windows.Forms
open Microsoft.Practices.EnterpriseLibrary.Data
let database = DatabaseFactory.CreateDatabase()
let dataSet = database.ExecuteDataSet
(CommandType.Text,
"select top 10 * from Person.Contact")
let form =
let temp = new Form()
let grid = new DataGridView(Dock = DockStyle.Fill)
temp.Controls.Add(grid)
grid.DataSource <- dataSet.Tables.Item(0)
temp
Application.Run(form)
Figure 9-3 shows the results from this example.
Figure 9-3. A data-bound data grid
An alternative to using a DataSet is to use an F# record type; to do this, you would generally create a generic function that uses reflection to create and populate your strongly typed collection. Here's an example of such a function:
#light
#r "Microsoft.Practices.EnterpriseLibrary.Data.dll";;
open System
open System.Collections.Generic
open System.Data
open System.Windows.Forms
open Microsoft.Practices.EnterpriseLibrary.Data
let execCommand<'a> commandString : seq<'a> =
let opener() =
let database = DatabaseFactory.CreateDatabase()
database.ExecuteReader(CommandType.Text, commandString)
let generator (reader : IDataReader) =
if reader.Read() then
let t = (type 'a)
let props = t.GetProperties()
let types =
props
|> Seq.map (fun x -> x.PropertyType)
|> Seq.to_array
let cstr = t.GetConstructor(types)
let values = Array.create reader.FieldCount (new obj())
reader.GetValues(values) |> ignore
let values =
values
|> Array.map
(fun x -> match x with | :? DBNull -> null | _ -> x)
Some (cstr.Invoke(values) :?> 'a)
else
None
Seq.generate_using
opener
generator
The first line of the sample uses a technique that you have not met before. Here you explicitly declare your function's type parameter:
let execCommand<'a> commandString : seq<'a>
You do this so you can explicitly give the generic argument 'a. This is used later in the function to create a type object that you then reflect over:
let t = (type 'a)
The function is designed to work with an F# record type, whose fields exactly match the fields resulting from the query. If this precondition is not met, then the code will fail, but such preconditions are typical in applications that use reflection in this way.
The function execCommand you have defined is generic and can be used with any query and matching record type. The following example shows how to apply it:
type Contact =
{
ContactID : Nullable<int> ;
NameStyle : Nullable<bool> ;
Title : string ;
FirstName : string ;
MiddleName : string ;
LastName : string ;
Suffix : string ;
EmailAddress : string ;
EmailPromotion : Nullable<int> ;
Phone: string ;
PasswordHash : string ;
PasswordSalt : string ;
AdditionalContactInfo : string ;
rowguid : Nullable<Guid> ;
ModifiedDate : Nullable<DateTime> ;
}
let form =
let temp = new Form()
let grid = new DataGridView(Dock = DockStyle.Fill)
temp.Controls.Add(grid)
let contacts =
execCommand<Contact> "select top 10 * from Person.Contact"
let contactsArray = contacts |> Seq.to_array
grid.DataSource <- contactsArray
temp
Application.Run(form)
The most important point is as follows:
let contacts =
execCommand<Contact> "select top 10 * from Person.Contact"Here you have explicitly declared the type parameter for the generic function execCommand. The results from this example are the same as the previous example and are shown in Figure 9-3.
ADO.NET Extensions
ADO.NET has been successful at providing a set of bases classes and interfaces that others have been able to implement to provide access to their relational database, so most relational databases can be accessed from F# with little effort. You have already met most of these classes, or at least classes that implement the functionality they are intended to provide, and Table 9-2 summarizes the key ones.
Table 9-2. The Key Classes in ADO.NET
The classes in Table 9-2, with the exception of System.Data.DataSet, are all abstract, so you must use concrete implementations of them. For example, here you create an instance of System.Data.SqlClient.SqlConnection, which is an implementation of System.Data.Common.DbConnection, which gives access to a SQL Server database:
using (new SqlConnection(connectionString))
If you wanted to access an Oracle database, you would simply replace the SqlConnection class with the OracleConnection class. Table 9-3 summarizes some of the most popular libraries and namespaces that implement these classes, although this table is incomplete because the range of providers is large.
Table 9-3. Database Providers for .NET
| Namespace | DLL | Description |
System.Data.Odbc |
System.Data.dll |
This namespaces allows you to connect to any database that provides drives that support the Open Database Connectivity standard. Most databases provide drivers that support this standard, but they should generally be avoided in favor of a more specific driver, which will probably be more efficient. |
System.Data.OleDb |
System.Data.dll |
OleDb is a COM-based standard for database drivers; again, a huge number of relational databases provide drivers that support this standard, but where possible you should use something more specific. This namespace is often used to connect to Access databases or Excel spreadsheets, which do not have .NET drivers of their own. |
System.Data. SqlClient |
System.Data.dll |
This is the native .NET Microsoft SQL Server driver. It will work with all supported versions of SQL Server and is the de facto choice when working with SQL Server. This has been the namespace used by the examples in this book. |
System.Data. OracleClient |
System.Data. OracleClient.dll |
This is the native .NET provider for the Oracle database created by Microsoft; it is distributed with the .NET Framework. |
Oracle.DataAccess. Client |
Oracle.DataAccess. Client.dll |
The Oracle data provider for .NET (ODP.NET) is a database provider for .NET developed by Oracle; it is available from www.oracle.com/technology/software/tech/windows/odpnet. |
IBM.Data.DB2 |
IBM.Data.DB2.dll |
This is the native .NET provider developed by IBM; it is provided with the distribution of the database. |
MySql.Data. MySqlClient |
MySql.Data.dll |
This is the open source native .NET provider created by the MySQL team. You can download it from dev.mysql.com/downloads/connector/net. |
FirebirdSql.Data. FirebirdClient |
FirebirdSql.Data. FirebirdClient.dll |
This is the native provider for the open source database Firebird; you can download it from www.firebirdsql.org/index.php?op=files&;id=netprovider. |
To demonstrate how to use the other .NET providers, I will now show an example of connecting to the Firebird employee sample database. To run this sample, you will need to install the Firebird database engine and Firebird .NET provider components from http://www.firebirdsql.org and be running the Firebird database service on your local machine.
#light
#I @"C:Program FilesFirebirdClient";;
#r @"FirebirdSql.Data.FirebirdClient.dll";;
open System.Configuration
open System.Collections.Generic
open System.Data
open FirebirdSql.Data.FirebirdClient;
open System.Data.Common
open System
let connectionString =
@"Database=C:Program FilesFirebird" +
@"Firebird_2_0examplesempbuildEMPLOYEE.FDB;" +
@"User=SYSDBA;" + "Password=masterkey;" +
@"Dialect=3;" + "Server=localhost";
let openFBConnection() =
let connection = new FbConnection (connectionString)
connection.Open();
connection
let openConnectionReader cmdString =
let conn = openFBConnection()
let cmd = conn.CreateCommand(CommandText=cmdString,
CommandType = CommandType.Text)
let reader = cmd.ExecuteReader(CommandBehavior.CloseConnection)
reader
let readOneRow (reader: #DbDataReader) =
if reader.Read() then
let dict = new Dictionary<string, obj>()
for x = 0 to (reader.FieldCount - 1) do
dict.Add(reader.GetName(x), reader.Item(x))
Some(dict)
else
None
let execCommand (cmdString : string) =
Seq.generate_using
// This function gets called to open a conn and create a reader
(fun () -> openConnectionReader cmdString)
// This function gets called to read a single item in
// the enumerable for a reader/conn pair
(fun reader -> readOneRow(reader))
let employeeTable =
execCommand
"select * from Employee"
for row in employeeTable do
for col in row.Keys do
printfn "%s = %O " col (row.Item(col))
The results of this example are as follows:
...
EMP_NO = 145
FIRST_NAME = Mark
LAST_NAME = Guckenheimer
PHONE_EXT = 221
HIRE_DATE = 02/05/1994 00:00:00
DEPT_NO = 622
JOB_CODE = Eng
JOB_GRADE = 5
JOB_COUNTRY = USA
SALARY = 32000
FULL_NAME = Guckenheimer, Mark
You will observe that very little changes were needed to convert the SQL Server Adventure-Works contact table example given earlier in the chapter to an example that executed a query against the Firebird employee example database.
Introducing LINQ
Language-Integrated Query (LINQ) is the next generation of .NET data access technology. It borrows heavily from functional programming, so it fits very nicely with F#.
Note All examples in this section and other sections about LINQ are based on the Community Technology Preview of May 2006, the Microsoft .NET LINQ Preview (May 2006), and the F# LINQ bindings that match this release. If you use the examples with later versions of LINQ, you will have to make changes to the code.
At its heart, LINQ is a set of libraries for manipulating collections that implement the IEnumerable<T> interface, a lot like F#'s Seq module, which was discussed in Chapter 7. The idea is that you can use this library to query any in-memory collection, whether the data comes from a database, an XML file, or just objects returned from another API.
Although the concepts implemented in the LINQ library will be familiar to you by now, they follow a slightly different naming convention that is based on SQL. For instance, the equivalent of Seq.map is called Sequence.Select, and the equivalent Seq.filter is called Sequence.Where. The next example shows how to use this library. The first step is to import the methods exposed by the LINQ library into a more usable form; this is how to do that:
#light
#I "C:Program FilesLINQ PreviewBin";;
#r "System.Query.dll";;
open System.Query
open System.Reflection
// define easier access to LINQ methods
let select f s = Sequence.Select(s, new Func<_,_> (f))
let where f s = Sequence.Where(s, new Func<_,_> (f))
let groupBy f s = Sequence.GroupBy(s, new Func<_,_> (f))
let orderBy f s = Sequence.OrderBy(s, new Func<_,_> (f))
let count s = Sequence.Count(s)
Once these functions have been imported, they can easily be applied, typically using the pipe forward operator. The following example demonstrates this. It uses the LINQ library to query the string class and group the overloads of its nonstatic methods together.
// query string methods using functions
let namesByFunction =
(type string).GetMethods()
|> where (fun m -> not m.IsStatic)
|> groupBy (fun m -> m.Name)
|> select (fun m -> m.Key, count m)
|> orderBy (fun (_, m) -> m)
namesByFunction
|> Seq.iter (fun (name, count) -> printfn "%s - %i" name count)
The results are as follows:
ToLowerInvariant - 1
TrimEnd - 1
GetHashCode - 1
TrimStart - 1
GetEnumerator - 1
GetType - 1
GetTypeCode - 1
ToUpperInvariant - 1
Clone - 1
CopyTo - 1
get_Length - 1
Insert - 1
get_Chars - 1
PadLeft - 2
CompareTo - 2
PadRight - 2
ToUpper - 2
ToLower - 2
ToString - 2
Trim - 2
Remove - 2
ToCharArray - 2
Substring - 2
IsNormalized - 2
Normalize - 2
Replace - 2
IndexOfAny - 3
EndsWith - 3
Equals - 3
StartsWith - 3
LastIndexOfAny - 3
Split - 6
LastIndexOf - 9
IndexOf - 9
Using LINQ to XML
The goal of LINQ to XML is to provide an XML object model that works well with LINQ's functional style of programming. Table 9-4 summarizes the important classes within this namespace.
Table 9-4. A Summary of the Classes Provided by LINQ to XML
| Class Name | Parent Class | Description |
XNode |
This class provides the basic functionality that is applicable to all nodes in an XML document. | |
XContainer |
XNode |
This class provides the functionality for XML nodes that can contain other nodes. |
XDocument |
XContainer |
This class represents the XML document as a whole. |
XElement |
XContainer |
This class represents an element in the XML document, that is, a regular XML node that can be a tag, <myTag />, or can possibly contain other tags or an attribute, such as myAttribute="myVal". |
XDocumentType |
XNode |
This class represents a document type tag. |
XProcessInstruction |
XNode |
This class represents a processing instruction, which is a tag of the form <? name instruction ?>. |
XText |
XNode |
This class represents text contained within the XML document. |
XName |
This class represents the name of a tag or an attribute. |
To show how to use this object model, you can revise the example from the previous section to output XML instead of plain text. LINQ to XML makes this easy to do; first you modify the select statement to return an XElement instead of a tuple:
|> select (fun m -> new XElement(XName.Get(m.Key), count m))
This gives an array of XElements that you can then use to initialize another XElement, which provides the root of the document. It is then just a matter of calling the root XElement's ToString method, which will provide the XML in the form of a string.
#light
#I "C:Program FilesLINQ PreviewBin";;
#r "System.Query.dll";;
#r "System.Xml.XLinq.dll";;
open System.Query
open System.Reflection
open System.Xml.XLinq
// define easier access to LINQ methods
let select f s = Sequence.Select(s, new Func<_,_> (f))
let where f s = Sequence.Where(s, new Func<_,_> (f))
let groupBy f s = Sequence.GroupBy(s, new Func<_,_> (f))
let orderBy f s = Sequence.OrderBy(s, new Func<_,_> (f))
let count s = Sequence.Count(s)
// query string methods using functions
let namesByFunction =
(type string).GetMethods()
|> where (fun m -> not m.IsStatic)
|> groupBy (fun m -> m.Name)
|> select (fun m -> new XElement(XName.Get(m.Key), count m))
|> orderBy (fun e -> int_of_string e.Value)
let overloadsXml =
new XElement(XName.Get("MethodOverloads"), namesByFunction)
print_endline (overloadsXml.ToString())
The results of this code, when compiled and executed, are as follows:
<MethodOverloads>
<Contains>1</Contains>
<ToLowerInvariant>1</ToLowerInvariant>
<TrimEnd>1</TrimEnd>
<GetHashCode>1</GetHashCode>
<TrimStart>1</TrimStart>
<GetEnumerator>1</GetEnumerator>
<GetType>1</GetType>
<GetTypeCode>1</GetTypeCode>
<ToUpperInvariant>1</ToUpperInvariant>
<Clone>1</Clone>
<CopyTo>1</CopyTo>
<get_Length>1</get_Length>
<Insert>1</Insert>
<get_Chars>1</get_Chars>
<PadLeft>2</PadLeft>
<CompareTo>2</CompareTo>
<PadRight>2</PadRight>
<ToUpper>2</ToUpper>
<ToLower>2</ToLower>
<ToString>2</ToString>
<Trim>2</Trim>
<Remove>2</Remove>
<ToCharArray>2</ToCharArray>
<Substring>2</Substring>
<IsNormalized>2</IsNormalized>
<Normalize>2</Normalize>
<Replace>2</Replace>
<IndexOfAny>3</IndexOfAny>
<EndsWith>3</EndsWith>
<Equals>3</Equals>
<StartsWith>3</StartsWith>
<LastIndexOfAny>3</LastIndexOfAny>
<Split>6</Split>
<LastIndexOf>9</LastIndexOf>
<IndexOf>9</IndexOf>
</MethodOverloads>
Using LINQ to SQL
LINQ to SQL is designed to allow data access to relational databases. It does this through a combination of code generation and the ability to quote code. For the code generation, LINQ to SQL provides a tool called SqlMetal.exe, which will generate a code version of your relational database; this code version of your database is an object model where the tables become classes with properties representing their columns. Quoting code means the ability to have the compile transform a section of code into data structure called an expression tree; you first came across this technique in Chapter 6, but this is the first time you will see it put into real use. The quoted code will be transformed into SQL that can then be executed on the database.
The first step in working with LINQ to ADO.NET is to generate a code version of your database. To do this, you would use the following command line, which generates a code version of the AdventureWorks database that is running on the local version of SQL Server Express:
SqlMetal.exe /server:.SQLEXPRESS /database:AdventureWorks /code:AdWorks.cs
/language:csharp
The C# classes that represent the database's objects are generated, but you do not need to know C# to work with it, because you only ever use a compiled version of the code, just like using any other .NET library.
This example relies on an example LINQ library distributed with F# that you can find in the samplesfsharpFLinq directory of the distribution. This library also provides useful functions that your DLINQ library requires; first it imports the .NET methods, and then it transforms them into F# methods. The other function it provides is to transform the F# expression trees into the trees used by DLINQ. The library is distributed as a sample, rather than being integrated into the F# libraries, because the F# team did not want to introduce a binary dependency onto something as experimental as LINQ. Once LINQ is released and part of the .NET Framework, the functionalities provided by this sample library will be migrated into the F# libraries.
The following example shows how to use DLINQ in F#. It shows two powerful features of DLINQ: the ability to have your F# code transformed into a SQL query and the ability to create instances of rows without having to use reflection, like you did for the data binding example earlier in the chapter:
#light
#r "Microsoft.Practices.EnterpriseLibrary.Data.dll";;
#r "flinq.dll";;
#r "AdventureWorks.dll";;
#r "System.Data.DLinq.dll";;
#r "System.Query.dll";;
open System.Windows.Forms
open Microsoft.FSharp.Quotations.Typed
open Microsoft.FSharp.Bindings.DLinq.Query
open Microsoft.Practices.EnterpriseLibrary.Data
module sOps = Microsoft.FSharp.Bindings.Linq.SequenceOps
let database = DatabaseFactory.CreateDatabase()
let adventureWorks = new AdventureWorks(database.CreateConnection())
type Person =
{ Title : string ;
FirstName : string ;
LastName : string ; }
let contacts =
adventureWorks.Person.Contact
|>where « fun c -> c.FirstName = "Robert" »
|>sOps.select
(fun c ->
{ Title = c.Title ;
FirstName = c.FirstName;
LastName = c.LastName })
|>Seq.to_array
let form =
let temp = new Form()
let grid = new DataGridView(Dock = DockStyle.Fill)
temp.Controls.Add(grid)
grid.DataSource <- contacts
temp
Application.Run(form)Caution If you want to use guillemets in your code, as in the expression « fun c -> c.FirstName = "Robert" », then you must save the file as UTF-8.
Figure 9-4 shows that the results from both examples are the same.
Figure 9-4. Data grid containing the results of a DLINQ query
Summary
This chapter has looked at the options for data access in F#. It has shown that the combination of F# with .NET libraries is powerful yet straightforward, no matter what your data source is. The next chapter will look at the related topic of how applications exchange data to become distributed applications.