Sunday, May 28, 2017

Accessing Oracle Databases in .NET

We recently went through hell trying to get an Oracle provider installed that works and is not a huge set of files that are impossible to install. We are primarily a SQL Server "shop" and hadn't had to use Oracle in a long, long time. So, this was quite an ordeal. I've been using SQL Server for 17 years (no expert, but I know my way around) ... but Oracle always mystifies me. It's just so different!

Anyway, I was not the one looking for a valid provider, it was my husband (we work together) ... and he finally found something that worked. And, luckily, he found it before he pulled out all his hair (and he *does* have a lot of it)!  You'll need to use this NuGet package (it's only a 2.5 MB download) and quite easy to install (as most NuGet packages are):

If you went to the above link, you'll see that all you have to do is install the package from the NuGet Package Manager Console command line. The Package Manager Console has been built into Visual Studio (2012 and later). Find it under Tools | NuGet Package Manager | Package Manager Console. If, for some reason, you do not see the NuGet stuff under Tools (either because you have a VS earlier than 2012 or it just wasn't installed when you installed VS), then it can be installed manually as shown here:

I should also mention that when you add the NuGet Oracle.ManagedDataAccess package, it'll add a section to the config file that contains a sample connection string:

<version number="*">
<dataSource alias="SampleDataSource" descriptor="(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ORCL))) "/>

We added the following <connectionStrings> setting in the config:

<add name="Oracle" connectionString="DATA SOURCE=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=XXX.XXX.XXX.XXX)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=MyDatabase)));User id=MyID;Password=MyPwd;"/>

Obviously, change the XXX.XXX.XXX.XXX to your server's IP address, as well as changing MyDatabase, MyID and MyPwd to your own settings.

That should do the trick! Happy coding!  =0)

Friday, March 31, 2017

Fire and Forget

Lately, I've run across a few questions in the Forums about being able to spin off a thread that might contain a long-running process that doesn't need to be monitored in any way. It's what's called a "Fire and Forget" process. This is useful for processes where the caller doesn't need to have any direct feedback from the process, it just needs to initiate that process and then move on. I can see where this could be quite useful for running some SQL scripts or Stored Procedures on a database.

For example, say that you have a UI (WinForms or WPF) and a button click (or whatever) to start the Processes. You don't want to block the UI thread, so that's an important thing to keep in mind. Let's also say that we have another class we use that contains all the processes that we want to Fire and Forget.  An excellent way to deal with all this is to initiate Fire and Forget threads using Task.Run().

In order to simulate this, I'm going to write to a TextBox from the UI thread, and use a Console.WriteLine() in the Fire and Forget processes to show the progress of each call. When running your application from Visual Studio, you can see the output from the Console.WriteLine() in the Output window (look for it under "Debug | Windows | Output" if you don't already have it show up when you're debugging).

So, first look at this code:

Utils util = new Utils();
Console.WriteLine("Run Tasks in 'for' loop ...");
for (int i = 0; i < 10; i++)
Task.Run(() =>

this.TextBox1.Text += string.Format("Started Thread {0} ...\r\n", i);

And here is the FireAndForget() method in the Utils class:

public void FireAndForget(int i)
Console.WriteLine("Starting Task #{0}", i);
// Simulate long running thread, but make them random time periods
var rand = new Random();
Thread.Sleep(rand.Next(10000)); // 10 seconds or less
Console.WriteLine("Completed Task #{0}", i);

Running this code, you can see in the UI that the TextBox sequentially lists all 10 threads as having started, and watching the Output window, you can see that the TextBox shows all 10 before the Output window shows all 10 (in other words, each running in a separate thread). And they are Completed at different times.

But, wait ... something is wrong!!  Notice in the Output window that you will often see Tasks with duplicate numbers! And, if you comment out the line for setting the TextBox1.Text, you will see every single Task has the number 10!! Why is this happening?

It has to do with something called Closures. And it's because the i variable used in Task.Run(()=>{ util.FireAndForget(i) }); by design, uses the current value of i (10), not the value of i when the delegate was created (0 thru 9). Closures close over variables, not over values. That's a quote from Eric Lippert's blog post, which will probably explain things a lot better than I can. See it here (and note that he has a link to Part 2 also):

The reason that setting the TextBox.Text in the UI seems to not affect it as much is simply because of the delay that the UI thread takes to update UI, in case you were wondering ...

There are two ways around this:

for (int i = 0; i < 10; i++)
// By creating a new variable each time through the for loop
// and using that instead, you can avoid the problem
int ii = i;
Task.Run(() =>

this.TextBox1.Text += string.Format("Started Thread {0} ...\r\n", i);

If you've looked at the link to Eric's blog post, you'll see that Microsoft decided to fix this issue in C# 5, but *only* in the foreach, not in the for. So, in the foreach version, you could do it like this with no problems:

//Console.WriteLine("Run Tasks in 'foreach' loop ...");
int[] iArray = { 0, 1, 2, 3, 4, 5, 6, 7, 8, 9 };
foreach (int i in iArray)
Task.Run(() =>
this.TextBox1.Text += string.Format("Started Thread {0} ...\r\n", i);

So, I diverged a bit from the original intent of this post, showing both the Fire And Forget process, and the little "gotcha" that you might have encountered had you used a for instead of a foreach.

Happy coding!  =0)

Sunday, February 26, 2017

It's All About The Data

Every application has to deal with *some* kind of data. Where it's stored externally and how it's used in the application can vary widely ... but in this blog post, I will deal with SQL Server database storage and reading the data into either a DataSet, DataTable or a List of objects. I will *not* talk about Entity Framework nor LINQ-to-SQL, mainly because I pretty much only use DataSets.

I have seen many posts on Forums, blogs and elsewhere, recommending the use of the Load() method of a DataSet or DataTable. The Load() method takes an IDataReader parameter (so, in this case, I'd use a SqlDataReader). Supposedly, according to the many times that I've seen this recommended, it is supposed to be the fastest way to do this.

Personally, I have always used a SqlDataAdapter and its Fill() method to put the result set(s) returned from a SQL call into a DataTable or a DataSet. And I always thought that this was the fastest (although I had never tested that).

A couple of days ago, I saw this Load() method recommended again, several times ... and again I wondered about the performance of the Load() vs the Fill(), so I decided that it's about time to run some performance tests to put the question to rest, once and for all. And, while I was at it, I also decided to throw in a test putting data into a List<T> in addition to the DataSet/DataTable tests (not for my benefit, since I seldom use List<T> in this manner; but for you, Dear Reader).

So, first, here is the benchmarking code:

private void TestLoadvsFillvsList()
DataTable dtGlobal;
DataSet dsGlobal;
decimal LoadMilli;
decimal FillMilli;
decimal ListMilli;

Stopwatch oWatch = new Stopwatch();

// The DataTable.Load(IDataReader) method:
using (SqlConnection conn = newSqlConnection(this.ConnectionString))
SqlCommand sc = new SqlCommand("select * from Logdata", conn);
this.dtGlobal = new DataTable();

SqlDataReader dr = sc.ExecuteReader();

// If you have multiple SELECTs in your SqlCommand, you can put the multiple result sets
// into a DataSet with syntax similar to the following examples:
//this.dsGlobal.Load(dr, LoadOption.OverwriteChanges, dtLogData, dtMessage);
//this.dsGlobal.Load(dr, LoadOption.OverwriteChanges, "Table1", "Table2");
LoadMilli = oWatch.ElapsedMilliseconds;
Console.WriteLine("Load Time {0} milliseconds, Row Count, {1}", LoadMilli, this.dtGlobal.Rows.Count);

// The DataAdapter.Fill(DataSet/DataTable) method:
using (SqlConnection conn = new SqlConnection(this.ConnectionString))
SqlCommand sc = new SqlCommand("select * from Logdata", conn);
this.dsGlobal = new DataSet();

SqlDataAdapter da = new SqlDataAdapter(sc);
da.Fill(this.dsGlobal); // could also Fill(this.dtGlobal)
FillMilli = oWatch.ElapsedMilliseconds;
Console.WriteLine("Fill Time {0} milliseconds, Row Count, {1}", FillMilli, this.dsGlobal.Tables[0].Rows.Count);

// The while (dr.Read()) method:
List<LogData> logList = new List<LogData>();
using (SqlConnection conn = new SqlConnection(this.ConnectionString))
SqlCommand sc = new SqlCommand("select * from Logdata", conn);

LogData oLog; ;
SqlDataReader dr = sc.ExecuteReader();
while (dr.Read())
oLog = new LogData();
oLog.logdatakey = (long)dr["logdatakey"];
oLog.logdatetime = (DateTime)dr["logdatetime"];
oLog.message = dr["message"].ToString();
oLog.category = dr["category"].ToString();
ListMilli = oWatch.ElapsedMilliseconds;
Console.WriteLine("List Time {0} milliseconds, Row Count, {1}", ListMilli, logList.Count);

if (LoadMilli > FillMilli)
Console.WriteLine("Fill is {0:0.00} faster than Load", LoadMilli / FillMilli);
Console.WriteLine("Load is {0:0.00} faster than Fill", FillMilli / LoadMilli);

Console.WriteLine("A List of objects is faster than either one!", FillMilli / LoadMilli);
Console.WriteLine("List is {0:0.00} times faster than Load", LoadMilli / ListMilli);
Console.WriteLine("List is {0:0.00} times faster than Fill", FillMilli / ListMilli);
public class LogData
public long logdatakey { get; set; }
public DateTime logdatetime { get; set; }
public string message { get; set; }
public string category { get; set; }

OK, so now notice in the last set of Console.WriteLine() statements above where I state that using a List<T> is *always* faster than using a DataSet/DataTable (and by a lot, as I'll get to in a minute). What this tells me is that if you have no use for DataSets at all, then you'll do just fine using a SqlDataReader to add your data to a List<T>, and then use your List elsewhere in your processing.

I used a sample size of 425,376 rows in the database table. The average time for the 3 methods were approximately as follows, in milliseconds:

Load 4040
Fill 3030
List 2020

So, doing the math:
The Fill about 1.33 times faster than the Load.
The List is about 2 times faster than the Load.
The List is about 1.5 times faster than the Fill.

I feel vindicated! I can now safely reply to these forum posts that, indeed, the .Fill() is significantly faster than the .Load()!!

Happy coding!! :0)

Tuesday, January 17, 2017

A Lot of Misbehaving - Bad Computer!!

Last week, both of my computers decided to misbehave at almost the same time. Windows Defender was the first to misbehave by hogging 40% of my CPU, which left my fan running constantly and my computer getting pretty warm ... this was on my Surface Pro 4, running Windows 10. Next, Windows Update did nearly the same thing, again hogging CPU and causing a constantly running fan and excess heat … this was on my Dell Venue 11 Pro, running Windows 8.1. Luckily for me, the Dell didn’t start having problems until after I had resolved the problems on the Surface, but I only got a day of breathing room. Talk about a frustrating week!!! So, how did I fix these naughty computers? Read on …

Windows Defender

The weird thing about this problem was that this computer wasn’t even idle at the time … Defender isn’t supposed to be running any kind of Scan while the computer is in use, and I was *definitely* using it when this mess started! But, I left it running, assuming it would finish up soon … 4 hours later, it was still running!  I realized later (when trying a Quick Scan on my other computer), that my scans only take about 3-8 minutes, not hours and hours and hours!! That should have been my first clue. I Googled and saw several suggestions, none of which helped:

  • I tried rebooting, didn’t help.
  • I set it to exclude scanning the file MsMPEng.exe, didn’t help.
  • I tried scanning with sfc (System File Check, using /verifyonly rather than /scannow), it told me I had no problems.

I hibernated overnight, because I didn’t want it overheating during the night. Obviously, when I brought it back from hibernation the next morning, it continued running at 40% CPU, as expected, and I shut it down again.

Later that day, I ran Defender from its UI on my *other* computer and I noticed that it shows a progress bar and a running count of the files it is scanning (I guess I never bothered to run it from the UI before). So, I decided to try a Quick Scan on *this* computer, even though Defender was still scanning (the 40% CPU). Lo and behold, the UI showed that it got stuck after scanning 12,471 files … number 12,472 just stayed up there and now my CPU usage rose to nearly 80%!  Yikes!!  I shut it down again, and worked the rest of the day from my other computer (I do all of my development work on VM’s that are hosted somewhere else on our network, so I could actually work on stuff from either computer).

Over the course of the day, I periodically started it up just to check my email or to try something I found while Googling (and hoping that just the act of restarting it might eventually magically make it stop), and then would shut it down again because no magic happened and the CPU was still running high. The only thing different that I did before shutting it down that night was to Disable the Scheduled Scan in the Task Scheduler, so it would at least not keep trying to run a scan.

The next morning after startup, it continued misbehaving and I continued shutting it down and starting it back up again to check my email. After a couple of rounds of this up and down, finally magic *did* happen! All of a sudden it started working. I think that Disabling the Scheduled Scan definitely helped. But the real kicker was my discovery from a fellow MVP (Derek Knight) as to *why* I probably had the problem in the first place.

Back in the day (ending with Windows 7), a full ShutDown was always better than a Restart because, basically, when you started back up from a full ShutDown it was starting up totally “from scratch”, like a cold boot. A Restart didn’t shut everything down before it came back up and didn’t do all of the “checks” that a full start up did. But now, starting with Windows 8, it’s totally the opposite behavior! And the problem that I was having was most likely related to always doing a ShutDown. Derek says that “Defender has an annoying habit of getting hung up on scanning a non existent driver that doesn't exist any longer. A full Restart loads a new instance of the drivers. That normally solves it. This can also happen when you shut down, if Fast Startup is enabled. After 3 or 4 start ups, some existing drivers don't fully load so Defender sees them as corrupt. They are in memory but not physically able to be scanned.”

Fast Startup was most likely the culprit … just prior to all hell breaking loose with my Defender problems, I had been having a lot of issues/warnings about a corrupted bluetooth mouse driver. Restarting seemed to solve the problem, but sometimes it didn’t (probably when I ShutDown instead of Restarted). Anyway, read this very interesting article from “How-To Geek” about Fast Startup. I turned mine off and have not had any problems since:

Windows Update

No sooner had I fixed the Defender problem on my Surface, then my Dell Venue started misbehaving with Windows Update. Arrrrgggghhhh!!!   =0(   Apparently, Update was getting stuck while “Checking for Updates”. Maybe it was stuck on a file download or maybe it was something else, I don’t know what. I only know that it would run up my CPU to about 25-30% and never stop!

So, I ended up shutting off Windows Update until I could find a solution. To turn it off go to Control Panel | System and Security | Windows Update | Change Settings and choose “Never check for updates” … like this:

I had found a few suggestions to try while Googling, like running the built-in Update Troubleshooter, which you can find by typing “troubleshooter” in the Start | Search box and click the Troubleshooter result. Then start Troubleshooting by clicking on System and Security | Fix Problems With Windows Update:

It didn’t work for me, but it’s a good starting place.

Next, it was “How-to Geek” to the rescue again:

The How-To Geek offers several things you can try to fix the problem. I had already tried one or two of them that I had found from my previous Googling. I decided that I was so sick and tired of trying every little suggestion I found when none of them ever worked, that I was going to skip to the end of the article and try the sure-fire, easy way (according to How-To Geek, it has always worked for them in the past when none of the other solutions did) … and that is using the third-party tool called WSUS Offline Update. The link to download it is provided in the above article, as well as instructions as to what to do with it once you’ve downloaded it. What WSUS does is download a bunch of Windows Updates, depending on which Windows version you’ve got and which downloads you’ve already installed. Be forewarned that it may take a while to download if you’re way behind on updates. Mine took all night to download (but, my Internet connection stinks … so YMMV). Installing the Updates took a *lot* less time than downloading them. But, it’s the end result that matters and when all is said and done, this *worked*!!!! No more stalling when it’s trying to Check for Updates!

Oh, and don’t forget to change the Windows Updates settings back to “Install Updates Automatically”.

Wednesday, December 28, 2016

Always Use Environment.SpecialFolders

Never, *ever*, hard-code a path to directories like User Documents or AppData!! As far back as Windows XP, a user could move those directories off of the C drive (from C:\Users\MyName\……) and put them on another drive. This is mainly done for two reasons:

  1. To save space on the C drive.
  2. To keep data separate from the OS and other installed programs.

So, in your application, if you hard-coded a path to C:\Users\MyName\Documents you could be in trouble if you tried to write a file there, and the user had “re-mapped” the folder elsewhere … because you’d get a “Directory Not Found” exception. Instead, use the Environment.SpecialFolders enum. (Please note that this probably does NOT apply to UWP apps … and since I don’t write those currently, I can’t speak authoritatively about the correct way to determine these folders in a UWP app.)

Here’s an example:

string DocFolder = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments);
string MyFile = "MyTestFile.txt";
File.WriteAllText(Path.Combine(DocFolder, MyFile), "This is my test data...");

The above code-snippet will result in the file being written where a user expects it to be written.

There are a number of these SpecialFolders, such as:


You can explore them yourself using Intellisense to see what else there is.

Another handy File/Directory trick to have up your sleeve, is the use of the Path.GetTempPath() and Path.GetTempFileName() methods.  There is typically a Temp directory in the User’s local AppData folder, so again it’s very important to use Path.GetTempPath() if you intend to write/read a file to this Temp directory.

The Path.GetTempFileName() method is great if you don’t really care about the name of the file you want to write, if it truly is a quickie temp file that you will use right away and then delete. What the GetTempFileName() method does is create a zero-byte file for you and returns the path to it, including the randomly-generated name it used. Here’s an example of how you might use it:

string temp = Path.GetTempFileName();
File.WriteAllText(temp, "This is data in a temporary file!");
// Then do something with that file, whatever your temporary needs are

One last thing that I should mention. I have not found a way to access the Downloads folder using the Environment.SpecialFolders enum nor any other built-in C# methods. A fellow named Ray Koopa has a Code Project article in which he has written a wrapper class to access all the Special Folders, Downloads included. He also has created a NuGet package for his wrapper. You can find it all here: 

Happy Coding! =0)

Tuesday, November 29, 2016

Microsoft MVP Summit 2016

I've been a Microsoft MVP since 2003. I still consider myself basically a C# MVP, but Microsoft has rolled up a lot of developer categories under one umbrella now: Visual Studio and Development Technologies. As you may know, being a Microsoft MVP means helping the community, in my case, the .NET community of developers. I do this with my blog posts and the many questions that I try to answer on the MSDN forums:

Many of my readers have probably heard of the annual Summit in which a few thousand MVPs from around the world descend on the Microsoft campus for 4-5 days of geekiness. I have attended every Summit since I've been an MVP. This year's Summit was interesting though ... since Microsoft has gone Open Source with most of .NET's source code, not everything at the Summit this year was under NDA (Non-Disclosure Agreement). It was hard to keep track of what was and what wasn't, so I decided it was easier just to not talk about anything when I got back.

But we *were* told this: that a lot of what we were hearing about at the Summit would become public the following week at the Connect conference in NYC. And so it did ... the conference was streamed live at the time, but the videos are available at ... watch and be amazed!

On the last day that I was at the Summit, I participated in the MVP Hackathon, where we get to write code and explore new features alongside Microsoft engineers. Kinda geeky and fun!  Here's a public link about the event:  I'm not in the group picture that was taken at the end of the day because I had to leave after lunch (pizza!) to drive home. It's not a long drive (100 miles), but I wanted to get home before dark (and the sun sets early this time of year) ... I really don't like to drive at night. During the Hackathon, I was learning how to write extensions to Visual Studio ... not a new thing, but something that I hadn't done before, so it was new to me. The other people in my group doing extensions wrote some awesome stuff: Nico Vermier, David Gardiner, Cecilia Wiren and Terje Sandstrom. Their stuff is all on GitHub, so take a look at the Hackathon link above and check out some of the great extensions that they wrote. I never finished my extension ... because I had to leave early and didn't really have anything specific in mind that I wanted to accomplish, I just wanted to learn how to do it. I should have finished my experimentation when I got home, but life and work get in the way sometimes. ;)

Happy coding ...

Sunday, October 09, 2016

OneDrive & Group Policy

This is another departure from my normal blog posts (which are usually about .NET and/or C#), but I had a problem with OneDrive last week and I found a way to fix it, which I will share with you.

My “old” computer is a Dell Venue 11 Pro running Windows 8.1. It’s been rather buggy since I bought the thing several years ago (especially the docking station which needs frequent “re-boots”), so I had attributed my issues to this inherent problem … but I was wrong. 

I noticed last week that OneDrive wasn’t updating and in fact, the little Cloud icon wasn’t even appearing in my system tray. OneDrive hadn’t been running in days and I never noticed it (the Venue isn’t my main computer anymore, as I now have a Surface Pro 4 running Windows 10 … which was having no problems at all with OneDrive). I figured that all I had to do was reboot and all would be fine. It wasn’t. When I rebooted, I noticed this message appear:

I really didn’t know if this had anything to do with my problem with OneDrive, but it definitely did *not* look like a good thing. So, I took a look at the Event Log and all it said was:

The Group Policy Client service failed to start due to the following error:
The service did not respond to the start or control request in a timely fashion.” 

I went to look at the Services (choose “Administrative Tools” under Control Panel, and then open “Computer Management” … click on Services under “Services and Applications”) and sure enough, the Group Policy Client service was not running.

Well, at least it gave me something else to Google. When I did that, I saw other people mentioning this error in respect to OneDrive … or actually, to SkyDrive (the precursor to OneDrive). As it turns out, when I bought my Venue, OneDrive hadn’t come out yet … so I had SkyDrive. When OneDrive arrived (with a Windows Update I assume, I don’t remember), it automatically linked my existing SkyDrive to the new OneDrive … however, my SkyDrive folders still “lived” in my User folder (C:\Users\Bonnie\SkyDrive). And there was the reason I was having a problem … my “standard” User folder was not accessible due to this Group Policy Service not starting.

So, I found this YouTube video by Eugene Robertus who told of a way to fix it by making some changes to the Registry. It’s a long video but I watched it all and, as it turns out, the thing that Eugene says to check for was OK in my Registry.

Go to the Registry key \HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\SvcHost. In the right panel, double-click the “netsvcs” entry to bring up the Edit Multi-String dialog box. Note that I already have an entry in that list for “gpsvc”, which is the Group Policy Service. So why wasn’t that Service running?

… so, back to the drawing board (back to Googling, actually).I found a blog post by a guy who had the exact same problem (everything looked fine in the Registry, GP Service still not running): Group Policy Client Services Error and this guy had the solution. Apparently, it’s possible there’s a blank line in that list in the Registry that’s causing a problem. He said that he went to the bottom of the list and noticed that the cursor was on a new line after the last entry. Just backspace so that the cursor is after the last character of the last entry, click OK. You’ll get a “Warning” message about “empty strings” and it says the Registry Editor will remove all empty strings it finds in that entry. Click OK again, it gets fixed and you’re done. Reboot and voila! No more error message about the Group Policy Service!

And guess what … my OneDrive started working too!

And just for the heck of it, I thought I’d check it out on my Surface Pro 4 too … I didn’t have a problem with OneDrive, but I did notice that the Group Policy Service wasn’t running (apparently though, it doesn’t always run … it’s a triggered service), but I had the same issue with the Registry entry (empty strings) so that got fixed … who knows what else it might have affected.