Data binding and Data grid view (DataGridView) controls

Data binding and Data grid view (DataGridView) controls

The Data grid view control, unlike the control we saw earlier, can display multiple columns, but the data must be formatted so that the data grid knows which column to display. There are two ways to do this: one is to bind the data grid view to a data table (DataTable), the other is to put the grid into a list of bound objects, the objects have many properties, and the different properties become columns of the grid.

The following example is a simple solution that binds to a dataset (dataset):

Open System

Open System.Collections.Generic

Open System.Configuration

Open System.Data

Open System.Data.SqlClient

Open System.Windows.Forms

Creates a connections then executes Thegiven command on it

Let CreateDataSet commandstring =

Read the connection string

Letconnectionsetting =

Configurationmanager.connectionstrings. ["MyConnection"]

Create a data adapter to fill the dataset

Letadapter = New SqlDataAdapter (CommandString, connectionsetting.connectionstring)

Create a new data set and fill it

Letds = new DataSet ()

Adapter. Fill (DS) |> ignore

Ds

Create the data set that would be boundto the form

let datasets = CreateDataSet "Selecttop * from Person.Contact"

Create a form containing a data bounddata grid view

Let form =

Lettemp = new Form ()

Letgrid = new DataGridView (Dock = DockStyle.Fill)

Temp. Controls.Add (GRID)

Grid. datasource<-Dataset.tables. [0]

Temp

Show the form

Application.Run (Form)

Run the previous code and you can see the results shown in 9-2.

Figure 9-2 Data grid with data bound

If you do not use a DataSet, you can also use F # record types. In this way, it is often necessary to create a generic function (generic

function) to create and publish a strongly typed collection through reflection (reflection). The following code demonstrates this generic function, wrapping it in a module so that it is easier to use for other code, and then executes a query against the database using this module:

Module Strangelights.datatools

Open System

Open System.Collections.Generic

Open System.Configuration

Open System.Data

Open System.Data.SqlClient

Open Microsoft.FSharp.Reflection

A command that returns dynamicallycreated stongly typed collection

Let execcommand< ' a> commandstring:seq< ' a> =

The opener that executes the command

Letopener () =

Read the connection string

Letconnectionsetting =

Configurationmanager.connectionstrings. ["MyConnection"]

Create the connection and open it

Letconn = new SqlConnection (connectionsetting.connectionstring)

Conn. Open ()

Excute the command, ensuring the read would close the connection

LETCMD = conn. CreateCommand (CommandType = CommandType.Text,

CommandText = commandstring)

Cmd. ExecuteReader (commandbehavior.closeconnection)

The generator, that generates anstrongly typed object for each row

Let generator (reader:idatareader) =

Ifreader. Read () Then

Get the type object and its properties

Lett = typeof< ' a>

Get the values for the row from the reader

Letvalues = Array.create Reader. FieldCount (New obj ())

Reader. GetValues (values) |> ignore

Letconvertvals x = Match box x with | :? DBNull, NULL | _-X

Letvalues = Array.map convertvals values

Create the record and return it

Some (Fsharpvalue.makerecord (t, values):?> ' a)

Else

None

Generate the sequence

Seq.generate

Opener

Generator

(Funr-R.dispose ())

The first line of the example code uses a method that we have not used before, and explicitly declares the type parameter of the function:

Let execcommand< ' a> commandstring:seq< ' a>

In doing so, you can explicitly give the generic parameter ' A, which is then used to create the type object and then reflect on it:

Let T = typeof< ' a>

This function is used to process the F # record type whose fields match the query results exactly, and if the prerequisites are not met, the code fails; However, this prerequisite is usually used in reflection in the application.

The previously defined generic function execcommand, which can be used with any query, matches the record type. The following code shows how to apply:

Open System

Open System.Windows.Forms

Open Strangelights.datatools

A type that mirrors the type of rowbeing created

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>}

A form containing a data bound data grid

Let form =

Lettemp = new Form ()

Letgrid = new DataGridView (Dock = DockStyle.Fill)

Temp. Controls.Add (GRID)

Letcontacts =

Execcommand<contact> "SELECT Top ten * from Person.Contact"

Letcontactsarray = Contacts |> Seq.to_array

Grid. datasource<-Contactsarray

Temp

Show the form

Application.Run (Form)

The most important is the following line:

Let contacts =

Execcommand<contact> "SELECT Top ten * from Person.Contact"

The type parameter is explicitly declared for the generic function execcommand. The results of this example are shown in the previous example, 9-2.

Attention

Using object-relational mappings, scale nhibernate, and the ability to perform such tasks are already quite common, and these tools provide a high degree of flexibility, but they tend to be overly dependent on the functionality of Pure C #, which is not a good experience to use in F #, so I don't discuss anything in the book. However, many people, including myself, are trying to solve this problem, and I suggest you follow my blog: Http://strangelights.com/blog.