Partager via

Use a different kind of grid in your applications

  • Article

My prior post (Create a .Net UserControl that calls a web service that acts as an ActiveX control to use in Excel, VB6, Foxpro) shows how to create a .Net control that you can use in VFP or VB6 that takes advantage of the .Net framework.

Here’s an example of how to use the same steps to create a DataGridView ActiveX control that you can populate using an ADO RecordSet. Follow the steps of the prior post, but instead of adding a reference to a web service, add a reference to .Net ADODB.( C:\Program Files\Microsoft.NET\Primary Interop Assemblies\adodb.dll)

Then add a button and a DataGridView. Change the Anchor property of the DataGridView to Top,Left,Bottom,Right.

The button code gets all the Customer data into an ADODB Recordset on the .Net side using the VFP OleDB provider. The Fox form code gets all the Orders data from the OleDB provider, then calls the SetRS method to send the data to the .Net side, which converts the Recordset to a DataTable,which can then be bound to the DataGridView.

The Fox code sets the Anchor of the control, so when the form resizes, the grid resizes too. It also adds a Fox button and a Fox textbox, so you can experiment with how focus changes between the ActiveX Control and the Fox native controls with the mouse and keyboard (Tab, Ctrl-Tab, arrow keys)

Notice how the column colors are changed based on the data. It’s a little different from the VFP way using DynamicBackColor, DynamicForecolor. Try clicking on the column headers to sort the columns, or rearrange the order of the columns by dragging/dropping the headers.

If you hit the control’s button, the data will switch to the Customer table, showing how the data can be populated from either .Net or VFP code.

For another DataGridView sample, see The VB version of the Blog Crawler

The VB.Net code (be sure to change the data paths to your machine):

Imports System.Runtime.InteropServices

<Microsoft.VisualBasic.ComClass()> Public Class VBNetCtrlGrid

    Private _TestString As String

    Public Sub SetRs(ByVal rs As Object)

        Me.DataGridView1.AutoGenerateColumns = True

        Me.DataGridView1.AllowUserToOrderColumns = True

        Me.DataGridView1.DataSource = RStoDT(CType(rs, ADODB.Recordset))

        Me.DataGridView1.AutoResizeColumns()

    End Sub

    Function RStoDT(ByVal rs As ADODB.Recordset) As DataTable

        Dim dt As New DataTable("Test")

        For Each fld As ADODB.Field In rs.Fields

            Dim ttype As Type = Nothing

            Select Case fld.Type

                Case ADODB.DataTypeEnum.adNumeric

                    ttype = GetType(Double)

                Case ADODB.DataTypeEnum.adCurrency

                    ttype = GetType(Decimal)

                Case ADODB.DataTypeEnum.adChar

                    ttype = GetType(String)

                Case ADODB.DataTypeEnum.adWChar

                    ttype = GetType(String)

                Case ADODB.DataTypeEnum.adInteger

                    ttype = GetType(Integer)

                Case ADODB.DataTypeEnum.adDBTimeStamp

                    ttype = GetType(DateTime)

                Case ADODB.DataTypeEnum.adDBDate

    ttype = GetType(DateTime)

                Case Else

                    System.Diagnostics.Debug.Assert(False)

            End Select

            dt.Columns.Add(New DataColumn(fld.Name, ttype))

        Next

        Dim vals(rs.Fields.Count - 1) As Object

        Do While Not rs.EOF

            Dim nFldCnt = 0

            For Each fld As ADODB.Field In rs.Fields

                vals(nFldCnt) = fld.Value

                nFldCnt += 1

            Next

            dt.Rows.Add(vals)

            rs.MoveNext()

        Loop

        Return dt

    End Function

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        Dim oConn As New ADODB.Connection

        oConn.Open("Provider=vfpoledb.1;data source=d:\fox90\samples\data\testdata.dbc")

        Dim oRS As ADODB.Recordset = oConn.Execute("select * from customer")

        Me.SetRs(oRS)

    End Sub

    <ComRegisterFunction()> _

    Public Shared Sub Register(ByVal t As Type)

        ComRegistration.RegisterControl(t)

    End Sub

    <ComUnregisterFunction()> _

    Public Shared Sub Unregister(ByVal t As Type)

        ComRegistration.UnregisterControl(t)

    End Sub

    Private Sub DataGridView1_CellFormatting(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellFormattingEventArgs) Handles DataGridView1.CellFormatting

        If Me.DataGridView1.Columns(e.ColumnIndex).Name.ToLower = "cust_id" Then

            If e.Value IsNot Nothing AndAlso e.Value.ToString < "M" Then

                e.CellStyle.BackColor = Color.CadetBlue

                e.CellStyle.SelectionBackColor = Color.Blue

                e.CellStyle.ForeColor = Color.Crimson

            End If

        End If

    End Sub

End Class

The Fox code:

CLEAR ALL

CLEAR

PUBLIC ox

ox=CREATEOBJECT("myform")

ox.show

DEFINE CLASS myform AS form

          left=300

          allowoutput=.f.

          width=800

          height=600

          ADD OBJECT cmd as commandbutton WITH caption="Click"

          ADD OBJECT txt as textbox WITH left=121

          PROCEDURE Init

                   this.AddObject("oc","myoc")

                   this.oc.width=thisform.Width

                   this.oc.height=thisform.Height

                   this.oc.visible=1

                   LOCAL oConn as ADODB.Connection

                   LOCAL oRS as adodb.recordset

                   oConn=CREATEOBJECT("adodb.connection")

                   *Change path and query to data on your machine

                   oConn.open("Provider=vfpoledb.1;data source=d:\fox90\samples\data\testdata.dbc")

                   oRS= oconn.Execute("select * from orders")

                   this.oc.object.SetRs(ors)

ENDDEFINE

DEFINE CLASS myoc AS olecontrol

          oleclass="VBNetCtrlGrid.VBNetCtrlGrid"

          PROCEDURE init

                   this.top=30

                   this.width=thisform.width

                   this.height = thisform.height-30

                   this.anchor=15

ENDDEFINE

Comments