CPSysUDB 2.1.0

.NET Framework 4.7.2
Install-Package CPSysUDB -Version 2.1.0
dotnet add package CPSysUDB --version 2.1.0
<PackageReference Include="CPSysUDB" Version="2.1.0" />
For projects that support PackageReference, copy this XML node into the project file to reference the package.
paket add CPSysUDB --version 2.1.0
The NuGet Team does not provide support for this client. Please contact its maintainers for support.
#r "nuget: CPSysUDB, 2.1.0"
#r directive can be used in F# Interactive, C# scripting and .NET Interactive. Copy this into the interactive tool or source code of the script to reference the package.
// Install CPSysUDB as a Cake Addin
#addin nuget:?package=CPSysUDB&version=2.1.0

// Install CPSysUDB as a Cake Tool
#tool nuget:?package=CPSysUDB&version=2.1.0
The NuGet Team does not provide support for this client. Please contact its maintainers for support.

CPSysUDB

NuGet Packages .NET Framework 4.7.2

pt-BR

Descrição

A biblioteca foi criada com a finalidade de tornar o desenvolvimento de uma aplicação mais ágil, facilitando a criação e atualização do banco de dados, comunicação com SQL Server, MySQL, Firebird e SQLite, contolar as rotinas de banco de dados e integração universal entre todas as plataformas compatíveis com C#.

Motivação

  • Criar um sistema em um banco de dados específico mas em algum cliente era necessário outro banco de dados
  • Criação e atualização do banco de dados do cliente pela própria aplicação
  • Não usar querys de banco de dados

CPSysSQLFramework2

Esta versão é a idéia final do projeto com diversas melhorias como o uso de entidades criadas pelo desenvolvedor.

Recursos
  • Uso de entidades (classes), cada entidade é uma tabela no banco de dados
  • Rotina para sempre recriar o banco de dados (muito útil para desenvolvimento) com opção para desabilitar
  • Controle total das rotinas de criação e atualização do banco de dados
  • Comunicação híbrida com SQL Server, MySQL, Firebird e SQLite sem necessidade de criar querys separadas apenas trocando os dados de conexão
  • Auto atualização do banco de dados, tabelas e campos
  • Criação de PK com auto incremento e FK
  • Respeitar regras do banco de dados
  • Cria o banco de dados automaticamente
  • Create DataBase, Create Table, Insert, Update, Delete, Select
  • Where, Order By, Group By, Union, Limit e Joins
  • Suporte para funções do banco de dados como GETDATE e DATEADD
  • Suporte para usar um campo de uma tabela no where
Compatibilidades
  • Suporte para int, string, DateTime, double e enum
  • Compatível com WEB e Desktop
Restições
  • Respeitar as regras do banco de dados
  • Criar as tabelas em ordem de dependëncia
Como Usar
  • Importe a biblioteca
using CPSysUDB;
  • Inicie uma conexão
CPSysSQLFramework2 cPSysSQLFramework2 = new CPSysSQLFramework2(CPSysUDB.Configuration.ConnectionData.CreateConnectionSQLSRV(@"SERVER\SQLEXPRESS", true, "db_teste2", "sa", "*****"), true);
CPSysSQLFramework2 cPSysSQLFramework2 = new CPSysSQLFramework2(CPSysUDB.Configuration.ConnectionData.CreateConnectionMYSQL(@"localhost", true, "db_teste2", "root", ""), true);
CPSysSQLFramework2 cPSysSQLFramework2 = new CPSysSQLFramework2(CPSysUDB.Configuration.ConnectionData.CreateConnectionFIREBIRD(@"localhost", true, "db_teste2.fdb", "SYSDBA", "masterkey"), true);
CPSysSQLFramework2 cPSysSQLFramework2 = new CPSysSQLFramework2(CPSysUDB.Configuration.ConnectionData.CreateConnectionSQLITE(@"db_teste2.sqlite", "Version=3;"), true);
  • Excluír todas as tabelas e criar novamente, é útil para o desenvolvimento mas para produção é necessário remover o comando
cPSysSQLFramework2.DropAllTables();// reinicie o banco de dados do zero
  • Crie a sua entidade
public enum Grade
{
	A, B, C, D, F
}

[CPSysUDB.Attribute.ClassAttribute("e")]// declare a entidade como objeto do banco de dados dando a ela um apelido
public class Enrollment
{
	[CPSysUDB.Attribute.SQLAttribute(CPSysUDB.Enums.SQLTypes.PRIMARY_KEY_IDENTITY)]// declare um campo como PK ou PK auto incremento
    public int EnrollmentID { get; set; }
    [CPSysUDB.Attribute.SQLAttribute(CPSysUDB.Enums.SQLTypes.FOREIGN_KEY, typeof(Course), "CourseID")]// declare um campo como FK
    public int CourseID { get; set; }
    [CPSysUDB.Attribute.SQLAttribute(CPSysUDB.Enums.SQLTypes.FOREIGN_KEY, typeof(Student), "ID")]
    public int StudentID { get; set; }
    [CPSysUDB.Attribute.SQLAttribute(CPSysUDB.Enums.SQLTypes.NORMAL)]// declare um campo normal, é necessário declarar todos os camps que será da tabela
    public Grade Grade { get; set; }
}
  • Crie as tabelas
cPSysSQLFramework2.CreateOrAlterTable<Enrollment>();
  • Insira dados por valor
List<CPSysUDB.DAL.Values> valores1 = new List<CPSysUDB.DAL.Values>();
valores1.Add(new CPSysUDB.DAL.Values("teste"));
valores1.Add(new CPSysUDB.DAL.Values(1));
valores1.Add(new CPSysUDB.DAL.Values(20.8));
cPSysSQLFramework2.InsertInto<Course>(valores1);
  • Insira dados por objeto
Enrollment enrollment = new Enrollment();
enrollment.CourseID = 1;
enrollment.StudentID = 1;
enrollment.Grade = Grade.A;
cPSysSQLFramework2.InsertInto<Enrollment>(enrollment);
  • Atualize dados
List<CPSysSQLFramework2.Where> wheres1 = new List<CPSysSQLFramework2.Where>();
wheres1.Add(new CPSysSQLFramework2.Where(new CPSysSQLFramework2.Field().NewField<Course>("CourseID"), CPSysUDB.Enums.Command.EQUALS, new CPSysUDB.DAL.Values(1)));
List<string> campos1 = new List<string>();
campos1.Add("Title");// campos a ser atualizado
List<CPSysUDB.DAL.Values> values1 = new List<CPSysUDB.DAL.Values>();
values1.Add(new CPSysUDB.DAL.Values("testes1234"));// valores a ser atualizado respectivamente
cPSysSQLFramework2.Update<Course>(campos1, values1, wheres1);
  • Exclua dados
List<CPSysSQLFramework2.Where> wheres3 = new List<CPSysSQLFramework2.Where>();
wheres3.Add(new CPSysSQLFramework2.Where(new CPSysSQLFramework2.Field().NewField<Enrollment>("EnrollmentID"), CPSysUDB.Enums.Command.EQUALS, new CPSysUDB.DAL.Values(1)));// where do delete
cPSysSQLFramework2.Delete<Enrollment>(wheres3);
  • Consulte dados
List<CPSysSQLFramework2.Select> selects1 = new List<CPSysSQLFramework2.Select>();
List<CPSysSQLFramework2.Join> Join1 = new List<CPSysSQLFramework2.Join>();
List<CPSysSQLFramework2.Field> campos2 = new List<CPSysSQLFramework2.Field>();
List<CPSysSQLFramework2.Where> wheres4 = new List<CPSysSQLFramework2.Where>();
List<CPSysSQLFramework2.OrderBy> orders = new List<CPSysSQLFramework2.OrderBy>();
List<CPSysSQLFramework2.GroupBy> groups = new List<CPSysSQLFramework2.GroupBy>();
campos2.Add(new CPSysSQLFramework2.Field().NewField<Course>("*"));
campos2.Add(new CPSysSQLFramework2.Field().NewField<Enrollment>("*"));
campos2.Add(new CPSysSQLFramework2.Field().NewField<Student>("*"));
Join1.Add(new CPSysSQLFramework2.Join(new CPSysSQLFramework2.Field().NewField<Course>("CourseID"), new CPSysSQLFramework2.Field().NewField<Enrollment>("CourseID")));
Join1.Add(new CPSysSQLFramework2.Join(new CPSysSQLFramework2.Field().NewField<Enrollment>("StudentID"), new CPSysSQLFramework2.Field().NewField<Student>("ID")));
//wheres4.Add(new CPSysSQLFramework2.Where(new CPSysSQLFramework2.Field().NewField<Course>("CourseID"), CPSysUDB.Enums.Command.EQUALS, new CPSysUDB.DAL.Values(new CPSysSQLFramework2.Field().NewField<Student>("ID"))));// use um campo de uma tabela no where
wheres4.Add(new CPSysSQLFramework2.Where(new CPSysSQLFramework2.Field().NewField<Course>("CourseID"), CPSysUDB.Enums.Command.EQUALS, new CPSysUDB.DAL.Values(1)));
orders.Add(new CPSysSQLFramework2.OrderBy().NewOrderBy(new CPSysSQLFramework2.Field().NewField<Course>("CourseID"), CPSysUDB.Enums.Order.DESC));
groups.Add(new CPSysSQLFramework2.GroupBy().NewGroupBy(new CPSysSQLFramework2.Field().NewField<Student>("ID")));
selects1.Add(new CPSysSQLFramework2.Select().NewSelect<Course>(campos2, Join1, wheres4, orders, groups, 5));
DataSet ds1 = cPSysSQLFramework2.SelectValue(selects1);
if (ds1 != null)
{
	Console.WriteLine("    COUNT: " + ds1.Tables[0].Rows.Count);
    string column = "", rows = "";
    foreach (DataColumn dataColumn in ds1.Tables[0].Columns)
    {
		column = column + " # " + dataColumn.Caption;
    }
    Console.WriteLine("    " + column);
    foreach (DataRow dataRow in ds1.Tables[0].Rows)
    {
		rows = "";
        foreach (var item in dataRow.ItemArray)
        {
			rows = rows + " # " + item;
        }
        Console.WriteLine("    " + rows);
    }
}

en-US

Description

The library was created with a process of making the development of an application more agile, facilitating the creation and updating of the database, communication with SQL Server, MySQL, Firebird and SQLite, controlling as database routines and universal integration between all compatible platforms. Ç #.

Motivation

  • Create a system in a specific database but in some client another database was needed
  • Creation and update of the customer database by the application itself
  • Do not use database queries

CPSysSQLFramework2

This version is the final idea of ​​the project with several improvements such as the use of entities created by the developer.

Resources
  • Use of entities (classes), each entity is a table in the database
  • Routine to always recreate the database (very useful for development) with option to disable
  • Full control of database creation and update routines
  • Hybrid communication with SQL Server, MySQL, Firebird and SQLite without the need to create separate queries just by exchanging connection data
  • Auto update of database, tables and fields
  • PK creation with auto increment and FK
  • Respect database rules
  • Creates the database automatically
  • Create DataBase, Create Table, Insert, Update, Delete, Select
  • Where, Order By, Group By, Union, Limit and Joins
  • Support for database functions like GETDATE and DATEDD
  • Support for using a field from a table in where
Compatibilities
  • Support for int, string, DateTime, double and enum
  • Compatible with WEB and Desktop
Refunds
  • Respect database rules
  • Create the tables in order of dependence
How to use
  • import a library
using CPSysUDB;
  • Start a connection
CPSysSQLFramework2 cPSysSQLFramework2 = new CPSysSQLFramework2(CPSysUDB.Configuration.ConnectionData.CreateConnectionSQLSRV(@"SERVER\SQLEXPRESS", true, "db_teste2", "sa", "*****"), true);
CPSysSQLFramework2 cPSysSQLFramework2 = new CPSysSQLFramework2(CPSysUDB.Configuration.ConnectionData.CreateConnectionMYSQL(@"localhost", true, "db_teste2", "root", ""), true);
CPSysSQLFramework2 cPSysSQLFramework2 = new CPSysSQLFramework2(CPSysUDB.Configuration.ConnectionData.CreateConnectionFIREBIRD(@"localhost", true, "db_teste2.fdb", "SYSDBA", "masterkey"), true);
CPSysSQLFramework2 cPSysSQLFramework2 = new CPSysSQLFramework2(CPSysUDB.Configuration.ConnectionData.CreateConnectionSQLITE(@"db_teste2.sqlite", "Version=3;"), true);
  • Delete all tables and create again, it is useful for development but for production it is necessary to remove the command
cPSysSQLFramework2.DropAllTables(); // restart the database from scratch
  • Create your entity
public enum Grade
{
	A B C D F
}

[CPSysUDB.Attribute.ClassAttribute("e")]// declare the entity as a database object giving it a nickname
public class Enrollment
{
	[CPSysUDB.Attribute.SQLAttribute(CPSysUDB.Enums.SQLTypes.PRIMARY_KEY_IDENTITY)] // declare a field as PK or PK auto increment
    public int EnrollmentID { get; set; }
    [CPSysUDB.Attribute.SQLAttribute(CPSysUDB.Enums.SQLTypes.FOREIGN_KEY, typeof(Course), "CourseID")]// declare a field as FK
    public int CourseID { get; set; }
    [CPSysUDB.Attribute.SQLAttribute(CPSysUDB.Enums.SQLTypes.FOREIGN_KEY, typeof(Student), "ID")]
    public int StudentID { get; set; }
    [CPSysUDB.Attribute.SQLAttribute(CPSysUDB.Enums.SQLTypes.NORMAL)] // declare a normal field, it is necessary to declare all the fields that will be of the table
    public Grade Grade { get; set; }
}
  • Create the tables
cPSysSQLFramework2.CreateOrAlterTable<Enrollment>();
  • Enter data by value
List<CPSysUDB.DAL.Values> values1 = new List<CPSysUDB.DAL.Values>();
values1.Add(new CPSysUDB.DAL.Values("test"));
values1.Add(new CPSysUDB.DAL.Values(1));
values1.Add(new CPSysUDB.DAL.Values(20.8));
cPSysSQLFramework2.InsertInto<Course>(values1);
  • Enter data by object
Enrollment enrollment = new Enrollment();
enrollment.CourseID = 1;
enrollment.StudentID = 1;
enrollment.Grade = Grade.A;
cPSysSQLFramework2.InsertInto<Enrollment>(enrollment);
  • Update data
List<CPSysSQLFramework2.Where> wheres1 = new List<CPSysSQLFramework2.Where>();
wheres1.Add(new CPSysSQLFramework2.Where(new CPSysSQLFramework2.Field().NewField<Course>("CourseID"), CPSysUDB.Enums.Command.EQUALS, new CPSysUDB.DAL.Values(1)));
List<string> fields1 = new List<string>();
fields1.Add("Title"); // fields to update
List<CPSysUDB.DAL.Values> values1 = new List<CPSysUDB.DAL.Values>();
values1.Add(new CPSysUDB.DAL.Values("tests1234")); // values ​​to be updated respectively
cPSysSQLFramework2.Update<Course>(fields1, values1, wheres1);
  • Delete data
List<CPSysSQLFramework2.Where> wheres3 = new List<CPSysSQLFramework2.Where>();
wheres3.Add(new CPSysSQLFramework2.Where(new CPSysSQLFramework2.Field().NewField<Enrollment>("EnrollmentID"), CPSysUDB.Enums.Command.EQUALS, new CPSysUDB.DAL.Values(1)));// where do delete
cPSysSQLFramework2.Delete<Enrollment>(wheres3);
  • See data
List<CPSysSQLFramework2.Select> selects1 = new List<CPSysSQLFramework2.Select>();
List<CPSysSQLFramework2.Join> Join1 = new List<CPSysSQLFramework2.Join>();
List<CPSysSQLFramework2.Field> fields2 = new List<CPSysSQLFramework2.Field>();
List<CPSysSQLFramework2.Where> wheres4 = new List<CPSysSQLFramework2.Where>();
List<CPSysSQLFramework2.OrderBy> orders = new List<CPSysSQLFramework2.OrderBy>();
List<CPSysSQLFramework2.GroupBy> groups = new List<CPSysSQLFramework2.GroupBy>();
fields2.Add(new CPSysSQLFramework2.Field().NewField<Course>("*"));
fields2.Add(new CPSysSQLFramework2.Field().NewField<Enrollment>("*"));
fields2.Add(new CPSysSQLFramework2.Field().NewField<Student>("*"));
Join1.Add(new CPSysSQLFramework2.Join(new CPSysSQLFramework2.Field().NewField<Course>("CourseID"), new CPSysSQLFramework2.Field().NewField<Enrollment>("CourseID")));
Join1.Add(new CPSysSQLFramework2.Join(new CPSysSQLFramework2.Field().NewField<Enrollment>("StudentID"), new CPSysSQLFramework2.Field().NewField<Student>("ID")));
//wheres4.Add(new CPSysSQLFramework2.Where(new CPSysSQLFramework2.Field().NewField<Course>("CourseID"), CPSysUDB.Enums.Command.EQUALS, new CPSysUDB.DAL.Values(new CPSysSQLFieldFrame(). NewField<Student>("ID")))); // use a field from a table in where
wheres4.Add(new CPSysSQLFramework2.Where(new CPSysSQLFramework2.Field().NewField<Course>("CourseID"), CPSysUDB.Enums.Command.EQUALS, new CPSysUDB.DAL.Values(1)));
orders.Add(new CPSysSQLFramework2.OrderBy().NewOrderBy(new CPSysSQLFramework2.Field().NewField<Course>("CourseID"), CPSysUDB.Enums.Order.DESC));
groups.Add(new CPSysSQLFramework2.GroupBy().NewGroupBy(new CPSysSQLFramework2.Field().NewField<Student>("ID")));
selects1.Add(new CPSysSQLFramework2.Select().NewSelect<Course>(fields2, Join1, wheres4, orders, groups, 5));
DataSet ds1 = cPSysSQLFramework2.SelectValue(selects1);
if (ds1 != null)
{
	Console.WriteLine(" COUNT: " + ds1.Tables[0].Rows.Count);
    string column = "", rows = "";
    foreach (DataColumn dataColumn in ds1.Tables[0].Columns)
    {
		column = column + " # " + dataColumn.Caption;
    }
    Console.WriteLine(" " + column);
    foreach (DataRow dataRow in ds1.Tables[0].Rows)
    {
		rows = "";
        foreach (var item in dataRow.ItemArray)
        {
			rows = rows + " # " + item;
        }
        Console.WriteLine(" " + rows);
    }
}

CREATOR

Follow me for my next creations

Product Versions
.NET Framework net472 net48
Compatible target framework(s)
Additional computed target framework(s)
Learn more about Target Frameworks and .NET Standard.

NuGet packages

This package is not used by any NuGet packages.

GitHub repositories

This package is not used by any popular GitHub repositories.

Version Downloads Last updated
2.1.0 58 6/2/2022
2.0.0 256 9/16/2021
1.0.3.11 185 8/10/2021
1.0.3.10 367 4/21/2020
1.0.3.9 353 4/18/2020
1.0.3.8 372 4/8/2020
1.0.3 453 4/7/2020
1.0.0 506 3/22/2020

[pt-BR]
     - Descontinuado CPSysSQLFramework1.
     - Adicionado suporte para Firebird e SQLite em CPSysSQLFramework2.
     - Construtor de ConnectionData foi removido e adicionado métodos estáticos CreateConnectionMYSQL, CreateConnectionSQLSRV, CreateConnectionFIREBIRD e CreateConnectionSQLITE com os respectivos parâmetros
     - Melhorias na criação das tabelas para o MYSQL adicionado as FK ao final da tabela
     - Correções para não adicionar campos tipo PRIMARY_KEY_IDENTITY em inserts
     - Correção no update onde que não estava pegando corretamente o tipo dos campos.
     [en-US]
     - Descontinuado CPSysSQLFramework1.
     - Adicionado suporte para Firebird e SQLite em CPSysSQLFramework2.
     - Construtor de ConnectionData foi removido e adicionado métodos estáticos CreateConnectionSQLSRV, CreateConnectionFIREBIRD e CreateConnectionSQLITE com os respectivos parâmetros
     - Melhorias na criação das tabelas para o MYSQL adicionado como FK ao final da tabela
     - Correções para não adicionar campos tipo PRIMARY_KEY_IDENTITY em inserts
     - Correção no update onde que não estava pegando corretamente o tipo dos campos.