Avatar of Jimbo99999
Jimbo99999
Flag for United States of America asked on

VB.Net - Most "Compact" Query Retrieval

Good Day Experts!

I need to execute a Select top 1 *...query and get a couple of field values returned by the query.  

What is the most "compact" way with minimum lines of code that I can achieve my desired results?

Thanks,
jimbo99999
Visual Basic.NET

Avatar of undefined
Last Comment
Jimbo99999

8/22/2022 - Mon
sognoct

my favorite version :

create a class for connection to db

create a shared method within this class that fills a datatable with the values from the query

  Public Class clsdb
    public Shared Database As String
    public Shared Server As String
    public Shared UserID As String
    public Shared Password As String

    Public Shared Function connectionString() As String
      Dim cs As String
      If _Server Is Nothing Or _UserID Is Nothing Or _Password Is Nothing Or _Database Is Nothing Then
        throw new Exception ("connectionString: incomplete parameters")
      End If
      cs = "SERVER=" + _Server.ToString + ";" ' async=true;"
      cs &= "User ID=" + _UserID.ToString + ";"
      cs &= "Password=" + Trim(_Password.ToString) + ";"
      cs &= "Initial Catalog=" + _Database.ToString + ";"
      cs &= " Connect Timeout=20"
      Return cs
    End Function
    
    Public Shared Function fillDt(ByVal s As String, ByRef dt As DataTable) As Int32
      Dim da As New SqlDataAdapter
      Dim cmd As New SqlCommand
      Dim cnn As New SqlClient.SqlConnection(connectionString())
      Dim nRecord As Int32 = 0
      Try
        cnn.Open()
        cmd.Connection = cnn
        cmd.CommandText = s
        cmd.CommandTimeout = 0
        da.SelectCommand = cmd
        nRecord = da.Fill(dt)
      Catch ex As Exception
        If cnn.State = ConnectionState.Open Then cnn.Close()
        cnn.Dispose()
        Throw New Exception("Error " & ex.Message)
      End Try
      If cnn.State = ConnectionState.Open Then cnn.Close()
      cnn.Dispose()
      Return nRecord
    End Function
  end class 

Open in new window


then just need to initialize the clsdb connection once in the main form

clsdb.Database = "dbname"
clsdb.Server = "192.168.1.xx\sqlservername"
clsdb.userid= "sa"
clsdb.Password = "myserverpass"

then you can populate datatable with :

clsdb.fillDt("SELECT TOP(1) row1, row2)", mydatatable)

Just one line of code
ASKER CERTIFIED SOLUTION
Jacques Bourgeois (James Burger)

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Jimbo99999

ASKER
Excellent...thanks for the help.

Jimbo99999
Your help has saved me hundreds of hours of internet surfing.
fblack61