WiseProgress.Data 2026.5.21.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. SqlManagement.Server
7. 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 INSERT, UPDATE, SELECT, and DELETE SQL command strings from field/value pairs, class instances, or full object graphs with child collections. Handles type formatting, null safety, value sanitization, and emits efficient multi-row INSERT statements when inserting multiple rows into the same table.

Annotations

DatabaseIdentityAttribute

Namespace: WiseProgress.Data.Annotations

Marks a property as a SQL Server IDENTITY column. Excluded from all INSERT and UPDATE commands.

<DatabaseIdentity>
Public Property Id As Integer

DatabaseChildListAttribute

Namespace: WiseProgress.Data.Annotations

Marks a List(Of T) property as an explicit child collection. Used only by the annotated workflow (GetInsertCommandWithChildren / GetUpdateCommandWithChildren). Not required when using HandleListAsChildren.

<DatabaseChildList>
Public Property Lines As List(Of PurchaseInvoiceLine)

Constructors

New()
New(Table As String)

Properties

Fluent Helper

Field Management

Staging Values

enFieldType Enum

Command Generation Methods

Child Collection Workflows

Workflow A — HandleListAsChildren (annotation-free)
Set HandleListAsChildren = True before calling GetInsertCommand. Every List(Of T) property is discovered automatically and emitted as a multi-row INSERT. No attribute needed on the model.

Workflow B — Explicit annotated methods
Decorate child list properties with <DatabaseChildList> and call GetInsertCommandWithChildren or GetUpdateCommandWithChildren. Use this when you need selective control over which collections are treated as children.

Performance

• Multi-row INSERT — All rows for the same table are combined into a single INSERT INTO … VALUES (r1),(r2),(r3) statement.
• Reflection caching — AddValuesOfClass caches the filtered PropertyInfo[] for each type on first use, skipping GetProperties() on subsequent calls.
• Method caching — The AddValuesOfClass generic MethodInfo used by child-batch methods is resolved once at class load.
• Zero-allocation SQL building — All builders use StringBuilder with sb.Length -= 1 to remove trailing commas.

Examples

Manual field staging

Using cb As New CommandBuilder("Products")
	cb.AddValue("Name", "Widget", CommandBuilder.enFieldType.Text)
	cb.AddValue("Price", 9.99, CommandBuilder.enFieldType.Number)
	cb.AddValue("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

Single object — INSERT and UPDATE

Dim p As New Product With {.Name = "Gadget", .Price = 19.99}
Using cb As New CommandBuilder()
	Dim insertSql As String = cb.GetInsertCommand(p)
	cb.AddValuesOfClass(p)
	Dim updateSql As String = cb.GetUpdateCommand("WHERE Id = 1")
	conn.ExecuteNonQuery(insertSql)
End Using

List of objects — multi-row INSERT

Dim people As New List(Of Person) From {
	New Person With {.Name = "Alice", .Age = 30},
	New Person With {.Name = "Bob",   .Age = 25}
}
Using cb As New CommandBuilder("Person")
	Dim sql As String = cb.GetInsertCommand(people)
	' → INSERT INTO Person ( [Name], [Age]) VALUES ( N'Alice', 30), ( N'Bob', 25)
	conn.ExecuteNonQuery(sql)
End Using

Parent + children — annotation-free (HandleListAsChildren)

' No attribute needed on Header.Lines
Using cb As New CommandBuilder()
	cb.HandleListAsChildren = True
	Dim sql As String = cb.GetInsertCommand(header)   ' flag auto-resets
	conn.ExecuteNonQuery(sql)
End Using

' Fluent one-liner
Using cb As New CommandBuilder()
	conn.ExecuteNonQuery(cb.WithChildLists().GetInsertCommand(header))
End Using

Parent + children — explicit annotated workflow

Public Class PurchaseInvoice
	<DatabaseIdentity>
	Public Property Id As Integer
	Public Property Number As String
	Public Property InvoiceDate As Date

	<DatabaseChildList>
	Public Property Lines As List(Of PurchaseInvoiceLine)

	<DatabaseChildList>
	Public Property Freights As List(Of PurchaseInvoiceFreight)
End Class

' INSERT
Using cb As New CommandBuilder()
	conn.ExecuteNonQuery(cb.GetInsertCommandWithChildren(invoice))
End Using

' UPDATE (DELETE + re-INSERT children)
Using cb As New CommandBuilder()
	Dim sql As String = cb.GetUpdateCommandWithChildren(
		invoice,
		"WHERE Id = 5",
		Function(tableName) "WHERE InvoiceId = 5")
	conn.ExecuteNonQuery(sql)
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

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.