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
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
create a class for connection to db
create a shared method within this class that fills a datatable with the values from the query
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\sqlservernam
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