DevToys.PocoDB.Core
1.1.6
dotnet add package DevToys.PocoDB.Core --version 1.1.6
NuGet\Install-Package DevToys.PocoDB.Core -Version 1.1.6
<PackageReference Include="DevToys.PocoDB.Core" Version="1.1.6" />
paket add DevToys.PocoDB.Core --version 1.1.6
#r "nuget: DevToys.PocoDB.Core, 1.1.6"
// Install DevToys.PocoDB.Core as a Cake Addin #addin nuget:?package=DevToys.PocoDB.Core&version=1.1.6 // Install DevToys.PocoDB.Core as a Cake Tool #tool nuget:?package=DevToys.PocoDB.Core&version=1.1.6
DevToys.PocoDB.Core
The DbCommandOperation wraps around the System.Data.Common.DbCommand object and supports all it's features in a declaritive way.
Define Connectionstring configuration.
For type anything derived from DbConnection can be used. ( FbConnection, OleDbConnection, etc).
DataConfiguration.Instance.Add<SqlConnection>(
new ConnectionConfig()
{
Key = "MySqlConfig",
ConnectionString = @"Server=LAPTOP-GUIDO\SQLEXPRESS;Database=Misc;Trusted_Connection=True;"
}
);
Define the Command
this object wraps to the DbCommand object.
it consists of 2 parts:
- The DbCommand attribute defining the query and what kind of Command type it is.
- The DbParameter(s) attribute defining each parameter for the DbCommand. supports input and output.
The query language depends on the DbConnection type used in the configuration.
[DBCommand(@"select id, [name], Adress, Country, ZipCode, HouseNumber, CompanyType, Text from dbo.Company where id = @Id", commandtype: CommandType.Text)]
public class GetCompanyById
{
[DBParameter("Id")]
public int Id { get; set; }
}
Define the Result Object
public class Company
{
[DBField("Id")]
public int Id { get; set; }
[DBField("Name")]
public string Name { get; set; }
[DBField("Adress")]
public string Adress { get; set; }
[DBField("Country")]
public string Country { get; set; }
[DBField("ZipCode")]
public string ZipCode { get; set; }
[DBField("HouseNumber")]
public string HouseNumber { get; set; }
[DBField("CompanyType")]
public CompanyType CompanyType { get; set; }
[DBField("Text")]
public string Text { get; set; }
}
Execute and consume the Query
The following query gives a single result object.
var operation = new DbCommandOperation<Company, GetCompanyById>("MySqlConfig");
Company _result = operation.ExecuteSingleReader(new GetCompanyById() { Id = 1 });
For resultsets use the ExecuteReader command.
var operation = new DbCommandOperation<Company, GetCompanyAll>("MySqlConfig");
IEnumerable<Company> _result = operation.ExecuteReader(new GetCompanyAll() { });
var _resultMaterialized = _result.ToList();
Inserting Random data
- the DBRandomParameter can be used to insert random data.
- in this example the Output parameter is used to retrieve the new id for the record.
[DBCommand(@"insert into dbo.Company ([name], Adress, Country, ZipCode, HouseNumber, CompanyType, Text)
values (@name, @Adress, @Country, @ZipCode, @HouseNumber, @CompanyType, @Text);
set @OutputId = @@IDENTITY", commandtype: CommandType.Text)]
public class InsertCompanyRandom
{
[DBParameter("OutputId", Direction = ParameterDirection.Output)]
public int Id { get; set; }
[DBRandomParameter("name", RandomStringType = RandomStringType.FirstName )]
public string Name { get; set; }
[DBRandomParameter("Adress", RandomStringType = RandomStringType.Adress )]
public string Adress { get; set; }
[DBRandomParameter("Country", RandomStringType = RandomStringType.Country )]
public string Country { get; set; }
[DBRandomParameter("ZipCode", RandomStringType = RandomStringType.ZipCode )]
public string ZipCode { get; set; }
[DBRandomParameter("HouseNumber", RandomStringType = RandomStringType.Number)]
public string HouseNumber { get; set; }
[DBRandomParameter("CompanyType")]
public CompanyType CompanyType { get; set; } = CompanyType.BV;
[DBRandomParameter("Text", RandomStringType = RandomStringType.Text, Max = 20 )]
public string Text { get; set; }
}
Inserting the random data
var operation = new DbCommandOperation<InsertCompanyRandom>("MySqlConfig");
InsertCompanyRandom parameters = new InsertCompanyRandom() { };
for (int ii = 0; ii < 50; ii++)
{
operation.ExecuteNonQuery(parameters);
int newId = parameters.Id;
}
Working with array parameters
Arrays can be used as parameters as well.
[DBCommand(@"select * from dbo.Company where id in (select convert(int, [value]) from STRING_SPLIT (@ids, ','));", commandtype: CommandType.Text)]
public class GetCompanies
{
[DBStringArrayParameter("Ids")]
public int[] Id { get; set; }
}
Executing a command with an array parameter.
var operation = new DbCommandOperation<Company, GetCompanies>("MySqlConfig");
var parameters = new GetCompanies() { Id = new int[] { 1, 3, 6, 9 } };
IEnumerable<Company> _result = operation.ExecuteReader(parameters);
var _resultMaterialized = _result.ToList();
Note: this sql example works only for Microsoft Sql Server.
Inserting binary data
[DBCommand("Insert into dbo.BinaryData (Name, Photo) values (@Name, @Photo);", commandtype: CommandType.Text)]
public class InsertPhoto
{
[DBParameter("Name")]
public string Name { get; set; }
[DBParameter("Photo")]
public byte[] Photo { get; set; }
}
var operation = new DbCommandOperation<InsertPhoto>("MySqlConfig");
IEnumerable<InsertPhoto> commands = GetFiles().Select(p => new InsertPhoto() { Photo = File.ReadAllBytes(p.FullName), Name = p.Name });
operation.ExecuteNonQuery(commands);
Product | Versions Compatible and additional computed target framework versions. |
---|---|
.NET | net5.0 is compatible. net5.0-windows was computed. 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 is compatible. 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 is compatible. 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. |
-
net5.0
- No dependencies.
-
net6.0
- No dependencies.
-
net7.0
- No dependencies.
-
net8.0
- No dependencies.
NuGet packages (1)
Showing the top 1 NuGet packages that depend on DevToys.PocoDB.Core:
Package | Downloads |
---|---|
DevToys.PocoDB.BulkInsert.Core
Bulkinsert wrapper on DevToys.PocoDB Readme: https://www.nuget.org/packages/DevToys.PocoDB.BulkInsert.Core/#readme-body-tab |
GitHub repositories
This package is not used by any popular GitHub repositories.
Version | Downloads | Last updated |
---|---|---|
1.1.6 | 238 | 12/22/2023 |
1.1.5 | 258 | 4/7/2023 |
1.1.3 | 233 | 3/22/2023 |
1.1.2 | 649 | 1/28/2022 |
1.1.0 | 5,467 | 11/23/2021 |
1.0.9 | 454 | 8/26/2021 |
1.0.8 | 343 | 8/25/2021 |
1.0.7 | 320 | 8/20/2021 |
1.0.6 | 335 | 4/6/2021 |
1.0.5 | 336 | 4/5/2021 |
1.0.4 | 360 | 4/5/2021 |
1.0.3 | 361 | 4/4/2021 |
1.0.2 | 370 | 4/4/2021 |
1.0.1 | 336 | 4/4/2021 |
1.0.0 | 403 | 4/4/2021 |
V1.1.3
Updated Readme and moved to Net6.0
V1.1.0
Improved performance
V1.0.9
BulkInsert moved to other NugetPackage: DevToys.PocoDB.BulkInsert.Core