Decimal Standards for Blockchain Transactions

Recommended Data Types

For SQL Server, use DECIMAL(18,8) (18 total digits, 8 after the decimal point) to handle most cryptocurrency needs. In C#, use the decimal type, which supports 28-29 significant digits and matches SQL Server’s DECIMAL for precision. For Android/Java, use BigDecimal to ensure exact precision, formatting to 8 decimal places before signing transactions. Python and JavaScript also require careful handling to maintain precision, often using specialized libraries or string representations.

These choices ensure exact numeric representation, sufficient scale, and safe data transfer between database and application, avoiding floating-point issues.

SQL Server: Table Definition

-- SQL Server table
CREATE TABLE Transactions (
    TransactionId BIGINT PRIMARY KEY,
    Amount DECIMAL(18,8) NOT NULL,
    TransactionDate DATETIME DEFAULT GETDATE(),
    WalletAddress VARCHAR(100),
    CONSTRAINT CHK_Amount_Positive CHECK (Amount >= 0)
);

-- Insert sample data
INSERT INTO Transactions (TransactionId, Amount, WalletAddress)
VALUES 
    (1001, 123.45678912, '0x1234...'),
    (1002, 0.00050000, '0x5678...'),
    (1003, 5000.25000000, '0x9abc...');

                

C#: Parameterized Queries

This example shows a complete C# implementation for managing cryptocurrency transactions with parameterized queries to maintain precision.

using System;
using System.Data.SqlClient;

public class TransactionRepository
{
    private readonly string connectionString = "your_connection_string_here";

    public void CreateTransaction(long transactionId, decimal amount, string walletAddress)
    {
        string query = @"
            INSERT INTO Transactions (TransactionId, Amount, WalletAddress)
            VALUES (@TransactionId, @Amount, @WalletAddress)";

        using (SqlConnection conn = new SqlConnection(connectionString))
        using (SqlCommand cmd = new SqlCommand(query, conn))
        {
            cmd.Parameters.Add("@TransactionId", SqlDbType.BigInt).Value = transactionId;
            cmd.Parameters.Add("@Amount", SqlDbType.Decimal).Value = amount;
            cmd.Parameters["@Amount"].Precision = 18;
            cmd.Parameters["@Amount"].Scale = 8;
            cmd.Parameters.Add("@WalletAddress", SqlDbType.VarChar, 100).Value = walletAddress;

            conn.Open();
            cmd.ExecuteNonQuery();
        }
    }

    public decimal GetTransactionAmount(long transactionId)
    {
        string query = "SELECT Amount FROM Transactions WHERE TransactionId = @TransactionId";

        using (SqlConnection conn = new SqlConnection(connectionString))
        using (SqlCommand cmd = new SqlCommand(query, conn))
        {
            cmd.Parameters.Add("@TransactionId", SqlDbType.BigInt).Value = transactionId;

            conn.Open();
            object result = cmd.ExecuteScalar();
            return result != null ? Convert.ToDecimal(result) : 0m;
        }
    }

    public void UpdateTransactionAmount(long transactionId, decimal newAmount)
    {
        string query = "UPDATE Transactions SET Amount = @Amount WHERE TransactionId = @TransactionId";

        using (SqlConnection conn = new SqlConnection(connectionString))
        using (SqlCommand cmd = new SqlCommand(query, conn))
        {
            cmd.Parameters.Add("@Amount", SqlDbType.Decimal).Value = newAmount;
            cmd.Parameters["@Amount"].Precision = 18;
            cmd.Parameters["@Amount"].Scale = 8;
            cmd.Parameters.Add("@TransactionId", SqlDbType.BigInt).Value = transactionId;

            conn.Open();
            cmd.ExecuteNonQuery();
        }
    }

    public decimal GetWalletBalance(string walletAddress)
    {
        string query = "SELECT SUM(Amount) FROM Transactions WHERE WalletAddress = @WalletAddress";

        using (SqlConnection conn = new SqlConnection(connectionString))
        using (SqlCommand cmd = new SqlCommand(query, conn))
        {
            cmd.Parameters.Add("@WalletAddress", SqlDbType.VarChar, 100).Value = walletAddress;

            conn.Open();
            object result = cmd.ExecuteScalar();
            return result != null ? Convert.ToDecimal(result) : 0m;
        }
    }
}

// Usage example
public class Program
{
    static void Main()
    {
        var repo = new TransactionRepository();

        // Create a transaction
        repo.CreateTransaction(1001, 123.45678912m, "0x1234...");

        // Get amount
        decimal amount = repo.GetTransactionAmount(1001);
        Console.WriteLine($"Amount: {amount}");

        // Update amount
        repo.UpdateTransactionAmount(1001, 150.75000000m);

        // Get wallet balance
        decimal balance = repo.GetWalletBalance("0x1234...");
        Console.WriteLine($"Balance: {balance}");
    }
}

                

Android/Java: Formatting Transaction Amount

This example demonstrates the best approach for formatting transaction amounts in Android/Java using BigDecimal to ensure exact precision before signing.

import java.math.BigDecimal;
import java.math.RoundingMode;

public class TransactionFormatter {
    public static String formatTransactionAmount(String str_amount) {
        try {
            // Use BigDecimal for exact precision
            BigDecimal amount = new BigDecimal(str_amount);
            // Set scale to 8 decimal places, round if necessary
            amount = amount.setScale(8, RoundingMode.HALF_UP);
            // Convert to string without scientific notation
            return amount.toPlainString();
        } catch (NumberFormatException e) {
            throw new IllegalArgumentException("Invalid amount format: " + str_amount, e);
        }
    }

    public static void main(String[] args) {
        String str_amount = "123.45678912345";
        String formatted = formatTransactionAmount(str_amount);
        System.out.println("Formatted Amount: " + formatted);
    }
}

                

Python: Formatting Transaction Amount

This example demonstrates formatting transaction amounts in Python using the decimal module to ensure exact precision for blockchain transactions.

from decimal import Decimal, ROUND_HALF_UP

def format_transaction_amount(str_amount):
    try:
        # Use Decimal for exact precision
        amount = Decimal(str_amount)
        # Set scale to 8 decimal places, round if necessary
        amount = amount.quantize(Decimal('0.00000001'), rounding=ROUND_HALF_UP)
        # Convert to string without scientific notation
        return str(amount)
    except ValueError:
        raise ValueError("Invalid amount format: " + str_amount)

if __name__ == "__main__":
    str_amount = "123.45678912345"
    formatted = format_transaction_amount(str_amount)
    print("Formatted Amount:", formatted)

                

Reference: Python decimal Module Documentation

JavaScript: Formatting Transaction Amount

This example demonstrates formatting transaction amounts in JavaScript using the big.js library to ensure exact precision for blockchain transactions, as JavaScript’s native numbers are floating-point and prone to precision errors.

const Big = require('big.js');

function formatTransactionAmount(str_amount) {
    try {
        // Use Big.js for exact precision
        const amount = new Big(str_amount);
        // Set scale to 8 decimal places, round if necessary
        return amount.toFixed(8);
    } catch (e) {
        throw new Error("Invalid amount format: " + str_amount);
    }
}

// Usage example
const str_amount = "123.45678912345";
const formatted = formatTransactionAmount(str_amount);
console.log("Formatted Amount:", formatted);

                

Reference: big.js Library Documentation

Data Type Comparison