c# – Vincy Boy!

Huge excel worksheet rowcount. Not all rows are read from OleDBDataAdapter.

I had a problem reading Microsoft Excel Worksheet from C#. My worksheet at the least containts 50,000 rows. I use OleDBDataAdapter class to read every rows. The problem is that only 4,000 rows are being read.

I did scour over the internet and a fix was suggested and it was simply adjusting connection string settings that is being passed to the OleDBConnection class. The fix was to assign [IMEX=1]. My previous settings was [IMEX=2]. Displayed below is the string property that I pass to the OleDBConnection class constructor.

protected virtual string ConnectionString
{
  get
  {
    return
      String.Format(
        "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=1\";", SourcePath);
  }
}

I hope this helps. Happy Coding!

.NET 3.5 PresentationCore Crash on Startup on Windows 11

Hi there,

If you are having a startup crash on your WPF application. With .NET 3.5 framework and on Windows 11. Also with this log on Event Viewer.

Fault bucket 2160294874680227364, type 5
Event Name: CLR20r3
Response: Not available
Cab Id: 0

Problem signature:
P1: itcapplauncher.exe // <– This is my app
P2: 1.0.0.0
P3: 8a01dbfb
P4: PresentationCore
P5: 3.0.0.0
P6: 6090bfa0
P7: 21db
P8: a
P9: System.NullReferenceException
P10:

My fix here is to move the .net framework higher than 3.5, I moved it to .NET framework 4. This is so I can still be compatible with windows XP.

Sorry my suggestion is a little unacceptible with other coders but I do not have a choice since the issue of crashing is outside my source code.

Happy Coding!

Microsoft.ApplicationBlocks. SqlHelper.ExecuteReader issue.

Yes, I had issues with the said title. This is specifically when a reader is declared under a SqlTransaction. In the end when the transaction also includes insert/delete and updates, the changes will not reflect.

Please look at this wrong code.

        public ReturnedCheckEntity SelectByID(int id)
        {
        
            if(WithTransaction)
            {
                using(SqlDataReader reader = SqlHelper.ExecuteReader(
                    Transaction,
                    _commandSelectByID,
                    id))
                {
                    return ProcessRow(reader);
                }
 
            }
            else
            {
                using(SqlDataReader reader = SqlHelper.ExecuteReader(
                    ConnectionString,
                    _commandSelectByID,
                    id))
                {
                    return ProcessRow(reader);
                }
 
            }
        }

This now is the right code.

public ReturnedCheckEntity SelectByID(int id)
        {
            if (WithTransaction)
            {
 
                using (SqlCommand cmd = new SqlCommand(_commandSelectByID, Connection, Transaction))
                {
 
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add(new SqlParameter("@ID", id));
 
                    using
                    (
                        SqlDataReader r =
                            cmd.ExecuteReader
                                ()
                    )
                    {
 
                        return ProcessRow(r);
 
                    }
                }
 
            }
            else
            {
                using
                    (
                        SqlDataReader r =
                            SqlHelper.ExecuteReader
                                (
                                    ConnectionString
                                    , System.Data.CommandType.StoredProcedure
                                    , _commandSelectByID
                                    , new SqlParameter("@ID", id)
                                )
                    )
                {
 
                    return ProcessRow(r);
 
                }
            }
        }

You must not use SqlHelper’s Execute reader instead use the native SqlCommand object for creating a SqlDataReader.

Happy Coding!

C#/WPF/MVVM/Web API Properly stream/download files from .NET Web API and display progress through progress bar

1. Setup .NET Web API code.

namespace MainWebService.Controllers
{
 
    [ApiController]
    [Route("[controller]")]
    public class BuildController : ControllerBase
    {        
 
        [HttpPost]
        [Route("[action]")]
        public Stream GetFile([FromBody] StringParamEntity parameters)
        {
            FileStream stream = System.IO.File.OpenRead(Path.Combine(System.IO.Directory.GetCurrentDirectory(), parameters.ParamString));
            return stream;
 
        }
 
    }
}

Nevermind the parameter for the method. Basically what the snippet says is you create a [FileStream] object and return it. You can you its base class [Stream] as the return type of the method.

2. Consume through HttpRequestMessage

public static async Task<Stream> CommonHTTPClientCodeForFileTransfer(HttpClient client, string baseURL, string webMethod, StringParamEntity parameters)
{
 
    try
    {
 
        HttpResponseMessage hrm = await ReturnHttpStream(client, baseURL, webMethod, parameters);
        return await hrm.Content.ReadAsStreamAsync();
 
    }
    catch(Exception ex)
    {
 
        Exception newEx = new Exception(ErrorMessageConstant.HTTP_CLIENT_FILE_STREAM_ERROR_MESSAGE, ex);
        throw newEx;
 
    }
 
}
 
private static Task<HttpResponseMessage> ReturnHttpStream(HttpClient client, string baseURL, string webMethod, StringParamEntity parameters)
{
 
 
 
    string requestUrl = new Uri(new Uri(baseURL), webMethod).ToString();
 
    JsonSerializerSettings microsoftDateFormatSettings = new JsonSerializerSettings
    {
        DateFormatString = Constant.MY_DATETIME_FORMAT,
        DateFormatHandling = DateFormatHandling.IsoDateFormat
    };
 
    string
        jsonData =
            JsonConvert.SerializeObject
                (
                    parameters,
                    microsoftDateFormatSettings
                );
 
    client.DefaultRequestHeaders.Accept.Clear();
 
    
    if( parameters.ParamString.IndexOf(".dll", StringComparison.CurrentCultureIgnoreCase) > 0 )
        client.DefaultRequestHeaders.Accept.Add(new MediaTypeWithQualityHeaderValue("application/x-msdownload"));
    if (parameters.ParamString.IndexOf(".exe", StringComparison.CurrentCultureIgnoreCase) > 0)
        client.DefaultRequestHeaders.Accept.Add(new MediaTypeWithQualityHeaderValue("application/octet-stream"));
    if (parameters.ParamString.IndexOf(".zip", StringComparison.CurrentCultureIgnoreCase) > 0)
        client.DefaultRequestHeaders.Accept.Add(new MediaTypeWithQualityHeaderValue("application/zip"));
 
    var request = new StringContent(jsonData, Encoding.UTF8, "application/json");
    
 
    using (HttpRequestMessage rm = new HttpRequestMessage(HttpMethod.Post, requestUrl))
    {
        rm.Content = request;
        return client.SendAsync(rm, HttpCompletionOption.ResponseHeadersRead);
    }
 
 
}

Use the class instance of HttpRequestMessage instead of HttpClient. Use the HttpCompletion.ResponseHeadersRead enum argument for the second parameter of its SendAsync method. This is a key setting that will properly allow every byte to be recognized in the stream(in real time). Either this argument is not used or you went to using a different class (HttpClient) anyway it will not work, the file will be downloaded completely even before iterating per byte in the stream instance at the client side.

Nevermind the other code that is for the settings of JSON serialization.

3. WPF XAML

<StackPanel Grid.Row="1" Orientation="Vertical">
        <TextBlock Text="{Binding StatusText}"></TextBlock>
        <ProgressBar Minimum="0" Maximum="100" Name="pbStatus" Value="{Binding ByteTransfered}"></ProgressBar>
</StackPanel>

Create the progress bar element and have the integer Value property binded to the View Model.

4. Finally, ViewModel code.

Task<Stream> tStream = CommonFunctions.CommonHTTPClientCodeForFileTransfer
(
    client,
    reg.DataSourceURLPath,
    "Build/GetFile",
    param2
);
 
await tStream;
 
Stream st = tStream.Result;
 
int length = f.ByteSize;
byte[] result = new byte[length];
 
StatusText = f.FileName;
 
string destinationPath = System.IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, Constant.STEP_ASIA_MIS_INSTALLATION_FOLDER, f.FileName);
 
byte[] bytes = new byte[length + 100000];
int numBytesToRead = (int)length;
int numBytesRead
do
{
    int n = await st.ReadAsync(bytes, numBytesRead, 100000);
    numBytesRead += n;
    numBytesToRead -= n;
 
    StatusText = string.Format("{0} {1:0.##}%",  f.FileName,((1.0 * numBytesRead) / length) * 100.0 );
    ByteTransfered = CommonFunctions.ConvertToInt( (1.0 * numBytesRead / length) * 100 );
 
 
} while (numBytesToRead > 0);
 
using (var fs = File.Create(destinationPath))
{
    fs.Write(bytes, 0, length);
}

Hope you learned.

Truncate decimal places with C#

Hi there, I just want to share with you on how to properly truncate decimal places.

Code Snippet

public static class DecimalExtensions
{
    public static decimal TruncateEx(this decimal value, int decimalPlaces)
    {
        if (decimalPlaces < 0)
            throw new ArgumentException(“decimalPlaces must be greater than or equal to 0.”);
 
        if (value == 0) return decimal.Zero;
 
        var modifier = Convert.ToDecimal(0.5 / Math.Pow(10, decimalPlaces));
        return Math.Round(value > 0 ? value – modifier : value + modifier, decimalPlaces, MidpointRounding.AwayFromZero);
    }
}

Example usage :

(9.577M).TruncateEx(2); //9.57

(119.577M).TruncateEx(2); //119.57

Kudos to https://stackoverflow.com/users/94990/nightcoder for the solution. I had added some code to fix bugs regarding MidpointRounding and wrong handling with zero argument.

This extension method is a very neat solution. It will give you immediate access to the truncate method from any decimal in your code.

WPF – Touchdown event firing at overlapping controls.

This happens when a button or link behind another button or link is getting its touchdown event fired as well.

The fix here:

1. Make sure tap event of both controls are subscribed on the Touchdown event.
2. Put a e.Handled = true; statement inside the eventhandler of the overlapping control.

Example:

Code Snippet

private void OpenDoctor_Touch(object sender, TouchEventArgs e)
{
    e.Handled = true;
    string commandParameter = ((Button)sender).CommandParameter.ToString();
    var viewModel = (DoctorsBySpecializationViewModel)DataContext;
    if (viewModel.OpenDoctor.CanExecute(commandParameter))
        viewModel.OpenDoctor.Execute(commandParameter);
}

Happy Coding!

WPF and MVVM – Touch Device needs to tap twice on button to fire command.

Touch devices may need to tap twice on a button to fire command.

This always happens on my end when the button is located alongside a textbox or other elements on a user or windows control.

The fix.

1. Add a touch down event for the event in the xaml.

2. Fire command manually

            MapEntity commandParameter = (MapEntity)(((Button)sender).CommandParameter);

            var viewModel = (FindADISViewModel)DataContext;
            if (viewModel.OpenDIS.CanExecute(commandParameter))
                viewModel.OpenDIS.Execute(commandParameter);

It works!

My stored procedure parameter is DateTime2 but I keep on getting SqlDateTime overflow. Microsoft.ApplicationBlocks.Data.SqlHelper

If you keep on getting this even if your stored procedure parameter is DateTime2. The error is:

Error – SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM

The solution is to pass an SqlParameter object with the DateTime2 SqlDBType specified. Before I used to pass parameters of System.Object type and let the CLR and SQL Server figure out the proper type between DateTime and DateTime2.

Please look at the code for before and after. Also focus on the DocumentDate, StartDate and EndDate parameters.

BEFORE – used parameters as System.Object

object retVal = SqlHelper.ExecuteScalar(
                    Transaction,
                    _commandInsert,
                    data.ItemID,
                    data.SequenceNumber,
                    data.ReferenceNumber,
                    data.DocumentDate,
                    data.StartDate,
                    data.EndDate,
                    data.Spoilage,
                    data.Remarks,
                    data.NotedByUserID,
                    data.QualityCheckUserID,
                    data.ReturnedByUserID,
                    data.CreatedBy,
                    data.UpdatedBy,
                    data.EstimatedQuantity,
                    data.ActualQuantity,
                    data.RawMaterial,
                    data.DirectLabor,
                    data.MachineOverhead,
                    data.ApproveMaterialsByUserID,
                    (byte)data.Status,
                    data.WIPItemID,
                    data.WIPReportID);

AFTER and the CORRECT way – passed parameters as SqlParameter, also specifying explicitly SqlDBType DateTime2

SqlParameter paramDocumentDate = new SqlParameter("@DocumentDate", SqlDbType.DateTime2); paramDocumentDate.Direction = ParameterDirection.Input;
            SqlParameter paramStartDate = new SqlParameter("@StartDate", SqlDbType.DateTime2); paramStartDate.Direction = ParameterDirection.Input;
            SqlParameter paramEndDate = new SqlParameter("@EndDate", SqlDbType.DateTime2); paramEndDate.Direction = ParameterDirection.Input;
 
            paramDocumentDate.Value = data.DocumentDate;
            paramStartDate.Value = data.StartDate;
            paramEndDate.Value = data.EndDate;
 
            if(WithTransaction)
            {
                SqlHelper.ExecuteNonQuery
                (
                    Transaction,
                    CommandType.StoredProcedure,
                    _commandInsert,
                    paramID,
                    new SqlParameter("@ItemID", data.ItemID),
                    new SqlParameter("@SequenceNumber", data.SequenceNumber),
                    new SqlParameter("@ReferenceNumber", data.ReferenceNumber),
                    paramDocumentDate,
                    paramStartDate,
                    paramEndDate,
                    new SqlParameter("@Spoilage", data.Spoilage),
                    new SqlParameter("@Remarks", data.Remarks),
                    new SqlParameter("@NotedByUserID", data.NotedByUserID),
                    new SqlParameter("@QualityCheckUserID", data.QualityCheckUserID),
                    new SqlParameter("@ReturnedByUserID", data.ReturnedByUserID),
                    new SqlParameter("@CreatedBy", data.CreatedBy),
                    new SqlParameter("@UpdatedBy", data.UpdatedBy),
                    new SqlParameter("@EstimatedWeight", data.EstimatedWeight),
                    new SqlParameter("@ActualWeight", data.ActualWeight),
                    new SqlParameter("@ActualQuantity", data.ActualQuantity),
                    new SqlParameter("@RawMaterial", data.RawMaterial),
                    new SqlParameter("@DirectLabor", data.DirectLabor),
                    new SqlParameter("@ManufacturingOverhead", data.ManufacturingOverhead),
                    new SqlParameter("@ApproveMaterialsByUserID", data.ApproveMaterialsByUserID),
                    new SqlParameter("@Status", (byte)data.Status),
                    new SqlParameter("@WIPItemID", data.WIPItemID),
                    new SqlParameter("@WIPReportID", data.WIPReportID),
                    paramRV
                );
            }

HAPPY CODING!!!

SSRS Reportviewer – The underlying connection was closed: A connection that was expected to be kept alive was closed by the server.

Our client is complaining about a report viewer issue happening only to a single computer. When the c# winforms application tries to open a form class that happens to have a reportviewer object in it, it hangs the whole application and eventually throws an exception.

There is nothing unusual with the SSRS server since it is hosted on a on-premise SQL Server and I happen to know that it is not heavily used.

The c# is throwing an exception of
The underlying connection was closed: A connection that was expected to be kept alive was closed by the server.

with an inner exception of

Unable to read data from the transport connection: An existing connection was forcibly closed by the remote host.

The stack trace for both respectively,

20180104 04:10:34.062
Computer Name: YOG
CFS Version: 2.0.4.9
The underlying connection was closed: A connection that was expected to be kept alive was closed by the server.

   at System.Web.Services.Protocols.WebClientProtocol.GetWebResponse(WebRequest request)
   at System.Web.Services.Protocols.HttpWebClientProtocol.GetWebResponse(WebRequest request)
   at Microsoft.SqlServer.ReportingServices2005.Execution.RSExecutionConnection.GetWebResponse(WebRequest request)
   at Microsoft.Reporting.WinForms.ServerReportSoapProxy.GetWebResponse(WebRequest request)
   at System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(String methodName, Object[] parameters)
   at Microsoft.SqlServer.ReportingServices2005.Execution.ReportExecutionService.LoadReport(String Report, String HistoryID)
   at Microsoft.SqlServer.ReportingServices2005.Execution.RSExecutionConnection.LoadReport(String Report, String HistoryID)
   at Microsoft.Reporting.WinForms.ServerReport.GetExecutionInfo()
   at Microsoft.Reporting.WinForms.ServerReport.SetParameters(IEnumerable`1 parameters)
   at Accounting.Reports.UI.FormReportPreview.LoadReport()
   at Accounting.UI.FormBase.PreLoadReportComponents()
————————————–

————————————–
20180104 04:10:34.062
Computer Name: YOG
CFS Version: 2.0.4.9
Unable to read data from the transport connection: An existing connection was forcibly closed by the remote host.

   at System.Net.Sockets.NetworkStream.Read(Byte[] buffer, Int32 offset, Int32 size)
   at System.Net.PooledStream.Read(Byte[] buffer, Int32 offset, Int32 size)
   at System.Net.Connection.SyncRead(HttpWebRequest request, Boolean userRetrievedStream, Boolean probeRead)
————————————–

Hours of research and trial and error led me to this fix which you only need to set this field upon runtime.

System.Net.ServicePointManager.Expect100Continue = false;

A better code is to add additional settings at your application config. This is better because it turned out to be a surgical strike and I never needed to commit unnecessary code to our codebase. The code was applied only to a specific computer.

Just put the code inside the configuration node like this

<configuration>
  <system.net>
    <settings>
      <servicePointManager expect100Continue="false"/>
    </settings>
  </system.net>
</configuration>

Happy Coding!

Better constructor, adding listviewitem object to listview.items collection

Sharing to you a simpler way to add a listviewitem object compared to creating an instance from the items.Add() method and adding subitems to the instance until the columns are completed.

ListViewItem item = new ListViewItem(new string[13] {
entity.ItemID.ToString(),
entity.Name.ToUpper(),
entity.Description,
entity.Code,
entity.Remarks,
entity.Size,
CommonFunctions.ReformatToDecimal(entity.Stock, Globals.SystemSetting.InventoryDecimalDigit),
CommonFunctions.ReformatToDecimal(entity.SellingPriceBeforeVAT, Globals.SystemSetting.InventoryDecimalDigit),
CommonFunctions.ReformatToDecimal(entity.MovingCost, Globals.SystemSetting.InventoryDecimalDigit),
brandName,
location,
entity.ItemUnit.ItemUnitName,
CommonFunctions.ConvertBoolToEnglish(entity.Active)});

item.Tag = entity;
listViewItem.Items.Add(item);

Just pass an array of strings to the constructor.