Snapper.All 3.2.0

dotnet add package Snapper.All --version 3.2.0
NuGet\Install-Package Snapper.All -Version 3.2.0
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="Snapper.All" Version="3.2.0" />
For projects that support PackageReference, copy this XML node into the project file to reference the package.
paket add Snapper.All --version 3.2.0
#r "nuget: Snapper.All, 3.2.0"
#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 Snapper.All as a Cake Addin
#addin nuget:?package=Snapper.All&version=3.2.0

// Install Snapper.All as a Cake Tool
#tool nuget:?package=Snapper.All&version=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
Product Compatible and additional computed target framework versions.
.NET net5.0 was computed.  net5.0-windows was computed.  net6.0 was computed.  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 was computed.  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 was computed.  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. 
.NET Core netcoreapp3.1 is compatible. 
Compatible target framework(s)
Included target framework(s) (in package)
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
3.2.0 107 9/23/2023

Criar arquivos .cs direto das tabelas do banco de dados