Endev.WrapSql.WrapOleDb 4.0.0-pre1

This is a prerelease version of Endev.WrapSql.WrapOleDb.
dotnet add package Endev.WrapSql.WrapOleDb --version 4.0.0-pre1                
NuGet\Install-Package Endev.WrapSql.WrapOleDb -Version 4.0.0-pre1                
This command is intended to be used within the Package Manager Console in Visual Studio, as it uses the NuGet module's version of Install-Package.
<PackageReference Include="Endev.WrapSql.WrapOleDb" Version="4.0.0-pre1" />                
For projects that support PackageReference, copy this XML node into the project file to reference the package.
paket add Endev.WrapSql.WrapOleDb --version 4.0.0-pre1                
#r "nuget: Endev.WrapSql.WrapOleDb, 4.0.0-pre1"                
#r directive can be used in F# Interactive and Polyglot Notebooks. Copy this into the interactive tool or source code of the script to reference the package.
// Install Endev.WrapSql.WrapOleDb as a Cake Addin
#addin nuget:?package=Endev.WrapSql.WrapOleDb&version=4.0.0-pre1&prerelease

// Install Endev.WrapSql.WrapOleDb as a Cake Tool
#tool nuget:?package=Endev.WrapSql.WrapOleDb&version=4.0.0-pre1&prerelease                

WrapSQL - C# Port

Method overview

Usage

Constructors

Note: The constructors are the only thing between different DB-Types that are individual for every DB-Type.

MySQL

// Initialising using a connection-string
WrapMySQL sql = new WrapMySQL("CustomConnectionString");
// Initialising using pre-defined connection-string
WrapMySQL sql = new WrapMySQL("localhost","northwind","username","password");
// Initialising using WrapMySQLData
WrapMySQLData dbData = new WrapMySQLData("localhost","northwind","username","password")
{
    Pooling = true,
    SSLMode = "none",
    Port = 1253
};

WrapMySQL sql = new WrapMySQL(dbData);

SQLite

// Initialising using the path to your sqlite-file
WrapSQLite sql = new WrapSQLite(@"Path\To\Your\File.db");
// Initialising using the path to your sqlite-file
WrapSQLite sql = new WrapSQLite("CustomConnectionString", false);

ODBC

// Initialising using a custom connection-string
WrapODBC sql = new WrapODBC("CustomConnectionString");

OleDb

// Initialising using a custom connection-string
WrapOleDb sql = new WrapOleDb("CustomConnectionString");

Open() and Close()

The connection should be kept open as short as possible.

sql.Open();
sql.ExecuteNonQuery("UPDATE ....");
sql.Close();

Methods with the suffix ACon open and close the connection automatically, this can however cause problems when running them several times after each other (e.g. in a loop).

sql.ExecuteScalarACon("SELECT ID FROM customers WHERE ...");

Transactions

sql.Open();
sql.TransactionBegin();
try
{
  sql.ExecuteNonQuery("UPDATE ...");
  sql.ExecuteNonQuery("DELETE ...");

  sql.TransactionCommit();
}
catch
{
  sql.TransactionRollback();
}
sql.Close();

NOTE: Methods with the suffix ACon are not allowed durring a transaction and will throw an exception!

Passing SQL-Statements and Parameters

It is recommended to pass sql-queries using parameters, protecting them against SQL-Injection attacks.

The following applies for every method which requires a SQL-query:

// Passing a sql-statement without parameters (NOT RECOMMENDED!)
string memberIDNr = "ABCD-EFGH-IJKL-MNOP";
sql.ExecuteScalar($"SELECT paymentDate FROM members WHERE memberID = '{memberIDNr}'");

// Passing a sql-statement with parameters (recommended)
string memberIDNr = "ABCD-EFGH-IJKL-MNOP";
sql.ExecuteScalar("SELECT paymentDate FROM members WHERE memberID = ?", memberIDNr);

ExecuteNonQuery() and ExecuteNonQueryACon()

ExecuteNonQuery-Methods are used to execute a non-query like statement, like UPDATE, DELETE, INSERT INTO, ALTER TABLE, ...

// Opening and closing the connection manually
sql.Open();
sql.ExecuteNonQuery("INSERT INTO ....");
sql.Close();
// Opening and closing the connection automatically
sql.ExecuteNonQueryACon("INSERT INTO ...");

ExecuteQuery()

The ExecuteQuery-Method provides a SQLReader for cycling through all results the query retrieves.

Make sure to use the correct SQLReader:

  • MySQL: MySQLDataReader
  • SQLite: SQLiteDataReader
  • ODBC: ODBCDataReader
  • OleDb: OleDbDataReader
sql.Open();
using(MySqlDataReader reader = (MySqlDataReader)sql.ExecuteQuery("SELECT * FROM orders"))
{
    while(reader.Read())
    {
        Console.WriteLine(reader["orderID"] + " " + reader["orderName"]);
    }
}
sql.Close();

ExecuteScalar() and ExecuteScalarACon()

ExecuteScalar-Methods are used to return a single "cell" or a single result from a query. The ExecuteScalar-Method has Normal and ACon variants, as well as auto-casting methods.

// Manual casting
sql.Open();
int amount = (int)sql.ExecuteScalar("SELECT COUNT(*) FROM employees ...");
sql.Close();
// Manual casting (ACon)
int amount = (int)sql.ExecuteScalarACon("SELECT COUNT(*) FROM employees ...");
// Auto casting
sql.Open();
var sum = sql.ExecuteScalar<double>("SELECT SUM(price) FROM products ...");
sql.Close();
// Auto casting (ACon)
var sum = sql.ExecuteScalarACon<double>("SELECT SUM(price) FROM products ...");

CreateDataTable()

The CreateDataTable-Method is usefull for populating form-controlls with DB-Entries:

// e.g. WinForms listbox:
listboxProducts.DisplayMember = "NameAndPrice";
listboxProducts.ValueMember = "productID";
listboxProducts.DataSource = sql.CreateDataTable("SELECT CONCAT_WS(name, price) AS NameAndPrice, productID FROM products");

No Open()/Close() is required for this method to work.

GetDataAdapter()

The GetDataAdapter-Method returns a DataAdapter-Object for further use.

MySQLDataAdapter da = sql.GetDataAdapter("SELECT * FROM ...");

No Open()/Close() is required.

Application examples

Fetching some values from a database

using(WrapSQLite sql = new WrapSQLite(@"Path/To/DB/File.db"))
{
    sql.Open();
    
    var value1 = sql.ExecuteScalar<string>("SELECT Firstname FROM customers WHERE CustomerID = ?", customerID);
    var value2 = sql.ExecuteScalar<int>("SELECT COUNT(*) FROM members");
    
    float value3 = sql.ExecuteScalar<float>("SELECT MAX(Price) FROM Items");
    
    sql.Close();
}

Inserting values into a database with a transaction

using(WrapMySQL sql = new WrapMySQL(dbData))
{
    sql.Open();
    
    sql.TransactionBegin();
    try
    {
        sql.ExecuteNonQuery("UPDATE players SET balance = balance + ? WHERE playerID = ?", 300, playerID);
        sql.ExecuteNonQuery("UPDATE businesses SET balance = balance - ? WHERE businessID = ?", 300, businessID);

        sql.TransactionCommit();
    }
    catch
    {
        sql.TransactionRollback();
    }
    
    sql.Close();
}

Using different database-types at the same time


static void Main(string[] args)
{
    WrapMySQL mysql = new WrapMySQL("ConnectionString");
    WrapSQLite sqlite = new WrapSQLite("ConnectionString", false);
    
    if(saveDataOnline) SaveData(mysql);
    else SaveData(sqlite);
}

static void SaveData(WrapSQL wrapSQLObject)
{
    // Since all WrapSQL sub-types are build on the same foundation (WrapSQLBase), 
    // it is possible to "switch" between db-types, e.g. MySQL and SQLite, 
    // without the need to call seperate methods for each db type
    
    wrapSQLObject.Open();
    wrapSQLObject.ExecuteNonQuery("UPDATE stats SET ....");
    wrapSQLObject.Close();
}




Product Compatible and additional computed target framework versions.
.NET net6.0 is compatible.  net6.0-android was computed.  net6.0-ios was computed.  net6.0-maccatalyst was computed.  net6.0-macos was computed.  net6.0-tvos was computed.  net6.0-windows was computed.  net7.0 was computed.  net7.0-android was computed.  net7.0-ios was computed.  net7.0-maccatalyst was computed.  net7.0-macos was computed.  net7.0-tvos was computed.  net7.0-windows was computed.  net8.0 was computed.  net8.0-android was computed.  net8.0-browser was computed.  net8.0-ios was computed.  net8.0-maccatalyst was computed.  net8.0-macos was computed.  net8.0-tvos was computed.  net8.0-windows was computed. 
Compatible target framework(s)
Included target framework(s) (in package)
Learn more about Target Frameworks and .NET Standard.

NuGet packages

This package is not used by any NuGet packages.

GitHub repositories

This package is not used by any popular GitHub repositories.

Version Downloads Last updated
4.0.0-pre1 396 4/23/2023
3.0.0 725 9/16/2022
2.1.0 702 9/6/2022
2.0.0.1 710 3/14/2021
2.0.0 731 9/8/2020
0.0.3 716 8/31/2020
0.0.2 918 8/16/2020