Snapper.All 3.2.0

<h1><b>Snapper</b></h1> Snapper is a package for easy database connection to execute DML commands and parameterized procedures for Oracle, SQL Server and MySQL.

<h2 align="left">🚀 MySQL sample use</h2>

<h3>Follow these steps<h3></h3>

<ol dir="auto"> <li> <p dir="auto">Create a Snapper.</p> </li> </ol>

using Snapper.Core;

var dataBase = new Snap("Develop", "localhost", "root", "R@bustec0", new MySqlEngine());

Where: <ul dir="auto"> <li><code style="color : red">"Develop"</code> is the database name for desired connection.</li> <li><code style="color : red">"localhost"</code> is the name of the server you woul'd like to connect.</li> <li><code style="color : red">"root"</code> is the database user name to connect to.</li> <li><code style="color : red">"R@bustec0"</code> is the user password.</li> <li><code style="color : red">"new MySqlEngine()"</code> is the engine for the connection. In this case is MySQL.</li> </ul>

So...

//Cosiderating the existence of a entity called "test"
var test = new Test
{
    Id = i,
    Amount = Convert.ToDecimal("234.90") + i,
    Name = $"Blaster - {i}",
    Creation = Convert.ToDateTime("2023-07-12 14:00:00")
};

<ol start="2" dir="auto"> <li>Fill up the database "connection" with necessary information. You can use two options:</li> <ul> <li>SQL Plain Text</li> <li>Stored Procedure</li> </ul> </ol>

<h3><code style="color : red">Using SQL Plain Text</code></h3>

//Write a plain SQL text command to fire it
var sql = $"INSERT INTO Test (id, name, amount, creation) VALUES ({i}, '{test.Name}', {test.Amount}, '{test.Creation:yyyy-MM-dd HH:mm:ss}');";
//Instead, if you don't have any entity where catch the data, you can do as follow
var sql = "INSERT INTO Test (id, name, amount, creation) VALUES (30, 'Blaster', 134.98, '2023-07-12 14:00:00');";
//For Oracle treating string to timestamp. Deal in the way you need.
var sql = "INSERT INTO Test (id, name, amount, creation) VALUES (31, 'Blaster', 134.98, TO_TIMESTAMP('2023-07-12 14:00:00','YYYY-MM-DD HH24:MI:SS'))";

dataBase.CreateCommand(
    sql,
    CommandType.Text,
    null
);

Where: <ul dir="auto"> <li><code style="color : red">"sql"</code> is the the sql plain text you've just written.</li> <li><code style="color : red">"CommandType.Text"</code> is the type of the command. In this case as like plain text, then the type is "Text".</li> <li><code style="color : red">"null"</code> is for the parameter list that you aren't using in this command. You can pass null or omit it if you don't have any.</li>

dataBase.CreateCommand(
    sql,
    CommandType.Text
);

</ul>

<h3><code style="color : red">Using Stored Procedure</code></h3> <p>The only two things you need to do differently from the up example are: change <code style="color : red">"sql"</code> by the name of your procedure and the type of the command from <code style="color : red">"ComandType.Text"</code> to <code style="color : red">"CommanType.StoredProcedure"</code>. In this case, you should create a list of Parameter declared in the stored procedure and fill up the property of each parameter.</p>

$${\color{red}The \space name \space of \space the \space parameter \space should \space be \space exactly \space the \space same \space of \space the \space procedure!}$$

dataBase.CreateCommand(
   "CREATE_TEST",
   CommandType.StoredProcedure,
   new List<Parameter>
   {
        new Parameter
        {
            Name = "p_Id",
            Value = i,
            ParameterDirection = ParameterDirection.Input,
            ParameterDbType = ParameterDbType.Int32
        },
        new Parameter
        {
            Name = "p_name",
            Value = $"Nome - {i:000}",
            ParameterDirection = ParameterDirection.Input,
            ParameterDbType = ParameterDbType.VarChar
        },
        new Parameter
        {
            Name = "p_amount",
            Value = 100 + (i*3),
            ParameterDirection = ParameterDirection.Input,
            ParameterDbType = ParameterDbType.Decimal
        },
        new Parameter
        {
            Name = "p_creation",
            Value = Convert.ToDateTime("2023-07-12 14:00:00").AddMinutes(i),
            ParameterDirection = ParameterDirection.Input,
            ParameterDbType = ParameterDbType.DateTime
        }
    }
);

Where: <ul dir="auto"> <li><code style="color : red">"CREATE_TEST"</code> is the name for the procedure you want to fire. You can use a package too! For this, declare the package's name where you procedure is before the procedure name like "PACKAGE_NAME.CREATE_TEST".</li> <li><code style="color : red">"CommandType.StoredProcedure"</code> is the type of the command. In this case, is an stored procedure, isn't?</li> <li><code style="color : red">List of the parametes declared in the procedure</code>. Pay attention in the <code style="color : red">"ParameterDbType"</code> witch should fit the same as the database engine: MySQL, Oracle, MSQLServe, etc.</li> </ul>

After all, execute the command:

dataBase.ExecuteCommand();
//or
dataBase.ExecuteCommandAsync();

<h2 align="left">🚀 Oracle sample use</h2> <p>Just change the way how Snapper is created changing the Engine type.</p>

using Snapper.Core;

var dataBase = new Snap("XE", "localhost", "system", "R@bustec0", new OracleEngine());

Where: <ul dir="auto"> <li><code style="color : red">"XE"</code> is the database name for desired connection.</li> <li><code style="color : red">"localhost"</code> is the name of the server you woul'd like to connect.</li> <li><code style="color : red">"system"</code> is the database user name to connect to.</li> <li><code style="color : red">"R@bustec0"</code> is the user password.</li> <li><code style="color : red">"new OracleEngine()"</code> is the engine for the connection. In this case is Oracle.</li> </ul>

<h2 align="left">🚀 MSQL Server sample use</h2> <p>Just change the way how Snapper is created. For MSQL Server. In addition to exchanging the Engine type you also should inform the "InstaceName"</p>

using Snapper.Core;

var dataBase = new Snap("Develop", "THINKPAD", "breula", "R@bustec0", new MsqlServerEngine { InstanceName = "SQLEXPRESS" });

Where: <ul dir="auto"> <li><code style="color : red">"Develop"</code> is the database name for desired connection.</li> <li><code style="color : red">"THINKPAD"</code> is the name of the server you woul'd like to connect.</li> <li><code style="color : red">"breula"</code> is the database user name to connect to.</li> <li><code style="color : red">"R@bustec0"</code> is the user password.</li> <li><code style="color : red">"new MsqlServerEngine { InstanceName = "SQLEXPRESS" }"</code> is the engine for the connection. In this case is MSQL Server and "SQLEXPRES" is the database instace name.</li> </ul>

<h2 align="left">⚙️ Engine types detail</h2> <h3><code style="color : red">MySqlEngine</code></h3> <p>When you create an engine of this type, this is how is the default creation. You can change these values as you need.</p>

var engine = new MySqlEngine
{
    Port = 3307,
    Compress = true,
    ConnectionTimeOut = 30,
    SslMode = "none",
    CharSet = "utf8"
};

<h3><code style="color : red">OracleEngine</code></h3> <p>When you create an engine of this type, this is how is the default creation. You can change these values as you need.</p>

var engine = new OracleEngine
{
    Port = 1521
};

<h3><code style="color : red">OracleEngine</code></h3> <p>When you create an engine of this type you need to inform the database instace name.</p>

var engine = new MsqlServerEngine
{
    InstanceName = "YOU_NEED_TO_INFORM"
};

<h2>⚙️ Executing a command</h2> <p>When you fire one of these commands you post the command to the database engine so that database to execute it. This command is for execute a procedure, insert, update or delete data. All is void and doesn't returns anything</p> <h3><code style="color : red">ExecuteCommand()</code></h3> Execute the commnad in syncronous way. <h3><code style="color : red">ExecuteCommandAsync()</code></h3> Execute the commnad in asyncronous way. <h3><code style="color : red">ExecuteReturn()</code></h3> This method is specific for Oracle engine. And it is used in case when you need to retrive some data from procedure execution as like Out parameter, as example follow:

CREATE OR REPLACE NONEDITIONABLE PROCEDURE GET_SALE
(
  P_ID                        test.id%type,
  O_Result                    OUT SYS_REFCURSOR
)
IS
BEGIN
    OPEN O_Result FOR SELECT * FROM sales s WHERE s.id = P_ID;
END GET_SALE;

Where you need to retrieve the "O_Result" data as soon the procedure is executed. You can get any type of OUT return like string, boolean, etc. You neet to treat the returns as you need.

<h2>⚙️ Retrieving data from the database.</h2> <h3><code style="color : red">GetDataAsync<T>()</code></h3> <p>First! No matters which database you are using, because for all it is made by the same way.</p> <p>Last! You need to use a generic type that represents the real type of entity you are using, example:</p>

dataBase.CreateCommand(
   "select Id, Name, Amount from test",
   CommandType.Text
);
var entities = await dataBase.GetDataAsync<List<object>>();
foreach (var entity in entities)
{
   Test item = JsonConvert.DeserializeObject<Test>(entity.ToString());
   Console.WriteLine($"{item.Id}, {item.Name}, {item.Amount}");
}

In the up example you got an list of "Test" entity and now you can do what you want to to with them. Our "Test" class is like:

    public class Test
    {
        /// <summary>
        /// Id
        /// </summary>
        [JsonPropertyName("id")]
        public int Id { get; set; }

        /// <summary>
        /// Name
        /// </summary>
        [JsonPropertyName("Name")]
        public string Name { get; set; }

        /// <summary>
        /// Amount
        /// </summary>
        [JsonPropertyName("Amount")]
        public decimal Amount { get; set; }

        /// <summary>
        /// Data
        /// </summary>
        [JsonPropertyName("Creation")]
        public DateTime Creation { get; set; }
    }

<br> <br> <br> <h2 align="left">:exclamation:<code style="color : red">Troubles?</code></h2> <p>When using <code style="color : red">MSQL Server</code> for example, if you face a message like this one it is probably because the procedure already is treating transaction using commit</p> <p><code style="color : red">System.Data.SqlClient.SqlException: 'Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 1, current count = 0.'</code></p>

USE Develop
GO
CREATE OR ALTER PROCEDURE dbo.Create_Test @p_id int, @p_name varchar(50) = NULL, @p_amount decimal(12,2) = 0, @p_creation datetime = NULL
AS
insert into Test (Id, Name, Amount, Creation) values(@p_id, @p_name, @p_amount, @p_creation);
commit;
GO

<p>In this case you should delete the <code style="color : red">"commit;"</code> command from the procedute. That is becouse the Snapper already deal with database transactions.</p> <p>Let your procedure code like this:</p>

USE Develop
GO
CREATE OR ALTER PROCEDURE dbo.Create_Test @p_id int, @p_name varchar(50) = NULL, @p_amount decimal(12,2) = 0, @p_creation datetime = NULL
AS
insert into Test (Id, Name, Amount, Creation) values(@p_id, @p_name, @p_amount, @p_creation);
GO