SQL SERVER - Stored Procedure sp_datatype_info to Get Supported Data Types

Sometimes you need to know more information about various data types supports by your SQL Server version. Information like if the specific data type is searchable or not, or it’s maximum precision, or if it’s case sensitive or not. sp_datatype_info system SP comes handy to retrieve such information.

1
2
3
4
5
USE master;
GO

EXEC sp_datatype_info;
GO
sp_datatype_info

ZarahDB - JSON based flat file database

The relational database management system is the de facto choice for any standard application to persist data on the disk. It provides all interfaces one requires to store, optimize, and query data for the client. But sometimes persisting data requirement for the application is so basic that we don’t need whole RDBMS engine on the backend to store data - storing in some form of flat file is sufficient enough to serve application data persistence need.

Creating such a backend layer to store data as flat files and querying data from these files can be a bit tedious which can become a project in itself. But there are many open source wrappers already around which can help you to kickstart building such data access layer. ZarahDB is one such open-source project.

ZarahDB is a flat file database engine which uses the combination of the root folder (per database), child folders (per table) and JSON files (per record) to persist data. Storing data as JSON files makes ZarahDB more structured and reliable compare to others. With that, storing data as files make it easy to backup or restore as it become just a matter of copying and pasting application data folder. As with other flat file based database platforms, you don’t need to install any database software on your server.

Project URL: http://zarahdb.com/
NuGet URL: https://www.nuget.org/packages/ZarahDB.Library
Source Code: https://github.com/MikeReedKS/ZarahDB

Using ZarahDB library in your project is easy - Simply add ZarahDB.Library through NuGet and your are ready.

ZarahDB - Nuget

ZarahDB - Nuget

Enough talk, let’s create a very basic .NET Core console application which will store and retrieve an employee information.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
using System;
using ZarahDB_Library;

namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {
            var instance = new Uri(AppDomain.CurrentDomain.BaseDirectory + "Data");
            var table = "Employees";

            // Save an employee to the database
            var key = "0001";
            ZarahDB.Put(instance, table, key, "Name", "Mike Smith");
            ZarahDB.Put(instance, table, key, "City", "Chicago");

            // Retrive employee from the database
            var name = ZarahDB.Get(instance, table, key, "Name").Value;
            var city = ZarahDB.Get(instance, table, key, "City").Value;

            Console.WriteLine("Name: " + name);
            Console.WriteLine("City: " + city);

            Console.ReadKey();
        }
    }
}

JSON stored in the Employees data folder (Location: Data\Employees\0\0\0\1\0001.json) will look like following:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
{
  "Keys": [
    {
      "Key": "0001",
      "ColumnValues": [
        {
          "Column": "Name",
          "Value": "Mike Smith",
          "PreviousValue": "",
          "Updated": "636935583251802007"
        },
        {
          "Column": "City",
          "Value": "Chicago",
          "PreviousValue": "",
          "Updated": "636935583256218064"
        }
      ]
    }
  ]
}
ZarahDB - Output

ZarahDB - Output

As you probably noticed, ZarahDB stores data values as string, which means you will require to explicitely convert non-string data type or object type as string before passing it to ZarahDB method to save it. This goes other way around as well: you will retrive stored data as string from ZarahDB, you will then require to convert string value to column data type.

Here is the example which explains how DateTime data value to stored and retrived in ZarahDB.

1
2
3
4
5
// Convert non-string data type value to string
ZarahDB.Put(instance, table, key, "BirthDate", (new DateTime(1998, 09, 12)).ToString());

// Convert string to column specifc data type
var birthDate = Convert.ToDateTime(ZarahDB.Get(instance, table, key, "BirthDate").Value);

To store complex object using ZarahDB, you will require to serialize it to Json or Xml string. And as you guessed, you will then require to deserialize returned Json or Xml string to object type.

To conclude this brief overview of ZarahDB, following is the code snippet which demonstrates taking a complex object like a blog post and store and retrive using ZarahDB through Json serialization.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
using Newtonsoft.Json;
using System;
using ZarahDB_Library;

namespace BlogEngine
{
    internal class Post
    {
        public string Title { get; set; }
        public string Content { get; set; }
        public string Author { get; set; }
        public string Category { get; set; }
        public DateTime Publish { get; set; }
    }

    class Program
    {
        static void Main(string[] args)
        {
            var instance = new Uri(AppDomain.CurrentDomain.BaseDirectory + "Data");
            var table = "Posts";

            string key = "quick-start-tutorial";
            var newPost = new Post
            {
                Title = "Quick Start Tutorial",
                Content = "The basic concept of a scattered database is ...",
                Author = "Mike Reed",
                Category = "Database",
                Publish = DateTime.Now
            };
            var jsonPost = JsonConvert.SerializeObject(newPost);

            // Save blog post to the database
            ZarahDB.Put(instance, table, key, "PostData", jsonPost);

            // Retrive blog post from the database
            var resultJson = ZarahDB.Get(instance, table, key, "PostData").Value;
            var resultPost = JsonConvert.DeserializeObject<Post>(resultJson);

            Console.WriteLine("Title: " + resultPost.Title);
            Console.WriteLine("Content: " + resultPost.Content);
            Console.WriteLine("Author: " + resultPost.Author);
            Console.WriteLine("Category: " + resultPost.Category);
            Console.WriteLine("Publish: " + resultPost.Publish.ToString("MM/dd/yyyy"));

            Console.ReadKey();
        }
    }
}
ZarahDB - Blog Example

ZarahDB - Blog Example

As I mentioned in the early part of this article, ZarahDB is great if you have a basic data persistence requirement but surely it’s not recommended for the more complex or data-heavy requirement. I hope you will find this short overview helpful.

ASP.NET 2.0, 1 Database Server, 1 IIS 6.0 Web Server and 32,000 Concurrent Connections!

Markus Frind has posted his experience of using ASP.NET 2.0 for one of largest dating site PlentyOfFish.com in Canada. In his post, Markus is telling how he has addressed concurrency issues by switching to ASP.NET 2.0:

At this point there were a lot of concurrency issues, the site would slooow down and just keep getting slower as more people came on. After switching to asp.net 2.0 all those problems went away. Now when there are over 32,000 concurrent connections at peak the site does not slow down. There are however still problems. I find that once you pass 100 Pageviews/second threads timers start to not get called for no reason.

Link:
Microsoft ASP.NET 2.0 Performance

He Interestingly didn’t use any kind of caching to render pages. Every page call actually pulls content from the database.