DatabaseLibraryMDS 3.0.12

About

The DatabaseLibraryMDS is a library for accessing Sql Server databases using the Microsoft.Data.SqlClient.

Built with .NET 8 the DatabaseLibraryMDS library is for performing CRUD operations on a SQL Server database with minimal code for the developer.

Key Features

• Makes reading and writing data to and from a database easier with less coding
• Automatically maps columns to properties when retrieving data
• Converts returning values according to the data type of the property
• Supports setting the query timeout
• Supports Async calls
• Supports Transactions
• Supports logging errors to the Windows Event Log
• Supports Unit Testing

How to Use

   Executes a query that returns a List<Email>
   public List<Email> Email(string connectionString, string parameter)
   {
       try
       {
           using (var ss = new SqlServer(connectionString, CommandType.StoredProcedure))
           {
               string queryString = "Runbook.dbo.cspGetEmailByEmailId";
               ss.AddParameter("@parameter", parameter);
               return ss.ExecuteReader<Email>(queryString);
           } 
       }
       catch (Exception e)
       {
           throw new Exception(e.Message, e);
       }
   }
   
   Executes an Insert statement that returns a Guid for the record ID
       NOTE: The stored procedure will need to OUTPUT the new ID
   public Guid Email(string connectionString, Email Email)
   {
       try
       {
           using (var ss = new SqlServer(connectionString, CommandType.StoredProcedure))
           {
               string spName = "Runbook.dbo.cspAddEmail";
               return ss.ExecuteForGuid(spName, Email);
           } 
       }
       catch (Exception e)
       {
           throw new Exception(e.Message, e);
       }
   }

BulkInserts

// Example — synchronous DataTable bulk insert (with transaction)
using System;
using System.Data;
using DatabaseLibraryMDS;

var dt = new DataTable();
dt.Columns.Add("Id", typeof(int));
dt.Columns.Add("Name", typeof(string));
dt.Columns.Add("CreatedAt", typeof(DateTime));

dt.Rows.Add(1, "Alice", DateTime.UtcNow);
dt.Rows.Add(2, "Bob", DateTime.UtcNow);

string connectionString = "Server=.;Database=MyDb;Trusted_Connection=True;";

using var sql = new SqlServer(connectionString, System.Data.CommandType.Text, Timeout: 60, useEventLogs: false);
try
{
   sql.BeginTrans(); // optional — BulkInsert will enlist in DbCommand.Transaction when present
   int attempted = sql.BulkInsert(dt, "dbo.MyTargetTable", batchSize: 500);
   sql.CommitTrans();

   Console.WriteLine($"Rows attempted: {attempted}");
}
catch (Exception ex)
{
   try { sql.RollbackTrans(); } catch { }
   Console.WriteLine($"BulkInsert failed: {ex.Message}");
}

// Example — async POCO list bulk insert (requires ToDataTable() extension used in this project)
using System;
using System.Collections.Generic;
using System.Threading.Tasks;
using DatabaseLibraryMDS;

public record Person(int Id, string Name, DateTime CreatedAt);

async Task ExampleAsync()
{
   var items = new List<Person>
   {
       new Person(1, "Alice", DateTime.UtcNow),
       new Person(2, "Bob", DateTime.UtcNow)
   };

   string connectionString = "Server=.;Database=MyDb;Trusted_Connection=True;";
   await using var sql = new SqlServer(connectionString, System.Data.CommandType.Text, Timeout: 60);

   try
   {
       int attempted = await sql.BulkInsertAsync(items, "dbo.MyTargetTable", batchSize: 1000);
       Console.WriteLine($"Rows attempted: {attempted}");
   }
   catch (Exception ex)
   {
       Console.WriteLine($"BulkInsertAsync failed: {ex.Message}");
       throw;
   }
}

await ExampleAsync();