DevToys.PocoDB.Core 1.0.6

#Configuration

    DataConfiguration.Instance.Add(
        new ConnectionConfig()
        {
            Key = "OneOfMyConnections",
            ConnectionTypeName = "SqlClient", // Available types: SqlClient, OleDb, Odbc ( can be extended for other types)
            ConnectionString = @"Server=LAPTOP-GUIDO\SQLEXPRESS;Database=Misc;Trusted_Connection=True;"
        }
    );
    

#Samples

###Sql Server Table

    Create table dbo.Company
    (
        Id int not null identity(1,1) primary key,
        [Name] varchar(128) not null,
        ZipCode varchar(16) not null,
    )
    go

###Poco Result object

    public class Company
    {
        [DBField("Id")]
        public int Id { get; set; }

        [DBField("Name")]
        public string Name { get; set; }

        [DBField("ZipCode")]
        public string ZipCode { get; set; }
    }

###Poco Command Objects

    [DBCommand(@"select id, [name], ZipCode from dbo.Company", commandtype: CommandType.Text)]
    public class GetCompanyAll
    {
    }        

    
    [DBCommand(@"select id, [name], ZipCode from dbo.Company where id = @id", commandtype: CommandType.Text)]
    public class GetCompanyById
    {
        [DBParameter("Id")]
        public int Id { get; set; }
    }        
        
    
    [DBCommand(@"insert into dbo.Company ([name], ZipCode) 
                    values (@name, @ZipCode);
                    set @OutputId = @@IDENTITY", commandtype: CommandType.Text)]
    public class InsertCompany
    {
        [DBParameter("OutputId", Direction = ParameterDirection.Output)]
        public int Id { get; set; }

        [DBParameter("name")]
        public string Name { get; set; }

        [DBParameter("ZipCode")]
        public string ZipCode { get; set; }

    }
     

###Executing and retrieving Data

    var insert = new InsertCompany()
    {
        Name = "First Company",
        ZipCode = "ABCD12"
    };
    var operationInsert = new DbCommandOperation<InsertCompany>("OneOfMyConnections");
    operationInsert.ExecuteNonQuery(insert);


    var operationGetAll = new DbCommandOperation<Company, GetCompanyAll>("OneOfMyConnections");
    List<Company> result1 = operationGetAll.ExecuteReader(new GetCompanyAll() { }).ToList();

    // Short hand (no parameters for GetCompanyAll
    List<Company> result2 = operationGetAll.ExecuteReader().ToList();


    int _newId = insert.Id;
    var operationSingle = new DbCommandOperation<Company, GetCompanyById>("OneOfMyConnections");
    Company result2 = operationSingle.ExecuteSingleReader(new GetCompanyById() { Id = _newId });

           

#Inject Third party DataProviders ( typeof : DbConnection )

    ConnectionFactory.Instance.AddType<FbConnection>("FireBirdClient");

    DataConfiguration.Instance.Add(
        new ConnectionConfig()
        {
        Key = "MyFireBirdConnection",
            ConnectionTypeName = "FireBirdClient",
            ConnectionString = @"datasource=192.168.178.11;database=EMPLOYEE.FDB;username=SYSDBA;password=blabla;"
        }
    );

#Bulk Insert (SqlClient Only)

    [DBBulkInsert("dbo.Company")]
    public class BulkCompany
    {
        [DBParameter("Id")]
        public int Id { get; set; } = 0;

        [DBParameter("Name")]
        public string Name { get; set; } = string.Empty;

        [DBParameter("ZipCode")]
        public string ZipCode { get; set; } = string.Empty;

    }

    List<BulkCompany> _data = new List<BulkCompany>();

    for (int ii = 0; ii < 10000; ii++)
        _data.Add(new BulkCompany() { Name = "MyCompany", ZipCode = "1234AB" });

    BulkInsertOperation<BulkCompany> operation = new BulkInsertOperation<BulkCompany>("OneOfMyConnections", 2096);
    // operation.Progress += Operation_Progress;

    operation.Insert(_data);


    

#Working with Transactions

   // Delete all companies outside Transaction.
    var deleteoperation = new DbCommandOperation<DeleteAllCompanies>("OneOfMyConnections");
    deleteoperation.ExecuteNonQuery();

    var operation1 = new DbCommandOperation<InsertCompanyByProcedure>("OneOfMyConnections");
    var operation2 = new DbCommandOperation<InsertCompanyBySqlStatement>("OneOfMyConnections");

    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()
                {
                    Name = "A Company Name",
                    ZipCode = "4555AA"
                };

                operation1.ExecuteNonQuery(connection, transaction, insert1);

                int newId = insert1.Id;

                // Too large ZipCode
                InsertCompanyBySqlStatement insert2 = new InsertCompanyBySqlStatement()
                {
                    Name = "A Company Name ",
                    ZipCode = "4555AA TO LONG AAAAAAAAAAAbbbbbbbbbbbAAAAAAAAA TO LONG"
                };

                operation2.ExecuteNonQuery(connection, transaction, insert2);

                int newId2 = insert2.Id;

                transaction.Commit();
            }
            catch (Exception exception)
            {
                transaction.Rollback();
            }
        }
        connection.Close();
    }
    
    

#Bulk Random Data ( SqlClient Only )

    [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("HouseNumber", RandomStringType = RandomStringType.Number, Min = 10, Max = 200)]
        public string HouseNumber { get; set; } = string.Empty;
    }
    
    BulkInsertOperation<BulkCompanyRandom> operation = new BulkInsertOperation<BulkCompanyRandom>("Local", 512);
    operation.Insert(1000);        
        
   

#Insert Random Data

   [DBCommand(@"insert into dbo.Company ([name]) 
                    values (@name);
                 set @OutputId = @@IDENTITY ", commandtype: CommandType.Text)]
    public class InsertCompanyRandom
    {
        [DBParameter("OutputId", Direction = ParameterDirection.Output)]
        public int Id { get; set; }

        [DBRandomParameter("name")]
        public string Name { 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;
    }