none
How is this done? RRS feed

  • Question

  • I have a datagridview that I am changing from using a dataset to using an SQL connection method.  For displaying the DGV, using a dataset, I have been using the following code.

    So my question is, can I display the DGV using something similar to the code here without using a dataset?

                With frmProcessList.dgvProcessList
                    .ReadOnly = False
                    .AllowUserToAddRows = False
                    .AllowUserToDeleteRows = False
                    .AllowUserToResizeColumns = False
                    .AllowUserToResizeRows = False
                    'Columns
                    .Size = New Size(445, 375)
                    .Location = New Point(8, 8)
                    'Cells
                    .AutoSizeRowsMode = DataGridViewAutoSizeRowsMode.AllCells
                    '.DataSource = frmProcessList._MasterBase_1_1DataSet
                    .DataMember = "tblProcessMaster"
                    .Columns(0).Visible = True
                    .Columns(0).HeaderText = "Process" & vbNewLine & "ID"
                    .Columns(0).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
                    .Columns(0).DefaultCellStyle.WrapMode = DataGridViewTriState.True
                    .Columns(0).Width = 100
                    .Columns(0).DisplayIndex = 0
                    .Columns(1).Visible = False
                    .Columns(2).Visible = True
                    .Columns(2).HeaderText = "Title/Name"
                    .Columns(2).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleLeft
                    .Columns(2).DefaultCellStyle.WrapMode = DataGridViewTriState.True
                    .Columns(2).Width = 250
                    .Columns(2).DisplayIndex = 1
                    .Columns(3).Visible = True
                    .Columns(3).HeaderText = "Revision"
                    .Columns(3).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
                    .Columns(3).DefaultCellStyle.WrapMode = DataGridViewTriState.True
                    .Columns(3).Width = 75
                    .Columns(3).DisplayIndex = 2
                    .Columns(4).Visible = False
                    .Columns(5).Visible = False
                    .Columns(6).Visible = False
                    .Columns(7).Visible = False
                    .Columns(8).Visible = False
                    .Columns(9).Visible = False
                    .Columns(10).Visible = False
                    .Columns(11).Visible = False
                    .Columns(12).Visible = False
                    .Columns(13).Visible = False
                    .Columns(14).Visible = False
                    .Columns(15).Visible = False
                End With


    gwboolean

    Thursday, August 9, 2018 11:39 PM

All replies

  • Hi

    Do you want to populate the DataGridview  without a Dataset?

    I hope the following code can help you.

     Using command As SqlCommand = New SqlCommand("select * from TableName", Conn)
            Dim dr As SqlDataReader = command.ExecuteReader()
            Dim bs As BindingSource = New BindingSource()
            bs.DataSource = dr
            Me.dataGridView1.DataSource = bs
        End Using
    

    Best Regards,

    Alex


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, August 10, 2018 1:25 AM
  • What I think you are telling me is that to use the display method I have above I would need to setup a dataset?

    I can do that.

     

    gwboolean

    Friday, August 10, 2018 1:42 AM
  • Hi,

    My method does not need to set a Dataset, is this not what you need?
    Do you want to set up a Dataset? Please tell me in detail.

    Best Regards,

    Alex


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, August 10, 2018 5:18 AM
  • Actually, I am looking at how to move away from datasets, but still use the same code I already have to display the data in the form.

    I have not yet gotten back to work on this, so I have not yet figured it all out (It generally takes me awhile to fully understand what is going on).  But yes, I think that will work.  


    gwboolean

    Friday, August 10, 2018 3:15 PM
  • My interpretation here is you may be looking for alternates to DataSet or DataTable but could be wrong. The following is a adaptation of code I wrote for using Entity Framework and DTO (Data Transfer Object) and applied it to working with a native data provider. A requirement is using the following component.

    Note this is a simple example which uses classes rather than DataSet or DataTable, it's not a full example but enough to get the idea, read data into a list (can be expanded to have relational tables). Here there I show read, add and modify.

    Class represents a simple table in a database table.

    Partial Public Class Customer
        Public Property Id As Integer
        Public Property FirstName As String
        Public Property LastName As String
        Public Property Address As String
        Public Property City As String
        Public Property State As String
        Public Property ZipCode As String
        Public Property AccountNumber As String
        Public Property CustomerState() As CustomerState
    
        'Public Overridable Property Orders As ICollection(Of Order) = New HashSet(Of Order)
    End Class
    

    An enum which mirrors the enum for DataRow GetChanges.

    Public Enum CustomerState
        Added
        Modified
        Deleted
        Detached
        Unchanged
    End Enum

    Exception handler

    Imports System.Data.SqlClient
    
    Public Class BaseExceptionsHandler
        Protected mHasException As Boolean
        ''' <summary>
        ''' Indicate the last operation thrown an 
        ''' exception or not
        ''' </summary>
        ''' <returns></returns>
        Public ReadOnly Property HasException() As Boolean
            Get
                Return mHasException
            End Get
        End Property
        Protected mLastException As Exception
        ''' <summary>
        ''' Provides access to the last exception thrown
        ''' </summary>
        ''' <returns></returns>
        Protected ReadOnly Property LastException() As Exception
            Get
                Return mLastException
            End Get
        End Property
        ''' <summary>
        ''' Indicates if there was a sql related exception
        ''' </summary>
        Public ReadOnly Property HasSqlException() As Boolean
            Get
                If LastException IsNot Nothing Then
                    Return TypeOf LastException Is SqlException
                Else
                    Return False
                End If
            End Get
        End Property
        ''' <summary>
        ''' If you don't need the entire exception as in 
        ''' LastException this provides just the text of the exception
        ''' </summary>
        ''' <returns></returns>
        Public ReadOnly Property LastExceptionMessage() As String
            Get
                Return LastException.Message
            End Get
        End Property
        ''' <summary>
        ''' Indicate for return of a function if there was an 
        ''' exception thrown or not.
        ''' </summary>
        ''' <returns></returns>
        Protected ReadOnly Property IsSuccessFul() As Boolean
            Get
                Return Not HasException
            End Get
        End Property
        ''' <summary>
        ''' Returns an array of the entire exception list in reverse order
        ''' (innermost to outermost exception)
        ''' </summary>
        ''' <param name="ex">The original exception to work off</param>
        ''' <returns>Array of Exceptions from innermost to outermost</returns>
        Public Function InnerExceptions(ex As Exception) As Exception()
            Dim exceptions As New List(Of Exception)()
            exceptions.Add(ex)
    
            Dim currentEx As Exception = ex
            Do While currentEx.InnerException IsNot Nothing
                exceptions.Add(currentEx)
            Loop
    
            ' Reverse the order to the innermost is first
            exceptions.Reverse()
    
            Return exceptions.ToArray()
    
        End Function
    End Class
    

    Connection class

    Public MustInherit Class BaseSqlServerConnection
        Inherits BaseExceptionsHandler
        ''' <summary>
        ''' This points to your database server
        ''' </summary>
        Protected DatabaseServer As String = "KARENS-PC"
        ''' <summary>
        ''' Name of database containing required tables
        ''' </summary>
        Protected DefaultCatalog As String = ""
        Public ReadOnly Property ConnectionString() As String
            Get
                Return $"Data Source={DatabaseServer};Initial Catalog={DefaultCatalog};Integrated Security=True"
            End Get
        End Property
    End Class
    

    Start of a data class

    Imports System.Data.SqlClient
    
    Public Class Operations
        Inherits BaseSqlServerConnection
    
        Public Sub New()
            DefaultCatalog = "CustomerEntityFrameworkSample"
        End Sub
        Public Function GetCustomers() As List(Of Customer)
            mHasException = False
            Dim custList As New List(Of Customer)
    
            Dim selectStatement As String =
                    "SELECT Id, FirstName, LastName, Address, City, ZipCode, State, AccountNumber " &
                    "FROM Customer "
    
            Using cn As New SqlConnection With {.ConnectionString = ConnectionString}
                Using cmd As New SqlCommand With {.Connection = cn}
                    Try
                        cn.Open()
                        cmd.CommandText = selectStatement
                        Dim reader = cmd.ExecuteReader
                        While reader.Read
                            '
                            ' Add customers and set their state to unchanged.
                            '
                            custList.Add(New Customer() With
                            {
                                .Id = reader.GetInt32(0),
                                .FirstName = reader.GetString(1),
                                .LastName = reader.GetString(2),
                                .Address = reader.GetString(3),
                                .City = reader.GetString(4),
                                .ZipCode = reader.GetString(5),
                                .State = reader.GetString(6),
                                .AccountNumber = reader.GetString(7),
                                .CustomerState = CustomerState.Unchanged
                            })
                        End While
                    Catch ex As Exception
                        mHasException = True
                        mLastException = ex
                    End Try
    
                End Using
            End Using
            Return custList
        End Function
    
    End Class
    

    Simple form.

    Public Class Form1
        Private _ops As Operations = New Operations()
        Private blCustomers As SortableBindingList(Of Customer)
        Private bsCustomers As New BindingSource
        Private Sub Form1_Shown(sender As Object, e As EventArgs) Handles Me.Shown
            Dim custList = _ops.GetCustomers()
            If _ops.HasException Then
                MessageBox.Show(_ops.LastExceptionMessage)
                Exit Sub
            End If
    
            bsCustomers.DataSource = New SortableBindingList(Of Customer)(custList)
            blCustomers = CType(bsCustomers.DataSource, SortableBindingList(Of Customer))
    
            '
            ' Add some customers for the demo to get added customers later
            ' The Id in a real app would be as follows, add the customer to the database table,
            ' on success pass back the new id and use it below.
            '
            bsCustomers.Add(New Customer() With
            {
                .Id = 11, .FirstName = "Karen",
                .LastName = "Payne",
                .CustomerState = CustomerState.Added
            })
            bsCustomers.Add(New Customer() With
            {
                .Id = 12,
                .FirstName = "Karen",
                .LastName = "Payne",
                .CustomerState = CustomerState.Added
            })
    
            DataGridView1.DataSource = bsCustomers
            DataGridView1.Columns("id").Visible = False
            DataGridView1.Columns("CustomerState").Visible = False
    
        End Sub
    
        Private Sub cmdCurrentCustomer_Click(sender As Object, e As EventArgs) Handles cmdCurrentCustomer.Click
            If bsCustomers.Current IsNot Nothing Then
                Dim customer As Customer = CType(bsCustomers.Current, Customer)
                MessageBox.Show($"First name: {customer.FirstName} and Id: {customer.Id}")
            End If
        End Sub
        Private Sub cmdChangeFirstName_Click(sender As Object, e As EventArgs) Handles cmdChangeFirstName.Click
            If Not String.IsNullOrWhiteSpace(txtNewFirstName.Text) Then
                If bsCustomers.Current IsNot Nothing Then
                    Dim customer As Customer = CType(bsCustomers.Current, Customer)
                    customer.FirstName = txtNewFirstName.Text
                    bsCustomers.ResetItem(bsCustomers.Position)
                    customer.CustomerState = CustomerState.Modified
                End If
            End If
        End Sub
        Private Sub cmdGetModified_Click(sender As Object, e As EventArgs) Handles cmdGetModified.Click
            Dim modified = blCustomers.ToList().Where(Function(cust) cust.CustomerState = CustomerState.Modified)
            Dim enumerable As IEnumerable(Of Customer) = modified.ToList()
            If enumerable.Any() Then
                Dim sb As New Text.StringBuilder
                For Each cust As Customer In enumerable
                    sb.AppendLine(cust.Id.ToString())
                Next
                MessageBox.Show(sb.ToString())
            End If
        End Sub
        Private Sub cmdNewlyAdded_Click(sender As Object, e As EventArgs) Handles cmdNewlyAdded.Click
            Dim modified = blCustomers.ToList().Where(Function(cust) cust.CustomerState = CustomerState.Added)
            Dim enumerable As IEnumerable(Of Customer) = modified.ToList()
    
            If enumerable.Any() Then
                Dim sb As New Text.StringBuilder
                For Each cust As Customer In enumerable
                    sb.AppendLine(cust.Id.ToString())
                Next
                MessageBox.Show(sb.ToString())
            End If
        End Sub
    End Class
    


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Sunday, August 12, 2018 12:23 PM
    Moderator