locked
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)
        conn.Open()
        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
    
        Reader.Close()       
    End Using

    Cheers!

    Thursday, May 24, 2018 3:06 PM

All replies

  • User753101303 posted

    Hi,

    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 
        conn.Open()
    
        ' 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 
        adapter.Fill(ds)
    
        Session("datatable") = ds
    End Sub

    Best Regards,

    Brando

    Friday, May 25, 2018 5:36 AM