Last post May 25, 2018 05:36 AM by Brando ZWZ
May 24, 2018 03:06 PM|jb2_86_uk|LINK
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)
Dim reader As SqlDataReader
Dim Field1 as string
Dim Field2 as string
Dim Field3 as string
Dim FieldN as string
reader = exec.ExecuteReader
Field1 = reader("Field1")
Field2 = reader("Field2")
Field3 = reader("Field3")
FieldN = reader("FieldN")
Session("Field1") = Field1
Session("Field2") = Field2
Session("Field3") = Field3
Session("FieldN") = FieldN
May 24, 2018 03:56 PM|PatriceSc|LINK
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.
May 25, 2018 05:36 AM|Brando ZWZ|LINK
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