How to update Dataset after editing the database?

[Rave]

Hi erveryone,


I have a little issue:
I've created a method to update my database by importing a CSV file


The method to update my database is the follow one :

Public Sub ImportCSV2SQL(fileName As String, sqlTable As DataTable, column2Import() As Integer)


        ' Pr?paration des variables SQL
        Dim sqlConxtion As SqlConnection = New SqlConnection("Data Source=(LocalDB)\v11.0;AttachDbFilename=C:\Users\jeanraad\Desktop\" & _
                                                             "Holcim Quality Solutions\VB.Net\HQS_Dev\HQS_Dev\HQSdb.mdf;Integrated Security=True" & _
                                                             ";Connect Timeout=30")


        Dim sqlAction As SqlBulkCopy = New SqlBulkCopy(sqlConxtion.ConnectionString)
        Dim sqlCommand As New SqlCommand("DELETE FROM dbo." & sqlTable.TableName, sqlConxtion)
        'Dim sqlAdapter As New SqlDataAdapter("Select * From dbo." & sqlTable.TableName, sqlConxtion)


        ' Pr?paration des variables StreamReader et CsvReader
        Dim stream As StreamReader = New StreamReader(fileName) : Dim streamCount As StreamReader = New StreamReader(fileName)
        Dim csvCopy As CsvReader = New CsvReader(stream, True, ";"c)


        ' D?compte du nombre de lignes dans le fichier *.CSV
        Dim nbrLines As Integer = 0
        While streamCount.Peek <> -1
            nbrLines += 1
            Dim line As String = streamCount.ReadLine()
        End While


        ' Comparaison avec la table existante dans SQL et le nombre de lignes est plus cons?quente
        ' dans le *.CSV alors remplacement de la table sinon demande ? l'utilisateur s'il veut la 
        ' remplacer(1) ou annuler l'action (2)
        If sqlTable.Rows.Count <= nbrLines - 1 And sqlTable.Rows.Count <> 0 Then
            Dim msgResult = MsgBox("The number of rows of the CCList that you are trying to import is higher than those of those of the already existing CCList. Do you want to replace the old table or abort the importation?")
            If msgResult = 1 Then
                Exit Sub
            End If
        End If


        ' Tentative de se conecter au serveur SQL
        Try
            sqlCommand.Connection.Open()
        Catch myerror As SqlException
            MsgBox("Error connection SQL")
            sqlCommand.Connection.Close()
            Exit Sub
        End Try


        ' Suppression de la table afin de pouvoir y introduire de nouvelles donn?es
        sqlCommand.ExecuteNonQuery()
        sqlCommand.Connection.Close()


        ' Mappings des colonnes entre le fichier *.CSV et la table de destination
        For i = 0 To column2Import.Length - 1
            sqlAction.ColumnMappings.Add(column2Import(i), i)
        Next


        ' Transf?re des donn?es des donn?es du fichier *.CSV ? la table de destination
        sqlAction.DestinationTableName = sqlTable.TableName
        sqlAction.WriteToServer(csvCopy)
    End Sub

And the button who call il is the next one:

Private Sub ButtonImportCC_Click(sender As Object, e As EventArgs) Handles ButtonImportCC.Click
        ' Param?trage de l'OpenFileDialog afin que l'utilisateur puisse choisir la CC List ? importer
        dialog2openFile.Title = "Select a CC List to Import"
        dialog2openFile.Filter = "CC List (*.csv)|*.csv"
        dialog2openFile.InitialDirectory = Application.StartupPath & "\Tools\"
        dialog2openFile.RestoreDirectory = True


        ' Ouverture de l'OpenFileDialog
        ' Si l'utilisateur click sur OK alors la bdd SQL CCList est mise ? jour
        If dialog2openFile.ShowDialog() = System.Windows.Forms.DialogResult.OK Then
            ImportCSV2SQL(dialog2openFile.FileName, Me.HQSdbDataSet.CCList, {0, 2})
        End If


        Me.CCListTableAdapter.Dispose()
        Me.CCListTableAdapter.Fill(Me.HQSdbDataSet.CCList)
        Me.DBDataGrid.DataSource = Me.CCListBindingSource
        Me.DBDataGrid.Dock = System.Windows.Forms.DockStyle.Fill

Everythings works perfectly but the DataGridview ("DBDataGrid") doesn't update. He updates only when we close the application (form) and restart it...
i've looked everywhere et tried a lot of things on the web but it still don't refresh...
Can someone explain me why there is this issue and how to solve it?


Thx in advance...

By the way i'm a beginner...

 

[jmcilhinney]

You're expecting magic. ADO.NET is specifically designed to work disconnected from the database. A DataSet has no specific connection to the database so no changes to the database will show up in the DataSet automatically. If you want data in your DataSet then you have to retrieve it explicitly. You're already doing that in your code. If you want to get new data, do the same thing again.

 

[Rave]

So if I understand you good, it's impossible to have a refresh of the datagridview after updating the database?

 

[jmcilhinney]

So if I understand you good, it's impossible to have a refresh of the datagridview after updating the database?

If that's what you think then you don't understand me at all. What I said is that it's not just going to happen of its own accord. If you want data from the database then you have to retrieve it explicitly. Here's how you are current retrieving data from the database:

        Me.CCListTableAdapter.Dispose()
        [B][U]Me.CCListTableAdapter.Fill(Me.HQSdbDataSet.CCList)[/U][/B]

If you want to retrieve data again then you do that again.

That said, what's the line before it doing there? Why would you dispose an object immediately before calling a method on it? Do you know what Dispose does? If not then why are you calling it at all? If so then you know that that's not the place to do it.

 

[Rave]

It's there because i've tried a lot of things to make it work... Ok it's maybe not the good line of code but i'm just a beginner... So don't shoot at me just because i'm trying and don't understand vb.net like an expert.. I started 3 weeks ago without a proper cursus...
If i'm here it's because i'm looking for help...
Ok Dispose is surely not what's gonna help me, I realize it now...

As showned in my code, the line

[COLOR=#3E3E3E]        [/COLOR][B][U]Me.CCListTableAdapter.Fill(Me.HQSdbDataSet.CCList)[/U][/B]

is just after importing new lines in my db, and it does not work.

I surely do something wrong but don't understand what...

 

[jmcilhinney]

Are you sure that you're importing the data into the same database that you're retrieving it from? From the looks of your connection string, I'd say that you're not. I suggest that you follow the first link in my signature to learn how to manage local data files. You should have a source data file in your project folder and a working data file in your output folder. The source file is the one you work on in the IDE but its the working file that the application should be interacting with. You should ALWAYS use |DataDirectory| to represent the folder path of that working file in a connection string.

 

[Rave]

That was it!!
Really really thank you!
I don't know why I changed this because in the beginning I used "|DataDirectory|" but again thank you...
I'm gonna read your first link in your signature to learn more about managing local data files... It won't be useless I think...
Again thanks you a lot

Edit: I was too fast... When I restart the debug Mode the datagrid is empty again. Is there a way to save after i've imported it and shown in the datagridview?

 

[jmcilhinney]

I think you'll find that that link I suggested will address that last issue.