Bind to DataGridView using a Stored Procedure

Question

I have a DataGridView with 5 columns all setup in the Form Designer. I also have a Stored Procedure that will SELECT the 5 columns and data required based on parameters. I want this data to be updateable; the user can add rows, delete rows and update existing rows.

I have so far managed to populate the DataGridView with data from a Stored Procedure in SQL Server, but unfortunately this is read-only and any changes made to the datatable post-load are only in the front end application.

I have done this many times in MS Access and I believe the DataSet needs to be 'bound' to the DataGridView rather than fed as a DataSource in order to make the DataGridView updateable directly to the data table in SQL Server but I am not sure on how to achieve this. I have looked online at very many sources and cannot seem to work out what it is I need to do in order to get this working.

Existing code:

    Public Sub loadDataGridView(taskID As Integer)
        Dim db As New SqlConnection(getCredentials)
        Dim sp As SqlCommand = db.CreateCommand
        Dim sa As SqlDataAdapter = New SqlDataAdapter(sp)
        Dim ds As DataSet = New DataSet()
        Dim x As Integer = 0

        db.Open()
        With sp
            .CommandText = "StoredProcedureName"
            .CommandType = CommandType.StoredProcedure
            .Parameters.Add(New SqlParameter("@taskID", taskID))
        End With

        sa.Fill(ds)

        DataGridView1.DataSource = ds.Tables(0)
        db.Close()
    End Sub

Answer 1

For anyone in future looking for how to get this done, I've attached the code I used to achieve this. This is based primarily on jmcilhinney examples posted here.

Module DataGridViewRetrieve
    Public db As New SqlConnection(lgnGetCredentials)
    Public sp As SqlCommand = db.CreateCommand
    Public sa As SqlDataAdapter = New SqlDataAdapter(sp)
    Public dt As DataTable = New DataTable()

    Public Sub initialiseDataAdapter()
        Dim dRecords As SqlCommand = db.CreateCommand
        Dim uRecords As SqlCommand = db.CreateCommand
        Dim iRecords As SqlCommand = db.CreateCommand

        ' # Initialise delete records Stored Procedure
        With dRecords
            .CommandText = "<NAME OF STORED PROCEDURE TO DELETE>"
            .CommandType = CommandType.StoredProcedure
            .Parameters.Clear()
            .Parameters.Add("@ID", SqlDbType.BigInt, 10, "<NAME OF ID COLUMN IN DataGridView>")
        End With

        ' # Initialise update records Stored Procedure
        ' Note - you'll need to add your parameters for each column in the below example
        With uRecords
            .CommandText = "<NAME OF STORED PROCEDURE TO UPDATE"
            .CommandType = CommandType.StoredProcedure
            .Parameters.Clear()
            .Parameters.Add("@dgID", SqlDbType.BigInt, 10, "<NAME OF ID COLUMN IN DataGridView>")
            .Parameters.Add("@Title", SqlDbType.Bit, 1, "<NAME OF COLUMN IN DataGridView>")
        End With

        ' # Initialise insert records Stored Procedure
        With iRecords
            .CommandText = "pl.x53_loadLifeListGoals_iRecords"
            .CommandType = CommandType.StoredProcedure
            .Parameters.Clear()
            .Parameters.Add("@dgID", SqlDbType.BigInt, 10, "<NAME OF ID COLUMN IN DataGridView>")
            .Parameters.Add("@Title", SqlDbType.Bit, 1, "<NAME OF COLUMN IN DataGridView>")
        End With

        ' # Set data adapter commands
        sa.DeleteCommand = dRecords
        sa.UpdateCommand = uRecords
        sa.InsertCommand = iRecords

        ' # Set missing schema action key
        sa.MissingSchemaAction = MissingSchemaAction.AddWithKey
    End Sub

    Public Sub loadData(TitleName As String)
        ' # Clears data
        FormName.DataGridViewName.DataSource = Null

        ' # Calls the initialise routine
        Call initialiseDataAdapter()

        ' # Initialise the SELECT Stored Procedure
        With sp
            .CommandText = "<NAME OF STORED PROCEDURE TO SELECT>"
            .CommandType = CommandType.StoredProcedure
            .Parameters.Clear()
            .Parameters.Add(New SqlParameter("@TitleName", TitleName))
        End With

        ' # Load data
        sa.Fill(dt)
        FormName.BindingSourceName.DataSource = dt
        FormName.DataGridViewName.DataSource = FormName.BindingSourceName
    End Sub

    Public Sub saveData()
        sa.Update(FormName.BindingSourceName.DataSource)
    End Sub
End Module

'// ATTACH YOUR LOAD CALL TO YOUR EVENT HANDLER - IN THIS INSTANCE, FORM LOAD
Private Sub Form1_Load(sender as object, e as EventArgs) Handles Me.Load
    Call loadData(Me.txtTitle.Text)
End Sub

Private Sub ButtonSave_Click(sender as object, e as EventArgs) Handles Me.ButtonSave
    Call saveData()
End Sub