Posts in Category: sql

Understanding SQL Grouping and Date Formats

No matter how many times you do it, you always find yourself having to go back and look up silly information on how to do things in something you haven’t done in awhile.

Case in point, I had to do some SQL magic on some data and had completely forgotten how to do proper groupings and summation of data by weeks and months – so here it is for all your future references.

Retrieving Results for a Month

This is easier than doing things by week and is easily done by using a derived table to get an initial result set and then using the outer query to present everything nicely.

 SELECT MONTH, COUNT(TOTAL) AS TOTAL, FORMAT(SUM(TOTAL),'C') AS SUM
FROM (
SELECT FORMAT(DO.SomeDateField,'MMM') + '-' + CAST(FORMAT(DATEADD(YEAR, 1, DA.SomeDateField), 'yy') AS VARCHAR(40)) AS MONTH,
MONTH(DA.SomeDateField) AS CALENDAR_MONTH_ORDER,
CAST(YEAR(DA.SomeDateField)															
Read More

Connecting to Azure Db via Code

Getting started with a new platform can be a little daunting, after all, there is so much to learn and where do you start.

From a client-side perspective, the only difference in connecting to an Azure Db over a server Db is the connection string.

string connectionstring = "Server=tcp:#SERVER#,1433;Initial Catalog=Signal;PersistSecurityInfo=False;User ID=#USERNAME#;Password=#PASSWORD#;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30";

SqlConnection cn = new SqlConnection();
cn.ConnectionString = connectionstring;

cn.Open();

From there, it’s a simple job of opening a connection, creating a command and querying your data.

The biggest problem I ran into was having to create the firewall rule to allow access (this will not be enabled by default).  Thankfully Visual Studio took care of this, giving me the prompt to do so and quickly enable.

firewall.pngRead More

Creating you First Azure Database

If you’re still creating databases locally for all your development and testing needs, it’s time to give yourself the kick you need to start doing things a little differently and learn something new in the process.

Going to Azure isn’t as complicated as you think (if you start small).  If you’re worried about costs, there are tons of credits floating around to make the learning cycle quick and painless.

First, setup a Resource Group, for a listing of all Azure related terminology, check here.  When you’re done doing this, navigate down to SQL Databases and create a database server (a URL) and whatever associated database you want to go along with it.

In my portal view, this looks a little like this where I now have a server and a database.

Read More

Accessing Data with LiteDb

In my last post I talked about how easy it was to create a database with LiteDb.

But accessing your data is even easier.

To start off, assuming we have no document (i.e., structure) associated with our data – we can create a generic table on the fly using the built in BSonDocument structure.

In this example, I first use my database to get a collection of documents under “FirstTable”.  If it’s your first time creating this document, don’t worry, it’ll be created for you, so no need to do any “CREATE” mumbo jumbo.

var FirstTable = database.GetCollection("FirstTable");

Now I’m going to create some data.  The simplest, most brain-dead way to do this is to create a Dictionary, make some columns and give them whatever values I want that are of type BsonValue

Dictionary<string, BsonValue> docs															
Read More

Getting Started with LiteDB

Every project you do will have data and eventually you’re going to need to store that data somewhere.

And config files are such a pain to manage (and also so early 2000s) so as much as possible I try to use a database or some unit of storage.

And like anything you build, once you start building it, it takes on a life of it’s own that you twist and turn and wrench into something useful until one day you look at it and go – maybe storing our configuration in a 25,000 line XML file wasn’t the best idea?

So if it’s going to be a bad idea down the road, why not start doing the right thing from the get go – enter LiteDb.

LiteDb is an incredibly easy to use,

Read More