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!

My lettuce and first time use of grow lights.

My hydroponics setup has always been in front of my brother’s house wall. I am only getting at the most 4.5 hours of sunlight. Intermittent sunlight due to cloudy skies and regular rains from the wet season disrupts the growth of my vegetables.

I bought two ten dollar grow lights. Added a little bit of vermicompost just before moving my lettuce to my hydroponics system. Good results.

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!

Homegrown lettuce 2020

I shared this pic since the particular lettuce stands out among the other lettuce planted on the same set.

I had it positioned in a way that it will receive at least 4 hours of sunlight.

I feel that I have accomplished a milestone. I got the size of the plant significantly larger against every plant I have planted. My next milestone is to produce my own greenhouse infrastructure. Currently I only have a small area that is capable of receiving at least 4 hours of sunlight.

Passed 70-767, 4th Microsoft Exam and MCSE Title

This particular exam is 70-767 SQL Server Data Warehousing.

This has been given the longest time for preparation and yet a sub 800 points was given to me. There has been a lot of terms that was unfamiliar especially under the Microsoft Azure zone.

For preparation:

  1. I bought the official reference book for the exam at Microsoft press store. I read the book at least 3 times. Also you need to read URL links that are referenced in the book. My rule when opening URL links is to read at least 2 levels, which means when there is another URL in the website, that gets opened and read as well.
  2. If you feel you need to find other articles on the web to get a complete grasp of a topic, please do.
  3. You need to practice on hand. It has a lot of benefits, one is you get to put syntaxes into your long term memory.

Once the exam day arrives, stay relax until the exam finishes. I told my self before hitting the End Exam button that whatever the score I’ll get, I’ll be fine with it. I just need to review and retake if needed.

DBCC CHECKIDENT RESEED Scenarios

I wrote this post to help SQL lovers get informed on how to use.

https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-checkident-transact-sql?view=sql-server-ver15

This post will primarily inform the exact behavior when we RESEED.

When you reseed upon

a. Right after CREATE TABLE and no records have been inserted

b. Right after TRUNCATE TABLE.

c. Right after DELETE TABLE but no records have been inserted(Yes, why would someone do this?)

THE NEW IDENTITY VALUE WILL FOLLOW THE RESEED VALUE.

But, when you reseed after records have been inserted. The followup new identity value will be an increment of 1 of the reseed value.

Code below show the different scenarios:

DECLARE @ReseedValue INT = 100;

IF OBJECT_ID(N’dbo.SimpleTable’, N’U’) IS NOT NULL
DROP TABLE dbo.SimpleTable;

CREATE TABLE dbo.SimpleTable
(
ID INT NOT NULL IDENTITY(1, 1),
[TestCol] CHAR NULL
);

DBCC CHECKIDENT(N’dbo.SimpleTable’, RESEED, @ReseedValue);

INSERT INTO dbo.SimpleTable VALUES
(‘A’)
, (‘B’)
, (‘C’);

SELECT * FROM dbo.SimpleTable;
/*
Result
ID TestCol
100 A
101 B
102 C
*/

TRUNCATE TABLE dbo.SimpleTable;

DBCC CHECKIDENT(N’dbo.SimpleTable’, RESEED, @ReseedValue);

INSERT INTO dbo.SimpleTable VALUES
(‘A’)
, (‘B’)
, (‘C’);

SELECT * FROM dbo.SimpleTable;

/*
Result
ID TestCol
100 A
101 B
102 C
*/

DELETE dbo.SimpleTable;

DBCC CHECKIDENT(N’dbo.SimpleTable’, RESEED, @ReseedValue);

INSERT INTO dbo.SimpleTable VALUES
(‘A’)
, (‘B’)
, (‘C’);

SELECT * FROM dbo.SimpleTable;

/*
Result
ID TestCol
101 A
102 B
103 C
*/
SET @ReseedValue = 200;
DBCC CHECKIDENT(N’dbo.SimpleTable’, RESEED, @ReseedValue);

INSERT INTO dbo.SimpleTable VALUES
(‘D’);

SELECT * FROM dbo.SimpleTable;

/*
Result
ID TestCol
101 A
102 B
103 C
201 D
*/

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([FromBodyStringParamEntity 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<StreamCommonHTTPClientCodeForFileTransfer(HttpClient clientstring baseURLstring webMethodStringParamEntity parameters)
{
 
    try
    {
 
        HttpResponseMessage hrm = await ReturnHttpStream(clientbaseURLwebMethodparameters);
        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<HttpResponseMessageReturnHttpStream(HttpClient clientstring baseURLstring webMethodStringParamEntity 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();
 
    
    ifparameters.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(jsonDataEncoding.UTF8, "application/json");
    
 
    using (HttpRequestMessage rm = new HttpRequestMessage(HttpMethod.Post, requestUrl))
    {
        rm.Content = request;
        return client.SendAsync(rmHttpCompletionOption.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<StreamtStream = 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(bytesnumBytesRead, 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.