Writing Ethereum Transactions to SQL Database Using Nethereum

Ethereum is an easy platform to create and run decentrialized applications on a blockchain network. When you run an Ethereum based application, you struggle with transactions which are main sources of blockchain based information.

Getting a transaction information from Ethereum is easy with Web3 and Nethereum. Web3 is a collection of libraries which allow you to interact with Ethereum network via HTTP or IPC. Nethereum is an open source .Net integration library for Ethereum. You can find all methods that you need for Ethereum.

But when it comes to get all transactions that belong to a custom address, it is not easy to deal with. First solution is using an Ethereum library and iterating over the all blocks and filtering the transactions that you need. But as you might guess it can be a long and complicated process. Secondly; you can find a proper API for getting old transactions for your needs. But these APIs can be paid services or can have limited access. The final solution is to store Ethereum data on SQL.

In this article I will try to explain how to get blocks and transaction details from blockchain and also get token transfer details from transaction logs and store them to SQL. You can find the project solutions from GitHub. This project stores three main information about network; Blocks, Transactions and Token Transfers. If you need more, you can improve this project or you can create issues via GitHub.

StorageProcessor is responsible for iterating via blocks and sending these informations to processors for processing needed informations. First gathered information is Block information. Project gets block details from network via Nethereum and writes them to NodeBlock table on SQL. This table contains informations like blockhash, blocknumber, blocktime etc. When you run the application over and over, it gets the last processed block’s number from table and starts from this block number.

var blocks = repositoryBase.GetRepository<NodeBlock>().GetAll();
 int startBlock = 5623328;
 if (blocks.Count() > 0)
   startBlock = blocks.Max(x => x.BlockNumber);
 var latestblock = await web3.Eth.Blocks.GetBlockNumber.SendRequestAsync();
 new Helpers().AddLog(LogType.Info, $"Last Block in DB : {startBlock} , Last Block in Network : {latestblock.Value}");
 var endBlock = (int)latestblock.Value;

 while (startBlock <= endBlock)
 {
   try
   {
     new Helpers().AddLog(LogType.Process, $"Block-{startBlock} Processing");
     var block = await blockProcessor.ProcessBlockAsync(startBlock);
     new Helpers().AddLog(LogType.Success, $"Block-{startBlock} Processed");

     new Helpers().AddLog(LogType.Info, $"Block-{startBlock} Transactions Processing, Tx Count : {block.TransactionHashes.Length} ..");
     await transactionsProcessor.ProcessTransactionAsync(block);
     new Helpers().AddLog(LogType.Success, $"Block-{startBlock} Transactions Processed");
   }
   catch (Exception e)
   {
	 }
   startBlock++;
 }

Block processor gets the block information from Storage processor and converts them to NodeBlock entity.

var _nodeBlock = new NodeBlock()
{
    BlockNumber = (int)block.Number.Value,
    BlockTime = new Helpers().UnixTimeStampToDateTime((double)block.Timestamp.Value),
    BlockHash = block.BlockHash,
    ParentHash = block.ParentHash,
    TransactionCount = block.TransactionHashes.Length,
    Nonce = block.Nonce,
};
blockRepository.Add(_nodeBlock);

After block processing, storage processor send block transactions to transaction processor and writes them to database.

var transaction = new NodeTransaction();
try
{
transaction = new NodeTransaction
  {
    NodeTokenTransfers = new List<NodeTokenTransfer>(),
    From = transactionSource.From,
    To = transactionSource.To,
    Status = (OpsICO.Core.Enums.TransactionStatus)((int)transactionReceipt.Status.Value),
    TxHash = transactionSource.TransactionHash,
    Value = new UnitConversion().FromWei(transactionSource.Value.Value)
};

To get token transfers from transaction details, it searches for “Transfer Event” in transaction logs. We should use signature of “transfer” event which uses a sha3 hashing algorithm.

"0xddf252ad1be2c89b69c2b068fc378daa952ba7f163c4a11628f55a4df523b3ef"

After getting token transfer log details, it converts log details to token transfer type and stores to database.

transaction.NodeTokenTransfers.Add(new NodeTokenTransfer()
{
  Amount = new Helpers().HextoString(_log.data),
  From = new AddressType().Decode<string>(_log.topics[1]),
  To = new AddressType().Decode<string>(_log.topics[2]),
  TokenContractAddress = transactionSource.To
});

After all these stuff, you can now easily filter and select all transaction data from your own blockchain storage. Waiting for your feedbacks and claps!

mrceylan/Nethereum.BlockchainStorage