A blog about one man's journey through code… and some pictures of the Peak District

Monthly Archives: July 2016

I’ve recently started investigating the use of SQLite for the purpose of storing data in my UWP application. Before I start, the following are the best tutorials that I could find on the net to get started with SQLite:

Finally, the nuget package to integrate SQLIte with your UWP app is here.

I didn’t find too much information on checking if a table exists, and I think it’s quite a key thing to be able to do, as you can’t really have a deployment script that runs when you deploy your UWP app. As a result I create a base data access class. Let’s start with the interface:

That’s it; I’m still not sure how I feel about SQLite. For a small, local storage, it feels like it could be an XML file; but I suppose it comes into its own when we have a potentially large local storage.

I used to believe that Nunit’s TestCase test (that is, an ability to define a test and then simply pass it alternate parameters) was denied MSTest users. It appears that this is, at least now, fallacious.

Given the constant problems that I have with finding the correct NUnit test adaptor, and trying to work out which are the right libraries, I think, despite coming late to this party, MS might actually drag people back to MSTest with this.

Templates in SSMS work really well; if you select to create a new stored procedure, you’ll get a skeleton procedure, and then placeholders, which you can populate by pressing Ctrl-Shift-M

But how can we replicate this same behaviour in a VS DB project?

Out of the box, creating a stored procedure will give you this:

The key to changing the above template is to create your own template. The templates in VS are stored wherever you tell them to be:

In my case:

C:\Users\Paul\Documents\Visual Studio 14\Templates\ItemTemplates

Export Template

So, how to create a custom template for, say, a stored procedure? First, create your template. Typically, this will be from an existing VS template:

Now, make your changes:

And then, export the file:

File -> Export Template

You’ll then be asked which item in your project to export:

It treats DB templates like any other, so the next question relates to dependencies. Finally, it asks what you would like to call your template:

If you select to automatically import into VS, then after restarting VS, you should see your new template:

Using Common Templates with Source Control

You obviously can mail this around to the team, and they can all import it. However, another option is that you can source control it.

If you have a look at the directory above, you’ll see that it has created a file there:

Like any other file, you can simply add this to source control. If you do, then you can change the template directories described above to point at the source control folder. That way, everyone on the team can point to up to date source controlled templates.

Notes

Everything in this article applies equally well to other files – it’s just that it is very well documented for this, but not so well for SQL DB projects.

I recently came across a little known feature of SSMS that made me think that, for small amounts of data, it may be possible to create a procedure to insert test data into a table. Let’s start with the table:

Randomising a date is relatively easy, using a combination of the RAND() function and the DATEADD() functions, you can do this in a single line. However, randomising text is more complex. If you just want random strings of letters, then you could try something like this. However, if you want something more sensible, then you can use a lookup table:

I learned about these two functions accidentally last year, and I wanted to try them both out to see what they do. A long time ago, I used to be a C programmer, so the concepts of the two functions are familiar to me (assuming they are similar to their C cousins).

xp_sprintf

This is kind of like the C function sprintf… except it isn’t. Firstly, it only supports the %s placeholder. This means that you can’t represent, or format a float, like you can in C. Secondly, it looks like it only works with strings of 255 characters or less. Here’s an example:

It makes a bit more sense. However, there are a number of restrictions with the procedure. There is the 255 characters, you can only substitute strings and, because it’s a procedure, you can’t include it in a query.

A better way: FORMATMESSSAGE

FORMATMESSAGE is a function, but it supports all the standard placeholders:

This looks like a far more useful function; as it allows parsing of a string. Whether or not you believe you should be storing your data in a manner that requires parsing, one day, you will need to do it. Consider this table:

The eagle eyed amongst you might notice a slight issue here – because it’s a procedure, it can’t be used inside a query. It’s also hampered by the same restrictions of 255 characters, etc… I couldn’t find an xp_scanf equivalent of FORMATMESSAGE, so I rolled my own: