Database access¶
Energy.Core.Source provides access to any SQL database source. It uses ADO.NET so any compatible driver can be used.
How this can be helpful?
It gives bunch of primitive functions that can be done using SQL database. That means executing queries, reading data from them, and maintain a connection if needed. This class also provides error handling.
Higher levels of database abstractions like models or ORM mapping are not concerned here.
Connection¶
Create Energy.Source.Connection object to enable database access layer operations.
You need to specify vendor class which implements IDbConnection interface from ADO.NET.
Energy.Source.Connection(Type vendor)
Energy.Source.Connection(Type vendor, string connectionString)
Energy.Source.Connection<Type>()
Energy.Source.Connection<Type>(string connectionString)
Vendor¶
| Provider | Class |
|---|---|
| MySQL | MySql.Data.MySqlClient.MySqlConnection |
| SqlServer | System.Data.SqlClient.SqlConnection |
| SQLite | System.Data.SQLite.SQLiteConnection |
Example¶
Energy.Source.Connection<MySql.Data.MySqlClient.MySqlConnection> db;
db = new Energy.Source.Connection<MySql.Data.MySqlClient.MySqlConnection>();
db.ConnectionString = @"Server=127.0.0.1;Database=test;Uid=test;Pwd=test;";
Execute¶
Execute SQL query.
For UPDATE, INSERT, and DELETE statements, the return value is the number of rows affected by the command.
For all other types of statements, the return value is -1.
On error, return value is -2.
int Execute(string query, out string error)
int Execute(string query)
Load¶
Load data from query into DataTable.
DataTable Load(string query, out string error)
DataTable Load(string query)
Read¶
Read query results into DataTable.
This function will populate values in a loop using IDataReader.
DataTable Read(string query, out string error)
DataTable Read(string query)
Fetch¶
Fetch query results into Energy.Base.Table.
Energy.Base.Table Fetch(string query, out string error)
Energy.Base.Table Fetch(string query)
Scalar¶
Execute SQL query and read scalar value as a result.
object Scalar(string query, out string error)
object Scalar(string query)
T Scalar<T>(string query, out string error)
T Scalar<T>(string query)
Persistent¶
If you want to limit connections to your database source you may turn on pertistent option by setting Persistent to true.
It’s not normally required to do that so at database access layer, but you still might need it in some particular cases.
Only one ADO.NET connection object will be used through multiple executions. This way concurrent SQL executions will have to wait which is acquired with standard .NET locking mechanism. That will have performance impact for sure, although it might not be an issue.
Useful when using in-memory database connections created per connection.
Running hundreds or thousands of operations on the database may cause performance results to be different when using persistent option.
Events¶
Event fired when vendor connection object is created by Activator.
event EventHandler OnCreate;
Event fired when vendor connection is open.
event EventHandler OnOpen;
Event fired when vendor connection was closed.
event EventHandler OnClose;