The empty Guid

Posted on November 25, 2013 by Gunnar

This is a neat trick I had to google for quite some time to find.
Now I’m saving it here to never have to search for it again!

Occasionally I’ve had to create an empty Guid in T-SQL and to do so I’ve done weird things like writing a statement like this:

SELECT NEWID()

1	SELECT NEWID()

Copy/Paste the results into the editor and then replace every single letter with a zero.

There is, of course, an easier way to do this and it’s kinda like the Guid.Empty property you can use in C# and it goes something like this:

SELECT CAST(0x0 AS uniqueidentifier)

1	SELECT CAST(0x0 AS uniqueidentifier)

It’s that simple.

Creating A Calculated Column In MS SQL

Posted on November 20, 2012 by Gunnar

This is something I’ve been doing a lot lately.
Seem to find myself googling for the syntax every time I have to so this is a reference for me, mostly, but you can use it if you behave.

The T-SQL syntax for adding a calculated column to a table looks like this:

ALTER TABLE [<TableName>]
ADD <NewCalculatedColumnName> AS (<SQL for value>) PERSISTED

1 2	ALTER TABLE [<TableName>] ADD <NewCalculatedColumnName> AS (<SQL for value>) PERSISTED

A couple of notes:

<TableName> is the name of the table you’re adding a column to.
<NewCalculatedColumnName> is the name of the new column
<SQL for value> is a valid T-SQL statement.
PERSISTED – Specifies that the Database Engine will physically store the computed values in the table, and update the values when any other columns on which the computed column depends are updated.

Deleting projects from tfspreview

Posted on November 2, 2012 by Gunnar

Just a quick trick for me and those of you who have been playing around with the tfs preview.

The trick? How to delete a project from tfspreview (I gave it away at the title, didn’t I?)

Connecting to an Oracle Database (and doing an update on a table)

Posted on July 6, 2012 by Gunnar

Answered my first stackoverflow question the other day (and earned some rep doing so).

The question was on how to connect to an Oracle database and update a table and since I use Oracle next to never I had to go Google for myself.
Many resources later I found an answer to the question and it is as follows:

To connect to an Oracle database you’ll need an Oracle driver if you haven’t got one.
The only location I’ve found for Oracle driver downloads is this one:
http://www.oracle.com/technetwork/topics/dotnet/index-085163.html

Choose the 32 bit driver for the time being. 64 bit is probably awesome when you’re running things in production but design time tools aren’t available in the 64 bit download.
Click the topmost link in the list that appears. At the time of writing it looked like this:

That should bring you to a page looking somewhat like this:
And now you’ll need to register to be able to download the driver. Having downloaded and installed the driver (you’ll see there is a different link in the above screenshot to a ODAC Installation Instructions, use that if needed) you’re finally ready for some coding.

First thing to do is to actually add reference to the new ODAC driver. You do that by right-clicking the references folder and choosing “Add Reference…”.

That brings up a tabbed window and we’re interested in the leftmost tab, .NET.
Choose the oracle stuff and press OK:

Next add these lines to the list of includes

using Oracle.DataAccess;
using System.Data;

1 2	using Oracle.DataAccess; using System.Data;

Now we need to add a connection string to the class responsible for communicating with the Oracle database.

private const string connString = @"Data Source=TORCL;User Id=myUsername;Password=myPassword;";

1	private const string connString = @"Data Source=TORCL;User Id=myUsername;Password=myPassword;";

Examples of proper Oracle connection strings can be found at the URL below. I picked the topmost connection string for this demo:
http://www.connectionstrings.com/oracle

And now some coding

private static void DoOracleStuff( int param)
{
  OracleConnection conn = new OracleConnection(connString);
  try
  {
    conn.Open();
    OracleCommand command = conn.CreateCommand();
    command.CommandText = "UPDATE <TableName> SET Task_status = 'Assigned' WHERE task_id = :taskId";

    OracleParameter taskId = new OracleParameter();
    taskId.DbType = DbType.Int32;
    taskId.Value = param; // this is your variable, right?
    taskId.ParameterName = "taskId";

    command.Parameters.Add(taskId);

    command.ExecuteNonQuery();
    command.Dispose();
  }
  catch (Exception ex)
  {
  // Handle the exception     
  }
  finally
  {
    conn.Close();
  }
}

private static void DoOracleStuff( int param)

{

OracleConnection conn = new OracleConnection(connString);

try

{

conn.Open();

OracleCommand command = conn.CreateCommand();

command.CommandText = "UPDATE <TableName> SET Task_status = 'Assigned' WHERE task_id = :taskId";

OracleParameter taskId = new OracleParameter();

taskId.DbType = DbType.Int32;

taskId.Value = param; // this is your variable, right?

taskId.ParameterName = "taskId";

command.Parameters.Add(taskId);

command.ExecuteNonQuery();

command.Dispose();

}

catch (Exception ex)

{

// Handle the exception

}

finally

{

conn.Close();

}

And that is that.

Now you should be able to connect to an Oracle database and perform an update on one of its tables.

Performing a query on the same table is almost identical: switch the update statement out for a select statement and instead of using a ExecuteNonQuery() you should use a DataReader or a DataAdapter.

Just ask and I’ll show you how

TFS Preview

Posted on June 28, 2012 by Gunnar

For those of you who need or want a safe storage for your solutions/projects/code you might want to check TFS Preview out.

Granted that this is in preview (as the name so obviously implies) and the service might not be as feature rich when it gets released as it’s being presented now but it’s still a cool step for Microsoft.

Of course they’re meeting the competition from other vendors such as FogCreek and their Mercurial based Kiln and the stuff that Assembla offers but it’s still a cool.

I think I’ve said cool too many times now.

Having your code in the cloud also means that you can, theoretically, worry less about it getting lost due to a disk crash or worse things.

Another cool thing I recently read about is TFS Express.
Not cloud based but for a free product it seems pretty feature rich.
Of course both products are for hooking you into using Microsoft products and get you stuck with them but to be honest, you can be stuck with worse.
Oh and if MS isn’t using TFS Preview for usage analysis then they’re fools.

I’m currently trying out the TFS Preview although I haven’t given it the time I’d want to.
I’m running it along with Visual Studio 2010. It works when you’ve updated VS 2010 with the download they provide on the TFS Preview page.

It has a nice little web interface that I do hope they continue to upgrade.
Deleting projects, for instance, is something that you should be able to do.
Now I just have to think up a cool little project so I can source control like the wind.
IN THE CLOUD!!!

discuss!

The Funkalistic Blog

I'll get to that later…

Author Archives: Gunnar

The empty Guid

Creating A Calculated Column In MS SQL

Deleting projects from tfspreview

Connecting to an Oracle Database (and doing an update on a table)

TFS Preview