Efficient code for assigning variables from SQL query (VB.net) RRS feed

  • Question

  • User1060443753 posted

    Hi all,

    I have a section of code I am using repeatedly for querying SQL databases and setting returned values as session variables.

    My question is - is there a more efficient way to do this? It feels very repetitive. Any advice or suggestions would be appreciated!

    Using conn As New SqlConnection(constr)
        Dim exec As SqlCommand = New SqlCommand("Select [Field1],[Field2],[Field3],[...],[FieldN] from [db].[dbo].[table] where [Param] = @Param", conn)
        exec.Parameters.AddWithValue("@Param", Session("Param"))
        Dim reader As SqlDataReader
        Dim Field1 as string
        Dim Field2 as string
        Dim Field3 as string
        Dim FieldN as string
        reader = exec.ExecuteReader
        While reader.Read()
            Field1 = reader("Field1")
            Field2 = reader("Field2")
            Field3 = reader("Field3")
            FieldN = reader("FieldN")
        End While
        Session("Field1") = Field1
        Session("Field2") = Field2
        Session("Field3") = Field3
        Session("FieldN") = FieldN
    End Using


    Thursday, May 24, 2018 3:06 PM

All replies

  • User753101303 posted


    You could consider maybe to start using EF (or another ORM tool) whose purpose is to expose db data as plain .NET objects.

    You are using session to keep the old values for your fields ?

    Even if not taking the full path you coudl likely tke avantage of https://msdn.microsoft.com/en-us/library/system.data.entity.database.sqlquery(v=vs.113).aspx#M:System.Data.Entity.Database.SqlQuery%60%601%28System.String,System.Object[]%29 or hide your repetitive code behind something similar to ease maybe a further migration.


    Thursday, May 24, 2018 3:56 PM
  • User283571144 posted

    Hi jb2_86_uk,

    According to your description, I suggest you could consider using sql adapter to achieve reduce your coding.

    You could directly generate a datatable from the sqladapter and put that datatable into session,

    More details, you could refer to below codes:

    Private Sub OleDbDataAdapter_Click(ByVal sender As Object, ByVal Args As System.Event)
        'Create a connection object
        Dim ConnectionString As String = "provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source= C:/northwind.mdb"
        Dim SQL As String = "SELECT * FROM Orders"
        Dim conn As OleDbConnection = New OleDbConnection(ConnectionString)
        ' open the connection 
        ' Create an OleDbDataAdapter object
        Dim adapter As OleDbDataAdapter = New OleDbDataAdapter()
        adapter.SelectCommand = New OleDbCommand(SQL, conn)
        ' Create Data Set object
        Dim ds As DataSet = New DataSet("orders")
        ' Call DataAdapter's Fill method to fill data from the
        ' DataAdapter to the DataSet 
        Session("datatable") = ds
    End Sub

    Best Regards,


    Friday, May 25, 2018 5:36 AM