WiseProgress.Data 2026.5.16.1

WiseProgress.Data

A data access library targeting .NET Standard 2.0 (compatible with .NET Framework 4.8 and .NET 8).
Namespace root: WiseProgress.Data
SQL provider: Microsoft.Data.SqlClient

Table of Contents

1. ConnectionInfo
2. SqlConnection
3. QueryBuilder(Of T)
4. CommandBuilder
5. CommandBuilderList
6. DatabaseIdentityAttribute
7. SqlManagement.Server
8. DatabaseComparer

ConnectionInfo

Namespace: WiseProgress.Data.SqlConnection
Implements: IDisposable

Holds the connection parameters used to build a SQL Server connection string.

Properties

Constructors

New()
New(ServerName, Database, SqlUser, SqlPassword, WindowsAuthentication)

Example

Dim info As New ConnectionInfo(".", "MyDB", "sa", "pass", False)
info.UseConnectedEnvironment = True   ' keep connection open (needed for transactions)

SqlConnection

Namespace: WiseProgress.Data.SqlConnection
Implements: IDisposable

The main database access class. Wraps Microsoft.Data.SqlClient.SqlConnection and exposes synchronous and asynchronous query, scalar, reader, and transaction methods.

Properties

Constructors

New()
New(ServerName, Database, SqlUser, SqlPassword, WindowsAuthentication)

Connection Methods

Transaction Methods

Requires ConnectionInfo.UseConnectedEnvironment = True

Execute Methods

Scalar / Utility Methods

Example

Dim conn As New SqlConnection.SqlConnection(".", "MyDB", "sa", "pass", False)

' Simple query → DataTable
Dim dt = conn.ExecuteQuery("SELECT * FROM Products")

' Typed query → List(Of T)
Dim products = conn.ExecuteQuery(Of Product)("SELECT * FROM Products WHERE Active = 1")

' Single value
Dim total As Double = conn.GetValue("SELECT SUM(Amount) FROM Orders")

' Insert / Update
conn.ExecuteNonQuery("INSERT INTO Log (Msg) VALUES (N'Started')")

' Transaction
conn.ConnectionInfo.UseConnectedEnvironment = True
Dim tx = conn.BeginTrans()
Try
	conn.ExecuteNonQuery("UPDATE Stock SET Qty = Qty - 1 WHERE Id = 5")
	conn.CommitTrans(tx)
Catch
	conn.RollBack(tx)
End Try

QueryBuilder(Of T)

Namespace: (root)

A fluent, strongly-typed SQL query builder that generates and executes SELECT statements.
The table name is derived from GetType(T).Name.

Constructor

New(Connection As SqlConnection.SqlConnection)

WHERE Clauses (chainable)

All conditions are combined with AND.

ORDER BY Clauses (chainable)

Execution Methods

Example

Dim conn As New SqlConnection.SqlConnection(info)

Dim results = New QueryBuilder(Of Product)(conn) _
	.Where(Function(p) p.CategoryId, "=", 3) _
	.WhereContains(Function(p) p.Name, "chair") _
	.OrderByAscending(Function(p) p.Name) _
	.ToList()

Dim sql = New QueryBuilder(Of Order)(conn) _
	.Where(Function(o) o.Date, ">=", #1/1/2024#) _
	.WhereIn(Function(o) o.StatusId, {1, 2, 3}) _
	.ToSql()
' → SELECT * FROM Order WHERE Date >= '2024-01-01 00:00:00' AND StatusId IN (1, 2, 3)

CommandBuilder

Namespace: WiseProgress.Data.CommandBuilder
Implements: IDisposable

Builds parameterized INSERT, UPDATE, and SELECT SQL command strings from field/value pairs or from a class instance.

Properties

Constructors

New()
New(Table As String)

enFieldType Enum

SetValue / AddValue Methods

Class-based Population

Command Generation Methods

Example

' Manual field-by-field
Using cb As New CommandBuilder("Products")
	cb.SetValue("Name", "Widget", CommandBuilder.enFieldType.Text)
	cb.SetValue("Price", 9.99, CommandBuilder.enFieldType.Number)
	cb.SetValue("CreatedAt", Now, CommandBuilder.enFieldType.DateTime)
	Dim sql = cb.GetInsertCommand()
	' → INSERT INTO Products ([Name],[Price],[CreatedAt]) VALUES (N'Widget',9.99,'2024-03-15 10:30:00')
	conn.ExecuteNonQuery(sql)
End Using

' From class instance
Dim p As New Product With {.Name = "Gadget", .Price = 19.99}
Using cb As New CommandBuilder()
	cb.AddValuesOfClass(p)
	conn.ExecuteNonQuery(cb.GetInsertCommand())
End Using

CommandBuilderList

Namespace: WiseProgress.Data.CommandBuilder
Implements: IDisposable

A container that accumulates SQL command strings and executes them as a single batch.

Methods

Example

Using list As New CommandBuilderList()
	list.AddCommand(cb1.GetInsertCommand())
	list.AddCommand(cb2.GetUpdateCommand("WHERE Id = 5"))
	conn.ExecuteNonQuery(list.CommandsForExecution())
End Using

DatabaseIdentityAttribute

Namespace: WiseProgress.Data.Annotations
Inherits: Attribute

Marks a property as a SQL Server IDENTITY column.
CommandBuilder.AddValuesOfClass automatically skips decorated properties so they are never included in INSERT or UPDATE statements.

Usage

Public Class Product
	<DatabaseIdentity>
	Public Property Id As Integer       ' IDENTITY — excluded from INSERT/UPDATE

	Public Property Name As String      ' Normal column — included
	Public Property Price As Decimal    ' Normal column — included
End Class

SqlManagement.Server

Namespace: WiseProgress.Data.SqlManagement

Exposes SQL Server instance metadata via SERVERPROPERTY.

Constructor

New(ServerConnectionInformation As ConnectionInfo)

Properties (all ReadOnly)

SqlServerVersion Enum

Example

Dim srv As New SqlManagement.Server(info)
Console.WriteLine(srv.VersionNumber)   ' "16.0.1000.6"
Console.WriteLine(srv.Version)         ' SqlServer2021
Console.WriteLine(srv.Edition)         ' "Developer Edition (64-bit)"

DatabaseComparer

Namespace: (root)

Compares two SQL Server databases and synchronizes the destination to match the source schema (tables, columns, views, functions, stored procedures).

Constructor

New(sourceConnectionString As String, destinationConnectionString As String)

Methods

What it compares

Example

Dim comparer As New DatabaseComparer(
	"Server=source;Database=MyDB_Dev;Integrated Security=True",
	"Server=dest;Database=MyDB_Prod;Integrated Security=True")

comparer.CompareAndUpdate()
' Missing tables/columns/views/functions/procs in Prod are created from Dev

Type Mapping (ExecuteQuery(Of T))

When mapping DataTable rows or SqlDataReader to typed objects, column names are matched to property names case-insensitively. The following conversions are handled automatically:

NULL database values are silently skipped — the property retains its default value.