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)