Nicola Celiento

As a database designer, I often create on database tables the primary key as identity autoincrement and sometimes I needed to find missing or deleted IDs thet interrupt the sequence of autoincrement number on the primary key.
To do this you can create a sequence from 1 (or 0 based on setted RESEED) to MAX ID available on the table and than make a LEFT JOIN of this sequence with start table.

The following snippet get the list of IDs of identity autoincrement column that interrupt the sequence of autoincrement values:

DECLARE @START_ID INT,  @MAXID INT

SET @START_ID = 1
SET @MAXID = (SELECT MAX(ID) FROM [TABLENAME_TO_CHECK])

DECLARE @SEQUENCE_TABLE TABLE (ID BIGINT)

--CREATE TABLE WITH ID FROM [@START_ID] TO [MAX TABLE ID]
WHILE @START_ID < @MAXID
	BEGIN
		INSERT INTO @SEQUENCE_TABLE VALUES(@START_ID)

		SET @START_ID = @START_ID + 1
	END

SELECT S.ID AS [MISSING ID] 
FROM @SEQUENCE_TABLE S 
		LEFT JOIN [TABLENAME_TO_CHECK] AS T ON  S.ID = T.ID
WHERE T.ID IS NULL

Recently I needed to find in all files version history of a TFS 2010 project with Visual Studio 2010.

Searching on Google I found Visual Studio 2010 and 2012 extensions, developed by Mattias Sköld, that allow you to achieve the goal.

Let’s see how to find inside all version of files with Visual Studio 2010 on TFS 2010.

Once installed the TFS Administrators Toolkit for VS 2010 extension we will get the feature by right-clicking on the TFS root Collection from Team Explorer window, as illustrated in Figure 1.

Figure 1

We you ckick Find in Files the followinf popup will open:

Figure 2

As you can see in the Figure 2, the feature allow you to:

1) Select Team Project
2) Filter by extension file type
3) Search or not in the file history
4) Specify search terms (keywords) with case sensitive and regular expressions options

For different needs, often I had needs to send a list of values ​​to a Stored Procedure.
To achieve this goal it is possible to use a workaround with the TSQL function nodes(XQuery) on the types of XML data.

We’ll see two examples to better understand the proposed solution.

Example 1: How to send to a Stored Procedure a list of ID.

1 – Build an XML that contains a list of ID, as follows:

 
<MyIDList>
  <ID>1</ID>
  <ID>2</ID>
</MyIDList>

In C # I can build XML as follows:

XmlDocument xmlDoc = new XmlDocument();
XmlElement xElem;
		
//add root node
XmlElement xRoot = xmlDoc.CreateElement("MyIDList");
xmlDoc.AppendChild(xRoot);
		
//Add ID 1
xElem = xmlDoc.CreateElement("ID");
xElem.InnerText = "1";
xRoot.AppendChild(xElem);
//Add ID 2
xElem = xmlDoc.CreateElement("ID");
xElem.InnerText = "2";
xRoot.AppendChild(xElem);

2 – Sent the XML built to the Stored Procedure.

In C# create DbParam as follows:

//@MyIDListXML
DbParameter dbParam = dbCommand.CreateParameter();
dbParam.ParameterName = "@MyIDListXML";
dbParam.DbType = DbType.Xml;
dbParam.Value = xmlDoc.InnerXml; //Note you need to pass the InnerXml Value
dbCommand.Parameters.Add(dbParam);				

3 – Get the @MyIDListXML parameter value and obtain the list of ID in a relational format to do a lot of relational operations (JOIN, WHERE, etc.)

SELECT T.Item.value('.' , 'BIGINT') AS ID
FROM @MyIDListXML.nodes('//MyIDList/ID') AS T(Item)

The result will be a table with two value like in figure:

Example 2: Hot to send a table to a Stored Procedure.

1 – Build an XML which represents a table like the following:

 
<MyList>
	<item attributo1="value11" attributo2="value12" />
	<item attributo1="value21" attributo2="value22" />
</MyList>

In C# I can create the XML as follows

 
XmlDocument xmlDoc = new XmlDocument();
XmlElement xElem;

//add root node
XmlElement xRoot = xmlDoc.CreateElement("MyList");
xmlDoc.AppendChild(xRoot);

//add item 1
xElem = xmlDoc.CreateElement("item");
xElem.SetAttribute("attributo1", "value11");
xElem.SetAttribute("attributo2", "value12");
xRoot.AppendChild(xElem);

//add item 2
xElem = xmlDoc.CreateElement("item");
xElem.SetAttribute("attributo1", "value21");
xElem.SetAttribute("attributo2", "value22");
xRoot.AppendChild(xElem);

2 – Sent to the Stored Procedure the XML built

In C# create DbParameter as follows:

 
//@MyListXML
DbParameter dbParam = dbCommand.CreateParameter();
dbParam.ParameterName = "@MyListXML";
dbParam.DbType = DbType.Xml;
dbParam.Value = xmlDoc.InnerXml; //Note you need to pass the InnerXml Value
dbCommand.Parameters.Add(dbParam);	

3 – Get @MyListXML list values and I can do a lot of relational operations (JOIN, WHERE, etc.)

 
SELECT T.Item.value('@attributo1' , 'varchar(10)') AS Attributo1,
	T.Item.value('@attributo2' , 'varchar(10)') AS Attributo2
FROM @MyListXML.nodes('//MyList/item') AS T(Item)

The result will be like in the following table:

It’s possible to pass any XML structure and work on SQL Engine with the nodes(XQuery) function.

Quando si sviluppa un Event Receiver su una generica lista Sharepoint non è possibile ottenere l’istanza di SPWeb dal contesto corrente mediante l’istruzione seguente:

SPWeb web = SPContext.Current.Web;

poichè viene eseguito in un contesto differente, e dunque SPContext.Current = null.

Per ovviare a questo problema è sufficiente far riferimento al parametro SPItemEventProperties ricevuto, e quindi ottenendo un’istanza di SPWeb come di seguito:

using (SPWeb web = properties.OpenWeb())
{
// do stuff
}