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

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!

Answer 1

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.

 

Answer 2

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