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.

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.

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
  1. private void OpenDoctor_Touch(object sender, TouchEventArgs e)
  2. {
  3.     e.Handled = true;
  4.     string commandParameter = ((Button)sender).CommandParameter.ToString();
  5.     var viewModel = (DoctorsBySpecializationViewModel)DataContext;
  6.     if (viewModel.OpenDoctor.CanExecute(commandParameter))
  7.         viewModel.OpenDoctor.Execute(commandParameter);
  8. }

Happy Coding!

Two extended dual monitors for business laptops

I have just ported our Accounting Suite project from Visual Studio 2008 R2 to Visual 2013. The latter supports “New Window” for opened .cs files.

Also current GUIs of SQL Server allows you to move your query tabs to a different screen.

More screens will maximize the benefits of this features. I have only one screen output connection.
Yesterday I just bought a VGA to USB 2.0 connection adapter.

Tested it right away with my extra monitor and it worked. The next day I bought a new monitor.

Cons : The screen’s resolution cannot be configured. It is stuck to 800×600.

Play safe. Neutral literal date format for multi language property of SQL Server Login

Lesson learned. My usual date time literal:

YYYY-MM-DD HH:MM:SS.nnn

happens to be a bad practice since it is interpreted differently by the language property of the current login.

This sample code shows how it interprets a date with poor format by different languages.

DECLARE @datePoorFormat AS VARCHAR(23);
SET @datePoorFormat = ‘2017-02-12 23:12:12.123’;

DECLARE @DateTable AS TABLE (temp1 DATETIME)

SET LANGUAGE us_english;
INSERT INTO @DateTable(temp1) VALUES (@datePoorFormat);

SET LANGUAGE british
INSERT INTO @DateTable(temp1) VALUES (@datePoorFormat);

SELECT temp1 FROM @DateTable

The result of the query is

2017-02-12 23:12:12.123 — Feb 12
2017-12-02 23:12:12.123 — Dec 02

Please use the neutral formats. For DATETIME type these are:

‘YYYYMMDDHH:MM:SS.nnn’
‘YYYY-MM-DDTHH:MM:SS.nnn’
‘YYYYMMDD’
I like the second one because hyphens emphasizes date parts better in your code.
BTW language config is set by default at your current login. The picture below shows where you can set it via GUI.