Thursday, August 27, 2015

Windows 10 Is Here! Let’s Develop Some Apps!

Now that Windows 10 has launched and the latest Visual Studio 2015 has been released, are you ready to start taking advantage of the latest products and technology for writing some cool Apps? Here are some resources to help you get started:

This free course from MVA (Microsoft Virtual Academy) is aimed more at IT Professionals than for Developers, but some of you may be interested in this as well: https://www.microsoftvirtualacademy.com/en-US/training-courses/getting-started-with-windows-10-for-it-professionals-10629/?Wt.mc_ic=dx_MVP4024627

And, of course, there’s always Channel 9: https://channel9.msdn.com/windows/?Wt.mc_ic=dx_MVP4024627

Developers, Developers, Developers!!!   ;0)

Friday, July 31, 2015

SQL Server - Chunking Large Deletes

I’ve gotten a bit tired of one of our implementation guys always asking what to do about the database log file getting full and failing because of it. This happens occasionally during the nightly purge job, which deletes a day’s worth of old rows of data that we don’t need (usually a day from a week to 30 days ago, configurable as a parameter to the Stored Procedure).

Normally this is not a problem … our databases are set up with Simple Recovery, so the log file gets re-used and will usually not out-grow it’s allocated size. The only time it became a problem is on heavy-use days when the deletes were using up more than the log file’s allocated size. Another thing that occasionally caused it to happen is if the nightly purge didn’t run for some reason and then the next night, there would likely be twice as many rows of data to delete and then it became an endless cycle of failures … especially when the implementation guys don’t notice it happening and a week goes by without anything getting purged!!! Arrgghh!

Well, there is a solution to the problem and that is to chunk the deletes into more byte-size pieces (pun intended!) … in other words, “bulk” delete a smaller subset of data within a transaction, and continue in a loop until the entire set of data has all been deleted. Here’s the beauty of this methodology: with a Simple Recovery database, all you have to do is issue the CHECKPOINT command after the COMMIT TRANSACTION, and the log file use gets set back to the beginning of the log file. This can also be done with the Full Recovery database, but you issue a BACKUP LOG command instead.

Here’s a sample of the T-SQL that you’d put into your Stored Procedure. Note that I’m deleting all rows earlier than a specified date (and with a nightly job, that amounts to deleting one day’s worth of data).

-- @delay is passed into the SP as a parameter
IF @delay > 0
SET @delay = -@delay

IF @delay < 0
BEGIN
DECLARE @time datetime
DECLARE @rc INT

SET @time = DATEADD(day, @delay, GETDATE())

-- delete from message
SET @rc = 1
WHILE @rc > 0
BEGIN
BEGIN TRANSACTION
DELETE TOP (100000) message WHERE saveddatetime < @time
SET @rc = @@ROWCOUNT
COMMIT TRANSACTION
CHECKPOINT
END

-- I have a bunch more deletes from other tables, each in the same format
SET @rc = 1
-- etc...
END

I didn’t figure this stuff out by myself. I found a really great post by Aaron Bertrand here: http://sqlperformance.com/2013/03/io-subsystem/chunk-deletes He posts a few nice charts comparing various combinations.

After reading that post, you might notice that the time it takes to perform these DELETEs could take a little longer using this method, but if all you care about is the size of the log file, it might not matter to you. One way to speed it up is to disable the indexes, do the deletes, enable the indexes. This is mentioned in the following blog post: http://shades-of-orange.com/post/2014/05/28/Delete-a-Large-Number-of-Rows-from-a-Table-in-SQL-Server  The only caveat that I can think of when messing with the indexes is that, depending on how many indexes you have to enable after you’re done, it may take a bit of time to re-index the data. In the blog post, the writer says it took an extra minute, but YMMV. I opted not to mess with the indexes … for me, it was all about the log file size, not the time it was taking.

Tuesday, June 30, 2015

DataAccess - Revisiting Yet Again

Almost 6 years ago, I wrote a 3-part series of blog posts about DataAccess. You can find them here: Part I, Part II and Part III.  These posts are still relevant, even though they are old. But, I’ve been meaning to add two things to the BBDataAccess class that I posted in Part III and I just keep forgetting to do it. Well, today is the day …

Implementing IDisposable

First, I wanted to make the class implement IDisposable. Here’s the addition that needs to be added to the BBDataAccess class:

public class BBDataAccess : IDisposable
{
// declarations & methods from Part III's previously published example
// ...
// ...

#region IDisposable Members

public void Dispose()
{
if (this.oConnection != null)
{
if (this.oConnection.State != ConnectionState.Closed)
this.oConnection.Close();
this.oConnection.Dispose();
}
}

#endregion
}

When calling methods on this class before we implemented IDisposable, you would have done something like this:

// Without IDisposable
CustomerDataAccess da = new CustomerDataAccess();
CustomerDataSet ds = da.GetCustomer(1);
// rest of your code

Now that you’ve implemented IDisposable, you’d call it like this:

// When the base BBDataAccess class implements IDisposable
using (CustomerDataAccess da = new CustomerDataAccess())
{
CustomerDataSet ds = da.GetCustomer(1);
// rest of your code
}

Transactions via TransactionScope

I also wanted to introduce Transactions using TransactionScope. You can make many things transactional, not just database access, so TransactionScope can be quite useful. Before we begin, take a look at my blog post about TransactionScope.

First, let’s just show you an example of how you might use TransactionScope in your calling code:

// The Utils class is from my TransactionScope blog post ...
// you *did* read it already, didn't you? ;0)
bool IsOK = false;
using (TransactionScope scope = Utils.GetTransactionScope())
{
using (CustomerDataAccess da = new CustomerDataAccess())
{
CustomerDataSet ds = da.GetCustomer(1);

// Do some stuff with the CustomerDataSet.
// Then call a method to create an Order.
// And then call another method that creates an Invoice.
// Then let's get the changes and save everything.

CustomerDataSet dsChanged = (CustomerDataSet)ds.GetChanges();
CustomerDataSet dsDeleted = (CustomerDataSet)ds.GetChanges(DataRowState.Deleted);

IsOK = da.SaveCustomerOrder(dsChanged, dsDeleted);
if (IsOK)
IsOK = da.SaveCustomerInvoice(dsChanged, dsDeleted);

if (IsOK)
scope.Complete();
}
}

We could also add TransactionScope to the CustomerDataAccess and/or to the base BBDataAccess class from Part III. If we did that, you would end up with nested Transactions, each one enlisting in the previous Transaction. This is usually a good thing.

For the CustomerDataAccess class, you could add the TransactionScope in each method like this:

public bool SaveCustomerOrder(CustomerDataSet dsChanged, CustomerDataSet dsDeleted)
{
this.DoIt = delegate()
{
// To use TransactionScope be sure to add the System.Transactions namespace
// If there is already a current ("ambient") TransactionScope, this one
// will enlist in the same transaction
using (TransactionScope scope = Utils.GetTransactionScope())
{
this.SaveTable(dsChanged.Customer, "csp_CustomerPut");
this.SaveTable(dsChanged.Orders, "csp_OrderPut");

if (dsDeleted != null)
{
this.SaveTable(dsDeleted.Orders, "csp_OrderDelete");
this.SaveTable(dsDeleted.Customer, "csp_CustomerDelete");
}

scope.Complete();
}
};
this.UndoIt = delegate()
{
dsChanged.RejectChanges();
dsDeleted.RejectChanges();
};

return this.Start();
}

You could also utilize TransactionScope in the Start method of the base class, like this:

public virtual bool Start()
{
bool success = false;

// Enter the retry loop
while (!success)
{
try
{
// Start the transaction and execute the methods. Any failures from here
// to the Complete() may result in an exception being thrown and caught which
// may lead to a retry or an abort depending on the type of error
using (TransactionScope scope = Utils.GetTransactionScope())
{
this.m_DoIt();

success = true;

scope.Complete();
}
}
catch (Exception e)
{
// Typically we'll want a Retry if the database error was the
// result of a deadlock, but you can Retry for any reason you wish.
if (this.RetryRequested(e) && this.attempt < this.maxRetries)
{
// Try to undo any changes before we retry
// If we can't, just abort
if (!this.UndoChanges())
{
this.AddToErrorMessage(e);
this.ForceCleanup();
break;
}

this.attempt++;

// Might want to sleep for random period
//System.Random rand = new System.Random();
//System.Threading.Thread.Sleep(rand.Next(MinSleepTime, MaxSleepTime));
}
else
{
// Max number of retries exceeded or an unrecoverable error
// - fail and return error message
this.AddToErrorMessage(e);
this.ForceCleanup();
break;
}
}
}

return (success);
}


That’s it for now. I hope this all makes sense to you, dear Reader. If not, please don’t hesitate to ask questions in the comments!

Saturday, May 30, 2015

A FIX For Corrupted Office Files

This will not be a “normal” post for me. Typically, I’m all about C# and .NET stuff. This post will be a bit different, although somewhat related … at least it starts with a developer-type problem with corrupted database files. Specifically, I’m talking about Access database files. I personally do not use Access (I’m a SQL Server gal), but a friend of mine told me about a problem he had with a corrupted Access database file and how he ultimately managed to get it fixed.

He had tried the recommended Access “Compact and Repair”, which can be run from the Access menu (see this link for a summary of how to run this, depending on your version of Access: Compact and Repair Database by Access Version ). Unfortunately, that didn’t fix his problem.

He ended up using this tool,Cimaware's AccessFIX , and it saved the day! He had only good things to say about it. It's not a free utility, but apparently you can try it before you buy it to see if it will work for you (with the trial/demo version you can't save the recovered file). If it appears that it's going to work OK for you, then you can buy the full version.

So, I looked into it further and discovered that Cimaware has other FIX software besides AccessFIX. You can buy it piecemeal, individual FIXes for the other Office files that you may have corruption issues with:

ExcelFIX 
OutlookFIX 
WordFIX

Or, you can get all the tools together in one package: OfficeFIX. Quite handy if you will possibly need more than one of the FIX utilities because the cost of OfficeFix is less than the cost of two of the FIX utilities!

Oh, there’s one more that’s not related to corrupted Office files, but could also come in handy if you’re a digital photographer (which I dabble in myself). It’s called DeleteFIX Photo. I haven’t ever had problems deleting files from my camera, but apparently it can happen. You know you deleted the files, but they still show up in your camera. Might be worth trying this utility if it happens to you.

Saturday, April 25, 2015

Communicating With a Windows Service

As most of you are aware, a Windows Service cannot have a GUI (Graphical User Interface) … you can’t run Forms in the Service, you can’t popup a Dialog box and you can’t get input via a Console.ReadLine()!. If you did not previously know that about Windows Services , well … now you do!

There was an interesting discussion about this last month on the MSDN forums. See the thread here:

https://social.msdn.microsoft.com/Forums/en-US/d9917807-2530-4ec1-a40b-edcc564bb726/service-app?forum=csharpgeneral

In that thread, I went back-and-forth with a fellow named Andrew (who has since changed his user name to “Content Removed”). The end result was possibly a misunderstanding between the use of the acronyms GUI and UI, because user interaction *can* be achieved without a GUI. A Service can certainly communicate with other applications (GUI or not) via methodologies such as MSMQ or writing to / reading from TCP/IP, files or databases.

What I didn’t previously know about, and I thank Andrew for showing how to do it, was the ability to communicate with a Service via something called a “Custom Command”. The only values for a Custom Command that you can use are those between 128 and 255. Integers below 128 correspond to system-reserved values. I, personally, have not had the need to do this, but it is an interesting concept to keep in one’s “bag of tricks”. The rest of my blog post will show you how to do this. But first, let me point you to a previous blog post of mine that shows how easy it is to create Windows Services: http://geek-goddess-bonnie.blogspot.com/2013/10/easy-windows-services.html

In my blog post linked to above, you can see that I have a service host, called MyServiceHost, that inherits from System.ServiceProcess.ServiceBase. This is where you would override the OnStart() and OnStop() base methods to do stuff before and/or after starting/stopping your Services (you can “host” multiple Services here if you want to). The way to utiilze the interaction that Andrew was talking about, is to override a method called OnCustomCommand(), which takes an integer parameter (as mentioned above).

There are two parts to implementing this functionality. First, you have to add code to your MyServiceHost class. Something like this:

// You'll want to use enums for your commands, it's more readable
// Custom commands can be any value from 128 to 255
public enum ServiceCommands
{
DoSomething = 128,
DoThingTwo = 129,
// etc.
}

protected override void OnCustomCommand(int command)
{
// always run the base!!
base.OnCustomCommand(command);

if (command >= 128)
{
switch ((ServiceCommands)command)
{
case ServiceCommands.DoSomething :
// code to do something
break;
case ServiceCommands.DoThingTwo:
// code to do something else
break;
default:
// code, if you want, to indicate an invalid value
break;
}
}
}


So, that’s pretty easy, right? Next, you’ll need code to actually send those commands to your Windows Service. This code can be anywhere … it can be in a GUI application, another Windows Service, a Console app … it doesn’t matter where. All you need to know is the name of the Service (and, I believe the application needs to be running with Administrator rights). Here’s some sample code run from a Windows Form:

// Be sure you have referenced the System.ServiceProcess
// and be sure you have a using for it
using System.ServiceProcess;

// In your application, this is all you need to send commands
// to your Windows Service.
// My Service was installed as My.MessagingService
using (ServiceController srvController = new ServiceController("My.MessagingService"))
{
if (srvController.Status.Equals(ServiceControllerStatus.Running))
srvController.ExecuteCommand((int)ServiceCommands.DoThingTwo);
else
System.Windows.Forms.MessageBox.Show(@"The service is not running.");
}


That’s all there is to it. Happy coding!

Tuesday, March 31, 2015

Gotcha With Dictionary Copies

Last week I found a little “gotcha” when I had the need to make a copy of an existing Dictionary object. The Dictionary class has a constructor that takes an existing Dictionary object and makes a copy of it to a new object (https://msdn.microsoft.com/en-us/library/et0ke8sz(v=vs.110).aspx), so I thought that was the perfect way to handle this. Unfortunately, I was wrong.

Here’s the scenario. I am listening to a TCP port, and storing the data as it comes in into a Dictionary<string, MyData>, either adding new items or updating existing ones. Since incoming data can arrive at a pretty fast rate, I didn’t want to process it as it came in, so at 1 minute intervals, I copy the Dictionary and process the copy, and the TCP Listener can go on grabbing data and putting it into the original Dictionary. We can test this with something like this:

// First, here's the data class
private class MyData
{
public int MyInt { get; set; }
public string MyString { get; set; }

public MyData(int start)
{
this.MyInt = start;
this.MyString = "Initialize ...";
}
public override string ToString()
{
return string.Format("{0}: {1}", this.MyString, this.MyInt);
}
}

// And a quick method to show the data:
private void PrintDictionary<T>(string message, Dictionary<string, T> test)
{
Console.WriteLine(message);
foreach (var key in test.Keys)
{
Console.WriteLine("{0}: {1}", key, test[key]);
}
}

// Now the testing begins
Dictionary<string, MyData> Orig = new Dictionary<string, MyData>();
Dictionary<string, MyData> Copy;

// put data in original
Orig.Add("a", new MyData(1));
Orig.Add("b", new MyData(2));
Orig.Add("c", new MyData(3));

// now let's make a copy
Copy = new Dictionary<string, MyData>(Orig);

this.PrintDictionary<MyData>("*** Original ***", Orig);
this.PrintDictionary<MyData>("*** Copy is the same ***", Copy);

Looks pretty simple, but unfortunately, that didn’t work as planned, because the Dictionary copy is actually a “shallow” copy instead of a “deep” copy. Nowhere in the documentation (link above) does it say anything about this being a shallow copy. Probably most of you know the difference between a shallow and a deep copy, but let me explain for those who might not.
  • A shallow copy will copy the original values to the copy.
    • If the values are references, only the references are copied.
    • If the values are primitive types, such as int or string, the actual values are copied.
  • A deep copy will copy the actual data to the copy.
    • If the values are references, the actual object is copied (a new instance, not just the reference to the existing object).
    • For primitive types, there’s no difference, since it copies the actual value of the object with either type of copy.

So what does this mean in plain English? For class types, such as the MyData class in the Dictionary I’ve defined in the above code snippet, a shallow copy means that Orig and Copy are both pointing to (referencing) the same data! If I make a change to an item in Orig, you’ll see the same change in Copy too. I needed a deep copy, meaning that changes to items in Orig did not affect the items in Copy.

Put this code after the above code snippet to see that the items in Orig do indeed point to the same items in Copy:

// now let's modify the original
MyData oldData = Orig["b"];
oldData.MyString = "Made a change";
oldData.MyInt = 42;

this.PrintDictionary<MyData>("*** Original After Modifying Existing Item ***", Orig);
this.PrintDictionary<MyData>("*** Copy changes ALSO!!! ***", Copy);

// Why did the copy change? Because when the original was copied,
// the copied class object did not actually get copied, only the
// reference (the pointer) to the class object was copied.
// So, when the original is changed, since the copy references the
// original, it appears to be changing too.

Once I discovered that the copy was not a deep copy, rather than research ways to change that behavior in the copy itself, for a quick fix I simply created a new MyData object when I needed to update an item in the Orig dictionary:

// I first got around this problem by creating a new class object instead of modifying existing
MyData newData = new MyData(666);
newData.MyString = "Brand New Instance";
Orig["b"] = newData;

this.PrintDictionary<MyData>("*** Original With New Item ***", Orig);
this.PrintDictionary<MyData>("*** Copy will NOT change ***", Copy);

I had needed a quick fix to my problem (I needed to get the code deployed), but I had some time to research this more while I was writing this blog post and found that there are a few other options. Here are some links to decent articles and other discussions:
 
 
I decided to make use of the ICloneable interface for MyData class and add an extension method for Dictionary. Here’s what I did:
 
// First, make MyData implement ICloneable
private class MyData : ICloneable
{
public int MyInt { get; set; }
public string MyString { get; set; }

public MyData(int start)
{
this.MyInt = start;
this.MyString = "Initialize ...";
}
// This protected constructor is just for the purpose of cloning/copying
protected MyData(MyData cloneFrom)
{
this.MyInt = cloneFrom.MyInt;
this.MyString = cloneFrom.MyString;
}
public override string ToString()
{
return string.Format("{0}: {1}", this.MyString, this.MyInt);
}

#region ICloneable Members

public object Clone()
{
// For classes that contain members that are more complex,
// utilize a protected constructor just for the purpose of cloning/copying
return new MyData(this);

// For this particular class, I could also have done this
// using MemberwiseClone, which does a shallow copy.
// Since my class has only primitive types, a shallow copy is fine.
//return this.MemberwiseClone();
}

#endregion
}

public static class Extensions
{
// An extension method for Dictionary. Notice that it is limited to TValue
// types that implement ICloneable.
public static Dictionary<TKey, TValue> Clone<TKey, TValue>(this Dictionary<TKey, TValue> source) where TValue : ICloneable
{
return source.ToDictionary(item => item.Key, item => (TValue)item.Value.Clone());
}
}

Now let’s test this using the following code:

Dictionary<string, MyData> Orig = new Dictionary<string, MyData>();
Dictionary<string, MyData> Copy;

// put data in original
Orig.Add("x", new MyData(1));
Orig.Add("y", new MyData(2));
Orig.Add("z", new MyData(3));

// now let's make a cloned copy, using the Dictionary Clone() extension method
Copy = Orig.Clone();

this.PrintDictionary<MyData>("*** Original ***", Orig);
this.PrintDictionary<MyData>("*** Copy created from Clone of Orig ***", Copy);

// now let's modify the original
MyData oldData = Orig["y"];
oldData.MyString = "Made a change";
oldData.MyInt = 42;

this.PrintDictionary<MyData>("*** Original After Modifying Existing Item ***", Orig);
this.PrintDictionary<MyData>("*** Copy will NOT change!!! ***", Copy);

Yay! It works! This is much “cleaner” than my quick-and-dirty original solution to the problem, which I’ve already deployed, so I’m not going to change it now. Next time I need to make a change to that particular bit of code, I think I’ll refactor it to make use of ICloneable. I like it.

Happy coding!

Saturday, February 28, 2015

Easy-Peasy Connection Strings

I frequently see questions on the Forums about what a developer’s database connection string should look like. The main site that everyone recommends for that is http://www.connectionstrings.com/ … this is a good site and usually very helpful. The only problem is that sometimes people still can’t figure out what their connection string ought to be. So, here’s another suggestion that’s pretty easy to do.

  • Create a new file text file (the easiest place to put that is on your desktop), and change the extension from .txt to .udl
  • Double-click the file and a “Data Link Properties” window opens. By default it opens to the second tab, the “Connection” tab. If you click on the Provider tab, you will see that it defaults to using the Provider for SQL Server. Change that if you’re not using SQL Server.
  • Back on the Connection tab:
    1. Select a server name from the dropdown list. If you don’t see your server there, click the Refresh button. If it’s still not there, then you’re not getting to it via your network and you won’t be able to test the connection. Optimally, it’s best to do this on a machine that has access to the machine that has the database server on it.
    2. Enter your login info, either Integrated security or username/password (check the “Allow saving password” box to save the password in the connection string … you’ll get a warning about saving the password to a file, but that’s OK) .
    3. Select the database.
    4. You can click the “Test Connection” button if you want to, but if you can see a list of databases in Step 3, then you’ve already got a valid connection.
  • You can still do the steps above (except for #4) without actual access to the database server by typing in the server name and database name without picking from a list, and the connection string will be valid (provided that you’ve hand-typed those values in correctly).
  • There are two other tabs on this window, usually you won’t need them.
    • The Advanced tab has a few more options that you usually won’t need to bother with, but take a look if you wish.
    • The All tab will show all the properties that can go into the connection string, the ones you just entered plus others. Make changes here if you wish (double-click an item to edit it), but it’s usually not necessary.
  • Now that you’re done, click OK to save it.
  • To get the actual connection string, right-click on the file, and “Open with” Notepad. Use the string shown (except that you don’t need the “Provider=XXX” part of it).

That’s it! It’s pretty painless …

Happy coding!  =0)