Endev.WrapSql 4.0.0-pre1

This is a prerelease version of Endev.WrapSql.
dotnet add package Endev.WrapSql --version 4.0.0-pre1                
NuGet\Install-Package Endev.WrapSql -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" 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 --version 4.0.0-pre1                
#r "nuget: Endev.WrapSql, 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 as a Cake Addin
#addin nuget:?package=Endev.WrapSql&version=4.0.0-pre1&prerelease

// Install Endev.WrapSql as a Cake Tool
#tool nuget:?package=Endev.WrapSql&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.
  • net6.0

    • No dependencies.

NuGet packages (4)

Showing the top 4 NuGet packages that depend on Endev.WrapSql:

Package Downloads
Endev.WrapSql.WrapMySql

Wrapper-class for MySql for simpler and cleaner code. WrapSql is a simple, lightweight database-wrapper for easier and faster development.

Endev.WrapSql.WrapSqlite

Wrapper-class for Sqlite for simpler and cleaner code. WrapSql is a simple, lightweight database-wrapper for easier and faster development.

Endev.WrapSql.WrapOdbc

Wrapper-class for Odbc for simpler and cleaner code. WrapSql is a simple, lightweight database-wrapper for easier and faster development.

Endev.WrapSql.WrapOleDb

Wrapper-class for OleDb for simpler and cleaner code. WrapSql is a simple, lightweight database-wrapper for easier and faster development.

GitHub repositories

This package is not used by any popular GitHub repositories.

Version Downloads Last updated
4.0.0-pre1 830 4/23/2023