Monthly Archives: July 2016

Check table exists in SQLite (in a UWP app)

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:

http://blogs.u2u.be/diederik/post/2015/09/08/Using-SQLite-on-the-Universal-Windows-Platform.aspx

https://blogs.windows.com/buildingapps/2016/05/03/data-access-in-universal-windows-platform-uwp-apps/

The SQLite home page is here.

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:

public interface IDataAccess
{
    void Connect();
    bool DoesTableExist<T>() where T : class;
    bool RemoveTable<T>() where T : class;

    bool CreateTable<T>() where T : class;
}

Next, I created a unit test to make sure that the method will work:


    [TestClass]
    public class TestBaseMethods
    {       
        [DataTestMethod]
        [DataRow("MyApp.DataAccess.SQLLiteDataAccess, MyApp, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null")]
        public void TablesCreate(string dataAccess)
        {
            // Arrange            
            IDataAccess bda = Activator.CreateInstance(Type.GetType(dataAccess)) as IDataAccess;
            bda.Connect();

            Assert.AreEqual(false, bda.DoesTableExist<TestEntity>());

            // Act
            bda.CreateTable<TestEntity>();

            // Assert
            Assert.AreEqual(true, bda.DoesTableExist<TestEntity>());


            // Clean-up
            bda.RemoveTable<TestEntity>();
            Assert.AreEqual(false, bda.DoesTableExist<TestEntity>());
        }

If you want more information about DataRow, then see my recent post

Implementation

We’ll start with a basic class that provides a Connect method and cleans up after itself:

namespace MyApp.DataAccess
{
    public class SQLLiteDataAccess : IDisposable, IDataAccess
    {
        SQLiteConnection _connection;

        public void Connect()
        {
            _connection = new SQLiteConnection(new SQLitePlatformWinRT(),
                Path.Combine(ApplicationData.Current.LocalFolder.Path, "Storage.sqlite"));
        }

        public void Dispose()
        {
            _connection.Dispose();
        }

Then we need to add the functionality from the interface:


        /// <summary>
        /// Generic method to determine is an object type exists within the DB
        /// </summary>
        /// <param name="type"></param>
        /// <param name="name"></param>
        /// <returns></returns>
        private bool DoesTypeExist(string type, string name)
        {
            SQLiteCommand command = _connection.CreateCommand("SELECT COUNT(1) FROM SQLITE_MASTER WHERE TYPE = @TYPE AND NAME = @NAME");
            command.Bind("@TYPE", type);
            command.Bind("@NAME", name);
            
            int result = command.ExecuteScalar<int>();

            return (result > 0);
        }

        /// <summary>
        /// Check whether a specific table exists in the database
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <returns>true if the specified table exists</returns>
        public bool DoesTableExist<T>() where T : class
        {
            bool exists = DoesTypeExist("table", typeof(T).Name);
            return exists;
        }

        /// <summary>
        /// Create a new table in the DB
        /// </summary>
        /// <typeparam name="T">Object to base the table on</typeparam>
        /// <returns></returns>
        public bool CreateTable<T>() where T : class
        {
            bool exists = DoesTableExist<T>();

            if (!exists)
            {
                int error = _connection.CreateTable<T>();
                exists = (error == 0);
            }

            return exists;
        }

        /// <summary>
        /// Remove / drop the specified table from the DB
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <returns></returns>
        public bool RemoveTable<T>() where T : class
        {
            bool exists = DoesTableExist<T>();

            if (exists)
            {
                int error = _connection.DropTable<T>();
                exists = (error != 0);
            }

            return exists;
        }

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.

Using MSTest DataRow as a Substitute for NUnit TestCase

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.

The following article implies that this is a recent change:

Taking the MSTest Framework forward with “MSTest V2”

This particular example is in a UWP application:

        [DataTestMethod]
        [DataRow(1, 2, 3, 6)]
        [DataRow(8, 2, 3, 13)]
        [DataRow(8, 5, 3, 12)]
        public void AddNumbers(int num1, int num2, int num3, int total)
        {
            Assert.AreEqual(num1 + num2 + num3, total);
        }

Will result in a failing test, and:

        [DataTestMethod]
        [DataRow(1, 2, 3, 6)]
        [DataRow(8, 2, 3, 13)]
        [DataRow(8, 5, 3, 16)]
        public void AddNumbers(int num1, int num2, int num3, int total)
        {
            Assert.AreEqual(num1 + num2 + num3, total);
        }

Results in a passing one.

If you want additional information relating to the test, you can use this syntax:


        [DataTestMethod]
        [DataRow(1, 2, 3, 6, DisplayName = "First test")]
        [DataRow(8, 2, 3, 13, DisplayName = "Second test")]
        [DataRow(8, 5, 3, 15, DisplayName = "This will fail")]
        public void AddNumbers(int num1, int num2, int num3, int total)
        {
            Assert.AreEqual(num1 + num2 + num3, total);
        }

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.

Custom Templates for SQL Database Files

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

SQLTemplate1

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:

SQLTemplate2

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:

SQLTemplate3

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:

SQLTemplate4

Now, make your changes:

SQLTemplate5

And then, export the file:

File -> Export Template

SQLTemplate6

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

SQLTemplate7

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:

SQLTemplate8

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

SQLTemplate9

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:

SQLTemplate10

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.

SQL Server quick insert test data

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:

CREATE TABLE [dbo].[Audit](
	[AuditDate] [datetime] NOT NULL,
	[AuditText] [varchar](max) NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

So, let’s create a statement that will inset data into this table:

INSERT INTO DBO.[Audit]
(AuditDate, AuditText)
VALUES
(GETDATE(), 'The sky is blue')

GO

sql1

Okay, so here’s the thing; try this:

INSERT INTO DBO.[Audit]
(AuditDate, AuditText)
VALUES
(GETDATE(), 'The sky is blue')

GO 10

sql2

So, that’s good, but what if you want some actual kosher data?

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:

DECLARE @lookupTable TABLE (TextLookup VARCHAR(255))
DECLARE @lookupTableSize INT

INSERT INTO @lookupTable
(TextLookup)
VALUES
('red'), ('blue'), ('green'), ('orange'), ('purple')

SELECT @lookupTableSize = COUNT(1) FROM @lookupTable

INSERT INTO dbo.[Audit]
(AuditDate, AuditText)
VALUES
(DATEADD(day, -ABS(CHECKSUM(NewId())) % 30000, GETDATE()),
(SELECT TOP 1 TextLookup FROM (
    SELECT TextLookup, ROW_NUMBER() OVER (ORDER BY TextLookup) AS RowNum
    FROM @lookupTable
) AS DerivedAudit
WHERE DerivedAudit.RowNum >= (ABS(CONVERT(BIGINT,CONVERT(BINARY(8), NEWID()))) % @lookupTableSize) + 1))

GO 100


SELECT *
FROM dbo.Audit

It’s worth noting that GO in this case executes the entire script, not just the bit you want; however, I’m not proposing you use this for any serious volume – just for add a quick few records.

SQL Server xp_sprintf and xp_sscanf

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:

DECLARE @myString varchar (255)
EXEC xp_sprintf @myString OUTPUT, 
    'There are %s fields in the table %s', '3', 'TestTable'

SELECT @myString

sql1

Admittedly, that seems a bit pointless; but what if we do this:


DECLARE @colCount INT
DECLARE @colCountStr varchar(255)
DECLARE @myString varchar(255)
DECLARE @tableName varchar(255) 

set @tableName = 'Audit'

SELECT @colCount = COUNT(COLUMN_NAME) 
FROM INFORMATION_SCHEMA.COLUMNS WHERE 
TABLE_CATALOG = 'TestDB' AND TABLE_SCHEMA = 'dbo'
AND TABLE_NAME = @tableName

SELECT @colCountStr = CONVERT(varchar, @colCount)

EXEC xp_sprintf @myString OUTPUT, 
    'There are %s fields in the table %s', @colCountStr, @tableName

SELECT @myString

sql2

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:


DECLARE @colCount INT
DECLARE @tableName varchar(255) 

set @tableName = 'Audit'

SELECT FORMATMESSAGE('There are %d fields in the table %s', COUNT(COLUMN_NAME), @tableName) 
FROM INFORMATION_SCHEMA.COLUMNS WHERE 
TABLE_CATALOG = 'TestDB' AND TABLE_SCHEMA = 'dbo'
AND TABLE_NAME = @tableName

sql2

xp_sscanf

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:

scanf

So, I want to analyse this data; how about:

select *
from dbo.[Audit]

DECLARE @colour varchar(255)
DECLARE @text varchar(255)

SELECT @text = AuditText from dbo.[Audit]

EXEC xp_sscanf @text, 'The sky is %s today', @colour OUTPUT

select @colour

sql3

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:

CREATE FUNCTION ScanFWrapper 
(	
	@inputText varchar(255),
	@formatText varchar(255)
)
RETURNS varchar(255)
AS
BEGIN	
	DECLARE @Result varchar(255)

	EXEC xp_sscanf @inputText, @formatText, @Result OUTPUT
		
	RETURN @Result

END
GO

Admittedly, it’s not very generic, but you can call it like this:

SELECT dbo.ScanFWrapper(AuditText, 'The sky is %s today') 
FROM dbo.[Audit]

sql4

Conclusion

There are a number of xp_* methods, and they seem to be one-off procedures, so I’m probably being unfair on them in trying to compare them to their C equivalents.