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                
This command is intended to be used within the Package Manager Console in Visual Studio, as it uses the NuGet module's version of Install-Package.
<PackageReference Include="DevToys.PocoDB.Core" Version="1.1.6" />                
For projects that support PackageReference, copy this XML node into the project file to reference the package.
paket add DevToys.PocoDB.Core --version 1.1.6                
#r "nuget: DevToys.PocoDB.Core, 1.1.6"                
#r directive can be used in F# Interactive and Polyglot Notebooks. Copy this into the interactive tool or source code of the script to reference the package.
// 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 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. 
Compatible target framework(s)
Included target framework(s) (in package)
Learn more about Target Frameworks and .NET Standard.
  • 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