Shimon Krokhmal's blog

i'm making some cool Linq presentation for my upcoming lecture in 888.
when installing visual studio 2008 and sql server 2005,
a funny error came accross when i tried to connect to the SQL server through the visual studio(on vista):

Could not open connection to SQL Server - Named Pipes Provider, error 40

a few configuration modifications need to be done in the sql server server surface area such as enabling the named pipes, and granting admin priviliges to the worker user.

the second(and most important) is on the server name field you need to specify the sql server instance (for the connection string).

Enjoy.

 

For those who didn't read the previews post about Sequence Implementation In SQL Server 2000 , please read it before continuing.

basically the idea is quite simple but i tough to add this post to complete the sequence framework.
adding some handling functions to your DAL object :

        /// <summary>
        /// this procedure retrieves from the database the value of the desired sequence.
        /// this is a generic function that gets the stored procedure to get the data from and the sequence name itself
        /// </summary>
        /// <param name="seqName">the name of the desired sequence</param>
        /// <param name="storedProcedure">the stored prosedure to call</param>
        /// <param name="connection">the connection to the database</param>
        /// <returns>an integer that represent the value of the sequence</returns>
        internal virtual int GetSequenceID(string seqName,string storedProcedure, SqlConnection connection)
        {
            int ret = int.MinValue;
            
            // Create a command
            SqlCommand command = new SqlCommand(storedProcedure, connection);    
            command.CommandType = CommandType.StoredProcedure;

            // pass the parameter of the sequence name for the series sequence
            SqlParameter p1 = new SqlParameter("@seqName",SqlDbType.VarChar);
            p1.Value = seqName;
            p1.Direction = ParameterDirection.Input;
            command.Parameters.Add(p1);        

            // open connection or using it if already opened
            bool OpenedConnection = false;
            try
            {
                if(connection.State!=System.Data.ConnectionState.Open)
                {
                    OpenedConnection = true;
                    connection.Open();
                }            
                
                // Execute the command
                ret = Convert.ToInt32(command.ExecuteScalar());
            }
            finally
            {
                if ((OpenedConnection) && (connection.State != System.Data.ConnectionState.Closed))
                    connection.Close();
            }
            
            return ret;
        }

        /// <summary>
        /// this is an internal function that retrieves the sequence value
        /// generally, it wraps the other internal method that does the actual work
        /// </summary>
        /// <param name="seqName">the name of the sequence that we want</param>
        /// <param name="storedProcedure">the stored procedure that we address for</param>
        /// <returns>the value of the sequence</returns>
        internal int GetSequenceID(string seqName,string storedProcedure)
        {
            int ret = int.MinValue;

            // Use the connection
            using(SqlConnection connection = DalServices.Connection())
            {
                try
                {
                    // Open the connection
                    connection.Open();
                    
                    // Get the object throught the internal method
                    ret = GetSequenceID(seqName,storedProcedure,connection);
                }
                
                finally
                {
                    // Close the connection
                    if (connection.State != System.Data.ConnectionState.Closed) connection.Close();
                }
            }
            
            return ret;
        }

and add some wrapping functions to extern the methods:

        /// <summary>
        /// The Next value of the series sequence
        /// </summary>
        /// <returns>the value of the sequence</returns>
        public int GetNextSeries()
        {
            // Calling the internall function to get the value
            return GetSequenceID("sq_series","sp_SequenceNextVal");
        }

        /// <summary>
        /// The current value of the series sequence
        /// </summary>
        /// <returns>the value of the sequence</returns>
        public int GetCurrentSeries()
        {
            // Calling the internall function to get the value
            return GetSequenceID("sq_series","sp_SequenceCurrVal");
        }

once again i'm a happy kamper.

for those folks who worked with oracle, know that sequence is an internal database object which has it's own methods and properties.
hey, why shouldn't i use this wonderful auto number feature that sql server has ?
well, auto number will give you a good solution to most of your needs that a sequence provided in oracle.
but, here is some conditions where auto number just won't suffice :

• Managing more than one "auto number" in one table - auto number can be defined only at one field.
• Managing the same auto number across multiple tables - the auto number can be asigned only to a specified table.

the solution is simple:
we will create some framework that will allow us to manage sequences and work with them.

first we will create one table to contain the sequences.

CREATE TABLE [dbo].[sysSequences] (
    [SequenceName] [char] (30) COLLATE Hebrew_CI_AS NOT NULL ,
    [Incrasement] [int] NOT NULL ,
    [StartValue] [int] NOT NULL ,
    [CurrentValue] [int] NOT NULL
) ON [PRIMARY]
GO

next thing we need to do is create some stored procedures that will handle these sequences.

the create sequence procedure :

CREATE procedure dbo.sp_Create_Sequence
(
    @seqName     varchar(30),
    @Increasement     int,
    @startValue    int
)
as
begin

declare @r int
begin transaction

    -- Check if the sequence exists
    set @r =(    select count(*)
    from sysSequences where SequenceName = @SeqName)
    
    if @r <> 0 --if error is raised
    begin
        goto LogError
    end

insert into sysSequences (SequenceName,Incrasement,StartValue,CurrentValue)
values (@seqName,@Increasement,@startValue,@startValue)    

commit transaction
goto ProcEnd

LogError:
rollback transaction
RAISERROR ('Can not create sequence with name %s, sequence exists already.',16,1,@seqName)

ProcEnd:
end

once we created the "object" (not really an object, more as a row in the sequence table), lets get his current value

create procedure dbo.sp_SequenceCurrVal
(
    @seqName     varchar(30)

)
as
begin

declare @rows int

begin transaction
    -- Check if the sequence exists
    set @rows =(    select count(*)
    from sysSequences where SequenceName = @SeqName)
    
    -- if no such row
    if @rows = 0
    begin
        goto LogError
    end

    -- Get the new value
    select CurrentValue from sysSequences
    where SequenceName = @SeqName
    
commit transaction
goto ProcEnd

LogError:
rollback transaction
RAISERROR ('sequence named %s, does not exists.',16,1,@seqName)

ProcEnd:
end

and the "get next value" method (very similar to the get current procedure.)

create procedure dbo.sp_SequenceNextVal
(
    @seqName     varchar(30)

)
as
begin

declare @rows int

begin transaction
    -- Check if the sequence exists
    set @rows =(    select count(*)
    from sysSequences where SequenceName = @SeqName)
    
    -- if no such row
    if @rows = 0
    begin
        goto LogError
    end


    -- Update the sequence increasement
    update sysSequences
    SET CurrentValue = CurrentValue + Incrasement
    where SequenceName = @SeqName

    -- Get the new value
    select CurrentValue from sysSequences
    where SequenceName = @SeqName
    
commit transaction
goto ProcEnd

LogError:
rollback transaction
RAISERROR ('sequence named %s, does not exists.',16,1,@seqName)

ProcEnd:
end

thats it, done.
now we have some sql server sequences

 

As you know, Microsoft didn't intended that you access the SPS database, but via object model only.
the problem that it has many bugs, performance issues, security issues and lots lots problems that will make the programmer's life a living hell.

So, lets see how can we break the 1st guideline of SPS programming - "do not use the SPS database directly".
man , I'm feeling like a criminal now, presenting a guide how to do something that Microsoft invested a lot of effort to prevent us from doing.

Lets get down to business,
don't count that the connection string is laying in some property, this one we need to do some dirty work.

A little background how we are going to do it:

The connection string looks like this :

"Integrated Security=SSPI;Server=someServer;database=SomeDatabase"

as you can see, the only thing that can change here is the server name and the database name.

lets create a core function that will receive DB collection and the desired site guid and construct the connection string.

private string GetConnectionStringForSite(SPContentDatabaseCollection DBs, Guid siteGuid)
{
    string rc = "";
    SPContentDatabase oDB = null;

    for(int i=0; i<DBs.Count;i++)
    {
        // Get the database
        oDB = DBs[i];

        SqlCommand c = new SqlCommand();
        string strConn = "Integrated Security=SSPI;Server=" + oDB.Server + ";database=" + oDB.Name;

        using(SqlConnection conn = new SqlConnection(strConn))
        {
            // Set the Connection
            c.Connection = conn;
            try
            {
                // Open the connection
                conn.Open();

                c.CommandText = "SELECT FullUrl FROM Sites where Id=@Id";

                // Set the parameter
                c.Parameters.Add(new SqlParameter("@Id",System.Data.SqlDbType.UniqueIdentifier));
                c.Parameters["@Id"].Value = siteGuid;

                // Execute reader
                SqlDataReader reader = c.ExecuteReader(System.Data.CommandBehavior.SingleRow);

                // check if we have rows
                bool hasRows = reader.HasRows;

                // Close the connection and the reader
                reader.Close();
                conn.Close();

                // Check if it got rows
                if(hasRows)
                {
                    // Set the return value
                    rs = strConn;

                    // Stop iterating through the DB's
                    break;
                }

                // Close the reader
                reader.Close();
            }
            finally
            {
                // Close the connection
                if(conn.State != System.Data.ConnectionState.Closed) conn.Close();
                oDB = null;
            }
        }
    }
    return rc;
}

 

this function will be placed in a dedicated class, i call is "SPSDB"

lets create the class itself and its private members

    public class SPSDB
    {
        private string _conString = "";
        private string _vsUrl = "";

        public String URL
        {
            get{return _vsURL;}
        }
    }

now we need to write the constructor and the connection "factory"

the constructor will look like this :

public SPSDB(string siteUrl)
{
    using(Microsoft.SharePoint.Administration.SPGlobalAdmin ga = new Microsoft.SharePoint.Administration.SPGlobalAdmin())
    {
        Microsoft.SharePoint.Administration.SPVirtualServerCollection VSc = ga.VirtualServers;

        SPVirtualServer vs = null;
        Guid g;
        bool isFound = false;
        
        // Run through all the VS collection
        for(int i=0; i<VSc.Count;i++)
        {
            // Get the virtual server reference
            vs = VSc[i];

            // if the current vs is not from the needed state than continue to the next VS
            if(vs.state != SPVirtualServerState.Ready)
                continue;

            // Run through all the sites in the vs
            for(int j=0; j< vs.Sites.Count;j++)
            {
                using(SPSite s = vs.Sites[j])
                {
                    // Check if we found the needed vs
                    if(s.Url.ToLower() == siteUrl.ToLower())
                    {
                        // Save the needed data
                        isFound = true;
                        g = s.ID;
                        _vsURL = vs.Url.ToString();
                        _conString = GetConnectionStringForSite(vs.ContentDatabases,g);
                        break;
                    }
                }
            }

            // Check if the vs has been found
            if(curVs != null)
                break;

            // Clear the virtual server object
            vs = null;

            // Clear memory
            GC.Collect();
            GC.WaitForPendingFinalizers();
        }

        // Throw an exception if the site guid is not there
        if(!isFound)
            throw new Exception("the given site url cannot be found");
        
    }
}

and the factory method will look like this :

public SqlConnection GetCon()
{
    return new SqlConnection(_conString);
}

thats it!
now to use it you can do it like this :

SPSDB dbstuff = new SPSDB("http://www.krokhmal.com");

using(SqlConnection con = dbstuff.GetCon())
{
    // Your DB code here ...
}

I've added a file attachment to this post with the mentioned class.
enjoy.

SPSDB.rar (1.34 KB)

remember this neat syntax that exists in oracle database for hierarchy selects ?
actually , its pretty simple :

SELECT last_name, employee_id, manager_id, LEVEL
FROM employees
START WITH employee_id = 100
CONNECT BY PRIOR employee_id = manager_id;

ever tried to do the same in sql server ?
well, these kind of syntax just does not exist,
we need to work very hard to create such a feature in our database.

so, what do we have:

• a table that contains entities
• each entity connected to some father entity
• each connection describes a "father - son" relation between the two entities

lets not forget the things we need to relay on , when implementing :

• what will happen when we will delete the father of some sub tree ?
• what should we do when we update/add a record ?
• how will we select the data ?
• algorithm efficiency is crucial, if we will need to wait 5 minutes for the data, it's not worth it

the first (but apparently the worst) idea that came to my mind is recursion
lets look at this table :

 

EmployeeID	Name	BossID
1	shimon	NULL
2	yossi	1
3	Gaby	1
4	koby	3
5	jack	3

 

we have we that the employee shimon is the "big boss" (because there is no other boss above him),
under shimon we have the employees Gaby and yossi,
and under Gaby, we have another 2 employees : Koby and jack

the recursive solution is to write some stored procedure that will receive the employeeID and return as a data-Table the results
i will not add the code for this solution and surely will not recommend it because it was many problems :

• for each record we received as a descendant ,
  we need to run with the function and get her descendants,
  and so on, until there are no descendants for the node
• we are limited to 32 levels of hierarchy
• the runtime will depend on the row count that is in the table (we will need to run on each of the rows one at the time)
• the run on the node will look like this :
  
  
  

by the way, the most common way that I've seen to select hierarchical structure,
is simply by setting a join between the levels in the select query.
for example :

SELECT TopBoss.Name TopBoss, Boss.Name Boss, Employees.Name Employee
FROM Employees
INNER JOIN Employees AS Boss ON Employees.BossID=Boss.EmployeeID
INNER JOIN Employees TopBoss ON Boss.BossID=TopBoss.EmployeeID

this apply to the selection of three levels

For each level, you'd need to join the table to itself...not an attractive option if you have 5 or more levels ,
you don't know how many levels you will have to select, there is no way can control it!
It would be great if it could join itself as many times as needed. This is called a recursive join, and though some database products support it (Oracle has the CONNECT BY syntax) SQL Server is not one of them.

the other way is based on a thread that i read here about hierarchies,

lets create a table :

CREATE TABLE Tree (
Node int NOT NULL IDENTITY(100, 1),
ParentNode int,
EmployeeID int NOT NULL,
Depth tinyint,
Lineage varchar(255) )

the extra fields that has been added are the "lineage" and the "depth"

• Depth - for saving the current depth of the record in the hierarchy
• Lineage - for saving all the ancestors of the record as a concatenated string

after filling the needed data for relations, the table looks like this :

Node	ParentNode	EmployeeID	Depth	Lineage
100	NULL	1001	NULL	NULL
101	100	1002	NULL	NULL
102	101	1003	NULL	NULL
103	102	1004	NULL	NULL
104	102	1005	NULL	NULL
105	102	1006	NULL	NULL

The next part is to find the root node of the tree, also known as the top-level, etc.
That's the node that has no parent (Null), so we will start there and set the Lineage column as the root:

UPDATE Tree SET Lineage='/', Depth=0 WHERE ParentNode Is Null

Once we did that,
we can then update the rows who are the descendant of the root node:

WHILE EXISTS (SELECT * FROM Tree WHERE Depth Is Null) 
   UPDATE T SET T.depth = P.Depth + 1, 
   T.Lineage = P.Lineage + Ltrim(Str(T.ParentNode,6,0)) + '/' 
   FROM Tree AS T 
   INNER JOIN Tree AS P ON (T.ParentNode=P.Node) 
   WHERE P.Depth>=0 
   AND P.Lineage Is Not Null 
   AND T.Depth Is Null

 

this loop will run once for each level of the hierarchy (not for each node as the recursion method.)
so, with data representation of 10,000 records with 8 levels of hierarchy,
this code will run only 8 times to populate the needed data of the "lineage" field and the "depth" field, and this "heavy" procedure will happen only once at the setup.
the table should look like this after the given operation :

Node	ParentNode	EmployeeID	Depth	Lineage
100	NULL	1001	0	/
101	100	1002	1	/100/
102	101	1003	2	/100/101/
103	102	1004	3	/100/101/102/
104	102	1005	3	/100/101/102/
105	102	1006	3	/100/101/102/

 

You'll notice that for each node, the entire lineage back to the root is stored. This means that finding someone's boss, or their boss' boss, doesn't require any self-joins or recursion to create an indented list. In fact, it can be accomplished with a single SELECT.

SELECT Space(T.Depth*2) + E.Name AS Name
FROM Employees E
INNER JOIN Tree T ON E.EmployeeID=T.EmployeeID
ORDER BY T.Lineage + Ltrim(Str(T.Node,6,0))

 

maintaining the table is really not a big deal if we will use triggers.
think about the new inserted record as the row that has not been filled in the setup process.
so the insert trigger should be :

UPDATE T SET T.depth = P.Depth + 1,
T.Lineage = P.Lineage + Ltrim(Str(T.ParentNode,6,0)) + '/'
FROM Tree AS T
INNER JOIN Tree AS P ON (T.ParentNode=P.Node)
WHERE P.Depth>=0
AND P.Lineage Is Not Null
AND T.Depth Is Null

 

the