Back to Basics

Today I figured I go back to basics with this blog since a majority of the visitors to this site are primarily DBA's and Database Guru's. This may turn into a series but for now we will discuss a bit of a feared power of sql server, and that is the fact that sql server in fact sits on top of the .net framework. This allows it to access its immense power and array of options that one just couldn’t do efficiently using traditional tools, in both the BI Stack and SQL’s T-SQL function library. This power comes from the fact that you can integrate SQL’s transactional ability with traditional programming’s ability of data manipulation. These tools are a powerful allies in the battle of efficient data conversion, transfer, and storage.

Today I will be using pseudo code, but the fact that it appears and will compile as C# is merely coincidently (stop whining VB people, C is cooler we have semi-colons).  Let’s begin with one of the most important fundamental component, variables.

You SQL People will be Familiar with this:

DECLARE @VAR INT

DECLARE @VAR INT = 0

Programmatic version would look like this:

int var;

int var2 = 0;

Okay so nothing to scary there I would say, In fact there isn’t too many fundamental differences between the concept involved with T-SQL and a programming language. The differences are merely in their approach. Lets continue.

So now we have a variable let’s do something with it. Now here is where the difference begins to show, programming languages power is concentrated in data manipulation so let try some. So we have this table in the computer’s memory, the ins and outs of that will be discussed another time, but .net handles most of the details.   

Datatable dt = GetTable(); 

So we are asking to create a Datatable, and filling it from the source GetTable(). Don’t worry about what GetTable() is, I just made it up, it would be like a Stored Procedure call in T-SQL providing data to our table. Now let’s add a column to this table since we don’t like the way this table like it is, perhaps it wronged us in the past due to its current table structure. or...I digress.

dt.Columns.Add("Apples");

Okay, so we just made a U-turn in the land of SQL type thinking. Or little datatable is more than just a table like what we think of in SQL, It’s a group of ideas that gives a place for both the data, and the things that you can do to the data.  Like adding a column, or retrieving what’s inside. These abilities are separated by dots which act much like Namespaces in SQL,  For instance, we have  DT the datatable, we say dt.coulmns to narrow it down to the column objects, then say add, and pass in the name of the column we want to create and do the creating.  Kind of like like passing int information to a stored procedure so it can receive external information. We call these stored procedures like things methods, and datatable like objects Classes (most of the time, but we will get to that later)

More to come in the next episode!