Thursday 25 June 2009

Common Table Expressions

Introduced in SQL 2005 was the Common Table Expression (CTE). These can act like subqueries or views within a SQL statement.

Good definition on 4GuysFromRolla and MSDN.

To use them you use the 'WITH' keyword, for example if you were using the following Subquery:


SELECT
*
FROM
(
SELECT
p.Forename
, p.Surname
, COUNT(m.MessageId) AS MessageCount
FROM
[Pupil] p
INNER JOIN [Message] m ON (p.PupilId = m.PupilId)
GROUP BY
p.Forename
, p.Surname
) AS PupilsWithMessages


to get all pupils with a message you could now use a CTE,


WITH PupilsWithMessages (Forename, Surname, MessageCount) AS (
SELECT
p.Forename
, p.Surname
, COUNT(m.MessageId) AS MessageCount
FROM
[Pupil] p
INNER JOIN [Message] m ON (p.PupilId = m.PupilId)
GROUP BY
p.Forename
, p.Surname
)

SELECT
*
FROM
PupilsWithMessages


This is a simple example but it looks a lot cleaner than subqueries and you can use them in the same way as you would a view. Better examples on the above links.

Recursive CTE's


You can also use them for recursing, a simple example I came up with was to loop through a comma delimited list of weekdays and return a seperate row for each one. Whereas previously you would need to use a Cursor you can now use a CTE.


DECLARE @String varchar(100)
SET @String = 'Monday,Tuesday,Wednesday,Thursday,Friday,Saturday,Sunday,';

WITH List (Val, Rest, HierarchyLevel) AS (
SELECT
SUBSTRING(@String, 1, CHARINDEX(',', @String)-1) AS Val
, SUBSTRING(@String, CHARINDEX(',', @String)+1,200) AS Rest
, 1 AS HierarchyLevel
UNION ALL
SELECT
SUBSTRING(l.Rest, 1, CHARINDEX(',',l.Rest)-1) AS Val
, SUBSTRING(l.Rest, CHARINDEX(',', l.Rest)+1,200) AS Rest
, l.HierarchyLevel + 1 AS HierarchyLevel
FROM
List l
WHERE
CHARINDEX(',', l.Rest) <> 0
--AND HierarchyLevel < 5 /*You can limit how 'deep' the recursion goes*/
)

SELECT
*
FROM
List


In the 2nd query the CTE references itself (List) to continue splitting up the string until the conditions specified in the WHERE statement are true.

Thursday 4 June 2009

New property syntax in C#3.0

Prior to C#3.0 we would use the following syntax for creating properties:

private string _GetAndSet;
public string GetAndSet { get { return _GetAndSet; } set { _GetAndSet = value; } }


private string _SetOnly;
public string SetOnly{ get { return _SetOnly; } set { _SetOnly = value; } }


C#3.0 allows us to use the following syntax which is the equivalent of above.

public string GetAndSet { get; set; }

public string SetOnly{ get; private set; }

Tuesday 2 June 2009

Create report in Reporting Services 2008

This is a great link to show you how to create reports and deploy them:

http://www.accelebrate.com/sql_training/ssrs_2008_tutorial.htm

Add a parameter to report once report is created:

Right click parameter in the report data window - add parameter and fill out required fields.
Right click the grid - tablix properties - filters and add in the filter using the parameter as the value.

Friday 15 May 2009

Upload XML File

To upload an XML file into SQL Server 2008:

1. Open SQL Server Business Intelligence Studio.
2. Create a new package ( shown in previous post)
3. Create Destination table on Server to upload file to.
4. Drag on Execute SQL Task - add TSQL to truncate the table.
5. Drag on Data Flow Task and double click
6. Drag on XML Source file and set the properties.
7. Drag on OLE DB Destination and set properties.

Job Done.

Thursday 14 May 2009

Subsonic Parent -> Child saving

How to save subsonic objects to the db that have FK values needing set.

Here's an example that also uses a transaction. Please note that you will have add a reference to the System.Transactions namespace in your project and then reference in your class.



   using (TransactionScope scope = new TransactionScope())

    {

        try

        {

            Order order = new Order();

            //populate order details.

            order.Save(); //Commit to DB





            OrderItem item = new OrderItem();

            //populate orderItem details.



            item.Order = order;   //THIS LINE SETS THE PARENT OBJECT TO ABOVE ORDER



            item.Save();  //Commit to DB



            //complete you transaction

            scope.Complete();



        }

        catch (System.Data.SqlClient.SqlException ex)

        {

            throw ex;

        }

    }


Create a SSIS package

Basic steps to create an SSIS package (old DTS) to copy data from one database to another in SQL Server 2008:

Great link for the basic steps http://it.toolbox.com/blogs/coding-dotnet/creating-a-ssis-package-24699

However to truncate the tables before filling them with the above package:

1. Drag a Execute SQL Task into the control Flow window.
2. Double click and set the connection
3. Enter SQL Statement
4. Drag green line to the data flow task or the next step to run.

Point to Note: This tool is similar to Reporting Services where you create a project and then can have many packages within the project. Each package can then share the connections within the Data Sources folder.

Enjoy!

Friday 8 May 2009

Setting up an ASP.Net site for membership using your own database

If you are setting up a new application and you want to use the membership and roles security then normally .Net will try to set this up for you in your local express server. If however you want to set this up in your application database, on your main server then do the following.

1) Run C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\aspnet_regsql.exe
2) Use the wizard to select the server and database. (I believe this can be any of 2000/05/08)
3) Change the following in your web config to suit your DB and server settings. Cahnge any other settings to suit your application. Click here for MSDN article.


<configuration>

<connectionStrings>

<add name="MySqlConnection" connectionString="Data

Source=YOURSQLSERVENAME;Initial Catalog=YOURDBNAME;Integrated

Security=SSPI;" />

</connectionStrings>

<system.web>

<authentication mode="Forms" >

<forms loginUrl="login.aspx"

name=".ASPXFORMSAUTH" />

</authentication>

<authorization>

<deny users="?" />

</authorization>

<membership defaultProvider="SqlProvider" userIsOnlineTimeWindow="15">

<providers>

<clear />

<add

name="SqlProvider"

type="System.Web.Security.SqlMembershipProvider"

connectionStringName="MySqlConnection"

applicationName="MyApplication"

enablePasswordRetrieval="false"

enablePasswordReset="true"

requiresQuestionAndAnswer="true"

requiresUniqueEmail="true"

passwordFormat="Hashed" />

</providers>

</membership>

</system.web>

</configuration>






4) That should be you!


I got this info by using StackOverflow and asking this question.



Free Hit Counter