T-SQL - Converting Time in SQL Server to Ticks

We were trying to determine why the start date field in a tool we use was stored as a bigint. I thought it may be ticks.

To figure this out we tried to do the following. It did not get our answer, but it was a very cool use of the binary data type.

DECLARE @date datetime = '1/22/2013'

,@ticksPerDay BIGINT = 864000000000declare @date2 datetime2 = @datedeclare @dateBinary binary(9) = cast(reverse(cast(@date2 as binary(9))) as binary(9))declare @days bigint = cast(substring(@dateBinary, 1, 3) as bigint)declare @time bigint = cast(substring(@dateBinary, 4, 5) as bigint)select @date as [DateTime], @date2 as [DateTime2], @days * @ticksPerDay + @time as [Ticks]


Detailed reference.
http://stackoverflow.com/questions/7386634/convert-sql-server-datetime-object-to-bigint-net-ticks

Turned out the value was a json formatted date, which is defined as number of milliseconds in Universal Coordinated Time (UTC) since midnight January 1, 1970 (UTC). Again, this is a mere convention and not part of the JSON standard. If you are exchanging data with another application, you will need to check its documentation to see how it encodes date and time values within a JSON literal. This is based on the face that javascript does not have a datetime datatype, so you can pass the time as a big int (which is what we had) or a string that is formatted like a date. The below test transform targets the date

DECLARE @EpochDate
DateTime2 = '1/1/1970',@TargetDate DateTime2
--change this date for your transformation,@JsonDate BIGINT = 628318530718
,@Milli INT,@Seconds BIGINT
SET @Milli = RIGHT(@JsonDate,3)SET @Seconds = LEFT(@JsonDate,LEN(@JsonDate)-3)SELECT @EpochDate, @JsonDate, @Milli, @Seconds SET @TargetDate = dateadd(ms, @Milli,dateadd(second,@Seconds,@EpochDate))IF ((MONTH(@TargetDate) >= 3 AND DAY(@TargetDate) >= 11 AND HOUR(@TargetDate)AND
(
MONTH(@TargetDate) <= 11 AND DAY(@TargetDate) <= 2))SELECT DATEADD(HOUR,-8, @TargetDate)ELSE SELECT DATEADD(HOUR,-7, @TargetDate)

T-SQL MERGE INTO With Replace Substring Manipulation - When You are Thinking About things the Hard Way

Today I was told that we needed to change the ULRs for some sites to remove the subdomain element of the URL and instead insert an environment value before the domain. 

I opted to do a MERGE INTO rather than a simple update. The reasons for this are technical and complicated. Well really, I had never used REPLACE in an UPDATE and assumed it would not work. I also was more interested in something fancy, rather than the simple solution. Oops.

So instead of doing this

USE Database123
DECLARE
    @prodDomainStr char(19) = '.Company.com',
    @qaDomainStr char(19) = 'UAT.Company.com',
    @configType INT = (SELECT ConfigTypeID FROM Config WHERE NAME = 'URLInBound')

UPDATE PartnerConfig USING (
        SET ConfigString = REPLACE(ConfigString,@prodDomainStr, @qaDomainStr)
WHERE ConfigTypeID = @configType


I used this. Both did the job. It is an ad hoc infrequent update of a limited record set (<100 rows). It took me longer to write the merge, and I am sure the query plan is worse for me doing so. Lesson learned.

USE Database123
DECLARE
    @prodDomainStr char(19) = '.Company.com',
    @qaDomainStr char(19) = 'UAT.Company.com',
    @configType INT = (SELECT ConfigTypeID FROM Config WHERE NAME = 'URLInBound')

MERGE INTO PartnerConfig CCTarget
USING (
        SELECT PartnerConfigID, REPLACE(ConfigString,@prodDomainStr, @qaDomainStr) VALUE
        FROM PartnerConfig
        WHERE ConfigTypeID = @configType
      ) AS CCSource
        ON CCTarget.PartnerConfigID = CCSource.PartnerConfig
WHEN MATCHED THEN
UPDATE
SET CCTarget.ConfigString = CCSource.ConfigString;

As always, the example has been sanitized to use generic names. This can lead to malformed code.

T-SQL - XQuery - Delete Nodes From XML Document

When working with XML in SQL server you are using a sublanguage called XQuery inside of T-SQL. You have access to XML methods when using an XML typed value.  This query was written to be run by support, so it has some built in tests that assist support in knowing we are targeting the correct data. The keys here are:
Practice this in a test environment before you do it on prod. XML is tricky.

Understand the layout of your XML document. The one I was targeting was simple. We have some duplicate data and I wan to remove it.

<ResponseRoot attrib1='1' attrib2='2'>
<Answer id='77' value='work it out'>32</Answer>
<Answer id='78' value='work it out'>22</Answer>
<Answer id='79' value='work it out'>1112</Answer>
<Answer id='80' value='work it out'>112</Answer>

<Answer id='81' value='work it out'>12</Answer>
<Answer id='82' value='work it out'>42</Answer>
<Answer id='83' value='work it out'>552</Answer>
<Answer id='84' value='work it out'>92</Answer>
<Answer id='85' value='work it out'>2</Answer>

<Answer id='86' value='work it out'>12</Answer>
<Answer id='87' value='work it out'>42</Answer>
<Answer id='88' value='work it out'>552</Answer>
<Answer id='89' value='work it out'>92</Answer>
<Answer id='90' value='work it out'>201</Answer>


</ResponseRoot>

The [13] is part of XPath, which is used by XQuery to locate items in an XML document. Unlike C# (which enumerates nodes from 0), XQuery in T-SQL enumerates the nodes from 1.  Below I am looking for the 9th through 13th child nodes of the root to delete. I deleted them from bottom to top to prevent changing the absolute position of the nodes as I deleted them. Note that the 13th node is not determined by the data or the attributes of the node. Just by the ordinal position from the root.

I build the XML value in a variable by selecting the XML data from the table, updating it before I run the update, and then updating the row with the variable.
 

BEGIN TRAN

DECLARE

  @UserId INT = (SELECT UserId FROM User WITH (NOLOCK)  WHERE FIRSTNAME = 'Person' AND LASTNAME = 'Someone')

, @ObjectID varchar(99) = (SELECT ObjectID From Object WITH (NOLOCK) WHERE Name = 'Object name')

, @ResponseId INT

SET @ResponseId = (SELECT MAX(ObjectResponseID) From ObjectResponse WITH (NOLOCK) WHERE UserId = @UserId AND ObjectID = @ObjectID)

--Verify correct response is targeted

IF @UserId IS NOT NULL

BEGIN

IF @ResponseId = 123456

BEGIN

SELECT ObjectResponseXML Before FROM ObjectResponse WITH (NOLOCK)

WHERE ObjectResponseID = @ResponseId

DECLARE @XDoc XML

SET @XDoc = (SELECT ObjectResponseXML FROM ObjectResponse WHERE ObjectResponseID = @ResponseId)

--Remove 13 before 12 to retain absloute order of preceding nodes you will delete

SET @XDoc.modify('delete (/ResponseRoot/Answer[13])')

SET @XDoc.modify('delete (/ResponseRoot/Answer[12])')

SET @XDoc.modify('delete (/ResponseRoot/Answer[11])')

SET @XDoc.modify('delete (/ResponseRoot/Answer[10])')

SET @XDoc.modify('delete (/ResponseRoot/Answer[9])')

UPDATE ObjectResponse

SET ObjectResponseXML = @XDoc

WHERE ObjectResponseID = @ResponseId

SELECT ObjectResponseXML After FROM ObjectResponse WITH (NOLOCK)

WHERE ObjectResponseID = @ResponseId

END

ELSE Print 'Object response not found.'

END

ELSE Print 'User not found.'

ROLLBACK

C# - Data Reader For Test Result Validation

This is a two class solution to reading data from the database for data scenario validation.

//CLASS 1

using System;
using System.Data.SqlClient;

namespace Thing_Automation.Helpers
{
    public class DBUtils
    {
        private static string SQL_DB_SOURCE = "SERVERNAMESOMETHING";
        private static string SQL_DB_CATALOG = "DATABASESOMETHING";
        private static string connectionString = "Data Source=" + SQL_DB_SOURCE + "; Initial Catalog=" + SQL_DB_CATALOG + "; Integrated Security=true; MultipleActiveResultSets=True";
     
        /// <summary>
        /// Get the score relative to the most recent completion
        /// </summary>
        /// <param name="Thing_Type">name of the Thing_ from the Thing_ table used by the client e.g. Thing_ 10, Thing_ 13</param>
        /// <param name="TestTime">Time the test started. Used to filter out Thing_ score relevant to this test context</param>
        /// <param name="UserName">User login for this test context</param>
        /// <returns>a decimal representing the actual Thing_ score</returns>

        public decimal ActualRawScore = 887;
        public int ActualThing_Level = 888;
        public decimal ActualThing_Score = 889;

        public Tuple<decimal,int,decimal> GetThing_Score(string Thing_Type, DateTime TestTime, string UserName)
        {

                using (SqlConnection connection = new SqlConnection(connectionString))
                {
                    string queryString = "USE " + SQL_DB_CATALOG
                                         + " SELECT SRP_RawScore.[Value], SRP_Thing_Lvl.Value, SRP_Thing_Score.Value FROM Thing_ S"
                                         + " INNER JOIN [Thing_AnswerSet_] SR ON S.Thing_ID = SR.Thing_ID "
                                         + " INNER JOIN Loging A ON A.PersonID = SR.PersonID "
                                         + " INNER JOIN [Thing_AnswerSet_Profile] SRP_RawScore ON SR.Thing_AnswerSet_ID = SRP_RawScore.Thing_AnswerSet_ID  AND SRP_RawScore.Thing_AnswerSet_ProfileTypeID = 1 "
                                         + " INNER JOIN [Thing_AnswerSet_Profile] SRP_Thing_Lvl ON SR.Thing_AnswerSet_ID = SRP_Thing_Lvl.Thing_AnswerSet_ID  AND SRP_Thing_Lvl.Thing_AnswerSet_ProfileTypeID = 2 "
                                         + " INNER JOIN [Thing_AnswerSet_Profile] SRP_Thing_Score ON SR.Thing_AnswerSet_ID = SRP_Thing_Score.Thing_AnswerSet_ID  AND SRP_Thing_Score.Thing_AnswerSet_ProfileTypeID = 3 "
                                         + " WHERE S.Name = '" + Thing_Type + "'"
                                         + " AND CompletedDateTime > '" + TestTime + "'"
                                         + " AND A.LoginName = '" + UserName + "'";

                    SqlCommand spcmd = new SqlCommand(queryString, connection);

                    //try
                    //{
                        SqlDataReader reader = null;
                        connection.Open();
                        reader = spcmd.ExecuteReader();
                        reader.Read();
                        ActualRawScore = decimal.Parse(reader[0].ToString());
                        ActualThing_Level = int.Parse(reader[1].ToString());
                        ActualThing_Score = decimal.Parse(reader[2].ToString());
                    //}
                    //catch (Exception e)
                    //{
                    //    ActualThing_Score = 999;
                    //}
               }
            return new Tuple<decimal, int, decimal>(ActualRawScore,ActualThing_Level,ActualThing_Score);
            }
        }
    }




//CONSUMING CLASS
using Microsoft.VisualStudio.TestTools.UnitTesting;
using Thing_Automation.Helpers;
using Thing_Automation.Pages;


namespace Thing_Automation.Tests
{

    [TestClass]
    public class Thingie_10Thing_ScoringTest : Thingie_Thing_Page
    {

        private decimal ActualRawScore;
        private int ActualThingie_Level;
        private decimal ActualThingie_Score;

        //TODO: move this stuff to the app.config
        private const string ClientSetupFile = "StLukesClientSetups.xml";
        private const string Thingie_Type = "Thingie_ 10";

        DBUtils utils = new DBUtils();

        /// <summary>
        /// method used to test Thingie_ score. references a Thingie_ score method with special joins in dbutil.
        /// inherits the registration and navigation for the UI from the registration and Thingie_Thing_ page classes
        /// </summary>
        /// <param name="regFile">The name of the registration file you want to pass in. Any valid age/gender scenario will work for Thingie_ 10 scoring</param>
        /// <param name="ClientSetupFile">The name of the access code file for the client you are passing.</param>
        /// <param name="Thingie_Thing_ScoreFile">The name of the file containing the answer set you want to pass into the scoring engine. These values are the ids for the answers rendered in the DOM/UI</param>
        /// <param name="ExpectedRawScore">The raw score you expect from the answers you are passing in</param>
        /// <param name="ExpectedThingie_Level">The Thingie_ level you expect from the answers you are passing in</param>
        /// <param name="ExpectedThingie_Score">The activation score you expect from the answers you are passing in</param>
        public void Thingie_10Thing_ScoreTest(string regFile, string ClientSetupFile, string Thingie_Thing_ScoreFile, decimal ExpectedRawScore, int ExpectedThingie_Level, decimal ExpectedThingie_Score)
        {
            CompleteThingie_10Thing_(regFile, ClientSetupFile, Thingie_Thing_ScoreFile);
            SignOut();
            ConfirmOnLoginPage();

            utils.GetThingie_Score(Thingie_Type, TestTime, UserName);
            ActualRawScore = utils.ActualRawScore;
            ActualThingie_Level = utils.ActualThingie_Level;
            ActualThingie_Score = utils.ActualThingie_Score;

            Assert.AreEqual(ExpectedRawScore, ActualRawScore);
            Assert.AreEqual(ExpectedThingie_Level, ActualThingie_Level);
            Assert.AreEqual(ExpectedThingie_Score, ActualThingie_Score);

            TearDown();
        }


       [TestMethod]
        public void Thingie_10Thing_AllNA()
    {
        Thingie_10Thing_ScoreTest("RegistrationMale58YearsOld.xml", ClientSetupFile, "Thingie_10Thing_AllNA.xml", 0, 2, 0);
    }

        [TestMethod]
        public void Thingie_10Thing_AllStrongA()
        {
            Thingie_10Thing_ScoreTest("RegistrationMale58YearsOld.xml", ClientSetupFile, "Thingie_10Thing_AllStrongA.xml", 40, 4, 99.1m);
        }
//...more methods
    }
}

C# Best Practices - Array vs Tuple

Several times I have asked myself when to use a list, array, or tuple. This depends on what you are storing.

If you are creating a homogeneous collection of fixed length, then an array is desirable.
If you want a collection whose values are of variable length, a List allows for more optimal memory allocation as the length is dynamically allocated based on the values. A list has more functionality than an array. (need examples).
If you are creating a heterogeneous collection, then a tuple is desirable.




REFERENCE
http://stackoverflow.com/questions/1708510/python-list-vs-tuple-when-to-use-each
http://stackoverflow.com/questions/3306854/generic-list-vs-array

TFS Adminsitration - How to Delete Workitems

This is based on VS2010. 2012 wiull have a different installation path.

For 64 bit OS machines use below. For 32 bit machines use c:\program files\

Escape the path when doing the cd.

cd "c:\Program Files (x86)\Microsoft Visual Studio 11.0\Common7\IDE"

You need admin rights to do this operation. Make really sure you know what item you want to destroy, especially in a multi-collection  multi-project environment.

c:\Program Files (x86)\Microsoft Visual Studio 11.0\Common7\IDE>witadmin destroywi /collection:http://sql1:8080/tfs/IH  /id:6576

Are you sure you want to destroy work item(s) 6576? This action is not recoverable. (Yes/No) y
The work item(s) were destroyed.

Test DB Connection w/o SQL Server Installed

There are times you do not have Management Studio installed on a server and need to validate a connection can be made to the database tier. The easiest way to do this is by creating an empty file and saving it with a .udl extension (e.g. TestDBConnection.udl). This opens a native connection tool. Be sure to know the type of security, account to be used, and the connection provider you will use (look in your config file). If you are missing the SQL Server Native Client in the provider tab, it is not installed on your server. This needs to be installed if you are targeting this provider in your code (which is the most effecient way to connect to a SQL Server db). You can then open the file and see the connections string that was generated.