Blogger Jeff = new Blogger

Programming and stuff in Western PA

Cascading deletes in SQL Server

I get asked from time to time if SQL Server supports cascading deletes, and the answer is Yes. SQL Server does so via foreign key constraints with the DELETE CASCADE flag set. In the following example, after creating the objects and inserting some data, we delete a USR_ID from the parent data. After querying the child table (USER_PHONE) a second time, we can see that the cascading delete worked :

CREATE TABLE USERS
(
    USR_ID int
    ,CONSTRAINT [PK_Temp_Users1] PRIMARY KEY CLUSTERED ([USR_ID])
)

CREATE TABLE USER_PHONE
(
    USR_ID int
    ,CONSTRAINT [PK_Temp_Users2] PRIMARY KEY CLUSTERED ([USR_ID])
)
ALTER TABLE [dbo].USER_PHONE  WITH CHECK ADD  
CONSTRAINT [FK_Temp_UsersPhone_Users] FOREIGN KEY([USR_ID])
REFERENCES [dbo].[Users] ([USR_ID])
ON DELETE CASCADE
GO

INSERT INTO USERS
    SELECT 1 UNION SELECT 2 UNION SELECT 3

INSERT INTO USER_PHONE
    SELECT 1 UNION SELECT 2 UNION SELECT 3

SELECT * FROM USER_PHONE
DELETE USERS WHERE USR_ID=2
SELECT * FROM USER_PHONE

DROP TABLE USER_PHONE
DROP TABLE USERS

Tags:

Advertisements

January 28, 2008 Posted by | SQL Server, Technology | 1 Comment

Using Linq to query a CSV File

I know there have been a ton of times I’ve had to deal directly with Comma Delimited files. Searching through the contents for certain pieces of data is usually not a whole lot of fun. However with Linq, things become a bit more enjoyable!

To start off, we’ll create a csv file with the following contents :

"Mets","New York","NL"
"Marlins","Florida","NL"
"Orioles","Baltimore","AL"
"Pirates","Pittsburgh","NL"
"Phillies","Philadelphia","NL"

Save that some place and name it “teams.csv”. Then we’ll add the following namespace to our class file :

using System.Data.OleDb

Then using OleDb, we can access the csv file, and load a data table into a data adapter :

String conn = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\;
    Extended Properties=""Text;HDR=No;FMT=Delimited""";

OleDbConnection cn = new OleDbConnection(conn);
OleDbCommand cmd = new OleDbCommand(@"SELECT * FROM C:\Temp\teams.csv", cn);
OleDbDataAdapter da = new OleDbDataAdapter(cmd);

cn.Open();

DataTable dt = new DataTable();

da.Fill(dt);

At this point, we are good to go to query the datatable with Linq. Our entire function looks like this :

static void QueryCsv()
{

    OleDbConnection cn = new OleDbConnection (@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=
C:\;Extended Properties=""Text;HDR=No;FMT=Delimited""");
    OleDbCommand cmd = new OleDbCommand(@"SELECT * FROM C:\Temp\teams.csv", cn);
    OleDbDataAdapter da = new OleDbDataAdapter(cmd);

    cn.Open();

    DataTable dt = new DataTable();

    da.Fill(dt);

    var teams = from r in dt.AsEnumerable()
                where r.Field(2) == "NL"
                select new { City = r.Field(0), TeamName= r.Field(1) };

    foreach (var team in teams)
    {
        Console.WriteLine(String.Format("The {0} {1}",team.TeamName,team.City));
    }

    Console.ReadLine();

    cn.Close();

}

If you run this you should see :

Result Set

Now if your life is like mine, the client will always throw a screwball at you and say something like : “We need to return everything from the csv file where the record also exists in this other data source.” Linq makes this easy too. We’ll create a quick class to demonstrate to represent the other data source :

public class Mascot
{
    public string Name;
    public string City;
    public string Player;
}

We’ll then create a list with our data in it

List mlb = new List
{
    new Mascot{City="Philadelphia",Name="Philllies",Player="Cole Hammels"},
    new Mascot{City="Boston",Name="Red Sox",Player="Curt Schilling"},
    new Mascot{City="Florida",Name="Marlins",Player="Dontrelle Willis"},
    new Mascot{City="Cleveland",Name="Indians",Player="Jake Westbrook"},
    new Mascot{City="Baltimore",Name="Braves",Player="Eric Bedard"},
    new Mascot{City="Los Angeles",Name="Dodgers",Player="Randy Wolf"}
};

Adjusting our original code, we can query and join the two data sources to display some common data :

static void QueryCsvWithJoin()
{

	 String conn = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\;
	     Extended Properties=""Text;HDR=No;FMT=Delimited""";

	 List mlb = new List
	 {
	     new Mascot{City="Philadelphia",Name="Philllies",Player="Cole Hammels"},
	     new Mascot{City="Boston",Name="Red Sox",Player="Curt Schilling"},
	     new Mascot{City="Florida",Name="Marlins",Player="Dontrelle Willis"},
	     new Mascot{City="Cleveland",Name="Indians",Player="Jake Westbrook"},
	     new Mascot{City="Baltimore",Name="Braves",Player="Eric Bedard"},
	     new Mascot{City="Los Angeles",Name="Dodgers",Player="Randy Wolf"}
	 };

	 OleDbConnection cn = new OleDbConnection(conn);
	 OleDbCommand cmd = new OleDbCommand(@"SELECT * FROM C:\Temp\teams.csv", cn);
	 OleDbDataAdapter da = new OleDbDataAdapter(cmd);

	 cn.Open();

	 DataTable dt = new DataTable();

	 da.Fill(dt);

	 var teams = from r in dt.AsEnumerable()
	             join o in mlb on r.Field(1) equals o.City
	             where r.Field(2) == "NL"
	             select new { City = r.Field(0), TeamName= r.Field(1),Player=o.Player }; 

	 foreach (var team in teams)
	 {
	     Console.WriteLine(String.Format("Pitcher {0} from the {1} {2}", team.Player,team.TeamName, team.City));
	 }

	 Console.ReadLine();

	 cn.Close(); 

}

When run this, we get

Result Set 2

Tags: , , , ,

January 28, 2008 Posted by | .Net, C#, Linq | 14 Comments

Sql Server 2005 ROW_NUMBER function

The ROW_NUMBER function is a neat little enhancement that makes a fairly common task in Sql pretty easy : generating a row number for a result set. You’ve always been able to do this Sql, but had to do something like the following :

CREATE TABLE #temp
(
	RowNumber int identity
	,LastName varchar(100)
	,HireDate datetime
)

INSERT INTO #temp(LastName
	,HireDate)
SELECT LastName
	,HireDate
FROM dbo.Employees
ORDER BY HireDate DESC,LastName DESC

SELECT * FROM #temp

Results :

Result Set 1

Why would you want to return a unique row number in a result set? A lot of times it’s for more complex comparisons, or maybe for a paging function.

The ROW_NUMBER function makes this step easier :

SELECT ROW_NUMBER() OVER(ORDER BY HireDate DESC,LastName DESC) AS 'RowNumber'
	,LastName
	,HireDate
FROM dbo.Employees

Results :

Result Set 2

All we’re basically doing here is telling the result set how the RowNumber will be assigned. This is doing the same thing as our first example.

However, the ROW_NUMBER function has a PARTITION attribute we can set. What this does is first group our data by one or more columns, and then assign the RowNumber column. For instance in our example above, we could partition the data on TitleOfCourtesy(Mr.,Mrs.,etc). What this means is for each unique instance of TitleOfCourtesy, we assign a RowNumber. So our Sql looks like this :

SELECT ROW_NUMBER() OVER(PARTITION BY TitleOfCourtesy 
			ORDER BY HireDate DESC,LastName DESC) AS 'RowNumber'
	,LastName
	,HireDate,TitleOfCourtesy
FROM dbo.Employees
ORDER BY ROWNUMBER

Which gives us this result set:

Result Set 3

Tags:

January 25, 2008 Posted by | SQL Server, Technology | 4 Comments

Dude, where’s my Management Studio?

This one has gotten me several times in the past after a clean Windows install. What happens is that after I go through installing Sql Server 2005, I can’t find Sql Managment Studio anywhere. The problem is that it won’t install unless you have Internet Information Services installed. For Windows XP, IIS is not installed by default.

To add it, do the following :

  • go to Control Panel
  • Click add or remove programs
  • Click Add/Remove Windows components
  • Click Internet Information Services and follow the directions from there on out

Tags:

January 21, 2008 Posted by | Technology | Leave a comment

Free Mac Applications for the developing soul

I am a very cheap man. When it comes to software, I always try to avoid paying for something if there is a quality, FREE alternative available. After abandoning my PC over a year ago in favor of a Mac, there is only one app I have shelled out money for (Parallels to run a few Windows apps). I have gotten by quite nicely with the below freebies :

Firefox : Without question my number one application. Nothing else even comes close to touching Firefox. The beauty of having tabbed web pages long before Internet Explorer aside, Firefox’s extensions are really what make this browser the best thing going. My favorite is Greasemonkey , which is an extension that allows your to interject custom javascript into any html page returned. There are also a ton of themes available too for Firefox.

Quicksilver : This is the application I surely use the most. It took me a while to get the hang of Quicksilver, but once I did, I now know why people call it indispensable. Quicksilver is billed as an application launcher. It does what Spotlight does (find things on your computer), but much quicker. For instance, I type “cmd+space”, then type the letter “f” and enter, and Firefox is launched. It also does a ton of other things which make your every day computing experience that much more enjoyable. You can append to text files, send an email or instant message, all with a few key strokes. Anything you can think about doing quicker can be accomplished with Quicksilver.

Textwrangler : Like any good American, I love text editors. Textwrangler is one of the best free ones I’ve found. However, I will admit that I recently caught the Textmate fever, and am now hopelessly in love with it. Textmate costs $59, but I haven’t forked over any money yet.

Qumana : This is a great blog editor, with a very easy to understand and elegant interface. You can hook it up so it fetches and posts directly to your blog address. However, I’ve been using Textmate for blogs now too (damn I may have to part with that $59!)

Netbeans : I’ve been learning a lot of Ruby on Rails lately, and have used a number of editors to do so. Netbeans is by no means a lightweight, as it is a full fledged development IDE. I also used Aptana extensively too, but I have to give the slight edge to Netbeans for it’s awesome code completion. Netbeans is quite comparable to Visual Studio.Net.

Thunderbird : It’s Firefox for your email. Simply awesome.

OpenOffice : A great open source office suite that along with Thunderbird, can legitimately replace Microsoft Office at the work place for most folks.

KeepassX : A very nice password manager. Will even generate very strong passwords and allow you to directly navigate to a website from within KeepassX. You can also set up parameters to pass to a website so fields (username,password,etc.) are automatically populated for you.

AOL Radio : I know, AOL sucks. But the big deal with this is that it plays XM Radio FOR FREE.

Adium : Instant messenger that allows you to talk with many other IM clients (MSN,AOL,Yahoo,etc.)

CocoaMySql : MySql database editor.

SNES9x : If you’re an old time video game freak like me, then this Super Nintendo emulator is a must. Final Fantasy IV!

Gimp : Legendary image editor.

Google Notifier : I use GMail and Google Calendar. This sits in my Mac Toolbar and alerts me to new emails or calendar events.

MacSolitaire : the classics never die.

Onyx : Keep your Mac humming along with this OSX maintenance and optimization tool.

Let me know if you can think of any others you would want to see on this list.

Tags: ,,,,,,,,,,,,,,,,,

January 21, 2008 Posted by | Mac, Technology | Leave a comment

Using Linq to query Xml

If you’ve used the .Net’s framework new Linq functionality, you’ve no doubt seen how nice it is to query a data object directly. For me, one of my favorite parts of Linq is being able to query an Xml object. Gone are the days are writing awkward XPath queries, or "ripping through" an Xml document.

Here’s the old school way of querying Xml :

static void OldSchool()
{ 
    string teams = @" " +
        "PhilliesNL" +
        "YankeesAL" +
        "AngelsAL" +
        "BrewersNL" +
        " ";

    XmlDocument xml = new XmlDocument();
    xml.LoadXml(teams);

    XmlNodeList NLTeams = xml.SelectNodes("//League[. = 'NL']/parent::node()/Name");

    foreach (XmlNode team in NLTeams)
        Console.WriteLine(team.InnerText);
}

And here’s the new school way :

static void NewSchool()
{
    XElement teams = XElement.Parse(@" " +
        "PhilliesNL" +
        "YankeesAL" +
        "AngelsAL" +
        "BrewersNL" +
        " ");
    
    IEnumerable NLTeams = from t in teams.Descendants("Team")
                   where t.Element("League").Value.Equals("NL")
                   select t.Element("Name").Value;

    foreach (var team in NLTeams)
        Console.WriteLine(team);
}

You’re pretty much writing the same amount of code, but the Linq way is more preferable in that it standardizes a way to query and object in .net for data.

Tags: , , ,

January 14, 2008 Posted by | .Net, C#, Linq, Technology, Xml | Leave a comment

SQL Server 2005 Productivity tips

(1)  Synonyms
Tired of typing "SELECT ID FROM TBL_THELONGESTTABLENAMEINHISTORY" ? Then create a synonym and make life easier for yourself.

CREATE SYNONYM [dbo].[mininame]
FOR [NorthWind].[dbo].[TBL_THELONGESTTABLENAMEINHISTORY]

Now to access that mama-jamma, your query is whittled down to :

SELECT ID FROM mini


(2) Use those Templates young man!
SQL Management Studio supplies you with a wealth of pre-defined templates. Want the basic syntax for a cursor, or need the full create syntax for a table constraint?  Just open up the Templates window and drag it onto your SQL query window. 

But if you’re like me, you may have to run the same code a number of times, and templates are a wonderful place to store stuff like that.  All you have to do is right click on the SQL Server Template folder in the Template Explorer and choose to add a new template.  For my personal templates, I even created my own folder to store them in:

You can even add your own parameters to templates, and be prompted for the values at design time.  Parameters are enclosed in less than/greater than signs, take the form of <Parameter Name,Type,Value> :

IF EXISTS (

 SELECT * 

 FROM INFORMATION_SCHEMA.ROUTINES 

 WHERE SPECIFIC_SCHEMA = N'<Schema_Name, sysname, Schema_Name>'

 AND SPECIFIC_NAME = N'<Procedure_Name, sysname, Procedure_Name>' 

)

 DROP PROCEDURE <Schema_Name, sysname, Schema_Name>.<Procedure_Name, sysname, Procedure_Name>

GO



CREATE PROCEDURE <Schema_Name, sysname, Schema_Name>.<Procedure_Name, sysname, Procedure_Name>

 <@param1, sysname, @p1> <datatype_for_param1, , int> = <default_value_for_param1, , 0>, 

 <@param2, sysname, @p2> <datatype_for_param2, , int> = <default_value_for_param2, , 0>

AS

 SELECT @p1, @p2

GO

After saving your template, you can then add it to an existing script in a query window, and then click Ctrl-Shift-M to bring up a dialogue box for you to fill in your parameter values :

(3) Metadata views

I recently had to dive into a huge (50 gig) database.  I had to generate some rather large stored procedures that would delete a common key from a lot of tables. To learn the database, I used the information schema views a lot.  I was able to determine if a column existed in all the tables by querying the COLUMNS and TABLES views. I even modified the Sql to generate the delete statements for me, like this:

SELECT 'DELETE ' + a.TABLE_NAME + ' WHERE ProductID = @ProductID'
FROM information_schema.columns a
INNER JOIN INFORMATION_SCHEMA.TABLES b ON a.table_name=b.table_name
WHERE column_name LIKE '%ProductID%'
AND table_type='BASE TABLE'
ORDER BY b.table_name

-------------------
RESULTS
-------------------
>>DELETE Order Details WHERE ProductID = @ProductID
>>DELETE Products WHERE ProductID = @ProductID

What I had to do when I was generating this for 50 tables was run the sql and see if any constraint errors were thrown.  Most likely, I just had to change the order of the delete statements.

(4) Use Sql Profiler to see the Sql your app runs

SQL profiler is best known for troubleshooting performance issues in SQL Server.  But the thing I usually use it for is to see exactly what sql gets run by an application.  Curious to see what happen when you use the neat little WYSIWYG editor in Crystal Reports to query the database?  Run a quick profiler trace.  Want to see what tables are getting accessed by your application, run a profiler trace. The nice thing about getting to the trace data is that you can also copy and paste it directly into SQL Management Studio, or into a separate stored procedure.  Which brings us to our next tip….

(5) Debugging stored procedures

In SQL Server 2005, the only way you can step into a stored procedure is if you create a Visual Studio.Net 2005 Database Project. If you right-click on the stored procedure, and then select step into, you’ll also be prompted to fill in any needed parameter values.  This can be a real pain in the hind quarters if there are a lot of parameters. What I end up doing is saving the sql that gets executed into a separate, temporary stored procedure, and then stepping into that procedure.

Tags:

January 14, 2008 Posted by | .Net, SQL Server | 3 Comments