Different web.configs

In the project that I'm currently working I had a problem because my connection strings are different from the other developers: I want to work locally in my SQL Express so the connections strings are different and since we are developing with Visual Source Safe 2005 as the version control system it is not possible to change the file where the connections strings are stored.

The easiest way was not to keep the web.config file under source control but of course that wasn't the desired way because we have lots of settings that we want to keep under source control.

After googling a while I found that the appSettings section in the web.config file of an ASP.NET web application has an attribute – file – that can point to an external file where we can store additional settings.

So my initial web.config file is:

<appSettings

<add
key="DB_1_ConnectionStringName"
value="DB_1" />

<add
key="DB_2_ConnectionStringName"
value="DB_2" />

</appSettings>

 

<connectionStrings>

<add
name="DB_1"
connectionString="server=db_server;database=db_1; … "
providerName="System.Data.SqlClient" />

<add
name="DB_2"
connectionString="server=db_server;database=DB_2; … "
providerName="System.Data.SqlClient" />

</connectionStrings>

Of course, when I want to work locally in my SQL Express instance this doesn't work. So I changed the web.config file like this:

<appSettings file="custom.config"

<add
key="DB_1_ConnectionStringName"
value="DB_1" />

<add
key="DB_2_ConnectionStringName"
value="DB_2" />

</appSettings>

 

<connectionStrings>

<add
name="DB_1"
connectionString="server=db_server;database=db_1; … "
providerName="System.Data.SqlClient" />

<add
name="DB_2"
connectionString="server=db_server;database=DB_2; … "
providerName="System.Data.SqlClient" />

 

<add
name="DB_1_LOCAL"
connectionString="server=.\SQLEXPRESS;database=db_1; … "
providerName="System.Data.SqlClient" />

<add
name="DB_2_LOCAL "
connectionString="server=db_.\SQLEXPRESS;database=DB_2; … "
providerName="System.Data.SqlClient" />

 

</connectionStrings>

And my custom.config file is:

<appSettings

<add
key="DB_1_ConnectionStringName"
value="DB_1_LOCAL" />

<add
key="DB_2_ConnectionStringName"
value="DB_2_LOCAL" />

</appSettings>

 

What happens is that the settings in the custom.config file override the settings in the web.config file.

The other developers don't need to have the custom.config files because "the runtime ignores the attribute, if the specified file cannot be found" [msdn].

So I have only the custom.config file that it isn't under source control but that's no problem because I only redefine the name of the connection strings. The actual connection strings are still stored in the web.config file and under source control.

 

Recursive Queries in SQL Server 2005

For building recursive queries In SQL Server 2005 we have to use a new functionality called Common Table Expressions.

Let's see how we can retrieve all the employees that report to a manager (ManagerID = 3) in the AdventureWorks database sample:

WITH CTE (EmployeeID, FullName, Title, ManagerID) AS
(

SELECT E.EmployeeID, C.FirstName + ' ' + C.LastName FullName, E.Title, E.ManagerID
FROM HumanResources.Employee E
INNER JOIN Person.Contact C ON E.ContactID = C.ContactID
WHERE E.EmployeeID = 3
UNION ALL

SELECT E.EmployeeID, C.FirstName + ' ' + C.LastName FullName, E.Title, E.ManagerID
FROM HumanResources.Employee E
INNER JOIN Person.Contact C ON E.ContactID = C.ContactID
INNER JOIN CTE ON E.ManagerID = CTE.EmployeeID
)

SELECT * FROM CTE

This query returns the following results:

EmployeeID

Full Name

Title

ManagerID

3

Roberto Tamburello

Engineering Manager

12

4

Rob Walters

Senior Tool Designer

3

9

Gail Erickson

Design Engineer

3

11

Jossef Goldberg

Design Engineer

3

158

Dylan Miller

Research and Development Manager

3

263

Ovidiu Cracium

Senior Tool Designer

3

267

Michael Sullivan

Senior Design Engineer

3

270

Sharon Salavaria

Design Engineer

3

5

Thierry D'Hers

Tool Designer

263

265

Janice Galvin

Tool Designer

263

79

Diane Margheim

Research and Development Engineer

158

114

Gigi Matthew

Research and Development Engineer

158

217

Michael Raheem

Research and Development Manager

158

 

If we don't want to return the manager we change the WHERE clause:

WITH CTE (EmployeeID, FullName, Title, ManagerID) AS
(

SELECT E.EmployeeID, C.FirstName + ' ' + C.LastName FullName, E.Title, E.ManagerID
FROM HumanResources.Employee E
INNER JOIN Person.Contact C ON E.ContactID = C.ContactID
WHERE E.ManagerID = 3
UNION ALL

SELECT E.EmployeeID, C.FirstName + ' ' + C.LastName FullName, E.Title, E.ManagerID
FROM HumanResources.Employee E
INNER JOIN Person.Contact C ON E.ContactID = C.ContactID
INNER JOIN CTE ON E.ManagerID = CTE.EmployeeID
)

SELECT * FROM CTE

And the results are:

EmployeeID

Full Name

Title

ManagerID

4

Rob Walters

Senior Tool Designer

3

9

Gail Erickson

Design Engineer

3

11

Jossef Goldberg

Design Engineer

3

158

Dylan Miller

Research and Development Manager

3

263

Ovidiu Cracium

Senior Tool Designer

3

267

Michael Sullivan

Senior Design Engineer

3

270

Sharon Salavaria

Design Engineer

3

5

Thierry D'Hers

Tool Designer

263

265

Janice Galvin

Tool Designer

263

79

Diane Margheim

Research and Development Engineer

158

114

Gigi Matthew

Research and Development Engineer

158

217

Michael Raheem

Research and Development Manager

158

 

If we want to get all the employees that an employee reports to, we just have to change the INNER JOIN order:

WITH CTE (EmployeeID, FullName, Title, ManagerID) AS
(

SELECT E.EmployeeID, C.FirstName + ' ' + C.LastName FullName, E.Title, E.ManagerID
FROM HumanResources.Employee E
INNER JOIN Person.Contact C ON E.ContactID = C.ContactID
WHERE E.EmployeeID = 3
UNION ALL

SELECT E.EmployeeID, C.FirstName + ' ' + C.LastName FullName, E.Title, E.ManagerID
FROM HumanResources.Employee E
INNER JOIN Person.Contact C ON E.ContactID = C.ContactID
INNER JOIN CTE ON E.EmployeeID = CTE.ManagerID
)

SELECT * FROM CTE

And the employees that the employee (EmployeeID = 3) reports to are:

EmployeeID

Full Name

Title

ManagerID

3

Roberto Tamburello

Engineering Manager

12

12

Terri Duffy

Vice President of Engineering

109

109

Ken Sánchez

Chief Executive Officer

NULL