A simple ORM for the MySql.Data NuGet package to use in any projects allowing for an easier and cleaner experience with basic MySQL operations.
This package currently only supports .NET 6
You do not need to add the MySql.Data package as it is already a dependency contained within this package.
Install-Package Fly.SQL
dotnet add package Fly.SQL
<PackageReference Include="Fly.SQL" Version="1.0.3" /> paket add Fly.SQL
#r "nuget: Fly.SQL, 1.0.3"
// Cake Addin
#addin nuget:?package=Fly.SQL&version=1.0.3
// Cake Tool
#tool nuget:?package=Fly.SQL&version=1.0.3
SQL is the base class to use for the methods to be added
These are condensed versions of MySqlDataReader's parsing methods for incoming values (GetInt32, GetString, ...)
Guid(int)String(int)Int(int)Long(int)Double(int)Float(int)Decimal(int)Date(int)Bool(int)
Same as above, but for possible null values. Condensed versions of MySqlDataReader null checks for incoming values (IsDBNull).
NGuid(int)- Returns null if null
NString(int)- Returns
""if null
- Returns
NStrung(int)- Returns null if null
NInt(int)- Returns 0 if null
NLong(int)- Returns 0 if null
NDouble(int)- Returns 0 if null
NFloat(int)- Returns 0 if null
NDecimal(int)- Returns 0.0m if null
NDate(int)- Returns DateTime.MinValue if null
NBool(int)- Returns false if null
The Close method simply closes the connection to the database. The Finish method is used to prepare and execute other queries.
Query(string statement, string param, object value)Select(string table)Select(string table, string target)Select(string table, string target, object value)Insert(string table, params string[] columns)OneInsert(string table, params object[] values)Update(string table, string target, params string[] columns)OneUpdate(string table, string target, object targetValue, params object[] values)Delete(string table, string target)Delete(string table, string target, object value)Mad(params object[] values)Bulk(params object[] values)
Full Read All Example
Full Read Example
Full Add Example
Full Edit Example
Full Delete Example
We will use User as the class for the examples
Add the using statement and SQL base class to setup the file for use of the package.
using FlySQL;
public class Example : SQL public User Get(int id)
{
Connect($"server={server};port={port};database={database};user={username};password={password}");
//Connect(string connection-string);
...
} public User Get(int id)
{
Connection = $"server={server};port={port};database={database};user={username};password={password}";
Connect();
...
} public User Get(int id)
{
//Connect(cs);
Query(@"SELECT * FROM users WHERE id=@userId");
//Query(string sql-query);
} public User Get(int id)
{
//Connect(cs);
//Query(statement);
Add("@userId", id);
//Add(param, value); Currently accepts int, string, bool, datetime
} public User Get(int id)
{
//Connect(cs);
//Query(statement);
//Add(param, value);
Read();
} public User Get(int id)
{
//Connect(cs);
//Query(statement);
//Add(param, value);
//Read();
// User is just a sample entity
Study();
return new User()
{
Id = Int(0),
Username = String(1),
Name = String(2),
};
} public User Get(int id)
{
//Connect(cs);
//Query(statement);
//Add(param, value);
//Read();
// User is just a sample entity
//Study();
//return new User()
//{
// ...
//};
Close();
} using FlySQL;
public class ReadUsers : SQL
{
public List<User> Get()
{
Connect($"server={server};port={port};database={database};user={username};password={password}");
Query(@"SELECT * FROM users");
Read();
// User is just a sample entity
List<User> users = new List<User>();
while (Study())
{
users.Add(new User()
{
Id = Int(0),
Username = String(1),
Name = String(2),
});
}
return users;
Close();
}
} using FlySQL;
public class ReadUser : SQL
{
public User Get(int userId)
{
Connect($"server={server};port={port};database={database};user={username};password={password}");
Query(@"SELECT * FROM users WHERE id=@userId");
Add("@userId", userId);
Read();
// User is just a sample entity
Study();
return new User()
{
Id = Int(0),
Username = String(1),
Name = String(2),
};
Close();
}
} using FlySQL;
public class AddUser : SQL
{
public void Add(User user)
{
Connect($"server={server};port={port};database={database};user={username};password={password}");
// Assume id is an integer and auto-incrementing
// User is just a sample entity
Query(@"INSERT INTO users (username, name) VALUES (@username, @name)");
// Add parameters
Add("@username", user.Username);
Add("@name", user.Name);
Finish(); // prepare statement and execute
}
} using FlySQL;
public class EditUser : SQL
{
public void Edit(User user)
{
Connect($"server={server};port={port};database={database};user={username};password={password}");
// User is just a sample entity
Query($@"UPDATE users SET username=@username, name=@name WHERE id=@userId");
// Add parameters
Add("@username", user.Username);
Add("@name", user.Name);
Add("@userId", user.Id);
Finish(); // prepare statement and execute
}
} using FlySQL;
public class DeleteUser : SQL
{
public void Delete(int id)
{
Connect($"server={server};port={port};database={database};user={username};password={password}");
// User is just a sample entity
Query(@"DELETE FROM users WHERE userId=@userId");
Add("@userId", id); // Add Parameter
Finish(); // prepare statement and execute
}
}Quick queries are used to simplify basic sql statements even further and are used in the following examples.
using FlySQL;
public class ReadUsers : SQL
{
public List<User> Get(int id)
{
Connect("connection-string");
// users and courses are just sample entities
Query(@"SELECT * FROM courses WHERE courseId IN (SELECT courseId FROM course_students WHERE userId=@userId)", "userId", id);
// The Query(stm, param, value) method is used for if you have a more complex query with only one parameter
// The parameter is added within the method
Read();
// User is just a sample entity
List<User> users = new List<User>();
while (Study())
{
users.Add(new User()
{
Id = Int(0),
Username = String(1),
Name = String(2),
});
}
Close();
return users;
}
} using FlySQL;
public class ReadUsers : SQL
{
public List<User> Get()
{
Connect("connection-string");
// User is just a sample entity
Select("users");
//SELECT * FROM users
// similar to Query(); but without the need to write the SQL statement, just put the table name if you want to select all columns
Read();
// User is just a sample entity
List<User> users = new List<User>();
while (Study())
{
users.Add(new User()
{
Id = Int(0),
Username = String(1),
Name = String(2),
});
}
Close();
return users;
}
} using FlySQL;
public class ReadUser : SQL
{
public User Get(int id)
{
Connect("connection-string");
// User is just a sample entity
Select("users", "userId");
//SELECT * FROM users WHERE userId=@userId
/* similar to Query(); but without the need to write the SQL statement, just put the table name and the target column name if you want to select a single value */
/* Note: this command only accepts one target column name. The column name is optional, and if not specified, the entire table will be returned */
Add("@userId", Id);
/* parameters add like normal, but make sure that they are same name as the target column name */
Read();
// User is just a sample entity
Study();
return new User()
{
Id = Int(0),
Username = String(1),
Name = String(2),
};
Close();
}
} using FlySQL;
public class ReadUser : SQL
{
public User Get(int id)
{
Connect("connection-string");
// User is just a sample entity
Select("users", "userId", id);
//SELECT * FROM users WHERE userId=@userId
// Just like Select(table, target) but the target value added and no need to use Add()
Read();
// User is just a sample entity
Study();
return new User()
{
Id = Int(0),
Username = String(1),
Name = String(2),
};
Close();
}
} using FlySQL;
public class AddUser : SQL
{
public void Add(User user)
{
Connect("connection-string");
// Assume id is an integer and auto-incrementing
// User is just a sample entity
Insert("users", "username", "name");
// table name, column name, column name, ...
//INSERT INTO users (username, name) VALUES (@username, @name)
/* The Insert command will take the table name as the first parameter, and any parameters after that are the column names */
/* Add parameters like normal, but be sure to use the same name as the column name */
Add("@username", user.Username);
Add("@name", user.Name);
Finish(); // prepares statement and executes, only if using Add()
/* You can also add multiple values at once with the Bulk() command */
Bulk("username", user.Username, "name", user.Name);
// Bulk("column name", value, column name, value, ...)
/* This command will throw an error in the request if the number of parameters is not even, but there is not a limit to the number of parameters you can add */
/* This method can be used after any other method that accepts adding parameters mentioned above */
/* If you use Bulk(), you cannot use Add() or Bulk() again after its use, and no other commands are to be used after as well, including Finish(), as the Bulk() command assumes all values are added and immediately executes */
/* You can use Add() before Bulk() if you want to add multiple values at once after having added one at a time with Add() */
}
} using FlySQL;
public class AddUser : SQL
{
public void Add(User user)
{
Connect("connection-string");
// Assume id is an integer and auto-incrementing
// User is just a sample entity
OneInsert("users", "username", user.Username, "name", user.Name);
// table name, param pairs
//INSERT INTO users (username, name) VALUES (@username, @name)
//Will auto run Finish() after OneInsert is called, assuming that all values have been added in the OneInsert() method
}
} using FlySQL;
public class EditUser : SQL
{
public void Edit(User user)
{
Connect("connection-string");
// User is just a sample entity
Update("users", "userId", "username", "name");
// table name, target name, column name, column name, ...
// UPDATE users SET username=@username, name=@name WHERE userId=@userId
// Add parameters
Add("@username", user.Username);
Add("@name", user.Name);
Add("@userId", user.Id);
Finish(); // prepare statement and execute
// Or Bulk() with auto execute
Bulk("userId", user.Id, "username", user.Username, "name", user.Name);
}
} using FlySQL;
public class EditUser : SQL
{
public void Edit(User user)
{
Connect("connection-string");
// User is just a sample entity
OneUpdate("users", "userId", user.Id,"username", user.Username, "name", user.Name);
// table name, target name, target value, param pairs ...
// UPDATE users SET username=@username, name=@name WHERE userId=@userId
// Will auto run Finish() after OneUpdate is called, assuming that all values have been added in the OneUpdate() method
}
} using FlySQL;
public class DeleteUser : SQL
{
public void Delete(int id)
{
Connect("connection-string");
// User is just a sample entity
Delete("users", "userId");
// table name, target name
// DELETE FROM users WHERE userId=@userId
Add("@userId", id); // Add Parameter
Finish(); // prepare statement and execute
}
} using FlySQL;
public class DeleteUser : SQL
{
public void Delete(int id)
{
Connect("connection-string");
// User is just a sample entity
Delete("users", "userId", id);
// table name, target name, target value
// DELETE FROM users WHERE userId=@userId
// Will auto run Finish() after Delete() is called
}
} using FlySQL;
// Does not have to just be edit, this is just an example to show how to use the method
public class EditUser : SQL
{
public void Edit(User user)
{
Connect("connection-string");
// User is just a sample entity
Update("users", "userId", "username", "name");
// table name, target name, column name, column name, ...
// UPDATE users SET username=@username, name=@name WHERE userId=@userId
// Add parameters
Mad("@username", user.Username, "@name", user.Name, "@userId", user.Id);
// Similar to Bulk() but does not auto Finish()
// Add() can be used before and after Mad()
// Mad() will throw an error if the number of parameters is not even, but there is not a limit to the number of parameters you can add
Finish(); // prepare statement and execute
}
}The FlySQL source code is made available under the MIT license.