Micro blog about Answer to the Ultimate Question of Life, the Universe, and Everything.
  • Home
    • List all categories
    • Sitemap
  • Downloads
    • WebSphere
    • Hitachi902
    • Hospital
    • Kryptonite
    • OCR
    • APK
  • About me
    • Gallery
      • Italy2022
      • Côte d'Azur 2024
    • Curriculum vitae
      • Resume
      • Lebenslauf
    • Social networks
      • Facebook
      • Twitter
      • LinkedIn
      • Xing
      • GitHub
      • Google Maps
      • Sports tracker
    • Adventures planning
  1. You are here:  
  2. Home

How to Load a Large File from MS SQL

Details
Written by: Stanko Milosev
Category: C#
Published: 02 May 2025
Last Updated: 02 May 2025
Hits: 67
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.

One consumer, multiple tasks

Details
Written by: Stanko Milosev
Category: C#
Published: 18 March 2025
Last Updated: 18 March 2025
Hits: 275
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.");
        }
    }
}

Get types and size from database

Details
Written by: Stanko Milosev
Category: C#
Published: 15 March 2025
Last Updated: 15 March 2025
Hits: 179
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

Task.Run cannot be canceled immediately

Details
Written by: Stanko Milosev
Category: C#
Published: 08 March 2025
Last Updated: 08 March 2025
Hits: 321
Example:
_cts = new System.Threading.CancellationTokenSource();

int[] sleepConfiguration = [5, 7, 10, 1, 3];

List<Task> sleepingTasks = new List<Task>();
foreach (int sleepSeconds in sleepConfiguration)
{
	Task sleepingTask = Task.Run(() =>
	{
		DoSomethingLong(sleepSeconds);
	}, _cts.Token);

	sleepingTasks.Add(sleepingTask);
}

await Task.WhenAll(sleepingTasks);
MessageBox.Show("Done!");

private void DoSomethingLong(int sleepSeconds)
{
	Thread.Sleep(sleepSeconds * 1000);
}
  1. Add record in Db from Parallel.ForEachAsync and Ef Core
  2. Exceptions in Parallel.ForEach, Parallel.ForEachAsync, Task.Run and Task.Run.ContinueWith
  3. Update UI from Parallel.ForEach
  4. Copy CSV file to MS SQL with SqlBulkCopy and IDataReader

Subcategories

C#

Azure

ASP.NET

JavaScript

Software Development Philosophy

MS SQL

IBM WebSphere MQ

MySQL

Joomla

Delphi

PHP

Windows

Life

Lazarus

Downloads

Android

CSS

Chrome

HTML

Linux

Eclipse

Page 1 of 163

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10