A .net standard client for MySQL based on MySQLConnector
- Added Query Composer
Library examples
NOTE: You can also pass to MySQLClient constructor the default connectino string
public async Task ExampleNormal()
{
MySQLConnectionString cs = new MySQLConnectionString("myHost", "3306", "schema", "user", "pass");
using (var client = new MySQLClient(cs))
{
// execute query here
}
}
public async Task ExampleTransaction()
{
bool condition = true;
MySQLConnectionString cs = new MySQLConnectionString("myHost", "3306", "schema", "user", "pass");
using (var client = new MySQLClient(cs))
{
await client.BeginTransactionAsync();
// do stuff
if (condition)
await client.CommitAsync();
else
await client.RoolbackAsync();
}
}
MySQLClient close the connection when the object is disposed. However you can force the connection close calling the CloseConnection method. However the connection is opened automatically when you start a transaction or perform a query.
We have three type of query for the library:
- ICollection: return a Collection of T where T is a class with default constructor
- ICollection: return a Collection of an array of object
- IMySqlResponse: return an object used to get informations about insert\delete\update
In this way you can get an ICollection of table Users (where Users is a class with the SAME properties)
public async Task ExampleQueryList()
{
MySQLConnectionString cs = new MySQLConnectionString("myHost", "3306", "schema", "user", "pass");
using (var client = new MySQLClient(cs))
{
var result = client.QueryAsync<User>("SELECT * FROM Users");
Console.WriteLine(result.Count > 0 ? result.First().Name : "empty");
}
}
In this way you can get an ICollection of array where every column is an index of the listed array
public async Task ExampleQueryArray()
{
MySQLConnectionString cs = new MySQLConnectionString("myHost", "3306", "schema", "user", "pass");
using (var client = new MySQLClient(cs))
{
var result = await client.QueryArrayAsync("SELECT * FROM Users");
Console.WriteLine(result.Count > 0 ? result.First()[0] : "empty");
}
}
In this way you can get an ICollection of array where every column is a collection of dictionary
public async Task ExampleQueryArray()
{
MySQLConnectionString cs = new MySQLConnectionString("myHost", "3306", "schema", "user", "pass");
using (var client = new MySQLClient(cs))
{
var result = await client.QueryArrayAsync("SELECT * FROM Users");
Console.WriteLine(result.Count > 0 ? result.First().First(x => x.Key == "Surname").Value : "empty");
//or
Console.WriteLine(result.Count > 0 ? result.First()["Surname"] : "empty");
}
}
Here we have an example of a query that doesn't return a resultset but an IMySQLResponse object
NOTE: In this case we use the prepare statement with an args. See "Query with args" section.
public async Task ExampleQueryWithResponse()
{
long toDelete = 10;
MySQLConnectionString cs = new MySQLConnectionString("myHost", "3306", "schema", "user", "pass");
using (var client = new MySQLClient(cs))
{
var result = await client.QueryAsync($"DELETE FROM Users WHERE Id=@id", new List<DbParameter>() {
new MySqlParameter(){ DbType = System.Data.DbType.Int64, Value=toDelete, ParameterName="@id" }
});
Console.WriteLine(result.GetRowsAffected() > 0 ? "deleted" : "not deleted");
}
}
When you get some args from web or other sources, you should prepare the statement before execute the query. With this library you can do it this way.
You just need to pass a List of DbParameter, in specific a list of MySqlParameter where in it you need to set at least:
- Value: the value of parameter
- ParameterName: the placeholder you have used in the query, ex. "@id"
- DbType: The database type (better for quickly cast)
public async Task ExampleQueryWithArgs()
{
MySQLConnectionString cs = new MySQLConnectionString("myHost", "3306", "schema", "user", "pass");
using (var client = new MySQLClient(cs))
{
var name = "Andrew";
var alive = true;
var result1 = await client.QueryAsync<User>($"SELECT * Users WHERE Name=@name AND IsAlive=@alive", new List<DbParameter>() {
new MySqlParameter(){ DbType = System.Data.DbType.String, Value=name, ParameterName="@name" },
new MySqlParameter(){ DbType = System.Data.DbType.Boolean, Value=alive, ParameterName="@alive" }
});
}
}
When the query is simple, like a single insert or a simple select on one table, you can use the simplified queries
An insert example
NOTE: in this case the class name must be the same of the table, if your tables use a prefix, you can pass it like second argument to Insert
public async Task ExampleInsert()
{
MySQLConnectionString cs = new MySQLConnectionString("myHost", "3306", "schema", "user", "pass");
using (var client = new MySQLClient(cs))
{
var result = client.InsertAsync<User>(new User() { Name = "Andrew", IsAlive = true });
}
}
IMPORTANT: You can also ignore fields for insert, like the id that many time is an autoincrement field.
You can do that with the "Attribute mapping". Check the section.
A delete example
NOTE: in this case the class name must be the same of the table, if your tables use a prefix, you can pass it like second argument to Delete
public async Task ExampleDelete()
{
MySQLConnectionString cs = new MySQLConnectionString("myHost", "3306", "schema", "user", "pass");
using (var client = new MySQLClient(cs))
{
var result = client.DeleteAsync<User>(new User() { Id = 4});
}
}
IMPORTANT: You must set at least a "CheckDelete" property in the class definition.
You can do that with the "Attribute mapping". Check the section.
An Update example
NOTE: in this case the class name must be the same of the table, if your tables use a prefix, you can pass it like second argument to Update
public async Task ExampleUpdate()
{
MySQLConnectionString cs = new MySQLConnectionString("myHost", "3306", "schema", "user", "pass");
using (var client = new MySQLClient(cs))
{
var result = client.UpdateAsync<User>(new User() { Id = 5 }, new User() { Name = "Andrew", IsAlive = false });
}
}
IMPORTANT: You can ignore properties for update setting (for example the row id) and you need to set at least a property like CheckUpdate to target the row\rows to update.
You can do that with the "Attribute mapping". Check the section.
There are two way to make simplified selects
NOTE: in this case the class name must be the same of the table, if your tables use a prefix, you can pass it like second argument to Select
public async Task ExampleUpdate()
{
MySQLConnectionString cs = new MySQLConnectionString("myHost", "3306", "schema", "user", "pass");
using (var client = new MySQLClient(cs))
{
var result = client.Select<User>();
}
}
Or you can use a predicate to filter
public async Task ExampleUpdate()
{
MySQLConnectionString cs = new MySQLConnectionString("myHost", "3306", "schema", "user", "pass");
using (var client = new MySQLClient(cs))
{
var result = client.Select<User>(x=> x.Name == "Andrew");
}
}
Other stuff
You can enable debug (for example you can enable\disable it for development\production environment)
public async Task ExampleDebug()
{
if (env.IsDevelopment())
MySQLClient.DebugOn = true;
MySQLConnectionString cs = new MySQLConnectionString("myHost", "3306", "schema", "user", "pass");
using (var client = new MySQLClient(cs))
{
var result = client.Select<User>(x=> x.Name == "Andrew");
}
}
You can also set the log way. The library use the IDewLogger interface. If you don't want implement a new logger, you can use one of the built in logger:
- DewConsole: Print into a console (for console applications)
- DewDebug: Print into the visual studio debug window
- DewFileLog: Print the log into a file
- DewServerLog: Send the log like get request to a server
You can set the logger this way
public async Task ExampleDebug()
{
if (env.IsDevelopment())
{
MySQLClient.DebugOn = true;
MySQLClient.SetDebugger(new DewConsole());
}
MySQLConnectionString cs = new MySQLConnectionString("myHost", "3306", "schema", "user", "pass");
using (var client = new MySQLClient(cs))
{
var result = client.Select<User>(x => x.Name == "Andrew");
}
}
- NoTransactionException: when you commit\rollback without begin transaction
When you define a class to use the query with T object, you can also map the properties to use the calss with simplified queries. Here an example:
public class User
{
[CheckDelete] //This property will be used to target the row in Delete function
[IgnoreInsert]//This property will be ignored in the Insert function
[CheckUpdate] //This property will be used to target the row in Update function
public long Id { get; set; }
public string Name { get; set; }
public bool IsAlive { get; set; }
[IgnoreInsert]//This property will be ignored in the Insert function
[IgnoreUpdate]//This property will be ignored in the Update function
public long IdTriggered { get; set; }
[NoColumn] //This property will be always ignored
public long FriendlyName {get => Name + (IsAlive ? "alive" : "dead"); }
}
With 2.8.0 I've added the Query Composer, a way to compose a query without (or we trying :) ) syntax errors.
We have two type of it:
- SimpleQueryComposer
- ComplexQueryComposer
The simple query composer let you compose the query with intellisense but without any type of control. For example you can call two times a select.
public void Example()
{
var c1 = new MySQLSimpleQueryComposer();
string query = c1.Select().From("ExampleTable").OrderBy("IdExample").ComposedQuery();
c1.Reset();
string query1 = c1.Select("Name", "Surname").From("Users").Where("Age", ">", "18").And("Sex", "=", "M").OrderByDesc("Age").ComposedQuery();
c1.Reset();
string queryArgs = c1.Select("Name", "Surname").From("Users").Where("Age", ">", "@age").And("Sex", "=", "@sex").OrderByDesc("Age").ComposedQuery();
c1.Reset();
var c2 = new MySQLSimpleQueryComposer().Select("ExampleCode").From("ExampleCodesTable");
string queryComposed = c1.Select().From("ExampleTable as A").Join("ExampleTable1 as B").On("A.Id", "B.IdExample").Where().Column("ExampleCode").Not().In(c2).ComposedQuery();
c1.Reset();
c1.From("Table").Select().And("Age", ">", "18");
string wrongQuery = c1;
Console.WriteLine(query);
Console.WriteLine(query1);
Console.WriteLine(queryArgs);
Console.WriteLine(queryComposed);
Console.WriteLine(wrongQuery);
}
The output is:
SELECT * FROM ExampleTable ORDER BY IdExample
SELECT Name,Surname FROM Users WHERE Age > 18 AND Sex = M ORDER BY Age DESC
SELECT Name,Surname FROM Users WHERE Age > @age AND Sex = @sex ORDER BY Age DESC
SELECT * FROM ExampleTable as A INNER JOIN ExampleTable1 as B ON A.Id = B.IdExample WHERE ExampleCode NOT IN (SELECT ExampleCode FROM ExampleCodesTable )
FROM Table SELECT * AND Age > 18
As you can see, with simplequerycomposer you don't have any type of control with SQL syntax.
The complex query composer will help you to prevent syntax errors because it use intellisense to lead you through the query composition.
Example:
public void Example()
{
RootComposer c = new RootComposer();
string val = c.Select().From("dev_barb").Where().Column<WhereComposer>("idBarb").Not().In(new RootComposer().Select("idBarb").From("dev_payed").Where("Pay", ">", "0")).And("State", "=", "1").ComposedQuery();
Console.WriteLine(val);
val = c.Select().From("dev_barb as T").OrderByDesc("name", "surname").ComposedQuery();
Console.WriteLine(val);
val = c.Select().From("A as X").Join("B as Y").OrderBy("puop").ComposedQuery();
Console.WriteLine(val);
val = c.Select().From("A as X").Join("B as Y").On("X.IdUser", "Y.IdUtente").OrderBy("puop").ComposedQuery();
Console.WriteLine(val);
}
Give output:
SELECT * FROM dev_barb WHERE idBarb NOT IN (SELECT idBarb FROM dev_payed WHERE Pay > 0 ) AND State = 1
SELECT * FROM dev_barb as T ORDER BY name,surname DESC
SELECT * FROM A as X INNER JOIN B as Y ORDER BY puop
SELECT * FROM A as X INNER JOIN B as Y ON X.IdUser = Y.IdUtente ORDER BY puop
The main difference is that you need to start from RootComposer that let you to choose between starts object like SELECT,INSERT etc. Now the intellisense will lead you, for example the SELECT composer object can only go to the FROM composer object.
NOTE: This will not prevent at 100% the sql syntax errors, for example the where goes in groupby, but if you use a groupby after a where in a delete you'll get an error.
You can find it on nuget with the name DewMySQLClient