Torna al Thread
Dim cnOrig As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\;Extended Properties=""text;HDR=No;FMT=Delimited""")
Dim cnDest As New SqlConnection("Data Source=msdevsrv;Initial Catalog=tempdb;Integrated Security=True")
Dim cmdSelectOrig As OleDbCommand = Nothing
Dim objDataReader As OleDbDataReader = Nothing
Dim Result As String = "Risultato importazione record" + vbCrLf
Dim mVbTab As String = vbTab + vbTab + vbTab
Dim transDest As SqlTransaction = Nothing
Dim cmdDest As SqlCommand = Nothing
Try
cnOrig.Open()
cnDest.Open()
' Carico i dati nel dataReader
cmdSelectOrig = New OleDbCommand("SELECT * FROM Testa.csv", cnOrig)
objDataReader = cmdSelectOrig.ExecuteReader(System.Data.CommandBehavior.CloseConnection)
' Inizio la Transaction per eseguire piu' comandi e' in caso di errore roll back
cmdDest = cnDest.CreateCommand()
transDest = cnDest.BeginTransaction("BuilderTransaction")
cmdDest.Connection = cnDest
cmdDest.Transaction = transDest
' Leggo i dati dal data reader e li scrivo nel db sql
If objDataReader.HasRows Then
While objDataReader.Read 'Leggo i dati
Dim codice As String = objDataReader(0).ToString
Dim Desc As String = objDataReader(1).ToString
Dim Note As String = objDataReader(2).ToString
cmdDest.CommandText = "Select Codice FROM Amm Where Codice = '" + codice + "'"
Dim ret As Object = cmdDest.ExecuteScalar
Result += vbTab
If ret Is Nothing Then ' se e' nothing aggiungo altrimenti aggiorno
Result += codice + mVbTab + Desc + mVbTab + Note + mVbTab + " -- Addeded --" + vbCrLf
cmdDest.CommandText = String.Format("INSERT INTO Amm (Codice, Descrizione, Note) VALUES ('{0}', '{1}', '{2}')", codice, Desc, Note)
Else
Result += codice + mVbTab + Desc + mVbTab + Note + mVbTab + " -- Updated --" + vbCrLf
cmdDest.CommandText = String.Format("UPDATE Amm SET Descrizione = '{0}', Note = '{1}' WHERE Codice = '{2}'", Desc, Note, codice)
End If
cmdDest.ExecuteNonQuery()
End While
transDest.Commit() ' Eseguo la Transaction
End If
Me.TextBox1.Text = Result
Console.WriteLine(Result)
' In caso di errore eseguo il Roll back
Catch ex As Exception
Try
If Not (transDest Is Nothing) Then
transDest.Rollback("BuilderTransaction")
End If
Catch exx As SqlException
Console.WriteLine(ex.Message + vbCrLf + "Non posso fare il roll back transaction")
End Try
Console.WriteLine("Errore Generico :" + vbCrLf + ex.Message)
Finally
If Not (cnDest Is Nothing) Then
cnDest.Close()
End If
End Try