.NET Data Secrets, Part 1, Page 2
Cheating with SQL
So, you're developing all those SQL Server applications—and keep forgetting the difference between an inner join and an outer join? Unsure whether you should "Order By", "Sort By", or "Group By"? Then maybe it's about time you started to cheat.
You see, there's an easy way to write SQL—visually! Simply use the Server Explorer to open up your SQL Server, then right-click on the Views node. Select 'New View,' add the tables you wish to use in your query—and begin designing, specifying any sorting or other criteria. Right-click a blank area of the table view and select Property Pages to specify further criteria.
Also, the designer is particularly useful when dealing with date and time fields because it incorporates any required SQL Server functions for you, automatically. Just write the query as you would in, say, Microsoft Access.
Top Tip: The designer may be cool—but don't trust the relationships it automatically adds for you. Hover your mouse over a relationship to view its details. If it's incorrect, select with your mouse and press Delete. To create a relationship, drag-and-drop one field onto the other. Right-click and select Property Pages if you want to alter its details.
When you've finished, click Query > Run to test that it produces the results you require. When you're happy, simply take text from the SQL pane and remove all instances of the useless term "dbo." (this is added by SQL Server for the view and will ruin your statement when used in data access code)—and that's it!
You now can put your SQL statement to work—and have saved yourself an hour in writing and debugging in the process. Not a bad two minutes' work.
Putting together our SQL statement the easy way
Finding the Last Identity Number Added
Everyone has this problem: you've just added a record to your SQL Server database and need to figure out what the automatically generated identity number was, so you can use the value as a foreign key in some child table.
It took me a good while to figure this one out. You simply need to issue the "select @@identity" command to SQL Server and it'll return a one-field response that contains the last identity number added during your connection.
Let's look at a commented example:
' Variable to hold the identity value of our record Dim MyIdentityValue As Integer ' Set up sample connection and command Dim objConnection As New SqlClient.SqlConnection( _ "server=NEMEAN;database=MYDATABASE;" & _ "user ID=USERID;password=PWD") Dim objCommand As New SqlClient.SqlCommand( _ "INSERT INTO author (authorname) " & _ "VALUES('Karl Moore')") ' Open connection and execute INSERT command objConnection.Open() objCommand.Connection = objConnection ' Execute and check minimum of one record affected... If objCommand.ExecuteNonQuery > 0 = True Then ' Set up separate command to retrieve identity value Dim objIdentifier As New _ SqlClient.SqlCommand("Select @@Identity", objConnection) Try ' Return value of field MyIdentityValue = objIdentifier.ExecuteScalar Catch MyIdentityValue = 0 End Try End If ' Close connection objConnection.Close()