commanet.Db.SQL
1.1.1
See the version list below for details.
dotnet add package commanet.Db.SQL --version 1.1.1
NuGet\Install-Package commanet.Db.SQL -Version 1.1.1
<PackageReference Include="commanet.Db.SQL" Version="1.1.1" />
<PackageVersion Include="commanet.Db.SQL" Version="1.1.1" />
<PackageReference Include="commanet.Db.SQL" />
paket add commanet.Db.SQL --version 1.1.1
#r "nuget: commanet.Db.SQL, 1.1.1"
#:package commanet.Db.SQL@1.1.1
#addin nuget:?package=commanet.Db.SQL&version=1.1.1
#tool nuget:?package=commanet.Db.SQL&version=1.1.1
commanet.Db
Provides interface with SQL Databases. ADO.Net based.
Opening Connection:
// 1. With minimum parameters
var db = new SQLDBConnection(DbType, DbUser, DbPassword, DbConStr);
// 2. With advanced parameters
var db = SQLDBConnection(DbTypeew SqlDbConnectionInfo()
{
DbUser = dbUser,
DbPassword = dbPassword,
DbConnectionString = dbConnection,
Pooling = true,
ConnectionTimeout = 60, // Parameter supported by ORACLE, SqlServer, PostgreSQL
ConnectionLifetime = 120, // Parameter supported by ORACLE, SqlServer
DecrPoolSize = 5; // Parameter supported by ORACLE
IncrPoolSize = 2; // Parameter supported by ORACLE
LoadBalancing = false; // Supported by ORACLE (Used for ORACLE custer)
HAEvents = false; // Supported by ORACLE (Used for ORACLE custer)
});
// 3. With single connection string
var db = new SQLDBConnection(FullConnectionString);
DbType : String value, one of: ORACLE,SqlServer,PostgreSQL,MySQL,SQLite
DbConStr : Connection string. Format depends of DBMS Used
FullConnectionString : Contains ';' separated key-value pairs in format <key>=<value>. Variable names (not case sensitive) are "Type", "User", "Password","Connection" and other matching with property names in SqlDbConnectionInfo type presented above.
Example: "Type=ORACLE;User=system;Password=manager;connection=localhost:orcl;Pooling=False"
To use particular database type will need to add to project dependencies related package.
| DB Type | Required package | Connection String Format |
|---|---|---|
| ORACLE | Oracle.ManagedDataAccess.Core | host[ [:service_name] | [:port:service_name] ]<br>Examples:<br>- localhost:MYDB<br>- localhost:1521:LEVEL2 |
| SQLServer | System.Data.SqlClient | host[:database_name]<br>Examples:<br>- localhost<br>- localhost:LEVEL2 |
| PostgreSQL | Npgsql | host[ [:database_name] | [:port:database_name] ]<br>Examples:<br>- localhost<br>- localhost:MYDB |
| MySQL | MySql.Data | host[ [:database_name] | [:port:database_name] ]<br>Examples:<br>- localhost<br>- localhost:MYDB |
| SQLite | System.Data.SQLite.Core | [filename] | :memory:<br>Examples:<br>- C:\MyDB.db3<br>- :memory: |
| ODBC | System.Data.Odbc | [ODBC Connection String]<br>Examples:<br>- Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=MyDb.mdb<br>- Driver={Microsoft Excel Driver (*.xls)};DBQ=book1.xls |
Notes About ODBC
You should check that driver you use is installed in your OS. It must match with your application architecture:
x86 or x64. In Windows can check it in ODBC Datasource Administrator: Control Panel / Administrative Tools / Odbc Data Sources (32-bit/64-bit).
Driver name passed in connection string must exactly match with one showed in ODBC Datasource Administrator.
If required driver is missing then it must be installed. For example, Windows10 usually does not have 64-bit driver
for Microsoft Access. You have to install AccessDatabaseEngine_X64.exe. It can be downloaded for free from Microsoft's web site.
Another specific of ODBC is that it does not support named parameters. By this reason all parameters passed will be
identified by postion only. Parameter names are ignored.
Parameters in SQL Operators
Regardless to parameter name agreement used for different databases names used in SQL queries should use ORACLE notation
with semicolon prefix:
SELECT * FROM mytable WHERE id=:MYPARAMETER
Names will be automatically converted to specific database format if it needs.
Read Data f
Read multiple rows:
var db = new SQLDBConnection(DbType, DbUser, DbPassword, DbConStr);
var SQL = "SELECT value FROM mytable";
db.ExecuteReader(SQL,(rd)=>{
var value = rd.GetString(0);
.....
return true; // true - continue read next row
// false - stop reading
});
Read single value:
var db = new SQLDBConnection(DbType, DbUser, DbPassword, DbConStr);
var SQL = "SELECT int_value FROM mytable";
int? value = db.ReadOneInt(SQL);
//There are many versions of method ReadOneXXX: ReadOneString, ReadOneDouble, ReadOneDateTime...
...
Using SQL Parameters
Parameters accepted as last optional arguments in methods ExecuteReader, ReadOneXXX and others.
var db = new SQLDBConnection(DbType, DbUser, DbPassword, DbConStr);
var SQL = "SELECT int_value FROM mytable WHERE key=:K";
int? value = db.ReadOneInt(SQL,
DbParam.New("K",1) // <= DbParam is helper class to create
); // SQL Parameters
...
Data Manipulation (DML)
All data changes can be performed only inside transaction. Transaction wrapped in Transaction method where as parameter passed an Action (lambda or other class method) with signature:
void MyTransactionMethod( TransactionHelper ac, IsolationLevel isolation = IsolationLevel.ReadCommitted);
{
// TO-DO: implement DML operations
}
Example:
var db = new SQLDBConnection(DbType, DbUser, DbPassword, DbConStr);
db.Transaction((th)=>{ // th is instance of transaction helper class provides methods for execute DML or DDL operations
var SQL = "INSERT INTO mytable (fieldq) VALUES(:V)";
th.ExecuteNonQuery(SQL,DbParam.New("V","Hello World"));
}); // Transaction will be automatically COMMITed after finishing passed action
// In case of unmanaged exception happens - ROLLBACK will be called
Full Transaction method signature:
public void Transaction(Action<TransactionHelper> ac, IsolationLevel isolation = IsolationLevel.ReadCommitted, bool NoTransaction = false)
Last parameter NoTransaction, if it is true then transaction will not be open. Transaction helper instance will just provide methods to execute DB writing operations (DDL, DML) which will works out of any transaction. It can be useful for some DBMS engines not supported DDL operations in transactions. Another reason is to improve performance in case if consistency is not important.
| Product | Versions Compatible and additional computed target framework versions. |
|---|---|
| .NET | net5.0 was computed. net5.0-windows was computed. net6.0 was computed. 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. net9.0 was computed. net9.0-android was computed. net9.0-browser was computed. net9.0-ios was computed. net9.0-maccatalyst was computed. net9.0-macos was computed. net9.0-tvos was computed. net9.0-windows was computed. net10.0 was computed. net10.0-android was computed. net10.0-browser was computed. net10.0-ios was computed. net10.0-maccatalyst was computed. net10.0-macos was computed. net10.0-tvos was computed. net10.0-windows was computed. |
| .NET Core | netcoreapp3.1 is compatible. |
-
.NETCoreApp 3.1
- Microsoft.Extensions.Configuration (>= 3.1.5)
- MySql.Data (>= 8.0.20)
- Npgsql (>= 4.1.3.1)
- Oracle.ManagedDataAccess.Core (>= 2.19.70)
- System.Data.Odbc (>= 4.7.0)
- System.Data.SqlClient (>= 4.8.1)
- System.Data.SQLite.Core (>= 1.0.113.1)
NuGet packages (2)
Showing the top 2 NuGet packages that depend on commanet.Db.SQL:
| Package | Downloads |
|---|---|
|
commanet.Db.ORMLite
Liteweight ORM interface for SQL databases |
|
|
commanet.Excel
Package for read/write Excel documents |
GitHub repositories
This package is not used by any popular GitHub repositories.
Initial Release