none
WHICH METHOD TO ADD DATA IN SQL SERVER IS BEST RRS feed

  • Question

  • HI

    METHOD 1

     Private Sub addData()
    
             Dim connection As New SqlConnection("Data Source=.\SQLExpress;Initial Catalog=k1;Integrated Security=True")
            Try
                Dim command As New SqlCommand("insert into area (AreaName) values('" & area_name_textbox.Text & "')", connection)
                
                connection.Open()
                command.ExecuteNonQuery()
            Catch ex As Exception
                MsgBox(ex.Message)
            Finally
                connection.Close()
               
            End Try
        End Sub

    METHOD 2

    PRIVATE SUB ADDDATA

    Try Dim connection As New SqlConnection("Data Source=.\SQLExpress;Initial Catalog=k1;Integrated Security=True") Dim command As New SqlCommand("insert into (AreaName) values(@AreaName)", connection) Command.Parameters.Add("@AreaName", SqlDbType.VARCHAR).Value = area_name_textbox.Text connection.Open() command.ExecuteNonQuery() Catch ex As Exception MsgBox(ex.Message) Finally connection.Close() End Try

    END SUB



    MUHAMMAD ANZAR E-mail : muhammadanzar@hotmail.com Mobile # :0092-3215096959

    Friday, February 22, 2019 11:24 AM

Answers

  • Best practice dictate to have one connection per operation where operation is read, update, add, remove and search.

    Developers who do not think all the way through how to plan out connections will do one of the.

    • Create a single connection (perhaps in a public code module) object, each time it's needed check if the connection is open or closed and if closed open it then do some work.  The first problem is we are never sure of the connection state open or closed.
    • Create a singleton pattern for the connection string while this has several drawbacks 1) the same as the bullet above, never sure of the connection state open or closed b) requires additional code to handle logic to check 1. if the connection object has been created 2. is using a global instance of a module and connection.

    So with that if you look at well designed applications the connection and command objects are done like I showed in the first post. One data class per table unless there are relational then this can go two ways, a class that handles multiple related table operations or two classes where you would work with the two classes in tangenet.

    DO NOT create one modules for all operations as this will cause maintainable issues, instead as mentioned above separate out the various table operations. I've seen many times when a developer will place all data operations in one class or code module where down the road they had issues figuring out how to find problems or how to add or change current functionality

    In regards to connection strings, it perfectly fine to have a class responsible for creating them as per the classes in the following code repository which also has a NuGet package for ease of use but can be used w/o the NuGet package.

    Take aways

    • Simple is not always better, e.g. one connection and one command 
    • Entity Framework, the current best practices for data operations uses a connection and command under the covers and for each operation creates  a new connection and command. So this should be done for non-Entity Framework too.
    • Create a connection and command, use them then dispose of them immediately. 


    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

    • Marked as answer by muhammadanzar Friday, February 22, 2019 2:04 PM
    Friday, February 22, 2019 1:32 PM
    Moderator

All replies

  • Hello,

    Option #2 but done with modifications.

    • Using statements will dispose of the connection and command objects even with an error
    • Command does an insert and returns the new key using ExecuteScalar (see this example).
    • Using AddWithValue for strings is best rather than Add and this goes for 99% of parameters.
    • In the catch you can use your MsgBox or other options such as send an email to the developer if not in the same location as you are.
    • Sending the parameter to the procedure guarantees the right type is being push to the database table.
    Imports System.Data.SqlClient
    
    Public Class Form1
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    
        End Sub
        ''' <summary>
        ''' Add new record via INSERT INTO, get new primary key via the SELECT CAST
        ''' </summary>
        ''' <param name="pAreaName"></param>
        Private Sub AddData(pAreaName As String)
            Using connection As New SqlConnection("Data Source=.\SQLExpress;Initial Catalog=k1;Integrated Security=True")
                Using command As New SqlCommand("INSERT INTO (AreaName)  values(@AreaName);SELECT CAST(scope_identity() AS int);", connection)
                    command.Parameters.AddWithValue("@AreaName", pAreaName)
                    Try
                        connection.Open()
                        Dim newIdentifier = Convert.ToInt32(command.ExecuteScalar())
                    Catch ex As Exception
                        '
                        ' handle error
                        '
                    End Try
                End Using
            End Using
    
        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


    Friday, February 22, 2019 12:14 PM
    Moderator
  • hi

    now i tell you in my project connection string use many time in many classes like when ever insert

    1.area form

    2.town form

    3.customer form

    4.product form

    5.company form

    6.supplier form

    7. product form

    8. purchase form 

    9.purchase return form

    10.sale form

    11. sale return form

    12. employee form

    each form have insert,update and delete commands,  when perform this it almost become 36 connection strings.

    i make a solution to make a module and keep connection string there.  this one string save me 36 connections.

    is this right way


    MUHAMMAD ANZAR E-mail : muhammadanzar@hotmail.com Mobile # :0092-3215096959

    Friday, February 22, 2019 1:10 PM
  • Best practice dictate to have one connection per operation where operation is read, update, add, remove and search.

    Developers who do not think all the way through how to plan out connections will do one of the.

    • Create a single connection (perhaps in a public code module) object, each time it's needed check if the connection is open or closed and if closed open it then do some work.  The first problem is we are never sure of the connection state open or closed.
    • Create a singleton pattern for the connection string while this has several drawbacks 1) the same as the bullet above, never sure of the connection state open or closed b) requires additional code to handle logic to check 1. if the connection object has been created 2. is using a global instance of a module and connection.

    So with that if you look at well designed applications the connection and command objects are done like I showed in the first post. One data class per table unless there are relational then this can go two ways, a class that handles multiple related table operations or two classes where you would work with the two classes in tangenet.

    DO NOT create one modules for all operations as this will cause maintainable issues, instead as mentioned above separate out the various table operations. I've seen many times when a developer will place all data operations in one class or code module where down the road they had issues figuring out how to find problems or how to add or change current functionality

    In regards to connection strings, it perfectly fine to have a class responsible for creating them as per the classes in the following code repository which also has a NuGet package for ease of use but can be used w/o the NuGet package.

    Take aways

    • Simple is not always better, e.g. one connection and one command 
    • Entity Framework, the current best practices for data operations uses a connection and command under the covers and for each operation creates  a new connection and command. So this should be done for non-Entity Framework too.
    • Create a connection and command, use them then dispose of them immediately. 


    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

    • Marked as answer by muhammadanzar Friday, February 22, 2019 2:04 PM
    Friday, February 22, 2019 1:32 PM
    Moderator
  • thank you

    MUHAMMAD ANZAR E-mail : muhammadanzar@hotmail.com Mobile # :0092-3215096959

    Friday, February 22, 2019 2:04 PM
  • thank you

    MUHAMMAD ANZAR E-mail : muhammadanzar@hotmail.com Mobile # :0092-3215096959

    Your very welcome :-)

    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

    Friday, February 22, 2019 2:23 PM
    Moderator