Binding a Record Value To a Control (OleDbDataAdapter Visual F#)

To bind a record value to a control, simply use the DataBindings method of the control which has the following syntax:

view plainprint?

1. controlobjvariable.DataBindings.Add(new Binding(controlproperty, datasetname, fieldname))  

controlobjvariable.DataBindings.Add(new Binding(controlproperty, datasetname, fieldname))

For instance:

view plainprint?

1. fnamelabel.DataBindings.Add(new Binding("Text", dataset11, "Table1.fname", true))  

fnamelabel.DataBindings.Add(new Binding("Text", dataset11, "Table1.fname", true))

For a simple data binding example, follow these steps:

Before the Database Connection and Binding process I want you to make an Ms-Access database file named “dbEmployee” containing a table named “tblEmployee”. Use the following specifications:

Field Name	Data Type	Description
chrempno	text	Handles employee id
chrfname	text	Handles employee’s name
chrlname	text	Holds employee’s last name

After designing the structure of your table, you can enter appropriate values for each field. For instance:

chrempno	chrfname	Chrlname
2010A	John	Doe
2010B	Jean	Doe

Now that we are done creating a table, we can now link to it by using OleDbDataAdapter in Visual F#:


1. Click Start>All Programs>Microsoft Visual Studio 2008>Microsoft Visual Studio 2008.

2. Click File>New>Project>Select Visual F# in the project types>Select F# application in the Visual Studio installed templates category.

3. Click the Project menu>Add reference>Click the .Net tab>Locate then double-click System.Windows.Forms. Do step 3 again and this time, select System.Drawing and System.Data from the .Net tab.

4. Enter the following code after the line “// Learn more about F# at http://fsharp.net “:

view plainprint?

1. // Learn more about F# at http://fsharp.net  
2. //specifies the memory location of the class files  
3. //that will be needed in our application  
4. open System  
5. open System.Windows.Forms  
6. open System.Data  
7. open System.Drawing  
8. //creates a font  
9. let ffont=new Font("Verdana", 9.75F,FontStyle.Regular, GraphicsUnit.Point)    
10. //creates a connection object  
11. let oleconn = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;  
12.   Data Source=C:\Documents and Settings\station 2\My Documents\dbEmployee.mdb")  
13.  //creates an OleDbDataAdapter  
14. let dataadpter = new System.Data.OleDb.OleDbDataAdapter("Select * from tblEmployee", oleconn)  
15. //generates a dataset  
16. let dataset11 = new DataSet()  
17. //fills the dataset with recod values  
18. dataadpter.Fill(dataset11,"tblEmployee")|>ignore  
19. //creates a form  
20. let dataform = new Form(Text="Use OleDbDataAdapter",AutoScaleDimensions=new System.Drawing.SizeF(60.0F, 13.0F),ClientSize=new System.Drawing.Size(300, 200),StartPosition=FormStartPosition.CenterScreen)  
21. //creates our controls  
22. let exitbutton=new Button(Text="Exit", Location=new System.Drawing.Point(190, 170))    
23. let label1=new Label(Text="Employee number:",Location=new System.Drawing.Point(0, 10),AutoSize=true)  
24. let label2=new Label(Text="Firstname:",Location=new System.Drawing.Point(0, 50),AutoSize=true)  
25. let label3=new Label(Text="Lastname:",Location=new System.Drawing.Point(0,100),AutoSize=true)  
26. let emplabel=new Label(Location=new System.Drawing.Point(140,10),BorderStyle=BorderStyle.FixedSingle)  
27. let fnamelabel=new Label(Location=new System.Drawing.Point(100,50),BorderStyle=BorderStyle.FixedSingle)  
28. let lnamelabel=new Label(Location=new System.Drawing.Point(100,100),BorderStyle=BorderStyle.FixedSingle)  
29. //assings the font to our form  
30. dataform.Font<-ffont  
31. //adds the controls to our form  
32. dataform.Controls.Add(exitbutton)  
33. dataform.Controls.Add(label1)  
34. dataform.Controls.Add(label2)  
35. dataform.Controls.Add(label3)  
36. dataform.Controls.Add(emplabel)  
37. dataform.Controls.Add(fnamelabel)  
38. dataform.Controls.Add(lnamelabel)  
39. //binds the fieldnames to our label  
40. emplabel.DataBindings.Add(new Binding("Text",dataset11,"tblEmployee.chrempno"))  
41. fnamelabel.DataBindings.Add(new Binding("Text",dataset11,"tblEmployee.chrfname"))  
42. lnamelabel.DataBindings.Add(new Binding("Text",dataset11,"tblEmployee.chrlname"))  
43. //when the exit button is clicked  
44. exitbutton.Click.Add(fun exit->  
45. //close the form and dataconnection  
46.                     dataform.Close()  
47.                     oleconn.Close())  
48.   
49. //executes our application  
50. dataform.Show()  
51. Application.Run(dataform)  

// Learn more about F# at http://fsharp.net
//specifies the memory location of the class files
//that will be needed in our application
open System
open System.Windows.Forms
open System.Data
open System.Drawing
//creates a font
let ffont=new Font("Verdana", 9.75F,FontStyle.Regular, GraphicsUnit.Point)  
//creates a connection object
let oleconn = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;
  Data Source=C:\Documents and Settings\station 2\My Documents\dbEmployee.mdb")
 //creates an OleDbDataAdapter
let dataadpter = new System.Data.OleDb.OleDbDataAdapter("Select * from tblEmployee", oleconn)
//generates a dataset
let dataset11 = new DataSet()
//fills the dataset with recod values
dataadpter.Fill(dataset11,"tblEmployee")|>ignore
//creates a form
let dataform = new Form(Text="Use OleDbDataAdapter",AutoScaleDimensions=new System.Drawing.SizeF(60.0F, 13.0F),ClientSize=new System.Drawing.Size(300, 200),StartPosition=FormStartPosition.CenterScreen)
//creates our controls
let exitbutton=new Button(Text="Exit", Location=new System.Drawing.Point(190, 170))  
let label1=new Label(Text="Employee number:",Location=new System.Drawing.Point(0, 10),AutoSize=true)
let label2=new Label(Text="Firstname:",Location=new System.Drawing.Point(0, 50),AutoSize=true)
let label3=new Label(Text="Lastname:",Location=new System.Drawing.Point(0,100),AutoSize=true)
let emplabel=new Label(Location=new System.Drawing.Point(140,10),BorderStyle=BorderStyle.FixedSingle)
let fnamelabel=new Label(Location=new System.Drawing.Point(100,50),BorderStyle=BorderStyle.FixedSingle)
let lnamelabel=new Label(Location=new System.Drawing.Point(100,100),BorderStyle=BorderStyle.FixedSingle)
//assings the font to our form
dataform.Font<-ffont
//adds the controls to our form
dataform.Controls.Add(exitbutton)
dataform.Controls.Add(label1)
dataform.Controls.Add(label2)
dataform.Controls.Add(label3)
dataform.Controls.Add(emplabel)
dataform.Controls.Add(fnamelabel)
dataform.Controls.Add(lnamelabel)
//binds the fieldnames to our label
emplabel.DataBindings.Add(new Binding("Text",dataset11,"tblEmployee.chrempno"))
fnamelabel.DataBindings.Add(new Binding("Text",dataset11,"tblEmployee.chrfname"))
lnamelabel.DataBindings.Add(new Binding("Text",dataset11,"tblEmployee.chrlname"))
//when the exit button is clicked
exitbutton.Click.Add(fun exit->
//close the form and dataconnection
                    dataform.Close()
                    oleconn.Close())

//executes our application
dataform.Show()
Application.Run(dataform)

5. Click the run icon to execute your application. You should now see an output similar to the following screen shot:

Linking to an Ms-Access DataSource using OledbDataAdapter in Visual F#

To link to an Ms-Access data source, the first thing that you’ll need to do is to create an object variable based on the OleDbConnection class. OleDbConnection specifies the name of the Data Provider and the Location of the database file that you wish to link to. To create an OleDbConnection object use the following syntax:

view plainprint?

1. let Oledbconnectionobjvariable = new System.Data.OleDb.OleDbConnection("Provider=DataProviderName;  
2. Data Source=Url and filename of the database file")  

let Oledbconnectionobjvariable = new System.Data.OleDb.OleDbConnection("Provider=DataProviderName;
Data Source=Url and filename of the database file")

For instance:

view plainprint?

1. let Oledbconnectionobjvariable = new System.Data.OleDb.OleDbConnection("Provider= Microsoft.Jet.OLEDB.4.0;  
2. Data Source=C:\Mydatabasefile.mdb")  

let Oledbconnectionobjvariable = new System.Data.OleDb.OleDbConnection("Provider= Microsoft.Jet.OLEDB.4.0;
Data Source=C:\Mydatabasefile.mdb")

Microsoft.Jet.OLEDB.4.0 is a data provider used to link to Ms-Access 2003 or higher database files.
After creating an OledbConnectionObject, the next thing that you’ll need to do is to add an OleDbDataAdapter control, to specify how the data will be displayed on your form and the information about the connection. This can be done by using the following syntax:

view plainprint?

1. let oledbdataadapterobjvariable= new System.Data.OleDb. OleDbDataAdapter("SQL statement", ConnectionString)  

let oledbdataadapterobjvariable= new System.Data.OleDb. OleDbDataAdapter("SQL statement", ConnectionString)

ConnectionString refers to the information about the connection and is saved in the Oledbconnection object.

Lastly, youll need to create a storage location for your table values. This can be done by creating a Dataset. To create a dataset, use the following syntax:

view plainprint?

1. Let Datasetobjvariable=new Dataset()  

Let Datasetobjvariable=new Dataset()

For a simple example on using OleDbDataAdapter to an AccessDataSource, follow these steps:

Before the Database Connection and Binding process I want you to make an Ms-Access database file named “dbEmployee” containing a table named “tblEmployee”. Use the following specifications:

Field Name	Data Type	Description
chrempno	text	Handles employee id
chrfname	text	Handles employee’s name
chrlname	text	Holds employee’s last name

After designing the structure of your table, you can enter appropriate values for each field. For instance:

chrempno	chrfname	Chrlname
2010A	John	Doe
2010B	Jean	Doe

Now that we are done creating a table, we can now link to it by using OleDbDataAdapter in Visual F#:


1. Click Start>All Programs>Microsoft Visual Studio 2008>Microsoft Visual Studio 2008.

2. Click File>New>Project>Select Visual F# in the project types>Select F# application in the Visual Studio installed templates category.

3. Click the Project menu>Add reference>Click the .Net tab>Locate then double-click System.Windows.Forms. Do step 3 again and this time, select System.Drawing and System.Data from the .Net tab.

4. Enter the following code after the line “// Learn more about F# at http://fsharp.net “:

view plainprint?

1. // Learn more about F# at http://fsharp.net  
2. //specifies the memory location of the class files  
3. //that will be needed in our application  
4. open System  
5. open System.Windows.Forms  
6. open System.Data  
7. open System.Drawing  
8. //creates a font  
9. let ffont=new Font("Verdana", 9.75F,FontStyle.Italic, GraphicsUnit.Point)    
10. //creates a connection object  
11. let oleconn = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;  
12. Data Source=C:\Documents and Settings\station 2\My Documents\dbEmployee.mdb")  
13. //creates an OleDbDataAdapter  
14. let dataadpter = new System.Data.OleDb.OleDbDataAdapter("Select * from tblEmployee", oleconn)  
15. //generates a dataset  
16. let dataset11 = new DataSet()  
17. //fills the dataset with recod values  
18. dataadpter.Fill(dataset11,"tblEmployee")|>ignore  
19. //creates a form  
20. let dataform = new Form(Text="Use OleDbDataAdapter",AutoScaleDimensions=new System.Drawing.SizeF(60.0F, 13.0F),ClientSize=new System.Drawing.Size(300, 200),StartPosition=FormStartPosition.CenterScreen)  
21. //creates an exit button  
22. let exitbutton=new Button(Text="Exit", Location=new System.Drawing.Point(190, 170))    
23. //creates a gridview control  
24. let datagrid = new DataGridView(Dock=DockStyle.None)  
25. //opens the connection to the datasource  
26. oleconn.Open()  
27. //assign our table  
28. //as a datagrid datasource  
29. datagrid.DataSource <- dataset11.Tables.["tblEmployee"]  
30. //add the controls to our form  
31. dataform.Font<-ffont  
32. dataform.Controls.Add(datagrid)  
33. dataform.Controls.Add(exitbutton)  
34. //when the exit button is clicked  
35. exitbutton.Click.Add(fun exit->  
36. //close the form and dataconnection  
37. dataform.Close()  
38. oleconn.Close())  
39.   
40. //executes our application  
41. dataform.Show()  
42. Application.Run(dataform)  

// Learn more about F# at http://fsharp.net
//specifies the memory location of the class files
//that will be needed in our application
open System
open System.Windows.Forms
open System.Data
open System.Drawing
//creates a font
let ffont=new Font("Verdana", 9.75F,FontStyle.Italic, GraphicsUnit.Point)  
//creates a connection object
let oleconn = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;
Data Source=C:\Documents and Settings\station 2\My Documents\dbEmployee.mdb")
//creates an OleDbDataAdapter
let dataadpter = new System.Data.OleDb.OleDbDataAdapter("Select * from tblEmployee", oleconn)
//generates a dataset
let dataset11 = new DataSet()
//fills the dataset with recod values
dataadpter.Fill(dataset11,"tblEmployee")|>ignore
//creates a form
let dataform = new Form(Text="Use OleDbDataAdapter",AutoScaleDimensions=new System.Drawing.SizeF(60.0F, 13.0F),ClientSize=new System.Drawing.Size(300, 200),StartPosition=FormStartPosition.CenterScreen)
//creates an exit button
let exitbutton=new Button(Text="Exit", Location=new System.Drawing.Point(190, 170))  
//creates a gridview control
let datagrid = new DataGridView(Dock=DockStyle.None)
//opens the connection to the datasource
oleconn.Open()
//assign our table
//as a datagrid datasource
datagrid.DataSource <- dataset11.Tables.["tblEmployee"]
//add the controls to our form
dataform.Font<-ffont
dataform.Controls.Add(datagrid)
dataform.Controls.Add(exitbutton)
//when the exit button is clicked
exitbutton.Click.Add(fun exit->
//close the form and dataconnection
dataform.Close()
oleconn.Close())

//executes our application
dataform.Show()
Application.Run(dataform)

5. Click the run icon to execute your application. You should now see an output similar to the following screen shot: