Details

Written by: Stanko Milosev

Category: C#

Published: 02 May 2025

Last Updated: 02 May 2025

Hits: 222

I have table like this:

CREATE TABLE [dbo].[BigFiles](
	[ID] [uniqueidentifier] NOT NULL,
	[Name] [nvarchar](255) NOT NULL,
	[Size] [bigint] NOT NULL,
	[Data] [image] NULL,
 CONSTRAINT [PK_File] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

and I inserted some file approx big 700mb:

INSERT INTO [BigFiles] (ID, Name, Size, Data)
SELECT
    NEWID(),
    'bigFile.zip',
    DATALENGTH(BulkColumn),
    BulkColumn
FROM OPENROWSET(BULK N'bigFile.zip', SINGLE_BLOB) AS FileData;

Then I have created 32bit project in Visual Studio, where my csproj looks like this:

<Project Sdk="Microsoft.NET.Sdk">

	<PropertyGroup>
		<OutputType>Exe</OutputType>
		<TargetFramework>net9.0</TargetFramework>
		<ImplicitUsings>enable</ImplicitUsings>
		<Nullable>enable</Nullable>
		<PlatformTarget>x86</PlatformTarget>
		<Prefer32Bit>true</Prefer32Bit>
	</PropertyGroup>

	<ItemGroup>
		<PackageReference Include="Microsoft.Data.SqlClient" Version="6.0.2" />
	</ItemGroup>

</Project>

Notice:

<PlatformTarget>x86</PlatformTarget>
<Prefer32Bit>true</Prefer32Bit>

Standard way with Ado.Net will not work:

using Microsoft.Data.SqlClient;

string connectionString = "Server=localhost;Database=saveBigData;User Id=myUserId;Password=myPass;TrustServerCertificate=True;Encrypt=False;";
using SqlConnection sqlConnection = new SqlConnection();
using SqlCommand sqlCommand = new SqlCommand();
sqlConnection.ConnectionString = connectionString;
sqlConnection.Open();
sqlCommand.Connection = sqlConnection;
sqlCommand.CommandText = "SELECT * FROM [dbo].[BigFiles]";
sqlCommand.CommandTimeout = 0;
using SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();

while (sqlDataReader.Read())
{
    object[] values = new object[4];
    sqlDataReader.GetValues(values);
}

In the line:

sqlDataReader.GetValues(values);

will be System.OutOfMemoryException raised. Instead the one should use SequentialAccess:

string fileName = Path.Combine(AppContext.BaseDirectory, $"{Guid.NewGuid()}.bin");

using SqlConnection sqlConnection = new SqlConnection();
using SqlCommand sqlCommand = new SqlCommand();
sqlConnection.ConnectionString = connectionString;
sqlConnection.Open();
sqlCommand.Connection = sqlConnection;
sqlCommand.CommandText = "SELECT * FROM [dbo].[BigFiles]";
sqlCommand.CommandTimeout = 0;
using SqlDataReader reader = sqlCommand.ExecuteReader(CommandBehavior.SequentialAccess);
while (reader.Read())
{
	using Stream sqlStream = reader.GetStream(3);

	using FileStream fileStream = new FileStream(fileName, FileMode.Create, FileAccess.Write);
	byte[] buffer = new byte[81920];
	int bytesRead;
	while ((bytesRead = sqlStream.Read(buffer, 0, buffer.Length)) > 0)
	{
		fileStream.Write(buffer, 0, bytesRead);
	}
}

Example download from here.

Details

Written by: Stanko Milosev

Category: C#

Published: 18 March 2025

Last Updated: 18 March 2025

Hits: 490

In the consumer-producer pattern, I want to have a single consumer under which I can define multiple optional tasks. I will store these tasks in a dictionary and execute them later within the consumer. To implement this example I will use the architecture from Steven van Deursen and channels.

In short, my consumer looks like this:

public class ReadAllHandlerAsync(
    Dictionary<IListOfTasksToExecuteInReader, IListOfTasksToExecuteInReaderCommand> listOfTasksToExecuteInReadAllAsync)
    : IListOfTasksToExecute
{
    public async Task Execute(IListOfTasksToExecuteCommand command)
    {
        var reader = command.MyChannelReader;
        if (reader is not null)
        {
            await foreach (var latLngFileName in reader.ReadAllAsync())
            {
                foreach (var taskToExecuteCommand in listOfTasksToExecuteInReadAllAsync)
                {
                    try
                    {
                        taskToExecuteCommand.Value.LatLngFileName = latLngFileName;
                        await taskToExecuteCommand.Key.Execute(taskToExecuteCommand.Value);
                    }
                    catch (Exception ex)
                    {
                        ((ReadAllAsyncHandlerCommand)command).Exceptions.Enqueue(ex);
                    }
                }
            }
        }
    }
}

Notice method signature:

Dictionary<IListOfTasksToExecuteInReader, IListOfTasksToExecuteInReaderCommand> listOfTasksToExecuteInReadAllAsync

Then I have created wrapper around my Parallel.ForEachAsync, since therotically speaking I could have also more consumers, I will have also list of taks in my wrapper which I can execute before starting Parallel.ForEachAsync, and where I will hand over the channel:

public class MyParallelForEachAsyncWrapper(MyParallelForEachAsync myParallelForEachAsync
    , Dictionary<IListOfTasksToExecute, IListOfTasksToExecuteCommand> listOfTasksToExecuteBeforeStartForEach) : ICommandHandlerAsync<MyParallelForEachAsyncWrapperCommand>
{
    public async Task Execute(MyParallelForEachAsyncWrapperCommand command)
    {
        var tasksToExecuteBeforeStartForEach = new List<Task>();
        try
        {
            foreach (var taskToExecuteBeforeStartForEach in listOfTasksToExecuteBeforeStartForEach)
            {
                if (command.MyInfoChannel != null)
                {
                    taskToExecuteBeforeStartForEach.Value.MyInfoChannelReader = command.MyInfoChannel;
                }
 
                tasksToExecuteBeforeStartForEach.Add(
                    taskToExecuteBeforeStartForEach.Key.Execute(taskToExecuteBeforeStartForEach.Value));
            }
 
            var myParallelForEachAsyncCommand = new MyParallelForEachAsyncCommand
            {
                FolderName = command.FolderName
                , MyChannel = command.MyChannel
            };
            await myParallelForEachAsync.Execute(myParallelForEachAsyncCommand);
        }
        catch (Exception e)
        {
            command.Exceptions.Enqueue(e);
        }
        finally
        {
            await Task.WhenAll(tasksToExecuteBeforeStartForEach);
        }
    }
}

Notice how I am handing over the channel:

if (command.MyInfoChannel != null)
{
	taskToExecuteBeforeStartForEach.Value.MyInfoChannelReader = command.MyInfoChannel;
}

and

var myParallelForEachAsyncCommand = new MyParallelForEachAsyncCommand
{
	FolderName = command.FolderName
	, MyChannel = command.MyChannel
};
await myParallelForEachAsync.Execute(myParallelForEachAsyncCommand);

Thats why my interface looks like:

public interface IListOfTasksToExecuteCommand
{
    ChannelReader<LatLngFileNameModel>? GpsInfoChannelReader { get; set; }
}

At the end, my Parallel.ForEachAsync method will look like this:

public class MyParallelForEachAsync : ICommandHandlerAsync<MyParallelForEachAsyncCommand>
{
    private readonly ConcurrentQueue<Exception> _exceptions = new();

    public async Task Execute(MyParallelForEachAsyncCommand command)
    {
        if (Directory.Exists(command.FolderName))
        {
            var imageExtensions = new HashSet<string>(StringComparer.OrdinalIgnoreCase)
            {
                ".jpg", ".jpeg", ".png", ".gif", ".bmp", ".tiff", ".webp"
            };

            await Parallel.ForEachAsync(
                EnumerateFilesSafe(command.FolderName), async (imageFileName, ct) =>
                {
                    if (imageExtensions.Contains(Path.GetExtension(imageFileName).ToLower()))
                    {
                        var extractGpsInfoFromImageCommand = new ExtractGpsInfoFromImageCommand
                        {
                            ImageFileNameToReadGpsFrom = imageFileName
                        };

                        if (command.GpsInfoChannel != null)
                            await command.GpsInfoChannel.Writer.WriteAsync(
                                ExtractGpsInfoFromImage(extractGpsInfoFromImageCommand), ct);
                    }
                });

            command.GpsInfoChannel?.Writer.Complete();

            if (!_exceptions.IsEmpty)
            {
                throw new AggregateException("Error in der Parallel.ForEachAsync", _exceptions);
            }
        }
        else
        {
            throw new DirectoryNotFoundException($"Directory {command.FolderName} not found.");
        }
    }
}

Details

Written by: Stanko Milosev

Category: C#

Published: 15 March 2025

Last Updated: 15 March 2025

Hits: 305

Here is a small example of how to display the type and size of fields from a schema table. This example works for MS SQL; I haven't tested it on other databases.

using System;
using System.Data.SqlClient;
using System.Windows.Forms;

namespace GetDbDataTypes
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void btnStart_Click(object sender, EventArgs e)
        {
            string connectionString = "Server=myServer;Database=myDb;Integrated Security=True";
            string query = "SELECT top 1 * FROM [myTable]";
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();

                using (SqlCommand command = new SqlCommand(query, connection))
                {
                    command.CommandTimeout = 3600;
                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        dataGridView1.DataSource = reader.GetSchemaTable();
                    }
                }
            }
        }
    }
}

Example download from here