2
\$\begingroup\$

I am almost finished with my queries which are running inside transactions so it's everything or nothing... Could you please take a look if I am missing something or if everything is where it belongs?

I am bit worried about my conn.Open() call, and about security in general. Can you help me out to tune it up and make it better?

#Region "Final articel insert to database"
    Public Function ProcessArticle(artikel As ArticlesVariations) As Boolean
        Dim result As Boolean = True
        Dim strcon = New AppSettingsReader().GetValue("ConnectionString", GetType(System.String)).ToString()

        Using connection As New SqlConnection(strcon)
            '-- Open generall connection for all the queries
            connection.Open()
            '-- Make the transaction.
            Dim transaction As SqlTransaction
            transaction = connection.BeginTransaction(IsolationLevel.ReadCommitted)

            Dim newArticleRowID As Integer = 0
            Dim articleIndex As Integer = 0
            Try
                For Each kvp As KeyValuePair(Of Integer, Artikel) In artikel.collection
                    Dim ckey As Integer = kvp.Key
                    articleIndex = kvp.Key              'save article key
                    Dim data As Artikel = kvp.Value

                    '-- Insert aricle to Article's table (tbArtikel) and get inserted row id to use it in other queries
                    Using cmd As New SqlCommand("INSERT INTO tbArtikel (Nummer, Name, Gewicht, EANBarcode, Vater, Hohe, Breite, Tiefe, Lange, Preis, FK_Geschaft_ID, 
                                            FK_SubKategorie_ID, FK_SubSubKategorie_ID, FK_Hersteller_ID) VALUES (@Nummer, @Name, @Gewicht, @EANBarcode, @Vater, 
                                            @Hohe, @Breite, @Tiefe, @Lange, @Preis, @FK_Geschaft_ID, @FK_SubKategorie_ID, @FK_SubSubKategorie_ID, @FK_Hersteller_ID);Select Scope_Identity()", connection)
                        cmd.CommandType = CommandType.Text
                        cmd.Connection = connection
                        cmd.Transaction = transaction
                        cmd.Parameters.AddWithValue("@Nummer", data.Nummer)
                        cmd.Parameters.AddWithValue("@Name", data.Name)
                        cmd.Parameters.AddWithValue("@Gewicht", data.Gewicht)
                        cmd.Parameters.AddWithValue("@EANBarcode", data.EANBarcode)
                        cmd.Parameters.AddWithValue("@Vater", data.Vater)
                        cmd.Parameters.AddWithValue("@Hohe", data.Hohe)
                        cmd.Parameters.AddWithValue("@Breite", data.Breite)
                        cmd.Parameters.AddWithValue("@Tiefe", data.Tiefe)
                        cmd.Parameters.AddWithValue("@Lange", data.Lange)
                        cmd.Parameters.AddWithValue("@Preis", data.Preis)
                        cmd.Parameters.AddWithValue("@FK_Geschaft_ID", data.FK_Geschaft_ID)
                        cmd.Parameters.AddWithValue("@FK_SubKategorie_ID", data.FK_SubKategorie_ID)
                        cmd.Parameters.AddWithValue("@FK_SubSubKategorie_ID", data.FK_SubSubKategorie_ID)
                        cmd.Parameters.AddWithValue("@FK_Hersteller_ID", data.FK_Hersteller_ID)
                        newArticleRowID = cmd.ExecuteScalar()      'new article row's id
                    End Using

                    '-- If given article contains images list (artikel_images is a list with pictures associated with article)
                    If Not IsNothing(artikel.collection(articleIndex).artikel_images) Then
                        For Each img In artikel.collection(articleIndex).artikel_images
                            '--Insert article's images if exists
                            Using cmd As New SqlCommand("INSERT INTO tbArticle_Image (Path, FK_Artikel_ID, Position) VALUES (@Path, @FK_Artikel_ID, @Position)", connection)
                                cmd.CommandType = CommandType.Text
                                cmd.Connection = connection
                                cmd.Transaction = transaction
                                cmd.Parameters.AddWithValue("@Path", img.Path)
                                cmd.Parameters.AddWithValue("@FK_Artikel_ID", newArticleRowID)
                                cmd.Parameters.AddWithValue("@Position", img.Position)
                                cmd.ExecuteScalar()
                            End Using
                        Next
                    End If

                    '-- If given article contains articles variations list (artikel_variation_attributes is a list with variations associated with article)
                    If Not IsNothing(artikel.collection(articleIndex).artikel_variation_attributes) Then
                        For Each var In artikel.collection(articleIndex).artikel_variation_attributes
                            '--Insert article's images if exists
                            Using cmd As New SqlCommand("INSERT INTO tbArtikel_variation (FK_Variation_Attribute_ID, FK_Artikel_ID, Position) VALUES (@FK_Variation_Attribute_ID, @FK_Artikel_ID, @Position)", connection)
                                cmd.CommandType = CommandType.Text
                                cmd.Connection = connection
                                cmd.Transaction = transaction
                                cmd.Parameters.AddWithValue("@FK_Variation_Attribute_ID", var.FK_Variation_Attribute_ID)
                                cmd.Parameters.AddWithValue("@FK_Artikel_ID", newArticleRowID)
                                cmd.Parameters.AddWithValue("@Position", var.Position)
                                cmd.ExecuteScalar()
                            End Using
                        Next
                    End If

                    '-- If given article contains additional kategories variations list (artikel_variation_attributes is a list with variations associated with article)
                    '-- In this case list - if exist could be only associated with first article and not for rest (if exist)
                    If articleIndex = 0 Then
                        If Not IsNothing(artikel.collection(articleIndex).artikela_additional_kategories.collection) Then
                            For Each addkat In artikel.collection(articleIndex).artikela_additional_kategories.collection
                                '--Insert article's images if exists
                                Using cmd As New SqlCommand("INySERT INTO tbAdditionalSubKategories (FK_SubKategorie_ID, FK_SubSubKategorie_ID, FK_Artikel_ID) VALUES (@FK_SubKategorie_ID, @FK_SubSubKategorie_ID, @FK_Artikel_ID)", connection)
                                    cmd.CommandType = CommandType.Text
                                    cmd.Connection = connection
                                    cmd.Transaction = transaction
                                    cmd.Parameters.AddWithValue("@FK_SubKategorie_ID", addkat.FK_SubKategorie_ID)
                                    cmd.Parameters.AddWithValue("@FK_SubSubKategorie_ID", addkat.FK_SubSubKategorie_ID)
                                    cmd.Parameters.AddWithValue("@FK_Artikel_ID", newArticleRowID)
                                    cmd.ExecuteScalar()
                                End Using
                            Next
                        End If
                    End If
                Next
                transaction.Commit()
            Catch ex As Exception
                result = False
                '-- Roll the transaction back.
                Try
                    transaction.Rollback()
                Catch ex2 As Exception
                    ' This catch block will handle any errors that may have occurred
                    ' on the server that would cause the rollback to fail, such as
                    ' a closed connection.
                    'Console.WriteLine("Rollback Exception Type: {0}", ex2.GetType())
                    'Console.WriteLine("  Message: {0}", ex2.Message)
                End Try

            End Try
        End Using

        Return result
    End Function
#End Region
\$\endgroup\$
  • 2
    \$\begingroup\$ Are you seeking code review of the T-SQL queries within this code, or just of the VB? Your SQL queries are fairly trivial and I personally wouldn't be able to critique them much. If not, can I suggest removing the t-sql tag and clarifying in the question what you want reviewed? \$\endgroup\$ – Stuart J Cuthbertson Apr 14 '16 at 12:42
  • \$\begingroup\$ Also you have a minor typo INySERT INTO. \$\endgroup\$ – Stuart J Cuthbertson Apr 14 '16 at 12:43
  • \$\begingroup\$ Please include the source code of relevant classes used in the question. \$\endgroup\$ – dfhwze Aug 29 '19 at 18:02

Browse other questions tagged or ask your own question.