DevToys.PocoDB.Core
GuidoK73 edited this page Mar 15, 2023
·
3 revisions
DevToys.PocoDB is a simple lightweight data to object mapper.
When would DevToys.PocoDB be interesting for you?
- You want to stay close to SQL (no translations between Linq and SQL, just SQL as it is.)
- You don't want entities but just want simple poco objects.
- You want to do a SqlServer bulk insert from a poco object source.
- You want to insert Random Data quickly either using BulkInsert or by DbCommand objects.
- If you really don't want to write much code you can use dynamic for input and output data. (Slower Performance)
For full working examples: see Unit Tests
Create table dbo.Company
(
Id int not null identity(1,1) primary key,
[Name] varchar(128) not null,
Adress varchar(128) null,
Country varchar(128) not null,
ZipCode varchar(16) not null,
HouseNumber varchar(16) not null,
Text varchar(256) null,
CompanyType int not null default 1,
)
go DataConfiguration.Instance.Add<SqlConnection>(
new ConnectionConfig()
{
Key = "Local",
ConnectionString = @"Server=LAPTOP-GUIDO\SQLEXPRESS;Database=Misc;Trusted_Connection=True;"
}
);the connection object needs to be a DbConnection inherited connection object:
SqlConnection, OleDbConnection, FireBirdConnection, etc.
public enum CompanyType
{
BV = 1,
NV = 2,
LLC = 3,
GMBH = 4
}
public class Company
{
[DBField("Id")]
public int Id { get; set; }
[DBField("Name")]
public string Name { get; set; }
...
[DBField("Text")]
public string Text { get; set; }
public override string ToString() => Name;
}
[DBCommand(@"select id, [name], Adress, Country, ZipCode, HouseNumber, CompanyType, Text from dbo.Company where id = @Id", commandtype: CommandType.Text)]
public class GetCompanyById_Sql
{
[DBParameter("Id")]
public int Id { get; set; }
}
var operation = new DbCommandOperation<Company, GetCompanyById>("Local");
Company _result = operation.ExecuteSingleReader(new GetCompanyById() { Id = 1 });
[DBCommand(@"select id, [name], Adress, Country, ZipCode, HouseNumber, CompanyType, Text from dbo.Company", commandtype: CommandType.Text)]
public class GetCompanyAll
{ }
var operation = new DbCommandOperation<Company, GetCompanyAll>("Local");
IEnumerable<Company> _result = operation.ExecuteReader(new GetCompanyAll() { });
var _resultMaterialized = _result.ToList(); [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 InsertCompanyBySqlStatement
{
[DBParameter("OutputId", Direction = ParameterDirection.Output)]
public int Id { get; set; }
[DBParameter("name")]
public string Name { get; set; }
...
}
var operation = new DbCommandOperation<InsertCompanyBySqlStatement>("Local");
InsertCompanyBySqlStatement parameters = new InsertCompanyBySqlStatement()
{
Adress = "",
Country = "NLD",
HouseNumber = "180",
Name = "A Company Name",
ZipCode = "5624AC",
CompanyType = CompanyType.NV,
Text = "My Name"
};
operation.ExecuteNonQuery(parameters);
int newId = parameters.Id; [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; }
}
var operation = new DbCommandOperation<InsertCompanyRandom>("Local");
InsertCompanyRandom parameters = new InsertCompanyRandom() { };
for (int ii = 0; ii < 50; ii++)
{
operation.ExecuteNonQuery(parameters);
int newId = parameters.Id;
}
// Delete all companies outside Transaction.
var deleteoperation = new DbCommandOperation<DeleteAllCompanies>("Local");
deleteoperation.ExecuteNonQuery();
var operation1 = new DbCommandOperation<InsertCompanyByProcedure>("Local");
var operation2 = new DbCommandOperation<InsertCompanyBySqlStatement>("Local");
using (DbConnection connection = operation1.CreateConnection())
{
// operation1.CreateConnection() : operation1 determines the connection to use.
// the result is the same as operation2.CreateConnection(), they both create a ConnectionObject from Config "Local"
connection.Open();
using (DbTransaction transaction = connection.BeginTransaction())
{
try
{
InsertCompanyByProcedure insert1 = new InsertCompanyByProcedure()
{
Adress = "",
Country = "NLD",
HouseNumber = "",
Name = "A Company Name",
ZipCode = "4555AA",
CompanyType = CompanyType.NV,
Text = "Guido Kleijer2"
};
operation1.ExecuteNonQuery(connection, transaction, insert1);
int newId = insert1.Id;
// Too large ZipCode
InsertCompanyBySqlStatement insert2 = new InsertCompanyBySqlStatement()
{
Adress = "",
Country = "NLD",
HouseNumber = "",
Name = "A Company Name ",
ZipCode = "4555AA TO LONG AAAAAAAAAAAbbbbbbbbbbbAAAAAAAAA TO LONG",
CompanyType = CompanyType.NV,
Text = "Guido Kleijer"
};
operation2.ExecuteNonQuery(connection, transaction, insert2);
int newId2 = insert2.Id;
transaction.Commit();
}
catch (Exception)
{
transaction.Rollback();
}
}
connection.Close();
}
Bulk Insert Features rely on System.Data.SqlClient and are SqlServer only.
Bulk Insert is available in a separate nuget package: DevToys.PocoDB.BulkInsert.Core
[DBBulkInsert("dbo.Company")]
public class BulkCompany
{
[DBParameter("Id")]
public int Id { get; set; } = 0;
[DBParameter("Name")]
public string Name { get; set; } = string.Empty;
...
[DBParameter("CompanyType")]
public CompanyType CompanyType { get; set; } = CompanyType.BV;
}
List<BulkCompany> _data = new List<BulkCompany>();
for (int ii = 0; ii < 10000; ii++)
_data.Add(new BulkCompany() { Name = "Guido", ZipCode = "4624JC", CompanyType = CompanyType.LLC });
BulkInsertOperation<BulkCompany> operation = new BulkInsertOperation<BulkCompany>("Local", 2096);
operation.Progress += Operation_Progress;
operation.Insert(_data);
[DBBulkInsert("dbo.Company")]
public class BulkCompanyRandom
{
[DBRandomParameter("Id")]
public int Id { get; set; } = 0;
[DBRandomParameter("Name", RandomStringType = RandomStringType.FullName)]
public string Name { get; set; } = string.Empty;
[DBRandomParameter("Adress", RandomStringType = RandomStringType.Adress)]
public string Adress { get; set; } = string.Empty;
[DBRandomParameter("Country", RandomStringType = RandomStringType.Country)]
public string Country { get; set; } = string.Empty;
[DBRandomParameter("ZipCode", RandomStringType = RandomStringType.ZipCode)]
public string ZipCode { get; set; } = string.Empty;
[DBRandomParameter("HouseNumber", RandomStringType = RandomStringType.Number, Min = 10, Max = 200)]
public string HouseNumber { get; set; } = string.Empty;
[DBRandomParameter("CompanyType")]
public CompanyType CompanyType { get; set; } = CompanyType.BV;
[DBParameter("Text")]
public string Text { get; set; } = string.Empty;
}
BulkInsertOperation<BulkCompanyRandom> operation = new BulkInsertOperation<BulkCompanyRandom>("Local", 1024);
operation.Progress += Operation_Progress;
operation.Insert(500000);