- Details
- Written by: Stanko Milosev
- Category: C#
- Hits: 67
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] GOand 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#
- Hits: 275
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> listOfTasksToExecuteInReadAllAsyncThen 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#
- Hits: 179
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
- Details
- Written by: Stanko Milosev
- Category: C#
- Hits: 321
_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);
}